[Home] [Help]
PACKAGE BODY: APPS.BOM_SUB_COMPONENT_UTIL
Source
1 PACKAGE BODY BOM_Sub_Component_Util AS
2 /* $Header: BOMUSBCB.pls 120.3.12000000.4 2007/02/23 05:41:15 pgandhik ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUSBCB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Sub_Component_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 17-JUL-1999 Rahul Chitko Initial Creation
21 -- 06-May-2005 Abhishek Rudresh Common BOM Attrs Update
22 ****************************************************************************/
23
24 -- Global constant holding the package name
25
26 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Sub_Component_Util';
27
28
29 -- Function Convert_Miss_To_Null
30
31 FUNCTION Convert_Miss_To_Null
32 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
33 ) RETURN Bom_Bo_Pub.Sub_Component_Rec_Type
34 IS
35 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type := p_sub_component_rec;
36 BEGIN
37
38 /*
39 IF l_sub_component_rec.substitute_component_id = FND_API.G_MISS_NUM THEN
40 l_sub_component_rec.substitute_component_id := NULL;
41 END IF;
42
43 IF l_sub_component_rec.last_update_date = FND_API.G_MISS_DATE THEN
44 l_sub_component_rec.last_update_date := NULL;
45 END IF;
46
47 IF l_sub_component_rec.last_updated_by = FND_API.G_MISS_NUM THEN
48 l_sub_component_rec.last_updated_by := NULL;
49 END IF;
50
51 IF l_sub_component_rec.creation_date = FND_API.G_MISS_DATE THEN
52 l_sub_component_rec.creation_date := NULL;
53 END IF;
54
55 IF l_sub_component_rec.created_by = FND_API.G_MISS_NUM THEN
56 l_sub_component_rec.created_by := NULL;
57 END IF;
58
59 IF l_sub_component_rec.last_update_login = FND_API.G_MISS_NUM THEN
60 l_sub_component_rec.last_update_login := NULL;
61 END IF;
62
63 IF l_sub_component_rec.substitute_item_quantity = FND_API.G_MISS_NUM THEN
64 l_sub_component_rec.substitute_item_quantity := NULL;
65 END IF;
66
67 IF l_sub_component_rec.component_sequence_id = FND_API.G_MISS_NUM THEN
68 l_sub_component_rec.component_sequence_id := NULL;
69 END IF;
70
71 IF l_sub_component_rec.acd_type = FND_API.G_MISS_NUM THEN
72 l_sub_component_rec.acd_type := NULL;
73 END IF;
74
75 IF l_sub_component_rec.change_notice = FND_API.G_MISS_CHAR THEN
76 l_sub_component_rec.change_notice := NULL;
77 END IF;
78
79 IF l_sub_component_rec.request_id = FND_API.G_MISS_NUM THEN
80 l_sub_component_rec.request_id := NULL;
81 END IF;
82
83 IF l_sub_component_rec.program_application_id = FND_API.G_MISS_NUM THEN
84 l_sub_component_rec.program_application_id := NULL;
85 END IF;
86
87 IF l_sub_component_rec.program_update_date = FND_API.G_MISS_DATE THEN
88 l_sub_component_rec.program_update_date := NULL;
89 END IF;
90
91 IF l_sub_component_rec.attribute_category = FND_API.G_MISS_CHAR THEN
92 l_sub_component_rec.attribute_category := NULL;
93 END IF;
94
95 IF l_sub_component_rec.attribute1 = FND_API.G_MISS_CHAR THEN
96 l_sub_component_rec.attribute1 := NULL;
97 END IF;
98
99 IF l_sub_component_rec.attribute2 = FND_API.G_MISS_CHAR THEN
100 l_sub_component_rec.attribute2 := NULL;
101 END IF;
102
103 IF l_sub_component_rec.attribute4 = FND_API.G_MISS_CHAR THEN
104 l_sub_component_rec.attribute4 := NULL;
105 END IF;
106
107 IF l_sub_component_rec.attribute5 = FND_API.G_MISS_CHAR THEN
108 l_sub_component_rec.attribute5 := NULL;
109 END IF;
110
111 IF l_sub_component_rec.attribute6 = FND_API.G_MISS_CHAR THEN
112 l_sub_component_rec.attribute6 := NULL;
113 END IF;
114
115 IF l_sub_component_rec.attribute8 = FND_API.G_MISS_CHAR THEN
116 l_sub_component_rec.attribute8 := NULL;
117 END IF;
118
119 IF l_sub_component_rec.attribute9 = FND_API.G_MISS_CHAR THEN
120 l_sub_component_rec.attribute9 := NULL;
121 END IF;
122
123 IF l_sub_component_rec.attribute10 = FND_API.G_MISS_CHAR THEN
124 l_sub_component_rec.attribute10 := NULL;
125 END IF;
126
127 IF l_sub_component_rec.attribute12 = FND_API.G_MISS_CHAR THEN
128 l_sub_component_rec.attribute12 := NULL;
129 END IF;
130
131 IF l_sub_component_rec.attribute13 = FND_API.G_MISS_CHAR THEN
132 l_sub_component_rec.attribute13 := NULL;
133 END IF;
134
135 IF l_sub_component_rec.attribute14 = FND_API.G_MISS_CHAR THEN
136 l_sub_component_rec.attribute14 := NULL;
137 END IF;
138
139 IF l_sub_component_rec.attribute15 = FND_API.G_MISS_CHAR THEN
140 l_sub_component_rec.attribute15 := NULL;
141 END IF;
142
143 IF l_sub_component_rec.program_id = FND_API.G_MISS_NUM THEN
144 l_sub_component_rec.program_id := NULL;
145 END IF;
146
147 IF l_sub_component_rec.attribute3 = FND_API.G_MISS_CHAR THEN
148 l_sub_component_rec.attribute3 := NULL;
149 END IF;
150
151 IF l_sub_component_rec.attribute7 = FND_API.G_MISS_CHAR THEN
152 l_sub_component_rec.attribute7 := NULL;
153 END IF;
154
155 IF l_sub_component_rec.attribute11 = FND_API.G_MISS_CHAR THEN
156 l_sub_component_rec.attribute11 := NULL;
157 END IF;
158
159 */
160
161 RETURN l_sub_component_rec;
162
163 END Convert_Miss_To_Null;
164
165 /********************************************************************
166 *
167 * Procedure : Update_Row
168 * Parameter IN : Substitute Component Record
169 * Sub. Comps Unexposed Cols. Record
170 * Parameter OUT : Return_Status - indicating success or failure
171 * Mesg_Token_Tbl - Filled with Errors or warnings
172 * Purpose : Update Row procedure will update the production rec
173 * to the new values as entered in the user record.
174 * Any errors are filled in the Mesg_Token_Tbl.
175 *
176 ********************************************************************/
177
178 PROCEDURE Update_Row
179 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
180 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
181 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
182 , x_return_status IN OUT NOCOPY VARCHAR2
183 )
184 IS
185 l_processed BOOLEAN;
186 l_sub_component_rec Bom_Bo_Pub.SUB_COMPONENT_REC_TYPE :=
187 p_sub_component_rec;
188 l_return_status VARCHAR2(200);
189 l_err_text VARCHAR2(255);
190 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
191 BEGIN
192
193 -- Lock the row before updating the row
194 --dbms_output.put_line('Within Update Row . . .');
195 --dbms_output.put_line('Comp.SeqId : ' ||
196 -- to_Char(p_sub_comp_Unexp_rec.component_sequence_id)
197 -- );
198 --dbms_output.put_line('Sub. Comp : ' ||
199 -- to_Char(p_sub_comp_Unexp_rec.substitute_component_id)
200 -- );
201 --dbms_output.put_line('Acd_Type : ' || to_Char(p_sub_component_rec.acd_type));
202
203
204 Error_Handler.Write_Debug ('Updating SCOMP Perform Writes ...');
205
206 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
207 UPDATE BOM_SUBSTITUTE_COMPONENTS
208 SET SUBSTITUTE_COMPONENT_ID = DECODE(p_sub_comp_Unexp_rec.new_substitute_component_id,
209 NULL, p_sub_comp_Unexp_rec.substitute_component_id,
210 FND_API.G_MISS_NUM,
211 p_sub_comp_Unexp_rec.substitute_component_id,
212 p_sub_comp_Unexp_rec.new_substitute_component_id
213 )
214 , SUBSTITUTE_ITEM_QUANTITY =
215 p_sub_component_rec.substitute_item_quantity
216 , ATTRIBUTE_CATEGORY = p_sub_component_rec.attribute_category
217 , ATTRIBUTE1 = p_sub_component_rec.attribute1
218 , ATTRIBUTE2 = p_sub_component_rec.attribute2
219 , ATTRIBUTE3 = p_sub_component_rec.attribute3
220 , ATTRIBUTE4 = p_sub_component_rec.attribute4
221 , ATTRIBUTE5 = p_sub_component_rec.attribute5
222 , ATTRIBUTE6 = p_sub_component_rec.attribute6
223 , ATTRIBUTE7 = p_sub_component_rec.attribute7
224 , ATTRIBUTE8 = p_sub_component_rec.attribute8
225 , ATTRIBUTE9 = p_sub_component_rec.attribute9
226 , ATTRIBUTE10 = p_sub_component_rec.attribute10
227 , ATTRIBUTE11 = p_sub_component_rec.attribute11
228 , ATTRIBUTE12 = p_sub_component_rec.attribute12
229 , ATTRIBUTE13 = p_sub_component_rec.attribute13
230 , ATTRIBUTE14 = p_sub_component_rec.attribute14
231 , ATTRIBUTE15 = p_sub_component_rec.attribute15
232 , Original_system_Reference =
233 p_sub_component_rec.original_system_reference
234 , Enforce_Int_Requirements = p_sub_comp_unexp_rec.Enforce_Int_Requirements_Code
235 , LAST_UPDATE_DATE = SYSDATE
236 , LAST_UPDATED_BY = BOM_Globals.Get_User_Id
237 , LAST_UPDATE_LOGIN = BOM_Globals.Get_Login_Id
238 , REQUEST_ID = Fnd_Global.Conc_Request_Id
239 , PROGRAM_ID = Fnd_Global.Conc_Program_Id
240 , PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
241 , PROGRAM_UPDATE_DATE = SYSDATE
242 WHERE SUBSTITUTE_COMPONENT_ID =
243 p_sub_comp_Unexp_rec.substitute_component_id
244 AND COMPONENT_SEQUENCE_ID = p_sub_comp_Unexp_rec.component_sequence_id
245 AND nvl(ACD_TYPE,1) = nvl(p_sub_component_rec.acd_type,1)
246 /* Bug 5726557; The code is modified to modify the substitute components
247 when implemneted through an ECO */
248 ;
249 -- end if;
250 --dbms_output.put_line('Update Row successful . . . ');
251
252 BOMPCMBM.Update_Related_Sub_Comp(p_component_sequence_id => p_sub_comp_Unexp_rec.component_sequence_id
253 , p_old_sub_comp_item_id => p_sub_comp_Unexp_rec.substitute_component_id
254 , p_new_sub_comp_item_id => nvl(p_sub_comp_Unexp_rec.new_substitute_component_id,
255 p_sub_comp_Unexp_rec.substitute_component_id)
256 , p_acd_type => p_sub_component_rec.acd_type
257 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
258 , x_Return_Status => x_return_status);
259 --x_return_status := FND_API.G_RET_STS_SUCCESS;
260 EXCEPTION
261
262 WHEN NO_DATA_FOUND THEN
263 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
264 THEN
265 Error_Handler.Add_Error_Token
266 ( p_Message_name => 'BOM_NOT_UPDATE_ROW'
267 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
268 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
269 );
270 END IF;
271
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
274
275 WHEN OTHERS THEN
276
277 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
278 THEN
279 l_err_text := G_PKG_NAME ||
280 'Utility (SubStitute Component Update)' ||
281 SUBSTR(SQLERRM, 1, 100);
282 --dbms_output.put_line('Update Row Unexpected Error: ' || l_err_text);
283
284 Error_Handler.Add_Error_Token
285 ( p_Message_Name => NULL
286 , p_Message_Text => l_err_text
287 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
288 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
289 );
290 END IF;
291 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293
294 END Update_Row;
295
296 /********************************************************************
297 *
298 * Procedure : Insert_Row
299 * Parameters IN : Substitute Component Record as given by the User
300 * Sub. Comps Unexposed Cols. Record
301 * Parameters OUT: Substitute Component Record
302 * Return_Status - Indicating success or faliure
303 * Mesg_Token_Tbl - Filled with any errors or warnings
304 * Purpose : Will Insert a new substitute component record in
305 * Bom_Substitute_Components table.
306 *
307 ********************************************************************/
308 PROCEDURE Insert_Row
309 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
310 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
311 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
312 , x_return_status IN OUT NOCOPY VARCHAR2
313 )
314 IS
315 l_processed BOOLEAN;
316 l_err_text VARCHAR2(255);
317 l_return_status VARCHAR2(200);
318 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
319 BEGIN
320
321 Error_Handler.Write_Debug ('Inserting SCOMP Perform Writes ...');
322
323 INSERT INTO BOM_SUBSTITUTE_COMPONENTS
324 ( SUBSTITUTE_COMPONENT_ID
325 , LAST_UPDATE_DATE
326 , LAST_UPDATED_BY
327 , CREATION_DATE
328 , CREATED_BY
329 , LAST_UPDATE_LOGIN
330 , SUBSTITUTE_ITEM_QUANTITY
331 , COMPONENT_SEQUENCE_ID
332 , ACD_TYPE
333 , CHANGE_NOTICE
334 , REQUEST_ID
335 , PROGRAM_APPLICATION_ID
336 , PROGRAM_UPDATE_DATE
337 , ATTRIBUTE_CATEGORY
338 , ATTRIBUTE1
339 , ATTRIBUTE2
340 , ATTRIBUTE3
341 , ATTRIBUTE4
342 , ATTRIBUTE5
343 , ATTRIBUTE6
344 , ATTRIBUTE7
345 , ATTRIBUTE8
346 , ATTRIBUTE9
347 , ATTRIBUTE10
348 , ATTRIBUTE11
349 , ATTRIBUTE12
350 , ATTRIBUTE13
351 , ATTRIBUTE14
352 , ATTRIBUTE15
353 , PROGRAM_ID
354 , Original_System_Reference
355 , Enforce_Int_Requirements
356 )
357 VALUES
358 ( p_sub_comp_unexp_rec.substitute_component_id
359 , SYSDATE
360 , Bom_globals.Get_User_Id
361 , SYSDATE
362 , Bom_Globals.Get_User_Id
363 , Bom_Globals.Get_User_Id
364 , p_sub_component_rec.substitute_item_quantity
365 , p_sub_comp_Unexp_rec.component_sequence_id
366 , p_sub_component_rec.acd_type
367 , p_sub_component_rec.Eco_Name
368 , Fnd_Global.Conc_Request_Id /* Request Id */
369 , Bom_Globals.Get_Prog_AppId
370 , SYSDATE
371 , p_sub_component_rec.attribute_category
372 , p_sub_component_rec.attribute1
373 , p_sub_component_rec.attribute2
374 , p_sub_component_rec.attribute3
375 , p_sub_component_rec.attribute4
376 , p_sub_component_rec.attribute5
377 , p_sub_component_rec.attribute6
378 , p_sub_component_rec.attribute7
379 , p_sub_component_rec.attribute8
380 , p_sub_component_rec.attribute9
381 , p_sub_component_rec.attribute10
382 , p_sub_component_rec.attribute11
383 , p_sub_component_rec.attribute12
384 , p_sub_component_rec.attribute13
385 , p_sub_component_rec.attribute14
386 , p_sub_component_rec.attribute15
387 , Bom_Globals.Get_Prog_Id
388 , p_sub_component_rec.Original_System_Reference
389 , p_sub_comp_Unexp_rec.enforce_int_requirements_code
390 );
391 BOMPCMBM.Insert_Related_Sub_Comp(p_component_sequence_id => p_sub_comp_Unexp_rec.component_sequence_id
392 , p_sub_comp_item_id => p_sub_comp_unexp_rec.substitute_component_id
393 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
394 , x_Return_Status => x_return_status);
395 --x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 EXCEPTION
398
399 WHEN OTHERS THEN
400 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
401 THEN
402 l_err_text := G_PKG_NAME ||
403 'Utility (Substitute Component Insert)' ||
404 SUBSTR(SQLERRM, 1, 100);
405 Error_Handler.Add_Error_Token
406 ( p_Message_Name => NULL
407 , p_Message_text => l_err_text
408 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
409 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
410 );
411 END IF;
412 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
414
415 END Insert_Row;
416
417
418 /********************************************************************
419 *
420 * Procedure : Delete_Row
421 * Parameters IN : Primary Key of Substitute Component Table
422 * Parameters OUT: Return_Status - Indicating success or faliure
423 * Mesg_Token_Tbl - Filled with any errors or warnings
424 * Purpose : Will delete a substitute component record using the
425 * primary unique key.
426 ********************************************************************/
427 PROCEDURE Delete_Row
428 ( p_substitute_component_id IN NUMBER
429 , p_change_notice IN VARCHAR2
430 , p_component_sequence_id IN NUMBER
431 , p_acd_type IN NUMBER
432 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
433 , x_return_status IN OUT NOCOPY VARCHAR2
434 )
435 IS
436 l_processed BOOLEAN;
437 l_return_status VARCHAR2(200);
438 l_err_text VARCHAR2(255);
439 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
440 l_BO_Id VARCHAR2(3) := Bom_Globals.Get_Bo_Identifier;
441 BEGIN
442
443 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
444 THEN
445 Bom_GLOBALS.Check_Approved_For_Process(p_change_notice,
446 Bom_GLOBALS.Get_org_id,
447 l_processed,
448 l_err_text);
449
450 if (l_processed = TRUE) then
451 Bom_GLOBALS.Set_Request_For_approval(p_change_notice,
452 Bom_GLOBALS.Get_org_id,
453 l_err_text);
454 end if;
455 END IF;
456
457 DELETE FROM BOM_SUBSTITUTE_COMPONENTS
458 WHERE SUBSTITUTE_COMPONENT_ID = p_substitute_component_id
459 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
460 AND (( l_BO_Id = Bom_Globals.G_ECO_BO AND
461 ACD_TYPE = p_acd_type
462 ) OR
463 (l_BO_Id = Bom_Globals.G_BOM_BO AND
464 acd_type IS NULL
465 )
466 );
467 BOMPCMBM.Delete_Related_Sub_Comp( p_src_comp_seq => p_component_sequence_id
468 , p_sub_comp_item_id => p_substitute_component_id
469 , x_return_status => x_return_status);
470 --x_return_status := FND_API.G_RET_STS_SUCCESS;
471
472 EXCEPTION
473
474 WHEN OTHERS THEN
475
476 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
477 THEN
478 l_err_text := G_PKG_NAME ||
479 'Utility (Substitute Component Delete_Row)' ||
480 SUBSTR(SQLERRM, 1, 100);
481
482 Error_Handler.Add_Error_Token
483 ( p_Message_Name => NULL
484 , p_Message_Text => l_err_text
485 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
486 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
487 );
488 END IF;
489 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
491
492 END Delete_Row;
493
494 /********************************************************************
495 *
496 * Procedure : Query_Row
497 * Parameters IN : Substitute Component primary key
498 * Parameters OUT: Substitute Component Record of exposed colmuns
499 * Substitute Component record of unexposed columns
500 * Return_Status - Indicating success or faliure
501 * Mesg_Token_Tbl - Filled with any errors or warnings
502 * Purpose : Complete Record will take the Database record and
503 * compare it with the user record and will complete
504 * the user record by filling in those values from the
505 * record that the user has left blank.
506 * Any user filled in columns will not be overwritten
507 * even if the values do not match.
508 ********************************************************************/
509 PROCEDURE Query_Row
510 ( p_substitute_component_id IN NUMBER
511 , p_component_sequence_id IN NUMBER
512 , p_acd_type IN NUMBER
513 , x_Sub_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
514 , x_Sub_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
515 , x_return_status IN OUT NOCOPY VARCHAR2
516 )
517 IS
518 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
519 l_Token_Tbl Error_Handler.Token_Tbl_Type;
520 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
521 BEGIN
522
523 SELECT SUBSTITUTE_ITEM_QUANTITY
524 , ATTRIBUTE_CATEGORY
525 , ATTRIBUTE1
526 , ATTRIBUTE2
527 , ATTRIBUTE3
528 , ATTRIBUTE4
529 , ATTRIBUTE5
530 , ATTRIBUTE6
531 , ATTRIBUTE7
532 , ATTRIBUTE8
533 , ATTRIBUTE9
534 , ATTRIBUTE10
535 , ATTRIBUTE11
536 , ATTRIBUTE12
537 , ATTRIBUTE13
538 , ATTRIBUTE14
539 , ATTRIBUTE15
540 , CHANGE_NOTICE
541 , ACD_TYPE
542 , SUBSTITUTE_COMPONENT_ID
543 , COMPONENT_SEQUENCE_ID
544 , ENFORCE_INT_REQUIREMENTS
545 INTO l_sub_component_rec.substitute_item_quantity
546 , l_sub_component_rec.attribute_category
547 , l_sub_component_rec.attribute1
548 , l_sub_component_rec.attribute2
549 , l_sub_component_rec.attribute3
550 , l_sub_component_rec.attribute4
551 , l_sub_component_rec.attribute5
552 , l_sub_component_rec.attribute6
553 , l_sub_component_rec.attribute7
554 , l_sub_component_rec.attribute8
555 , l_sub_component_rec.attribute9
556 , l_sub_component_rec.attribute10
557 , l_sub_component_rec.attribute11
558 , l_sub_component_rec.attribute12
559 , l_sub_component_rec.attribute13
560 , l_sub_component_rec.attribute14
561 , l_sub_component_rec.attribute15
562 , l_Sub_Component_Rec.Eco_Name
563 , l_Sub_Component_Rec.Acd_Type
564 , x_Sub_comp_Unexp_Rec.Substitute_Component_Id
565 , x_Sub_Comp_Unexp_Rec.Component_Sequence_Id
566 , x_Sub_Comp_Unexp_Rec.enforce_int_requirements_code
567 FROM BOM_SUBSTITUTE_COMPONENTS
568 WHERE SUBSTITUTE_COMPONENT_ID = p_substitute_component_id
569 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
570 AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 1) =
571 NVL(DECODE(p_acd_type, FND_API.G_MISS_NUM, null, p_acd_type), 1)
572 /* Bug 5726557; The code is modified inorder to update an
573 Substitute component when implemneted through an ECO */
574 ;
575
576 IF x_sub_comp_unexp_rec.enforce_int_requirements_code IS NOT NULL AND
577 x_sub_comp_unexp_rec.enforce_int_requirements_code <> FND_API.G_MISS_NUM THEN
578 SELECT meaning INTO l_sub_component_rec.enforce_int_requirements FROM mfg_lookups
579 WHERE lookup_type = 'BOM_ENFORCE_INT_REQUIREMENTS' AND
580 lookup_code = x_sub_comp_unexp_rec.enforce_int_requirements_code;
581 END IF;
582
583 x_Sub_Component_Rec := l_sub_component_rec;
584 x_return_status := Bom_Globals.G_RECORD_FOUND;
585
586 EXCEPTION
587 WHEN NO_DATA_FOUND THEN
588 x_Sub_Component_Rec := l_sub_component_rec;
589 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
590
591 WHEN OTHERS THEN
592 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
593
594 END Query_Row;
595
596 -- Procedure lock_Row
597 --
598
599 PROCEDURE Lock_Row
600 ( x_return_status IN OUT NOCOPY VARCHAR2
601 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
602 , x_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
603 , x_err_text IN OUT NOCOPY VARCHAR2
604 )
605 IS
606 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
607 l_err_text VARCHAR2(255);
608 BEGIN
609 NULL;
610 END Lock_Row;
611
612 PROCEDURE Perform_Writes
613 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
614 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
615 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
616 , x_Return_Status IN OUT NOCOPY VARCHAR2
617 )
618 IS
619 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
620 l_return_status VARCHAR2(1);
621 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
622 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
623 BEGIN
624 l_sub_component_rec := p_sub_component_rec;
625 l_sub_comp_unexp_rec := p_sub_comp_unexp_rec;
626
627 IF l_sub_component_rec.transaction_type IS NULL THEN
628 Error_Handler.Write_Debug ('In SCOMP Perform Writes ...is NULL');
629 ELSIF l_sub_component_rec.transaction_type = FND_API.G_MISS_CHAR THEN
630 Error_Handler.Write_Debug ('In SCOMP Perform Writes ...is MISSING');
631 ELSE
632 Error_Handler.Write_Debug('In SCOMP Perform Writes...'||l_sub_component_rec.transaction_type);
633 END IF;
634 IF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_CREATE
635 THEN
636 Insert_Row( p_sub_component_rec => l_sub_component_rec
637 , p_Sub_Comp_Unexp_Rec => l_sub_comp_unexp_rec
638 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
639 , x_return_status => l_return_status
640 );
641 ELSIF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
642 THEN
643 Update_Row( p_sub_component_rec => l_sub_component_rec
644 , p_Sub_Comp_Unexp_Rec => l_sub_comp_unexp_rec
645 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
646 , x_return_status => l_return_status
647 );
648
649 ELSIF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_DELETE
650 THEN
651 Delete_Row
652 ( p_substitute_component_id =>
653 l_sub_comp_unexp_rec.substitute_component_id
654 , p_change_notice =>
655 l_sub_component_rec.eco_name
656 , p_component_sequence_id =>
657 l_sub_comp_unexp_rec.component_sequence_id
658 , p_acd_type =>
659 l_sub_component_rec.acd_type
660 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
661 , x_return_status => l_return_status
662 );
663
664 END IF;
665 Error_Handler.Write_Debug ('After SCOMP Perform Writes ...'||nvl(l_sub_component_rec.transaction_type,'NULL'));
666
667 x_return_status := l_return_status;
668 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
669
670 END Perform_Writes;
671
672 /*
673 ** Procedures for BOM Business Object
674 */
675 FUNCTION Convert_Miss_To_Null
676 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
677 ) RETURN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
678 IS
679 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
680 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
681 l_bom_sub_component_rec Bom_Bo_Pub.Bom_Sub_Component_Rec_Type;
682 l_bom_sub_comp_unexp_rec Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type;
683 BEGIN
684 --
685 -- Convert the BOM record
686 --
687 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
688 ( p_bom_sub_component_rec => p_bom_sub_component_rec
689 , x_sub_component_rec => l_sub_component_rec
690 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
691 );
692
693 l_sub_component_rec :=
694 Convert_Miss_To_Null
695 ( p_sub_component_rec => l_sub_component_rec );
696
697 --
698 -- Convert the ECO record back
699 --
700 Bom_Bo_Pub.Convert_EcoSComp_To_BomSComp
701 ( p_sub_component_rec => l_sub_component_rec
702 , x_bom_sub_component_rec => l_bom_sub_component_rec
703 , x_bom_sub_comp_unexp_rec => l_bom_sub_comp_unexp_rec
704 );
705
706 END Convert_Miss_To_Null;
707
708 -- Function Query_Row
709
710 PROCEDURE Query_Row
711 ( p_substitute_component_id IN NUMBER
712 , p_component_sequence_id IN NUMBER
713 , p_acd_type IN NUMBER
714 , x_bom_Sub_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
715 , x_bom_Sub_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
716 , x_return_status IN OUT NOCOPY VARCHAR2
717 )
718 IS
719 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
720 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
721 BEGIN
722
723 --
724 -- Call Query Row
725 --
726 Query_Row
727 ( p_substitute_component_id => p_substitute_component_id
728 , p_component_sequence_id => p_component_sequence_id
729 , p_acd_type => p_acd_type
730 , x_sub_component_rec => l_sub_component_rec
731 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
732 , x_return_status => x_return_status
733 );
734
735 --
736 -- Convert the ECO record before retuning
737 --
738 Bom_Bo_Pub.Convert_EcoSComp_To_BomSComp
739 ( p_sub_component_rec => l_sub_component_rec
740 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
741 , x_bom_sub_component_rec => x_bom_sub_component_rec
742 , x_bom_sub_comp_unexp_rec => x_bom_sub_comp_unexp_rec
743 );
744
745 END Query_Row;
746
747 PROCEDURE Perform_Writes
748 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
749 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
750 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
751 , x_Return_Status IN OUT NOCOPY VARCHAR2
752 )
753 IS
754 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
755 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
756 BEGIN
757 --
758 -- Convert the BOM Record to ECO Record
759 --
760 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
761 ( p_bom_sub_component_rec => p_bom_sub_component_rec
762 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
763 , x_sub_component_rec => l_sub_component_rec
764 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
765 );
766
767 -- Call Perform Writes
768
769 Perform_Writes
770 ( p_sub_component_rec => l_sub_component_rec
771 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
772 , x_return_status => x_return_status
773 , x_mesg_token_tbl => x_mesg_token_tbl
774 );
775
776 END Perform_Writes;
777
778
779 END BOM_Sub_Component_Util;