[Home] [Help]
PACKAGE BODY: APPS.ENG_VALIDATE_SUB_COMPONENT
Source
1 PACKAGE BODY ENG_Validate_Sub_Component AS
2 /* $Header: ENGLSBCB.pls 115.23 2002/12/12 18:52:43 akumar ship $ */
3
4 -- +------------------------------------------+
5 -- | Global constant holding the package name |
6 -- +------------------------------------------+
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Validate_Sub_Component';
9 G_RET_CODE NUMBER;
10
11 /*********************** Entity **********************************************/
12
13 PROCEDURE CHECK_REQUIRED( x_return_status OUT NOCOPY VARCHAR2
14 , p_sub_component_rec IN
15 Bom_Bo_Pub.Sub_Component_Rec_Type
16 , x_Mesg_Token_tbl OUT NOCOPY
17 Error_Handler.Mesg_Token_Tbl_Type
18 )
19 IS
20 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
21 BEGIN
22 IF p_sub_component_rec.Revised_Item_Name IS NULL OR
23 p_sub_component_rec.Revised_Item_Name = FND_API.G_MISS_CHAR OR
24 p_sub_component_rec.Start_Effective_Date IS NULL OR
25 p_sub_component_rec.Start_Effective_Date = FND_API.G_MISS_DATE OR
26 p_sub_component_rec.New_Revised_Item_Revision IS NULL OR
27 p_sub_component_rec.New_Revised_Item_Revision = FND_API.G_MISS_CHAR OR
28 p_sub_component_rec.Operation_Sequence_Number IS NULL OR
29 p_sub_component_rec.Operation_Sequence_Number = FND_API.G_MISS_NUM OR
30 p_sub_component_rec.Component_Item_Name IS NULL OR
31 p_sub_component_rec.Component_Item_Name = FND_API.G_MISS_CHAR OR
32 p_sub_component_rec.Alternate_BOM_Code IS NULL OR
33 p_sub_component_rec.Alternate_BOM_Code = FND_API.G_MISS_CHAR OR
34 p_sub_component_rec.Substitute_Component_Name IS NULL OR
35 p_sub_component_rec.Substitute_Component_Name = FND_API.G_MISS_CHAR OR
36 p_sub_component_rec.acd_type IS NULL OR
37 p_sub_component_rec.acd_type = FND_API.G_MISS_NUM
38 THEN
39 x_return_status := FND_API.G_RET_STS_ERROR;
40 Error_Handler.Add_Error_Token
41 ( x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
42 , p_Message_Name => 'ENG_SUB_COMP_REQ'
43 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
44 );
45 -- Log an error indicating that one of the required columns is
46 -- missing with scope of 'R'
47 RETURN;
48 END IF;
49
50 x_return_status := FND_API.G_RET_STS_SUCCESS;
51
52 END CHECK_REQUIRED;
53
54 /*******************************************************************
55 * Procedure : Entity
56 * Parameter IN : Substitute Component Record
57 * Substitute component Record of Unexposed Columns
58 * Parameter OUT : Return_Status - Indicating status of the process.
59 * Mesg_Token_Tbl - Table of Errors and their tokens
60 *
61 * Purpose : Entity procedure will validate the entity record by
62 * verfying the business logic for Substitute Components
63 *
64 *********************************************************************/
65 PROCEDURE Check_Entity
66 ( x_return_status OUT NOCOPY VARCHAR2
67 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
68 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
69 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
70 )
71 IS
72 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
73 l_sub_comp_unique VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
74 l_dummy NUMBER :=0;
75 l_dummy2 NUMBER :=0;
76 l_parent_acd_type NUMBER :=0;
77 l_parent_BIT NUMBER;
78 l_rec_BSI NUMBER;
79 l_rec_AST NUMBER;
80 l_rec_BIT NUMBER;
81 l_rec_AII NUMBER;
82 l_rec_CII NUMBER;
83 l_rec_ID DATE;
84 l_processed BOOLEAN;
85 l_err_text VARCHAR2(200);
86 stmt_num NUMBER := 0;
87 l_token_tbl Error_Handler.Token_Tbl_Type;
88 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
89 l_sbc_item_type NUMBER;
90 l_sbc_bom_enabled_flag VARCHAR2(1);
91 l_sbc_eng_item_flag VARCHAR2(1);
92 BEGIN
93
94 BEGIN
95 ------------------------------------------------------------------
96 -- Select Bill_Sequence_Id, Assembly_Type, Assembly_Item_Id, |
97 -- ACD_TYPE, Component_Item_Id and Implementation_Date |
98 -- from bom_inventory_component and bom_bill_of_materials tables |
99 ------------------------------------------------------------------
100
101 SELECT bbom.bill_sequence_id ,
102 bbom.assembly_type,
103 bbom.assembly_item_id,
104 bic.ACD_TYPE,
105 bic.bom_item_type,
106 bic.component_item_id,
107 bic.implementation_date,
108 msi.bom_item_type
109 INTO l_rec_BSI,
110 l_rec_AST,
111 l_rec_AII,
112 l_parent_acd_type,
113 l_rec_BIT,
114 l_rec_CII,
115 l_rec_ID,
116 l_parent_BIT
117 FROM mtl_system_items msi,
118 bom_inventory_components bic,
119 bom_bill_of_materials bbom
120 WHERE msi.inventory_item_id = bbom.assembly_item_id
121 AND msi.organization_id = bbom.organization_id
122 AND bic.component_sequence_id =
123 p_Sub_Comp_Unexp_Rec.component_sequence_id
124 AND bic.bill_sequence_id = bbom.bill_sequence_id;
125 EXCEPTION
126 WHEN OTHERS THEN
127 NULL;
128 END;
129 --dbms_output.put_line('Bill SequenceId : ' || to_char(l_rec_BSI));
130 --dbms_output.put_line('Assembly Type : ' || to_char(l_rec_AST));
131 --dbms_output.put_line('Comp SequenceId : ' || to_char(p_Sub_Comp_Unexp_Rec.component_sequence_id));
132
133
134 ---------------------------------------------------------------------
135 -- If the Transaction Type is CREATE and the ACD_type is ADD |
136 -- then check the type of item to which a sub component is being |
137 -- added. Planning bills cannot have sub comps esgs and also |
138 -- components which are not Standard cannot have sub comps. This |
139 -- OR so even if either exists sub comp cannot be added. |
140 ---------------------------------------------------------------------
141 IF p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_CREATE THEN
142
143 BEGIN
144 --dbms_output.put_line('Checking for planning bill or non-std. component . . . ');
145
146 IF l_rec_bit IN (1, 2, 3)
147 THEN
148 l_err_text := 'ENG_SBC_NON_STD_PARENT';
149 l_return_status := FND_API.G_RET_STS_ERROR;
150 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
151 --dbms_output.put_line
152 -- ('Expected Error. non-standard component . . . ');
153
154 Error_Handler.Add_Error_Token
155 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
156 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
157 , p_message_name => l_err_text
158 );
159 END IF;
160
161 END IF;
162
163 IF l_parent_bit = 3 THEN
164 l_Err_text := 'ENG_SBC_PLANNING_BILL';
165 l_return_status := FND_API.G_RET_STS_ERROR;
166
167 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
168 --dbms_output.put_line
169 -- ('Expected Error. planning parent. . . ');
170
171 Error_Handler.Add_Error_Token
172 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
173 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
174 , p_message_name => l_err_text
175 );
176 END IF;
177 END IF;
178
179 -----------------------------------------------------------------
180 -- If a record is found, then log an error because of the above |
181 -- mentioned comment. |
182 -----------------------------------------------------------------
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 NULL; -- Do nothing
187 WHEN OTHERS THEN
188 --dbms_output.put_line('Unexpected error in Checking Planning Item
189 -- ' || SQLERRM);
190 Error_Handler.Add_Error_Token
191 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
192 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
193 , p_message_name => NULL
194 , p_message_text => 'ERROR in Entity validation ' ||
195 substr(SQLERRM, 1, 240) || ' ' ||
196 to_char(SQLCODE)
197 );
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END;
200
201 END IF;
202
203 ------------------------------------------------------------------
204 -- When update an ECO that has (a process and Approval Status of |
205 -- 'Approval approved') we should change status to not submitted |
206 -- for Approval |
207 ------------------------------------------------------------------
208 stmt_num := 2;
209 IF (p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE OR
210 p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_CREATE)
211 THEN
212 Bom_Globals.Check_Approved_For_Process
213 ( p_change_notice => p_sub_component_rec.Eco_Name
214 , p_organization_id => p_Sub_Comp_Unexp_Rec.Organization_Id
215 , x_processed =>l_processed
216 , x_err_text =>l_err_text
217 );
218 IF (l_processed) THEN
219 Bom_Globals.Set_Request_For_Approval(
220 p_change_notice => p_sub_component_rec.Eco_Name
221 ,p_organization_id => p_Sub_Comp_Unexp_Rec.Organization_Id
222 ,x_err_text => l_err_text);
223 END IF;
224 END IF;
225
226 -----------------------------------------------------------------------
227 -- Check new substitute component item (for create or update) already |
228 -- exists in MTL_SYSTEM_ITEMS and has the correct item attributes |
229 -----------------------------------------------------------------------
230
231 stmt_num := 7;
232 BEGIN
233 --dbms_output.put_line
234 --('Verify sub. comp. exists in MTL_System_Item with correct attributes . . .');
235
236 SELECT bom_item_type, bom_enabled_flag, eng_item_flag
237 INTO l_sbc_item_type, l_sbc_bom_enabled_flag, l_sbc_eng_item_flag
238 FROM mtl_system_items
239 WHERE organization_id = p_Sub_Comp_Unexp_Rec.Organization_Id
240 AND inventory_item_id = p_Sub_Comp_Unexp_Rec.substitute_component_id;
241
242 IF l_sbc_item_type <> 4 THEN
243 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
244 Error_Handler.Add_Error_Token
245 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
246 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
247 , p_message_name => 'ENG_SUB_COMP_NOT_STD'
248 );
249 END IF;
250 l_return_status := FND_API.G_RET_STS_ERROR;
251 END IF;
252
253 IF l_sbc_bom_enabled_flag <> 'Y'
254 THEN
255 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
256 l_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
257 l_token_tbl(1).token_value :=
258 p_sub_component_rec.substitute_component_name;
259 Error_Handler.Add_Error_Token
260 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
261 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
262 , p_message_name => 'ENG_SUB_COMP_NOT_BOM_ENABLED'
263 , p_token_tbl => l_token_tbl
264 );
265 END IF;
266 l_return_status := FND_API.G_RET_STS_ERROR;
267 END IF;
268
269 IF ((l_rec_AST = 2) OR (l_rec_AST = 1 AND l_sbc_eng_item_flag = 'Y'))
270 THEN
271 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
272 Error_Handler.Add_Error_Token
273 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
274 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
275 , p_message_name => 'ENG_SUB_COMP_ASSEMBLY_MFG'
276 );
277 END IF;
278 l_return_status := FND_API.G_RET_STS_ERROR;
279 END IF;
280
281 EXCEPTION
282 WHEN no_data_found THEN
283 NULL;
284 END;
285
286 -------------------------------------------------------------------
287 -- If bill is a Common for other bills, then make sure Substitute |
288 -- Component Item exists in those orgs |
289 -------------------------------------------------------------------
290 stmt_num := 8;
291 BEGIN
292 --dbms_output.put_line('Verfying component for other common orgs . . . ');
293
294 SELECT 1
295 INTO l_dummy
296 FROM bom_bill_of_materials bbom
297 WHERE rownum =1
298 AND bbom.common_bill_sequence_id = l_rec_BSI
299 AND bbom.organization_id <> bbom.common_organization_id
300 AND not exists
301 (SELECT null
302 FROM mtl_system_items msi
303 WHERE msi.organization_id = bbom.organization_id
304 AND msi.inventory_item_id =
305 p_Sub_Comp_Unexp_Rec.substitute_component_id
306 AND msi.bom_enabled_flag = 'Y'
307 AND msi.bom_item_type = 4
308 AND (bbom.assembly_type = 2 OR
309 (bbom.assembly_type = 1 AND msi.eng_item_flag = 'N')));
310
311 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
312 l_token_tbl.DELETE;
313 l_Token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
314 l_token_tbl(1).token_value :=
315 p_sub_component_rec.substitute_component_name;
316 Error_Handler.Add_Error_Token
317 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
318 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
319 , p_message_name => 'ENG_SBC_COMBILL_DOES_NOT_EXIST'
320 , p_token_tbl => l_token_tbl
321 );
322 END IF;
323 l_return_status := FND_API.G_RET_STS_ERROR;
324 EXCEPTION
325 WHEN no_data_found THEN
326 null;
327 END;
328
329 ---------------------------------------------------------
330 -- Verify sub comp is not the same as bill or component |
331 ---------------------------------------------------------
332 stmt_num := 9;
333 --dbms_output.put_line('Verifying sub. comp not same as parent . . . ');
334
335 IF (p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE AND
336 p_Sub_Comp_Unexp_Rec.Component_Sequence_Id IN
337 (l_rec_AII, l_rec_CII))
338 OR
339 (p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_CREATE AND
340 p_Sub_Comp_Unexp_Rec.Substitute_Component_Id IN
341 (l_rec_AII,l_rec_CII))
342 THEN
343 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
344 Error_Handler.Add_Error_Token
345 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
346 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
347 , p_message_name => 'ENG_SUBCOMP_SAMEAS_ITEM_COMP'
348 , p_token_tbl => l_token_tbl
349 );
350 END IF;
351 l_return_status := FND_API.G_RET_STS_ERROR;
352 END IF;
353
354 ------------------------------------------
355 -- Validate attribute dependencies here.|
356 ------------------------------------------
357 -----------------------------------------
358 -- substitute quantity couldn't be zero |
359 -----------------------------------------
360
361 stmt_num := 10;
362 -----------------------------------------------------------------------
363 -- If parent component acd_type is disabled, we can't do anything for |
364 -- child component. |
365 -----------------------------------------------------------------------
366 stmt_num := 11;
367 IF l_parent_acd_type = 3 THEN
368 l_return_status := FND_API.G_RET_STS_ERROR;
369 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
370 Error_Handler.Add_Error_Token
371 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
372 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
373 , p_message_name => 'ENG_SBC_COMP_ACD_TYPE_DISABLE'
374 );
375 END IF;
376 END IF;
377
378 -------------------------------------------------------------------
379 -- if parent's acd_type is add, child acd_type should also be add |
380 -------------------------------------------------------------------
381 stmt_num := 12;
382 IF ((l_parent_acd_type = 1 ) and ( p_Sub_Component_Rec.ACD_Type <> 1))
383 THEN
384 l_return_status := FND_API.G_RET_STS_ERROR;
385 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
386 l_token_tbl(1).token_value := p_sub_component_rec.component_item_name;
387 Error_Handler.Add_Error_Token
388 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
389 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
390 , p_message_name => 'ENG_SBC_ACD_NOT_COMPATIBLE'
391 , p_token_tbl => l_token_tbl
392 );
393 END IF;
394
395 ------------------------------------------------------------------------
396 -- if acd_type is disable, sub comp must belong to revisd comp already |
397 -- if acd_type is not disable, sub comp must be unique for that |
398 -- revised component |
399 ------------------------------------------------------------------------
400 stmt_num := 13;
401 /*
402 IF p_sub_component_rec.acd_type <> 3
403 AND ( p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_CREATE OR
404 p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE
405 )
406 THEN
407
408
409 l_sub_comp_unique:= Verify_Unique_Substitute( p_sub_component_rec
410 , p_sub_comp_Unexp_rec
411 );
412
413 END IF;
414
415 */
416
417 stmt_num := 14;
418 -------------------------------------------------------------------------
419 -- if acd_type is disable, sub comp must belong to revised comp already |
420 -------------------------------------------------------------------------
421
422 IF p_sub_component_rec.acd_type = 3 THEN
423 BEGIN
424 --dbms_output.put_line('Verifying if component is not disable type . . . ');
425
426 SELECT 1
427 INTO l_dummy
428 FROM bom_substitute_components sub_comp,
429 bom_inventory_components rev_comp
430 WHERE sub_comp.component_sequence_id =
431 p_Sub_Comp_Unexp_Rec.Component_Sequence_Id
432 AND sub_comp.Substitute_Component_Id =
433 p_Sub_Comp_Unexp_Rec.Substitute_Component_Id
434 AND rev_comp.component_sequence_id =
435 p_Sub_Comp_Unexp_Rec.Component_Sequence_Id
436 AND rev_comp.implementation_date IS NOT NULL;
437 EXCEPTION
438 WHEN NO_DATA_FOUND THEN
439 If FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) Then
440 l_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
441 l_token_tbl(1).token_value :=
442 p_sub_component_rec.substitute_component_name;
443 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
444 l_token_tbl(2).token_value :=
445 p_sub_component_rec.component_item_name;
446 Error_Handler.Add_Error_Token
447 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
448 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
449 , p_message_name => 'ENG_DISABLE_SCOMP_NOT_FOUND'
450 , p_token_tbl => l_token_tbl
451 );
452 end if;
453 l_return_status := FND_API.G_RET_STS_ERROR;
454 END;
455 END IF;
456
457 ---dbms_output.put_line('l_return_status : '|| l_return_status);
458
459 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462
463 ----------------------------
464 -- Done validating entity |
465 ----------------------------
466
467 x_return_status := l_return_status;
468 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
469
470 EXCEPTION
471 WHEN FND_API.G_EXC_ERROR THEN
472 --dbms_output.put_line('expected Error : stmt_num -'
473 -- || to_char(stmt_num));
474 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
475 x_return_status := FND_API.G_RET_STS_ERROR;
476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
477 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
479 --dbms_output.put_line('unexpected Error : stmt_num -'
480 -- || to_char(stmt_num));
481 WHEN OTHERS THEN
482 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
483 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
484 --dbms_output.put_line('other unexpected Error :
485 -- stmt_num -' || to_char(stmt_num));
486 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
487 THEN
488 l_err_text := G_PKG_NAME ||
489 'Validation (Substitute Component Entity)' ||
490 SUBSTR(SQLERRM, 1, 100);
491 Error_Handler.Add_Error_Token( x_Mesg_Token_tbl => x_Mesg_Token_tbl
492 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
493 , p_message_name => NULL
494 , p_message_text => l_err_text
495 );
496
497 END IF;
498 END Check_Entity;
499
500
501 /*******************************************************************
502 * Procedure : Check_Attributes
503 * Parameter IN : Substitute Component Record
504 * Parameter OUT : Return_Status - Indicating status of the process.
505 * Mesg_Token_Tbl - Table of Errors and their tokens
506 *
507 * Purpose : Procedure Attributes will verify the validity of
508 * all exposed columns to check if the user has given
509 * values that the columns can actually hold.
510 *********************************************************************/
511
512 PROCEDURE Check_Attributes
513 ( x_return_status OUT NOCOPY VARCHAR2
514 , x_Mesg_Token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
515 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
516 )
517 IS
518 l_err_text VARCHAR2(255);
519 l_token_tbl Error_Handler.Token_Tbl_Type;
520 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
521 BEGIN
522
523 x_return_status := FND_API.G_RET_STS_SUCCESS;
524
525 /**************************************************************************
526 With patch to bug 728002, this is not valid now
527
528 IF p_sub_component_rec.substitute_item_quantity IS NOT NULL AND
529 p_sub_component_rec.substitute_item_quantity = 0
530 THEN
531 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
532 THEN
533 l_token_tbl(1).token_name := 'SUBSITUTE_ITEM_NAME';
534 l_token_tbl(1).token_value :=
535 p_sub_component_rec.substitute_component_name;
536 Error_Handler.Add_Error_Token
537 ( x_Mesg_Token_tbl => x_Mesg_Token_tbl
538 , p_message_name => 'ENG_SUB_COMP_QTY_ZERO'
539 , p_token_tbl => l_token_tbl
540 );
541 END IF;
542 x_return_status := FND_API.G_RET_STS_ERROR;
543
544 END IF;
545 **************************************************************************/
546
547
548 END Check_Attributes;
549
550 /*******************************************************************
551 * Procedure : Check_Entity_Delete
552 * Parameter IN : Substitute Component Record
553 * Substitute component Record of Unexposed Columns
554 * Parameter OUT : Return_Status - Indicating status of the process.
555 * Mesg_Token_Tbl - Table of Errors and their tokens
556 *
557 * Purpose : Entity_Delete procedure will verify if the record
558 * can be delete without violating any dependency rules
559 *********************************************************************/
560
561 PROCEDURE Check_Entity_Delete
562 ( x_return_status OUT NOCOPY VARCHAR2
563 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
564 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
565 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
566 )
567 IS
568 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
569 l_err_text VARCHAR2(255);
570 l_rec_ID DATE := NULL;
571 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
572 BEGIN
573
574 -- Validate entity delete.
575 x_return_status := l_return_status;
576
577 END Check_Entity_Delete;
578
579 /**************************************************************************
580 * Procedure : Check_Existence
581 * Parameters IN : Substitute Component exposed column record
582 * Substitute Component unexposed column record
583 * Parameters OUT: Old Substitute Component exposed column record
584 * Old substitute component unexposed column record
585 * Return status
586 * Mesg Token Table
587 * Purpose : This procedure will verify if the user given record exists
588 * when the operation is Update/Delete and does not exist when
589 * the operation is Create. If the operation is Update/Delete
590 * the procedure will query the existing record and return them
591 * as old records.
592 ***************************************************************************/
593 PROCEDURE Check_Existence
594 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
595 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
596 , x_old_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
597 , x_old_sub_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
598 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
599 , x_Return_Status OUT NOCOPY VARCHAR2
600 )
601 IS
602 l_token_tbl Error_Handler.Token_Tbl_Type;
603 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
604 l_return_status VARCHAR2(1);
605 BEGIN
606 l_Token_Tbl(1).Token_Name := 'SUBSTITUTE_ITEM_NAME';
607 l_Token_Tbl(1).Token_Value :=
608 p_sub_component_rec.substitute_component_name;
609 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
610 l_token_tbl(2).token_value :=
611 p_sub_component_rec.component_item_name;
612
613 ENG_Sub_Component_Util.Query_Row
614 ( p_substitute_component_id =>
615 p_sub_comp_unexp_rec.substitute_component_id
616 , p_component_sequence_id =>
617 p_sub_comp_unexp_rec.component_sequence_id
618 , p_acd_type =>
619 p_sub_component_rec.acd_type
620 , x_Sub_Component_Rec => x_old_sub_component_rec
621 , x_Sub_Comp_Unexp_Rec => x_old_sub_comp_unexp_rec
622 , x_return_status => l_return_status
623 );
624
625 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
626 p_sub_component_rec.transaction_type = Bom_Globals.G_OPR_CREATE
627 THEN
628 Error_Handler.Add_Error_Token
629 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
630 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
631 , p_message_name => 'ENG_SUB_COMP_ALREADY_EXISTS'
632 , p_token_tbl => l_token_tbl
633 );
634 l_return_status := FND_API.G_RET_STS_ERROR;
635 ELSIF l_return_status = Bom_Globals.G_RECORD_NOT_FOUND AND
636 p_sub_component_rec.transaction_type IN
637 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
638 THEN
639 Error_Handler.Add_Error_Token
640 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
641 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
642 , p_message_name => 'ENG_SUB_COMP_DOESNOT_EXIST'
643 , p_token_tbl => l_token_tbl
644 );
645 l_return_status := FND_API.G_RET_STS_ERROR;
646 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
647 THEN
648 Error_Handler.Add_Error_Token
649 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
650 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
651 , p_message_name => NULL
652 , p_message_text =>
653 'Unexpected error while existence verification of ' ||
654 'Substitute component '||
655 p_sub_component_rec.substitute_component_name
656 , p_token_tbl => l_token_tbl
657 );
658 ELSE
659 l_return_status := FND_API.G_RET_STS_SUCCESS;
660 END IF;
661
662 x_return_status := l_return_status;
663 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
664
665 END Check_Existence;
666
667 /****************************************************************************
668 * Procedure : Check_Lineage
669 * Parameters IN : Substitute Component exposed column record
670 * Substitute Component unexposed column record
671 * Parameters OUT: Mesg Token Table
672 * Return Status
673 * Purpose : Procedure will verify that the parent-child relationship
674 * hold good in the production tables based on the data that
675 * the user has given.
676 *****************************************************************************/
677 PROCEDURE Check_Lineage
678 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
679 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
680 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
681 , x_Return_Status OUT NOCOPY VARCHAR2
682 )
683 IS
684 l_token_tbl Error_Handler.Token_Tbl_Type;
685 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
686
687 CURSOR c_GetComponent IS
688 SELECT revised_item_sequence_id
689 FROM bom_inventory_components
690 WHERE component_item_id = p_sub_comp_unexp_rec.component_item_id
691 AND operation_seq_num = p_sub_component_rec.operation_sequence_number
692 AND effectivity_date = p_sub_component_rec.start_effective_date
693 AND bill_sequence_id = p_sub_comp_unexp_rec.bill_sequence_id;
694 BEGIN
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 FOR Component IN c_GetComponent LOOP
698 IF Component.revised_item_sequence_id <>
699 p_sub_comp_unexp_rec.revised_item_sequence_id
700 THEN
701 l_Token_Tbl(1).token_name :=
702 'REVISED_COMPONENT_NAME';
703 l_Token_Tbl(1).token_value :=
704 p_sub_component_rec.component_item_name;
705 l_Token_Tbl(2).token_name :=
706 'REVISED_ITEM_NAME';
707 l_Token_Tbl(2).token_value :=
708 p_sub_component_rec.revised_item_name;
709 l_Token_tbl(3).token_name :=
710 'SUBSTITUTE_ITEM_NAME';
711 l_token_tbl(3).token_value :=
712 p_sub_component_rec.substitute_component_name;
713
714 Error_Handler.Add_Error_Token
715 ( p_Message_Name => 'ENG_SBC_REV_ITEM_MISMATCH'
716 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
717 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
718 , p_Token_Tbl => l_Token_Tbl
719 );
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 END IF;
722 END LOOP;
723
724 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
725
726 END CHECK_LINEAGE;
727
728 /****************************************************************************
729 * Procedure : Check_Access
730 * Parameters IN : Substitute Component exposed column record
731 * Substitute Component unexposed column record
732 * Prameters OUT : Mesg Token Table
733 * Return Status
734 * Purpose : If the System Information record values are not already filled
735 * the process will query the appropriate profile values and
736 * verify that the user has access to the Revised Item, the
737 * parent component item and the item type of the substitute
738 * Component. It will also verify that the revised item is not
739 * already implemented or canceled.
740 ****************************************************************************/
741 PROCEDURE Check_Access
742 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
743 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
744 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
745 , x_Return_Status OUT NOCOPY VARCHAR2
746 )
747 IS
748 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
749 l_Token_Tbl Error_Handler.Token_Tbl_Type;
750 l_Return_Status VARCHAR2(1);
751 CURSOR c_GetSubCompType IS
752 SELECT bom_item_type
753 FROM mtl_system_items
754 WHERE inventory_item_id = p_sub_comp_unexp_rec.substitute_component_id
755 AND organization_id = p_sub_comp_unexp_rec.organization_id;
756
757 BEGIN
758 l_return_status := FND_API.G_RET_STS_SUCCESS;
759
760 --
761 -- The calling progrma must make a calls to the parent entities
762 -- Check_Access to make sure that the parent is accessible
763 --
764 /********************************************************
765 IF Bom_Globals.Is_RComp_Cancl IS NULL OR
766 Bom_Globals.Is_RComp_Cancl = FALSE
767 THEN
768 Eng_Validate_Rev_Component.Check_Access
769 ( p_revised_item_name => p_sub_component_rec.revised_item_name
770 , p_revised_item_id => p_sub_comp_unexp_rec.revised_item_id
771 , p_organization_id => p_sub_comp_unexp_rec.organization_id
772 , p_change_notice => p_sub_component_rec.eco_name
773 , p_new_item_revision =>
774 p_sub_component_rec.new_revised_item_revision
775 , p_effectivity_date =>
776 p_sub_component_rec.start_effective_date
777 , p_component_item_id =>
778 p_sub_comp_unexp_rec.component_item_id
779 , p_operation_seq_num =>
780 p_sub_component_rec.operation_sequence_number
781 , p_bill_sequence_id =>
782 p_sub_comp_unexp_rec.bill_sequence_id
783 , p_component_name =>
784 p_sub_component_rec.component_item_name
785 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
786 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
787 , x_Return_Status => l_return_status
788 );
789 END IF;
790 **********************************************************/
791
792 FOR SubCompType IN c_GetSubCompType
793 LOOP
794 IF SubCompType.bom_item_type = Bom_Globals.G_PRODUCT_FAMILY
795 THEN
796 Error_Handler.Add_Error_Token
797 ( p_Message_Name => 'ENG_SUB_COMP_PF'
798 , p_mesg_token_tbl => l_mesg_token_tbl
799 , x_mesg_token_tbl => l_mesg_token_tbl
800 , p_token_tbl => l_token_tbl
801 );
802 l_return_status := FND_API.G_RET_STS_ERROR;
803 ELSIF SubCompType.bom_item_type NOT IN
804 (NVL(Bom_Globals.Get_MDL_Item_Access,0),
805 NVL(Bom_Globals.Get_OC_Item_Access,0),
806 NVL(Bom_Globals.Get_PLN_Item_Access,0),
807 NVL(Bom_Globals.Get_STD_Item_Access,0)
808 )
809 THEN
810 l_token_tbl(1).token_name := 'REV_COMP';
811 l_token_tbl(1).token_value :=
812 p_sub_component_rec.substitute_component_name;
813 l_token_tbl(2).token_name := 'BOM_ITEM_TYPE';
814 l_token_tbl(2).translate := TRUE;
815
816 IF SubCompType.bom_item_type = 1
817 THEN
818 l_Token_Tbl(2).Token_Value := 'ENG_MODEL';
819 ELSIF SubCompType.bom_item_type = 2
820 THEN
821 l_Token_Tbl(2).Token_Value:='ENG_OPTION_CLASS';
822 ELSIF SubCompType.bom_item_type = 3
823 THEN
824 l_Token_Tbl(2).Token_Value := 'ENG_PLANNING';
825 ELSIF SubCompType.bom_item_type = 4
826 THEN
827 l_Token_Tbl(2).Token_Value := 'ENG_STANDARD';
828 END IF;
829
830 Error_Handler.Add_Error_Token
831 ( p_Message_Name => 'ENG_SUB_COMP_ACCESS_DENIED'
832 , p_Mesg_Token_Tbl => l_mesg_token_tbl
833 , x_Mesg_Token_Tbl => l_mesg_token_tbl
834 , p_Token_Tbl => l_token_tbl
835 );
836 l_token_tbl.DELETE(2);
837 l_return_status := FND_API.G_RET_STS_ERROR;
838 END IF;
839 END LOOP;
840 x_return_status := l_return_status;
841
842 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
843 END Check_Access;
844
845 END ENG_Validate_Sub_Component;