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