[Home] [Help]
PACKAGE BODY: APPS.BOM_SUB_COMPONENT_UTIL
Source
1 PACKAGE BODY BOM_Sub_Component_Util AS
2 /* $Header: BOMUSBCB.pls 120.4.12010000.3 2010/02/13 01:41:55 umajumde 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
114
111 IF l_sub_component_rec.attribute6 = FND_API.G_MISS_CHAR THEN
112 l_sub_component_rec.attribute6 := NULL;
113 END IF;
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
243 p_sub_comp_Unexp_rec.substitute_component_id
240 , PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
241 , PROGRAM_UPDATE_DATE = SYSDATE
242 WHERE 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 --following function has been added for bug 7713832
297 FUNCTION Common_CompSeqIdSC( p_comp_seq_id NUMBER)
298 RETURN NUMBER
299 IS
300 l_src_comp_seq_id NUMBER;
301
302 BEGIN
303
304 SELECT common_component_sequence_id
305 INTO l_src_comp_seq_id
306 FROM bom_components_b
307 WHERE component_sequence_id = p_comp_seq_id
308 and component_sequence_id <> common_component_sequence_id;
309
310 RETURN l_src_comp_seq_id;
311
312 EXCEPTION
313 WHEN NO_DATA_FOUND THEN
314 RETURN NULL;
315
316 WHEN OTHERS THEN
317 RETURN NULL;
318 END;
319
320 /********************************************************************
321 *
322 * Procedure : Insert_Row
323 * Parameters IN : Substitute Component Record as given by the User
324 * Sub. Comps Unexposed Cols. Record
325 * Parameters OUT: Substitute Component Record
326 * Return_Status - Indicating success or faliure
327 * Mesg_Token_Tbl - Filled with any errors or warnings
328 * Purpose : Will Insert a new substitute component record in
329 * Bom_Substitute_Components table.
330 *
331 ********************************************************************/
332 PROCEDURE Insert_Row
333 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
334 , p_Sub_Comp_Unexp_Rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
335 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
336 , x_return_status IN OUT NOCOPY VARCHAR2
337 )
338 IS
339 l_processed BOOLEAN;
340 l_err_text VARCHAR2(255);
341 l_return_status VARCHAR2(200);
342 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
343 l_src_comp_seq_id NUMBER := NULL; --Bug 7713832
344
345 BEGIN
346
347 Error_Handler.Write_Debug ('Inserting SCOMP Perform Writes ...');
348 --Bug 7712832 changes start
349 IF Bom_Globals.Get_Caller_Type = 'MIGRATION' THEN
350 l_src_comp_seq_id := Common_CompSeqIdSC( p_comp_seq_id => p_sub_comp_Unexp_rec.component_sequence_id
351 );
352 END IF;
353 --Bug 7712832 changes end
354
355 INSERT INTO BOM_SUBSTITUTE_COMPONENTS
356 ( SUBSTITUTE_COMPONENT_ID
357 , LAST_UPDATE_DATE
358 , LAST_UPDATED_BY
359 , CREATION_DATE
360 , CREATED_BY
361 , LAST_UPDATE_LOGIN
362 , SUBSTITUTE_ITEM_QUANTITY
363 , COMPONENT_SEQUENCE_ID
364 , ACD_TYPE
365 , CHANGE_NOTICE
366 , REQUEST_ID
367 , PROGRAM_APPLICATION_ID
368 , PROGRAM_UPDATE_DATE
369 , ATTRIBUTE_CATEGORY
370 , ATTRIBUTE1
371 , ATTRIBUTE2
372 , ATTRIBUTE3
373 , ATTRIBUTE4
374 , ATTRIBUTE5
375 , ATTRIBUTE6
376 , ATTRIBUTE7
377 , ATTRIBUTE8
378 , ATTRIBUTE9
379 , ATTRIBUTE10
380 , ATTRIBUTE11
381 , ATTRIBUTE12
385 , PROGRAM_ID
382 , ATTRIBUTE13
383 , ATTRIBUTE14
384 , ATTRIBUTE15
386 , Original_System_Reference
387 , Enforce_Int_Requirements
388 , Common_Component_Sequence_Id --bug 7713832
389 )
390 VALUES
391 ( p_sub_comp_unexp_rec.substitute_component_id
392 , SYSDATE
393 , Bom_globals.Get_User_Id
394 , SYSDATE
395 , Bom_Globals.Get_User_Id
396 , Bom_Globals.Get_User_Id
397 , p_sub_component_rec.substitute_item_quantity
398 , p_sub_comp_Unexp_rec.component_sequence_id
399 , p_sub_component_rec.acd_type
400 , p_sub_component_rec.Eco_Name
401 , Fnd_Global.Conc_Request_Id /* Request Id */
402 , Bom_Globals.Get_Prog_AppId
403 , SYSDATE
404 , p_sub_component_rec.attribute_category
405 , p_sub_component_rec.attribute1
406 , p_sub_component_rec.attribute2
407 , p_sub_component_rec.attribute3
408 , p_sub_component_rec.attribute4
409 , p_sub_component_rec.attribute5
410 , p_sub_component_rec.attribute6
411 , p_sub_component_rec.attribute7
412 , p_sub_component_rec.attribute8
413 , p_sub_component_rec.attribute9
414 , p_sub_component_rec.attribute10
415 , p_sub_component_rec.attribute11
416 , p_sub_component_rec.attribute12
417 , p_sub_component_rec.attribute13
418 , p_sub_component_rec.attribute14
419 , p_sub_component_rec.attribute15
420 , Bom_Globals.Get_Prog_Id
421 , p_sub_component_rec.Original_System_Reference
422 , p_sub_comp_Unexp_rec.enforce_int_requirements_code
423 , l_src_comp_seq_id --bug 7713832
424 );
425 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN --Bug 7713832
426 BOMPCMBM.Insert_Related_Sub_Comp(p_component_sequence_id => p_sub_comp_Unexp_rec.component_sequence_id
427 , p_sub_comp_item_id => p_sub_comp_unexp_rec.substitute_component_id
428 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
429 , x_Return_Status => x_return_status);
430 END IF;
431 --x_return_status := FND_API.G_RET_STS_SUCCESS;
432
433 EXCEPTION
434
435 WHEN OTHERS THEN
436 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
437 THEN
438 l_err_text := G_PKG_NAME ||
439 'Utility (Substitute Component Insert)' ||
440 SUBSTR(SQLERRM, 1, 100);
441 Error_Handler.Add_Error_Token
442 ( p_Message_Name => NULL
443 , p_Message_text => l_err_text
444 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
445 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
446 );
447 END IF;
448 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
449 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
450
451 END Insert_Row;
452
453
454 /********************************************************************
455 *
456 * Procedure : Delete_Row
457 * Parameters IN : Primary Key of Substitute Component Table
458 * Parameters OUT: Return_Status - Indicating success or faliure
459 * Mesg_Token_Tbl - Filled with any errors or warnings
460 * Purpose : Will delete a substitute component record using the
461 * primary unique key.
462 ********************************************************************/
463 PROCEDURE Delete_Row
464 ( p_substitute_component_id IN NUMBER
465 , p_change_notice IN VARCHAR2
466 , p_component_sequence_id IN NUMBER
467 , p_acd_type IN NUMBER
468 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
469 , x_return_status IN OUT NOCOPY VARCHAR2
470 )
471 IS
472 l_processed BOOLEAN;
473 l_return_status VARCHAR2(200);
474 l_err_text VARCHAR2(255);
475 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
476 l_BO_Id VARCHAR2(3) := Bom_Globals.Get_Bo_Identifier;
477 BEGIN
478
479 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
480 THEN
481 Bom_GLOBALS.Check_Approved_For_Process(p_change_notice,
482 Bom_GLOBALS.Get_org_id,
483 l_processed,
484 l_err_text);
485
486 if (l_processed = TRUE) then
487 Bom_GLOBALS.Set_Request_For_approval(p_change_notice,
488 Bom_GLOBALS.Get_org_id,
489 l_err_text);
490 end if;
491 END IF;
492
493 DELETE FROM BOM_SUBSTITUTE_COMPONENTS
494 WHERE SUBSTITUTE_COMPONENT_ID = p_substitute_component_id
495 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
496 AND (( l_BO_Id = Bom_Globals.G_ECO_BO AND
497 ACD_TYPE = p_acd_type
498 ) OR
499 (l_BO_Id = Bom_Globals.G_BOM_BO AND
500 acd_type IS NULL
501 )
502 );
503 BOMPCMBM.Delete_Related_Sub_Comp( p_src_comp_seq => p_component_sequence_id
504 , p_sub_comp_item_id => p_substitute_component_id
505 , x_return_status => x_return_status);
506 --x_return_status := FND_API.G_RET_STS_SUCCESS;
507
508 EXCEPTION
509
510 WHEN OTHERS THEN
511
512 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
513 THEN
514 l_err_text := G_PKG_NAME ||
515 'Utility (Substitute Component Delete_Row)' ||
519 ( p_Message_Name => NULL
516 SUBSTR(SQLERRM, 1, 100);
517
518 Error_Handler.Add_Error_Token
520 , p_Message_Text => l_err_text
521 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
522 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
523 );
524 END IF;
525 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
527
528 END Delete_Row;
529
530 /********************************************************************
531 *
532 * Procedure : Query_Row
533 * Parameters IN : Substitute Component primary key
534 * Parameters OUT: Substitute Component Record of exposed colmuns
535 * Substitute Component record of unexposed columns
536 * Return_Status - Indicating success or faliure
537 * Mesg_Token_Tbl - Filled with any errors or warnings
538 * Purpose : Complete Record will take the Database record and
539 * compare it with the user record and will complete
540 * the user record by filling in those values from the
541 * record that the user has left blank.
542 * Any user filled in columns will not be overwritten
543 * even if the values do not match.
544 ********************************************************************/
545 PROCEDURE Query_Row
546 ( p_substitute_component_id IN NUMBER
547 , p_component_sequence_id IN NUMBER
548 , p_acd_type IN NUMBER
549 , x_Sub_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
550 , x_Sub_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
551 , x_return_status IN OUT NOCOPY VARCHAR2
552 )
553 IS
554 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
555 l_Token_Tbl Error_Handler.Token_Tbl_Type;
556 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
557 BEGIN
558
559 SELECT SUBSTITUTE_ITEM_QUANTITY
560 , ATTRIBUTE_CATEGORY
561 , ATTRIBUTE1
562 , ATTRIBUTE2
563 , ATTRIBUTE3
564 , ATTRIBUTE4
565 , ATTRIBUTE5
566 , ATTRIBUTE6
567 , ATTRIBUTE7
568 , ATTRIBUTE8
569 , ATTRIBUTE9
570 , ATTRIBUTE10
571 , ATTRIBUTE11
572 , ATTRIBUTE12
573 , ATTRIBUTE13
574 , ATTRIBUTE14
575 , ATTRIBUTE15
576 , CHANGE_NOTICE
577 , ACD_TYPE
578 , SUBSTITUTE_COMPONENT_ID
579 , COMPONENT_SEQUENCE_ID
580 , ENFORCE_INT_REQUIREMENTS
581 INTO l_sub_component_rec.substitute_item_quantity
582 , l_sub_component_rec.attribute_category
583 , l_sub_component_rec.attribute1
584 , l_sub_component_rec.attribute2
585 , l_sub_component_rec.attribute3
586 , l_sub_component_rec.attribute4
587 , l_sub_component_rec.attribute5
588 , l_sub_component_rec.attribute6
589 , l_sub_component_rec.attribute7
590 , l_sub_component_rec.attribute8
591 , l_sub_component_rec.attribute9
592 , l_sub_component_rec.attribute10
593 , l_sub_component_rec.attribute11
594 , l_sub_component_rec.attribute12
595 , l_sub_component_rec.attribute13
596 , l_sub_component_rec.attribute14
597 , l_sub_component_rec.attribute15
598 , l_Sub_Component_Rec.Eco_Name
599 , l_Sub_Component_Rec.Acd_Type
600 , x_Sub_comp_Unexp_Rec.Substitute_Component_Id
601 , x_Sub_Comp_Unexp_Rec.Component_Sequence_Id
602 , x_Sub_Comp_Unexp_Rec.enforce_int_requirements_code
603 FROM BOM_SUBSTITUTE_COMPONENTS
604 WHERE SUBSTITUTE_COMPONENT_ID = p_substitute_component_id
605 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
606 AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 1) =
607 NVL(DECODE(p_acd_type, FND_API.G_MISS_NUM, null, p_acd_type), 1)
608 /* Bug 5726557; The code is modified inorder to update an
609 Substitute component when implemneted through an ECO */
610 ;
611
612 IF x_sub_comp_unexp_rec.enforce_int_requirements_code IS NOT NULL AND
613 x_sub_comp_unexp_rec.enforce_int_requirements_code <> FND_API.G_MISS_NUM THEN
614 SELECT meaning INTO l_sub_component_rec.enforce_int_requirements FROM mfg_lookups
615 WHERE lookup_type = 'BOM_ENFORCE_INT_REQUIREMENTS' AND
616 lookup_code = x_sub_comp_unexp_rec.enforce_int_requirements_code;
617 END IF;
618
619 x_Sub_Component_Rec := l_sub_component_rec;
620 x_return_status := Bom_Globals.G_RECORD_FOUND;
621
622 EXCEPTION
623 WHEN NO_DATA_FOUND THEN
624 x_Sub_Component_Rec := l_sub_component_rec;
625 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
626
627 WHEN OTHERS THEN
628 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
629
630 END Query_Row;
631
632 -- Procedure lock_Row
633 --
634
635 PROCEDURE Lock_Row
636 ( x_return_status IN OUT NOCOPY VARCHAR2
637 , p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
638 , x_sub_component_rec IN OUT NOCOPY Bom_Bo_Pub.Sub_Component_Rec_Type
639 , x_err_text IN OUT NOCOPY VARCHAR2
640 )
641 IS
642 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
643 l_err_text VARCHAR2(255);
644 BEGIN
645 NULL;
646 END Lock_Row;
647
648 PROCEDURE Perform_Writes
652 , x_Return_Status IN OUT NOCOPY VARCHAR2
649 ( p_sub_component_rec IN Bom_Bo_Pub.Sub_Component_Rec_Type
650 , p_sub_comp_unexp_rec IN Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type
651 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
653 )
654 IS
655 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
656 l_return_status VARCHAR2(1);
657 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
658 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
659 BEGIN
660 l_sub_component_rec := p_sub_component_rec;
661 l_sub_comp_unexp_rec := p_sub_comp_unexp_rec;
662
663 IF l_sub_component_rec.transaction_type IS NULL THEN
664 Error_Handler.Write_Debug ('In SCOMP Perform Writes ...is NULL');
665 ELSIF l_sub_component_rec.transaction_type = FND_API.G_MISS_CHAR THEN
666 Error_Handler.Write_Debug ('In SCOMP Perform Writes ...is MISSING');
667 ELSE
668 Error_Handler.Write_Debug('In SCOMP Perform Writes...'||l_sub_component_rec.transaction_type);
669 END IF;
670 IF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_CREATE
671 THEN
672 Insert_Row( p_sub_component_rec => l_sub_component_rec
673 , p_Sub_Comp_Unexp_Rec => l_sub_comp_unexp_rec
674 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
675 , x_return_status => l_return_status
676 );
677 ELSIF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
678 THEN
679 Update_Row( p_sub_component_rec => l_sub_component_rec
680 , p_Sub_Comp_Unexp_Rec => l_sub_comp_unexp_rec
681 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
682 , x_return_status => l_return_status
683 );
684
685 ELSIF l_sub_component_rec.transaction_type = Bom_Globals.G_OPR_DELETE
686 THEN
687 Delete_Row
688 ( p_substitute_component_id =>
689 l_sub_comp_unexp_rec.substitute_component_id
690 , p_change_notice =>
691 l_sub_component_rec.eco_name
692 , p_component_sequence_id =>
693 l_sub_comp_unexp_rec.component_sequence_id
694 , p_acd_type =>
695 l_sub_component_rec.acd_type
696 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
697 , x_return_status => l_return_status
698 );
699
700 END IF;
701 Error_Handler.Write_Debug ('After SCOMP Perform Writes ...'||nvl(l_sub_component_rec.transaction_type,'NULL'));
702
703 x_return_status := l_return_status;
704 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
705
706 END Perform_Writes;
707
708 /*
709 ** Procedures for BOM Business Object
710 */
711 FUNCTION Convert_Miss_To_Null
712 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
713 ) RETURN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
714 IS
715 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
716 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
717 l_bom_sub_component_rec Bom_Bo_Pub.Bom_Sub_Component_Rec_Type;
718 l_bom_sub_comp_unexp_rec Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type;
719 BEGIN
720 --
721 -- Convert the BOM record
722 --
723 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
724 ( p_bom_sub_component_rec => p_bom_sub_component_rec
725 , x_sub_component_rec => l_sub_component_rec
726 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
727 );
728
729 l_sub_component_rec :=
730 Convert_Miss_To_Null
731 ( p_sub_component_rec => l_sub_component_rec );
732
733 --
734 -- Convert the ECO record back
735 --
736 Bom_Bo_Pub.Convert_EcoSComp_To_BomSComp
737 ( p_sub_component_rec => l_sub_component_rec
738 , x_bom_sub_component_rec => l_bom_sub_component_rec
739 , x_bom_sub_comp_unexp_rec => l_bom_sub_comp_unexp_rec
740 );
741
742 END Convert_Miss_To_Null;
743
744 -- Function Query_Row
745
746 PROCEDURE Query_Row
747 ( p_substitute_component_id IN NUMBER
748 , p_component_sequence_id IN NUMBER
749 , p_acd_type IN NUMBER
750 , x_bom_Sub_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
751 , x_bom_Sub_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
752 , x_return_status IN OUT NOCOPY VARCHAR2
753 )
754 IS
755 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
756 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
757 BEGIN
758
759 --
760 -- Call Query Row
761 --
762 Query_Row
763 ( p_substitute_component_id => p_substitute_component_id
764 , p_component_sequence_id => p_component_sequence_id
765 , p_acd_type => p_acd_type
766 , x_sub_component_rec => l_sub_component_rec
767 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
768 , x_return_status => x_return_status
769 );
770
771 --
772 -- Convert the ECO record before retuning
773 --
774 Bom_Bo_Pub.Convert_EcoSComp_To_BomSComp
775 ( p_sub_component_rec => l_sub_component_rec
776 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
777 , x_bom_sub_component_rec => x_bom_sub_component_rec
778 , x_bom_sub_comp_unexp_rec => x_bom_sub_comp_unexp_rec
779 );
780
781 END Query_Row;
782
783 PROCEDURE Perform_Writes
784 ( p_bom_sub_component_rec IN Bom_Bo_Pub.Bom_Sub_Component_Rec_Type
785 , p_bom_sub_comp_unexp_rec IN Bom_Bo_Pub.Bom_Sub_Comp_Unexp_Rec_Type
786 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
787 , x_Return_Status IN OUT NOCOPY VARCHAR2
788 )
789 IS
790 l_sub_component_rec Bom_Bo_Pub.Sub_Component_Rec_Type;
791 l_sub_comp_unexp_rec Bom_Bo_Pub.Sub_Comp_Unexposed_Rec_Type;
792 BEGIN
793 --
794 -- Convert the BOM Record to ECO Record
795 --
796 Bom_Bo_Pub.Convert_BomSComp_To_EcoSComp
797 ( p_bom_sub_component_rec => p_bom_sub_component_rec
798 , p_bom_sub_comp_unexp_rec => p_bom_sub_comp_unexp_rec
799 , x_sub_component_rec => l_sub_component_rec
800 , x_sub_comp_unexp_rec => l_sub_comp_unexp_rec
801 );
802
803 -- Call Perform Writes
804
805 Perform_Writes
806 ( p_sub_component_rec => l_sub_component_rec
807 , p_sub_comp_unexp_rec => l_sub_comp_unexp_rec
808 , x_return_status => x_return_status
809 , x_mesg_token_tbl => x_mesg_token_tbl
810 );
811
812 END Perform_Writes;
813
814
815 END BOM_Sub_Component_Util;