[Home] [Help]
PACKAGE BODY: APPS.EAM_OP_VALIDATE_PVT
Source
1 PACKAGE BODY EAM_OP_VALIDATE_PVT AS
2 /* $Header: EAMVOPVB.pls 120.5 2007/12/13 06:14:06 rnandyal ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVOPVB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_OP_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_OP_VALIDATE_PVT';
24
25 g_token_tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
26 g_shutdown_type VARCHAR2(30) := EAM_CONSTANTS.G_SHUTDOWN_TYPE;
27
28 /*******************************************************************
29 * Procedure : Check_Existence
30 * Returns : None
31 * Parameters IN : Operation Record
32 * Parameters OUT NOCOPY: Old Operation 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_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
48 , x_old_eam_op_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_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 Operations'); END IF;
60
61 EAM_OP_UTILITY_PVT.Query_Row
62 ( p_wip_entity_id => p_eam_op_rec.wip_entity_id
63 , p_organization_id => p_eam_op_rec.organization_id
64 , p_operation_seq_num => p_eam_op_rec.operation_seq_num
65 , x_eam_op_rec => x_old_eam_op_rec
66 , x_Return_status => l_return_status
67 );
68
69 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Query Row Returned with : ' || l_return_status); END IF;
70
71 IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND AND
72 p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
73 THEN
74 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
75 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
76
77 l_out_mesg_token_tbl := l_mesg_token_tbl;
78 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
79 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
80 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
81 , p_message_name => 'EAM_OP_ALREADY_EXISTS'
82 , p_token_tbl => l_token_tbl
83 );
84 l_mesg_token_tbl := l_out_mesg_token_tbl;
85
86 l_return_status := FND_API.G_RET_STS_ERROR;
87
88 ELSIF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND AND
89 p_eam_op_rec.transaction_type IN
90 (EAM_PROCESS_WO_PVT.G_OPR_UPDATE, EAM_PROCESS_WO_PVT.G_OPR_DELETE)
91 THEN
92 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
93 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
94
95 l_out_mesg_token_tbl := l_mesg_token_tbl;
96 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
97 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
98 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
99 , p_message_name => 'EAM_OP_DOESNOT_EXISTS'
100 , p_token_tbl => l_token_tbl
101 );
102 l_mesg_token_tbl := l_out_mesg_token_tbl;
103
104 l_return_status := FND_API.G_RET_STS_ERROR;
105
106 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
107 THEN
108 l_out_mesg_token_tbl := l_mesg_token_tbl;
109 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
110 ( x_Mesg_token_tbl => l_out_Mesg_Token_Tbl
111 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
112 , p_message_name => NULL
113 , p_message_text => 'Unexpected error while existence verification of ' || 'Operation '|| p_eam_op_rec.operation_seq_num , p_token_tbl => l_token_tbl
114 );
115 l_mesg_token_tbl := l_out_mesg_token_tbl;
116 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
117
118 ELSE /* Assign the relevant transaction type for SYNC operations */
119 IF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_SYNC THEN
120 IF l_return_status = EAM_PROCESS_WO_PVT.G_RECORD_FOUND THEN
121 x_old_eam_op_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_UPDATE;
122 ELSE
123 x_old_eam_op_rec.transaction_type := EAM_PROCESS_WO_PVT.G_OPR_CREATE;
124 END IF;
125 END IF;
126 l_return_status := FND_API.G_RET_STS_SUCCESS;
127
128 END IF;
129
130 x_return_status := l_return_status;
131 x_mesg_token_tbl := l_mesg_token_tbl;
132 END Check_Existence;
133
134
135 /********************************************************************
136 * Procedure : Is_Dept_Updateable
137 * Parameters IN : Wip_Entity_Id and Operation_Seq_Num
138 * Parameters OUT NOCOPY: Return Status
139 * Mesg Token Table
140 * Purpose : Is_Dept_Updateable will check if the operation's
141 * department can be updateable or not
142 **********************************************************************/
143 FUNCTION Is_Dept_Updateable
144 ( p_wip_entity_id NUMBER,
145 p_organization_id NUMBER,
146 p_operation_seq_num NUMBER
147 ) RETURN BOOLEAN
148 IS
149 l_inv_count NUMBER;
150 l_direct_count NUMBER;
151 l_res_count NUMBER;
152 l_po_count NUMBER;
153 l_req_count NUMBER;
154 l_dist_count NUMBER;
155 BEGIN
156
157 --initialise variables
158 l_inv_count := 0;
159 l_direct_count := 0;
160 l_res_count := 0;
161 l_po_count := 0;
162 l_req_count := 0;
163 l_dist_count := 0;
164
165 --check if any stocked/non-stocked inventory items exist for this op
166 select count(*)
167 into l_inv_count
168 from dual
169 where exists (select 1
170 from wip_requirement_operations wro
171 where wro.wip_entity_id = p_wip_entity_id
172 and wro.organization_id = p_organization_id
173 and wro.operation_seq_num = p_operation_seq_num
174 and ( quantity_issued > 0 or
175 EAM_MATERIAL_ALLOCQTY_PKG.allocated_quantity(
176 wro.wip_entity_id,
177 wro.organization_id,
178 wro.operation_seq_num,
179 wro.inventory_item_id ) >0 )
180 );
181
182 IF(l_inv_count > 0) THEN
183 RETURN FALSE;
184 END IF;
185
186 --check if any resources exist for this op
187 select count(*)
188 into l_res_count
189 from dual
190 where exists (select 1
191 from wip_operation_resources
192 where wip_entity_id = p_wip_entity_id
193 and organization_id = p_organization_id
194 and operation_seq_num = p_operation_seq_num);
195
196 IF(l_res_count > 0) THEN
197 RETURN FALSE;
198 END IF;
199
200 --check if any pending requisitions exist for this op
201 /* select count(*)
202 into l_po_count
203 from DUAL
204 WHERE EXISTS (SELECT 1
205 FROM po_requisitions_interface
206 where wip_entity_id = p_wip_entity_id
207 and destination_organization_id = p_organization_id
208 and wip_operation_seq_num = p_operation_seq_num);
209
210 if(l_po_count > 0) then
211 RETURN FALSE;
212 end if; */
213
214
215 --check if any requisitions exist for this op
216 select count(*)
217 into l_req_count
218 from DUAL
219 WHERE EXISTS (SELECT 1
220 FROM po_requisition_lines prl, po_requisition_headers prh
221 where prl.requisition_header_id = prh.requisition_header_id
222 and prl.wip_entity_id = p_wip_entity_id
223 and prl.destination_organization_id = p_organization_id
224 and prl.wip_operation_seq_num = p_operation_seq_num
225 and (prh.authorization_status <>'CANCELLED'
226 or prh.authorization_status is null)
227 and (prl.cancel_flag <>'Y' or prl.cancel_flag is null)
228 and (prl.closed_code not in ('FINALLY CLOSED')
229 or prl.closed_code is null)
230 and (prl.modified_by_agent_flag <> 'Y'
231 or prl.modified_by_agent_flag is null));
232
233
234 if(l_req_count > 0) then
235 RETURN FALSE;
236 end if;
237
238
239 --check if any purchase orders exist for this op
240 select count(*)
241 into l_dist_count
242 from DUAL
243 WHERE EXISTS (SELECT 1
244 FROM po_distributions pds,po_line_locations poll
245 where pds.line_location_id = poll.line_location_id
246 and pds.wip_entity_id = p_wip_entity_id
247 and pds.destination_organization_id = p_organization_id
248 and pds.wip_operation_seq_num = p_operation_seq_num
249 and (poll.cancel_flag <>'Y' or poll.cancel_flag is null)
250 and (poll.closed_code not in ('CANCELLED','FINALLY CLOSED')
251 or poll.closed_code is null));
252
253 IF(l_dist_count > 0) THEN
254 RETURN FALSE;
255 END IF;
256
257 --dept is updateable
258 RETURN TRUE ;
259
260 END Is_Dept_Updateable;
261
262
263
264
265 /********************************************************************
266 * Procedure : Check_Attributes
267 * Parameters IN : Operation Column record
268 * Old Operation Column record
269 * Parameters OUT NOCOPY: Return Status
270 * Mesg Token Table
271 * Purpose : Check_Attrbibutes procedure will validate every
272 * revised item attrbiute in its entirety.
273 **********************************************************************/
274
275 PROCEDURE Check_Attributes
276 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
277 , p_old_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
278 , x_return_status OUT NOCOPY VARCHAR2
279 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
280 )
281 IS
282 l_err_text VARCHAR2(2000) := NULL;
283 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
284 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
285 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
286 g_dummy NUMBER;
287 l_mat_count NUMBER;
288 l_di_count NUMBER;
289 l_wedi_count NUMBER;
290 l_po_count NUMBER;
291 l_req_count NUMBER;
292 OP_DEPT_NOT_UPDATEABLE EXCEPTION;
293
294 BEGIN
295
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Within WO Check Attributes . . . '); END IF;
299
300
301 -- department_id
302 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating department_id . . . '); END IF;
303
304 begin
305
306 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
307
308 select 1
309 into g_dummy
310 from bom_departments
311 where department_id = p_eam_op_rec.department_id
312 and organization_id = p_eam_op_rec.organization_id;
313
314 if (p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE and
315 (p_eam_op_rec.department_id <> p_old_eam_op_rec.department_id)
316 and ((Is_Dept_Updateable(p_wip_entity_id => p_eam_op_rec.wip_entity_id
317 , p_organization_id => p_eam_op_rec.organization_id
318 , p_operation_seq_num => p_eam_op_rec.operation_seq_num ))
319 in (FALSE) and is_op_dept_change_allowed(p_eam_op_rec.wip_entity_id,p_eam_op_rec.operation_seq_num)='N')
320 ) THEN
321 raise OP_DEPT_NOT_UPDATEABLE;
322 end if; --end of check for update and is_dept_updateable
323 end if; --end of check for create/update transaction
324
325 x_return_status := FND_API.G_RET_STS_SUCCESS;
326
327 exception
328 WHEN OP_DEPT_NOT_UPDATEABLE THEN
329 l_token_tbl(1).token_name := 'DEPT_NAME';
330
331 SELECT bd.department_code into l_token_tbl(1).token_value
332 FROM bom_departments bd
333 WHERE bd.DEPARTMENT_ID = p_eam_op_rec.department_id
334 AND bd.organization_id = p_eam_op_rec.organization_id;
335
336
337 l_out_mesg_token_tbl := l_mesg_token_tbl;
338 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
339 ( p_message_name => 'EAM_OP_DEPT_UPDATE'
340 , p_token_tbl => l_token_tbl
341 , p_mesg_token_tbl => l_mesg_token_tbl
342 , x_mesg_token_tbl => l_out_mesg_token_tbl
343 );
344 l_mesg_token_tbl := l_out_mesg_token_tbl;
345
346 x_return_status := FND_API.G_RET_STS_ERROR;
347 x_mesg_token_tbl := l_mesg_token_tbl ;
348 return;
349
350 when others then
351
352 l_token_tbl(1).token_name := 'DEPARTMENT_NAME';
353
354 SELECT bd.department_code into l_token_tbl(1).token_value
355 FROM bom_departments bd
356 WHERE bd.DEPARTMENT_ID = p_eam_op_rec.department_id
357 AND bd.organization_id = p_eam_op_rec.organization_id;
358
359
360 l_out_mesg_token_tbl := l_mesg_token_tbl;
361 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
362 ( p_message_name => 'EAM_OP_DEPT_INVALID'
363 , p_token_tbl => l_token_tbl
364 , p_mesg_token_tbl => l_mesg_token_tbl
365 , x_mesg_token_tbl => l_out_mesg_token_tbl
366 );
367 l_mesg_token_tbl := l_out_mesg_token_tbl;
368
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 x_mesg_token_tbl := l_mesg_token_tbl ;
371 return;
372
373 end;
374
375 -- standard_operation_id
376 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating standard_operation_id . . . '); END IF;
377
378 begin
379
380 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
381
382 if (p_eam_op_rec.standard_operation_id is not null) then
383
384 select 1
385 into g_dummy
386 from bom_standard_operations
387 where standard_operation_id = p_eam_op_rec.standard_operation_id
388 and organization_id = p_eam_op_rec.organization_id;
389 end if;
390
391 end if;
392
393 x_return_status := FND_API.G_RET_STS_SUCCESS;
394
395 exception
396 when others then
397
398 l_token_tbl(1).token_name := 'STANDARD_OPERATION';
399 l_token_tbl(1).token_value := p_eam_op_rec.standard_operation_id;
400
401 l_out_mesg_token_tbl := l_mesg_token_tbl;
402 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
403 ( p_message_name => 'EAM_OP_STD_OP_INVALID'
404 , p_token_tbl => l_token_tbl
405 , p_mesg_token_tbl => l_mesg_token_tbl
406 , x_mesg_token_tbl => l_out_mesg_token_tbl
407 );
408 l_mesg_token_tbl := l_out_mesg_token_tbl;
409
410 x_return_status := FND_API.G_RET_STS_ERROR;
411 x_mesg_token_tbl := l_mesg_token_tbl ;
412 return;
413
414 end;
415
416 -- minimum_transfer_quantity
417 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating minimum_transfer_quantity . . . '); END IF;
418
419 begin
420
421 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
422
423 if p_eam_op_rec.minimum_transfer_quantity < 0 then
424 raise fnd_api.g_exc_unexpected_error;
425 end if;
426
427 end if;
428
429 x_return_status := FND_API.G_RET_STS_SUCCESS;
430
431 exception
432 when others then
433
434 l_token_tbl(1).token_name := 'MINIMUM_TRANSFER_QUANTITY';
435 l_token_tbl(1).token_value := p_eam_op_rec.minimum_transfer_quantity;
436
437 l_out_mesg_token_tbl := l_mesg_token_tbl;
438 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
439 ( p_message_name => 'EAM_OP_MIN_TRS_QTY_INVALID'
440 , p_token_tbl => l_token_tbl
441 , p_mesg_token_tbl => l_mesg_token_tbl
442 , x_mesg_token_tbl => l_out_mesg_token_tbl
443 );
444 l_mesg_token_tbl := l_out_mesg_token_tbl;
445
446 x_return_status := FND_API.G_RET_STS_ERROR;
447 x_mesg_token_tbl := l_mesg_token_tbl ;
448 return;
449
450 end;
451
452 -- count_point_type
453 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating count_point_type . . . '); END IF;
454
455 begin
456
457 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
458
459 select 1
460 into g_dummy
461 from mfg_lookups
462 where lookup_type = 'BOM_COUNT_POINT_TYPE'
463 and lookup_code = p_eam_op_rec.count_point_type;
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 := 'COUNT_POINT_TYPE';
473 l_token_tbl(1).token_value := p_eam_op_rec.count_point_type;
474
475 l_out_mesg_token_tbl := l_mesg_token_tbl;
476 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
477 ( p_message_name => 'EAM_OP_COUNT_POINT_INVALID'
478 , p_token_tbl => l_token_tbl
479 , p_mesg_token_tbl => l_mesg_token_tbl
480 , x_mesg_token_tbl => l_out_mesg_token_tbl
481 );
482 l_mesg_token_tbl := l_out_mesg_token_tbl;
483
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 x_mesg_token_tbl := l_mesg_token_tbl ;
486 return;
487
488 end;
489
490 -- backflush_flag
491 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating backflush_flag . . . '); END IF;
492
493 begin
494
495 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
496
497 select 1
498 into g_dummy
499 from mfg_lookups
500 where lookup_type = 'SYS_YES_NO'
501 and lookup_code = p_eam_op_rec.backflush_flag;
502
503 end if;
504
505 x_return_status := FND_API.G_RET_STS_SUCCESS;
506
507 exception
508 when others then
509
510 l_token_tbl(1).token_name := 'BACKFLUSH_FLAG';
511 l_token_tbl(1).token_value := p_eam_op_rec.backflush_flag;
512
513 l_out_mesg_token_tbl := l_mesg_token_tbl;
514 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
515 ( p_message_name => 'EAM_OP_BACKFLUSH_INVALID'
516 , p_token_tbl => l_token_tbl
517 , p_mesg_token_tbl => l_mesg_token_tbl
518 , x_mesg_token_tbl => l_out_mesg_token_tbl
519 );
520 l_mesg_token_tbl := l_out_mesg_token_tbl;
521
522 x_return_status := FND_API.G_RET_STS_ERROR;
523 x_mesg_token_tbl := l_mesg_token_tbl ;
524 return;
525
526 end;
527
528
529 -- shutdown_type
530 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating shutdown_type . . . '); END IF;
531
532 begin
533
534 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
535
536 if p_eam_op_rec.shutdown_type is not null then
537 select 1
538 into g_dummy
539 from mfg_lookups
540 where lookup_type = g_shutdown_type
541 and lookup_code = p_eam_op_rec.shutdown_type
542 and enabled_flag = 'Y';
543 end if;
544
545 end if;
546
547 x_return_status := FND_API.G_RET_STS_SUCCESS;
548
549 exception
550 when others then
551
552 l_token_tbl(1).token_name := 'SHUTDOWN_TYPE';
553 l_token_tbl(1).token_value := p_eam_op_rec.shutdown_type;
554
555 l_out_mesg_token_tbl := l_mesg_token_tbl;
556 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
557 ( p_message_name => 'EAM_OP_SHUTDOWN_INVALID'
558 , p_token_tbl => l_token_tbl
559 , p_mesg_token_tbl => l_mesg_token_tbl
560 , x_mesg_token_tbl => l_out_mesg_token_tbl
561 );
562 l_mesg_token_tbl := l_out_mesg_token_tbl;
563
564 x_return_status := FND_API.G_RET_STS_ERROR;
565 x_mesg_token_tbl := l_mesg_token_tbl ;
566 return;
567
568 end;
569
570 -- start_date
571 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating start_date . . . '); END IF;
572
573 begin
574
575 if (p_eam_op_rec.transaction_type in (EAM_PROCESS_WO_PVT.G_OPR_CREATE, EAM_PROCESS_WO_PVT.G_OPR_UPDATE)) then
576
577 if p_eam_op_rec.start_date > p_eam_op_rec.completion_date then
578 raise fnd_api.g_exc_unexpected_error;
579 end if;
580
581 end if;
582
583 x_return_status := FND_API.G_RET_STS_SUCCESS;
584
585 exception
586 when others then
587
588 l_token_tbl(1).token_name := 'START_DATE';
589 l_token_tbl(1).token_value := p_eam_op_rec.start_date;
590
591 l_out_mesg_token_tbl := l_mesg_token_tbl;
592 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
593 ( p_message_name => 'EAM_OP_START_DATE_INVALID'
594 , p_token_tbl => l_token_tbl
595 , p_mesg_token_tbl => l_mesg_token_tbl
596 , x_mesg_token_tbl => l_out_mesg_token_tbl
597 );
598 l_mesg_token_tbl := l_out_mesg_token_tbl;
599
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 x_mesg_token_tbl := l_mesg_token_tbl ;
602 return;
603
604 end;
605
606
607 -- delete operation
608 IF EAM_PROCESS_WO_PVT.GET_DEBUG = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Validating operation . . . '); END IF;
609
610 declare
611 l_count_eoct NUMBER :=0;
612 l_count_res NUMBER :=0;
613 l_count_on NUMBER :=0;
614 l_count_mr NUMBER :=0;
615 begin
616
617 if (p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE) then
618
619 select count(*)
620 into l_count_eoct
621 from eam_op_completion_txns
622 where wip_entity_id = p_eam_op_rec.wip_entity_id
623 and organization_id = p_eam_op_rec.organization_id
624 and operation_seq_num = p_eam_op_rec.operation_seq_num;
625
626 if(l_count_eoct > 0) then
627 raise fnd_api.g_exc_unexpected_error;
628 end if;
629
630 select count(*)
631 into l_count_res
632 from wip_operation_resources
633 where wip_entity_id = p_eam_op_rec.wip_entity_id
634 and organization_id = p_eam_op_rec.organization_id
635 and operation_seq_num = p_eam_op_rec.operation_seq_num;
636
637 if(l_count_res > 0) then
638 raise fnd_api.g_exc_unexpected_error;
639 end if;
640
641 select count(*)
642 into l_count_on
643 from wip_operation_networks
644 where wip_entity_id = p_eam_op_rec.wip_entity_id
645 and organization_id = p_eam_op_rec.organization_id
646 and ( prior_operation = p_eam_op_rec.operation_seq_num
647 or next_operation = p_eam_op_rec.operation_seq_num);
648
649 if(l_count_on > 0) then
650 raise fnd_api.g_exc_unexpected_error;
651 end if;
652
653
654 select count(*)
655 into l_count_mr
656 from wip_requirement_operations
657 where wip_entity_id = p_eam_op_rec.wip_entity_id
658 and organization_id = p_eam_op_rec.organization_id
659 and operation_seq_num = p_eam_op_rec.operation_seq_num;
660
661 if(l_count_mr <> 0) then
662 raise fnd_api.g_exc_unexpected_error;
663 end if;
664
665 end if;
666
667 x_return_status := FND_API.G_RET_STS_SUCCESS;
668
669 exception
670 when others then
671
672 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
673 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
674
675 l_out_mesg_token_tbl := l_mesg_token_tbl;
676 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
677 ( p_message_name => 'EAM_OP_DELETE_INVALID'
678 , p_token_tbl => l_token_tbl
679 , p_mesg_token_tbl => l_mesg_token_tbl
680 , x_mesg_token_tbl => l_out_mesg_token_tbl
681 );
682 l_mesg_token_tbl := l_out_mesg_token_tbl;
683
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 x_mesg_token_tbl := l_mesg_token_tbl ;
686 return;
687
688 end;
689
690
691
692 EXCEPTION
693 WHEN OTHERS THEN
694
695 l_token_tbl(1).token_name := 'Validation (Check Attributes)';
696 l_token_tbl(1).token_value := substrb(SQLERRM,1,200);
697
698 l_out_mesg_token_tbl := l_mesg_token_tbl;
699 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
700 ( p_message_name => NULL
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 -- Return the status and message table.
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
709 x_mesg_token_tbl := l_mesg_token_tbl ;
710
711
712 END Check_Attributes;
713
714 /*********************************************************************
715 * Procedure : Check_Required
716 * Parameters IN : Operation column record
717 * Parameters OUT NOCOPY: Mesg Token Table
718 * Return_Status
719 * Purpose :
720 **********************************************************************/
721
722 PROCEDURE Check_Required
723 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
724 , x_return_status OUT NOCOPY VARCHAR2
725 , x_Mesg_Token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
726 )
727 IS
728 l_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
729 l_out_Mesg_Token_Tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
730 l_Token_Tbl EAM_ERROR_MESSAGE_PVT.Token_Tbl_Type;
731 BEGIN
732
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734
735 IF p_eam_op_rec.wip_entity_id IS NULL
736 THEN
737 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
738 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
739
740 l_out_mesg_token_tbl := l_mesg_token_tbl;
741 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
742 ( p_message_name => 'EAM_OP_ENTITY_ID_REQUIRED'
743 , p_token_tbl => l_Token_tbl
744 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
745 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
746 );
747 l_mesg_token_tbl := l_out_mesg_token_tbl;
748
749 x_return_status := FND_API.G_RET_STS_ERROR;
750
751 END IF;
752
753 IF p_eam_op_rec.operation_seq_num IS NULL
754 THEN
755 --l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
756 --l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
757 l_token_tbl.delete;
758
759 l_out_mesg_token_tbl := l_mesg_token_tbl;
760 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
761 ( p_message_name => 'EAM_OP_OP_SEQ_REQUIRED'
762 , p_token_tbl => l_Token_tbl
763 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
764 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
765 );
766 l_mesg_token_tbl := l_out_mesg_token_tbl;
767
768 x_return_status := FND_API.G_RET_STS_ERROR;
769
770 END IF;
771
772 IF p_eam_op_rec.organization_id IS NULL
773 THEN
774 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
775 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
776
777 l_out_mesg_token_tbl := l_mesg_token_tbl;
778 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
779 ( p_message_name => 'EAM_OP_ORG_REQUIRED'
780 , p_token_tbl => l_Token_tbl
781 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
782 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
783 );
784 l_mesg_token_tbl := l_out_mesg_token_tbl;
785
786 x_return_status := FND_API.G_RET_STS_ERROR;
787
788 END IF;
789
790 IF p_eam_op_rec.department_id IS NULL
791 THEN
792 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
793 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
794
795 l_out_mesg_token_tbl := l_mesg_token_tbl;
796 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
797 ( p_message_name => 'EAM_OP_DEPT_REQUIRED'
798 , p_token_tbl => l_Token_tbl
799 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
800 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
801 );
802 l_mesg_token_tbl := l_out_mesg_token_tbl;
803
804 x_return_status := FND_API.G_RET_STS_ERROR;
805
806 END IF;
807
808 IF p_eam_op_rec.start_date IS NULL
809 THEN
810 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
811 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
812
813 l_out_mesg_token_tbl := l_mesg_token_tbl;
814 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
815 ( p_message_name => 'EAM_OP_START_DATE_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 IF p_eam_op_rec.completion_date IS NULL
827 THEN
828 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
829 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
830
831 l_out_mesg_token_tbl := l_mesg_token_tbl;
832 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
833 ( p_message_name => 'EAM_OP_COMPL_DATE_REQUIRED'
834 , p_token_tbl => l_Token_tbl
835 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
836 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
837 );
838 l_mesg_token_tbl := l_out_mesg_token_tbl;
839
840 x_return_status := FND_API.G_RET_STS_ERROR;
841
842 END IF;
843
844 IF p_eam_op_rec.count_point_type IS NULL
845 THEN
846 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
847 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
848
849 l_out_mesg_token_tbl := l_mesg_token_tbl;
850 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
851 ( p_message_name => 'EAM_OP_COUNT_POINT_REQUIRED'
852 , p_token_tbl => l_Token_tbl
853 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
854 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
855 );
856 l_mesg_token_tbl := l_out_mesg_token_tbl;
857
858 x_return_status := FND_API.G_RET_STS_ERROR;
859
860 END IF;
861
862 IF p_eam_op_rec.backflush_flag IS NULL
863 THEN
864 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
865 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
866
867 l_out_mesg_token_tbl := l_mesg_token_tbl;
868 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
869 ( p_message_name => 'EAM_OP_BACKFLUSH_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 IF p_eam_op_rec.minimum_transfer_quantity IS NULL
881 THEN
882 l_token_tbl(1).token_name := 'OPERATION_SEQ_NUM';
883 l_token_tbl(1).token_value := p_eam_op_rec.operation_seq_num;
884
885 l_out_mesg_token_tbl := l_mesg_token_tbl;
886 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
887 ( p_message_name => 'EAM_OP_MIN_TRANS_QTY_REQUIRED'
888 , p_token_tbl => l_Token_tbl
889 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
890 , x_Mesg_Token_Tbl => l_out_Mesg_Token_Tbl
891 );
892 l_mesg_token_tbl := l_out_mesg_token_tbl;
893
894 x_return_status := FND_API.G_RET_STS_ERROR;
895
896 END IF;
897
898
899 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
900
901 END Check_Required;
902
903 -- This procedure will check that after operation updatates,the depdendency in the operation depdendency network is valid
904 -- If the depdencdency fails then ,it throws an error
905
906 PROCEDURE Check_Operation_Netwrok_Dates
907 (
908 p_api_version IN NUMBER,
909 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
910 p_commit IN VARCHAR2 := FND_API.G_FALSE,
911 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
912
913 p_wip_entity_id IN NUMBER,
914
915 x_return_status OUT NOCOPY VARCHAR2,
916 x_pri_operation_no OUT NOCOPY NUMBER,
917 x_next_operation_no OUT NOCOPY NUMBER
918 ) IS
919
920 CURSOR l_op_network(l_wip_entity_id NUMBER) IS
921 SELECT
922 won.prior_operation,won.next_operation,wo.last_unit_completion_date,wo1.first_unit_start_date
923 FROM wip_operation_networks won,wip_operations wo,wip_operations wo1
924 WHERE won.wip_entity_id = wo.wip_entity_id AND
925 won.wip_entity_id = wo1.wip_entity_id AND
926 won.prior_operation = wo.operation_seq_num AND
927 won.next_operation = wo1.operation_seq_num AND
928 won.wip_entity_id = l_wip_entity_id;
929
930 BEGIN
931
932 FOR l_opeation IN l_op_network (p_wip_entity_id)
933 LOOP
934 If l_opeation.last_unit_completion_date > l_opeation.first_unit_start_date THEN
935 x_return_status := FND_API.G_RET_STS_ERROR;
936 x_pri_operation_no := l_opeation.prior_operation;
937 x_next_operation_no := l_opeation.next_operation;
938 RETURN ;
939 End if;
940 END LOOP;
941
942 x_return_status := FND_API.G_RET_STS_SUCCESS;
943
944 EXCEPTION
945 when others then
946 x_return_status := FND_API.G_RET_STS_ERROR;
947 return;
948
949 END Check_Operation_Netwrok_Dates;
950
951 /*********************************************************************
952 * Procedure : is_op_dept_change_allowed
953 * Parameters IN : Wip entity id operation sequence number
954 * Parameters OUT NOCOPY: Valication Flag
955 * Purpose :
956 **********************************************************************/
957
958
959 FUNCTION is_op_dept_change_allowed(p_wip_entity_id NUMBER, p_op_seq_num NUMBER) RETURN VARCHAR2 IS l_op_completed VARCHAR2(1);
960 l_q_issued NUMBER;
961 l_q_received NUMBER;
962 l_amount_delivered NUMBER;
963 l_return VARCHAR2(1);
964 l_tx_count NUMBER;
965 BEGIN
966
967 --1.Is Op Completed
968
969 SELECT nvl(operation_completed, 'N')
970 INTO l_op_completed
971 FROM wip_operations
972 WHERE wip_entity_id = p_wip_entity_id
973 AND operation_seq_num = p_op_seq_num;
974
975 IF l_op_completed = 'Y' THEN
976 RETURN 'N';
977 END IF;
978
979 --2.Is Materail Tx Done
980 --WIP_REQUIREMENT_OPERATIONS. quantity_issued > 0
981 BEGIN
982 SELECT nvl(sum(quantity_issued), 0)
983 INTO l_q_issued
984 FROM wip_requirement_operations
985 WHERE wip_entity_id = p_wip_entity_id
986 AND operation_seq_num = p_op_seq_num;
987
988 IF l_q_issued > 0 THEN
989 RETURN 'N';
990 END IF;
991
992 EXCEPTION
993 WHEN no_data_found THEN
994 l_return := 'Y';
995 END;
996 --3.Is non-stock material receipt Done
997 --4.Is Direct material receipt done
998 --5.IS Out side processing (OSP) receipt done
999 --EAM_WO_DIRECT_ITEMS_LITE_V. quantity_received > 0 or EAM_WO_DIRECT_ITEMS_LITE_V. amount_delivered > 0
1000 BEGIN
1001 SELECT nvl(sum(quantity_received), 0),
1002 nvl(sum(amount_delivered), 0)
1003 INTO l_q_received,
1004 l_amount_delivered
1005 FROM eam_wo_direct_items_lite_v
1006 WHERE wip_entity_id = p_wip_entity_id
1007 AND operation_seq_num = p_op_seq_num;
1008
1009 IF l_q_received > 0 OR l_amount_delivered > 0 THEN
1010 RETURN 'N';
1011 END IF;
1012
1013 EXCEPTION
1014 WHEN no_data_found THEN
1015 l_return := 'Y';
1016 END ;
1017 -- For checking resource transaction use wip_cost_txn_interface.
1018
1019 select count(*) into l_tx_count from dual
1020 where EXISTS (SELECT transaction_id FROM wip_cost_txn_interface
1021 WHERE wip_entity_id = p_wip_entity_id
1022 AND operation_seq_num = p_op_seq_num);
1023
1024 IF l_tx_count > 0 THEN
1025 RETURN 'N';
1026 END IF;
1027 l_return := 'Y';
1028
1029
1030 RETURN l_return;
1031
1032 EXCEPTION
1033 WHEN no_data_found THEN
1034 RETURN 'Y';
1035 END is_op_dept_change_allowed;
1036
1037 /*********************************************************************
1038 * Procedure : is_wo_dept_change_allowed
1039 * Parameters IN : Wip entity id
1040 * Parameters OUT NOCOPY: Valication Flag
1041 * Purpose :
1042 **********************************************************************/
1043
1044 FUNCTION is_wo_dept_change_allowed(x_wip_entity_id NUMBER) RETURN VARCHAR2 IS l_status NUMBER;
1045 BEGIN
1046
1047 SELECT user_defined_status_id
1048 INTO l_status
1049 FROM eam_work_order_details
1050 WHERE wip_entity_id = x_wip_entity_id;
1051 --Is WO status in Draft(17), Un Released(1), Released(3), On Hold(6)
1052
1053 IF l_status = 17 or l_status = 1 or l_status = 3 or l_status = 6 THEN
1054 RETURN 'Y';
1055 END IF;
1056
1057 RETURN 'N';
1058 END is_wo_dept_change_allowed;
1059
1060 /*********************************************************************
1061 * Procedure : validate_dept_res
1062 * Parameters IN : Department ID Resource ID
1063 * Parameters OUT NOCOPY: Valication Flag
1064 * Purpose :
1065 **********************************************************************/
1066
1067
1068 function validate_dept_res(p_dept_id number , p_res_code varchar2) return varchar2 is
1069 l_rowcount number := 0;
1070 begin
1071 select count(*) into l_rowcount from BOM_DEPARTMENT_RESOURCES_V where DEPARTMENT_ID=p_dept_id and RESOURCE_CODE=p_res_code;
1072 if(l_rowcount > 0) then
1073 return 'Y';
1074 end if;
1075 return 'N';
1076 end validate_dept_res;
1077 /*********************************************************************
1078 * Procedure : validate_dept_res_instance
1079 * Parameters IN : Department Id Instance ID Resource ID
1080 * Parameters OUT NOCOPY: Valication Flag
1081 * Purpose :
1082 **********************************************************************/
1083 function validate_dept_res_instance(p_dept_id number , p_inst_id number, p_res_id Number) return varchar2 is
1084 l_rowcount number := 0;
1085 begin
1086 select count(*) into l_rowcount from dual
1087 where EXISTS (select ROW_ID from BOM_DEPT_RES_INSTANCES_EMP_V
1088 where DEPARTMENT_ID=p_dept_id and INSTANCE_ID=p_inst_id and RESOURCE_ID=p_res_id);
1089 if(l_rowcount > 0) then
1090 return 'N';
1091 end if;
1092 return 'Y';
1093 end;
1094
1095
1096
1097 END EAM_OP_VALIDATE_PVT;