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