[Home] [Help]
PACKAGE BODY: APPS.EAM_DIRECT_ITEMS_VALIDATE_PVT
Source
1 PACKAGE BODY EAM_DIRECT_ITEMS_VALIDATE_PVT AS
2 /* $Header: EAMVDIVB.pls 120.1 2006/03/16 16:25:22 baroy noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVDIVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_DIRECT_ITEMS_VALIDATE_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 15-SEP-2003 Basanth Roy Initial Creation
21 ***************************************************************************/
22
23 G_Pkg_Name VARCHAR2(30) := 'EAM_DIRECT_ITEMS_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 : Direct Items Record
32 * Parameters OUT NOCOPY: Old Direct Items 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_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
48 , x_old_eam_direct_items_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_direct_items_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 Direct Item'); END IF;
60
61 EAM_DIRECT_ITEMS_UTILITY_PVT.Query_Row
62 ( p_wip_entity_id => p_eam_direct_items_rec.wip_entity_id
63 , p_organization_id => p_eam_direct_items_rec.organization_id
64 , p_operation_seq_num => p_eam_direct_items_rec.operation_seq_num
65 , p_direct_item_sequence_id => p_eam_direct_items_rec.direct_item_sequence_id
66 , x_eam_direct_items_rec => x_old_eam_direct_items_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_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
74 THEN
75 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
76 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_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_DI_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_direct_items_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 := 'DIRECT_ITEM_SEQUENCE_ID';
94 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_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_DI_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 ' || 'Direct Item '|| p_eam_direct_items_rec.direct_item_sequence_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_direct_items_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_direct_items_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
123 ELSE
124 x_old_eam_direct_items_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 : Direct Items Column record
140 * Old Direct Items 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_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
149 , p_old_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_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
161 BEGIN
162
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164
165 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within Direct Item Check Attributes . . . '); END IF;
166
167
168 -- department_id
169 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating department_id . . . '); END IF;
170
171 begin
172
173 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
174
175 if (p_eam_direct_items_rec.operation_seq_num = 1 and
176 p_eam_direct_items_rec.department_id is not null) OR
177 (p_eam_direct_items_rec.operation_seq_num <> 1 and
178 p_eam_direct_items_rec.department_id is null) then
179 raise fnd_api.g_exc_error;
180 end if;
181
182 if p_eam_direct_items_rec.operation_seq_num <> 1 then
183
184 select 1 into g_dummy
185 from bom_departments where
186 department_id = p_eam_direct_items_rec.department_id
187 and organization_id = p_eam_direct_items_rec.organization_id;
188
189 select 1 into g_dummy
190 from wip_operations where
191 wip_entity_id = p_eam_direct_items_rec.wip_entity_id and
192 organization_id = p_eam_direct_items_rec.organization_id and
193 operation_seq_num = p_eam_direct_items_rec.operation_seq_num and
194 department_id = p_eam_direct_items_rec.department_id;
195
196 end if;
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 := 'DEPARTMENT_ID';
206 l_token_tbl(1).token_value := p_eam_direct_items_rec.department_id;
207
208 l_out_mesg_token_tbl := l_mesg_token_tbl;
209 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
210 ( p_message_name => 'EAM_DI_DEPT_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 --start of fix for 3352406
223 g_dummy:=0;
224 l_ordered_quantity:=0;
225
226 IF(p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_DELETE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) THEN
227 --Bug#3691325 If Po Quantity or Req Quantity is greater than zero,we cannot delete the direct item
228 --Bug#4862404 - (appsperf). Brought the ewodi view query inline and removed all unnecessary tables/columns.
229
230 SELECT greatest(nvl(ewodi.po_quantity_ordered,0), nvl(ewodi.rql_quantity_ordered,0))
231 INTO l_ordered_quantity
232 from
233 (
234 SELECT
235 wed.wip_entity_id AS wip_entity_id,
236 wed.operation_seq_num,
237 wed.organization_id,
238 wed.direct_item_sequence_id,
239 wed.description as item_description,
240 wed.quantity as rql_quantity_ordered,
241 sum(pd.quantity_ordered) as po_quantity_ordered
242 FROM
243 ( SELECT
244 wed.wip_entity_id,
245 wed.operation_seq_num,
246 wed.organization_id,
247 wed.direct_item_sequence_id,
248 wed.description,
249 sum(rql.quantity) quantity
250 FROM
251 wip_eam_direct_items wed, po_requisition_lines_all rql, po_requisition_headers_all rqh
252 WHERE
253 wed.wip_entity_id = rql.wip_entity_id (+)
254 AND wed.organization_id = rql.destination_organization_id (+)
255 AND wed.operation_seq_num = rql.wip_operation_seq_num (+)
256 AND rql.requisition_header_id = rqh.requisition_header_id(+)
257 AND upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED')
258 AND rql.item_id is null
259 AND (wed.direct_item_sequence_id = rql.wip_resource_seq_num OR rql.wip_resource_seq_num is null )
260 AND wed.description = rql.item_description(+)
261 GROUP BY wed.wip_entity_id, wed.operation_seq_num, wed.organization_id,
262 wed.direct_item_sequence_id, wed.description
263 )
264 wed,
265 ( SELECT
266 pd1.wip_entity_id,
267 pd1.wip_operation_seq_num,
268 pd1.destination_organization_id,
269 pol.item_description,
270 pd1.wip_resource_seq_num,
271 pd1.quantity_ordered,
272 pol.item_id,
273 pol.cancel_flag
274 FROM
275 po_lines_all pol,
276 po_distributions_all pd1
277 WHERE
278 pol.po_line_id = pd1.po_line_id ) pd
279 WHERE
280 wed.wip_entity_id = pd.wip_entity_id(+)
281 AND wed.organization_id = pd.destination_organization_id (+)
282 AND wed.operation_seq_num = pd.wip_operation_seq_num(+)
283 AND upper(nvl(pd.cancel_flag,'N')) <> 'Y'
284 AND pd.item_id is null
285 AND (wed.direct_item_sequence_id = pd.wip_resource_seq_num OR pd.wip_resource_seq_num is null )
286 AND wed.description = pd.item_description(+)
287 GROUP BY
288 wed.wip_entity_id,
289 wed.operation_seq_num,
290 wed.organization_id,
291 wed.direct_item_sequence_id,
292 wed.description,
293 wed.quantity
294 ) ewodi
295 WHERE ewodi.wip_entity_id= p_eam_direct_items_rec.wip_entity_id
296 AND ewodi.operation_seq_num=p_eam_direct_items_rec.operation_seq_num
297 AND ewodi.organization_id =p_eam_direct_items_rec.organization_id
298 AND ewodi.direct_item_sequence_id=p_eam_direct_items_rec.direct_item_sequence_id;
299
300
301
302
303
304
305
306 IF(p_eam_direct_items_rec.transaction_type =EAM_PROCESS_WO_PVT.G_OPR_DELETE) AND
307 (l_ordered_quantity > 0) THEN
308 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
309 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_entity_id;
310
311 l_out_mesg_token_tbl := l_mesg_token_tbl;
312 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
313 ( p_message_name => 'EAM_DI_DELETE_INVALID'
314 , p_token_tbl => l_token_tbl
315 , p_mesg_token_tbl => l_mesg_token_tbl
316 , x_mesg_token_tbl => l_out_mesg_token_tbl
317 );
318 l_mesg_token_tbl := l_out_mesg_token_tbl;
319 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Cannot delete direct item . . . '); END IF;
320
321 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_ERROR;
322 x_mesg_token_tbl := l_mesg_token_tbl ;
323 return;
324 END IF;
325 END IF;
326 --end of fix for 3352406
327 /*
328
329 -- operation_seq_num
330
331 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating operation_seq_num . . . '); END IF;
332
333 begin
334
335 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
336
337 if p_eam_direct_items_rec.operation_seq_num = 1 then
338 select 1
339 into g_dummy
340 from dual
341 where p_eam_direct_items_rec.operation_seq_num = 1
342 and not exists (select 1 from wip_operations
343 where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
344 and organization_id = p_eam_direct_items_rec.organization_id);
345 else
346 select 1
347 into g_dummy
348 from wip_operations wo
349 where wo.organization_id = p_eam_direct_items_rec.organization_id
350 and wo.wip_entity_id = p_eam_direct_items_rec.wip_entity_id
351 and wo.operation_seq_num = p_eam_direct_items_rec.operation_seq_num;
352 end if;
353
354 end if;
355
356 x_return_status := FND_API.G_RET_STS_SUCCESS;
357
358 exception
359 when others then
360
361 l_token_tbl(1).token_name := 'OP_SEQ_NUM';
362 l_token_tbl(1).token_value := p_eam_direct_items_rec.operation_seq_num;
363
364 l_out_mesg_token_tbl := l_mesg_token_tbl;
365 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
366 ( p_message_name => 'EAM_DI_OP_SEQ_INVALID'
367 , p_token_tbl => l_token_tbl
368 , p_mesg_token_tbl => l_mesg_token_tbl
369 , x_mesg_token_tbl => l_out_mesg_token_tbl
370 );
371 l_mesg_token_tbl := l_out_mesg_token_tbl;
372
373 x_return_status := FND_API.G_RET_STS_ERROR;
374 x_mesg_token_tbl := l_mesg_token_tbl ;
375 return;
376
377 end;
378
379
380 -- direct_item_sequence_id
381 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating direct_item_sequence_id . . . '); END IF;
382
383 begin
384
385 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
386
387 -- Fix for bug# 3602041 - Removed check for bom_enabled flag in the where clause of the query
388 select 1
389 into g_dummy
390 from mtl_system_items
391 where direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id
392 and organization_id = p_eam_direct_items_rec.organization_id
393 and ( bom_item_type = 4
394 and ( eam_item_type IS NULL or eam_item_type = 3) );
395
396 -- Check added so that assets and activities are not included
397
398
399 end if;
400
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 exception
404 when others then
405
406 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
407 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
408
409 l_out_mesg_token_tbl := l_mesg_token_tbl;
410 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
411 ( p_message_name => 'EAM_DI_INV_ITEM_INVALID'
412 , p_token_tbl => l_token_tbl
413 , p_mesg_token_tbl => l_mesg_token_tbl
414 , x_mesg_token_tbl => l_out_mesg_token_tbl
415 );
416 l_mesg_token_tbl := l_out_mesg_token_tbl;
417
418 x_return_status := FND_API.G_RET_STS_ERROR;
419 x_mesg_token_tbl := l_mesg_token_tbl ;
420 return;
421
422 end;
423
424 -- quantity_per_assembly
425 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating quantity_per_assembly . . . '); END IF;
426
427 begin
428
429 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
430
431 if p_eam_direct_items_rec.quantity_per_assembly < 0 then
432 raise fnd_api.g_exc_unexpected_error;
433 end if;
434
435 end if;
436
437 x_return_status := FND_API.G_RET_STS_SUCCESS;
438
439 exception
440 when others then
441
442 l_token_tbl(1).token_name := 'QUANTITY_PER_ASSEMBLY';
443 l_token_tbl(1).token_value := p_eam_direct_items_rec.quantity_per_assembly;
444
445 l_out_mesg_token_tbl := l_mesg_token_tbl;
446 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
447 ( p_message_name => 'EAM_DI_QTY_PER_ASSY_INVALID'
448 , p_token_tbl => l_token_tbl
449 , p_mesg_token_tbl => l_mesg_token_tbl
450 , x_mesg_token_tbl => l_out_mesg_token_tbl
451 );
452 l_mesg_token_tbl := l_out_mesg_token_tbl;
453
454 x_return_status := FND_API.G_RET_STS_ERROR;
455 x_mesg_token_tbl := l_mesg_token_tbl ;
456 return;
457
458 end;
459
460
461 -- wip_supply_type
462 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_supply_type . . . '); END IF;
463
464 begin
465
466 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
467
468
469 if(p_eam_direct_items_rec.wip_supply_type is not null and p_eam_direct_items_rec.wip_supply_type not in (wip_constants.push, wip_constants.bulk, wip_constants.based_on_bom)) then
470 --not a valid supply type
471
472 raise fnd_api.g_exc_unexpected_error;
473
474 end if;
475
476 end if;
477
478 x_return_status := FND_API.G_RET_STS_SUCCESS;
479
480 exception
481 when others then
482
483 l_token_tbl(1).token_name := 'WIP_SUPPLY_TYPE';
484 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_supply_type;
485
486 l_out_mesg_token_tbl := l_mesg_token_tbl;
487 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
488 ( p_message_name => 'EAM_DI_SUPPLY_TYPE_INVALID'
489 , p_token_tbl => l_token_tbl
490 , p_mesg_token_tbl => l_mesg_token_tbl
491 , x_mesg_token_tbl => l_out_mesg_token_tbl
492 );
493 l_mesg_token_tbl := l_out_mesg_token_tbl;
494
495 x_return_status := FND_API.G_RET_STS_ERROR;
496 x_mesg_token_tbl := l_mesg_token_tbl ;
497 return;
498
499 end;
500
501
502 -- mrp_net_flag
503 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating mrp_net_flag . . . '); END IF;
504
505 begin
506
507 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
508
509 if p_eam_direct_items_rec.mrp_net_flag not in (wip_constants.yes, wip_constants.no) then
510 raise fnd_api.g_exc_unexpected_error;
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 := 'MRP_NET_FLAG';
521 l_token_tbl(1).token_value := p_eam_direct_items_rec.mrp_net_flag;
522
523 l_out_mesg_token_tbl := l_mesg_token_tbl;
524 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
525 ( p_message_name => 'EAM_DI_MRP_NET_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
540 -- delete material_requirement
541 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_requirement . . . '); END IF;
542
543 declare
544 l_count_mmt NUMBER :=0;
545 l_count_mmtt NUMBER :=0;
546 l_issued_qty NUMBER :=0;
547 begin
548
549 if (p_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
550
551 select count(*)
552 into l_count_mmtt
553 from mtl_material_transactions_temp
554 where transaction_source_id = p_eam_direct_items_rec.wip_entity_id
555 and organization_id = p_eam_direct_items_rec.organization_id
556 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
557 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
558
559 if(l_count_mmtt > 0) then
560 raise fnd_api.g_exc_unexpected_error;
561 end if;
562
563 select count(*)
564 into l_count_mmt
565 from mtl_material_transactions
566 where transaction_source_id = p_eam_direct_items_rec.wip_entity_id
567 and organization_id = p_eam_direct_items_rec.organization_id
568 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
569 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
570
571 if(l_count_mmt > 0) then
572 raise fnd_api.g_exc_unexpected_error;
573 end if;
574
575 select quantity_issued
576 into l_issued_qty
577 from wip_requirement_operations
578 where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
579 and organization_id = p_eam_direct_items_rec.organization_id
580 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
581 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
582
583 if(l_issued_qty <> 0) then
584 raise fnd_api.g_exc_unexpected_error;
585 end if;
586
587 end if;
588
589 x_return_status := FND_API.G_RET_STS_SUCCESS;
590
591 exception
592 when others then
593
594 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
595 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_entity_id;
596
597 l_out_mesg_token_tbl := l_mesg_token_tbl;
598 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
599 ( p_message_name => 'EAM_DI_DELETE_INVALID'
600 , p_token_tbl => l_token_tbl
601 , p_mesg_token_tbl => l_mesg_token_tbl
602 , x_mesg_token_tbl => l_out_mesg_token_tbl
603 );
604 l_mesg_token_tbl := l_out_mesg_token_tbl;
605
606 x_return_status := FND_API.G_RET_STS_ERROR;
607 x_mesg_token_tbl := l_mesg_token_tbl ;
608 return;
609
610 end;
611
612
613
614 -- Required Quantity
615 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating required_quantity . . . '); END IF;
616
617 begin
618 if p_eam_direct_items_rec.required_quantity < p_eam_direct_items_rec.quantity_issued then
619 raise fnd_api.g_exc_unexpected_error;
620 end if;
621
622 x_return_status := FND_API.G_RET_STS_SUCCESS;
623
624 exception
625 when others then
626
627 l_out_mesg_token_tbl := l_mesg_token_tbl;
628 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
629 ( p_message_name => 'EAM_DI_REQ_QTY_INVALID'
630 , p_token_tbl => l_token_tbl
631 , p_mesg_token_tbl => l_mesg_token_tbl
632 , x_mesg_token_tbl => l_out_mesg_token_tbl
633 );
634 l_mesg_token_tbl := l_out_mesg_token_tbl;
635
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 x_mesg_token_tbl := l_mesg_token_tbl ;
638 return;
639
640 end;
641
642
643
644 -- delete material_requirement
645 */
646
647
648 EXCEPTION
649 WHEN OTHERS THEN
650
651 l_token_tbl(1).token_name := 'Validation (Check Attributes)';
652 l_token_tbl(1).token_value := substrb(SQLERRM,1,200);
653
654 l_out_mesg_token_tbl := l_mesg_token_tbl;
655 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
656 ( p_message_name => NULL
657 , p_token_tbl => l_token_tbl
658 , p_mesg_token_tbl => l_mesg_token_tbl
659 , x_mesg_token_tbl => l_out_mesg_token_tbl
660 ) ;
661 l_mesg_token_tbl := l_out_mesg_token_tbl;
662
663 -- Return the status and message table.
664 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
665 x_mesg_token_tbl := l_mesg_token_tbl ;
666
667
668 END Check_Attributes;
669
670 /*********************************************************************
671 * Procedure : Check_Required
672 * Parameters IN : Direct Items column record
673 * Parameters OUT NOCOPY: Mesg Token Table
674 * Return_Status
675 * Purpose :
676 **********************************************************************/
677
678 PROCEDURE Check_Required
679 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
680 , x_return_status OUT NOCOPY VARCHAR2
681 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
682 )
683 IS
684 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
685 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
686 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
687 BEGIN
688
689 x_return_status := FND_API.G_RET_STS_SUCCESS;
690
691
692
693 IF p_eam_direct_items_rec.wip_entity_id IS NULL
694 THEN
695 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
696 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
697
698 l_out_mesg_token_tbl := l_mesg_token_tbl;
699 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
700 ( p_message_name => 'EAM_DI_ENTITY_ID_REQUIRED'
701 , p_token_tbl => l_Token_tbl
702 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
703 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
704 );
705 l_mesg_token_tbl := l_out_mesg_token_tbl;
706
707 x_return_status := FND_API.G_RET_STS_ERROR;
708
709 END IF;
710
711
712 IF p_eam_direct_items_rec.organization_id IS NULL
713 THEN
714 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
715 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
716
717 l_out_mesg_token_tbl := l_mesg_token_tbl;
718 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
719 ( p_message_name => 'EAM_DI_ORG_REQUIRED'
720 , p_token_tbl => l_Token_tbl
721 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
722 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
723 );
724 l_mesg_token_tbl := l_out_mesg_token_tbl;
725
726 x_return_status := FND_API.G_RET_STS_ERROR;
727
728 END IF;
729
730
731 IF p_eam_direct_items_rec.direct_item_sequence_id IS NULL
732 THEN
733 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
734 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
735
736 l_out_mesg_token_tbl := l_mesg_token_tbl;
737 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
738 ( p_message_name => 'EAM_DI_INV_ITEM_REQUIRED'
739 , p_token_tbl => l_Token_tbl
740 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
741 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
742 );
743 l_mesg_token_tbl := l_out_mesg_token_tbl;
744
745 x_return_status := FND_API.G_RET_STS_ERROR;
746
747 END IF;
748
749
750 IF p_eam_direct_items_rec.operation_seq_num IS NULL
751 THEN
752 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
753 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
754
755 l_out_mesg_token_tbl := l_mesg_token_tbl;
756 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
757 ( p_message_name => 'EAM_DI_OP_SEQ_REQUIRED'
758 , p_token_tbl => l_Token_tbl
759 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
760 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
761 );
762 l_mesg_token_tbl := l_out_mesg_token_tbl;
763
764 x_return_status := FND_API.G_RET_STS_ERROR;
765
766 END IF;
767
768 IF p_eam_direct_items_rec.description IS NULL
769 THEN
770 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
771 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
772
773 l_out_mesg_token_tbl := l_mesg_token_tbl;
774 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
775 ( p_message_name => 'EAM_DI_DESC_REQUIRED'
776 , p_token_tbl => l_Token_tbl
777 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
778 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
779 );
780 l_mesg_token_tbl := l_out_mesg_token_tbl;
781
782 x_return_status := FND_API.G_RET_STS_ERROR;
783
784 END IF;
785
786
787 IF p_eam_direct_items_rec.purchasing_category_id IS NULL
788 THEN
789 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
790 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
791
792 l_out_mesg_token_tbl := l_mesg_token_tbl;
793 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
794 ( p_message_name => 'EAM_DI_PURCH_CAT_REQUIRED'
795 , p_token_tbl => l_Token_tbl
796 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
797 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
798 );
799 l_mesg_token_tbl := l_out_mesg_token_tbl;
800
801 x_return_status := FND_API.G_RET_STS_ERROR;
802
803 END IF;
804
805
806 IF p_eam_direct_items_rec.required_quantity IS NULL
807 THEN
808 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
809 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
810
811 l_out_mesg_token_tbl := l_mesg_token_tbl;
812 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
813 ( p_message_name => 'EAM_DI_REQ_QTY_REQUIRED'
814 , p_token_tbl => l_Token_tbl
815 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
816 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
817 );
818 l_mesg_token_tbl := l_out_mesg_token_tbl;
819
820 x_return_status := FND_API.G_RET_STS_ERROR;
821
822 END IF;
823
824
825 IF p_eam_direct_items_rec.uom IS NULL
826 THEN
827 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
828 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
829
830 l_out_mesg_token_tbl := l_mesg_token_tbl;
831 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
832 ( p_message_name => 'EAM_DI_UOM_REQUIRED'
833 , p_token_tbl => l_Token_tbl
834 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
835 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
836 );
837 l_mesg_token_tbl := l_out_mesg_token_tbl;
838
839 x_return_status := FND_API.G_RET_STS_ERROR;
840
841 END IF;
842
843
844 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
845
846 END Check_Required;
847
848 END EAM_DIRECT_ITEMS_VALIDATE_PVT;