[Home] [Help]
PACKAGE BODY: APPS.BOM_VALIDATE_SUB_COMPONENT
Source
1 PACKAGE BODY BOM_Validate_Sub_Component AS
2 /* $Header: BOMLSBCB.pls 120.7 2010/07/28 00:13:36 umajumde ship $ */
3 /*============================================================================
4 | Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
5 | All rights reserved.
6 |=============================================================================
7 |
8 | FILENAME
9 | BOMLSBCB.pls
10 |
11 | DESCRIPTION
12 | Body of package BOM_Validate_Sub_Component
13 |
14 | NOTES
15 |
16 | HISTORY
17 | 17-JUL-1999 Rahul Chitko Initial Creation
18 | 08-MAY-2001 Refai Farook EAM related changes
19 |
20 | 20-Jun-05 Vani Hymavathi Validations for to OPM convergence project
21 =============================================================================*/
22 -- +------------------------------------------+
23 -- | Global constant holding the package name |
24 -- +------------------------------------------+
25
26 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_Validate_Sub_Component';
27 G_RET_CODE NUMBER;
28
29 /*********************** Entity **********************************************/
30
31 PROCEDURE CHECK_REQUIRED( x_return_status IN OUT NOCOPY VARCHAR2
32 , p_sub_component_rec IN
33 Bom_Bo_Pub.Sub_Component_Rec_Type
34 , x_Mesg_Token_tbl IN OUT NOCOPY
35 Error_Handler.Mesg_Token_Tbl_Type
36 )
37 IS
38 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
39 BEGIN
40 IF p_sub_component_rec.Revised_Item_Name IS NULL OR
41 p_sub_component_rec.Revised_Item_Name = FND_API.G_MISS_CHAR OR
42 p_sub_component_rec.Start_Effective_Date IS NULL OR
43 p_sub_component_rec.Start_Effective_Date = FND_API.G_MISS_DATE OR
44 p_sub_component_rec.Operation_Sequence_Number IS NULL OR
45 p_sub_component_rec.Operation_Sequence_Number = FND_API.G_MISS_NUM OR
46 p_sub_component_rec.Component_Item_Name IS NULL OR
47 p_sub_component_rec.Component_Item_Name = FND_API.G_MISS_CHAR OR
48 p_sub_component_rec.Alternate_BOM_Code IS NULL OR
49 p_sub_component_rec.Alternate_BOM_Code = FND_API.G_MISS_CHAR OR
50 p_sub_component_rec.Substitute_Component_Name IS NULL OR
51 p_sub_component_rec.Substitute_Component_Name = FND_API.G_MISS_CHAR OR
52 ( ( p_sub_component_rec.acd_type IS NULL OR
53 p_sub_component_rec.acd_type = FND_API.G_MISS_NUM OR
54 p_sub_component_rec.New_Revised_Item_Revision IS NULL OR
55 p_sub_component_rec.New_Revised_Item_Revision =
56 FND_API.G_MISS_CHAR
57 ) AND
58 Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
59 )
60 THEN
61 x_return_status := FND_API.G_RET_STS_ERROR;
62 Error_Handler.Add_Error_Token
63 ( x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
64 , p_Message_Name => 'BOM_SUB_COMP_REQ'
65 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
66 );
67 -- Log an error indicating that one of the required columns is
68 -- missing with scope of 'R'
69 RETURN;
70 END IF;
71
72 x_return_status := FND_API.G_RET_STS_SUCCESS;
73
74 END CHECK_REQUIRED;
75
76 /*******************************************************************
77 * Procedure : Entity
78 * Parameter IN : Substitute Component Record
79 * Substitute component Record of Unexposed Columns
80 * Parameter OUT : Return_Status - Indicating status of the process.
81 * Mesg_Token_Tbl - Table of Errors and their tokens
82 *
83 * Purpose : Entity procedure will validate the entity record by
84 * verfying the business logic for Substitute Components
85 *
86 *********************************************************************/
87 PROCEDURE Check_Entity
88 ( x_return_status IN OUT NOCOPY VARCHAR2
89 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
90 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
91 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
92 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type
93 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
94 )
95 IS
96 l_temp_var NUMBER :=0;
97 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
98 l_sub_comp_unique VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
99 l_dummy NUMBER :=0;
100 l_dummy2 NUMBER :=0;
101 l_parent_acd_type NUMBER :=0;
102 l_parent_BIT NUMBER;
103 l_rec_BSI NUMBER;
104 l_rec_AST NUMBER;
105 l_rec_BIT NUMBER;
106 l_rec_AII NUMBER;
107 l_rec_CII NUMBER;
108 l_rec_ID DATE;
109 l_processed BOOLEAN;
110 l_err_text VARCHAR2(200);
111 stmt_num NUMBER := 0;
112 l_token_tbl Error_Handler.Token_Tbl_Type;
113 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
114 l_sbc_item_type NUMBER;
115 l_sbc_bom_enabled_flag VARCHAR2(1);
116 l_sbc_eng_item_flag VARCHAR2(1);
117 l_sbc_tracking_qty_ind VARCHAR2(30);
118 l_assy_bom_enabled VARCHAR2(1);
119 l_parent_PTO_flag VARCHAR2(1);
120 l_rec_ATO_flag VARCHAR2(1);
121 l_rec_optional NUMBER;
122 BEGIN
123
124 BEGIN
125 ------------------------------------------------------------------
126 -- Select Bill_Sequence_Id, Assembly_Type, Assembly_Item_Id, |
127 -- ACD_TYPE, Component_Item_Id and Implementation_Date |
128 -- from bom_inventory_component and bom_bill_of_materials tables |
129 ------------------------------------------------------------------
130
131 --modifying the query below for BOM ER #9946990
132
133 SELECT bbom.bill_sequence_id ,
134 bbom.assembly_type,
135 bbom.assembly_item_id,
136 bic.ACD_TYPE,
137 bic.bom_item_type,
138 bic.component_item_id,
139 bic.implementation_date,
140 msi.bom_item_type,
141 msi.pick_components_flag,
142 msi2.replenish_to_order_flag,
143 bic.optional
144 INTO l_rec_BSI,
145 l_rec_AST,
146 l_rec_AII,
147 l_parent_acd_type,
148 l_rec_BIT,
149 l_rec_CII,
150 l_rec_ID,
151 l_parent_BIT,
152 l_parent_PTO_flag,
153 l_rec_ATO_flag,
154 l_rec_optional
155 FROM mtl_system_items msi,
156 mtl_system_items msi2,
157 bom_inventory_components bic,
158 bom_bill_of_materials bbom
159 WHERE msi.inventory_item_id = bbom.assembly_item_id
160 AND msi.organization_id = bbom.organization_id
161 AND msi2.inventory_item_id = bic.component_item_id
162 AND msi2.organization_id = bic.pk2_value
163 AND bic.component_sequence_id =
164 p_Sub_Comp_Unexp_Rec.component_sequence_id
165 AND bic.bill_sequence_id = bbom.bill_sequence_id;
166 EXCEPTION
167 WHEN OTHERS THEN
168 NULL;
169 END;
170 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Bill SequenceId : ' || to_char(l_rec_BSI)); END IF;
171 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Assembly Type : ' || to_char(l_rec_AST)); END IF;
172 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Comp SequenceId : ' || to_char(p_Sub_Comp_Unexp_Rec.component_sequence_id)); END IF;
173
174 if bom_globals.get_debug = 'Y' then Error_Handler.write_debug('Sub Comp: Checking for editable common bill...'); END IF;
175
176 --validation for BOM ER #9946990
177 --if an ATO item was added in a kit when the related profile was set, you cannot
178 --update the component once the profile is unset (meaning you cannot insert, update or delete
179 --a substitute component on it )
180 --BOM ER #9946990 changes (begin)
181
182 IF
183 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
184 l_parent_BIT = Bom_Globals.G_STANDARD AND
185 l_parent_PTO_Flag = 'Y' AND
186 l_rec_ATO_Flag = 'Y' AND
187 l_rec_BIT = Bom_Globals.G_STANDARD
188 THEN
189 Error_Handler.Add_Error_Token
190 ( p_message_name => 'BOM_KIT_COMP_PRF_NOT_SET'
191 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
192 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
193 , p_Token_Tbl => l_Token_Tbl
194 );
195 l_return_status := FND_API.G_RET_STS_ERROR;
196 END IF;
197
198
199 --if a mandatory ATO item was added in a pto model when the related profile was set, you cannot
200 --update or delete the component once the profile is unset (meaning you cannot insert, update
201 --or delete a substitute component on it
202
203 IF
204 nvl(fnd_profile.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1 AND
205 l_parent_BIT = Bom_Globals.G_MODEL AND
206 l_parent_PTO_Flag = 'Y' AND
207 l_rec_ATO_Flag = 'Y' AND
208 l_rec_BIT = Bom_Globals.G_STANDARD AND
209 l_rec_optional = 2
210 THEN
211 Error_Handler.Add_Error_Token
212 ( p_message_name => 'BOM_MODEL_COMP_PRF_NOT_SET'
213 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
214 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
215 , p_Token_Tbl => l_Token_Tbl
216 );
217 l_return_status := FND_API.G_RET_STS_ERROR;
218 END IF;
219 --BOM ER #9946990 changes (end)
220
221 BEGIN
222 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN
223 SELECT 1
224 INTO l_dummy
225 FROM bom_bill_of_materials
226 WHERE bill_sequence_id = source_bill_sequence_id
227 AND bill_sequence_id = l_rec_BSI; --p_sub_comp_unexp_rec.bill_Sequence_id; --Bug 5726557
228 END IF;
229 EXCEPTION
230 WHEN NO_DATA_FOUND THEN
231 Error_Handler.Add_Error_Token
232 ( p_Message_Name => 'BOM_COMMON_SUB_COMP'
233 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
234 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
235 , p_Token_Tbl => l_Token_Tbl
236 );
237 l_Return_Status := FND_API.G_RET_STS_ERROR;
238 END;
239
240 SELECT msi.bom_enabled_flag
241 INTO l_assy_bom_enabled
242 FROM mtl_system_items_b msi,
243 bom_bill_of_materials bbom
244 WHERE bbom.bill_sequence_id = l_rec_BSI
245 AND bbom.assembly_item_id = msi.inventory_item_id
246 AND bbom.organization_id = msi.organization_id;
247
248 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Assy Bom Enabled flag : ' || l_assy_bom_enabled); END IF;
249
250 IF l_assy_bom_enabled <> 'Y'
251 THEN
252 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
253 l_token_tbl(1).token_name := 'REVISED_ITEM_NAME';
254 l_token_tbl(1).token_value :=
255 p_sub_component_rec.Revised_Item_Name;
256 Error_Handler.Add_Error_Token
257 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
258 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
259 , p_message_name => 'BOM_REV_ITEM_BOM_NOT_ENABLED'
260 , p_token_tbl => l_token_tbl
261 );
262 END IF;
263 l_return_status := FND_API.G_RET_STS_ERROR;
264 END IF;
265
266 IF p_control_rec.caller_type <> 'FORM'
267 THEN
268
269 ---------------------------------------------------------------------
270 -- If the Transaction Type is CREATE and the ACD_type is ADD |
271 -- then check the type of item to which a sub component is being |
272 -- added. Planning bills cannot have sub comps esgs and also |
273 -- components which are not Standard cannot have sub comps. This |
274 -- OR so even if either exists sub comp cannot be added. |
275 ---------------------------------------------------------------------
276 IF p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_CREATE THEN
277
278 BEGIN
279 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Checking for planning bill or non-std. component . . . '); END IF;
280
281 IF l_rec_bit IN (1, 2, 3, 5)
282 THEN
283 l_err_text := 'BOM_SBC_NON_STD_PARENT';
284 l_return_status := FND_API.G_RET_STS_ERROR;
285 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
286 -- dbms_output.put_line
287 -- ('Expected Error. non-standard component . . . ');
288
289 Error_Handler.Add_Error_Token
290 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
291 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
292 , p_message_name => l_err_text
293 );
294 END IF;
295
296 END IF;
297
298 IF l_parent_bit = 3 THEN
299 l_Err_text := 'BOM_SBC_PLANNING_BILL';
300 l_return_status := FND_API.G_RET_STS_ERROR;
301
302 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
303 -- dbms_output.put_line
304 -- ('Expected Error. planning parent. . . ');
305
306 Error_Handler.Add_Error_Token
307 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
308 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
309 , p_message_name => l_err_text
310 );
311 END IF;
312 END IF;
313
314 -----------------------------------------------------------------
315 -- If a record is found, then log an error because of the above |
316 -- mentioned comment. |
317 -----------------------------------------------------------------
318
319 EXCEPTION
320 WHEN NO_DATA_FOUND THEN
321 NULL; -- Do nothing
322 WHEN OTHERS THEN
323 -- dbms_output.put_line('Unexpected error in Checking Planning Item
324 -- ' || SQLERRM);
325 Error_Handler.Add_Error_Token
326 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
327 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
328 , p_message_name => NULL
329 , p_message_text => 'ERROR in Entity validation ' ||
330 substr(SQLERRM, 1, 240) || ' ' ||
331 to_char(SQLCODE)
332 );
333 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
334 END;
335
336 END IF;
337
338 ------------------------------------------------------------------
339 -- When update an ECO that has (a process and Approval Status of |
340 -- 'Approval approved') we should change status to not submitted |
341 -- for Approval |
342 ------------------------------------------------------------------
343 stmt_num := 2;
344 IF (p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE OR
345 p_sub_component_rec.Transaction_Type = Bom_Globals.G_OPR_CREATE)
346 AND
347 Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
348 THEN
349 Bom_Globals.Check_Approved_For_Process
350 ( p_change_notice => p_sub_component_rec.Eco_Name
351 , p_organization_id => p_Sub_Comp_Unexp_Rec.Organization_Id
352 , x_processed =>l_processed
353 , x_err_text =>l_err_text
354 );
355 IF (l_processed) THEN
356 Bom_Globals.Set_Request_For_Approval(
357 p_change_notice => p_sub_component_rec.Eco_Name
358 ,p_organization_id => p_Sub_Comp_Unexp_Rec.Organization_Id
359 ,x_err_text => l_err_text);
360 END IF;
361 END IF;
362
363 -----------------------------------------------------------------------
364 -- Check new substitute component item (for create or update) already |
365 -- exists in MTL_SYSTEM_ITEMS and has the correct item attributes |
366 -----------------------------------------------------------------------
367
368 stmt_num := 7;
369 BEGIN
370 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verify sub. comp. exists in MTL_System_Item with correct attributes . . .'); END IF;
371
372 SELECT bom_item_type, bom_enabled_flag, eng_item_flag ,tracking_quantity_ind
373 INTO l_sbc_item_type, l_sbc_bom_enabled_flag, l_sbc_eng_item_flag,l_sbc_tracking_qty_ind
374 FROM mtl_system_items
375 WHERE organization_id = p_Sub_Comp_Unexp_Rec.Organization_Id
376 AND inventory_item_id = decode(p_Sub_Comp_Unexp_Rec.new_substitute_component_id,
377 NULL,
378 p_Sub_Comp_Unexp_Rec.substitute_component_id,
379 FND_API.G_MISS_NUM,
380 p_Sub_Comp_Unexp_Rec.substitute_component_id,
381 p_Sub_Comp_Unexp_Rec.new_substitute_component_id
382 );
383
384
385 IF l_sbc_item_type <> 4 THEN
386 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
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 => 'BOM_SUB_COMP_NOT_STD'
391 );
392 END IF;
393 l_return_status := FND_API.G_RET_STS_ERROR;
394 END IF;
395 /* Validations for OPM Convergence Project
396 Dual UOM controlled items should not be allowed*/
397
398 IF (l_sbc_tracking_qty_ind='Y' )THEN
399 Error_Handler.Add_Error_Token
400 ( p_Message_name => 'BOM_DUAL_UOM_ITEMS'
401 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
402 , x_mesg_token_tbl => l_mesg_token_tbl
403 );
404 l_return_status := FND_API.G_RET_STS_ERROR;
405 END IF;
406
407 /* IF l_sbc_bom_enabled_flag <> 'Y'
408 THEN
409 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
410 l_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
411 l_token_tbl(1).token_value :=
412 p_sub_component_rec.substitute_component_name;
413 Error_Handler.Add_Error_Token
414 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
415 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
416 , p_message_name => 'BOM_SUB_COMP_NOT_BOM_ENABLED'
417 , p_token_tbl => l_token_tbl
418 );
419 END IF;
420 l_return_status := FND_API.G_RET_STS_ERROR;
421 END IF; */ -- Commented as part of bug 3420657.
422
423 -- IF ((l_rec_AST = 2) OR (l_rec_AST = 1 AND l_sbc_eng_item_flag = 'Y')) -- Bug No: 3561608
424 IF (l_rec_AST = 1 AND l_sbc_eng_item_flag = 'Y')
425 THEN
426 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
427 Error_Handler.Add_Error_Token
428 ( x_Mesg_Token_tbl => l_Mesg_Token_Tbl
429 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
430 , p_message_name => 'BOM_SUB_COMP_ASSEMBLY_MFG'
431 );
432 END IF;
433 l_return_status := FND_API.G_RET_STS_ERROR;
434 END IF;
435
436 EXCEPTION
437 WHEN no_data_found THEN
438 NULL;
439 END;
440
441 END IF; -- end of code that executes if caller is not ECO form.
442
443 -------------------------------------------------------------------
444 -- If bill is a Common for other bills, then make sure Substitute |
445 -- Component Item exists in those orgs |
446 -------------------------------------------------------------------
447 stmt_num := 8;
448 BEGIN
449 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verfying component for other common orgs . . . '); END IF;
450
451 SELECT 1
452 INTO l_dummy
453 FROM bom_bill_of_materials bbom
454 WHERE rownum =1
455 AND bbom.common_bill_sequence_id = l_rec_BSI
456 AND bbom.organization_id <> bbom.common_organization_id
457 AND not exists
458 (SELECT null
459 FROM mtl_system_items msi
460 WHERE msi.organization_id = bbom.organization_id
461 AND msi.inventory_item_id =
462 decode(p_Sub_Comp_Unexp_Rec.new_substitute_component_id,
463 NULL,
464 p_Sub_Comp_Unexp_Rec.substitute_component_id,
465 FND_API.G_MISS_NUM,
466 p_Sub_Comp_Unexp_Rec.substitute_component_id,
467 p_Sub_Comp_Unexp_Rec.new_substitute_component_id
468 )
469 AND msi.bom_enabled_flag = 'Y'
470 AND msi.bom_item_type = 4
471 AND (bbom.assembly_type = 2 OR
472 (bbom.assembly_type = 1 AND msi.eng_item_flag = 'N')));
473
474 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
475 l_token_tbl.DELETE;
476 l_Token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
477 l_token_tbl(1).token_value :=
478 p_sub_component_rec.substitute_component_name;
479 Error_Handler.Add_Error_Token
480 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
481 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
482 , p_message_name => 'BOM_SBC_COMBILL_DOES_NOT_EXIST'
483 , p_token_tbl => l_token_tbl
484 );
485 END IF;
486 l_return_status := FND_API.G_RET_STS_ERROR;
487 EXCEPTION
488 WHEN no_data_found THEN
489 null;
490 END;
491
492 IF p_control_rec.caller_type <> 'FORM'
493 THEN
494
495 ---------------------------------------------------------
496 -- Verify sub comp is not the same as bill or component |
497 ---------------------------------------------------------
498 stmt_num := 9;
499 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verifying sub. comp not same as parent . . . '); END IF;
500
501 IF (p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE AND
502 p_Sub_Comp_Unexp_Rec.New_Substitute_Component_Id IN
503 (l_rec_AII, l_rec_CII))
504 OR
505 (p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_CREATE AND
506 p_Sub_Comp_Unexp_Rec.Substitute_Component_Id IN
507 (l_rec_AII,l_rec_CII))
508 THEN
509 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
510 Error_Handler.Add_Error_Token
511 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
512 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
513 , p_message_name => 'BOM_SUBCOMP_SAMEAS_ITEM_COMP'
514 , p_token_tbl => l_token_tbl
515 );
516 END IF;
517 l_return_status := FND_API.G_RET_STS_ERROR;
518 END IF;
519
520 ------------------------------------------
521 -- Validate attribute dependencies here.|
522 ------------------------------------------
523 -----------------------------------------
524 -- substitute quantity couldn't be zero |
525 -----------------------------------------
526
527 stmt_num := 10;
528 -----------------------------------------------------------------------
529 -- If parent component acd_type is disabled, we can't do anything for |
530 -- child component. |
531 -----------------------------------------------------------------------
532 stmt_num := 11;
533 --
534 -- Peform this set of validations only if the ECO BO is calling the
535 -- validation package
536 --
537 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
538 THEN
539 IF l_parent_acd_type = 3 THEN
540 l_return_status := FND_API.G_RET_STS_ERROR;
541 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
542 Error_Handler.Add_Error_Token
543 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
544 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
545 , p_message_name => 'BOM_SBC_COMP_ACD_TYPE_DISABLE'
546 );
547 END IF;
548 END IF;
549
550 -------------------------------------------------------------------
551 -- if parent's acd_type is add, child acd_type should also be add |
552 -------------------------------------------------------------------
553 stmt_num := 12;
554 IF ((l_parent_acd_type = 1 ) and ( p_Sub_Component_Rec.ACD_Type <> 1))
555 THEN
556 l_return_status := FND_API.G_RET_STS_ERROR;
557 l_token_tbl(1).token_name := 'REVISED_COMPONENT_NAME';
558 l_token_tbl(1).token_value := p_sub_component_rec.component_item_name;
559 Error_Handler.Add_Error_Token
560 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
561 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
562 , p_message_name => 'BOM_SBC_ACD_NOT_COMPATIBLE'
563 , p_token_tbl => l_token_tbl
564 );
565 END IF;
566
567 ------------------------------------------------------------------------
568 -- if acd_type is disable, sub comp must belong to revisd comp already |
569 -- if acd_type is not disable, sub comp must be unique for that |
570 -- revised component |
571 ------------------------------------------------------------------------
572 stmt_num := 13;
573 /*
574 IF p_sub_component_rec.acd_type <> 3
575 AND ( p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_CREATE OR
576 p_Sub_Component_Rec.Transaction_Type = Bom_Globals.G_OPR_UPDATE
577 )
578 THEN
579
580
581 l_sub_comp_unique:= Verify_Unique_Substitute( p_sub_component_rec
582 , p_sub_comp_Unexp_rec
583 );
584
585 END IF;
586
587 */
588
589 stmt_num := 14;
590 -------------------------------------------------------------------------
591 -- if acd_type is disable, sub comp must belong to revised comp already |
592 -------------------------------------------------------------------------
593
594 IF p_sub_component_rec.acd_type = 3 THEN
595 BEGIN
596 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verifying if component is not disable type . . . '); END IF;
597
598 SELECT count(*)
599 INTO l_dummy
600 FROM bom_substitute_components sub_comp,
601 bom_inventory_components rev_comp
602 WHERE rev_comp.component_sequence_id =
603 p_Sub_Comp_Unexp_Rec.Component_Sequence_Id
604 AND sub_comp.component_sequence_id =
605 rev_comp.old_component_Sequence_id
606 AND sub_comp.Substitute_Component_Id =
607 p_Sub_Comp_Unexp_Rec.Substitute_Component_Id;
608
609 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
610 ( 'Sub Components found: ' || l_dummy);
611 END IF;
612
613 IF (l_dummy = 0)
614 THEN
615 l_token_tbl(1).token_name := 'SUBSTITUTE_ITEM_NAME';
616 l_token_tbl(1).token_value :=
617 p_sub_component_rec.substitute_component_name;
618 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
619 l_token_tbl(2).token_value :=
620 p_sub_component_rec.component_item_name;
621 Error_Handler.Add_Error_Token
622 ( x_Mesg_Token_tbl => l_Mesg_Token_tbl
623 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
624 , p_message_name => 'BOM_DISABLE_SCOMP_NOT_FOUND'
625 , p_token_tbl => l_token_tbl
626 );
627 l_return_status := FND_API.G_RET_STS_ERROR;
628 END IF;
629 END;
630 END IF;
631 END IF; -- ECO BO only validations ends
632
633 END IF; -- end of code that executes when ECO form has not called package.
634
635 -- dbms_output.put_line('l_return_status : '|| l_return_status);
636
637 IF ( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
638 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
639 END IF;
640
641 ----------------------------
642 -- Done validating entity |
643 ----------------------------
644
645 x_return_status := l_return_status;
646 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
647
648 /* When the Substitute component is updated with new Substitute Component, It should be checked that the
649 New Substitute Component does not exists already */
650
651 IF ( p_Sub_Comp_Unexp_Rec.new_substitute_component_id is not null
652 and p_Sub_Comp_Unexp_Rec.new_substitute_component_id <> FND_API.G_MISS_NUM
653 and p_sub_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE) THEN
654
655 select count(*) into l_temp_var
656 FROM BOM_SUBSTITUTE_COMPONENTS
657 WHERE substitute_component_id = p_Sub_Comp_Unexp_Rec.new_substitute_component_id
658 AND COMPONENT_SEQUENCE_ID = p_Sub_Comp_Unexp_Rec.component_sequence_id
659 AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 0) =
660 NVL(DECODE(p_sub_component_rec.acd_type, FND_API.G_MISS_NUM, null, p_sub_component_rec.acd_type), 0) ;
661
662 IF (l_temp_var <>0) then
663
664 l_Token_Tbl(1).Token_Name := 'SUBSTITUTE_ITEM_NAME';
665 l_Token_Tbl(1).Token_Value :=
666 p_sub_component_rec.new_substitute_component_name;
667 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
668 l_token_tbl(2).token_value := p_sub_component_rec.component_item_name;
669
670 Error_Handler.Add_Error_Token
671 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
672 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
673 , p_message_name => 'BOM_SUB_COMP_ALREADY_EXISTS'
674 , p_token_tbl => l_token_tbl
675 );
676 RAISE FND_API.G_EXC_ERROR;
677 END IF;
678 END IF;
679
680 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Verified New Substitute Component ...'); END IF;
681
682
683
684 EXCEPTION
685 WHEN FND_API.G_EXC_ERROR THEN
686 -- dbms_output.put_line('expected Error : stmt_num -'
687 -- || to_char(stmt_num));
688 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
693 -- dbms_output.put_line('unexpected Error : stmt_num -'
694 -- || to_char(stmt_num));
695 WHEN OTHERS THEN
696 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698 -- dbms_output.put_line('other unexpected Error :
699 -- stmt_num -' || to_char(stmt_num));
700 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
701 THEN
702 l_err_text := G_PKG_NAME ||
703 'Validation (Substitute Component Entity)' ||
704 SUBSTR(SQLERRM, 1, 100);
705 Error_Handler.Add_Error_Token( x_Mesg_Token_tbl => x_Mesg_Token_tbl
706 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
707 , p_message_name => NULL
708 , p_message_text => l_err_text
709 );
710
711 END IF;
712 END Check_Entity;
713
714
715 /*******************************************************************
716 * Procedure : Check_Attributes
717 * Parameter IN : Substitute Component Record
718 * Parameter IN OUT NOCOPY : Return_Status - Indicating status of the process.
719 * Mesg_Token_Tbl - Table of Errors and their tokens
720 *
721 * Purpose : Procedure Attributes will verify the validity of
722 * all exposed columns to check if the user has given
723 * values that the columns can actually hold.
724 *********************************************************************/
725
726 PROCEDURE Check_Attributes
727 ( x_return_status IN OUT NOCOPY VARCHAR2
728 , x_Mesg_Token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
729 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
730 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
731 )
732 IS
733 l_dummy VARCHAR2(1);
734 l_return_status VARCHAR2(1);
735 l_err_text VARCHAR2(255);
736 l_token_tbl Error_Handler.Token_Tbl_Type;
737 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
738 BEGIN
739
740 l_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 /**************************************************************************
743 With patch to bug 728002, this is not valid now
744
745 IF p_sub_component_rec.substitute_item_quantity IS NOT NULL AND
746 p_sub_component_rec.substitute_item_quantity = 0
747 THEN
748 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
749 THEN
750 l_token_tbl(1).token_name := 'SUBSITUTE_ITEM_NAME';
751 l_token_tbl(1).token_value :=
752 p_sub_component_rec.substitute_component_name;
753 Error_Handler.Add_Error_Token
754 ( x_Mesg_Token_tbl => x_Mesg_Token_tbl
755 , p_message_name => 'BOM_SUB_COMP_QTY_ZERO'
756 , p_token_tbl => l_token_tbl
757 );
758 END IF;
759 x_return_status := FND_API.G_RET_STS_ERROR;
760
761 END IF;
762 **************************************************************************/
763
764 -- Validation specific to enforce integer requirements
765
766 IF p_sub_comp_unexp_rec.enforce_int_requirements_code = 1 /* If the enforce int req is 'Up' */
767 THEN
768
769 BEGIN
770 /* Enforce_Integer can be UP only if the component item's rounding control type allows
771 to round order quantities */
772
773 SELECT 'x' INTO l_dummy FROM mtl_system_items WHERE
774 inventory_item_id = p_sub_comp_unexp_rec.component_item_id
775 AND organization_id = p_sub_comp_unexp_rec.organization_id
776 AND rounding_control_type = 1;
777
778 EXCEPTION WHEN NO_DATA_FOUND THEN
779 Error_Handler.Add_Error_Token
780 ( p_Message_Name => 'BOM_ENFORCE_INT_INVALID'
781 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
782 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
783 , p_Token_Tbl => l_Token_Tbl
784 );
785 l_return_status := FND_API.G_RET_STS_ERROR;
786
787 WHEN OTHERS THEN
788
789 Error_Handler.Add_Error_Token
790 ( p_Message_Name => NULL
791 , p_Message_Text =>
792 'Error in Subs.Comp Attr. Validation '
793 || SUBSTR(SQLERRM, 1, 30) || ' ' ||
794 to_char(SQLCODE)
795 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
796 , p_Mesg_Token_Tbl => l_Mesg_token_Tbl
797 );
798 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
799 END;
800
801 x_return_status := l_return_status;
802 x_mesg_token_tbl := l_mesg_token_tbl;
803
804 END IF;
805
806 END Check_Attributes;
807
808 /*******************************************************************
809 * Procedure : Check_Entity_Delete
810 * Parameter IN : Substitute Component Record
811 * Substitute component Record of Unexposed Columns
812 * Parameter OUT : Return_Status - Indicating status of the process.
813 * Mesg_Token_Tbl - Table of Errors and their tokens
814 *
815 * Purpose : Entity_Delete procedure will verify if the record
816 * can be delete without violating any dependency rules
817 *********************************************************************/
818
819 PROCEDURE Check_Entity_Delete
820 ( x_return_status IN OUT NOCOPY VARCHAR2
821 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
822 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
823 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
824 )
825 IS
826 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
827 l_err_text VARCHAR2(255);
828 l_rec_ID DATE := NULL;
829 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
830 BEGIN
831
832 -- Validate entity delete.
833 x_return_status := l_return_status;
834
835 END Check_Entity_Delete;
836
837 /**************************************************************************
838 * Procedure : Check_Existence
839 * Parameters IN : Substitute Component exposed column record
840 * Substitute Component unexposed column record
841 * Parameters OUT: Old Substitute Component exposed column record
842 * Old substitute component unexposed column record
843 * Return status
844 * Mesg Token Table
845 * Purpose : This procedure will verify if the user given record exists
846 * when the operation is Update/Delete and does not exist when
847 * the operation is Create. If the operation is Update/Delete
848 * the procedure will query the existing record and return them
849 * as old records.
850 ***************************************************************************/
851 PROCEDURE Check_Existence
852 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
853 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
854 , x_old_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
855 , x_old_sub_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
856 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
857 , x_Return_Status IN OUT NOCOPY VARCHAR2
858 )
859 IS
860 l_token_tbl Error_Handler.Token_Tbl_Type;
861 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
862 l_return_status VARCHAR2(1);
863 BEGIN
864 l_Token_Tbl(1).Token_Name := 'SUBSTITUTE_ITEM_NAME';
865 l_Token_Tbl(1).Token_Value :=
866 p_sub_component_rec.substitute_component_name;
867 l_token_tbl(2).token_name := 'REVISED_COMPONENT_NAME';
868 l_token_tbl(2).token_value :=
869 p_sub_component_rec.component_item_name;
870
871 Error_Handler.Write_Debug('In SCOMP Check Exist before query row...'||p_sub_component_rec.transaction_type);
872 BOM_Sub_Component_Util.Query_Row
873 ( p_substitute_component_id =>
874 p_sub_comp_unexp_rec.substitute_component_id
875 , p_component_sequence_id =>
876 p_sub_comp_unexp_rec.component_sequence_id
877 , p_acd_type =>
878 p_sub_component_rec.acd_type
879 , x_Sub_Component_Rec => x_old_sub_component_rec
880 , x_Sub_Comp_Unexp_Rec => x_old_sub_comp_unexp_rec
881 , x_return_status => l_return_status
882 );
883
884 IF p_sub_component_rec.transaction_type IS NULL THEN
885 Error_Handler.Write_Debug ('In SCOMP Check Exist ...is NULL');
886 ELSIF p_sub_component_rec.transaction_type = FND_API.G_MISS_CHAR THEN
887 Error_Handler.Write_Debug ('In SCOMP Check Exist ...is MISSING');
888 ELSE
889 Error_Handler.Write_Debug('In SCOMP Check Exist...'||p_sub_component_rec.transaction_type);
890 END IF;
891
892 IF l_return_status = Bom_Globals.G_RECORD_FOUND AND
893 p_sub_component_rec.transaction_type = Bom_Globals.G_OPR_CREATE
894 THEN
895 Error_Handler.Add_Error_Token
896 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
897 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
898 , p_message_name => 'BOM_SUB_COMP_ALREADY_EXISTS'
899 , p_token_tbl => l_token_tbl
900 );
901 l_return_status := FND_API.G_RET_STS_ERROR;
902 ELSIF l_return_status = Bom_Globals.G_RECORD_NOT_FOUND AND
903 p_sub_component_rec.transaction_type IN
904 (Bom_Globals.G_OPR_UPDATE, Bom_Globals.G_OPR_DELETE)
905 THEN
906 Error_Handler.Add_Error_Token
907 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
908 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
909 , p_message_name => 'BOM_SUB_COMP_DOESNOT_EXIST'
910 , p_token_tbl => l_token_tbl
911 );
912 l_return_status := FND_API.G_RET_STS_ERROR;
913 ELSIF l_Return_status = FND_API.G_RET_STS_UNEXP_ERROR
914 THEN
915 Error_Handler.Add_Error_Token
916 ( x_Mesg_token_tbl => l_Mesg_Token_Tbl
917 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
918 , p_message_name => NULL
919 , p_message_text =>
920 'Unexpected error while existence verification of ' ||
921 'Substitute component '||
922 p_sub_component_rec.substitute_component_name
923 , p_token_tbl => l_token_tbl
924 );
925 ELSE
926
927 /* Assign the relevant transaction type for SYNC operations */
928
929 IF p_sub_component_rec.transaction_type = 'SYNC' THEN
930 IF l_return_status = Bom_Globals.G_RECORD_FOUND THEN
931 x_old_sub_component_rec.transaction_type :=
932 Bom_Globals.G_OPR_UPDATE;
933 ELSE
934 x_old_sub_component_rec.transaction_type :=
935 Bom_Globals.G_OPR_CREATE;
936 END IF;
937 END IF;
938 l_return_status := FND_API.G_RET_STS_SUCCESS;
939 END IF;
940
941 x_return_status := l_return_status;
942 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
943
944 END Check_Existence;
945
946 /****************************************************************************
947 * Procedure : Check_Lineage
948 * Parameters IN : Substitute Component exposed column record
949 * Substitute Component unexposed column record
950 * Parameters OUT: Mesg Token Table
951 * Return Status
952 * Purpose : Procedure will verify that the parent-child relationship
953 * hold good in the production tables based on the data that
954 * the user has given.
955 *****************************************************************************/
956 PROCEDURE Check_Lineage
957 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
958 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
959 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
960 , x_Return_Status IN OUT NOCOPY VARCHAR2
961 )
962 IS
963 l_token_tbl Error_Handler.Token_Tbl_Type;
964 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
965
966 CURSOR c_GetComponent IS
967 SELECT revised_item_sequence_id
968 FROM bom_inventory_components
969 WHERE component_item_id = p_sub_comp_unexp_rec.component_item_id
970 AND operation_seq_num = p_sub_component_rec.operation_sequence_number
971 AND effectivity_date = p_sub_component_rec.start_effective_date
972 AND bill_sequence_id = p_sub_comp_unexp_rec.bill_sequence_id;
973 BEGIN
974 x_return_status := FND_API.G_RET_STS_SUCCESS;
975
976 FOR Component IN c_GetComponent LOOP
977 IF NVL(Component.revised_item_sequence_id, 0) <>
978 NVL(p_sub_comp_unexp_rec.revised_item_sequence_id, 0)
979 THEN
980 l_Token_Tbl(1).token_name :=
981 'REVISED_COMPONENT_NAME';
982 l_Token_Tbl(1).token_value :=
983 p_sub_component_rec.component_item_name;
984 l_Token_Tbl(2).token_name :=
985 'REVISED_ITEM_NAME';
986 l_Token_Tbl(2).token_value :=
987 p_sub_component_rec.revised_item_name;
988 l_Token_tbl(3).token_name :=
989 'SUBSTITUTE_ITEM_NAME';
990 l_token_tbl(3).token_value :=
991 p_sub_component_rec.substitute_component_name;
992
993 Error_Handler.Add_Error_Token
994 ( p_Message_Name => 'BOM_SBC_REV_ITEM_MISMATCH'
995 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
996 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
997 , p_Token_Tbl => l_Token_Tbl
998 );
999 x_return_status := FND_API.G_RET_STS_ERROR;
1000 END IF;
1001 END LOOP;
1002
1003 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1004
1005 END CHECK_LINEAGE;
1006
1007 /****************************************************************************
1008 * Procedure : Check_Access
1009 * Parameters IN : Substitute Component exposed column record
1010 * Substitute Component unexposed column record
1011 * Prameters OUT : Mesg Token Table
1012 * Return Status
1013 * Purpose : If the System Information record values are not already filled
1014 * the process will query the appropriate profile values and
1015 * verify that the user has access to the Revised Item, the
1016 * parent component item and the item type of the substitute
1017 * Component. It will also verify that the revised item is not
1018 * already implemented or canceled.
1019 ****************************************************************************/
1020 PROCEDURE Check_Access
1021 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
1022 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
1023 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1024 , x_Return_Status IN OUT NOCOPY VARCHAR2
1025 )
1026 IS
1027 l_OPM_org VARCHAR2(1);
1028 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1029 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1030 l_Return_Status VARCHAR2(1);
1031 CURSOR c_GetSubCompType IS
1032 SELECT bom_item_type
1033 FROM mtl_system_items
1034 WHERE inventory_item_id = p_sub_comp_unexp_rec.substitute_component_id
1035 AND organization_id = p_sub_comp_unexp_rec.organization_id;
1036
1037 BEGIN
1038 l_return_status := FND_API.G_RET_STS_SUCCESS;
1039
1040 SELECT process_enabled_flag
1041 INTO l_OPM_org
1042 FROM mtl_parameters
1043 WHERE organization_id = p_sub_comp_unexp_rec.organization_id;
1044
1045
1046 FOR SubCompType IN c_GetSubCompType
1047 LOOP
1048
1049 /* Validations for OPM Convergence Project
1050 Model/Option class items are not allowed in OPM organizations*/
1051
1052 IF (l_OPM_org='Y' and SubCompType.bom_item_type in ( Bom_Globals.G_MODEL, Bom_Globals.G_OPTION_CLASS))THEN
1053 Error_Handler.Add_Error_Token
1054 ( p_Message_name => 'BOM_OPM_ORG_MODEL_OC'
1055 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1056 , x_mesg_token_tbl => l_mesg_token_tbl
1057 );
1058 l_return_status := FND_API.G_RET_STS_ERROR;
1059 ELSIF SubCompType.bom_item_type = Bom_Globals.G_PRODUCT_FAMILY
1060 THEN
1061 Error_Handler.Add_Error_Token
1062 ( p_Message_Name => 'BOM_SUB_COMP_PF'
1063 , p_mesg_token_tbl => l_mesg_token_tbl
1064 , x_mesg_token_tbl => l_mesg_token_tbl
1065 , p_token_tbl => l_token_tbl
1066 );
1067 l_return_status := FND_API.G_RET_STS_ERROR;
1068 ELSIF SubCompType.bom_item_type NOT IN
1069 (NVL(Bom_Globals.Get_MDL_Item_Access,0),
1070 NVL(Bom_Globals.Get_OC_Item_Access,0),
1071 NVL(Bom_Globals.Get_PLN_Item_Access,0),
1072 NVL(Bom_Globals.Get_STD_Item_Access,0)
1073 )
1074 THEN
1075 l_token_tbl(1).token_name := 'REV_COMP';
1076 l_token_tbl(1).token_value :=
1077 p_sub_component_rec.substitute_component_name;
1078 l_token_tbl(2).token_name := 'BOM_ITEM_TYPE';
1079 l_token_tbl(2).translate := TRUE;
1080
1081 IF SubCompType.bom_item_type = 1
1082 THEN
1083 l_Token_Tbl(2).Token_Value := 'BOM_MODEL';
1084 ELSIF SubCompType.bom_item_type = 2
1085 THEN
1086 l_Token_Tbl(2).Token_Value:='BOM_OPTION_CLASS';
1087 ELSIF SubCompType.bom_item_type = 3
1088 THEN
1089 l_Token_Tbl(2).Token_Value := 'BOM_PLANNING';
1090 ELSIF SubCompType.bom_item_type = 4
1091 THEN
1092 l_Token_Tbl(2).Token_Value := 'BOM_STANDARD';
1093 END IF;
1094
1095 Error_Handler.Add_Error_Token
1096 ( p_Message_Name => 'BOM_SUB_COMP_ACCESS_DENIED'
1097 , p_Mesg_Token_Tbl => l_mesg_token_tbl
1098 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1099 , p_Token_Tbl => l_token_tbl
1100 );
1101 l_token_tbl.DELETE(2);
1102 l_return_status := FND_API.G_RET_STS_ERROR;
1103 END IF;
1104 END LOOP;
1105 x_return_status := l_return_status;
1106
1107 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1108 END Check_Access;
1109
1110 /*
1111 ** Procedures for BOM Business Object
1112 */
1113 PROCEDURE Check_Required
1114 ( x_return_status IN OUT NOCOPY VARCHAR2
1115 , p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1116 , x_Mesg_Token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1117 )
1118 IS
1119 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1120 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1121 BEGIN
1122 --
1123 -- Convert the BOM Record
1124 --
1125 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1126 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1127 , x_sub_component_rec => l_sub_component_rec
1128 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1129 );
1130
1131 --
1132 -- Call Check Required.
1133 --
1134
1135 Check_Required
1136 ( p_sub_component_rec => l_sub_component_rec
1137 , x_return_Status => x_return_status
1138 , x_mesg_token_tbl => x_mesg_token_tbl
1139 );
1140
1141 END Check_Required;
1142
1143 -- Procedure Entity
1144
1145 PROCEDURE Check_Entity
1146 ( x_return_status IN OUT NOCOPY VARCHAR2
1147 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1148 , p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1149 , p_bom_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1150 )
1151 IS
1152 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1153 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1154 BEGIN
1155 --
1156 -- Convert the BOM Record
1157 --
1158 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1159 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1160 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
1161 , x_sub_component_rec => l_sub_component_rec
1162 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1163 );
1164
1165 --
1166 -- Call Check Entity.
1167 --
1168 Check_Entity
1169 ( p_sub_component_rec => l_sub_component_rec
1170 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1171 , x_return_status => x_return_status
1172 , x_mesg_token_tbl => x_mesg_token_tbl
1173 );
1174
1175 END Check_Entity;
1176
1177 -- Procedure Attributes
1178
1179 PROCEDURE Check_Attributes
1180 ( x_return_status IN OUT NOCOPY VARCHAR2
1181 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1182 , p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1183 , p_bom_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1184 )
1185 IS
1186 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1187 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1188 BEGIN
1189
1190 --
1191 -- Convert the BOM Record
1192 --
1193 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1194 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1195 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
1196 , x_sub_component_rec => l_sub_component_rec
1197 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1198 );
1199
1200
1201 -- Call Check Attributes
1202
1203 Check_Attributes
1204 ( p_sub_component_rec => l_sub_component_rec
1205 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1206 , x_return_Status => x_return_Status
1207 , x_mesg_token_tbl => x_mesg_token_tbl
1208 );
1209 END Check_Attributes;
1210
1211 -- Procedure Entity_Delete
1212
1213 PROCEDURE Check_Entity_Delete
1214 ( x_return_status IN OUT NOCOPY VARCHAR2
1215 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1216 , p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1217 , p_bom_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1218 )
1219 IS
1220 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1221 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1222 BEGIN
1223
1224 --
1225 -- Convert the BOM Record
1226 --
1227 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1228 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1229 , x_sub_component_rec => l_sub_component_rec
1230 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1231 );
1232
1233 -- Call Check Entity Delete
1234
1235 Check_Entity_Delete
1236 ( p_sub_component_rec => l_sub_component_rec
1237 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1238 , x_return_status => x_return_status
1239 , x_mesg_token_tbl => x_mesg_token_tbl
1240 );
1241
1242 END Check_Entity_Delete;
1243
1244 PROCEDURE Check_Existence
1245 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1246 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1247 , x_old_bom_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1248 , x_old_bom_sub_comp_unexp_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1249 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1250 , x_Return_Status IN OUT NOCOPY VARCHAR2
1251 )
1252 IS
1253 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1254 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1255 l_old_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1256 l_old_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1257 BEGIN
1258
1259 --
1260 -- Convert the BOM Record
1261 --
1262 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1263 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1264 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
1265 , x_sub_component_rec => l_sub_component_rec
1266 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1267 );
1268
1269 Check_Existence
1270 ( p_sub_component_rec => l_sub_component_rec
1271 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1272 , x_old_sub_component_rec => l_old_sub_component_rec
1273 , x_old_sub_comp_unexp_rec => l_old_sub_comp_unexp_rec
1274 , x_return_status => x_return_status
1275 , x_mesg_token_tbl => x_mesg_token_tbl
1276 );
1277
1278 --
1279 -- Convert the Old record received from Check Existence
1280 --
1281
1282 Bom_Bo_Pub.Convert_EcoSComp_To_BomSComp
1283 ( p_sub_component_rec => l_old_sub_component_rec
1284 , p_sub_comp_unexp_rec => l_old_sub_comp_unexp_rec
1285 , x_bom_sub_component_rec => x_old_bom_sub_component_rec
1286 , x_bom_sub_comp_unexp_rec => x_old_bom_sub_comp_unexp_rec
1287 );
1288
1289 END Check_Existence;
1290
1291 PROCEDURE Check_Lineage
1292 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1293 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1294 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1295 , x_Return_Status IN OUT NOCOPY VARCHAR2
1296 )
1297 IS
1298 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1299 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1300 BEGIN
1301
1302 --
1303 -- Convert the BOM Record
1304 --
1305 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1306 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1307 , x_sub_component_rec => l_sub_component_rec
1308 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1309 );
1310
1311 Check_Lineage
1312 ( p_sub_component_rec => l_sub_component_rec
1313 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1314 , x_return_status => x_return_status
1315 , x_mesg_token_tbl => x_mesg_token_tbl
1316 );
1317
1318 END Check_Lineage;
1319
1320 PROCEDURE Check_Access
1321 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
1322 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
1323 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1324 , x_Return_Status IN OUT NOCOPY VARCHAR2
1325 )
1326 IS
1327 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
1328 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
1329 BEGIN
1330
1331 --
1332 -- Convert the BOM Record
1333 --
1334 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
1335 ( p_bom_sub_component_rec => p_bom_sub_component_rec
1336 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
1337 , x_sub_component_rec => l_sub_component_rec
1338 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1339 );
1340
1341
1342 --
1343 -- Call Check Access
1344 --
1345 Check_Access
1346 ( p_sub_component_rec => l_sub_component_rec
1347 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
1348 , x_return_Status => x_return_status
1349 , x_mesg_token_tbl => x_mesg_token_tbl
1350 );
1351
1352 END Check_Access;
1353
1354 END BOM_Validate_Sub_Component;