[Home] [Help]
PACKAGE BODY: APPS.EAM_MAT_REQ_VALIDATE_PVT
Source
1 PACKAGE BODY EAM_MAT_REQ_VALIDATE_PVT AS
2 /* $Header: EAMVMRVB.pls 120.6 2011/02/22 11:50:45 rsandepo ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVMRVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_MAT_REQ_VALIDATE_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 ***************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'EAM_MAT_REQ_VALIDATE_PVT';
24
25 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
26
27
28 /*******************************************************************
29 * Procedure : Check_Existence
30 * Returns : None
31 * Parameters IN : Material Requirements Record
32 * Parameters OUT NOCOPY: Old Material Requirements Record
33 * Mesg Token Table
34 * Return Status
35 * Purpose : Procedure will query the old EAM work order
36 * record and return it in old record variables. If the
37 * Transaction Type is Create and the record already
38 * exists the return status would be error or if the
39 * transaction type is Update and the record
40 * does not exist then the return status would be an
41 * error as well. Mesg_Token_Table will carry the
42 * error messsage and the tokens associated with the
43 * message.
44 *********************************************************************/
45
46 PROCEDURE Check_Existence
47 ( p_eam_mat_req_rec IN EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
48 , x_old_eam_mat_req_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
49 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
50 , x_return_status OUT NOCOPY VARCHAR2
51 )
52 IS
53 l_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
54 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
55 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
56 l_return_status VARCHAR2(1);
57 l_inventory_item_name VARCHAR2(240);
58 BEGIN
59
60 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Quering Material Requirement'); END IF;
61
62 EAM_MAT_REQ_UTILITY_PVT.Query_Row
63 ( p_wip_entity_id => p_eam_mat_req_rec.wip_entity_id
64 , p_organization_id => p_eam_mat_req_rec.organization_id
65 , p_operation_seq_num => p_eam_mat_req_rec.operation_seq_num
66 , p_inventory_item_id => p_eam_mat_req_rec.inventory_item_id
67 , x_eam_mat_req_rec => x_old_eam_mat_req_rec
68 , x_Return_status => l_return_status
69 );
70
71 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
72
73 IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND AND
74 p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
75 THEN
76 --Start bug# 11672256
77 SELECT segment1 INTO l_inventory_item_name FROM mtl_system_items WHERE inventory_item_id=p_eam_mat_req_rec.inventory_item_id AND organization_id=p_eam_mat_req_rec.organization_id;
78 l_token_tbl(1).token_name := 'INVENTORY_ITEM_NAME';
79 l_token_tbl(1).token_value := l_inventory_item_name;
80 --End bug# 11672256
81
82 l_out_mesg_token_tbl := l_mesg_token_tbl;
83 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
84 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
85 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
86 , p_message_name => 'EAM_MR_ALREADY_EXISTS'
87 , p_token_tbl => l_token_tbl
88 );
89 l_mesg_token_tbl := l_out_mesg_token_tbl;
90
91 l_return_status := FND_API.G_RET_STS_ERROR;
92
93 ELSIF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND AND
94 p_eam_mat_req_rec.transaction_type IN
95 (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
96 THEN
97 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
98 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
99
100 l_out_mesg_token_tbl := l_mesg_token_tbl;
101 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
102 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
103 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
104 , p_message_name => 'EAM_MR_DOESNOT_EXISTS'
105 , p_token_tbl => l_token_tbl
106 );
107 l_mesg_token_tbl := l_out_mesg_token_tbl;
108
109 l_return_status := FND_API.G_RET_STS_ERROR;
110
111 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
112 THEN
113 l_out_mesg_token_tbl := l_mesg_token_tbl;
114 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
115 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
116 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
117 , p_message_name => NULL
118 , p_message_text => 'Unexpected error while existence verification of ' || 'Material Requirement '|| p_eam_mat_req_rec.inventory_item_id , p_token_tbl => l_token_tbl
119 );
120 l_mesg_token_tbl := l_out_mesg_token_tbl;
121 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122
123 ELSE /* Assign the relevant transaction type for SYNC operations */
124 IF p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
125 IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
126 x_old_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
127 ELSE
128 x_old_eam_mat_req_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
129 END IF;
130 END IF;
131 l_return_status := FND_API.G_RET_STS_SUCCESS;
132
133 END IF;
134
135 x_return_status := l_return_status;
136 x_mesg_token_tbl := l_mesg_token_tbl;
137 END Check_Existence;
138
139
140
141 /********************************************************************
142 * Procedure : Check_Attributes
143 * Parameters IN : Material Requirements Column record
144 * Old Material Requirements Column record
145 * Parameters OUT NOCOPY: Return Status
146 * Mesg Token Table
147 * Purpose : Check_Attrbibutes procedure will validate every
148 * revised item attrbiute in its entirety.
149 **********************************************************************/
150
151 PROCEDURE Check_Attributes
152 ( p_eam_mat_req_rec IN EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
153 , p_old_eam_mat_req_rec IN EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
154 , x_return_status OUT NOCOPY VARCHAR2
155 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
156 )
157 IS
158 l_err_text VARCHAR2(2000) := NULL;
159 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
160 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
161 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
162 g_dummy NUMBER;
163 l_ordered_quantity NUMBER;
164 l_stockable_flag VARCHAR2(1);
165 l_allocated NUMBER;
166 l_uom VARCHAR2(5);
167 l_material_issue_by_mo VARCHAR2(1);
168
169 BEGIN
170
171 x_return_status := FND_API.G_RET_STS_SUCCESS;
172
173 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within Material Requirement Check Attributes . . . '); END IF;
174
175
176 -- operation_seq_num
177
178 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating operation_seq_num . . . '); END IF;
179
180 begin
181
182 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
183
184 begin
185 select 1
186 into g_dummy
187 from wip_operations wo
188 where wo.organization_id = p_eam_mat_req_rec.organization_id
189 and wo.wip_entity_id = p_eam_mat_req_rec.wip_entity_id
190 and wo.operation_seq_num = p_eam_mat_req_rec.operation_seq_num;
191 exception
192 when others then
193 if p_eam_mat_req_rec.operation_seq_num <> 1 then
194 raise fnd_api.g_exc_error;
195 end if;
196 if p_eam_mat_req_rec.operation_seq_num = 1 and
197 p_eam_mat_req_rec.department_id is not null then
198 raise fnd_api.g_exc_error;
199 end if;
200 end;
201
202 end if;
203
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205
206 exception
207 when others then
208
209 l_token_tbl(1).token_name := 'OP_SEQ_NUM';
210 l_token_tbl(1).token_value := p_eam_mat_req_rec.operation_seq_num;
211
212 l_out_mesg_token_tbl := l_mesg_token_tbl;
213 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
214 ( p_message_name => 'EAM_MR_OP_SEQ_INVALID'
215 , p_token_tbl => l_token_tbl
216 , p_mesg_token_tbl => l_mesg_token_tbl
217 , x_mesg_token_tbl => l_out_mesg_token_tbl
218 );
219 l_mesg_token_tbl := l_out_mesg_token_tbl;
220
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 x_mesg_token_tbl := l_mesg_token_tbl ;
223 return;
224
225 end;
226
227
228 -- inventory_item_id
229 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating inventory_item_id . . . '); END IF;
230
231 declare
232 l_stockable_flag VARCHAR2(2);
233 NOT_PURCHASABLE EXCEPTION;
234 l_count NUMBER;
235 begin
236
237 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
238
239 -- Fix for bug# 3602041 - Removed check for bom_enabled flag in the where clause of the query
240 select 1
241 into g_dummy
242 from mtl_system_items
243 where inventory_item_id = p_eam_mat_req_rec.inventory_item_id
244 and organization_id = p_eam_mat_req_rec.organization_id
245 and ( bom_item_type = 4
246 and ( eam_item_type IS NULL or eam_item_type = 3 or eam_item_type = 1) );
247
248 /* Check added so that assets and activities are not included */
249
250
251 -- Check that if item is stockable, then it is also purchased
252 -- and purchasable
253 select stock_enabled_flag into l_stockable_flag
254 from mtl_system_items where
255 inventory_item_id = p_eam_mat_req_rec.inventory_item_id
256 and organization_id = p_eam_mat_req_rec.organization_id;
257 if l_stockable_flag = 'N' then
258 select count(*) into l_count from mtl_system_items
259 where inventory_item_id = p_eam_mat_req_rec.inventory_item_id
260 and organization_id = p_eam_mat_req_rec.organization_id
261 and purchasing_enabled_flag = 'Y'
262 and purchasing_item_flag = 'Y';
263 if l_count <> 1 then
264 raise NOT_PURCHASABLE;
265 end if;
266 end if;
267
268 end if;
269
270 x_return_status := FND_API.G_RET_STS_SUCCESS;
271
272 exception
273
274 when NOT_PURCHASABLE then
275
276 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
277 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
278
279 l_out_mesg_token_tbl := l_mesg_token_tbl;
280 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
281 ( p_message_name => 'EAM_MR_ITEM_NOT_PURCH'
282 , p_token_tbl => l_token_tbl
283 , p_mesg_token_tbl => l_mesg_token_tbl
284 , x_mesg_token_tbl => l_out_mesg_token_tbl
285 );
286 l_mesg_token_tbl := l_out_mesg_token_tbl;
287
288 x_return_status := FND_API.G_RET_STS_ERROR;
289 x_mesg_token_tbl := l_mesg_token_tbl ;
290 return;
291
292 when others then
293
294 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
295 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
296
297 l_out_mesg_token_tbl := l_mesg_token_tbl;
298 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
299 ( p_message_name => 'EAM_MR_INV_ITEM_INVALID'
300 , p_token_tbl => l_token_tbl
301 , p_mesg_token_tbl => l_mesg_token_tbl
302 , x_mesg_token_tbl => l_out_mesg_token_tbl
303 );
304 l_mesg_token_tbl := l_out_mesg_token_tbl;
305
306 x_return_status := FND_API.G_RET_STS_ERROR;
307 x_mesg_token_tbl := l_mesg_token_tbl ;
308 return;
309
310 end;
311
312
313 -- quantity_per_assembly
314 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating quantity_per_assembly . . . '); END IF;
315
316 begin
317
318 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
319
320 if p_eam_mat_req_rec.quantity_per_assembly < 0 then
321 raise fnd_api.g_exc_unexpected_error;
322 end if;
323
324 end if;
325
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 exception
329 when others then
330
331 l_token_tbl(1).token_name := 'QUANTITY_PER_ASSEMBLY';
332 l_token_tbl(1).token_value := p_eam_mat_req_rec.quantity_per_assembly;
333
334 l_out_mesg_token_tbl := l_mesg_token_tbl;
335 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
336 ( p_message_name => 'EAM_MR_QTY_PER_ASSY_INVALID'
337 , p_token_tbl => l_token_tbl
338 , p_mesg_token_tbl => l_mesg_token_tbl
339 , x_mesg_token_tbl => l_out_mesg_token_tbl
340 );
341 l_mesg_token_tbl := l_out_mesg_token_tbl;
342
343 x_return_status := FND_API.G_RET_STS_ERROR;
344 x_mesg_token_tbl := l_mesg_token_tbl ;
345 return;
346
347 end;
348
349
350 -- supply_subinventory
351 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating supply_subinventory . . . '); END IF;
352
353 begin
354
355 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
356
357 if p_eam_mat_req_rec.supply_subinventory is not null then
358 select 1 into g_dummy
359 from mtl_secondary_inventories msinv
360 where nvl(msinv.disable_date, sysdate+2) > sysdate
361 and msinv.organization_id = p_eam_mat_req_rec.organization_id
362 and msinv.secondary_inventory_name = p_eam_mat_req_rec.supply_subinventory;
363 end if;
364
365 end if;
366
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368
369 exception
370 when others then
371
372 l_token_tbl(1).token_name := 'SUPPLY_SUB';
373 l_token_tbl(1).token_value := p_eam_mat_req_rec.supply_subinventory;
374
375 l_out_mesg_token_tbl := l_mesg_token_tbl;
376 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
377 ( p_message_name => 'EAM_MR_SUPPLY_SUB_INVALID'
378 , p_token_tbl => l_token_tbl
379 , p_mesg_token_tbl => l_mesg_token_tbl
380 , x_mesg_token_tbl => l_out_mesg_token_tbl
381 );
382 l_mesg_token_tbl := l_out_mesg_token_tbl;
383
384 x_return_status := FND_API.G_RET_STS_ERROR;
385 x_mesg_token_tbl := l_mesg_token_tbl ;
386 return;
387
388 end;
389
390
391
392 -- supply_locator_id
393 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating supply_locator_id . . . '); END IF;
394
395 begin
396
397 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
398
399 if p_eam_mat_req_rec.supply_locator_id is not null then
400 select 1 into g_dummy
401 from mtl_item_locations_kfv
402 where (disable_date > sysdate or disable_date is null)
403 and organization_id = p_eam_mat_req_rec.organization_id
404 and subinventory_code = p_eam_mat_req_rec.supply_subinventory
405 and inventory_location_id = p_eam_mat_req_rec.supply_locator_id;
406 end if;
407
408 end if;
409
410 x_return_status := FND_API.G_RET_STS_SUCCESS;
411
412 exception
413 when others then
414
415 l_token_tbl(1).token_name := 'SUPPLY_LOC';
416 l_token_tbl(1).token_value := p_eam_mat_req_rec.supply_locator_id;
417
418 l_out_mesg_token_tbl := l_mesg_token_tbl;
419 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
420 ( p_message_name => 'EAM_MR_SUPPLY_LOC_INVALID'
421 , p_token_tbl => l_token_tbl
422 , p_mesg_token_tbl => l_mesg_token_tbl
423 , x_mesg_token_tbl => l_out_mesg_token_tbl
424 );
425 l_mesg_token_tbl := l_out_mesg_token_tbl;
426
427 x_return_status := FND_API.G_RET_STS_ERROR;
428 x_mesg_token_tbl := l_mesg_token_tbl ;
429 return;
430
431 end;
432
433
434
435
436 -- department_id
437 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating department_id . . . '); END IF;
438
439 begin
440
441 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
442
443 if p_eam_mat_req_rec.department_id is null then
444
445 if p_eam_mat_req_rec.operation_seq_num <> 1 then
446 raise fnd_api.g_exc_error;
447 end if;
448
449 else
450
451 select 1 into g_dummy
452 from wip_operations where
453 wip_entity_id = p_eam_mat_req_rec.wip_entity_id and
454 organization_id = p_eam_mat_req_rec.organization_id and
455 operation_seq_num = p_eam_mat_req_rec.operation_seq_num and
456 department_id = p_eam_mat_req_rec.department_id;
457
458 select 1 into g_dummy
459 from bom_departments where
460 department_id = p_eam_mat_req_rec.department_id
461 and organization_id = p_eam_mat_req_rec.organization_id;
462
463 end if;
464
465 end if;
466
467 x_return_status := FND_API.G_RET_STS_SUCCESS;
468
469 exception
470 when others then
471
472 l_token_tbl(1).token_name := 'DEPARTMENT_NAME';
473 -- l_token_tbl(1).token_value := p_eam_mat_req_rec.department_id;
474
475 SELECT bd.department_code into l_token_tbl(1).token_value
476 FROM bom_departments bd
477 WHERE bd.DEPARTMENT_ID = p_eam_mat_req_rec.department_id
478 AND bd.organization_id = p_eam_mat_req_rec.organization_id;
479
480
481
482 l_out_mesg_token_tbl := l_mesg_token_tbl;
483 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
484 ( p_message_name => 'EAM_MR_DEPT_INVALID'
485 , p_token_tbl => l_token_tbl
486 , p_mesg_token_tbl => l_mesg_token_tbl
487 , x_mesg_token_tbl => l_out_mesg_token_tbl
488 );
489 l_mesg_token_tbl := l_out_mesg_token_tbl;
490
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 x_mesg_token_tbl := l_mesg_token_tbl ;
493 return;
494
495 end;
496
497
498 -- wip_supply_type
499 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_supply_type . . . '); END IF;
500
501 begin
502
503 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
504
505
506 if(p_eam_mat_req_rec.wip_supply_type is not null and p_eam_mat_req_rec.wip_supply_type not in (wip_constants.push, wip_constants.bulk, wip_constants.based_on_bom)) then
507 --not a valid supply type
508
509 raise fnd_api.g_exc_unexpected_error;
510
511 end if;
512
513 end if;
514
515 x_return_status := FND_API.G_RET_STS_SUCCESS;
516
517 exception
518 when others then
519
520 l_token_tbl(1).token_name := 'WIP_SUPPLY_TYPE';
521 l_token_tbl(1).token_value := p_eam_mat_req_rec.wip_supply_type;
522
523 l_out_mesg_token_tbl := l_mesg_token_tbl;
524 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
525 ( p_message_name => 'EAM_MR_SUPPLY_TYPE_INVALID'
526 , p_token_tbl => l_token_tbl
527 , p_mesg_token_tbl => l_mesg_token_tbl
528 , x_mesg_token_tbl => l_out_mesg_token_tbl
529 );
530 l_mesg_token_tbl := l_out_mesg_token_tbl;
531
532 x_return_status := FND_API.G_RET_STS_ERROR;
533 x_mesg_token_tbl := l_mesg_token_tbl ;
534 return;
535
536 end;
537
538
539 -- mrp_net_flag
540 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating mrp_net_flag . . . '); END IF;
541
542 begin
543
544 if (p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
545
546 if p_eam_mat_req_rec.mrp_net_flag not in (wip_constants.yes, wip_constants.no) then
547 raise fnd_api.g_exc_unexpected_error;
548 end if;
549
550 end if;
551
552 x_return_status := FND_API.G_RET_STS_SUCCESS;
553
554 exception
555 when others then
556
557 l_token_tbl(1).token_name := 'MRP_NET_FLAG';
558 l_token_tbl(1).token_value := p_eam_mat_req_rec.mrp_net_flag;
559
560 l_out_mesg_token_tbl := l_mesg_token_tbl;
561 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
562 ( p_message_name => 'EAM_MR_MRP_NET_INVALID'
563 , p_token_tbl => l_token_tbl
564 , p_mesg_token_tbl => l_mesg_token_tbl
565 , x_mesg_token_tbl => l_out_mesg_token_tbl
566 );
567 l_mesg_token_tbl := l_out_mesg_token_tbl;
568
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 x_mesg_token_tbl := l_mesg_token_tbl ;
571 return;
572
573 end;
574
575 SELECT stock_enabled_flag,primary_uom_code
576 INTO l_stockable_flag,l_uom
577 FROM MTL_SYSTEM_ITEMS_KFV
578 WHERE inventory_item_id = p_eam_mat_req_rec.inventory_item_id
579 AND organization_id = p_eam_mat_req_rec.organization_id;
580
581 IF(p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_DELETE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) THEN
582 l_allocated := EAM_MATERIAL_ALLOCQTY_PKG.allocated_quantity(
583 p_eam_mat_req_rec.wip_entity_id,
584 p_eam_mat_req_rec.operation_seq_num,
585 p_eam_mat_req_rec.organization_id,
586 p_eam_mat_req_rec.inventory_item_id);
587 END IF;
588
589 IF(l_stockable_flag='Y') THEN
590 -- delete material_requirement
591 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_requirement . . . '); END IF;
592
593 declare
594 l_count_mmt NUMBER :=0;
595 l_count_mmtt NUMBER :=0;
596 l_issued_qty NUMBER :=0;
597 begin
598
599 if (p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
600
601 select count(*)
602 into l_count_mmtt
603 from mtl_material_transactions_temp
604 where transaction_source_id = p_eam_mat_req_rec.wip_entity_id
605 and organization_id = p_eam_mat_req_rec.organization_id
606 and operation_seq_num = p_eam_mat_req_rec.operation_seq_num
607 and inventory_item_id = p_eam_mat_req_rec.inventory_item_id;
608
609 if(l_count_mmtt > 0) then
610 raise fnd_api.g_exc_unexpected_error;
611 end if;
612
613 select count(*)
614 into l_count_mmt
615 from mtl_material_transactions
616 where transaction_source_id = p_eam_mat_req_rec.wip_entity_id
617 and organization_id = p_eam_mat_req_rec.organization_id
618 and operation_seq_num = p_eam_mat_req_rec.operation_seq_num
619 and inventory_item_id = p_eam_mat_req_rec.inventory_item_id;
620
621 if(l_count_mmt > 0) then
622 raise fnd_api.g_exc_unexpected_error;
623 end if;
624
625 select quantity_issued
626 into l_issued_qty
627 from wip_requirement_operations
628 where wip_entity_id = p_eam_mat_req_rec.wip_entity_id
629 and organization_id = p_eam_mat_req_rec.organization_id
630 and operation_seq_num = p_eam_mat_req_rec.operation_seq_num
631 and inventory_item_id = p_eam_mat_req_rec.inventory_item_id;
632
633 if(l_issued_qty <> 0) then
634 raise fnd_api.g_exc_unexpected_error;
635 end if;
636
637 end if;
638
639 x_return_status := FND_API.G_RET_STS_SUCCESS;
640
641 exception
642 when others then
643
644 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
645 l_token_tbl(1).token_value := p_eam_mat_req_rec.wip_entity_id;
646
647 l_out_mesg_token_tbl := l_mesg_token_tbl;
648 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
649 ( p_message_name => 'EAM_MR_DELETE_INVALID'
650 , p_token_tbl => l_token_tbl
651 , p_mesg_token_tbl => l_mesg_token_tbl
652 , x_mesg_token_tbl => l_out_mesg_token_tbl
653 );
654 l_mesg_token_tbl := l_out_mesg_token_tbl;
655
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 x_mesg_token_tbl := l_mesg_token_tbl ;
658 return;
659
660 end;
661
662
663
664 -- Required Quantity
665 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating required_quantity . . . '); END IF;
666 if (p_eam_mat_req_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
667 and p_eam_mat_req_rec.required_quantity < p_old_eam_mat_req_rec.required_quantity) then
668 begin
669
670 select material_issue_by_mo
671 into l_material_issue_by_mo
672 from wip_discrete_jobs
673 where organization_id=p_eam_mat_req_rec.organization_id
674 and wip_entity_id=p_eam_mat_req_rec.wip_entity_id;
675
676 if l_material_issue_by_mo='Y' and p_eam_mat_req_rec.required_quantity < nvl(p_eam_mat_req_rec.quantity_issued,0)+ nvl(l_allocated,0) then
677 raise fnd_api.g_exc_unexpected_error;
678 end if;
679
680 x_return_status := FND_API.G_RET_STS_SUCCESS;
681
682 exception
683 when others then
684
685 l_out_mesg_token_tbl := l_mesg_token_tbl;
686 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
687 ( p_message_name => 'EAM_MR_REQ_QTY_INVALID'
688 , p_token_tbl => l_token_tbl
689 , p_mesg_token_tbl => l_mesg_token_tbl
690 , x_mesg_token_tbl => l_out_mesg_token_tbl
691 );
692 l_mesg_token_tbl := l_out_mesg_token_tbl;
693
694 x_return_status := FND_API.G_RET_STS_ERROR;
695 x_mesg_token_tbl := l_mesg_token_tbl ;
696 return;
697
698 end;
699 end if;
700 --start of fix for 3352406
701 ELSE
702 g_dummy:=0;
703 l_ordered_quantity:=0;
704
705 IF(p_eam_mat_req_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_DELETE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) THEN
706 --Bug#3691325 If Po Quantity or Req Quantity is greater than zero,we cannot delete the direct item
707 --Bug#4862404 (appsperf) - Brought the eam_work_order_direct_items_v view query inline and removed all
708 -- unnecessary columns/tables.
709 SELECT greatest(nvl(ewodi.po_quantity_ordered,0), nvl(ewodi.rql_quantity_ordered,0))
710 INTO l_ordered_quantity
711 from
712 (
713 SELECT
714 wro.wip_entity_id,
715 wro.operation_seq_num,
716 wro.organization_id,
717 wro.inventory_item_id as item_id,
718 wro.quantity as rql_quantity_ordered,
719 sum(pd.quantity_ordered) as po_quantity_ordered
720 FROM ( SELECT wro.wip_entity_id, wro.operation_seq_num, wro.organization_id, wro.inventory_item_id,
721 /* sum(rql.quantity) quantity */
722 sum(Decode(upper(NVL(rqh.authorization_status, 'APPROVED')), 'CANCELLED', 0, 'REJECTED', 0, 'SYSTEM_SAVED',0,rql.quantity)) quantity
723 FROM (
724 SELECT
725 wro.wip_entity_id, wro.operation_seq_num, wro.organization_id, wro.inventory_item_id
726 FROM wip_requirement_operations wro, mtl_system_items_kfv msi
727 WHERE msi.inventory_item_id = wro.inventory_item_id
728 AND msi.organization_id = wro.organization_id
729 AND nvl(msi.stock_enabled_flag, 'N') = 'N'
730 )
731 wro,
732 po_requisition_lines_all rql,
733 po_requisition_headers_all rqh
734 WHERE
735 wro.wip_entity_id = rql.wip_entity_id (+)
736 AND wro.organization_id = rql.destination_organization_id (+)
737 AND wro.operation_seq_num = rql.wip_operation_seq_num (+)
738 AND rql.requisition_header_id = rqh.requisition_header_id(+)
739 /* AND upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED') */
740 AND rql.wip_resource_seq_num is null AND wro.inventory_item_id = rql.item_id (+)
741 GROUP BY
742 wro.wip_entity_id, wro.operation_seq_num, wro.organization_id,
743 wro.inventory_item_id)
744 wro,
745 ( SELECT pd1.wip_entity_id,
746 pd1.wip_operation_seq_num,
747 pd1.destination_organization_id,
748 pd1.wip_resource_seq_num,
749 pd1.quantity_ordered,
750 pol.item_id,
751 pol.cancel_flag
752 FROM po_lines_all pol, po_distributions_all pd1
753 WHERE pol.po_line_id = pd1.po_line_id
754 AND upper(nvl(pol.cancel_flag, 'N')) <> 'Y') pd
755 WHERE wro.wip_entity_id = pd.wip_entity_id(+)
756 AND wro.organization_id = pd.destination_organization_id(+)
757 AND wro.operation_seq_num = pd.wip_operation_seq_num(+)
758 /*AND upper(nvl(pd.cancel_flag, 'N')) <> 'Y' #7509781*/
759 AND pd.wip_resource_seq_num is null
760 AND wro.inventory_item_id = pd.item_id (+)
761 GROUP BY
762 wro.wip_entity_id, wro.operation_seq_num, wro.organization_id,
763 wro.inventory_item_id,
764 wro.quantity
765 ) ewodi
766 WHERE ewodi.wip_entity_id= p_eam_mat_req_rec.wip_entity_id
767 AND ewodi.operation_seq_num=p_eam_mat_req_rec.operation_seq_num
768 AND ewodi.organization_id=p_eam_mat_req_rec.organization_id
769 AND ewodi.item_id=p_eam_mat_req_rec.inventory_item_id;
770
771
772
773 IF(p_eam_mat_req_rec.transaction_type =EAM_PROCESS_WO_PVT.G_OPR_DELETE) AND
774 (l_ordered_quantity > 0) THEN
775 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
776 l_token_tbl(1).token_value := p_eam_mat_req_rec.wip_entity_id;
777
778 l_out_mesg_token_tbl := l_mesg_token_tbl;
779 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
780 ( p_message_name => 'EAM_DI_DELETE_INVALID'
781 , p_token_tbl => l_token_tbl
782 , p_mesg_token_tbl => l_mesg_token_tbl
783 , x_mesg_token_tbl => l_out_mesg_token_tbl
784 );
785 l_mesg_token_tbl := l_out_mesg_token_tbl;
786 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Cannot delete non-stockable inventory item . . . '); END IF;
787
788 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_ERROR;
789 x_mesg_token_tbl := l_mesg_token_tbl ;
790 return;
791 END IF;
792 END IF;
793 END IF;
794 --end of fix for 3352406
795
796 -- delete material_requirement
797
798
799 EXCEPTION
800 WHEN OTHERS THEN
801
802 l_token_tbl(1).token_name := 'Validation (Check Attributes)';
803 l_token_tbl(1).token_value := substrb(SQLERRM,1,200);
804
805 l_out_mesg_token_tbl := l_mesg_token_tbl;
806 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
807 ( p_message_name => NULL
808 , p_token_tbl => l_token_tbl
809 , p_mesg_token_tbl => l_mesg_token_tbl
810 , x_mesg_token_tbl => l_out_mesg_token_tbl
811 ) ;
812 l_mesg_token_tbl := l_out_mesg_token_tbl;
813
814 -- Return the status and message table.
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
816 x_mesg_token_tbl := l_mesg_token_tbl ;
817
818
819 END Check_Attributes;
820
821 /*********************************************************************
822 * Procedure : Check_Required
823 * Parameters IN : Material Requirements column record
824 * Parameters OUT NOCOPY: Mesg Token Table
825 * Return_Status
826 * Purpose :
827 **********************************************************************/
828
829 PROCEDURE Check_Required
830 ( p_eam_mat_req_rec IN EAM_PROCESS_WO_PUB.eam_mat_req_rec_type
831 , x_return_status OUT NOCOPY VARCHAR2
832 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
833 )
834 IS
835 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
836 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
837 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
838 BEGIN
839
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841
842
843 IF p_eam_mat_req_rec.wip_entity_id IS NULL
844 THEN
845 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
846 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
847
848 l_out_mesg_token_tbl := l_mesg_token_tbl;
849 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
850 ( p_message_name => 'EAM_MR_ENTITY_ID_REQUIRED'
851 , p_token_tbl => l_Token_tbl
852 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
853 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
854 );
855 l_mesg_token_tbl := l_out_mesg_token_tbl;
856
857 x_return_status := FND_API.G_RET_STS_ERROR;
858
859 END IF;
860
861
862 IF p_eam_mat_req_rec.organization_id IS NULL
863 THEN
864 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
865 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
866
867 l_out_mesg_token_tbl := l_mesg_token_tbl;
868 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
869 ( p_message_name => 'EAM_MR_ORG_REQUIRED'
870 , p_token_tbl => l_Token_tbl
871 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
872 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
873 );
874 l_mesg_token_tbl := l_out_mesg_token_tbl;
875
876 x_return_status := FND_API.G_RET_STS_ERROR;
877
878 END IF;
879
880
881 IF p_eam_mat_req_rec.inventory_item_id IS NULL
882 THEN
883 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
884 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
885
886 l_out_mesg_token_tbl := l_mesg_token_tbl;
887 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
888 ( p_message_name => 'EAM_MR_INV_ITEM_REQUIRED'
889 , p_token_tbl => l_Token_tbl
890 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
891 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
892 );
893 l_mesg_token_tbl := l_out_mesg_token_tbl;
894
895 x_return_status := FND_API.G_RET_STS_ERROR;
896
897 END IF;
898
899
900 IF p_eam_mat_req_rec.operation_seq_num IS NULL
901 THEN
902 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
903 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
904
905 l_out_mesg_token_tbl := l_mesg_token_tbl;
906 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
907 ( p_message_name => 'EAM_MR_OP_SEQ_REQUIRED'
908 , p_token_tbl => l_Token_tbl
909 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
910 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
911 );
912 l_mesg_token_tbl := l_out_mesg_token_tbl;
913
914 x_return_status := FND_API.G_RET_STS_ERROR;
915
916 END IF;
917
918
919 IF p_eam_mat_req_rec.wip_supply_type IS NULL
920 THEN
921 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
922 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
923
924 l_out_mesg_token_tbl := l_mesg_token_tbl;
925 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
926 ( p_message_name => 'EAM_MR_SUPPLY_TYPE_REQUIRED'
927 , p_token_tbl => l_Token_tbl
928 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
929 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
930 );
931 l_mesg_token_tbl := l_out_mesg_token_tbl;
932
933 x_return_status := FND_API.G_RET_STS_ERROR;
934
935 END IF;
936
937
938 IF p_eam_mat_req_rec.required_quantity IS NULL
939 THEN
940 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
941 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
942
943 l_out_mesg_token_tbl := l_mesg_token_tbl;
944 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
945 ( p_message_name => 'EAM_MR_REQUIRED_QTY_REQUIRED'
946 , p_token_tbl => l_Token_tbl
947 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
948 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
949 );
950 l_mesg_token_tbl := l_out_mesg_token_tbl;
951
952 x_return_status := FND_API.G_RET_STS_ERROR;
953
954 END IF;
955
956
957 IF p_eam_mat_req_rec.quantity_issued IS NULL
958 THEN
959 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
960 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
961
962 l_out_mesg_token_tbl := l_mesg_token_tbl;
963 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
964 ( p_message_name => 'EAM_MR_ISSUED_QTY_REQUIRED'
965 , p_token_tbl => l_Token_tbl
966 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
967 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
968 );
969 l_mesg_token_tbl := l_out_mesg_token_tbl;
970
971 x_return_status := FND_API.G_RET_STS_ERROR;
972
973 END IF;
974
975
976 IF p_eam_mat_req_rec.quantity_per_assembly IS NULL
977 THEN
978 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
979 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
980
981 l_out_mesg_token_tbl := l_mesg_token_tbl;
982 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
983 ( p_message_name => 'EAM_MR_ASSY_QTY_REQUIRED'
984 , p_token_tbl => l_Token_tbl
985 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
986 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
987 );
988 l_mesg_token_tbl := l_out_mesg_token_tbl;
989
990 x_return_status := FND_API.G_RET_STS_ERROR;
991
992 END IF;
993
994
995 IF p_eam_mat_req_rec.mrp_net_flag IS NULL
996 THEN
997 l_token_tbl(1).token_name := 'INVENTORY_ITEM_ID';
998 l_token_tbl(1).token_value := p_eam_mat_req_rec.inventory_item_id;
999
1000 l_out_mesg_token_tbl := l_mesg_token_tbl;
1001 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1002 ( p_message_name => 'EAM_MR_MRP_NET_REQUIRED'
1003 , p_token_tbl => l_Token_tbl
1004 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1005 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
1006 );
1007 l_mesg_token_tbl := l_out_mesg_token_tbl;
1008
1009 x_return_status := FND_API.G_RET_STS_ERROR;
1010
1011 END IF;
1012
1013
1014 IF p_eam_mat_req_rec.date_required IS NULL
1015 THEN
1016 l_token_tbl(1).token_name := 'INV_ID';
1017 l_token_tbl(1).token_value := p_eam_mat_req_rec.wip_entity_id;
1018
1019 l_out_mesg_token_tbl := l_mesg_token_tbl;
1020 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
1021 ( p_message_name => 'EAM_MR_DATE_REQ_REQUIRED'
1022 , p_token_tbl => l_Token_tbl
1023 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1024 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
1025 );
1026 l_mesg_token_tbl := l_out_mesg_token_tbl;
1027
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029
1030 END IF;
1031
1032
1033 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1034
1035 END Check_Required;
1036
1037 END EAM_MAT_REQ_VALIDATE_PVT;