[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.4 2010/10/26 00:58:43 mashah ship $ */
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) #6118897 7509781*/
250 sum(Decode(upper(NVL(rqh.authorization_status, 'APPROVED')), 'CANCELLED', 0, 'REJECTED', 0, 'SYSTEM_SAVED',0,rql.quantity)) quantity
251 FROM
252 wip_eam_direct_items wed, po_requisition_lines_all rql, po_requisition_headers_all rqh
253 WHERE
254 wed.wip_entity_id = rql.wip_entity_id (+)
255 AND wed.organization_id = rql.destination_organization_id (+)
256 AND wed.operation_seq_num = rql.wip_operation_seq_num (+)
257 AND rql.requisition_header_id = rqh.requisition_header_id(+)
258 /*AND upper(NVL(rqh.authorization_status, 'APPROVED') ) not in ('CANCELLED', 'REJECTED','SYSTEM_SAVED') #6118897 7509781*/
259 AND rql.item_id is null
260 AND (wed.direct_item_sequence_id = rql.wip_resource_seq_num OR rql.wip_resource_seq_num is null )
261 AND wed.description = rql.item_description(+)
262 GROUP BY wed.wip_entity_id, wed.operation_seq_num, wed.organization_id,
263 wed.direct_item_sequence_id, wed.description
264 )
265 wed,
266 ( SELECT
267 pd1.wip_entity_id,
268 pd1.wip_operation_seq_num,
269 pd1.destination_organization_id,
270 pol.item_description,
271 pd1.wip_resource_seq_num,
272 pd1.quantity_ordered,
273 pol.item_id,
274 pol.cancel_flag
275 FROM
276 po_lines_all pol,
277 po_distributions_all pd1
278 WHERE
279 pol.po_line_id = pd1.po_line_id
280 AND upper(nvl(pol.cancel_flag,'N')) <> 'Y' ) pd /* #7509781*/
281 WHERE
282 wed.wip_entity_id = pd.wip_entity_id(+)
283 AND wed.organization_id = pd.destination_organization_id (+)
284 AND wed.operation_seq_num = pd.wip_operation_seq_num(+)
285 /*AND upper(nvl(pd.cancel_flag,'N')) <> 'Y' 7509781 */
286 AND pd.item_id is null
287 AND wed.direct_item_sequence_id = pd.wip_resource_seq_num(+) /* #7509781 */
288 AND wed.description = pd.item_description(+)
289 GROUP BY
290 wed.wip_entity_id,
291 wed.operation_seq_num,
292 wed.organization_id,
293 wed.direct_item_sequence_id,
294 wed.description,
295 wed.quantity
296 ) ewodi
297 WHERE ewodi.wip_entity_id= p_eam_direct_items_rec.wip_entity_id
298 AND ewodi.operation_seq_num=p_eam_direct_items_rec.operation_seq_num
299 AND ewodi.organization_id =p_eam_direct_items_rec.organization_id
300 AND ewodi.direct_item_sequence_id=p_eam_direct_items_rec.direct_item_sequence_id;
301
302
303
304
305
306
307
308 IF(p_eam_direct_items_rec.transaction_type =EAM_PROCESS_WO_PVT.G_OPR_DELETE) AND
309 (l_ordered_quantity > 0) THEN
310 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
311 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_entity_id;
312
313 l_out_mesg_token_tbl := l_mesg_token_tbl;
314 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
315 ( p_message_name => 'EAM_DI_DELETE_INVALID'
316 , p_token_tbl => l_token_tbl
317 , p_mesg_token_tbl => l_mesg_token_tbl
318 , x_mesg_token_tbl => l_out_mesg_token_tbl
319 );
320 l_mesg_token_tbl := l_out_mesg_token_tbl;
321 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Cannot delete direct item . . . '); END IF;
322
323 x_return_status := EAM_ERROR_MESSAGE_PVT.G_STATUS_ERROR;
324 x_mesg_token_tbl := l_mesg_token_tbl ;
325 return;
326 END IF;
327 END IF;
328 --end of fix for 3352406
329 /*
330
331 -- operation_seq_num
332
333 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating operation_seq_num . . . '); END IF;
334
335 begin
336
337 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
338
339 if p_eam_direct_items_rec.operation_seq_num = 1 then
340 select 1
341 into g_dummy
342 from dual
343 where p_eam_direct_items_rec.operation_seq_num = 1
344 and not exists (select 1 from wip_operations
345 where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
346 and organization_id = p_eam_direct_items_rec.organization_id);
347 else
348 select 1
349 into g_dummy
350 from wip_operations wo
351 where wo.organization_id = p_eam_direct_items_rec.organization_id
352 and wo.wip_entity_id = p_eam_direct_items_rec.wip_entity_id
353 and wo.operation_seq_num = p_eam_direct_items_rec.operation_seq_num;
354 end if;
355
356 end if;
357
358 x_return_status := FND_API.G_RET_STS_SUCCESS;
359
360 exception
361 when others then
362
363 l_token_tbl(1).token_name := 'OP_SEQ_NUM';
364 l_token_tbl(1).token_value := p_eam_direct_items_rec.operation_seq_num;
365
366 l_out_mesg_token_tbl := l_mesg_token_tbl;
367 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
368 ( p_message_name => 'EAM_DI_OP_SEQ_INVALID'
369 , p_token_tbl => l_token_tbl
370 , p_mesg_token_tbl => l_mesg_token_tbl
371 , x_mesg_token_tbl => l_out_mesg_token_tbl
372 );
373 l_mesg_token_tbl := l_out_mesg_token_tbl;
374
375 x_return_status := FND_API.G_RET_STS_ERROR;
376 x_mesg_token_tbl := l_mesg_token_tbl ;
377 return;
378
379 end;
380
381
382 -- direct_item_sequence_id
383 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating direct_item_sequence_id . . . '); END IF;
384
385 begin
386
387 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
388
389 -- Fix for bug# 3602041 - Removed check for bom_enabled flag in the where clause of the query
390 select 1
391 into g_dummy
392 from mtl_system_items
393 where direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id
394 and organization_id = p_eam_direct_items_rec.organization_id
395 and ( bom_item_type = 4
396 and ( eam_item_type IS NULL or eam_item_type = 3) );
397
398 -- Check added so that assets and activities are not included
399
400
401 end if;
402
403 x_return_status := FND_API.G_RET_STS_SUCCESS;
404
405 exception
406 when others then
407
408 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
409 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
410
411 l_out_mesg_token_tbl := l_mesg_token_tbl;
412 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
413 ( p_message_name => 'EAM_DI_INV_ITEM_INVALID'
414 , p_token_tbl => l_token_tbl
415 , p_mesg_token_tbl => l_mesg_token_tbl
416 , x_mesg_token_tbl => l_out_mesg_token_tbl
417 );
418 l_mesg_token_tbl := l_out_mesg_token_tbl;
419
420 x_return_status := FND_API.G_RET_STS_ERROR;
421 x_mesg_token_tbl := l_mesg_token_tbl ;
422 return;
423
424 end;
425
426 -- quantity_per_assembly
427 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating quantity_per_assembly . . . '); END IF;
428
429 begin
430
431 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
432
433 if p_eam_direct_items_rec.quantity_per_assembly < 0 then
434 raise fnd_api.g_exc_unexpected_error;
435 end if;
436
437 end if;
438
439 x_return_status := FND_API.G_RET_STS_SUCCESS;
440
441 exception
442 when others then
443
444 l_token_tbl(1).token_name := 'QUANTITY_PER_ASSEMBLY';
445 l_token_tbl(1).token_value := p_eam_direct_items_rec.quantity_per_assembly;
446
447 l_out_mesg_token_tbl := l_mesg_token_tbl;
448 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
449 ( p_message_name => 'EAM_DI_QTY_PER_ASSY_INVALID'
450 , p_token_tbl => l_token_tbl
451 , p_mesg_token_tbl => l_mesg_token_tbl
452 , x_mesg_token_tbl => l_out_mesg_token_tbl
453 );
454 l_mesg_token_tbl := l_out_mesg_token_tbl;
455
456 x_return_status := FND_API.G_RET_STS_ERROR;
457 x_mesg_token_tbl := l_mesg_token_tbl ;
458 return;
459
460 end;
461
462
463 -- wip_supply_type
464 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating wip_supply_type . . . '); END IF;
465
466 begin
467
468 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
469
470
471 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
472 --not a valid supply type
473
474 raise fnd_api.g_exc_unexpected_error;
475
476 end if;
477
478 end if;
479
480 x_return_status := FND_API.G_RET_STS_SUCCESS;
481
482 exception
483 when others then
484
485 l_token_tbl(1).token_name := 'WIP_SUPPLY_TYPE';
486 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_supply_type;
487
488 l_out_mesg_token_tbl := l_mesg_token_tbl;
489 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
490 ( p_message_name => 'EAM_DI_SUPPLY_TYPE_INVALID'
491 , p_token_tbl => l_token_tbl
492 , p_mesg_token_tbl => l_mesg_token_tbl
493 , x_mesg_token_tbl => l_out_mesg_token_tbl
494 );
495 l_mesg_token_tbl := l_out_mesg_token_tbl;
496
497 x_return_status := FND_API.G_RET_STS_ERROR;
498 x_mesg_token_tbl := l_mesg_token_tbl ;
499 return;
500
501 end;
502
503
504 -- mrp_net_flag
505 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating mrp_net_flag . . . '); END IF;
506
507 begin
508
509 if (p_eam_direct_items_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
510
511 if p_eam_direct_items_rec.mrp_net_flag not in (wip_constants.yes, wip_constants.no) then
512 raise fnd_api.g_exc_unexpected_error;
513 end if;
514
515 end if;
516
517 x_return_status := FND_API.G_RET_STS_SUCCESS;
518
519 exception
520 when others then
521
522 l_token_tbl(1).token_name := 'MRP_NET_FLAG';
523 l_token_tbl(1).token_value := p_eam_direct_items_rec.mrp_net_flag;
524
525 l_out_mesg_token_tbl := l_mesg_token_tbl;
526 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
527 ( p_message_name => 'EAM_DI_MRP_NET_INVALID'
528 , p_token_tbl => l_token_tbl
529 , p_mesg_token_tbl => l_mesg_token_tbl
530 , x_mesg_token_tbl => l_out_mesg_token_tbl
531 );
532 l_mesg_token_tbl := l_out_mesg_token_tbl;
533
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 x_mesg_token_tbl := l_mesg_token_tbl ;
536 return;
537
538 end;
539
540
541
542 -- delete material_requirement
543 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating material_requirement . . . '); END IF;
544
545 declare
546 l_count_mmt NUMBER :=0;
547 l_count_mmtt NUMBER :=0;
548 l_issued_qty NUMBER :=0;
549 begin
550
551 if (p_eam_direct_items_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
552
553 select count(*)
554 into l_count_mmtt
555 from mtl_material_transactions_temp
556 where transaction_source_id = p_eam_direct_items_rec.wip_entity_id
557 and organization_id = p_eam_direct_items_rec.organization_id
558 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
559 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
560
561 if(l_count_mmtt > 0) then
562 raise fnd_api.g_exc_unexpected_error;
563 end if;
564
565 select count(*)
566 into l_count_mmt
567 from mtl_material_transactions
568 where transaction_source_id = p_eam_direct_items_rec.wip_entity_id
569 and organization_id = p_eam_direct_items_rec.organization_id
570 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
571 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
572
573 if(l_count_mmt > 0) then
574 raise fnd_api.g_exc_unexpected_error;
575 end if;
576
577 select quantity_issued
578 into l_issued_qty
579 from wip_requirement_operations
580 where wip_entity_id = p_eam_direct_items_rec.wip_entity_id
581 and organization_id = p_eam_direct_items_rec.organization_id
582 and operation_seq_num = p_eam_direct_items_rec.operation_seq_num
583 and direct_item_sequence_id = p_eam_direct_items_rec.direct_item_sequence_id;
584
585 if(l_issued_qty <> 0) then
586 raise fnd_api.g_exc_unexpected_error;
587 end if;
588
589 end if;
590
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592
593 exception
594 when others then
595
596 l_token_tbl(1).token_name := 'WIP_ENTITY_ID';
597 l_token_tbl(1).token_value := p_eam_direct_items_rec.wip_entity_id;
598
599 l_out_mesg_token_tbl := l_mesg_token_tbl;
600 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
601 ( p_message_name => 'EAM_DI_DELETE_INVALID'
602 , p_token_tbl => l_token_tbl
603 , p_mesg_token_tbl => l_mesg_token_tbl
604 , x_mesg_token_tbl => l_out_mesg_token_tbl
605 );
606 l_mesg_token_tbl := l_out_mesg_token_tbl;
607
608 x_return_status := FND_API.G_RET_STS_ERROR;
609 x_mesg_token_tbl := l_mesg_token_tbl ;
610 return;
611
612 end;
613
614
615
616 -- Required Quantity
617 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating required_quantity . . . '); END IF;
618
619 begin
620 if p_eam_direct_items_rec.required_quantity < p_eam_direct_items_rec.quantity_issued then
621 raise fnd_api.g_exc_unexpected_error;
622 end if;
623
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 exception
627 when others then
628
629 l_out_mesg_token_tbl := l_mesg_token_tbl;
630 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
631 ( p_message_name => 'EAM_DI_REQ_QTY_INVALID'
632 , p_token_tbl => l_token_tbl
633 , p_mesg_token_tbl => l_mesg_token_tbl
634 , x_mesg_token_tbl => l_out_mesg_token_tbl
635 );
636 l_mesg_token_tbl := l_out_mesg_token_tbl;
637
638 x_return_status := FND_API.G_RET_STS_ERROR;
639 x_mesg_token_tbl := l_mesg_token_tbl ;
640 return;
641
642 end;
643
644
645
646 -- delete material_requirement
647 */
648
649
650 EXCEPTION
651 WHEN OTHERS THEN
652
653 l_token_tbl(1).token_name := 'Validation (Check Attributes)';
654 l_token_tbl(1).token_value := substrb(SQLERRM,1,200);
655
656 l_out_mesg_token_tbl := l_mesg_token_tbl;
657 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
658 ( p_message_name => NULL
659 , p_token_tbl => l_token_tbl
660 , p_mesg_token_tbl => l_mesg_token_tbl
661 , x_mesg_token_tbl => l_out_mesg_token_tbl
662 ) ;
663 l_mesg_token_tbl := l_out_mesg_token_tbl;
664
665 -- Return the status and message table.
666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
667 x_mesg_token_tbl := l_mesg_token_tbl ;
668
669
670 END Check_Attributes;
671
672 /*********************************************************************
673 * Procedure : Check_Required
674 * Parameters IN : Direct Items column record
675 * Parameters OUT NOCOPY: Mesg Token Table
676 * Return_Status
677 * Purpose :
678 **********************************************************************/
679
680 PROCEDURE Check_Required
681 ( p_eam_direct_items_rec IN EAM_PROCESS_WO_PUB.eam_direct_items_rec_type
682 , x_return_status OUT NOCOPY VARCHAR2
683 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
684 )
685 IS
686 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
687 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
688 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
689 BEGIN
690
691 x_return_status := FND_API.G_RET_STS_SUCCESS;
692
693
694
695 IF p_eam_direct_items_rec.wip_entity_id IS NULL
696 THEN
697 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
698 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
699
700 l_out_mesg_token_tbl := l_mesg_token_tbl;
701 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
702 ( p_message_name => 'EAM_DI_ENTITY_ID_REQUIRED'
703 , p_token_tbl => l_Token_tbl
704 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
705 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
706 );
707 l_mesg_token_tbl := l_out_mesg_token_tbl;
708
709 x_return_status := FND_API.G_RET_STS_ERROR;
710
711 END IF;
712
713
714 IF p_eam_direct_items_rec.organization_id IS NULL
715 THEN
716 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
717 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
718
719 l_out_mesg_token_tbl := l_mesg_token_tbl;
720 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
721 ( p_message_name => 'EAM_DI_ORG_REQUIRED'
722 , p_token_tbl => l_Token_tbl
723 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
724 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
725 );
726 l_mesg_token_tbl := l_out_mesg_token_tbl;
727
728 x_return_status := FND_API.G_RET_STS_ERROR;
729
730 END IF;
731
732
733 IF p_eam_direct_items_rec.direct_item_sequence_id IS NULL
734 THEN
735 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
736 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
737
738 l_out_mesg_token_tbl := l_mesg_token_tbl;
739 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
740 ( p_message_name => 'EAM_DI_INV_ITEM_REQUIRED'
741 , p_token_tbl => l_Token_tbl
742 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
743 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
744 );
745 l_mesg_token_tbl := l_out_mesg_token_tbl;
746
747 x_return_status := FND_API.G_RET_STS_ERROR;
748
749 END IF;
750
751
752 IF p_eam_direct_items_rec.operation_seq_num IS NULL
753 THEN
754 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
755 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
756
757 l_out_mesg_token_tbl := l_mesg_token_tbl;
758 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
759 ( p_message_name => 'EAM_DI_OP_SEQ_REQUIRED'
760 , p_token_tbl => l_Token_tbl
761 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
762 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
763 );
764 l_mesg_token_tbl := l_out_mesg_token_tbl;
765
766 x_return_status := FND_API.G_RET_STS_ERROR;
767
768 END IF;
769
770 IF p_eam_direct_items_rec.description IS NULL
771 THEN
772 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
773 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
774
775 l_out_mesg_token_tbl := l_mesg_token_tbl;
776 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
777 ( p_message_name => 'EAM_DI_DESC_REQUIRED'
778 , p_token_tbl => l_Token_tbl
779 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
780 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
781 );
782 l_mesg_token_tbl := l_out_mesg_token_tbl;
783
784 x_return_status := FND_API.G_RET_STS_ERROR;
785
786 END IF;
787
788
789 IF p_eam_direct_items_rec.purchasing_category_id IS NULL
790 THEN
791 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
792 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
793
794 l_out_mesg_token_tbl := l_mesg_token_tbl;
795 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
796 ( p_message_name => 'EAM_DI_PURCH_CAT_REQUIRED'
797 , p_token_tbl => l_Token_tbl
798 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
799 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
800 );
801 l_mesg_token_tbl := l_out_mesg_token_tbl;
802
803 x_return_status := FND_API.G_RET_STS_ERROR;
804
805 END IF;
806
807
808 IF p_eam_direct_items_rec.required_quantity IS NULL
809 THEN
810 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
811 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
812
813 l_out_mesg_token_tbl := l_mesg_token_tbl;
814 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
815 ( p_message_name => 'EAM_DI_REQ_QTY_REQUIRED'
816 , p_token_tbl => l_Token_tbl
817 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
818 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
819 );
820 l_mesg_token_tbl := l_out_mesg_token_tbl;
821
822 x_return_status := FND_API.G_RET_STS_ERROR;
823
824 END IF;
825
826
827 IF p_eam_direct_items_rec.uom IS NULL
828 THEN
829 l_token_tbl(1).token_name := 'DIRECT_ITEM_SEQUENCE_ID';
830 l_token_tbl(1).token_value := p_eam_direct_items_rec.direct_item_sequence_id;
831
832 l_out_mesg_token_tbl := l_mesg_token_tbl;
833 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
834 ( p_message_name => 'EAM_DI_UOM_REQUIRED'
835 , p_token_tbl => l_Token_tbl
836 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
837 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
838 );
839 l_mesg_token_tbl := l_out_mesg_token_tbl;
840
841 x_return_status := FND_API.G_RET_STS_ERROR;
842
843 END IF;
844
845
846 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
847
848 END Check_Required;
849
850 END EAM_DIRECT_ITEMS_VALIDATE_PVT;