[Home] [Help]
PACKAGE BODY: APPS.BOMPCMBM
Source
1 PACKAGE BODY BOMPCMBM AS
2 /* $Header: BOMCMBMB.pls 120.27.12010000.2 2008/11/07 06:05:53 jewen ship $ */
3
4 /*==========================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 +===========================================================================+
8 | |
9 | File Name : BOMCMBMB.pls |
10 | DESCRIPTION : This file is a packaged body for creating
11 | common bill(s) for the following organization scope :
12 | a) Single organization
13 | b) Organization Hierarchy
14 | c) All Organizations
15 | Parameters: scope 1 - Single Organization, 2-Org Hierarchy
16 | 3 - All Orgs
17 | org_hierarchy Organization Hierarchy
18 | Current_org_id Organization from where the concprogram launch
19 | Common_item_from Item from which commoning to be done
20 | alternate alternate bom designator of the commonitemfrom
21 | common_item_to Item to which commoning to be done for scope=1
22 | common_org_to Org to which commoning to be done for scope=1
23 | error_code error code
24 | error_msg error message
25 |
26 | HISTORY: ..-SEP-03 odaboval added procedures Event_Acknowledgement
27 | 06-May-05 Abhishek Rudresh Common BOM Attr updates
28 +==========================================================================*/
29
30 -- ERES change begins :
31
32 G_PKG_NAME VARCHAR2(30) := 'BOMPCMBM';
33
34 PROCEDURE Event_Acknowledgement( p_event_name IN VARCHAR2
35 , p_event_key IN VARCHAR2
36 , p_event_status IN VARCHAR2)
37 IS
38
39 l_erecord_id NUMBER;
40 l_return_status VARCHAR2(2);
41 l_msg_count NUMBER;
42 l_msg_data VARCHAR2(2000);
43 l_dummy_cnt NUMBER;
44 l_trans_status VARCHAR2(10);
45 l_ackn_by VARCHAR2(200);
46 SEND_ACKN_ERROR EXCEPTION;
47
48 BEGIN
49
50 -- First Get the parent event details (BillCreate/BillUpdate)
51 -- If the call fails or returns and error, the exception is not catched.
52 QA_EDR_STANDARD.GET_ERECORD_ID
53 ( p_api_version => 1.0
54 , p_init_msg_list => FND_API.G_TRUE
55 , x_return_status => l_return_status
56 , x_msg_count => l_msg_count
57 , x_msg_data => l_msg_data
58 , p_event_name => p_event_name
59 , p_event_key => p_event_key
60 , x_erecord_id => l_erecord_id);
61
62 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Checking event(commonBill)='||p_event_name||', event_key='||p_event_key||', erecord_id='||l_erecord_id||', msg_cnt='||l_msg_count);
63
64 IF (NVL( l_erecord_id, -1) >0)
65 THEN
66 IF (p_event_status = 'SUCCESS')
67 THEN
68 l_trans_status := 'SUCCESS';
69 ELSE
70 l_trans_status := 'ERROR';
71 END IF;
72
73 -- Get message that will be send to SEND_ACKN :
74 FND_MESSAGE.SET_NAME('ENG', 'BOM_ERES_ACKN_BILL');
75 l_ackn_by := FND_MESSAGE.GET;
76
77 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Acknowledging eRecord_id='||l_erecord_id||' with status='||l_trans_status);
78 QA_EDR_STANDARD.SEND_ACKN
79 ( p_api_version => 1.0
80 , p_init_msg_list => FND_API.G_TRUE
81 , x_return_status => l_return_status
82 , x_msg_count => l_msg_count
83 , x_msg_data => l_msg_data
84 , p_event_name => p_event_name
85 , p_event_key => p_event_key
86 , p_erecord_id => l_erecord_id
87 , p_trans_status => l_trans_status
88 , p_ackn_by => l_ackn_by
89 , p_ackn_note => p_event_name||', '||p_event_key
90 , p_autonomous_commit => FND_API.G_TRUE);
91
92 FND_FILE.PUT_LINE(FND_FILE.LOG, 'After QA_EDR_STANDARD.SEND_ACKN msg='||l_msg_count);
93
94 IF (l_return_status <> FND_API.G_TRUE)
95 THEN
96 RAISE SEND_ACKN_ERROR;
97 END IF;
98
99 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Normal end of acknowledgement part ');
100 ELSE
101 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No Acknowledgement.');
102 END IF;
103
104 EXCEPTION
105 WHEN SEND_ACKN_ERROR THEN
106 FND_MSG_PUB.Get(
107 p_msg_index => 1,
108 p_data => l_msg_data,
109 p_encoded => FND_API.G_FALSE,
110 p_msg_index_out => l_dummy_cnt);
111 FND_FILE.PUT_LINE(FND_FILE.LOG, 'ACKN Error: '||l_msg_data);
112
113 WHEN OTHERS THEN
114 l_msg_data := 'ACKN Others Error='||SQLERRM;
115 FND_FILE.PUT_LINE(FND_FILE.LOG, l_msg_data);
116
117
118 END Event_Acknowledgement;
119 -- ERES change ends
120
121 PROCEDURE create_common_bills(
122 ERRBUF IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
123 RETCODE IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
124 scope IN NUMBER DEFAULT 1,
125 org_hierarchy IN VARCHAR2 DEFAULT NULL,
126 current_org_id IN NUMBER,
127 common_item_from IN NUMBER,
128 alternate IN VARCHAR2 DEFAULT NULL,
129 common_org_to IN NUMBER DEFAULT NULL,
130 common_item_to IN NUMBER DEFAULT NULL
131 , enable_attrs_update IN VARCHAR2
132 ) IS
133 t_org_code_list INV_OrgHierarchy_PVT.OrgID_tbl_type;
134 l_org_name VARCHAR2(60) ;
135 common_item_from_name VARCHAR2(200) ;
136 common_item_to_name VARCHAR2(200) ;
137 common_org_to_code VARCHAR2(4) ;
138 l_bill_sequence_id NUMBER ;
139 l_bill_exists NUMBER ;
140 l_assembly_type NUMBER ;
141 l_org_code VARCHAR2(4) ;
142 l_org_code_to VARCHAR2(4) ;
143 l_organization_code VARCHAR2(4) ;
144 l_assy_item_name VARCHAR2(200) ;
145 l_assembly_item_name VARCHAR2(200) ;
146 N NUMBER := 0 ;
147 I NUMBER := 1 ;
148 K NUMBER := 1 ;
149 item_not_found NUMBER := 0 ;
150 l_return_status VARCHAR2(1) ;
151 l_msg_count NUMBER;
152 starting_org_counter NUMBER ;
153 success_counter NUMBER := 0 ;
154 failure_counter NUMBER := 0 ;
155 conc_status BOOLEAN ;
156 Current_Error_Code Varchar2(20) := NULL;
157 msg VARCHAR2(2000) ;
158
159 l_bom_header_rec Bom_Bo_Pub.Bom_Head_Rec_Type ;
160 l_bom_revision_tbl Bom_Bo_Pub.Bom_Revision_Tbl_Type ;
161 l_bom_component_tbl Bom_Bo_Pub.Bom_Comps_Tbl_Type ;
162 l_bom_sub_component_tbl Bom_Bo_Pub.Bom_Sub_Component_Tbl_Type;
163 l_bom_ref_designator_tbl Bom_Bo_Pub.Bom_Ref_Designator_Tbl_type;
164 l_error_tbl Error_Handler.Error_Tbl_Type ;
165
166 /* Bug 3171435 fix to mainline(3386399) */
167 l_bill_seq_id NUMBER;
168 delete_group_id NUMBER;
169 delete_entity_id NUMBER;
170 l_item_id NUMBER;
171 l_org_id NUMBER;
172 l_item_desc VARCHAR2(240);
173 to_proceed NUMBER := 0;
174 common_bill_seq_id NUMBER ;
175 bill_seq_id NUMBER;
176 del_group_name Varchar2(7);
177 delete_error_rec NUMBER := 0;
178 ERROR_MSG VARCHAR2(200);
179 RETCOD VARCHAR2(10);
180 /* End Bug 3171435 fix to mainline(3386399) */
181
182
183 Hierarchy_not_specified EXCEPTION;
184 invalid_common_itemorg_to EXCEPTION ;
185 same_common_itemorg_to EXCEPTION;
186
187 -- ERES change begins
188 l_erecord_id NUMBER;
189 -- ERES change ends
190 BEGIN
191 /* Print the list of parameters */
192 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
193 FND_FILE.PUT_LINE( FND_FILE.LOG,'SCOPE='||to_char(scope));
194 FND_FILE.PUT_LINE( FND_FILE.LOG,'ORG_HIERARCHY='||org_hierarchy);
195 FND_FILE.PUT_LINE( FND_FILE.LOG,'CURRENT_ORG_ID='||to_char(current_org_id));
196 FND_FILE.PUT_LINE( FND_FILE.LOG,'COMMON_ITEM_FROM='||to_char(common_item_from));
197 FND_FILE.PUT_LINE( FND_FILE.LOG,'ALTERNATE='||alternate);
198 FND_FILE.PUT_LINE( FND_FILE.LOG,'COMMON_ORG_TO='||to_char(common_org_to));
199 FND_FILE.PUT_LINE( FND_FILE.LOG,'COMMON_ITEM_TO='||to_char(common_item_to));
200 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
201
202 /* Make sure the right set of parameter are passed */
203 IF (scope = 2) AND (org_hierarchy IS NULL) THEN
204 raise Hierarchy_not_specified ;
205 END IF ;
206
207 IF (scope = 1) AND ((common_item_to IS NULL) OR (common_org_to IS NULL)) THEN
208 raise invalid_common_itemorg_to ;
209 END IF ;
210
211 /* Get the "common item from" item name */
212 SELECT concatenated_segments
213 INTO common_item_from_name
214 FROM MTL_SYSTEM_ITEMS_VL
215 WHERE inventory_item_id = common_item_from
216 AND organization_id = current_org_id;
217
218 /* Get the "common org to" Organization code */
219 IF (scope = 1) AND (common_org_to is not null) THEN
220 SELECT organization_code
221 INTO common_org_to_code
222 FROM ORG_ORGANIZATION_DEFINITIONS
223 WHERE Organization_id = common_org_to ;
224 END IF ;
225
226 /* Get the "common Item to" item id */
227 IF (scope = 1) AND (common_item_to is not null) then
228 SELECT concatenated_segments, description
229 INTO common_item_to_name,l_item_desc
230 FROM MTL_SYSTEM_ITEMS_VL
231 WHERE inventory_item_id = common_item_to
232 AND organization_id = common_org_to ;
233 END IF ;
234
235
236 IF ((scope = 1) AND (common_org_to = current_org_id) AND (common_item_from = common_item_to)) THEN
237 raise same_common_itemorg_to ;
238 END IF ;
239
240 /* Get bill_sequence_id */
241 SELECT bill_sequence_id,assembly_type
242 INTO l_bill_sequence_id,l_assembly_type
243 FROM BOM_STRUCTURES_B
244 WHERE organization_id = current_org_id
245 AND assembly_item_id = common_item_from
246 AND nvl(alternate_bom_designator,'NONE') = nvl(alternate,'NONE') ;
247
248 /* If the parameter : Scope = 1 then
249 Take the current Organization
250 else If Scope = 2 is passed then
251 Call the Inventory API to get the list of Organizations
252 under the current Organization Hierarchy
253 else if Scope = 3 is passed then
254 Find the list of all the Organizations (in same item master and to which access is allowed */
255
256 IF ( scope = 2 ) THEN
257 starting_org_counter := 2 ;
258 SELECT organization_name
259 INTO l_org_name
260 FROM org_organization_definitions
261 WHERE organization_id = current_org_id;
262 INV_ORGHIERARCHY_PVT.ORG_HIERARCHY_LIST(org_hierarchy,current_org_id,t_org_code_list) ;
263 ELSIF ( scope = 3 ) THEN
264 starting_org_counter := 1 ;
265 -- bug:4931463 Modified below cursor query to reduce shared memory
266 for C1 in (
267 SELECT
268 orgs.ORGANIZATION_ID
269 FROM
270 ORG_ACCESS_VIEW oav,
271 MTL_SYSTEM_ITEMS_B msi,
272 MTL_PARAMETERS orgs,
273 MTL_PARAMETERS child_org
274 WHERE
275 orgs.ORGANIZATION_ID = oav.ORGANIZATION_ID
276 AND msi.ORGANIZATION_ID = orgs.ORGANIZATION_ID
277 AND orgs.MASTER_ORGANIZATION_ID = child_org.MASTER_ORGANIZATION_ID
278 AND oav.RESPONSIBILITY_ID = FND_PROFILE.Value('RESP_ID')
279 AND oav.RESP_APPLICATION_ID = FND_PROFILE.value('RESP_APPL_ID')
280 AND msi.INVENTORY_ITEM_ID = common_item_from
281 AND orgs.ORGANIZATION_ID <> current_org_id
282 AND child_org.ORGANIZATION_ID = current_org_id
283 )
284 LOOP
285 N:=N+1;
286 t_org_code_list(N) := C1.organization_id;
287 END LOOP;
288 ELSIF ( scope = 1 ) then
289 starting_org_counter := 1 ;
290 t_org_code_list(1) := common_org_to;
291 END IF;
292
293 SELECT organization_code
294 INTO l_org_code
295 FROM ORG_ORGANIZATION_DEFINITIONS
296 WHERE organization_id = current_org_id ;
297
298 /*** Loop through the organization in the list of organizations ***/
299 FOR I in starting_org_counter..t_org_code_list.LAST LOOP
300 FND_FILE.PUT_LINE( FND_FILE.LOG,'Processing Organization : '||t_org_code_list(I));
301
302 SELECT organization_code
303 INTO l_org_code_to
304 FROM ORG_ORGANIZATION_DEFINITIONS
305 WHERE organization_id = t_org_code_list(I) ;
306
307 item_not_found := 0 ;
308 IF (scope = 2 OR scope = 3) THEN
309 BEGIN
310 SELECT concatenated_segments, DESCRIPTION
311 INTO l_assy_item_name, l_item_desc
312 FROM MTL_SYSTEM_ITEMS_VL
313 WHERE organization_id = t_org_code_list(I)
314 AND inventory_item_id = common_item_from ;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 item_not_found := 1 ;
318 END ;
319 END IF;
320
321 IF (item_not_found = 0) THEN
322 IF (SCOPE = 2 OR SCOPE = 3) THEN
323 SELECT COUNT(*)
324 INTO l_bill_exists
325 FROM BOM_STRUCTURES_B
326 WHERE ASSEMBLY_ITEM_ID = common_item_from
327 AND ORGANIZATION_ID = t_org_code_list(I)
328 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
329 ELSIF (SCOPE = 1) THEN
330 SELECT COUNT(*)
331 INTO l_bill_exists
332 FROM BOM_STRUCTURES_B
333 WHERE ASSEMBLY_ITEM_ID = common_item_to
334 AND ORGANIZATION_ID = common_org_to
335 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
336 END IF ;
337
338 /* Modified if condn to include l_bill_exists = 1. This is being done to support
339 Change of ownership of an existing common BOM to an other BOM */
340 IF (l_bill_exists = 0 or l_bill_exists = 1) THEN -- bug 3171435 fix to mainline
341 to_proceed := 0; -- Setting this value to 0 intially.
342
343 /* if single org then use Item to and Org to values else use current Item i.e Item from*/
344 IF (scope = 1) THEN
345 l_assembly_item_name := common_item_to_name ;
346 l_organization_code := common_org_to_code ;
347 l_org_id := common_org_to;
348 l_item_id := common_item_to;
349 ELSE
350 l_assembly_item_name := l_assy_item_name ;
351 l_organization_code := l_org_code_to ;
352 l_org_id := t_org_code_list(I);
353 l_item_id := common_item_from;
354 END IF ;
355
356 /* If bill exists then check if in the org list if the existing bill is common or not .
357 This can be done by comparing the current bill sequence id and common bill sequence id.
358 If they are the same then should not commmon the Bill */
359
360 if (l_bill_exists = 1 and (scope = 2 or scope = 3)) then
361 SELECT common_bill_sequence_id, bill_sequence_id
362 INTO common_bill_seq_id, bill_seq_id
363 FROM BOM_STRUCTURES_B
364 WHERE ASSEMBLY_ITEM_ID = common_item_from
365 AND ORGANIZATION_ID = t_org_code_list(I)
366 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
367
368 elsif(l_bill_exists =1 and scope = 1) then
369 SELECT common_bill_sequence_id, bill_sequence_id
370 INTO common_bill_seq_id, bill_seq_id
371 FROM BOM_STRUCTURES_B
372 WHERE ASSEMBLY_ITEM_ID = common_item_to
373 AND ORGANIZATION_ID = common_org_to
374 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
375 End if;
376
377 If (common_bill_seq_id = bill_seq_id) Then
378 l_bill_exists := 0; -- Cannot delete as existing bill is not common
379 End if;
380
381 if (l_bill_exists = 1) then
382 -- Create delete group records and then call the bom_delete-groups API
383 -- that will delete the common bill record.
384
385 If (((scope = 2 or scope = 3) and (t_org_code_list(I) <> current_org_id))
386 OR (scope = 1)) then
387 SELECT BILL_SEQUENCE_ID
388 INTO l_bill_seq_id
389 FROM BOM_STRUCTURES_B
390 WHERE ASSEMBLY_ITEM_ID = l_item_id
391 AND ORGANIZATION_ID = l_org_id
392 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(alternate,'NONE') ;
393
394 SELECT BOM_DELETE_GROUPS_S.NEXTVAL
395 INTO delete_group_id
396 FROM DUAL;
397
398 SELECT BOM_DELETE_ENTITIES_S.NEXTVAL
399 INTO delete_entity_id
400 FROM dual;
401
402 if (length(delete_group_id) > 7) Then
403 del_group_name := substr(delete_group_id,length(delete_group_id) - 6,7);
404 else
405 del_group_name := delete_group_id;
406 End if;
407
408 INSERT INTO BOM_DELETE_GROUPS
409 (DELETE_GROUP_SEQUENCE_ID,
410 DELETE_GROUP_NAME,
411 DELETE_ORG_TYPE,
412 ORGANIZATION_ID,
413 DELETE_TYPE,
414 ACTION_TYPE,
415 DELETE_COMMON_BILL_FLAG,
416 ENGINEERING_FLAG,
417 LAST_UPDATE_DATE,
418 LAST_UPDATED_BY,
419 CREATION_DATE,
420 CREATED_BY)
421 VALUES
422 (delete_group_id,
423 l_organization_code||del_group_name,
424 1,--bug:4201690 The delete group procedure should get executed for every org.
425 t_org_code_list(I),
426 2,
427 1,
428 2,
429 l_assembly_type,
430 SYSDATE,
431 to_number(FND_PROFILE.Value('USER_ID')),
432 SYSDATE,
433 to_number(FND_PROFILE.Value('USER_ID')));
434
435
436 INSERT INTO bom_delete_entities
437 (DELETE_ENTITY_SEQUENCE_ID,
438 DELETE_GROUP_SEQUENCE_ID,
439 DELETE_ENTITY_TYPE,
440 DELETE_STATUS_TYPE,
441 BILL_SEQUENCE_ID,
442 INVENTORY_ITEM_ID,
443 ORGANIZATION_ID,
444 ITEM_DESCRIPTION,
445 ALTERNATE_DESIGNATOR,
446 ITEM_CONCAT_SEGMENTS,
447 PRIOR_PROCESS_FLAG,
448 PRIOR_COMMIT_FLAG,
449 LAST_UPDATE_DATE,
450 LAST_UPDATED_BY,
451 CREATION_DATE,
452 CREATED_BY)
453 VALUES
454 (delete_entity_id,
455 delete_group_id,
456 2,
457 1,
458 l_bill_seq_id,
459 l_item_id,
460 l_org_id,
461 l_item_desc,
462 nvl(alternate,NULL),
463 l_assembly_item_name,
464 1,
465 1,
466 SYSDATE,
467 to_number(FND_PROFILE.Value('USER_ID')),
468 SYSDATE,
469 to_number(FND_PROFILE.Value('USER_ID')));
470
471 Bom_Delete_Groups_Api.Delete_Groups
472 (ERRBUF => ERROR_MSG,
473 RETCODE => RETCOD,
474 delete_group_id => delete_group_id,
475 action_type => 2,
476 delete_type => 2,
477 archive => 2) ;
478
479 --bug:5235742 Delete groups API will return 1 when one or more entities
480 --can not be deleted.
481 If ( ( RETCOD <> '0' ) AND ( RETCOD <> '1' ) ) Then
482 FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' could not be deleted');
483 FND_FILE.PUT_LINE(FND_FILE.LOG,'') ;
484 failure_counter := failure_counter + 1 ;
485 ROLLBACK ;
486 to_proceed := 1;
487 Else
488 delete_error_rec := 0;
489 Select count(*)
490 into delete_error_rec
491 From bom_delete_errors
492 where DELETE_ENTITY_SEQUENCE_ID = delete_entity_id;
493
494 If (delete_error_rec = 0) then
495 to_proceed := 2;
496 FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' has been deleted succesfully');
497 FND_FILE.PUT_LINE(FND_FILE.LOG,'') ;
498 Else
499 to_proceed := 1;
500 FND_FILE.PUT_LINE(FND_FILE.LOG,'Existing Common Bill For Item ' || l_assembly_item_name|| ' in organization ' || l_organization_code || ' could not be deleted because of delete constraints');
501 FND_FILE.PUT_LINE(FND_FILE.LOG,'') ;
502 failure_counter := failure_counter + 1 ;
503 End if;
504 End if;
505 END IF;
506 END IF;
507 if (to_proceed <> 1) then
508 l_bom_header_rec.assembly_item_name := l_assembly_item_name ;
509 l_bom_header_rec.organization_code := l_organization_code ;
510 l_bom_header_rec.alternate_bom_code := alternate ;
511 l_bom_header_rec.common_assembly_item_name := common_item_from_name ;
512 l_bom_header_rec.common_organization_code := l_org_code;
513 l_bom_header_rec.assembly_comment := NULL ;
514 l_bom_header_rec.assembly_type := l_assembly_type ;
515 l_bom_header_rec.transaction_type := 'CREATE' ;
516 l_bom_header_rec.return_status := NULL ;
517 l_bom_header_rec.attribute_category := NULL ;
518 l_bom_header_rec.attribute1 := NULL ;
519 l_bom_header_rec.attribute2 := NULL ;
520 l_bom_header_rec.attribute3 := NULL ;
521 l_bom_header_rec.attribute4 := NULL ;
522 l_bom_header_rec.attribute5 := NULL ;
523 l_bom_header_rec.attribute6 := NULL ;
524 l_bom_header_rec.attribute7 := NULL ;
525 l_bom_header_rec.attribute8 := NULL ;
526 l_bom_header_rec.attribute9 := NULL ;
527 l_bom_header_rec.attribute10 := NULL ;
528 l_bom_header_rec.attribute11 := NULL ;
529 l_bom_header_rec.attribute12 := NULL ;
530 l_bom_header_rec.attribute13:= NULL ;
531 l_bom_header_rec.attribute14:= NULL ;
532 l_bom_header_rec.attribute15:= NULL ;
533 l_bom_header_rec.original_system_reference := NULL ;
534 l_bom_header_rec.delete_group_name := NULL ;
535 l_bom_header_rec.DG_description := NULL ;
536 --Common BOM enh
537 l_bom_header_rec.enable_attrs_update := enable_attrs_update;
538
539 /** Initialize the System Information **/
540 FND_GLOBAL.apps_initialize
541 (user_id=>FND_PROFILE.Value('USER_ID'),
542 resp_id=>FND_PROFILE.Value('RESP_ID'),
543 resp_appl_id=>FND_PROFILE.Value('RESP_APPL_ID'),
544 security_group_id=>FND_PROFILE.Value('SECURITY_GROUP_ID')) ;
545
546 /** Initialize the message list **/
547 ERROR_HANDLER.INITIALIZE ;
548 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling BOM_BO_PUB.PROCESS_BOM');
549
550 /** Call the BOM Business Object **/
551 BOM_BO_PUB.PROCESS_BOM
552 (
553 p_bo_identifier => 'BOM',
554 p_api_version_number => 1.0,
555 p_init_msg_list => FALSE,
556 p_bom_header_rec => l_bom_header_rec,
557 p_bom_revision_tbl => l_bom_revision_tbl,
558 p_bom_component_tbl => l_bom_component_tbl,
559 p_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
560 p_bom_sub_component_tbl => l_bom_sub_component_tbl,
561 x_bom_header_rec => l_bom_header_rec,
562 x_bom_revision_tbl => l_bom_revision_tbl,
563 x_bom_component_tbl => l_bom_component_tbl,
564 x_bom_ref_designator_tbl => l_bom_ref_designator_tbl,
565 x_bom_sub_component_tbl => l_bom_sub_component_tbl,
566 x_return_status => l_return_status,
567 x_msg_count => l_msg_count,
568 p_debug => 'N',
569 p_output_dir => '/sqlcom/log/dom1151',
570 p_debug_filename => 'BOM_BO_debug.log'
571 ) ;
572
573 IF (l_return_status = 'S') THEN
574 -- ERES change begins
575 Event_Acknowledgement
576 ( p_event_name => 'oracle.apps.bom.billUpdate'
577 , p_event_key => TO_CHAR( l_bill_sequence_id)
578 , p_event_status => 'SUCCESS');
579 -- ERES change ends
580
581 success_counter := success_counter + 1 ;
582 FND_FILE.PUT_LINE(FND_FILE.LOG,'Successfully commoned for organization : '||t_org_code_list(I)) ;
583 COMMIT ;
584 ELSE
585 -- ERES change begins
586 Event_Acknowledgement
587 ( p_event_name => 'oracle.apps.bom.billUpdate'
588 , p_event_key => TO_CHAR( l_bill_sequence_id)
589 , p_event_status => 'FAILURE');
590 -- ERES change ends
591
592 failure_counter := failure_counter + 1 ;
593 FND_FILE.PUT_LINE(FND_FILE.LOG,'Commoning errored for organization : '||t_org_code_list(I)) ;
594 ROLLBACK ;
595 ERROR_HANDLER.GET_MESSAGE_LIST(x_message_list=>l_error_tbl) ;
596 FOR K in l_error_tbl.FIRST..l_error_tbl.LAST LOOP
597 FND_FILE.PUT_LINE(FND_FILE.LOG,'******************************************') ;
598 FND_FILE.PUT_LINE(FND_FILE.LOG,'entity_id : '||l_error_tbl(K).entity_id) ;
599 FND_FILE.PUT_LINE(FND_FILE.LOG,'message_text : '||l_error_tbl(K).message_text) ;
600 END LOOP ;
601 END IF ;
602 END IF;
603 ELSE
604 FND_MESSAGE.SET_NAME('BOM','BOM_BILL_EXISTS');
605 FND_MESSAGE.SET_TOKEN('ORG_CODE',t_org_code_list(I));
606 msg := FND_MESSAGE.GET ;
607 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
608 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill already exists for organization '||t_org_code_list(I)) ;
609 END IF ;
610 ELSE
611 FND_MESSAGE.SET_NAME('BOM','BOM_INVALID_ASSEMBLY');
612 FND_MESSAGE.SET_TOKEN('ASSEMBLY_NAME',l_assy_item_name);
613 FND_MESSAGE.SET_TOKEN('ORG_CODE',t_org_code_list(I));
614 msg := FND_MESSAGE.GET ;
615 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
616 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Assembly item'||l_assy_item_name||'does not exists in organization'||t_org_code_list(I)) ;
617 END IF ;
618 END LOOP ;
619
620 FND_MESSAGE.SET_NAME('BOM','BOM_COMMON_SUMMARY');
621 FND_MESSAGE.SET_TOKEN('ENTITY1','SUCCEDED');
622 FND_MESSAGE.SET_TOKEN('ENTITY2',success_counter);
623 msg := FND_MESSAGE.GET ;
624 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
625
626 FND_MESSAGE.SET_NAME('BOM','BOM_COMMON_SUMMARY');
627 FND_MESSAGE.SET_TOKEN('ENTITY1','FAILED');
628 FND_MESSAGE.SET_TOKEN('ENTITY2',failure_counter);
629 msg := FND_MESSAGE.GET ;
630 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
631
632 /*
633 FND_FILE.PUT_LINE(FND_FILE.LOG,'*************SUMMARY***************') ;
634 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of organizations successfully commoned : '||success_counter) ;
635 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of organizations for which commoning failed : '||failure_counter) ;
636
637 */
638
639 IF (failure_counter > 0) THEN
640 RETCODE := 1 ;
641 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',Current_Error_Code);
642 ELSE
643 RETCODE := 0 ;
644 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',Current_Error_Code);
645 END IF ;
646
647 EXCEPTION
648 WHEN Hierarchy_not_specified THEN
649 FND_MESSAGE.SET_NAME('BOM','BOM_HIERARCHY_MISSING');
650 msg := FND_MESSAGE.GET ;
651 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
652 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Hierarchy Name must be specified') ;
653 RETCODE := 2;
654 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
655
656 WHEN invalid_common_itemorg_to THEN
657 FND_MESSAGE.SET_NAME('BOM','BOM_COMMON_ITEM_ORG_INVALID');
658 msg := FND_MESSAGE.GET ;
659 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
660 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Either common item to or common org to is not specified') ;
661 RETCODE := 2;
662 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
663
664 WHEN same_common_itemorg_to THEN
665 FND_MESSAGE.SET_NAME('BOM','BOM_TO_ORG_ITEM_SAME');
666 msg := FND_MESSAGE.GET ;
667 FND_FILE.PUT_LINE(FND_FILE.LOG,msg) ;
668 -- FND_FILE.PUT_LINE(FND_FILE.LOG,'For Single Organization Scope, both TO org and item cannot be same as From org and item') ;
669 RETCODE := 2;
670 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
671
672 WHEN OTHERS THEN
673 FND_FILE.PUT_LINE(FND_FILE.LOG,'Others '||SQLCODE || ':'||SQLERRM) ;
674 RETCODE := 2;
675 conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',Current_Error_Code);
676 END create_common_bills;
677
678
679 /*
680 * This Procedure will modify the bill header attributes of a common BOM to make it updateable.
681 * @param p_bill_sequence_id IN Bill Sequence Id of the common BOM
682 */
683 PROCEDURE Dereference_Header(p_bill_sequence_id NUMBER)
684 IS
685 BEGIN
686 Update BOM_STRUCTURES_B
687 Set source_bill_sequence_id = common_bill_Sequence_id,
688 common_bill_sequence_id = bill_sequence_id
689 Where bill_sequence_id = p_bill_sequence_id;
690 END;
691
692 /*
693 * Function to return the nextval from Bom_Inventory_Components_S
694 */
695 FUNCTION Get_Component_Sequence
696 RETURN NUMBER
697 IS
698 CURSOR Comp_Seq IS
699 SELECT Bom_Inventory_Components_S.NEXTVAL Component_Sequence
700 FROM SYS.DUAL;
701 BEGIN
702 FOR c_Comp_Seq IN Comp_Seq LOOP
703 RETURN c_Comp_Seq.Component_Sequence;
704 END LOOP ;
705 RETURN NULL;
706 END Get_Component_Sequence;
707
708
709 /*
710 * This Procedure is used to resolve the old comp attrs while propagating changes to dest bills
711 * @param p_dest_bill_seq_id IN Bill Sequence Id of the dest component.
712 * @param p_orig_old_comp_seq IN old comp seq id in the source bill
713 */
714 Procedure Resolve_Old_Comp_Attrs(p_dest_bill_seq_id IN NUMBER
715 , p_orig_old_comp_seq IN NUMBER
716 , x_old_comp_seq_id IN OUT NOCOPY NUMBER
717 , x_wip_supply_type IN OUT NOCOPY NUMBER
718 , x_wip_supply_subinv IN OUT NOCOPY VARCHAR2
719 , x_wip_supply_locator_id IN OUT NOCOPY NUMBER
720 , x_inc_in_cost_rollup IN OUT NOCOPY NUMBER
721 , x_op_seq IN OUT NOCOPY NUMBER)
722 IS
723 l_impl_date DATE;
724 l_old_comp_seq NUMBER;
725 BEGIN
726
727 IF p_orig_old_comp_seq is null
728 THEN
729 RETURN;
730 END IF;
731
732 SELECT implementation_date
733 INTO l_impl_date
734 FROM BOM_COMPONENTS_B
735 WHERE component_sequence_id = p_orig_old_comp_seq;
736
737 SELECT component_sequence_id, wip_supply_type, supply_subinventory, supply_locator_id, include_in_cost_rollup, operation_seq_num
738 INTO x_old_comp_seq_id, x_wip_supply_type, x_wip_supply_subinv, x_wip_supply_locator_id, x_inc_in_cost_rollup, x_op_seq
739 FROM BOM_COMPONENTS_B bic
740 WHERE bic.bill_sequence_id = p_dest_bill_seq_id
741 AND bic.COMMON_COMPONENT_SEQUENCE_ID = p_orig_old_comp_seq --bic.old_component_sequence_id
742 AND (
743 (bic.implementation_date is not null
744 AND l_impl_date is not null
745 /*
746 *commented by jewen on 2008-11-6 to fix bug 7487640
747 AND sysdate between bic.effectivity_date and nvl (bic.disable_date, sysdate + 1)
748 */
749 )
750 OR
751 (l_impl_date is null
752 and bic.implementation_date is null
753 )
754 );
755
756 END;
757
758
759
760 PROCEDURE check_comp_fixed_rev_dtls(p_src_bill_seq_id IN NUMBER
761 , p_src_comp_seq_id IN NUMBER
762 , x_return_status OUT NOCOPY VARCHAR2)
763 IS
764 Cursor get_common_orgs(p_bill_seq_id NUMBER)
765 IS
766 SELECT distinct organization_id
767 FROM BOM_STRUCTURES_B
768 WHERE source_bill_sequence_id = p_bill_seq_id;
769
770 l_comp_rev_id NUMBER;
771
772 BEGIN
773 SELECT component_item_revision_id
774 INTO l_comp_rev_id
775 FROM bom_components_b
776 WHERE component_sequence_id = p_src_comp_seq_id;
777
778 IF l_comp_rev_id IS NULL
779 THEN
780 x_return_status := FND_API.G_RET_STS_SUCCESS;
781 RETURN;
782 END IF;
783
784 FOR org in get_common_orgs(p_src_bill_seq_id)
785 LOOP
786 IF get_rev_id_for_local_org(l_comp_rev_id, org.organization_id) IS NULL
787 THEN
788 x_return_status := FND_API.G_RET_STS_ERROR;
789 RETURN;
790 END IF;
791 END LOOP;
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793 END;
794
795
796
797 /*
798 * This Procedure will replicate the components of the source BOM as components of the Common BOM.
799 * @param p_src_bill_sequence_id IN Bill Sequence Id of the source BOM
800 * @param p_dest_bill_sequence_id IN Bill Sequence Id of the common BOM
801 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
802 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
803 */
804 PROCEDURE Replicate_Components (p_src_bill_sequence_id IN NUMBER
805 , p_dest_bill_sequence_id IN NUMBER
806 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
807 , x_Return_Status IN OUT NOCOPY VARCHAR2)
808 IS
809 Cursor get_source_components(p_bill_sequence_id Number)
810 IS
811 SELECT *
812 from BOM_COMPONENTS_B
813 where bill_sequence_id = p_bill_sequence_id;
814
815 Cursor get_structure_type(p_bill_seq_id NUMBER)
816 IS
817 SELECT structure_type_id
818 from BOM_STRUCTURES_B
819 where bill_sequence_id = p_bill_seq_id;
820 default_wip_params NUMBER;
821
822 Cursor get_dest_components(p_src_comp_seq_id IN NUMBER, p_dest_bill_sequence_id IN NUMBER)
823 IS
824 SELECT *
825 FROM BOM_COMPONENTS_B
826 WHERE common_component_sequence_id = p_src_comp_seq_id
827 and component_sequence_id <> common_component_sequence_id
828 AND bill_sequence_id = p_dest_bill_sequence_id;
829
830 l_wip_supply_type number := null;
831 l_locator_id number := null;
832 l_supply_subinventory varchar2(10) := null;
833 l_dest_org_id number;
834 l_src_comp_seq_id NUMBER;
835 l_err_text VARCHAR2(2000);
836 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
837 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
838 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
839 l_new_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
840 l_str_type NUMBER;
841 l_errorcode NUMBER;
842 l_msg_data VARCHAR2(100);
843 l_msg_count NUMBER := 0;
844 l_src_str_type NUMBER;
845 l_assy_item_id NUMBER;
846 l_alt_bom_desg varchar2(10);
847 l_effectivity_ctrl NUMBER;
848 l_token_table Error_Handler.Token_Tbl_Type;
849
850 l_pend_supply_type NUMBER;
851 l_pend_supply_subinv VARCHAR2 (10);
852 l_pend_supply_locator_id NUMBER;
853 l_pend_inc_in_cost_rollup NUMBER;
854 l_pend_op_seq NUMBER;
855
856 BEGIN
857
858 FND_PROFILE.GET('BOM:DEFAULT_WIP_VALUES', default_wip_params);
859 --arudresh_debug('BOM:DEFAULT_WIP_VALUES'|| default_wip_params);
860 SELECT assembly_item_id, organization_id, alternate_bom_designator, effectivity_control
861 into l_assy_item_id, l_dest_org_id, l_alt_bom_desg, l_effectivity_ctrl
862 from BOM_STRUCTURES_B
863 where bill_sequence_id = p_dest_bill_sequence_id;
864
865 IF l_effectivity_ctrl in (2,3,4)
866 THEN
867 x_Return_Status := FND_API.G_RET_STS_ERROR;
868 --arudresh_debug('adding error token');
869 Error_Handler.Add_Error_Token
870 (p_Message_Name => 'BOM_EDIT_COMM_INVALID_EFF'
871 -- , p_Message_Text => NULL
872 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
873 , x_Mesg_Token_Tbl => l_mesg_token_tbl
874 , p_Token_Tbl => l_token_table
875 );
876 fnd_message.set_name('BOM', 'BOM_EDIT_COMM_INVALID_EFF');
877 FND_FILE.PUT_LINE(FND_FILE.LOG, FND_MESSAGE.GET);
878 /*Error_Handler.Log_Error
879 (p_error_status => FND_API.G_RET_STS_ERROR
880 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
881 , p_error_scope => Error_Handler.G_SCOPE_RECORD
882 , p_error_level => Error_Handler.G_BH_LEVEL
883 , x_bom_header_rec => Bom_Bo_Pub.G_MISS_BOM_HEADER_REC
884 , x_bom_revision_tbl => Bom_Bo_Pub.G_MISS_BOM_REVISION_TBL
885 , x_bom_component_tbl => Bom_Bo_Pub.G_MISS_BOM_COMPONENT_TBL
886 , x_bom_ref_Designator_tbl => Bom_Bo_Pub.G_MISS_BOM_REF_DESIGNATOR_TBL
887 , x_bom_sub_component_tbl => Bom_Bo_Pub.G_MISS_BOM_SUB_COMPONENT_TBL
888 );*/
889 --arudresh_debug('added error token');
890 fnd_message.set_name('BOM', 'BOM_EDIT_COMM_INVALID_EFF');
891 return;
892 END IF;
893
894 Validate_Operation_Sequence_Id(p_src_bill_sequence_id => p_src_bill_sequence_id
895 , p_assembly_item_id => l_assy_item_id
896 , p_organization_id => l_dest_org_id
897 , p_alt_desg => l_alt_bom_desg
898 , x_Return_Status => x_Return_Status);
899 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
900 THEN
901 fnd_message.set_name('BOM', 'BOM_COMM_OP_SEQ_INVALID');
902 return;
903 END IF;
904
905 -- FORALL comp_rec in get_source_components(p_src_bill_sequence_id)
906 --loop
907 /*l_src_comp_seq_id := comp_rec.component_sequence_id;
908 comp_rec.common_component_sequence_id := comp_rec.component_sequence_id;
909 comp_rec.bill_sequence_id := p_dest_bill_sequence_id;
910 comp_rec.component_sequence_id := Get_Component_Sequence;*/
911 --comp_rec.organization_id := l_dest_org_id;
912
913 /*if default_wip_params = 'Y'
914 then
915 SELECT ITM.WIP_SUPPLY_TYPE, ITM.WIP_SUPPLY_LOCATOR_ID, ITM.WIP_SUPPLY_SUBINVENTORY
916 --INTO l_wip_supply_type, l_locator_id, l_supply_subinventory
917 FROM MTL_SYSTEM_ITEMS_B ITM
918 WHERE inventory_item_id = comp_rec.component_item_id
919 AND organization_id = l_dest_org_id;
920 comp_rec.supply_locator_id := l_locator_id;
921 comp_rec.wip_supply_type := l_wip_supply_type;
922 comp_rec.supply_subinventory := l_supply_subinventory;
923 else
924 comp_rec.supply_locator_id := null;
925 --comp_rec.wip_supply_type := null;
926 --Cant create a record with null wip_supply_type
927 comp_rec.supply_subinventory := null;
928 end if;*/
929 /*insert into BOM_COMPONENTS_B(<col list>)
930 values(<comp_rec>);*/
931 INSERT INTO BOM_COMPONENTS_B
932 ( SUPPLY_SUBINVENTORY
933 , OPERATION_LEAD_TIME_PERCENT
934 , REVISED_ITEM_SEQUENCE_ID
935 , COST_FACTOR
936 , REQUIRED_FOR_REVENUE
937 , HIGH_QUANTITY
938 , COMPONENT_SEQUENCE_ID
939 , PROGRAM_APPLICATION_ID
940 , WIP_SUPPLY_TYPE
941 , SUPPLY_LOCATOR_ID
942 , BOM_ITEM_TYPE
943 , OPERATION_SEQ_NUM
944 , COMPONENT_ITEM_ID
945 , LAST_UPDATE_DATE
946 , LAST_UPDATED_BY
947 , CREATION_DATE
948 , CREATED_BY
949 , LAST_UPDATE_LOGIN
950 , ITEM_NUM
951 , COMPONENT_QUANTITY
952 , COMPONENT_YIELD_FACTOR
953 , COMPONENT_REMARKS
954 , EFFECTIVITY_DATE
955 , CHANGE_NOTICE
956 , IMPLEMENTATION_DATE
957 , DISABLE_DATE
958 , ATTRIBUTE_CATEGORY
959 , ATTRIBUTE1
960 , ATTRIBUTE2
961 , ATTRIBUTE3
962 , ATTRIBUTE4
963 , ATTRIBUTE5
964 , ATTRIBUTE6
965 , ATTRIBUTE7
966 , ATTRIBUTE8
967 , ATTRIBUTE9
968 , ATTRIBUTE10
969 , ATTRIBUTE11
970 , ATTRIBUTE12
971 , ATTRIBUTE13
972 , ATTRIBUTE14
973 , ATTRIBUTE15
974 , PLANNING_FACTOR
975 , QUANTITY_RELATED
976 , SO_BASIS
977 , OPTIONAL
978 , MUTUALLY_EXCLUSIVE_OPTIONS
979 , INCLUDE_IN_COST_ROLLUP
980 , CHECK_ATP
981 , SHIPPING_ALLOWED
982 , REQUIRED_TO_SHIP
983 , INCLUDE_ON_SHIP_DOCS
984 , INCLUDE_ON_BILL_DOCS
985 , LOW_QUANTITY
986 , ACD_TYPE
987 , OLD_COMPONENT_SEQUENCE_ID
988 , BILL_SEQUENCE_ID
989 , REQUEST_ID
990 , PROGRAM_ID
991 , PROGRAM_UPDATE_DATE
992 , PICK_COMPONENTS
993 , Original_System_Reference
994 , From_End_Item_Unit_Number
995 , To_End_Item_Unit_Number
996 , Eco_For_Production -- Added by MK
997 , Enforce_Int_Requirements
998 , Auto_Request_Material -- Added in 11.5.9 by ADEY
999 , Obj_Name -- Added by hgelli.
1000 , pk1_value
1001 , pk2_value
1002 , Suggested_Vendor_Name --- Deepu
1003 , Vendor_Id --- Deepu
1004 , Unit_Price --- Deepu
1005 ,from_object_revision_id
1006 , from_minor_revision_id
1007 , common_component_sequence_id
1008 , basis_type
1009 , component_item_revision_id
1010 )
1011 SELECT decode(default_wip_params, 1, item.wip_supply_subinventory, null)
1012 , comp_rec.OPERATION_LEAD_TIME_PERCENT
1013 , comp_rec.REVISED_ITEM_SEQUENCE_ID
1014 , comp_rec.COST_FACTOR
1015 , comp_rec.REQUIRED_FOR_REVENUE
1016 , comp_rec.HIGH_QUANTITY
1017 , Bom_Inventory_Components_S.NEXTVAL
1018 , comp_rec.PROGRAM_APPLICATION_ID
1019 , decode(default_wip_params, 1, item.WIP_SUPPLY_TYPE, null)--supply type can be null
1020 , decode(default_wip_params, 1, item.WIP_SUPPLY_LOCATOR_ID, null)
1021 , comp_rec.BOM_ITEM_TYPE
1022 , comp_rec.OPERATION_SEQ_NUM
1023 , comp_rec.COMPONENT_ITEM_ID
1024 , comp_rec.LAST_UPDATE_DATE
1025 , comp_rec.LAST_UPDATED_BY
1026 , comp_rec.CREATION_DATE
1027 , comp_rec.CREATED_BY
1028 , comp_rec.LAST_UPDATE_LOGIN
1029 , comp_rec.ITEM_NUM
1030 , comp_rec.COMPONENT_QUANTITY
1031 , comp_rec.COMPONENT_YIELD_FACTOR
1032 , comp_rec.COMPONENT_REMARKS
1033 , comp_rec.EFFECTIVITY_DATE
1034 , comp_rec.CHANGE_NOTICE
1035 , comp_rec.IMPLEMENTATION_DATE
1036 , comp_rec.DISABLE_DATE
1037 , comp_rec.ATTRIBUTE_CATEGORY
1038 , comp_rec.ATTRIBUTE1
1039 , comp_rec.ATTRIBUTE2
1040 , comp_rec.ATTRIBUTE3
1041 , comp_rec.ATTRIBUTE4
1042 , comp_rec.ATTRIBUTE5
1043 , comp_rec.ATTRIBUTE6
1044 , comp_rec.ATTRIBUTE7
1045 , comp_rec.ATTRIBUTE8
1046 , comp_rec.ATTRIBUTE9
1047 , comp_rec.ATTRIBUTE10
1048 , comp_rec.ATTRIBUTE11
1049 , comp_rec.ATTRIBUTE12
1050 , comp_rec.ATTRIBUTE13
1051 , comp_rec.ATTRIBUTE14
1052 , comp_rec.ATTRIBUTE15
1053 , comp_rec.PLANNING_FACTOR
1054 , comp_rec.QUANTITY_RELATED
1055 , comp_rec.SO_BASIS
1056 , comp_rec.OPTIONAL
1057 , comp_rec.MUTUALLY_EXCLUSIVE_OPTIONS
1058 , comp_rec.INCLUDE_IN_COST_ROLLUP
1059 , comp_rec.CHECK_ATP
1060 , comp_rec.SHIPPING_ALLOWED
1061 , comp_rec.REQUIRED_TO_SHIP
1062 , comp_rec.INCLUDE_ON_SHIP_DOCS
1063 , comp_rec.INCLUDE_ON_BILL_DOCS
1064 , comp_rec.LOW_QUANTITY
1065 , comp_rec.ACD_TYPE
1066 , comp_rec.OLD_COMPONENT_SEQUENCE_ID
1067 , p_dest_bill_sequence_id
1068 , comp_rec.REQUEST_ID
1069 , comp_rec.PROGRAM_ID
1070 , comp_rec.PROGRAM_UPDATE_DATE
1071 , comp_rec.PICK_COMPONENTS
1072 , comp_rec.Original_System_Reference
1073 , comp_rec.From_End_Item_Unit_Number
1074 , comp_rec.To_End_Item_Unit_Number
1075 , comp_rec.Eco_For_Production -- Added by MK
1076 , comp_rec.Enforce_Int_Requirements
1077 , comp_rec.Auto_Request_Material -- Added in 11.5.9 by ADEY
1078 , comp_rec.Obj_Name -- Added by hgelli.
1079 , comp_rec.pk1_value
1080 , l_dest_org_id
1081 , comp_rec.Suggested_Vendor_Name --- Deepu
1082 , comp_rec.Vendor_Id --- Deepu
1083 , comp_rec.Unit_Price --- Deepu
1084 , comp_rec.from_object_revision_id
1085 , comp_rec.from_minor_revision_id
1086 , comp_rec.component_sequence_id
1087 , comp_rec.basis_type
1088 , decode(comp_rec.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(comp_rec.component_item_revision_id, l_dest_org_id))
1089 FROM BOM_COMPONENTS_B comp_rec, MTL_SYSTEM_ITEMS_B item, BOM_STRUCTURES_B bom
1090 WHERE comp_rec.bill_sequence_id = p_src_bill_sequence_id
1091 AND bom.bill_sequence_id = comp_rec.bill_sequence_id
1092 AND comp_rec.COMPONENT_ITEM_ID = item.inventory_item_id
1093 AND item.organization_id = l_dest_org_id
1094 --and comp_rec.implementation_date is not null
1095 ;
1096
1097 --Now update all the change rows(pending/implemented) to refer the replicated component rows.
1098
1099
1100
1101 --Replicate Structure User Attribute Values
1102 Open get_structure_type(p_bill_seq_id => p_dest_bill_sequence_id);
1103 Fetch get_structure_type into l_str_type;
1104 Close get_structure_type;
1105
1106 Open get_structure_type(p_bill_seq_id => p_src_bill_sequence_id);
1107 Fetch get_structure_type into l_src_str_type;
1108 Close get_structure_type;
1109
1110 --Copy user attrs only if the str type is same
1111 IF l_src_str_type = l_str_type
1112 THEN
1113 /*l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
1114 EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID',to_char(p_src_bill_sequence_id)));
1115 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('BILL_SEQUENCE_ID',to_char(p_dest_bill_sequence_id)));
1116 l_new_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ('STRUCTURE_TYPE_ID',TO_CHAR(l_str_type)));
1117 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data(
1118 p_api_version =>1.0
1119 ,p_application_id =>702
1120 ,p_object_name =>'BOM_STRUCTURE'
1121 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
1122 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
1123 ,p_new_cc_col_value_pairs => l_new_str_type
1124 ,x_return_status => x_Return_Status
1125 ,x_errorcode => l_errorcode
1126 ,x_msg_count => l_msg_count
1127 ,x_msg_data => l_msg_data
1128 );*/
1129 --DOnt copy the structure header user attrs
1130 Replicate_Comp_User_Attrs(p_src_bill_seq_id => p_src_bill_sequence_id,
1131 p_dest_bill_seq_id => p_dest_bill_sequence_id,
1132 x_Return_Status => x_Return_Status);
1133 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1134 THEN
1135 return;
1136 END IF;
1137 END IF; /*IF l_src_str_type = l_str_type*/
1138
1139
1140 --start another loop here
1141 FOR comp_rec in get_source_components(p_src_bill_sequence_id)
1142 loop
1143 IF comp_rec.change_notice is not null
1144 THEN
1145 FOR destn_comp in get_dest_components(comp_rec.component_sequence_id, p_dest_bill_sequence_id)
1146 loop
1147 Resolve_Old_Comp_Attrs(p_dest_bill_seq_id => destn_comp.bill_sequence_id
1148 , p_orig_old_comp_seq => destn_comp.old_component_sequence_id
1149 , x_old_comp_seq_id => destn_comp.old_component_sequence_id
1150 , x_wip_supply_type => destn_comp.wip_supply_type
1151 , x_wip_supply_subinv => destn_comp.supply_subinventory
1152 , x_wip_supply_locator_id => destn_comp.supply_locator_id
1153 , x_inc_in_cost_rollup => destn_comp.include_in_cost_rollup
1154 , x_op_seq => destn_comp.operation_seq_num);
1155 UPDATE BOM_COMPONENTS_B
1156 SET old_component_sequence_id = destn_comp.old_component_sequence_id,
1157 wip_supply_type = destn_comp.wip_supply_type,
1158 supply_subinventory = destn_comp.supply_subinventory,
1159 supply_locator_id = destn_comp.supply_locator_id,
1160 include_in_cost_rollup = destn_comp.include_in_cost_rollup,
1161 operation_seq_num = destn_comp.operation_seq_num
1162 WHERE component_sequence_id = destn_comp.component_sequence_id;
1163 end loop;
1164 END IF;
1165 Replicate_Ref_Desg(p_component_sequence_id => comp_rec.component_sequence_id
1166 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
1167 , x_Return_Status => x_Return_Status);
1168 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1169 THEN
1170 return;
1171 END IF;
1172 Replicate_Sub_Comp(p_component_sequence_id => comp_rec.component_sequence_id
1173 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
1174 , x_Return_Status => x_Return_Status);
1175 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1176 THEN
1177 return;
1178 END IF;
1179 Replicate_Comp_Ops(p_component_sequence_id => comp_rec.component_sequence_id
1180 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
1181 , x_Return_Status => x_Return_Status);
1182
1183 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1184 THEN
1185 return;
1186 END IF;
1187
1188 end loop; /*FOR comp_rec in get_source_components(p_src_bill_sequence_id) */
1189 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1190
1191 EXCEPTION
1192
1193 WHEN OTHERS THEN
1194 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
1195 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1196 THEN
1197 l_err_text := G_PKG_NAME ||' : Utility (Component Replicate) '
1198 || SUBSTR(SQLERRM, 1, 200);
1199 Error_Handler.Add_Error_Token
1200 ( p_Message_Name => NULL
1201 , p_Message_Text => l_err_text
1202 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1203 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1204 );
1205 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1206 END IF;
1207 --arudresh_debug('Error in replicate component'||SQLERRM);
1208 fnd_message.set_name('BOM', 'BOM_REPLICATE_FAILED');
1209 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1210
1211 END Replicate_Components;
1212
1213
1214 /*
1215 * This Procedure will replicate the components of the source BOM as components of the Common BOM.
1216 * Overloaded to be called from JBO.
1217 * @param p_src_bill_sequence_id IN Bill Sequence Id of the source BOM
1218 * @param p_dest_bill_sequence_id IN Bill Sequence Id of the common BOM
1219 */
1220 PROCEDURE Replicate_Components (p_src_bill_sequence_id IN NUMBER
1221 , p_dest_bill_sequence_id IN NUMBER)
1222 IS
1223 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1224 l_Return_Status VARCHAR2(1);
1225 BEGIN
1226 Replicate_Components (p_src_bill_sequence_id => p_src_bill_sequence_id
1227 , p_dest_bill_sequence_id => p_dest_bill_sequence_id
1228 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1229 , x_Return_Status => l_Return_Status);
1230 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
1231 THEN
1232 app_exception.raise_exception;
1233 END IF;
1234 END Replicate_Components;
1235
1236 /*
1237 * This Procedure should be called when a component is added to a bom that is commoned by other boms.
1238 * This will add the component to the common boms.
1239 * @param p_src_bill_seq_id IN Bill Sequence Id of the source BOM
1240 * @param p_src_comp_seq_id IN Component Sequence Id of the component added
1241 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
1242 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
1243 */
1244 PROCEDURE Insert_Related_Components
1245 ( p_src_bill_seq_id IN NUMBER
1246 , p_src_comp_seq_id IN NUMBER
1247 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1248 , x_Return_Status IN OUT NOCOPY VARCHAR2
1249 )
1250 IS
1251 Cursor get_related_bills(p_src_bill_sequence_id NUMBER) IS
1252 Select *
1253 from BOM_STRUCTURES_B
1254 where source_bill_sequence_id <> common_bill_sequence_id
1255 and source_bill_sequence_id = p_src_bill_sequence_id;
1256
1257 Cursor get_src_comp_details(p_src_comp_seq_id NUMBER) IS
1258 Select *
1259 From BOM_COMPONENTS_B
1260 where component_sequence_id = p_src_comp_seq_id;
1261
1262 l_err_text VARCHAR2(2000);
1263 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1264 l_Bo_Id VARCHAR2(3);
1265
1266 l_old_component_sequence_id NUMBER; -- Bug 2820641
1267 default_wip_params NUMBER;
1268
1269 l_object_revision_id NUMBER;
1270 l_minor_revision_id NUMBER;
1271 l_comp_revision_id NUMBER;
1272 l_comp_minor_revision_id NUMBER;
1273 l_operation_leadtime NUMBER := NULL;
1274 l_operation_seq_num NUMBER;
1275
1276 l_wip_supply_type number := null;
1277 l_locator_id number := null;
1278 l_supply_subinventory varchar2(10) := null;
1279 l_dest_org_id number;
1280
1281 --l_comp_name VARCHAR2(80);
1282 l_dest_assy_item VARCHAR2(80);
1283 l_dest_org_code VARCHAR2(3);
1284 l_token_tbl Error_Handler.Token_Tbl_Type;
1285
1286 l_pend_supply_type NUMBER;
1287 l_pend_supply_subinv VARCHAR2 (10);
1288 l_pend_supply_locator_id NUMBER;
1289 l_pend_inc_in_cost_rollup NUMBER;
1290 l_pend_op_seq NUMBER;
1291
1292 l_dummy VARCHAR2(10);
1293 l_comp_name MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
1294
1295 BEGIN
1296
1297 l_Bo_Id := Bom_Globals.Get_Bo_Identifier;
1298 FND_PROFILE.GET('BOM:DEFAULT_WIP_VALUES', default_wip_params);
1299
1300 SELECT operation_seq_num
1301 INTO l_operation_seq_num
1302 FROM BOM_COMPONENTS_B
1303 WHERE component_Sequence_id = p_src_comp_seq_id;
1304
1305 Check_Comp_Fixed_Rev_Dtls(p_src_bill_seq_id => p_src_bill_seq_id
1306 , p_src_comp_seq_id => p_src_comp_seq_id
1307 , x_return_status => x_return_status);
1308
1309 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1310 THEN
1311
1312 SELECT concatenated_segments
1313 into l_comp_name
1314 from mtl_system_items_kfv item, bom_components_b comp
1315 where item.inventory_item_id = comp.pk1_value
1316 and item.organization_id = comp.pk2_value
1317 and comp.component_sequence_id = p_src_comp_seq_id;
1318
1319 l_token_tbl.DELETE;
1320 l_token_tbl(1).token_name := 'COMP_NAME';
1321 l_token_tbl(1).token_value := l_comp_name;
1322
1323 Error_Handler.Add_Error_Token
1324 ( p_Message_Name => 'BOM_FIXED_REV_NOT_ALLOWED'
1325 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1326 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1327 , p_Token_Tbl => l_token_tbl
1328 );
1329 x_Return_Status := FND_API.G_RET_STS_ERROR;
1330 fnd_message.set_name('BOM', 'BOM_FIXED_REV_NOT_ALLOWED');
1331 RETURN;
1332
1333 END IF;
1334
1335 IF NOT BOMPCMBM.Check_Op_Seq_In_Ref_Boms(p_src_bill_seq_id => p_src_bill_seq_id
1336 , p_op_seq => l_operation_seq_num)
1337 THEN
1338 Error_Handler.Add_Error_Token
1339 ( p_Message_Name => 'BOM_COMMON_OP_SEQ_INVALID'
1340 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1341 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1342 , p_Token_Tbl => l_token_tbl
1343 );
1344 x_Return_Status := FND_API.G_RET_STS_ERROR;
1345 fnd_message.set_name('BOM', 'BOM_COMMON_OP_SEQ_INVALID');
1346 RETURN;
1347 END IF;
1348
1349 for bill_rec in get_related_bills(p_src_bill_seq_id)
1350 loop
1351 l_dest_org_id := bill_rec.organization_id;
1352 for src_comp_details in get_src_comp_details(p_src_comp_seq_id)
1353 loop
1354 if default_wip_params = 1
1355 then
1356 SELECT WIP_SUPPLY_TYPE, WIP_SUPPLY_LOCATOR_ID, WIP_SUPPLY_SUBINVENTORY
1357 INTO l_wip_supply_type, l_locator_id, l_supply_subinventory
1358 FROM MTL_SYSTEM_ITEMS_B
1359 WHERE inventory_item_id = src_comp_details.component_item_id
1360 AND organization_id = bill_rec.organization_id;
1361 src_comp_details.supply_locator_id := l_locator_id;
1362 src_comp_details.wip_supply_type := l_wip_supply_type;
1363 src_comp_details.supply_subinventory := l_supply_subinventory;
1364 else
1365 --dbms_output.put_line(' insert related comps :: in else of default wip params');
1366 src_comp_details.supply_locator_id := null;
1367 src_comp_details.supply_subinventory := null;
1368 end if; --if default_wip_params = 1
1369 --dbms_output.put_line(' insert related comps :: after end if ');
1370 src_comp_details.bill_sequence_id := bill_rec.bill_sequence_id;
1371 src_comp_details.common_component_sequence_id := src_comp_details.component_sequence_id;
1372 src_comp_details.component_sequence_id := get_component_sequence;
1373
1374 --Set the old_component_sequence ids, if any, to refer to comps in the same bill instead of source bill.
1375
1376 INSERT INTO BOM_COMPONENTS_B
1377 ( SUPPLY_SUBINVENTORY
1378 , OPERATION_LEAD_TIME_PERCENT
1379 , REVISED_ITEM_SEQUENCE_ID
1380 , COST_FACTOR
1381 , REQUIRED_FOR_REVENUE
1382 , HIGH_QUANTITY
1383 , COMPONENT_SEQUENCE_ID
1384 , PROGRAM_APPLICATION_ID
1385 , WIP_SUPPLY_TYPE
1386 , SUPPLY_LOCATOR_ID
1387 , BOM_ITEM_TYPE
1388 , OPERATION_SEQ_NUM
1389 , COMPONENT_ITEM_ID
1390 , LAST_UPDATE_DATE
1391 , LAST_UPDATED_BY
1392 , CREATION_DATE
1393 , CREATED_BY
1394 , LAST_UPDATE_LOGIN
1395 , ITEM_NUM
1396 , COMPONENT_QUANTITY
1397 , COMPONENT_YIELD_FACTOR
1398 , COMPONENT_REMARKS
1399 , EFFECTIVITY_DATE
1400 , CHANGE_NOTICE
1401 , IMPLEMENTATION_DATE
1402 , DISABLE_DATE
1403 , ATTRIBUTE_CATEGORY
1404 , ATTRIBUTE1
1405 , ATTRIBUTE2
1406 , ATTRIBUTE3
1407 , ATTRIBUTE4
1408 , ATTRIBUTE5
1409 , ATTRIBUTE6
1410 , ATTRIBUTE7
1411 , ATTRIBUTE8
1412 , ATTRIBUTE9
1413 , ATTRIBUTE10
1414 , ATTRIBUTE11
1415 , ATTRIBUTE12
1416 , ATTRIBUTE13
1417 , ATTRIBUTE14
1418 , ATTRIBUTE15
1419 , PLANNING_FACTOR
1420 , QUANTITY_RELATED
1421 , SO_BASIS
1422 , OPTIONAL
1423 , MUTUALLY_EXCLUSIVE_OPTIONS
1424 , INCLUDE_IN_COST_ROLLUP
1425 , CHECK_ATP
1426 , SHIPPING_ALLOWED
1427 , REQUIRED_TO_SHIP
1428 , INCLUDE_ON_SHIP_DOCS
1429 , INCLUDE_ON_BILL_DOCS
1430 , LOW_QUANTITY
1431 , ACD_TYPE
1432 , OLD_COMPONENT_SEQUENCE_ID
1433 , BILL_SEQUENCE_ID
1434 , REQUEST_ID
1435 , PROGRAM_ID
1436 , PROGRAM_UPDATE_DATE
1437 , PICK_COMPONENTS
1438 , Original_System_Reference
1439 , From_End_Item_Unit_Number
1440 , To_End_Item_Unit_Number
1441 , Eco_For_Production -- Added by MK
1442 , Enforce_Int_Requirements
1443 , Auto_Request_Material -- Added in 11.5.9 by ADEY
1444 , Obj_Name -- Added by hgelli.
1445 , pk1_value
1446 , pk2_value
1447 , Suggested_Vendor_Name --- Deepu
1448 , Vendor_Id --- Deepu
1449 -- , Purchasing_Category_id --- Deepu
1450 , Unit_Price --- Deepu
1451 ,from_object_revision_id
1452 , from_minor_revision_id
1453 --,component_item_revision_id
1454 --,component_minor_revision_id
1455 , common_component_sequence_id
1456 , basis_type
1457 , component_item_revision_id
1458 )
1459 VALUES
1460 ( src_comp_details.supply_subinventory
1461 , src_comp_details.OPERATION_LEAD_TIME_PERCENT --check this
1462 , src_comp_details.revised_item_sequence_id
1463 , NULL /* Cost Factor */
1464 , src_comp_details.required_for_revenue
1465 , src_comp_details.HIGH_QUANTITY
1466 , src_comp_details.component_sequence_id
1467 , BOM_Globals.Get_Prog_AppId
1468 , src_comp_details.wip_supply_type
1469 , DECODE(src_comp_details.supply_locator_id, FND_API.G_MISS_NUM,
1470 NULL, src_comp_details.supply_locator_id)
1471 , src_comp_details.bom_item_type
1472 , src_comp_details.operation_seq_num --Check this too
1473 , src_comp_details.component_item_id
1474 , SYSDATE /* Last Update Date */
1475 , src_comp_details.last_updated_by /* Last Updated By */
1476 , SYSDATE /* Creation Date */
1477 , src_comp_details.created_by /* Created By */
1478 , src_comp_details.last_update_login /* Last Update Login */
1479 , DECODE(src_comp_details.ITEM_NUM, FND_API.G_MISS_NUM,
1480 1, NULL,1,src_comp_details.ITEM_NUM)
1481 , src_comp_details.component_quantity
1482 , src_comp_details.COMPONENT_YIELD_FACTOR
1483 , src_comp_details.COMPONENT_REMARKS
1484 , nvl(src_comp_details.effectivity_date,SYSDATE) --2169237
1485 , src_comp_details.Change_Notice
1486 , src_comp_details.implementation_date/* Implementation Date */
1487 /*
1488 , DECODE(l_Bo_Id,
1489 Bom_Globals.G_BOM_BO,
1490 SYSDATE,
1491 NULL
1492 ) -- Implementation Date
1493 */
1494 , src_comp_details.disable_date
1495 , src_comp_details.attribute_category
1496 , src_comp_details.attribute1
1497 , src_comp_details.attribute2
1498 , src_comp_details.attribute3
1499 , src_comp_details.attribute4
1500 , src_comp_details.attribute5
1501 , src_comp_details.attribute6
1502 , src_comp_details.attribute7
1503 , src_comp_details.attribute8
1504 , src_comp_details.attribute9
1505 , src_comp_details.attribute10
1506 , src_comp_details.attribute11
1507 , src_comp_details.attribute12
1508 , src_comp_details.attribute13
1509 , src_comp_details.attribute14
1510 , src_comp_details.attribute15
1511 , src_comp_details.planning_factor
1512 , src_comp_details.quantity_related
1513 , src_comp_details.so_basis
1514 , src_comp_details.optional
1515 , src_comp_details.mutually_exclusive_options
1516 , src_comp_details.include_in_cost_rollup
1517 , src_comp_details.check_atp
1518 , src_comp_details.shipping_allowed
1519 , src_comp_details.required_to_ship
1520 , src_comp_details.include_on_ship_docs
1521 , NULL /* Include On Bill Docs */
1522 , src_comp_details.low_quantity
1523 , src_comp_details.acd_type
1524 -- , DECODE( p_rev_comp_Unexp_rec.old_component_sequence_id
1525 -- , FND_API.G_MISS_NUM
1526 -- , NULL
1527 -- ,p_rev_comp_Unexp_rec.old_component_sequence_id
1528 -- )
1529 , l_old_component_sequence_id --Chk this
1530 , src_comp_details.bill_sequence_id
1531 , NULL /* Request Id */
1532 , BOM_Globals.Get_Prog_Id
1533 , SYSDATE /* program_update_date */
1534 , src_comp_details.pick_components
1535 , src_comp_details.original_system_reference
1536 , DECODE( src_comp_details.from_end_item_unit_number
1537 , FND_API.G_MISS_CHAR
1538 , null
1539 , src_comp_details.from_end_item_unit_number
1540 )
1541 , DECODE( src_comp_details.to_end_item_unit_number
1542 , FND_API.G_MISS_CHAR
1543 , null
1544 , src_comp_details.to_end_item_unit_number
1545 )
1546 , BOM_Globals.Get_Eco_For_Production
1547 -- DECODE( l_Bo_Id, BOM_Globals.G_ECO_BO, l_Eco_For_Production, 2) /* Eco for Production flag */
1548 , src_comp_details.Enforce_Int_Requirements
1549 , src_comp_details.auto_request_material -- Added in 11.5.9 by ADEY
1550 , NULL-- Added by hgelli. Identifies this record as Bom Component.
1551 , src_comp_details.component_item_id
1552 , bill_rec.organization_id
1553 , src_comp_details.Suggested_Vendor_Name --- Deepu
1554 , src_comp_details.Vendor_Id --- Deepu
1555 -- , p_rev_component_rec.purchasing_category_id --- Deepu
1556 , src_comp_details.Unit_Price --- Deepu
1557 ,src_comp_details.from_object_revision_id
1558 ,src_comp_details.from_minor_revision_id
1559 , src_comp_details.common_component_sequence_id
1560 , src_comp_details.basis_type
1561 , decode(src_comp_details.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(src_comp_details.component_item_revision_id, bill_rec.organization_id))
1562 --,l_comp_revision_id
1563 --,l_comp_minor_revision_id
1564 );
1565 BOMPCMBM.Resolve_Old_Comp_Attrs(p_dest_bill_seq_id => src_comp_details.bill_sequence_id
1566 , p_orig_old_comp_seq => src_comp_details.old_component_sequence_id
1567 , x_old_comp_seq_id => src_comp_details.old_component_sequence_id
1568 , x_wip_supply_type => src_comp_details.wip_supply_type
1569 , x_wip_supply_subinv => src_comp_details.supply_subinventory
1570 , x_wip_supply_locator_id => src_comp_details.supply_locator_id
1571 , x_inc_in_cost_rollup => src_comp_details.include_in_cost_rollup
1572 , x_op_seq => src_comp_details.operation_seq_num);
1573
1574 IF src_comp_details.old_component_sequence_id IS NOT NULL
1575 THEN
1576 UPDATE BOM_COMPONENTS_B
1577 SET old_component_sequence_id = src_comp_details.old_component_sequence_id,
1578 wip_supply_type = src_comp_details.wip_supply_type,
1579 supply_subinventory = src_comp_details.supply_subinventory,
1580 supply_locator_id = src_comp_details.supply_locator_id,
1581 include_in_cost_rollup = src_comp_details.include_in_cost_rollup,
1582 operation_seq_num = src_comp_details.operation_seq_num
1583 WHERE COMPONENT_SEQUENCE_ID = src_comp_details.component_Sequence_id;
1584 END IF;
1585
1586 --Check if the insert caused overlapping components in any of the editable comm bills
1587 IF BOMPCMBM.Check_Component_Overlap(p_dest_bill_sequence_id => src_comp_details.bill_sequence_id
1588 , p_dest_comp_seq_id => src_comp_details.component_sequence_id
1589 , p_comp_item_id => src_comp_details.component_item_id
1590 , p_op_seq_num => src_comp_details.operation_seq_num
1591 , p_change_notice => src_comp_details.change_notice
1592 , p_eff_date => src_comp_details.effectivity_date
1593 , p_disable_date => src_comp_details.disable_date
1594 , p_impl_date => src_comp_details.implementation_date
1595 , p_rev_item_seq_id => src_comp_details.revised_item_sequence_id
1596 , p_src_bill_seq_id => bill_rec.source_bill_sequence_id
1597 )
1598 THEN --overlap exists
1599 x_Return_Status := FND_API.G_RET_STS_ERROR;
1600 SELECT concatenated_segments
1601 into l_comp_name
1602 from mtl_system_items_kfv
1603 where inventory_item_id = src_comp_details.component_item_id
1604 and organization_id = l_dest_org_id;
1605
1606 SELECT concatenated_segments
1607 into l_dest_assy_item
1608 from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
1609 where item.inventory_item_id = bom.assembly_item_id
1610 and item.organization_id = bom.organization_id
1611 and bom.bill_sequence_id = src_comp_details.bill_sequence_id;
1612
1613 SELECT organization_code
1614 into l_dest_org_code
1615 from mtl_parameters
1616 where organization_id = l_dest_org_id;
1617
1618 l_token_tbl(1).token_name := 'COMP_NAME';
1619 l_token_tbl(1).token_value := l_comp_name;
1620 l_token_tbl(2).token_name := 'ORG_CODE';
1621 l_token_tbl(2).token_value := l_dest_org_code;
1622 l_token_tbl(3).token_name := 'ASSY_NAME';
1623 l_token_tbl(3).token_value := l_dest_assy_item;
1624
1625 Error_Handler.Add_Error_Token
1626 (p_Message_Name => 'BOM_COMMON_OVERLAP'
1627 -- , p_Message_Text => NULL
1628 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1629 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1630 , p_Token_Tbl => l_token_tbl
1631 );
1632
1633 fnd_message.set_name('BOM', 'BOM_COMMON_OVERLAP');
1634 fnd_message.set_token('COMP_NAME', l_comp_name);
1635 fnd_message.set_token('ORG_CODE', l_dest_org_code);
1636 fnd_message.set_token('ASSY_NAME', l_dest_assy_item);
1637
1638 Return;
1639 END IF;
1640
1641 --Cannot have records with basis type as lot and supply type Phantom.
1642 BEGIN
1643 SELECT 'Y'
1644 INTO l_dummy
1645 FROM BOM_COMPONENTS_B
1646 WHERE wip_supply_type = 6
1647 AND basis_type = 2
1648 AND component_sequence_id = src_comp_details.component_sequence_id;
1649
1650 --If such record exists, raise an error
1651 x_Return_Status := FND_API.G_RET_STS_ERROR;
1652
1653 SELECT concatenated_segments
1654 into l_comp_name
1655 from mtl_system_items_kfv
1656 where inventory_item_id = src_comp_details.component_item_id
1657 and organization_id = l_dest_org_id;
1658
1659 SELECT concatenated_segments
1660 into l_dest_assy_item
1661 from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
1662 where item.inventory_item_id = bom.assembly_item_id
1663 and item.organization_id = bom.organization_id
1664 and bom.bill_sequence_id = src_comp_details.bill_sequence_id;
1665
1666 SELECT organization_code
1667 into l_dest_org_code
1668 from mtl_parameters
1669 where organization_id = l_dest_org_id;
1670
1671 l_token_tbl(1).token_name := 'COMP_NAME';
1672 l_token_tbl(1).token_value := l_comp_name;
1673 l_token_tbl(2).token_name := 'ORG_CODE';
1674 l_token_tbl(2).token_value := l_dest_org_code;
1675 l_token_tbl(3).token_name := 'ASSY_NAME';
1676 l_token_tbl(3).token_value := l_dest_assy_item;
1677
1678 Error_Handler.Add_Error_Token
1679 (p_Message_Name => 'BOM_COMM_SUPPLY_BASIS_CONFLICT'
1680 -- , p_Message_Text => NULL
1681 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1682 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1683 , p_Token_Tbl => l_token_tbl
1684 );
1685
1686 fnd_message.set_name('BOM', 'BOM_COMM_SUPPLY_BASIS_CONFLICT');
1687 fnd_message.set_token('COMP_NAME', l_comp_name);
1688 fnd_message.set_token('ORG_CODE', l_dest_org_code);
1689 fnd_message.set_token('ASSY_NAME', l_dest_assy_item);
1690
1691 Return;
1692
1693 EXCEPTION
1694 WHEN NO_DATA_FOUND THEN
1695 NULL;
1696 END;
1697 /*Propagate_Comp_User_Attributes(p_src_comp_seq_id => src_comp_details.common_component_sequence_id,
1698 x_Return_Status => x_Return_Status);*/
1699 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1700 THEN
1701 return;
1702 END IF;
1703 end loop; --for src_comp_details in get_src_comp_details(p_src_comp_seq_id)
1704 end loop; --for bill_rec in get_related_bills(p_src_bill_seq_id)
1705
1706 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1707
1708 EXCEPTION
1709 WHEN OTHERS THEN
1710 --dbms_output.put_line(' SQL Error '||SQLERRM);
1711 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
1712
1713 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1714 THEN
1715 l_err_text := G_PKG_NAME ||' : Utility (Related Component Insert) '
1716 ||SUBSTR(SQLERRM, 1, 200);
1717 Error_Handler.Add_Error_Token
1718 ( p_Message_Name => NULL
1719 , p_Message_Text => l_err_text
1720 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1721 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1722 );
1723 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1724 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1725 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
1726 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1727
1728 END Insert_Related_Components;
1729
1730
1731
1732 /*
1733 * This is an overloaded Procedure called when a component is added to a bom that is commoned by other boms.
1734 * This will add the component to the common boms.
1735 * @param p_src_bill_seq_id IN Bill Sequence Id of the source BOM
1736 * @param p_src_comp_seq_id IN Component Sequence Id of the component added
1737 */
1738 PROCEDURE Insert_Related_Components( p_src_bill_seq_id IN NUMBER
1739 , p_src_comp_seq_id IN NUMBER)
1740 IS
1741 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1742 l_Return_Status VARCHAR2(1);
1743 BEGIN
1744 Insert_Related_Components(p_src_bill_seq_id => p_src_bill_seq_id
1745 , p_src_comp_seq_id => p_src_comp_seq_id
1746 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1747 , x_Return_Status => l_Return_Status);
1748 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
1749 THEN
1750 app_exception.raise_exception;
1751 END IF;
1752
1753 END Insert_Related_Components;
1754
1755
1756
1757 /*
1758 * This Procedure should be called when a component is updated in a bom that is commoned by other boms.
1759 * This will update the component in the common boms.
1760 * @param p_src_comp_seq_id IN Component Sequence Id of the component updated
1761 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
1762 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
1763 */
1764 PROCEDURE Update_Related_Components
1765 ( p_src_comp_seq_id IN NUMBER
1766 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1767 , x_Return_Status IN OUT NOCOPY VARCHAR2
1768 )
1769 IS
1770 Cursor get_related_Components(p_src_comp_seq_id NUMBER) IS
1771 SELECT *
1772 FROM BOM_COMPONENTS_B
1773 WHERE common_component_sequence_id = p_src_comp_seq_id
1774 AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID;
1775
1776 Cursor get_src_comp_details(p_src_comp_seq_id NUMBER) IS
1777 SELECT *
1778 FROM BOM_COMPONENTS_B
1779 WHERE component_sequence_id = p_src_comp_seq_id;
1780
1781 l_return_status varchar2(80);
1782 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1783 l_err_text VARCHAR2(2000);
1784 l_token_tbl Error_Handler.Token_Tbl_Type;
1785 l_comp_name VARCHAR2(80);
1786 l_dest_assy_item VARCHAR2(80);
1787 l_dest_org_code VARCHAR2(3);
1788 l_dest_org_id NUMBER;
1789
1790 l_dummy VARCHAR2(10);
1791 BEGIN
1792
1793 /* need to populate Operation Lead Time percent corresponding to the operation
1794 -vhymavat bug3537394 */
1795 /*IF((p_rev_component_rec.new_operation_sequence_number IS NULL) OR
1796 (p_rev_component_rec.new_operation_sequence_number =FND_API.G_MISS_NUM) ) THEN
1797 l_operation_seq_num := p_rev_component_rec.operation_sequence_number;
1798
1799 ELSE
1800 l_operation_seq_num :=p_rev_component_rec.new_operation_sequence_number;
1801 END IF;*/
1802
1803 --OPEN ISSUE: With our opn Seq no defaulting, op seqs can be different for src and common boms
1804 --Do we need this validation? Or, since Operation sequence is updateable anyway, should an update
1805 -- of op seq on a component be propagated to related comps? Dont think so. -AR.
1806
1807 /*IF(l_operation_seq_num <>1 and p_rev_component_rec.acd_type is null) THEN
1808 l_operation_leadtime :=
1809 Get_Operation_Leadtime (
1810 p_assembly_item_id =>p_rev_comp_Unexp_rec.revised_item_id
1811 ,p_organization_id =>p_rev_comp_Unexp_rec.organization_id
1812 ,p_alternate_bom_code =>p_rev_component_rec.alternate_bom_code
1813 ,p_operation_seq_num => l_operation_seq_num
1814 );
1815
1816 END IF;
1817 */
1818
1819 for src_comp in get_src_comp_details(p_src_comp_seq_id)
1820 loop
1821 Check_Comp_Fixed_Rev_Dtls(p_src_bill_seq_id => src_comp.bill_sequence_id
1822 , p_src_comp_seq_id => src_comp.component_sequence_id
1823 , x_return_status => x_return_status);
1824
1825 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
1826 THEN
1827
1828 SELECT concatenated_segments
1829 into l_comp_name
1830 from mtl_system_items_kfv item, bom_components_b comp
1831 where item.inventory_item_id = comp.pk1_value
1832 and item.organization_id = comp.pk2_value
1833 and comp.component_sequence_id = p_src_comp_seq_id;
1834
1835 l_token_tbl.DELETE;
1836 l_token_tbl(1).token_name := 'COMP_NAME';
1837 l_token_tbl(1).token_value := l_comp_name;
1838
1839 Error_Handler.Add_Error_Token
1840 ( p_Message_Name => 'BOM_FIXED_REV_NOT_ALLOWED'
1841 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1842 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1843 , p_Token_Tbl => l_token_tbl
1844 );
1845 x_Return_Status := FND_API.G_RET_STS_ERROR;
1846 fnd_message.set_name('BOM', 'BOM_FIXED_REV_NOT_ALLOWED');
1847 fnd_message.set_token('COMP_NAME', l_comp_name, true);
1848 RETURN;
1849
1850 END IF;
1851
1852 for dest_comp in get_related_Components(p_src_comp_seq_id)
1853 loop
1854 UPDATE BOM_COMPONENTS_B dest_cmpo
1855 SET REQUIRED_FOR_REVENUE = src_comp.required_for_revenue
1856 , HIGH_QUANTITY = src_comp.HIGH_QUANTITY
1857 /* , WIP_SUPPLY_TYPE = p_rev_component_rec.wip_supply_type
1858 , SUPPLY_LOCATOR_ID =
1859 DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
1860 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
1861 , OPERATION_SEQ_NUM = l_operation_seq_num*/
1862 , EFFECTIVITY_DATE = src_comp.effectivity_date
1863 , LAST_UPDATE_DATE = SYSDATE
1864 , LAST_UPDATED_BY = src_comp.LAST_UPDATED_BY
1865 , LAST_UPDATE_LOGIN = src_comp.LAST_UPDATE_LOGIN
1866 , ITEM_NUM = src_comp.ITEM_NUM
1867 , COMPONENT_QUANTITY = src_comp.COMPONENT_QUANTITY
1868 , COMPONENT_YIELD_FACTOR = src_comp.COMPONENT_YIELD_FACTOR
1869 , COMPONENT_REMARKS = src_comp.COMPONENT_REMARKS
1870 , DISABLE_DATE = DECODE(src_comp.IMPLEMENTATION_DATE,
1871 NULL, src_comp.DISABLE_DATE,
1872 DECODE(DISABLE_DATE,
1873 NULL, src_comp.DISABLE_DATE,
1874 Greatest(src_comp.EFFECTIVITY_DATE, DISABLE_DATE), DECODE(DISABLE_DATE,
1875 GREATEST(DISABLE_DATE, SYSDATE), src_comp.DISABLE_DATE,
1876 DISABLE_DATE
1877 ),
1878 DISABLE_DATE
1879 )
1880 )
1881 , ATTRIBUTE_CATEGORY = src_comp.attribute_category
1882 , ATTRIBUTE1 = src_comp.attribute1
1883 , ATTRIBUTE2 = src_comp.attribute2
1884 , ATTRIBUTE3 = src_comp.attribute3
1885 , ATTRIBUTE4 = src_comp.attribute4
1886 , ATTRIBUTE5 = src_comp.attribute5
1887 , ATTRIBUTE6 = src_comp.attribute6
1888 , ATTRIBUTE7 = src_comp.attribute7
1889 , ATTRIBUTE8 = src_comp.attribute8
1890 , ATTRIBUTE9 = src_comp.attribute9
1891 , ATTRIBUTE10 = src_comp.attribute10
1892 , ATTRIBUTE11 = src_comp.attribute11
1893 , ATTRIBUTE12 = src_comp.attribute12
1894 , ATTRIBUTE13 = src_comp.attribute13
1895 , ATTRIBUTE14 = src_comp.attribute14
1896 , ATTRIBUTE15 = src_comp.attribute15
1897 , PLANNING_FACTOR = src_comp.planning_factor
1898 , QUANTITY_RELATED = src_comp.quantity_related
1899 , SO_BASIS = src_comp.so_basis
1900 , OPTIONAL = src_comp.optional
1901 , MUTUALLY_EXCLUSIVE_OPTIONS = src_comp.mutually_exclusive_options
1902 --, INCLUDE_IN_COST_ROLLUP = src_comp.include_in_cost_rollup
1903 , CHECK_ATP = src_comp.check_atp
1904 , SHIPPING_ALLOWED = src_comp.shipping_allowed
1905 , REQUIRED_TO_SHIP = src_comp.required_to_ship
1906 , INCLUDE_ON_SHIP_DOCS = src_comp.include_on_ship_docs
1907 , LOW_QUANTITY = src_comp.LOW_QUANTITY
1908 , ACD_TYPE = src_comp.acd_type
1909 , PROGRAM_UPDATE_DATE = SYSDATE
1910 , PROGRAM_ID = BOM_Globals.Get_Prog_Id
1911 , OPERATION_LEAD_TIME_PERCENT = src_comp.operation_lead_time_percent
1912 , Original_System_Reference =
1913 src_comp.original_system_reference
1914 , From_End_Item_Unit_Number = src_comp.From_End_Item_Unit_Number
1915 , To_End_Item_Unit_Number = src_comp.To_End_Item_Unit_Number
1916 , Enforce_Int_Requirements = src_comp.Enforce_Int_Requirements
1917 , Auto_Request_Material = src_comp.auto_request_material -- Added in 11.5.9 by ADEY
1918 , Suggested_Vendor_Name = src_comp.Suggested_Vendor_Name --- Deepu
1919 , Vendor_Id = src_comp.Vendor_Id --- Deepu
1920 -- , Purchasing_Category_id = src_comp.purchasing_category_id --- Deepu
1921 , Unit_Price = src_comp.Unit_Price --- Deepu
1922 , Basis_type = src_comp.Basis_type
1923 , COMPONENT_ITEM_REVISION_ID = decode(src_comp.component_item_revision_id, null, null, BOMPCMBM.get_rev_id_for_local_org(src_comp.component_item_revision_id, dest_comp.pk2_value))
1924 WHERE COMPONENT_SEQUENCE_ID = dest_comp.component_sequence_id
1925 AND COMPONENT_SEQUENCE_ID <> COMMON_COMPONENT_SEQUENCE_ID
1926 ;
1927 --Check if the insert caused overlapping components in any of the editable comm bills
1928 IF BOMPCMBM.Check_Component_Overlap(p_dest_bill_sequence_id => dest_comp.bill_sequence_id
1929 , p_dest_comp_seq_id => dest_comp.component_sequence_id
1930 , p_comp_item_id => dest_comp.component_item_id
1931 , p_op_seq_num => dest_comp.operation_seq_num
1932 , p_change_notice => dest_comp.change_notice
1933 , p_eff_date => dest_comp.effectivity_date
1934 , p_disable_date => dest_comp.disable_date
1935 , p_impl_date => dest_comp.implementation_date
1936 , p_rev_item_seq_id => dest_comp.revised_item_Sequence_id
1937 , p_src_bill_seq_id => src_comp.bill_sequence_id
1938 )
1939 THEN --overlap exists
1940 x_Return_Status := FND_API.G_RET_STS_ERROR;
1941
1942 SELECT Organization_id
1943 INTO l_dest_org_id
1944 FROM BOM_STRUCTURES_B
1945 WHERE bill_sequence_id = dest_comp.bill_sequence_id;
1946
1947 SELECT concatenated_segments
1948 into l_comp_name
1949 from mtl_system_items_kfv
1950 where inventory_item_id = src_comp.component_item_id
1951 and organization_id = l_dest_org_id;
1952
1953 SELECT concatenated_segments
1954 into l_dest_assy_item
1955 from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
1956 where item.inventory_item_id = bom.assembly_item_id
1957 and item.organization_id = bom.organization_id
1958 and bom.bill_sequence_id = src_comp.bill_sequence_id;
1959
1960 SELECT organization_code
1961 into l_dest_org_code
1962 from mtl_parameters
1963 where organization_id = l_dest_org_id;
1964
1965 l_token_tbl(1).token_name := 'COMP_NAME';
1966 l_token_tbl(1).token_value := l_comp_name;
1967 l_token_tbl(2).token_name := 'ORG_CODE';
1968 l_token_tbl(2).token_value := l_dest_org_code;
1969 l_token_tbl(3).token_name := 'ASSY_NAME';
1970 l_token_tbl(3).token_value := l_dest_assy_item;
1971
1972 Error_Handler.Add_Error_Token
1973 (p_Message_Name => 'BOM_COMMON_OVERLAP'
1974 -- , p_Message_Text => NULL
1975 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1976 , x_Mesg_Token_Tbl => l_mesg_token_tbl
1977 , p_Token_Tbl => l_token_tbl
1978 );
1979
1980 fnd_message.set_name('BOM', 'BOM_COMMON_OVERLAP');
1981 fnd_message.set_token('COMP_NAME', l_comp_name);
1982 fnd_message.set_token('ORG_CODE', l_dest_org_code);
1983 fnd_message.set_token('ASSY_NAME', l_dest_assy_item);
1984
1985 Return;
1986 END IF;
1987
1988 --Cannot have records with basis type as lot and supply type Phantom.
1989 BEGIN
1990
1991 SELECT basis_type
1992 INTO l_dummy
1993 FROM BOM_COMPONENTS_B
1994 WHERE component_sequence_id = dest_comp.component_sequence_id;
1995
1996 SELECT 'Y'
1997 INTO l_dummy
1998 FROM BOM_COMPONENTS_B
1999 WHERE wip_supply_type = 6
2000 AND basis_type = 2
2001 AND component_sequence_id = dest_comp.component_sequence_id;
2002
2003 --If such record exists, raise an error
2004 x_Return_Status := FND_API.G_RET_STS_ERROR;
2005
2006 SELECT Organization_id
2007 INTO l_dest_org_id
2008 FROM BOM_STRUCTURES_B
2009 WHERE bill_sequence_id = dest_comp.bill_sequence_id;
2010
2011 SELECT concatenated_segments
2012 into l_comp_name
2013 from mtl_system_items_kfv
2014 where inventory_item_id = src_comp.component_item_id
2015 and organization_id = l_dest_org_id;
2016
2017 SELECT concatenated_segments
2018 into l_dest_assy_item
2019 from mtl_system_items_kfv item, BOM_STRUCTURES_B bom
2020 where item.inventory_item_id = bom.assembly_item_id
2021 and item.organization_id = bom.organization_id
2022 and bom.bill_sequence_id = src_comp.bill_sequence_id;
2023
2024 SELECT organization_code
2025 into l_dest_org_code
2026 from mtl_parameters
2027 where organization_id = l_dest_org_id;
2028
2029 l_token_tbl(1).token_name := 'COMP_NAME';
2030 l_token_tbl(1).token_value := l_comp_name;
2031 l_token_tbl(2).token_name := 'ORG_CODE';
2032 l_token_tbl(2).token_value := l_dest_org_code;
2033 l_token_tbl(3).token_name := 'ASSY_NAME';
2034 l_token_tbl(3).token_value := l_dest_assy_item;
2035
2036 Error_Handler.Add_Error_Token
2037 (p_Message_Name => 'BOM_COMM_SUPPLY_BASIS_CONFLICT'
2038 -- , p_Message_Text => NULL
2039 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2040 , x_Mesg_Token_Tbl => l_mesg_token_tbl
2041 , p_Token_Tbl => l_token_tbl
2042 );
2043
2044 fnd_message.set_name('BOM', 'BOM_COMM_SUPPLY_BASIS_CONFLICT');
2045 fnd_message.set_token('COMP_NAME', l_comp_name);
2046 fnd_message.set_token('ORG_CODE', l_dest_org_code);
2047 fnd_message.set_token('ASSY_NAME', l_dest_assy_item);
2048
2049 Return;
2050 EXCEPTION
2051 WHEN NO_DATA_FOUND THEN
2052 NULL;
2053 END;
2054 /*Propagate_Comp_User_Attributes(p_src_comp_seq_id => p_src_comp_seq_id,
2055 x_Return_Status => x_return_status);
2056 This api will not overwrite data. */
2057 end loop; --for dest_comp in get_related_Components(p_src_comp_seq_id)
2058 end loop; --for src_comp in get_src_comp_details(p_src_comp_seq_id)
2059
2060 --If the update is of the editable attrs of a component in an editable common bill
2061
2062 --When the WIP attrs for the old component are modified, the pending changes still referencing
2063 --the ECO on src bill should be synchronized with the same values.
2064 UPDATE BOM_COMPONENTS_B bic
2065 SET (wip_supply_type, supply_locator_id, supply_subinventory, operation_seq_num, include_in_cost_rollup) =
2066 (SELECT wip_supply_type, supply_locator_id, supply_subinventory, operation_seq_num, include_in_cost_rollup
2067 FROM BOM_COMPONENTS_B
2068 WHERE component_sequence_id = p_src_comp_Seq_id)
2069 WHERE old_component_sequence_id = p_src_comp_Seq_id
2070 AND implementation_date IS NULL
2071 AND nvl(common_component_sequence_id, component_sequence_id) <> component_sequence_id
2072 AND bill_sequence_id NOT IN (SELECT bill_sequence_id
2073 FROM eng_revised_items
2074 WHERE change_notice = bic.change_notice);
2075
2076 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2077
2078 EXCEPTION
2079
2080 WHEN OTHERS THEN
2081 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2082 THEN
2083 l_err_text := G_PKG_NAME ||' : Utility (Related Component Update) '
2084 ||SUBSTR(SQLERRM, 1, 200);
2085 Error_Handler.Add_Error_Token
2086 ( p_Message_Name => NULL
2087 , p_Message_Text => l_err_text
2088 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2089 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2090 );
2091 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2092 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2093 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
2094 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2095 END Update_Related_Components;
2096
2097
2098
2099 /*
2100 * This overloaded Procedure should be called from java when a component is updated in a bom that is commoned by other boms.
2101 * This will update the component in the common boms.
2102 * @param p_src_comp_seq_id IN Component Sequence Id of the component updated
2103 */
2104 PROCEDURE Update_Related_Components( p_src_comp_seq_id IN NUMBER)
2105 IS
2106 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2107 l_Return_Status VARCHAR2(1);
2108 BEGIN
2109 Update_Related_Components( p_src_comp_seq_id => p_src_comp_seq_id
2110 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2111 , x_Return_Status => l_Return_Status);
2112 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2113 THEN
2114 app_exception.raise_exception;
2115 END IF;
2116 END Update_Related_Components;
2117
2118
2119 /*
2120 * This Procedure will replicate the ref designators of components of the source BOM as ref desgs of components of the Common BOM.
2121 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2122 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2123 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2124 */
2125 Procedure Replicate_Ref_Desg(p_component_sequence_id IN NUMBER
2126 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2127 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2128 IS
2129
2130 l_return_status varchar2(80);
2131 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2132 l_err_text VARCHAR2(2000);
2133
2134 BEGIN
2135 INSERT INTO BOM_REFERENCE_DESIGNATORS
2136 ( COMPONENT_REFERENCE_DESIGNATOR
2137 , LAST_UPDATE_DATE
2138 , LAST_UPDATED_BY
2139 , CREATION_DATE
2140 , CREATED_BY
2141 , LAST_UPDATE_LOGIN
2142 , REF_DESIGNATOR_COMMENT
2143 , CHANGE_NOTICE
2144 , COMPONENT_SEQUENCE_ID
2145 , ACD_TYPE
2146 , REQUEST_ID
2147 , PROGRAM_APPLICATION_ID
2148 , PROGRAM_ID
2149 , PROGRAM_UPDATE_DATE
2150 , ATTRIBUTE_CATEGORY
2151 , ATTRIBUTE1
2152 , ATTRIBUTE2
2153 , ATTRIBUTE3
2154 , ATTRIBUTE4
2155 , ATTRIBUTE5
2156 , ATTRIBUTE6
2157 , ATTRIBUTE7
2158 , ATTRIBUTE8
2159 , ATTRIBUTE9
2160 , ATTRIBUTE10
2161 , ATTRIBUTE11
2162 , ATTRIBUTE12
2163 , ATTRIBUTE13
2164 , ATTRIBUTE14
2165 , ATTRIBUTE15
2166 , Original_System_Reference
2167 , Common_component_sequence_id
2168 )
2169 SELECT
2170 ref_desg.component_reference_designator
2171 , SYSDATE
2172 , ref_desg.LAST_UPDATED_BY
2173 , SYSDATE
2174 , ref_desg.CREATED_BY
2175 , ref_desg.LAST_UPDATE_LOGIN
2176 , DECODE( ref_desg.ref_designator_comment
2177 , FND_API.G_MISS_CHAR
2178 , NULL
2179 , ref_desg.ref_designator_comment )
2180 , ref_desg.change_notice
2181 , comp.component_sequence_id
2182 , ref_desg.acd_type
2183 , NULL /* Request Id */
2184 , Bom_Globals.Get_Prog_AppId
2185 , Bom_Globals.Get_Prog_Id
2186 , SYSDATE
2187 , ref_desg.attribute_category
2188 , ref_desg.attribute1
2189 , ref_desg.attribute2
2190 , ref_desg.attribute3
2191 , ref_desg.attribute4
2192 , ref_desg.attribute5
2193 , ref_desg.attribute6
2194 , ref_desg.attribute7
2195 , ref_desg.attribute8
2196 , ref_desg.attribute9
2197 , ref_desg.attribute10
2198 , ref_desg.attribute11
2199 , ref_desg.attribute12
2200 , ref_desg.attribute13
2201 , ref_desg.attribute14
2202 , ref_desg.attribute15
2203 , ref_desg.Original_System_Reference
2204 , p_component_sequence_id
2205 FROM BOM_COMPONENTS_B comp, BOM_REFERENCE_DESIGNATORS ref_desg
2206 WHERE comp.component_sequence_id <> comp.common_component_sequence_id
2207 AND comp.common_component_sequence_id = ref_desg.component_sequence_id
2208 AND ref_desg.component_sequence_id = p_component_sequence_id
2209 AND NOT EXISTS
2210 (
2211 SELECT 1
2212 FROM bom_reference_designators ref2
2213 where ref2.component_sequence_id = comp.component_sequence_id
2214 and ref2.component_reference_designator = ref_desg.component_reference_designator
2215 )
2216 ;
2217 /*insert into bom_reference_designators(<col_list>)
2218 values(<ref_desg>);*/
2219 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2220 EXCEPTION
2221 WHEN OTHERS THEN
2222 --dbms_output.put_line(' Error '||SQLERRM);
2223 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2224 THEN
2225 l_err_text := G_PKG_NAME ||' : Common BOM (Ref Desg Replicate) '
2226 ||SUBSTR(SQLERRM, 1, 200);
2227 Error_Handler.Add_Error_Token
2228 ( p_Message_Name => NULL
2229 , p_Message_Text => l_err_text
2230 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2231 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2232 );
2233 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2234 END IF;
2235 fnd_message.set_name('BOM', 'BOM_REPLICATE_FAILED');
2236 --arudresh_debug('error in replicate ref desg'||SQLERRM);
2237 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2238 END Replicate_Ref_Desg;
2239
2240
2241 /*
2242 * This overloaded Procedure should be called from java to replicate the ref designators of components of
2243 * the source BOM as ref desgs of components of the Common BOM.
2244 * @param p_component_sequence_id IN Component Sequence Id of the component updated.
2245 */
2246 Procedure Replicate_Ref_Desg(p_component_sequence_id IN NUMBER)
2247 IS
2248 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2249 l_Return_Status VARCHAR2(1);
2250 BEGIN
2251 Replicate_Ref_Desg(p_component_sequence_id => p_component_sequence_id
2252 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2253 , x_Return_Status => l_Return_Status);
2254 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2255 THEN
2256 app_exception.raise_exception;
2257 END IF;
2258 END;
2259
2260
2261 /*
2262 * This Procedure is used to add reference designators to the related components of the common boms whenever
2263 * reference designator is added to a component of a source bom.
2264 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2265 * @param p_ref_desg IN Reference Designator added.
2266 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2267 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2268 */
2269 PROCEDURE Insert_Related_Ref_Desg(p_component_sequence_id IN NUMBER
2270 , p_ref_desg IN VARCHAR2
2271 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2272 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2273 IS
2274 l_return_status varchar2(80);
2275 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2276 l_err_text VARCHAR2(2000);
2277
2278 Cursor get_ref_desg_details(p_comp_seq_id NUMBER,
2279 p_ref_desg VARCHAR2)
2280 IS
2281 select *
2282 from bom_reference_designators
2283 where component_sequence_id = p_comp_seq_id
2284 and component_reference_designator = p_ref_desg;
2285
2286 Cursor get_destn_comps(p_comp_seq_id number)
2287 is
2288 select dest.component_sequence_id
2289 from BOM_COMPONENTS_B dest, BOM_COMPONENTS_B src
2290 where dest.component_sequence_id <> dest.common_component_sequence_id
2291 and dest.common_component_sequence_id = p_comp_seq_id
2292 and src.component_sequence_id = dest.common_component_sequence_id
2293 and ((src.implementation_date is null
2294 and dest.implementation_date is null
2295 )
2296 OR
2297 dest.implementation_date is not null
2298 );
2299
2300 BEGIN
2301 for ref_desg in get_ref_desg_details(p_component_sequence_id, p_ref_desg)
2302 loop
2303 for dest_comp in get_destn_comps(p_component_sequence_id)
2304 loop
2305 INSERT INTO BOM_REFERENCE_DESIGNATORS
2306 ( COMPONENT_REFERENCE_DESIGNATOR
2307 , LAST_UPDATE_DATE
2308 , LAST_UPDATED_BY
2309 , CREATION_DATE
2310 , CREATED_BY
2311 , LAST_UPDATE_LOGIN
2312 , REF_DESIGNATOR_COMMENT
2313 , CHANGE_NOTICE
2314 , COMPONENT_SEQUENCE_ID
2315 , ACD_TYPE
2316 , REQUEST_ID
2317 , PROGRAM_APPLICATION_ID
2318 , PROGRAM_ID
2319 , PROGRAM_UPDATE_DATE
2320 , ATTRIBUTE_CATEGORY
2321 , ATTRIBUTE1
2322 , ATTRIBUTE2
2323 , ATTRIBUTE3
2324 , ATTRIBUTE4
2325 , ATTRIBUTE5
2326 , ATTRIBUTE6
2327 , ATTRIBUTE7
2328 , ATTRIBUTE8
2329 , ATTRIBUTE9
2330 , ATTRIBUTE10
2331 , ATTRIBUTE11
2332 , ATTRIBUTE12
2333 , ATTRIBUTE13
2334 , ATTRIBUTE14
2335 , ATTRIBUTE15
2336 , Original_System_Reference
2337 , Common_component_sequence_id
2338 )
2339 VALUES
2340 ( ref_desg.component_reference_designator
2341 , SYSDATE
2342 , ref_desg.LAST_UPDATED_BY
2343 , SYSDATE
2344 , ref_desg.CREATED_BY
2345 , ref_desg.LAST_UPDATE_LOGIN
2346 , DECODE( ref_desg.ref_designator_comment
2347 , FND_API.G_MISS_CHAR
2348 , NULL
2349 , ref_desg.ref_designator_comment )
2350 , ref_desg.Change_Notice
2351 , dest_comp.component_sequence_id
2352 , ref_desg.acd_type
2353 , NULL /* Request Id */
2354 , Bom_Globals.Get_Prog_AppId
2355 , Bom_Globals.Get_Prog_Id
2356 , SYSDATE
2357 , ref_desg.attribute_category
2358 , ref_desg.attribute1
2359 , ref_desg.attribute2
2360 , ref_desg.attribute3
2361 , ref_desg.attribute4
2362 , ref_desg.attribute5
2363 , ref_desg.attribute6
2364 , ref_desg.attribute7
2365 , ref_desg.attribute8
2366 , ref_desg.attribute9
2367 , ref_desg.attribute10
2368 , ref_desg.attribute11
2369 , ref_desg.attribute12
2370 , ref_desg.attribute13
2371 , ref_desg.attribute14
2372 , ref_desg.attribute15
2373 , ref_desg.Original_System_Reference
2374 , ref_desg.component_sequence_id
2375 );
2376 end loop;--for dest_comp in get_destn_comps(p_component_sequence_id)
2377 end loop;--for ref_desg in get_ref_desg_details(p_component_sequence_id, p_ref_desg)
2378 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2379 EXCEPTION
2380 WHEN OTHERS THEN
2381 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2382 THEN
2383 l_err_text := G_PKG_NAME ||' : Common BOM (Related Ref Desg Insert) '
2384 ||SUBSTR(SQLERRM, 1, 200);
2385 Error_Handler.Add_Error_Token
2386 ( p_Message_Name => NULL
2387 , p_Message_Text => l_err_text
2388 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2389 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2390 );
2391 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2392 END IF;
2393 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
2394 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2395
2396 END Insert_Related_Ref_Desg;
2397
2398
2399 /*
2400 * This overloaded Procedure is called from Java to add reference designators to the related components of the common boms whenever
2401 * reference designator is added to a component of a source bom.
2402 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2403 * @param p_ref_desg IN Reference Designator added.
2404 */
2405 PROCEDURE Insert_Related_Ref_Desg(p_component_sequence_id IN NUMBER
2406 , p_ref_desg IN VARCHAR2)
2407 IS
2408 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2409 l_Return_Status VARCHAR2(1);
2410 BEGIN
2411 Insert_Related_Ref_Desg(p_component_sequence_id => p_component_sequence_id
2412 , p_ref_desg => p_ref_desg
2413 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2414 , x_Return_Status => l_Return_Status);
2415 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2416 THEN
2417 app_exception.raise_exception;
2418 END IF;
2419 END Insert_Related_Ref_Desg;
2420
2421 /*
2422 * This Procedure is used to update reference designators of the related components of the common boms whenever
2423 * reference designator of a component of a source bom is updated.
2424 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2425 * @param p_ref_desg IN Reference Designator updated.
2426 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2427 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2428 */
2429 PROCEDURE Update_Related_Ref_Desg(p_component_sequence_id IN NUMBER
2430 , p_old_ref_desg IN VARCHAR2
2431 , p_new_ref_desg IN VARCHAR2
2432 , p_acd_type IN NUMBER
2433 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2434 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2435 IS
2436 l_return_status varchar2(80);
2437 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2438 l_err_text VARCHAR2(2000);
2439
2440 Cursor get_ref_desg_details(p_comp_seq_id NUMBER,
2441 p_ref_desg VARCHAR2)
2442 IS
2443 select *
2444 from bom_reference_designators
2445 where component_sequence_id = p_comp_seq_id
2446 and component_reference_designator = p_ref_desg;
2447
2448 Cursor get_destn_comps(p_comp_seq_id number)
2449 is
2450 select component_sequence_id
2451 from BOM_COMPONENTS_B
2452 where component_sequence_id <> common_component_sequence_id
2453 and common_component_sequence_id = p_comp_seq_id;
2454
2455 BEGIN
2456 for ref_desg in get_ref_desg_details(p_component_sequence_id, p_new_ref_desg)
2457 loop
2458 /*for dest_comp in get_destn_comps(p_component_sequence_id)
2459 loop*/
2460 UPDATE BOM_REFERENCE_DESIGNATORS
2461 SET COMPONENT_REFERENCE_DESIGNATOR = ref_desg.COMPONENT_REFERENCE_DESIGNATOR
2462 , LAST_UPDATE_DATE = SYSDATE
2463 , LAST_UPDATED_BY = ref_desg.LAST_UPDATED_BY
2464 , LAST_UPDATE_LOGIN = ref_desg.LAST_UPDATE_LOGIN
2465 , REF_DESIGNATOR_COMMENT = ref_desg.REF_DESIGNATOR_COMMENT
2466 , ATTRIBUTE_CATEGORY = ref_desg.attribute_category
2467 , ATTRIBUTE1 = ref_desg.attribute1
2468 , ATTRIBUTE2 = ref_desg.attribute2
2469 , ATTRIBUTE3 = ref_desg.attribute3
2470 , ATTRIBUTE4 = ref_desg.attribute4
2471 , ATTRIBUTE5 = ref_desg.attribute5
2472 , ATTRIBUTE6 = ref_desg.attribute6
2473 , ATTRIBUTE7 = ref_desg.attribute7
2474 , ATTRIBUTE8 = ref_desg.attribute8
2475 , ATTRIBUTE9 = ref_desg.attribute9
2476 , ATTRIBUTE10 = ref_desg.attribute10
2477 , ATTRIBUTE11 = ref_desg.attribute11
2478 , ATTRIBUTE12 = ref_desg.attribute12
2479 , ATTRIBUTE13 = ref_desg.attribute13
2480 , ATTRIBUTE14 = ref_desg.attribute14
2481 , ATTRIBUTE15 = ref_desg.attribute15
2482 , Original_System_Reference =
2483 ref_desg.Original_System_Reference
2484 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_old_ref_desg
2485 AND COMMON_COMPONENT_SEQUENCE_ID = p_component_sequence_id
2486 AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
2487 AND NVL(ACD_TYPE, 0) = nvl(p_acd_type, 0);
2488 --end loop; --for dest_comp in get_destn_comps(p_component_sequence_id)
2489 end loop; --for ref_desg in get_ref_desg_details(p_component_sequence_id, p_ref_desg)
2490
2491 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2492
2493 EXCEPTION
2494 WHEN OTHERS THEN
2495
2496 /*IF G_CONTROL_REC.caller_type = 'FORM'
2497 THEN
2498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2499 RAISE;
2500 END IF;*/
2501 Error_Handler.Add_Error_Token
2502 ( p_Message_Name => NULL
2503 , p_Message_Text => 'ERROR in Update Row (Related Ref Desgs)' ||
2504 substr(SQLERRM, 1, 100) || ' ' ||
2505 to_char(SQLCODE)
2506 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2507 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl);
2508
2509 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
2510 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2511
2512
2513 END Update_Related_Ref_Desg;
2514
2515
2516 /*
2517 * This overloaded Procedure is called from Java to update reference designators of the related components of the common boms whenever
2518 * reference designator of a component of a source bom is updated.
2519 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2520 * @param p_ref_desg IN Reference Designator updated.
2521 */
2522 PROCEDURE Update_Related_Ref_Desg(p_component_sequence_id IN NUMBER
2523 , p_old_ref_desg IN VARCHAR2
2524 , p_new_ref_desg IN VARCHAR2
2525 , p_acd_type IN NUMBER)
2526 IS
2527 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2528 l_Return_Status VARCHAR2(1);
2529 BEGIN
2530 Update_Related_Ref_Desg(p_component_sequence_id => p_component_sequence_id
2531 , p_new_ref_desg => p_new_ref_desg
2532 , p_old_ref_desg => p_old_ref_desg
2533 , p_acd_type => p_acd_type
2534 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2535 , x_Return_Status => l_Return_Status);
2536 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2537 THEN
2538 app_exception.raise_exception;
2539 END IF;
2540 END;
2541
2542
2543 /*
2544 * This Procedure will replicate the substitutes of components of the source BOM as susbtitutes of components of the Common BOM.
2545 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2546 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2547 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2548 */
2549 PROCEDURE Replicate_Sub_Comp(p_component_sequence_id IN NUMBER
2550 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2551 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2552 IS
2553
2554 Cursor get_sub_comps(p_component_sequence_id NUMBER)
2555 is
2556 SELECT * from bom_substitute_components
2557 where component_sequence_id = p_component_sequence_id;
2558
2559 Cursor get_destn_comps(p_comp_seq_id number)
2560 is
2561 select component_sequence_id
2562 from BOM_COMPONENTS_B
2563 where component_sequence_id <> common_component_sequence_id
2564 and common_component_sequence_id = p_component_sequence_id;
2565
2566 l_return_status varchar2(80);
2567 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2568 l_err_text VARCHAR2(2000);
2569
2570 BEGIN
2571 INSERT INTO BOM_SUBSTITUTE_COMPONENTS
2572 ( SUBSTITUTE_COMPONENT_ID
2573 , LAST_UPDATE_DATE
2574 , LAST_UPDATED_BY
2575 , CREATION_DATE
2576 , CREATED_BY
2577 , LAST_UPDATE_LOGIN
2578 , SUBSTITUTE_ITEM_QUANTITY
2579 , COMPONENT_SEQUENCE_ID
2580 , ACD_TYPE
2581 , CHANGE_NOTICE
2582 , REQUEST_ID
2583 , PROGRAM_APPLICATION_ID
2584 , PROGRAM_UPDATE_DATE
2585 , ATTRIBUTE_CATEGORY
2586 , ATTRIBUTE1
2587 , ATTRIBUTE2
2588 , ATTRIBUTE3
2589 , ATTRIBUTE4
2590 , ATTRIBUTE5
2591 , ATTRIBUTE6
2592 , ATTRIBUTE7
2593 , ATTRIBUTE8
2594 , ATTRIBUTE9
2595 , ATTRIBUTE10
2596 , ATTRIBUTE11
2597 , ATTRIBUTE12
2598 , ATTRIBUTE13
2599 , ATTRIBUTE14
2600 , ATTRIBUTE15
2601 , PROGRAM_ID
2602 , Original_System_Reference
2603 , Enforce_Int_Requirements
2604 , Common_component_sequence_id
2605 )
2606 SELECT
2607 sub_comp.substitute_component_id
2608 , SYSDATE
2609 , sub_comp.LAST_UPDATED_BY
2610 , SYSDATE
2611 , sub_comp.CREATED_BY
2612 , sub_comp.LAST_UPDATE_LOGIN
2613 , sub_comp.substitute_item_quantity
2614 , dest_comp.component_sequence_id
2615 , sub_comp.acd_type
2616 , sub_comp.Change_Notice
2617 , NULL /* Request Id */
2618 , Bom_Globals.Get_Prog_AppId
2619 , SYSDATE
2620 , sub_comp.attribute_category
2621 , sub_comp.attribute1
2622 , sub_comp.attribute2
2623 , sub_comp.attribute3
2624 , sub_comp.attribute4
2625 , sub_comp.attribute5
2626 , sub_comp.attribute6
2627 , sub_comp.attribute7
2628 , sub_comp.attribute8
2629 , sub_comp.attribute9
2630 , sub_comp.attribute10
2631 , sub_comp.attribute11
2632 , sub_comp.attribute12
2633 , sub_comp.attribute13
2634 , sub_comp.attribute14
2635 , sub_comp.attribute15
2636 , Bom_Globals.Get_Prog_Id
2637 , sub_comp.Original_System_Reference
2638 , sub_comp.enforce_int_requirements
2639 , sub_comp.component_sequence_id
2640 FROM BOM_SUBSTITUTE_COMPONENTS sub_comp, BOM_COMPONENTS_B dest_comp
2641 WHERE dest_comp.component_Sequence_id <> dest_comp.common_component_sequence_id
2642 AND dest_comp.common_component_sequence_id = sub_comp.component_sequence_id
2643 AND sub_comp.component_sequence_id = p_component_sequence_id
2644 AND NOT EXISTS
2645 (
2646 SELECT 1
2647 FROM bom_substitute_components bsc2
2648 where bsc2.component_sequence_id = dest_comp.component_sequence_id
2649 and bsc2.substitute_component_id = sub_comp.substitute_component_id
2650 )
2651 ;
2652
2653 x_return_status := FND_API.G_RET_STS_SUCCESS;
2654
2655 EXCEPTION
2656
2657 WHEN OTHERS THEN
2658 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2659 THEN
2660 l_err_text := G_PKG_NAME ||'Utility (Substitute Component Replicate)'
2661 ||SUBSTR(SQLERRM, 1, 100);
2662 Error_Handler.Add_Error_Token
2663 ( p_Message_Name => NULL
2664 , p_Message_text => l_err_text
2665 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2666 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2667 );
2668 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2669 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2670 fnd_message.set_name('BOM', 'BOM_REPLICATE_FAILED');
2671 --arudresh_debug('error in replicate sub comp '||SQLERRM);
2672 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2673 END Replicate_Sub_Comp;
2674
2675 /*
2676 * This overloaded Procedure is called from Java to replicate the substitutes of components of the source BOM
2677 * as susbtitutes of components of the Common BOM.
2678 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2679 */
2680 PROCEDURE Replicate_Sub_Comp(p_component_sequence_id IN NUMBER)
2681 IS
2682 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2683 l_Return_Status VARCHAR2(1);
2684 BEGIN
2685 Replicate_Sub_Comp(p_component_sequence_id => p_component_sequence_id
2686 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2687 , x_Return_Status => l_Return_Status);
2688 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2689 THEN
2690 app_exception.raise_exception;
2691 END IF;
2692 END;
2693
2694
2695 /*
2696 * This Procedure is used to add Substitute Components to the related components of the common boms whenever
2697 * a substitute component is added to a component of a source bom.
2698 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2699 * @param p_sub_comp_item_id IN Substitute Component Id added.
2700 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2701 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2702 */
2703 PROCEDURE Insert_Related_Sub_Comp(p_component_sequence_id IN NUMBER
2704 , p_sub_comp_item_id IN NUMBER
2705 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2706 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2707 IS
2708
2709 l_return_status varchar2(80);
2710 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2711 l_err_text VARCHAR2(2000);
2712
2713 Cursor get_sub_comp_details(p_comp_seq_id NUMBER,
2714 p_sub_comp_item_id NUMBER)
2715 IS
2716 select *
2717 from bom_substitute_components
2718 where component_sequence_id = p_comp_seq_id
2719 and substitute_component_id = p_sub_comp_item_id;
2720
2721 Cursor get_destn_comps(p_comp_seq_id number)
2722 is
2723 select dest.component_sequence_id
2724 from BOM_COMPONENTS_B dest, BOM_COMPONENTS_B src
2725 where dest.component_sequence_id <> dest.common_component_sequence_id
2726 and dest.common_component_sequence_id = p_comp_seq_id
2727 and src.component_sequence_id = dest.common_component_sequence_id
2728 and ((src.implementation_date is null
2729 and dest.implementation_date is null
2730 )
2731 OR
2732 dest.implementation_date is not null
2733 );
2734
2735 BEGIN
2736 for sub_comp in get_sub_comp_details(p_component_sequence_id, p_sub_comp_item_id)
2737 loop
2738 for dest_comp in get_destn_comps(p_component_sequence_id)
2739 loop
2740
2741 INSERT INTO BOM_SUBSTITUTE_COMPONENTS
2742 ( SUBSTITUTE_COMPONENT_ID
2743 , LAST_UPDATE_DATE
2744 , LAST_UPDATED_BY
2745 , CREATION_DATE
2746 , CREATED_BY
2747 , LAST_UPDATE_LOGIN
2748 , SUBSTITUTE_ITEM_QUANTITY
2749 , COMPONENT_SEQUENCE_ID
2750 , ACD_TYPE
2751 , CHANGE_NOTICE
2752 , REQUEST_ID
2753 , PROGRAM_APPLICATION_ID
2754 , PROGRAM_UPDATE_DATE
2755 , ATTRIBUTE_CATEGORY
2756 , ATTRIBUTE1
2757 , ATTRIBUTE2
2758 , ATTRIBUTE3
2759 , ATTRIBUTE4
2760 , ATTRIBUTE5
2761 , ATTRIBUTE6
2762 , ATTRIBUTE7
2763 , ATTRIBUTE8
2764 , ATTRIBUTE9
2765 , ATTRIBUTE10
2766 , ATTRIBUTE11
2767 , ATTRIBUTE12
2768 , ATTRIBUTE13
2769 , ATTRIBUTE14
2770 , ATTRIBUTE15
2771 , PROGRAM_ID
2772 , Original_System_Reference
2773 , Enforce_Int_Requirements
2774 , Common_component_sequence_id
2775 )
2776 VALUES
2777 ( sub_comp.substitute_component_id
2778 , SYSDATE
2779 , sub_comp.LAST_UPDATED_BY
2780 , SYSDATE
2781 , sub_comp.CREATED_BY
2782 , sub_comp.LAST_UPDATE_LOGIN
2783 , sub_comp.substitute_item_quantity
2784 , dest_comp.component_sequence_id
2785 , sub_comp.acd_type
2786 , sub_comp.Change_Notice
2787 , NULL /* Request Id */
2788 , Bom_Globals.Get_Prog_AppId
2789 , SYSDATE
2790 , sub_comp.attribute_category
2791 , sub_comp.attribute1
2792 , sub_comp.attribute2
2793 , sub_comp.attribute3
2794 , sub_comp.attribute4
2795 , sub_comp.attribute5
2796 , sub_comp.attribute6
2797 , sub_comp.attribute7
2798 , sub_comp.attribute8
2799 , sub_comp.attribute9
2800 , sub_comp.attribute10
2801 , sub_comp.attribute11
2802 , sub_comp.attribute12
2803 , sub_comp.attribute13
2804 , sub_comp.attribute14
2805 , sub_comp.attribute15
2806 , Bom_Globals.Get_Prog_Id
2807 , sub_comp.Original_System_Reference
2808 , sub_comp.enforce_int_requirements
2809 , sub_comp.component_sequence_id
2810 );
2811
2812 end loop; --for dest_comp in get_destn_comps(p_component_sequence_id)
2813 end loop;--for sub_comp in get_sub_comp_details(p_component_sequence_id, p_sub_comp_item_id)
2814 x_return_status := FND_API.G_RET_STS_SUCCESS;
2815
2816 EXCEPTION
2817
2818 WHEN OTHERS THEN
2819 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2820 THEN
2821 l_err_text := G_PKG_NAME ||'Utility (Related Substitute Component Insert)'
2822 ||SUBSTR(SQLERRM, 1, 100);
2823 Error_Handler.Add_Error_Token
2824 ( p_Message_Name => NULL
2825 , p_Message_text => l_err_text
2826 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2827 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2828 );
2829 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2830 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2831 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
2832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2833 END Insert_Related_Sub_Comp;
2834
2835
2836
2837 /*
2838 * This overloaded Procedure is called from Java to add Substitute Components to the related components of the common boms whenever
2839 * a substitute component is added to a component of a source bom.
2840 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2841 * @param p_sub_comp_item_id IN Substitute Component Id added.
2842 */
2843 PROCEDURE Insert_Related_Sub_Comp(p_component_sequence_id IN NUMBER
2844 , p_sub_comp_item_id IN NUMBER)
2845 IS
2846 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2847 l_Return_Status VARCHAR2(1);
2848 begin
2849 Insert_Related_Sub_Comp(p_component_sequence_id => p_component_sequence_id
2850 , p_sub_comp_item_id => p_sub_comp_item_id
2851 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2852 , x_Return_Status => l_Return_Status);
2853 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2854 THEN
2855 app_exception.raise_exception;
2856 END IF;
2857 end;
2858
2859 /*
2860 * This Procedure is used to update substitutes of the related components of the common boms whenever
2861 * substitute of a component of a source bom is updated.
2862 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2863 * @param p_sub_comp_item_id IN Substitute Component Id updated.
2864 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2865 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2866 */
2867 PROCEDURE Update_Related_Sub_Comp(p_component_sequence_id IN NUMBER
2868 , p_old_sub_comp_item_id IN NUMBER
2869 , p_new_sub_comp_item_id IN NUMBER
2870 , p_acd_type IN NUMBER
2871 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2872 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2873 IS
2874
2875 l_return_status varchar2(80);
2876 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2877 l_err_text VARCHAR2(2000);
2878 l_acd_type NUMBER;
2879
2880 Cursor get_sub_comp_details(p_comp_seq_id NUMBER,
2881 p_sub_comp_item_id NUMBER)
2882 IS
2883 select *
2884 from bom_substitute_components
2885 where component_sequence_id = p_comp_seq_id
2886 and substitute_component_id = p_new_sub_comp_item_id
2887 and nvl(acd_type, 0) = nvl(p_acd_type, 0);
2888
2889 Cursor get_destn_comps(p_comp_seq_id number)
2890 is
2891 select component_sequence_id
2892 from BOM_COMPONENTS_B
2893 where component_sequence_id <> common_component_sequence_id
2894 and common_component_sequence_id = p_comp_seq_id;
2895
2896 BEGIN
2897 for sub_comp in get_sub_comp_details(p_component_sequence_id, p_new_sub_comp_item_id)
2898 loop
2899 /*for dest_comp in get_destn_comps(p_component_sequence_id)
2900 loop
2901 l_acd_type := sub_comp.acd_type;*/
2902 UPDATE BOM_SUBSTITUTE_COMPONENTS
2903 SET SUBSTITUTE_COMPONENT_ID = sub_comp.substitute_component_id
2904 , SUBSTITUTE_ITEM_QUANTITY = sub_comp.substitute_item_quantity
2905 , ATTRIBUTE_CATEGORY = sub_comp.attribute_category
2906 , ATTRIBUTE1 = sub_comp.attribute1
2907 , ATTRIBUTE2 = sub_comp.attribute2
2908 , ATTRIBUTE3 = sub_comp.attribute3
2909 , ATTRIBUTE4 = sub_comp.attribute4
2910 , ATTRIBUTE5 = sub_comp.attribute5
2911 , ATTRIBUTE6 = sub_comp.attribute6
2912 , ATTRIBUTE7 = sub_comp.attribute7
2913 , ATTRIBUTE8 = sub_comp.attribute8
2914 , ATTRIBUTE9 = sub_comp.attribute9
2915 , ATTRIBUTE10 = sub_comp.attribute10
2916 , ATTRIBUTE11 = sub_comp.attribute11
2917 , ATTRIBUTE12 = sub_comp.attribute12
2918 , ATTRIBUTE13 = sub_comp.attribute13
2919 , ATTRIBUTE14 = sub_comp.attribute14
2920 , ATTRIBUTE15 = sub_comp.attribute15
2921 , Original_system_Reference =
2922 sub_comp.original_system_reference
2923 , Enforce_Int_Requirements = sub_comp.Enforce_Int_Requirements
2924 WHERE SUBSTITUTE_COMPONENT_ID = p_old_sub_comp_item_id
2925 AND COMMON_COMPONENT_SEQUENCE_ID = sub_comp.component_sequence_id
2926 AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
2927 AND nvl(ACD_TYPE,0) = nvl(p_acd_type, 0)
2928 ;
2929 --end loop;--for dest_comp in get_destn_comps(p_component_sequence_id)
2930 end loop; --for sub_comp in get_sub_comp_details(p_component_sequence_id, p_sub_comp_item_id)
2931 x_return_status := FND_API.G_RET_STS_SUCCESS;
2932 EXCEPTION
2933
2934 WHEN OTHERS THEN
2935 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2936 THEN
2937 l_err_text := G_PKG_NAME ||'Utility (Related Substitute Component Insert)'
2938 ||SUBSTR(SQLERRM, 1, 100);
2939 Error_Handler.Add_Error_Token
2940 ( p_Message_Name => NULL
2941 , p_Message_text => l_err_text
2942 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2943 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2944 );
2945 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2946 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2947 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
2948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2949 END Update_Related_Sub_Comp;
2950
2951 /*
2952 * This overloaded Procedure is called from Java to update substitutes of the related components of the common boms whenever
2953 * substitute of a component of a source bom is updated.
2954 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2955 * @param p_sub_comp_item_id IN Substitute Component Id updated.
2956 */
2957 PROCEDURE Update_Related_Sub_Comp(p_component_sequence_id IN NUMBER
2958 , p_old_sub_comp_item_id IN NUMBER
2959 , p_new_sub_comp_item_id IN NUMBER
2960 , p_acd_type IN NUMBER)
2961 IS
2962 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2963 l_Return_Status VARCHAR2(1);
2964 BEGIN
2965 Update_Related_Sub_Comp(p_component_sequence_id => p_component_sequence_id
2966 , p_old_sub_comp_item_id => p_old_sub_comp_item_id
2967 , p_new_sub_comp_item_id => p_new_sub_comp_item_id
2968 , p_acd_type => p_acd_type
2969 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2970 , x_Return_Status => l_Return_Status);
2971 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
2972 THEN
2973 app_exception.raise_exception;
2974 END IF;
2975 END;
2976
2977
2978
2979
2980
2981
2982 /*
2983 * This Procedure will replicate the component operations of the source BOM as component operations of the Common BOM.
2984 * @param p_component_sequence_id IN Component Sequence Id of the component updated
2985 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
2986 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
2987 */
2988 PROCEDURE Replicate_Comp_Ops(p_component_sequence_id IN NUMBER
2989 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2990 , x_Return_Status IN OUT NOCOPY VARCHAR2)
2991 IS
2992
2993 Cursor get_comp_ops(p_component_sequence_id NUMBER)
2994 is
2995 SELECT * from bom_component_operations
2996 where component_sequence_id = p_component_sequence_id;
2997
2998 Cursor get_destn_comps(p_comp_seq_id number)
2999 is
3000 select *
3001 from BOM_COMPONENTS_B
3002 where component_sequence_id <> common_component_sequence_id
3003 and common_component_sequence_id = p_comp_seq_id;
3004
3005 l_return_status varchar2(80);
3006 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3007 l_err_text VARCHAR2(2000);
3008 l_dummy NUMBER;
3009 l_token_tbl Error_Handler.Token_tbl_Type;
3010 l_comp_op_exists VARCHAR2(1);
3011
3012 BEGIN
3013
3014 BEGIN
3015 SELECT 'Y'
3016 INTO l_comp_op_exists
3017 FROM BOM_COMPONENT_OPERATIONS
3018 WHERE component_sequence_id = p_component_sequence_id;
3019 EXCEPTION
3020 WHEN NO_DATA_FOUND THEN
3021 --No comp ops to replicate, return
3022 x_return_status := FND_API.G_RET_STS_SUCCESS;
3023 Return;
3024 WHEN TOO_MANY_ROWS THEN
3025 --Continue with validations
3026 Null;
3027 END;
3028 --Check whether the op seq num of the comp op is valid for dest bills.
3029 --If not, return with error.
3030
3031
3032 FOR destn_comps in get_destn_comps(p_comp_seq_id => p_component_sequence_id)
3033 loop
3034 BEGIN
3035 SELECT bco.operation_seq_num
3036 INTO l_dummy
3037 FROM bom_component_operations bco, BOM_COMPONENTS_B bic
3038 WHERE bco.component_sequence_id = bic.component_sequence_id
3039 AND bic.component_sequence_id = p_component_sequence_id
3040 AND EXISTS(
3041 SELECT operation_seq_num, bos.routing_sequence_id
3042 FROM bom_operational_routings bor, bom_operation_sequences bos, BOM_STRUCTURES_B bom
3043 WHERE bos.routing_sequence_id = bor.common_routing_sequence_id
3044 AND bos.operation_seq_num = bco.operation_seq_num
3045 AND bor.assembly_item_id = bom.assembly_item_id
3046 AND bor.organization_id = bom.ORGANIZATION_id
3047 AND nvl(bor.alternate_routing_designator, 'XXX') = Nvl(bom.alternate_bom_designator, 'XXX')
3048 AND bom.bill_sequence_id = destn_comps.bill_sequence_id
3049 );
3050 EXCEPTION
3051 WHEN NO_DATA_FOUND THEN
3052 --At least one referring bill's rtg does not have the op seq of the comp op defined.
3053 Error_Handler.Add_Error_Token
3054 ( p_Message_Name => 'BOM_COMMON_OP_SEQ_INVALID'
3055 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3056 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3057 , p_Token_Tbl => l_Token_Tbl
3058 );
3059 fnd_message.set_name('BOM', 'BOM_COMMON_OP_SEQ_INVALID');
3060 x_return_status := FND_API.G_RET_STS_ERROR;
3061 Return;
3062 WHEN TOO_MANY_ROWS THEN
3063 --Means just that more than one comp opn is associated with the comp.
3064 NULL;
3065 END;
3066 END LOOP;
3067
3068 INSERT INTO bom_component_operations
3069 (
3070 COMP_OPERATION_SEQ_ID ,
3071 OPERATION_SEQ_NUM ,
3072 OPERATION_SEQUENCE_ID ,
3073 LAST_UPDATE_DATE ,
3074 LAST_UPDATED_BY ,
3075 CREATION_DATE ,
3076 CREATED_BY ,
3077 LAST_UPDATE_LOGIN ,
3078 COMPONENT_SEQUENCE_ID ,
3079 BILL_SEQUENCE_ID ,
3080 ATTRIBUTE_CATEGORY ,
3081 ATTRIBUTE1 ,
3082 ATTRIBUTE2 ,
3083 ATTRIBUTE3 ,
3084 ATTRIBUTE4 ,
3085 ATTRIBUTE5 ,
3086 ATTRIBUTE6 ,
3087 ATTRIBUTE7 ,
3088 ATTRIBUTE8 ,
3089 ATTRIBUTE9 ,
3090 ATTRIBUTE10 ,
3091 ATTRIBUTE11 ,
3092 ATTRIBUTE12 ,
3093 ATTRIBUTE13 ,
3094 ATTRIBUTE14 ,
3095 ATTRIBUTE15 ,
3096 COMMON_COMPONENT_SEQUENCE_ID)
3097 SELECT
3098 bom_component_operations_s.NEXTVAL ,
3099 comp_ops.OPERATION_SEQ_NUM ,
3100 comp_ops.OPERATION_SEQUENCE_ID ,
3101 comp_ops.LAST_UPDATE_DATE ,
3102 comp_ops.LAST_UPDATED_BY ,
3103 comp_ops.CREATION_DATE ,
3104 comp_ops.CREATED_BY ,
3105 comp_ops.LAST_UPDATE_LOGIN ,
3106 dest_comp.COMPONENT_SEQUENCE_ID ,
3107 dest_comp.BILL_SEQUENCE_ID ,
3108 comp_ops.ATTRIBUTE_CATEGORY ,
3109 comp_ops.ATTRIBUTE1 ,
3110 comp_ops.ATTRIBUTE2 ,
3111 comp_ops.ATTRIBUTE3 ,
3112 comp_ops.ATTRIBUTE4 ,
3113 comp_ops.ATTRIBUTE5 ,
3114 comp_ops.ATTRIBUTE6 ,
3115 comp_ops.ATTRIBUTE7 ,
3116 comp_ops.ATTRIBUTE8 ,
3117 comp_ops.ATTRIBUTE9 ,
3118 comp_ops.ATTRIBUTE10 ,
3119 comp_ops.ATTRIBUTE11 ,
3120 comp_ops.ATTRIBUTE12 ,
3121 comp_ops.ATTRIBUTE13 ,
3122 comp_ops.ATTRIBUTE14 ,
3123 comp_ops.ATTRIBUTE15 ,
3124 comp_ops.COMPONENT_SEQUENCE_ID
3125 FROM BOM_COMPONENT_OPERATIONS comp_ops, BOM_COMPONENTS_B dest_comp
3126 WHERE dest_comp.component_Sequence_id <> dest_comp.common_component_sequence_id
3127 AND dest_comp.common_component_sequence_id = comp_ops.component_sequence_id
3128 AND comp_ops.component_sequence_id = p_component_sequence_id
3129 AND NOT EXISTS
3130 (
3131 SELECT 1
3132 FROM BOM_COMPONENT_OPERATIONS ops2
3133 where ops2.component_sequence_id = dest_comp.component_sequence_id
3134 )
3135 ;
3136
3137 x_return_status := FND_API.G_RET_STS_SUCCESS;
3138
3139 EXCEPTION
3140
3141 WHEN OTHERS THEN
3142 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3143 THEN
3144 l_err_text := G_PKG_NAME ||'Utility (Component Operations Replicate)'
3145 ||SUBSTR(SQLERRM, 1, 100);
3146 Error_Handler.Add_Error_Token
3147 ( p_Message_Name => NULL
3148 , p_Message_text => l_err_text
3149 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3150 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3151 );
3152 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3153 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3154 fnd_message.set_name('BOM', 'BOM_REPLICATE_FAILED');
3155 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3156 END Replicate_Comp_Ops;
3157
3158
3159 /*
3160 * This overloaded Procedure is called from Java to replicate the component operations of the source BOM
3161 * as component operations of the Common BOM.
3162 * @param p_component_sequence_id IN Component Sequence Id of the component updated
3163 */
3164 PROCEDURE Replicate_Comp_Ops(p_component_sequence_id IN NUMBER)
3165 IS
3166 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3167 l_Return_Status VARCHAR2(1);
3168 BEGIN
3169 Replicate_Comp_Ops(p_component_sequence_id => p_component_sequence_id
3170 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3171 , x_Return_Status => l_Return_Status);
3172 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
3173 THEN
3174 app_exception.raise_exception;
3175 END IF;
3176 END;
3177
3178
3179 /*
3180 * This Procedure is used to add Component Operations to the related components of the common boms whenever
3181 * a component operation is added to a component of a source bom.
3182 * @param p_component_sequence_id IN Component Sequence Number of the component updated
3183 * @param p_operation_seq_num IN Operation Sequence number added.
3184 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
3185 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
3186 */
3187 PROCEDURE Insert_Related_Comp_Ops(p_component_sequence_id IN NUMBER
3188 , p_operation_seq_num IN NUMBER
3189 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3190 , x_Return_Status IN OUT NOCOPY VARCHAR2)
3191 IS
3192
3193 l_return_status varchar2(80);
3194 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3195 l_err_text VARCHAR2(2000);
3196
3197 Cursor get_comp_op_details(p_comp_seq_id NUMBER,
3198 p_operation_seq_num NUMBER)
3199 IS
3200 select *
3201 from bom_component_operations
3202 where component_sequence_id = p_comp_seq_id
3203 and operation_seq_num= p_operation_seq_num;
3204
3205 Cursor get_destn_comps(p_comp_seq_id number)
3206 is
3207 select component_sequence_id, bill_sequence_id
3208 from BOM_COMPONENTS_B
3209 where component_sequence_id <> common_component_sequence_id
3210 and common_component_sequence_id = p_comp_seq_id;
3211
3212 BEGIN
3213 for comp_ops in get_comp_op_details(p_component_sequence_id, p_operation_seq_num)
3214 loop
3215 for dest_comp in get_destn_comps(p_component_sequence_id)
3216 loop
3217 INSERT INTO bom_component_operations
3218 (
3219 COMP_OPERATION_SEQ_ID ,
3220 OPERATION_SEQ_NUM ,
3221 OPERATION_SEQUENCE_ID ,
3222 LAST_UPDATE_DATE ,
3223 LAST_UPDATED_BY ,
3224 CREATION_DATE ,
3225 CREATED_BY ,
3226 LAST_UPDATE_LOGIN ,
3227 COMPONENT_SEQUENCE_ID ,
3228 BILL_SEQUENCE_ID ,
3229 ATTRIBUTE_CATEGORY ,
3230 ATTRIBUTE1 ,
3231 ATTRIBUTE2 ,
3232 ATTRIBUTE3 ,
3233 ATTRIBUTE4 ,
3234 ATTRIBUTE5 ,
3235 ATTRIBUTE6 ,
3236 ATTRIBUTE7 ,
3237 ATTRIBUTE8 ,
3238 ATTRIBUTE9 ,
3239 ATTRIBUTE10 ,
3240 ATTRIBUTE11 ,
3241 ATTRIBUTE12 ,
3242 ATTRIBUTE13 ,
3243 ATTRIBUTE14 ,
3244 ATTRIBUTE15 ,
3245 COMMON_COMPONENT_SEQUENCE_ID)
3246 VALUES(
3247 bom_component_operations_s.NEXTVAL ,
3248 comp_ops.OPERATION_SEQ_NUM ,
3249 comp_ops.OPERATION_SEQUENCE_ID ,
3250 comp_ops.LAST_UPDATE_DATE ,
3251 comp_ops.LAST_UPDATED_BY ,
3252 comp_ops.CREATION_DATE ,
3253 comp_ops.CREATED_BY ,
3254 comp_ops.LAST_UPDATE_LOGIN ,
3255 dest_comp.COMPONENT_SEQUENCE_ID ,
3256 dest_comp.BILL_SEQUENCE_ID ,
3257 comp_ops.ATTRIBUTE_CATEGORY ,
3258 comp_ops.ATTRIBUTE1 ,
3259 comp_ops.ATTRIBUTE2 ,
3260 comp_ops.ATTRIBUTE3 ,
3261 comp_ops.ATTRIBUTE4 ,
3262 comp_ops.ATTRIBUTE5 ,
3263 comp_ops.ATTRIBUTE6 ,
3264 comp_ops.ATTRIBUTE7 ,
3265 comp_ops.ATTRIBUTE8 ,
3266 comp_ops.ATTRIBUTE9 ,
3267 comp_ops.ATTRIBUTE10 ,
3268 comp_ops.ATTRIBUTE11 ,
3269 comp_ops.ATTRIBUTE12 ,
3270 comp_ops.ATTRIBUTE13 ,
3271 comp_ops.ATTRIBUTE14 ,
3272 comp_ops.ATTRIBUTE15 ,
3273 comp_ops.component_sequence_id
3274 );
3275 end loop; --for dest_comp in get_destn_comps(p_component_sequence_id)
3276 end loop; --for comp_ops in get_comp_op_details(p_component_sequence_id, p_comp_operation_seq_id)
3277 x_return_status := FND_API.G_RET_STS_SUCCESS;
3278
3279 EXCEPTION
3280
3281 WHEN OTHERS THEN
3282 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3283 THEN
3284 l_err_text := G_PKG_NAME ||'Utility (Related Component Operation Insert)'
3285 ||SUBSTR(SQLERRM, 1, 100);
3286 Error_Handler.Add_Error_Token
3287 ( p_Message_Name => NULL
3288 , p_Message_text => l_err_text
3289 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3290 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3291 );
3292 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3293 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3294 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3296 END Insert_Related_Comp_Ops;
3297
3298
3299
3300 /*
3301 * This overloaded Procedure is called from Java to add Component Operations to the related components of the common boms whenever
3302 * a Component Operation is added to a component of a source bom.
3303 * @param p_component_sequence_id IN Component Sequence number of the component updated
3304 * @param p_operation_seq_num IN Operation Sequence Number added.
3305 */
3306 PROCEDURE Insert_Related_Comp_Ops(p_component_sequence_id IN NUMBER
3307 , p_operation_seq_num IN NUMBER)
3308 IS
3309 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3310 l_Return_Status VARCHAR2(1);
3311 begin
3312 Insert_Related_Comp_Ops(p_component_sequence_id => p_component_sequence_id
3313 , p_operation_seq_num => p_operation_seq_num
3314 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3315 , x_Return_Status => l_Return_Status);
3316 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
3317 THEN
3318 app_exception.raise_exception;
3319 END IF;
3320 end;
3321
3322 /*
3323 * This Procedure is used to update Component Operations of the related components of the common boms whenever
3324 * Component Operations of a source bom is updated.
3325 * @param p_component_sequence_id IN Component Sequence Id of the component updated
3326 * @param p_old_operation_seq_num IN Component Operation Id added.
3327 * @param p_new_operation_seq_num IN Component Operation Id added.
3328 * @param x_Mesg_Token_Tbl IN OUT Message tokens in the error message thrown.
3329 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
3330 */
3331 PROCEDURE Update_Related_Comp_Ops(p_component_sequence_id IN NUMBER
3332 , p_old_operation_seq_num IN NUMBER
3333 , p_new_operation_seq_num IN NUMBER
3334 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
3335 , x_Return_Status IN OUT NOCOPY VARCHAR2)
3336 IS
3337
3338 l_return_status varchar2(80);
3339 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3340 l_err_text VARCHAR2(2000);
3341
3342 Cursor get_comp_op_details(p_comp_seq_id NUMBER,
3343 p_new_operation_seq_num NUMBER)
3344 IS
3345 select *
3346 from bom_component_operations
3347 where component_sequence_id = p_comp_seq_id
3348 and operation_seq_num = p_new_operation_seq_num;
3349
3350 Cursor get_destn_comps(p_comp_seq_id number)
3351 is
3352 select component_sequence_id
3353 from BOM_COMPONENTS_B
3354 where component_sequence_id <> common_component_sequence_id
3355 and common_component_sequence_id = p_comp_seq_id;
3356
3357 BEGIN
3358 for comp_ops in get_comp_op_details(p_component_sequence_id, p_new_operation_seq_num)
3359 loop
3360 /*for dest_comp in get_destn_comps(p_component_sequence_id)
3361 loop*/
3362 UPDATE bom_component_operations SET
3363 OPERATION_SEQ_NUM = comp_ops.OPERATION_SEQ_NUM ,
3364 OPERATION_SEQUENCE_ID = comp_ops.OPERATION_SEQUENCE_ID,
3365 LAST_UPDATE_DATE = comp_ops.LAST_UPDATE_DATE ,
3366 LAST_UPDATED_BY = comp_ops.LAST_UPDATED_BY ,
3367 LAST_UPDATE_LOGIN = comp_ops.LAST_UPDATE_LOGIN ,
3368 ATTRIBUTE_CATEGORY = comp_ops.ATTRIBUTE_CATEGORY,
3369 ATTRIBUTE1 = comp_ops.ATTRIBUTE1 ,
3370 ATTRIBUTE2 = comp_ops.ATTRIBUTE2 ,
3371 ATTRIBUTE3 = comp_ops.ATTRIBUTE3 ,
3372 ATTRIBUTE4 = comp_ops.ATTRIBUTE4 ,
3373 ATTRIBUTE5 = comp_ops.ATTRIBUTE5 ,
3374 ATTRIBUTE6 = comp_ops.ATTRIBUTE6 ,
3375 ATTRIBUTE7 = comp_ops.ATTRIBUTE7 ,
3376 ATTRIBUTE8 = comp_ops.ATTRIBUTE8 ,
3377 ATTRIBUTE9 = comp_ops.ATTRIBUTE9 ,
3378 ATTRIBUTE10 = comp_ops.ATTRIBUTE10 ,
3379 ATTRIBUTE11 = comp_ops.ATTRIBUTE11 ,
3380 ATTRIBUTE12 = comp_ops.ATTRIBUTE12 ,
3381 ATTRIBUTE13 = comp_ops.ATTRIBUTE13 ,
3382 ATTRIBUTE14 = comp_ops.ATTRIBUTE14 ,
3383 ATTRIBUTE15 = comp_ops.ATTRIBUTE15,
3384 REQUEST_ID = comp_ops.REQUEST_ID,
3385 PROGRAM_ID = comp_ops.PROGRAM_ID,
3386 PROGRAM_APPLICATION_ID = comp_ops.PROGRAM_APPLICATION_ID,
3387 PROGRAM_UPDATE_DATE = comp_ops.PROGRAM_UPDATE_DATE
3388 WHERE OPERATION_SEQ_NUM = p_old_operation_seq_num
3389 AND COMMON_COMPONENT_SEQUENCE_ID = p_component_sequence_id
3390 AND COMMON_COMPONENT_SEQUENCE_ID <> COMPONENT_SEQUENCE_ID
3391 ;
3392 --end loop; --for dest_comp in get_destn_comps(p_component_sequence_id)
3393 end loop; --for comp_ops in get_comp_op_details(p_component_sequence_id, p_comp_operation_seq_id)
3394 x_return_status := FND_API.G_RET_STS_SUCCESS;
3395 EXCEPTION
3396
3397 WHEN OTHERS THEN
3398 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3399 THEN
3400 l_err_text := G_PKG_NAME ||'Utility (Related Component Operation Insert)'
3401 ||SUBSTR(SQLERRM, 1, 100);
3402 Error_Handler.Add_Error_Token
3403 ( p_Message_Name => NULL
3404 , p_Message_text => l_err_text
3405 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3406 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3407 );
3408 END IF; --IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3409 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
3410 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3411 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3412 END Update_Related_Comp_Ops;
3413
3414 /*
3415 * This overloaded Procedure is called from Java to update Component Operations of the common boms whenever
3416 * Component Operations of a source bom is updated.
3417 * @param p_component_sequence_id IN Component Sequence Id of the component updated
3418 * @param p_old_operation_seq_num IN Component Operation Id added.
3419 * @param p_new_operation_seq_num IN Component Operation Id added.
3420 */
3421 PROCEDURE Update_Related_Comp_Ops(p_component_sequence_id IN NUMBER
3422 , p_old_operation_seq_num IN NUMBER
3423 , p_new_operation_seq_num IN NUMBER)
3424 IS
3425 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
3426 l_Return_Status VARCHAR2(1);
3427 BEGIN
3428 Update_Related_Comp_Ops(p_component_sequence_id => p_component_sequence_id
3429 , p_old_operation_seq_num => p_old_operation_seq_num
3430 , p_new_operation_seq_num => p_new_operation_seq_num
3431 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
3432 , x_Return_Status => l_Return_Status);
3433 IF l_Return_Status <> FND_API.G_RET_STS_SUCCESS
3434 THEN
3435 app_exception.raise_exception;
3436 END IF;
3437 END;
3438
3439
3440
3441
3442 /*
3443 * This Procedure is used to delete related comp ops from the referencing boms when comp ops
3444 * from the source bom is deleted.
3445 * @param p_src_comp_seq_id IN Component Sequence Id of the source component.
3446 * @param p_operation_seq_num IN Operation sequence number of the dest source component.
3447 */
3448 PROCEDURE Delete_Related_Comp_Ops(p_src_comp_seq_id IN NUMBER,
3449 p_operation_seq_num IN NUMBER,
3450 x_return_status IN OUT NOCOPY VARCHAR2)
3451 IS
3452 BEGIN
3453 DELETE FROM BOM_COMPONENT_OPERATIONS
3454 WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq_id
3455 AND OPERATION_SEQ_NUM = p_operation_seq_num;
3456 x_return_status := FND_API.G_RET_STS_SUCCESS;
3457 EXCEPTION
3458 WHEN OTHERS THEN
3459 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3460 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3461 END;
3462
3463
3464 /*
3465 * This Procedure is used to validate the operation sequences of the source bom.
3466 * @param p_src_bill_sequence_id IN Bill Sequence Id of the source bom
3467 * @param p_assembly_item_id IN Assembly Item Id of the common bom.
3468 * @param p_organization_id IN Organization Id of the Commmon BOM
3469 * @param p_alt_desg IN Alternate BOM Designator of the BOM
3470 * @param x_Return_Status IN OUT Return Status of the api: S(uccess)/E(rror)/U(nexpected) error
3471 */
3472 PROCEDURE Validate_Operation_Sequence_Id(p_src_bill_sequence_id IN NUMBER
3473 , p_assembly_item_id IN NUMBER
3474 , p_organization_id IN NUMBER
3475 , p_alt_desg IN VARCHAR2
3476 , x_Return_Status IN OUT NOCOPY VARCHAR2)
3477 IS
3478
3479 /* Cursor check_src_routing_exists(p_src_bill_sequence_id NUMBER)
3480 IS
3481 Select 'Routing Exists'
3482 from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
3483 Where bom.assembly_item_id = bor.assembly_item_id
3484 And bom.organization_id = bor.organizatin_id
3485 And nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
3486 And bom.bill_sequence_id = p_src_bill_sequence_id*/
3487
3488 Cursor get_src_op_seq(p_src_bill_sequence_id NUMBER)
3489 IS
3490 Select OPERATION_SEQ_NUM
3491 From BOM_COMPONENTS_B
3492 Where bill_sequence_id = p_src_bill_sequence_id;
3493
3494 /* Cursor get_primary_rtg_opns(p_assy_item_id NUMBER, p_org_id NUMBER, p_alt_desg VARCHAR2)
3495 IS
3496 Select OPERATION_SEQ_NUM
3497 From BOM_OPERATION_SEQUENCES
3498 Where Routing_Sequence_Id = (Select common_routing_sequence_id
3499 from bom_operational_routings
3500 where assembly_item_id = p_assy_item_id
3501 and organization_id = p_org_id
3502 and alternate_routing_designator is null
3503 and not exists
3504 (select 1
3505 from bom_operational_routings
3506 where assembly_item_id = p_assy_item_id
3507 and organization_id = p_org_id
3508 and alternate_routing_designator = p_alt_desg
3509 )
3510 )
3511 UNION
3512 SELECT 1 from dual;
3513 */
3514 Cursor get_rtg_opns(p_assy_item_id NUMBER, p_org_id NUMBER, p_alt_desg VARCHAR2)
3515 IS
3516 Select OPERATION_SEQ_NUM
3517 From BOM_OPERATION_SEQUENCES
3518 Where Routing_Sequence_Id = (Select common_routing_sequence_id
3519 from bom_operational_routings
3520 where assembly_item_id = p_assy_item_id
3521 and organization_id = p_org_id
3522 and nvl(alternate_routing_designator, 'XXX') = nvl(p_alt_desg, 'XXX'))
3523
3524 UNION
3525 SELECT 1 from dual;
3526
3527 l_rtg_exist varchar2(30);
3528 l_stmt_num number;
3529 found boolean;
3530 valid_op_seq boolean;
3531 l_alt_rtg_exists NUMBER := 0;
3532
3533 BEGIN
3534 --Check if Routing exists for the source bom
3535 BEGIN
3536 Select 'Routing Exists'
3537 INTO l_rtg_exist
3538 from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
3539 Where bom.assembly_item_id = bor.assembly_item_id
3540 And bom.organization_id = bor.organization_id
3541 And (nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
3542 OR bor.alternate_routing_designator IS NULL)
3543 And bom.bill_sequence_id = p_src_bill_sequence_id;
3544 EXCEPTION
3545 WHEN TOO_MANY_ROWS THEN
3546 NULL;
3547 END;
3548
3549 IF p_alt_desg IS NOT NULL
3550 THEN
3551 BEGIN
3552 SELECT 1
3553 INTO l_alt_rtg_exists
3554 FROM BOM_OPERATIONAL_ROUTINGS
3555 WHERE assembly_item_id = p_assembly_item_id
3556 AND organization_id = p_organization_id
3557 AND alternate_routing_designator = p_alt_desg;
3558 EXCEPTION
3559 WHEN NO_DATA_FOUND THEN
3560 l_alt_rtg_exists := 0;
3561 END;
3562 END IF;
3563
3564 for src_op_seq in get_src_op_seq(p_src_bill_sequence_id)
3565 loop
3566 --arudresh_debug('Looking for op seq '||src_op_seq.OPERATION_SEQ_NUM);
3567 found := false;
3568 IF l_alt_rtg_exists = 1
3569 THEN
3570 /* check only in the alt rtg */
3571 for dest_op_Seq in get_rtg_opns(p_assembly_item_id, p_organization_id, p_alt_desg)
3572 loop
3573 if src_op_seq.OPERATION_SEQ_NUM = dest_op_seq.OPERATION_SEQ_NUM
3574 then
3575 found := true;
3576 end if; --if src_op_seq.OPERATION_SEQ_NUM = dest_op_seq.OPERATION_SEQ_NUM
3577 end loop; --for dest_op_Seq in get_rtg_opns(p_assembly_item_id, p_organization_id, p_alt_desg)
3578 ELSE
3579 for dest_op_Seq in get_rtg_opns(p_assembly_item_id, p_organization_id, null)
3580 loop
3581 if src_op_seq.OPERATION_SEQ_NUM = dest_op_seq.OPERATION_SEQ_NUM
3582 then
3583 found := true;
3584 end if; --if src_op_seq.OPERATION_SEQ_NUM = dest_op_seq.OPERATION_SEQ_NUM
3585 end loop; --for dest_op_Seq in get_rtg_opns(p_assembly_item_id, p_organization_id, p_alt_desg)
3586 END IF;
3587 if not found then
3588 valid_op_seq := false;
3589 end if; --if not found
3590 EXIT When not valid_op_seq;
3591 end loop; --for src_op_seq in get_src_op_seq(p_src_bill_sequence_id)
3592
3593 if not valid_op_seq then
3594 x_return_status := FND_API.G_RET_STS_ERROR;
3595 else
3596 x_return_status := FND_API.G_RET_STS_SUCCESS;
3597 end if; --if not valid_op_seq
3598 EXCEPTION
3599 WHEN NO_DATA_FOUND THEN
3600 --Source bill doesnt have a rtg. No validation reqd.
3601 x_return_status := FND_API.G_RET_STS_SUCCESS;
3602 RETURN;
3603 WHEN TOO_MANY_ROWS THEN
3604 NULL;
3605 END;
3606
3607 /*
3608 * This Procedure is used to replicate the component user attributes from the source bom.
3609 * @param p_src_bill_seq_id IN Bill Sequence Id of the source component.
3610 * @param p_dest_bill_seq_id IN Bill Sequence Id of the dest source component.
3611 */
3612 Procedure Replicate_Comp_User_Attrs(p_src_bill_seq_id IN NUMBER,
3613 p_dest_bill_seq_id IN NUMBER,
3614 x_Return_Status OUT NOCOPY VARCHAR2)
3615 IS
3616 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3617 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3618 l_new_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
3619 l_str_type NUMBER;
3620 l_errorcode NUMBER;
3621 l_msg_data VARCHAR2(100);
3622 l_msg_count NUMBER := 0;
3623 --l_return_status VARCHAR2(1);
3624 l_src_str_type NUMBER;
3625
3626 Cursor get_source_and_dest_components(p_bill_sequence_id Number)
3627 IS
3628 SELECT
3629 component_sequence_id, common_component_sequence_id
3630 from BOM_COMPONENTS_B
3631 where bill_sequence_id = p_bill_sequence_id
3632 and component_sequence_id <> common_component_sequence_id;
3633
3634 Cursor get_structure_type(p_bill_seq_id NUMBER)
3635 IS
3636 Select structure_type_id
3637 from BOM_STRUCTURES_B
3638 where bill_sequence_id = p_bill_seq_id;
3639
3640 BEGIN
3641
3642 Open get_structure_type(p_bill_seq_id => p_dest_bill_seq_id);
3643 Fetch get_structure_type INTO l_str_type;
3644 Close get_structure_type;
3645
3646 Open get_structure_type(p_bill_seq_id => p_src_bill_seq_id);
3647 Fetch get_structure_type INTO l_src_str_type;
3648 Close get_structure_type;
3649
3650 IF l_src_str_type <> l_str_type
3651 THEN
3652 --Cannot copy user attributes across structure types.
3653 return;
3654 END IF; --IF l_src_str_type <> l_str_type
3655
3656 for comp in get_source_and_dest_components(p_bill_sequence_id => p_dest_bill_seq_id)
3657 loop
3658 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(comp.common_component_sequence_id))
3659 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(p_src_bill_seq_id)) );
3660 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(comp.component_sequence_id)),
3661 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(p_dest_bill_seq_id)) );
3662 l_new_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID', TO_CHAR(l_str_type)));
3663 EGO_USER_ATTRS_DATA_PUB.Copy_User_Attrs_Data(
3664 p_api_version => 1.0
3665 ,p_application_id => 702
3666 ,p_object_name => 'BOM_COMPONENTS'
3667 ,p_old_pk_col_value_pairs => l_src_pk_col_name_val_pairs
3668 ,p_new_pk_col_value_pairs => l_dest_pk_col_name_val_pairs
3669 ,p_new_cc_col_value_pairs => l_new_str_type
3670 ,x_return_status => x_Return_Status
3671 ,x_errorcode => l_errorcode
3672 ,x_msg_count => l_msg_count
3673 ,x_msg_data => l_msg_data
3674 );
3675 IF x_Return_Status <> FND_API.G_RET_STS_SUCCESS
3676 THEN
3677 fnd_message.set_name('BOM', 'BOM_REPLICATE_FAILED');
3678 return;
3679 END IF;
3680 end loop; --for comp in get_source_and_dest_components(p_bill_sequence_id => p_dest_bill_seq_id)
3681 END Replicate_Comp_User_Attrs;
3682
3683 /*
3684 * This Procedure is used to copy the component user attributes from the source bom.
3685 * @param p_src_comp_seq_id IN Component Sequence Id of the source source component.
3686 */
3687 Procedure Propagate_Comp_User_Attributes(p_src_comp_seq_id IN NUMBER
3688 , p_attr_grp_id IN NUMBER
3689 , x_Return_Status OUT NOCOPY VARCHAR2)
3690 IS
3691 l_dest_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3692 l_src_pk_col_name_val_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3693 l_new_str_type EGO_COL_NAME_VALUE_PAIR_ARRAY;
3694 l_str_type NUMBER;
3695 l_errorcode NUMBER;
3696 l_msg_data VARCHAR2(100);
3697 l_msg_count NUMBER := 0;
3698 --l_return_status VARCHAR2(1);
3699 l_dest_comp_seq_id NUMBER;
3700 l_dest_comp_seq_id NUMBER;
3701 l_src_bill_seq_id NUMBER;
3702 l_src_str_type NUMBER;
3703
3704
3705 --l_return_status VARCHAR2(100);
3706 --l_errorcode NUMBER;
3707 --l_msg_count NUMBER ;
3708 --l_msg_data VARCHAR2(100);
3709 l_row_table_index NUMBER := 1;
3710 l_data_table_index NUMBER := 1;
3711 l_failed_row_count NUMBER;
3712 --l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3713 l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
3714 l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
3715 l_attr_group_request_table EGO_ATTR_GROUP_REQUEST_TABLE;
3716 l_current_row_element EGO_USER_ATTR_ROW_OBJ;
3717 l_current_data_element EGO_USER_ATTR_DATA_OBJ;
3718 l_ego_attr_group_request_obj EGO_ATTR_GROUP_REQUEST_OBJ;
3719 l_ego_col_name_value_pair_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
3720 --l_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
3721 --l_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
3722
3723 --Consolidated data and row tables
3724 --l_cons_attributes_data_table EGO_USER_ATTR_DATA_TABLE;
3725 --l_cons_attributes_row_table EGO_USER_ATTR_ROW_TABLE;
3726
3727 Cursor get_dest_comps(p_src_comp_seq_id NUMBER, p_str_type_id NUMBER)
3728 IS
3729 SELECT bcb.component_sequence_id, bcb.bill_sequence_id
3730 FROM BOM_COMPONENTS_B bcb, BOM_STRUCTURES_B bsb
3731 WHERE bcb.common_component_sequence_id = p_src_comp_seq_id
3732 AND bcb.common_component_sequence_id <> bcb.component_sequence_id
3733 AND bsb.structure_type_id = p_str_type_id
3734 AND bsb.bill_sequence_id = bcb.bill_sequence_id
3735 ;
3736
3737 Cursor get_structure_type(p_bill_seq_id NUMBER)
3738 IS
3739 SELECT structure_type_id
3740 FROM BOM_STRUCTURES_B
3741 WHERE bill_sequence_id = p_bill_seq_id;
3742
3743 Cursor Get_Attribute_Groups(p_component_seq_id NUMBER, p_bill_seq_id NUMBER)
3744 IS
3745 SELECT ATTR_GROUP_ID
3746 FROM BOM_COMPONENTS_EXT_B
3747 WHERE component_sequence_id = p_component_seq_id
3748 AND bill_Sequence_id = p_bill_seq_id;
3749
3750 BEGIN
3751 SELECT bill_sequence_id
3752 into l_src_bill_seq_id
3753 from BOM_COMPONENTS_B
3754 where component_sequence_id = p_src_comp_seq_id;
3755
3756 Open get_structure_type(p_bill_seq_id => l_src_bill_seq_id);
3757 Fetch get_structure_type into l_src_str_type;
3758 Close get_structure_type;
3759
3760 l_src_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(p_src_comp_seq_id))
3761 ,EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(l_src_bill_seq_id)) );
3762 --Table to maintain the attr grp data
3763 --l_cons_attributes_row_table := EGO_USER_ATTR_ROW_TABLE();
3764
3765 --Table to maintain attributes data
3766 --l_cons_attributes_data_table := EGO_USER_ATTR_DATA_TABLE();
3767
3768 --For each attr grp, get data from source, and copy in the destn comp
3769
3770 /*FOR attr_grp IN Get_Attribute_Groups(p_component_seq_id => p_src_comp_seq_id, p_bill_seq_id => l_src_bill_seq_id)
3771 loop*/
3772 l_ego_attr_group_request_obj := EGO_ATTR_GROUP_REQUEST_OBJ(p_attr_grp_id, 702 ,'BOM_COMPONENTMGMT_GROUP',
3773 NULL,NULL,NULL,NULL , NULL, NULL, NULL,NULL);
3774 l_attr_group_request_table := EGO_ATTR_GROUP_REQUEST_TABLE(l_ego_attr_group_request_obj);
3775
3776 EGO_USER_ATTRS_DATA_PUB.Get_User_Attrs_Data(
3777 p_api_version => 1.0
3778 ,p_object_name => 'BOM_COMPONENTS'
3779 ,p_pk_column_name_value_pairs => l_src_pk_col_name_val_pairs
3780 ,p_attr_group_request_table => l_attr_group_request_table
3781 ,p_commit => FND_API.G_TRUE
3782 ,x_attributes_row_table => l_attributes_row_table
3783 ,x_attributes_data_table => l_attributes_data_table
3784 ,x_return_status => x_return_status
3785 ,x_errorcode => l_errorcode
3786 ,x_msg_count => l_msg_count
3787 ,x_msg_data => l_msg_data
3788 );
3789 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3790 THEN
3791 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3792 Return;
3793 END IF;
3794 /* IF l_attributes_row_table IS NOT NULL
3795 THEN
3796 l_cons_attributes_row_table.EXTEND;
3797 l_cons_attributes_row_table(l_row_table_index) := l_attributes_row_table(1);
3798 l_cons_attributes_row_table(l_row_table_index).ROW_IDENTIFIER := l_row_table_index;
3799
3800 FOR i IN 1..l_attributes_data_table.COUNT
3801 LOOP
3802 l_cons_attributes_data_table.EXTEND;
3803 l_cons_attributes_data_table(l_data_table_index) := l_attributes_data_table(i);
3804 l_cons_attributes_data_table(l_data_table_index).ROW_IDENTIFIER := l_row_table_index;
3805 l_data_table_index := l_data_table_index + 1;
3806 END LOOP;
3807
3808 l_row_table_index := l_row_table_index + 1;
3809
3810 END IF; --l_attributes_row_table IS NOT NULL
3811 */
3812 -- end loop; --End for attr_grp IN Get_Attribute_Groups
3813
3814 --Classification code
3815 l_new_str_type := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'STRUCTURE_TYPE_ID', TO_CHAR(l_src_str_type)));
3816 FOR dest_comps in get_dest_comps(p_src_comp_seq_id => p_src_comp_seq_id, p_str_type_id => l_src_str_type)
3817 LOOP
3818 l_dest_pk_col_name_val_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(EGO_COL_NAME_VALUE_PAIR_OBJ( 'COMPONENT_SEQUENCE_ID' , to_char(dest_comps.component_sequence_id)),
3819 EGO_COL_NAME_VALUE_PAIR_OBJ( 'BILL_SEQUENCE_ID' , to_char(dest_comps.bill_sequence_id)) );
3820 EGO_USER_ATTRS_DATA_PUB.Process_User_Attrs_Data (
3821 p_api_version => 1.0
3822 ,p_object_name => 'BOM_COMPONENTS'
3823 ,p_attributes_row_table => l_attributes_row_table
3824 ,p_attributes_data_table => l_attributes_data_table
3825 ,p_pk_column_name_value_pairs => l_dest_pk_col_name_val_pairs
3826 ,p_class_code_name_value_pairs => l_new_str_type
3827 ,x_failed_row_id_list => l_failed_row_count
3828 ,x_return_status => x_return_status
3829 ,x_errorcode => l_errorcode
3830 ,x_msg_count => l_msg_count
3831 ,x_msg_data => l_msg_data
3832 );
3833 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
3834 THEN
3835 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3836 Return;
3837 END IF;
3838 END LOOP;
3839 END;
3840
3841
3842 /*
3843 * This Function is used to validate the operation seq num from the source bom
3844 * whenever a component is added to it.
3845 * @return boolean
3846 * @param p_src_bill_seq_id IN Bill Sequence Id of the source bom.
3847 * @param p_op_seq IN Operation Sequence number
3848 */
3849 Function Check_Op_Seq_In_Ref_Boms(p_src_bill_seq_id IN NUMBER
3850 , p_op_seq IN NUMBER)
3851 Return boolean
3852 IS
3853
3854 Cursor get_related_bills(p_src_bill_sequence_id NUMBER) IS
3855 Select bill_sequence_id, organization_id, assembly_item_id, alternate_bom_designator
3856 from BOM_STRUCTURES_B
3857 where source_bill_sequence_id <> common_bill_sequence_id
3858 and source_bill_sequence_id = p_src_bill_sequence_id;
3859
3860
3861 Cursor get_rtg_opns(p_assy_item_id NUMBER, p_org_id NUMBER, p_alt_desg VARCHAR2)
3862 IS
3863 Select OPERATION_SEQ_NUM
3864 From BOM_OPERATION_SEQUENCES
3865 Where Routing_Sequence_Id = (Select common_routing_sequence_id
3866 from bom_operational_routings
3867 where assembly_item_id = p_assy_item_id
3868 and organization_id = p_org_id
3869 and nvl(alternate_routing_designator, 'XXX') = nvl(p_alt_desg, 'XXX'));
3870
3871 l_rtg_exist varchar2(30) := null;
3872 l_stmt_num number;
3873 found boolean;
3874 valid_op_seq boolean;
3875 l_src_assy_item_id number;
3876 l_src_org_id number;
3877 l_src_alt varchar2(80);
3878 l_found_alt boolean := false;
3879
3880 BEGIN
3881
3882 BEGIN
3883 Select 'Routing Exists'
3884 INTO l_rtg_exist
3885 from BOM_OPERATIONAL_ROUTINGS bor, BOM_STRUCTURES_B bom
3886 Where bom.assembly_item_id = bor.assembly_item_id
3887 And bom.organization_id = bor.organization_id
3888 And (nvl(bor.alternate_routing_designator, 'XXX') = nvl(bom.alternate_bom_designator, 'XXX')
3889 OR bor.alternate_routing_designator IS NULL
3890 )
3891 And bom.bill_sequence_id = p_src_bill_seq_id;
3892
3893 EXCEPTION
3894 WHEN NO_DATA_FOUND THEN
3895 NULL;
3896 WHEN TOO_MANY_ROWS THEN
3897 l_rtg_exist := 'Routing Exists';
3898
3899 END;
3900
3901 --If source routing does not exist or op seq entered is 1, no validation is requireed.
3902 IF l_rtg_exist is null
3903 OR p_op_seq = 1
3904 THEN
3905 return true;
3906 END IF;
3907
3908
3909 for bills in get_related_bills(p_src_bill_sequence_id => p_src_bill_seq_id)
3910 loop
3911 --Check if each of these bills has a corresponding routing and that routing in turn contains
3912 -- an operation of opn sequence specified by p_op_seq
3913 l_found_alt := false;
3914 found := false;
3915 for op_seq in get_rtg_opns(p_assy_item_id => bills.assembly_item_id,
3916 p_org_id => bills.organization_id,
3917 p_alt_desg => bills.alternate_bom_designator)
3918 loop
3919 l_found_alt := true;
3920
3921 if p_op_seq = op_seq.OPERATION_SEQ_NUM
3922 then
3923 found := true;
3924 end if; --if p_op_seq = op_seq.OPERATION_SEQ_NUM
3925
3926 end loop; --for op_seq in get_rtg_opns
3927
3928 -- if alt rtg is not found, look in primary rtg
3929 if not l_found_alt
3930 then
3931 for op_seq in get_rtg_opns(p_assy_item_id => bills.assembly_item_id,
3932 p_org_id => bills.organization_id,
3933 p_alt_desg => null)
3934 loop
3935 if p_op_seq = op_seq.OPERATION_SEQ_NUM
3936 then
3937 found := true;
3938 end if; --if p_op_seq = op_seq.OPERATION_SEQ_NUM
3939 end loop; --for op_seq in get_rtg_opns
3940 end if;
3941
3942 if not found
3943 then
3944 --the current op seq cant be used as at least one of the referecning bills' routing does not have the op seq
3945 return false;
3946 end if; --if not found
3947
3948 end loop; --for bills in get_related_bills
3949
3950
3951 return true;
3952 END;
3953
3954 /*
3955 * This Procedure is used to delete related ref desgs from the referencing boms when ref desg
3956 * from the source bom is deleted.
3957 * @param p_src_comp_seq IN Component Sequence Id of the source component.
3958 * @param p_ref_desg IN Ref Desg of the dest source component.
3959 */
3960 Procedure Delete_Related_Ref_Desg(p_src_comp_seq IN NUMBER
3961 , p_ref_desg IN VARCHAR2
3962 , x_return_status IN OUT NOCOPY VARCHAR2)
3963 IS
3964 BEGIN
3965 DELETE FROM BOM_REFERENCE_DESIGNATORS
3966 WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq
3967 AND COMPONENT_REFERENCE_DESIGNATOR = p_ref_desg;
3968 EXCEPTION
3969 WHEN OTHERS THEN
3970 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3971 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3972 END;
3973
3974 /*
3975 * This Procedure is used to delete related sub comps from the referencing boms when sub comps
3976 * from the source bom is deleted.
3977 * @param p_src_comp_seq IN Component Sequence Id of the source component.
3978 * @param p_sub_comp_item_id IN Sub Comp of the dest source component.
3979 */
3980 Procedure Delete_Related_Sub_Comp(p_src_comp_seq IN NUMBER
3981 , p_sub_comp_item_id IN NUMBER
3982 , x_return_status IN OUT NOCOPY VARCHAR2)
3983 IS
3984 BEGIN
3985 DELETE FROM BOM_SUBSTITUTE_COMPONENTS
3986 WHERE COMMON_COMPONENT_SEQUENCE_ID = p_src_comp_seq
3987 AND SUBSTITUTE_COMPONENT_ID = p_sub_comp_item_id;
3988 EXCEPTION
3989 WHEN OTHERS THEN
3990 fnd_message.set_name('BOM', 'BOM_PROPAGATE_FAILED');
3991 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3992 END;
3993
3994
3995 /*
3996 * This Function is used to dedetermine if the insert/update of replicated component records
3997 * caused an overlap in the destination bill.
3998 * @param p_dest_bill_sequence_id IN Bill Sequence Id of the dest bill.
3999 * @param p_dest_comp_seq_id IN Component Sequence Id of the dest component.
4000 * @param p_comp_item_id IN Inv item Id of the component.
4001 * @param p_op_seq_num IN Op Sequence num of the source component.
4002 * @param p_change_notice IN change notice of the source component.
4003 * @param p_eff_date IN Effectivity date of the source component.
4004 * @param p_disable_date IN disable date component.
4005 */
4006 Function Check_Component_Overlap(p_dest_bill_sequence_id IN NUMBER
4007 , p_dest_comp_seq_id IN NUMBER
4008 , p_comp_item_id IN NUMBER
4009 , p_op_seq_num IN NUMBER
4010 , p_change_notice IN VARCHAR2
4011 , p_eff_date IN DATE
4012 , p_disable_date IN DATE
4013 , p_impl_date IN DATE
4014 , p_rev_item_seq_id IN NUMBER
4015 , p_src_bill_seq_id IN NUMBER
4016 )
4017 Return Boolean
4018 IS
4019 l_dummy NUMBER;
4020 l_rev_itm_bill_seq NUMBER;
4021
4022 BEGIN
4023
4024 BEGIN
4025 SELECT bill_Sequence_id
4026 INTO l_rev_itm_bill_seq
4027 FROM eng_revised_items
4028 WHERE revised_item_sequence_id = p_rev_item_seq_id;
4029 EXCEPTION
4030 WHEN NO_DATA_FOUND THEN
4031 NULL;
4032 END;
4033
4034 IF p_impl_date is NULL
4035 AND l_rev_itm_bill_seq = p_src_bill_seq_id
4036 THEN
4037
4038 SELECT 1
4039 INTO l_dummy
4040 FROM BOM_COMPONENTS_B bic
4041 WHERE bill_sequence_id = p_dest_bill_sequence_id
4042 AND component_sequence_id <> p_dest_comp_seq_id
4043 AND component_item_id = p_comp_item_id
4044 AND operation_seq_num = p_op_seq_num
4045 AND (
4046 change_notice is not null
4047 and(
4048 implementation_date is not null and p_change_notice is null
4049 OR
4050 (implementation_date is null and change_notice = p_change_notice
4051 AND EXISTS(
4052 SELECT 1 from eng_revised_items eri
4053 where eri.revised_item_sequence_id = bic.revised_item_sequence_id
4054 and eri.bill_Sequence_id = l_rev_itm_bill_seq
4055 )
4056 )
4057
4058 )
4059 OR
4060 (change_notice is null and p_change_notice is null)
4061 )
4062 AND (
4063 ( p_disable_date IS NULL OR p_disable_date > effectivity_Date ) AND
4064 ( p_eff_date < disable_Date OR disable_Date IS NULL)
4065 )
4066 AND rownum = 1
4067 ;
4068 ELSE
4069 SELECT 1
4070 INTO l_dummy
4071 FROM BOM_COMPONENTS_B bic
4072 WHERE bill_sequence_id = p_dest_bill_sequence_id
4073 AND component_sequence_id <> p_dest_comp_seq_id
4074 AND component_item_id = p_comp_item_id
4075 AND operation_seq_num = p_op_seq_num
4076 AND (
4077 change_notice is not null
4078 and(
4079 implementation_date is not null and p_change_notice is null
4080 OR
4081 (implementation_date is null and change_notice = p_change_notice
4082 AND EXISTS(
4083 SELECT 1 from eng_revised_items eri
4084 where eri.revised_item_sequence_id = bic.revised_item_sequence_id
4085 and eri.bill_Sequence_id = bic.bill_Sequence_id
4086 )
4087 )
4088
4089 )
4090 OR
4091 (change_notice is null and p_change_notice is null)
4092 )
4093 AND (
4094 ( p_disable_date IS NULL OR p_disable_date > effectivity_Date ) AND
4095 ( p_eff_date < disable_Date OR disable_Date IS NULL)
4096 )
4097 AND rownum = 1
4098 ;
4099 END IF;
4100 RETURN true;
4101
4102 EXCEPTION
4103 WHEN NO_DATA_FOUND THEN
4104 Return false;
4105
4106 END;
4107
4108
4109 Procedure Delete_Related_Pending_Comps(p_src_comp_seq_id IN NUMBER
4110 , x_Return_Status IN OUT NOCOPY VARCHAR2)
4111 IS
4112
4113 l_impl_date DATE;
4114
4115 BEGIN
4116
4117 /* SELECT implementation_date
4118 INTO l_impl_date
4119 FROM BOM_COMPONENTS_B
4120 where component_sequence_id = p_src_comp_seq_id;
4121
4122 IF l_impl_date IS NOT NULL
4123 THEN
4124 x_Return_Status := FND_API.G_RET_STS_ERROR;
4125 RETURN;
4126 END IF;
4127 */
4128 --Commented
4129 DELETE BOM_COMPONENTS_B
4130 WHERE common_component_sequence_id = p_src_comp_seq_id;
4131
4132 DELETE BOM_SUBSTITUTE_COMPONENTS
4133 WHERE common_component_sequence_id = p_src_comp_seq_id;
4134
4135 DELETE BOM_REFERENCE_DESIGNATORS
4136 WHERE common_component_sequence_id = p_src_comp_seq_id;
4137
4138 DELETE BOM_COMPONENT_OPERATIONS
4139 WHERE common_component_sequence_id = p_src_comp_seq_id;
4140
4141 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
4142
4143 EXCEPTION
4144
4145 WHEN OTHERS THEN
4146 x_Return_Status := FND_API.G_RET_STS_ERROR;
4147 app_exception.raise_exception;
4148
4149 END;
4150
4151 ------------------------------------------------------------------------
4152 -- API name : Copy_Pending_Dest_Components --
4153 -- Type : Private --
4154 -- Pre-reqs : None. --
4155 -- Procedure : Propagates the specified ECO --
4156 -- Parameters : --
4157 -- IN : p_src_old_comp_seq_id NUMBER Required --
4158 -- p_src_comp_seq_id NUMBER Required --
4159 -- p_change_notice vARCHAR2 Required --
4160 -- p_revised_item_sequence_id NUMBER Required --
4161 -- p_effectivity_date NUMBER Required --
4162 -- OUT : x_return_status VARCHAR2(1) --
4163 -- Version : Current version 1.0 --
4164 -- Initial version 1.0 --
4165 -- --
4166 -- Notes : This API is invoked only when a common bill has --
4167 -- pending changes associated for its WIP supply type --
4168 -- attributes and the common component in the source --
4169 -- bill is being implemented. --
4170 -- API Copy_Revised_Item is called and then --
4171 -- A copy of all the destination changes are then made --
4172 -- to this revised item with the effectivity range of --
4173 -- the component being implemented. --
4174 ------------------------------------------------------------------------
4175
4176 PROCEDURE Copy_Pending_Dest_Components (
4177 p_src_old_comp_seq_id IN NUMBER
4178 , p_src_comp_seq_id IN NUMBER
4179 , p_change_notice IN VARCHAR2
4180 , p_organization_id IN NUMBER
4181 , p_revised_item_sequence_id IN NUMBER
4182 , p_effectivity_date IN DATE
4183 , x_return_status OUT NOCOPY VARCHAR2
4184 ) IS
4185
4186 --
4187 -- Cursor to fetch the pending components associated to the old component specified.
4188 -- The cursor is ordered by change notice and revised item sequence id so that the
4189 -- copying take place one revised item at a time and all its corresponding changes
4190 -- can be inserted in one go.
4191 --
4192 CURSOR c_pending_components ( cp_old_component_sequence_id NUMBER
4193 , cp_bill_sequence_id NUMBER ) IS
4194 SELECT *
4195 FROM bom_components_b bcb
4196 WHERE bcb.old_component_sequence_id = cp_old_component_sequence_id
4197 AND bcb.bill_sequence_id = cp_bill_sequence_id
4198 AND bcb.implementation_date IS NULL
4199 -- The following exists clause is to ensure that the pending component is not a source
4200 -- referenced component but the one actually created for the destination bill itself
4201 AND EXISTS (SELECT 1 FROM eng_revised_items eri
4202 WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
4203 AND eri.change_notice= bcb.change_notice
4204 AND eri.bill_sequence_id = bcb.bill_sequence_id)
4205 ORDER BY change_notice, revised_item_sequence_id;
4206
4207 --
4208 -- Cursor to fetch the component being implemented wrt the detination bill
4209 -- for the change in the soruce bill
4210 --
4211 CURSOR c_related_components IS
4212 SELECT bcb.component_sequence_id, old_component_sequence_id, bill_sequence_id, effectivity_date
4213 FROM bom_components_b bcb
4214 WHERE bcb.change_notice = p_change_notice
4215 AND bcb.revised_item_sequence_id = p_revised_item_sequence_id
4216 AND bcb.common_component_sequence_id = p_src_comp_seq_id
4217 AND bcb.common_component_sequence_id <> bcb.component_sequence_id
4218 AND bcb.implementation_date IS NULL;
4219
4220
4221 l_component_rec bom_components_b%ROWTYPE;
4222 l_dest_new_comp_seq_id NUMBER;
4223 l_old_rev_item_seq_id NUMBER;
4224 l_gen_rev_item_seq_id NUMBER;
4225 l_return_status VARCHAR2(1);
4226 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
4227 l_plsql_block VARCHAR2(1000);
4228
4229 BEGIN
4230 --
4231 -- Initialize
4232 --
4233 x_return_status := FND_API.G_RET_STS_SUCCESS;
4234 l_old_rev_item_seq_id := NULL;
4235
4236 Eng_globals.Init_System_Info_Rec
4237 ( x_mesg_token_tbl => l_mesg_token_tbl
4238 , x_return_status => l_return_status
4239 );
4240 --
4241 -- Processing Begins
4242 -- For each destination component record that will be implemented
4243 --
4244 FOR c_dest_comp_rec IN c_related_components
4245 LOOP
4246 l_return_status := null;
4247 FOR l_component_rec IN c_pending_components (c_dest_comp_rec.old_component_sequence_id, c_dest_comp_rec.bill_sequence_id)
4248 LOOP
4249
4250 IF l_old_rev_item_seq_id IS NULL
4251 OR l_old_rev_item_seq_id <> l_component_rec.revised_item_sequence_id
4252 THEN
4253
4254 -- Generate a new reviseed item for the copies that are going to be inserted
4255 l_old_rev_item_seq_id := l_component_rec.revised_item_sequence_id;
4256 l_gen_rev_item_seq_id := NULL;
4257 l_plsql_block := 'Begin
4258 Eng_revised_Item_Util.Copy_revised_Item(
4259 p_old_revised_item_seq_id => :1
4260 , p_effectivity_date => :2
4261 , x_new_revised_item_seq_id => :3
4262 , x_return_status => :4
4263 );
4264 End;';
4265 BEGIN
4266 Execute Immediate l_plsql_block
4267 USING IN l_old_rev_item_seq_id
4268 , IN p_effectivity_date
4269 , OUT l_gen_rev_item_seq_id
4270 --, OUT l_Mesg_Token_Tbl
4271 , OUT l_return_status;
4272 EXCEPTION
4273 WHEN OTHERS THEN
4274 l_return_status := FND_API.G_RET_STS_ERROR;
4275 END;
4276 END IF;
4277 IF (l_return_status = 'S')
4278 THEN
4279 SELECT bom_inventory_components_s.NEXTVAL INTO l_dest_new_comp_seq_id FROM dual;
4280 -- changed values from other record
4281 l_component_rec.component_sequence_id := l_dest_new_comp_seq_id;
4282 l_component_rec.old_component_sequence_id := c_dest_comp_rec.component_sequence_id;
4283 l_component_rec.common_component_sequence_id := p_src_comp_seq_id;
4284 l_component_rec.revised_item_sequence_id := l_gen_rev_item_seq_id;
4285 -- who columns
4286 l_component_rec.creation_date := sysdate;
4287 l_component_rec.created_by := FND_PROFILE.value('USER_ID');
4288 l_component_rec.last_update_date := sysdate;
4289 l_component_rec.last_updated_by := FND_PROFILE.value('USER_ID');
4290 l_component_rec.last_update_login := FND_PROFILE.value('LOGIN_ID');
4291 l_component_rec.request_id := FND_PROFILE.value('REQUEST_ID');
4292 l_component_rec.program_application_id := FND_PROFILE.value('RESP_APPL_ID');
4293 l_component_rec.program_id := FND_PROFILE.value('PROGRAM_ID');
4294 l_component_rec.program_update_date := sysdate;
4295
4296 INSERT INTO BOM_COMPONENTS_B
4297 ( SUPPLY_SUBINVENTORY
4298 , OPERATION_LEAD_TIME_PERCENT
4299 , REVISED_ITEM_SEQUENCE_ID
4300 , COST_FACTOR
4301 , REQUIRED_FOR_REVENUE
4302 , HIGH_QUANTITY
4303 , COMPONENT_SEQUENCE_ID
4304 , PROGRAM_APPLICATION_ID
4305 , WIP_SUPPLY_TYPE
4306 , SUPPLY_LOCATOR_ID
4307 , BOM_ITEM_TYPE
4308 , OPERATION_SEQ_NUM
4309 , COMPONENT_ITEM_ID
4310 , LAST_UPDATE_DATE
4311 , LAST_UPDATED_BY
4312 , CREATION_DATE
4313 , CREATED_BY
4314 , LAST_UPDATE_LOGIN
4315 , ITEM_NUM
4316 , COMPONENT_QUANTITY
4317 , COMPONENT_YIELD_FACTOR
4318 , COMPONENT_REMARKS
4319 , EFFECTIVITY_DATE
4320 , CHANGE_NOTICE
4321 , IMPLEMENTATION_DATE
4322 , DISABLE_DATE
4323 , ATTRIBUTE_CATEGORY
4324 , ATTRIBUTE1
4325 , ATTRIBUTE2
4326 , ATTRIBUTE3
4327 , ATTRIBUTE4
4328 , ATTRIBUTE5
4329 , ATTRIBUTE6
4330 , ATTRIBUTE7
4331 , ATTRIBUTE8
4332 , ATTRIBUTE9
4333 , ATTRIBUTE10
4334 , ATTRIBUTE11
4335 , ATTRIBUTE12
4336 , ATTRIBUTE13
4337 , ATTRIBUTE14
4338 , ATTRIBUTE15
4339 , PLANNING_FACTOR
4340 , QUANTITY_RELATED
4341 , SO_BASIS
4342 , OPTIONAL
4343 , MUTUALLY_EXCLUSIVE_OPTIONS
4344 , INCLUDE_IN_COST_ROLLUP
4345 , CHECK_ATP
4346 , SHIPPING_ALLOWED
4347 , REQUIRED_TO_SHIP
4348 , INCLUDE_ON_SHIP_DOCS
4349 , INCLUDE_ON_BILL_DOCS
4350 , LOW_QUANTITY
4351 , ACD_TYPE
4352 , OLD_COMPONENT_SEQUENCE_ID
4353 , BILL_SEQUENCE_ID
4354 , REQUEST_ID
4355 , PROGRAM_ID
4356 , PROGRAM_UPDATE_DATE
4357 , PICK_COMPONENTS
4358 , Original_System_Reference
4359 , From_End_Item_Unit_Number
4360 , To_End_Item_Unit_Number
4361 , Eco_For_Production -- Added by MK
4362 , Enforce_Int_Requirements
4363 , Auto_Request_Material -- Added in 11.5.9 by ADEY
4364 , Obj_Name -- Added by hgelli.
4365 , pk1_value
4366 , pk2_value
4367 , Suggested_Vendor_Name --- Deepu
4368 , Vendor_Id --- Deepu
4369 --, Purchasing_Category_id --- Deepu
4370 , Unit_Price --- Deepu
4371 , from_object_revision_id
4372 , from_minor_revision_id
4373 --,component_item_revision_id
4374 --,component_minor_revision_id
4375 , common_component_sequence_id
4376 , basis_type
4377 , component_item_revision_id
4378 ) VALUES
4379 ( l_component_rec.supply_subinventory
4380 , l_component_rec.OPERATION_LEAD_TIME_PERCENT --check this
4381 , l_component_rec.revised_item_sequence_id
4382 , l_component_rec.cost_factor /* Cost Factor */
4383 , l_component_rec.required_for_revenue
4384 , l_component_rec.HIGH_QUANTITY
4385 , l_component_rec.component_sequence_id
4386 , l_component_rec.program_application_id
4387 , l_component_rec.wip_supply_type
4388 , l_component_rec.supply_locator_id
4389 , l_component_rec.bom_item_type
4390 , l_component_rec.operation_seq_num --Check this too
4391 , l_component_rec.component_item_id
4392 , SYSDATE /* Last Update Date */
4393 , l_component_rec.last_updated_by /* Last Updated By */
4394 , SYSDATE /* Creation Date */
4395 , l_component_rec.created_by /* Created By */
4396 , l_component_rec.last_update_login /* Last Update Login */
4397 , l_component_rec.ITEM_NUM
4398 , l_component_rec.component_quantity
4399 , l_component_rec.COMPONENT_YIELD_FACTOR
4400 , l_component_rec.COMPONENT_REMARKS
4401 , nvl(l_component_rec.effectivity_date,SYSDATE) --2169237
4402 , l_component_rec.Change_Notice
4403 , l_component_rec.implementation_date/* Implementation Date */
4404 , l_component_rec.disable_date
4405 , l_component_rec.attribute_category
4406 , l_component_rec.attribute1
4407 , l_component_rec.attribute2
4408 , l_component_rec.attribute3
4409 , l_component_rec.attribute4
4410 , l_component_rec.attribute5
4411 , l_component_rec.attribute6
4412 , l_component_rec.attribute7
4413 , l_component_rec.attribute8
4414 , l_component_rec.attribute9
4415 , l_component_rec.attribute10
4416 , l_component_rec.attribute11
4417 , l_component_rec.attribute12
4418 , l_component_rec.attribute13
4419 , l_component_rec.attribute14
4420 , l_component_rec.attribute15
4421 , l_component_rec.planning_factor
4422 , l_component_rec.quantity_related
4423 , l_component_rec.so_basis
4424 , l_component_rec.optional
4425 , l_component_rec.mutually_exclusive_options
4426 , l_component_rec.include_in_cost_rollup
4427 , l_component_rec.check_atp
4428 , l_component_rec.shipping_allowed
4429 , l_component_rec.required_to_ship
4430 , l_component_rec.include_on_ship_docs
4431 , l_component_rec.include_on_bill_docs /* Include On Bill Docs */
4432 , l_component_rec.low_quantity
4433 , l_component_rec.acd_type
4434 , l_component_rec.old_component_sequence_id --Chk this
4435 , l_component_rec.bill_sequence_id
4436 , l_component_rec.request_id
4437 , l_component_rec.program_id
4438 , SYSDATE /* program_update_date */
4439 , l_component_rec.pick_components
4440 , l_component_rec.original_system_reference
4441 , l_component_rec.from_end_item_unit_number
4442 , l_component_rec.to_end_item_unit_number
4443 , l_component_rec.Eco_For_Production
4444 , l_component_rec.Enforce_Int_Requirements
4445 , l_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
4446 , NULL-- Added by hgelli. Identifies this record as Bom Component.
4447 , l_component_rec.component_item_id
4448 , p_organization_id
4449 , l_component_rec.Suggested_Vendor_Name --- Deepu
4450 , l_component_rec.Vendor_Id --- Deepu
4451 --, p_rev_component_rec.purchasing_category_id --- Deepu
4452 , l_component_rec.Unit_Price --- Deepu
4453 , l_component_rec.from_object_revision_id
4454 , l_component_rec.from_minor_revision_id
4455 , l_component_rec.common_component_sequence_id
4456 , l_component_rec.basis_type
4457 , decode(l_component_rec.component_item_revision_id,
4458 NULL, NULL,
4459 BOMPCMBM.get_rev_id_for_local_org(l_component_rec.component_item_revision_id, p_organization_id))
4460 --, l_comp_revision_id
4461 --, l_comp_minor_revision_id
4462 );
4463 ELSE
4464 x_return_status := FND_API.G_RET_STS_ERROR;
4465 RETURN;
4466 END IF;
4467 END LOOP;
4468 END LOOP;
4469 x_return_status := l_return_status;
4470 EXCEPTION
4471 WHEN OTHERS THEN
4472 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4473 END Copy_Pending_Dest_Components;
4474
4475
4476
4477 PROCEDURE check_comp_rev_in_local_org(p_src_bill_seq_id IN NUMBER,
4478 p_org_id IN NUMBER,
4479 x_return_status OUT NOCOPY VARCHAR2)
4480 IS
4481 l_rev_count NUMBER;
4482 l_comp_count NUMBER;
4483 BEGIN
4484
4485 SELECT count(*)
4486 INTO l_comp_count
4487 FROM bom_components_b
4488 WHERE bill_sequence_id = p_src_bill_seq_id
4489 AND COMPONENT_ITEM_REVISION_ID IS NOT NULL;
4490
4491 IF l_comp_count > 0
4492 THEN
4493 SELECT count(*)
4494 INTO l_rev_count
4495 FROM MTL_ITEM_REVISIONS_B source, MTL_ITEM_REVISIONS_B dest
4496 WHERE source.inventory_item_id = dest.inventory_item_id
4497 AND source.revision_id IN (SELECT COMPONENT_ITEM_REVISION_ID
4498 FROM BOM_COMPONENTS_B
4499 WHERE BILL_SEQUENCE_ID = p_src_bill_seq_id)
4500 AND dest.organization_id = p_org_id
4501 AND source.revision = dest.revision;
4502
4503 IF l_comp_count <> l_rev_count
4504 THEN
4505 x_return_status := FND_API.G_RET_STS_ERROR;
4506 RETURN;
4507 END IF;
4508 END IF;
4509
4510 x_return_status := FND_API.G_RET_STS_SUCCESS;
4511 END;
4512
4513
4514
4515 /*
4516 * This function will return the corresponding revision id
4517 * in the local org for the revision passed.
4518 */
4519 Function get_rev_id_for_local_org(p_rev_id IN NUMBER, p_org_id IN NUMBER)
4520 Return NUMBER
4521 IS
4522 l_rev_id NUMBER;
4523 BEGIN
4524 SELECT dest.revision_id
4525 INTO l_rev_id
4526 FROM MTL_ITEM_REVISIONS_B src, MTL_ITEM_REVISIONS_B dest
4527 WHERE dest.inventory_item_id = src.inventory_item_id
4528 AND dest.organization_id = p_org_id
4529 AND dest.revision = src.revision
4530 AND src.revision_id = p_rev_id;
4531
4532 RETURN l_rev_id;
4533
4534 EXCEPTION
4535 WHEN NO_DATA_FOUND THEN
4536 Return NULL;
4537
4538 END;
4539
4540
4541 /**
4542 * This function validates the fixed revision of a component
4543 * wrt common boms in different organizations.
4544 */
4545
4546 Function Check_comp_rev_for_Com_Boms(p_rev_id IN NUMBER, p_src_bill_seq_id IN NUMBER)
4547 RETURN VARCHAR2
4548 IS
4549 CURSOR get_orgs IS
4550 SELECT DISTINCT organization_id
4551 FROM bom_structures_b
4552 WHERE source_bill_Sequence_id = p_src_bill_seq_id;
4553
4554 BEGIN
4555 FOR org IN get_orgs
4556 LOOP
4557 IF get_rev_id_for_local_org(p_rev_id => p_rev_id, p_org_id => org.organization_id) IS NULL
4558 THEN
4559 RETURN FND_API.G_RET_STS_ERROR;
4560 END IF;
4561 END LOOP;
4562 RETURN FND_API.G_RET_STS_SUCCESS;
4563 END;
4564
4565 END bompcmbm ;