1 PACKAGE BODY Bom_Open_Interface_Utl AS
2 /* $Header: BOMUBOIB.pls 120.15 2010/09/20 20:40:44 umajumde ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUBOIB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Open_Interface_Utl
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 22-NOV-02 Vani Hymavathi Initial Creation
21 -- 01-JUN-05 Bhavnesh Patel Added Batch Id
22 ***************************************************************************/
23 /*--------------------------Process_Header_Info------------------------------
24
25 NAME
26 Process_Header_Info
27 DESCRIPTION
28 Populate the user-friendly columns to bill record in the interface table
29 REQUIRES
30
31 MODIFIES
32 BOM_BILL_OF_MTLS_INTERFACE
33 MTL_INTERFACE_ERRORS
34 RETURNS
35 0 if successful
36 SQLCODE if unsuccessful
37 NOTES
38 -----------------------------------------------------------------------------*/
39
40 FUNCTION Process_Header_Info (
41 org_id NUMBER,
42 all_org NUMBER ,
43 user_id NUMBER,
44 login_id NUMBER,
45 prog_appid NUMBER,
46 prog_id NUMBER,
47 req_id NUMBER,
48 err_text IN OUT NOCOPY VARCHAR2,
49 p_batch_id IN NUMBER
50 )
51 return INTEGER
52 IS
53 stmt_num NUMBER := 0;
54 l_sysdate DATE := SYSDATE;
55 msg_name1 varchar2(30);
56 msg_name2 varchar2(30);
57 msg_text1 varchar2(2000);
58 msg_text2 varchar2(2000);
59
60 BEGIN
61
62 stmt_num := 1;
63 /* Resolve the Bill sequence ids for updates and deletes */
64
65 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
66 SET(assembly_item_id, organization_id, alternate_bom_designator)
67 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
68 FROM bom_structures_b BBM1
69 WHERE BBM1.bill_sequence_id = BBMI.bill_sequence_id)
70 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
71 AND upper(transaction_type) in (G_Delete, G_Update)
72 AND bill_sequence_id is not null
73 AND
74 (
75 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
76 OR ( p_batch_id = BBMI.batch_id )
77 )
78 AND exists (SELECT 'x'
79 FROM bom_structures_b BBM2
80 WHERE BBM2.bill_sequence_id = BBMI.bill_sequence_id);
81
82
83
84 stmt_num := 2;
85
86 /* Update Organization Code using Organization_id
87 this also needed if Organization_id is given and code is not given*/
88
89 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
90 SET organization_code = (SELECT organization_code
91 FROM MTL_PARAMETERS MP1
92 WHERE mp1.organization_id = BBMI.organization_id)
93 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
94 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
95 AND organization_id is not null
96 AND
97 (
98 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
99 OR ( p_batch_id = BBMI.batch_id )
100 )
101 AND exists (SELECT 'x'
102 FROM MTL_PARAMETERS MP2
103 WHERE mp2.organization_id = BBMI.organization_id);
104
105
106
107 stmt_num := 3;
108 /* Update Organization_ids if organization code is given org id is null.
109 Orgnaization_id information is needed in the next steps */
110
111 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
112 SET organization_id = (SELECT organization_id
113 FROM MTL_PARAMETERS mp1
114 WHERE mp1.organization_code = BBMI.organization_code)
115 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
116 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
117 AND organization_id is null
118 AND organization_code is not null
119 AND
120 (
121 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
122 OR ( p_batch_id = BBMI.batch_id )
123 );
124
125 stmt_num := 3.1;
126 /* Update Assembly Item name */
127 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
128 SET item_number = (SELECT concatenated_segments
129 FROM MTL_SYSTEM_ITEMS_KFV mvl1
130 WHERE mvl1.inventory_item_id = BBMI.assembly_item_id
131 and mvl1.organization_id = BBMI.organization_id)
132 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
133 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
134 AND assembly_item_id is not null
135 AND organization_id is not null
136 AND
137 (
138 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
139 OR ( p_batch_id = BBMI.batch_id )
140 )
141 AND exists (SELECT 'x'
142 FROM MTL_SYSTEM_ITEMS MKFV
143 WHERE MKFV.inventory_item_id = BBMI.assembly_item_id
144 AND MKFV.organization_id = BBMI.organization_id);
145
146 stmt_num := 4;
147
148 /* Update Assembly Item Id*/
149 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
150 SET assembly_item_id = (SELECT inventory_item_id
151 FROM MTL_SYSTEM_ITEMS_KFV mvl1
152 WHERE mvl1.concatenated_segments = BBMI.item_number
153 AND mvl1.organization_id = BBMI.organization_id)
154 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
155 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
156 AND item_number is not null
157 AND organization_id is not null
158 AND assembly_item_id is null
159 AND
160 (
161 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
162 OR ( p_batch_id = BBMI.batch_id )
163 );
164
165 stmt_num := 5;
166 /* Assign transaction ids */
167
168 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
169 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
170 transaction_type = upper(transaction_type)
171 WHERE transaction_id is null
172 AND upper(transaction_type) in (G_Create, G_Update, G_Delete,'NO_OP')
173 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
174 AND
175 (
176 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
177 OR ( p_batch_id = BBMI.batch_id )
178 )
179 AND (all_org = 1
180 OR
181 (all_org = 2 AND organization_id = org_id));
182
183
184
185 stmt_num := 6;
186 /* Assign Common Item id and Common Organization id if common_bill_sequence_id is given
187 and a bill exists with that bill_sequence_id */
188
189 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
190 SET(common_assembly_item_id, common_organization_id)
191 = (SELECT assembly_item_id, organization_id
192 FROM bom_structures_b BBM1
193 WHERE BBM1.bill_sequence_id = BBMI.common_bill_sequence_id)
194 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
195 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
196 AND common_bill_sequence_id is not null
197 AND
198 (
199 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
200 OR ( p_batch_id = BBMI.batch_id )
201 )
202 AND exists (SELECT 'x'
203 FROM bom_structures_b BBM2
204 WHERE BBM2.bill_sequence_id = BBMI.common_bill_sequence_id);
205
206
207
208 stmt_num :=7;
209 /* Assign common_organization_code if common_organization_id is populated */
210
211 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
212 SET common_org_code = (SELECT organization_code
213 FROM MTL_PARAMETERS mp1
214 WHERE mp1.organization_id = BBMI.common_organization_id)
215 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
216 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
217 AND common_organization_id is not null
218 AND
219 (
220 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
221 OR ( p_batch_id = BBMI.batch_id )
222 )
223 AND exists (SELECT 'x'
224 FROM MTL_PARAMETERS mp2
225 WHERE mp2.organization_id = BBMI.common_organization_id);
226
227
228
229 stmt_num :=8 ;
230 /* Update Organization_ids if organization_code is given org id is null.
231 Orgnaization_id information is needed in the next steps */
232
233 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
234 SET common_organization_id = (SELECT organization_id
235 FROM MTL_PARAMETERS MP1
236 WHERE mp1.organization_code = BBMI.common_org_code)
237 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
238 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
239 AND common_organization_id is null
240 AND common_org_code is not null
241 AND
242 (
243 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
244 OR ( p_batch_id = BBMI.batch_id )
245 );
246
247 stmt_num := 9;
248 /* Update Assembly Item name */
249
250 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
251 SET common_item_number = (SELECT concatenated_segments
252 FROM MTL_SYSTEM_ITEMS_KFV mvl1
253 WHERE mvl1.inventory_item_id = BBMI.common_assembly_item_id
254 AND mvl1.organization_id = BBMI.common_organization_id)
255 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
256 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,G_NoOp)
257 AND common_assembly_item_id is not null
258 AND common_organization_id is not null
259 AND
260 (
261 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
262 OR ( p_batch_id = BBMI.batch_id )
263 )
264 AND exists (SELECT 'x'
265 FROM MTL_SYSTEM_ITEMS mvl2
266 WHERE mvl2.inventory_item_id = BBMI.common_assembly_item_id
267 AND mvl2.organization_id = BBMI.common_organization_id);
268
269 /* Update the delete_group_name from bom_interface_delete_groups */
270 stmt_num := 9.5;
271 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
272 SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
273 = (SELECT DELETE_GROUP_NAME, DESCRIPTION
274 FROM bom_interface_delete_groups
275 Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
276 And rownum = 1)
277 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
278 AND upper(transaction_type) in (G_Delete)
279 AND organization_id is not null
280 AND delete_group_name is null
281 AND
282 (
283 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
284 OR ( p_batch_id = BBMI.batch_id )
285 )
286 AND exists (SELECT 'x'
287 FROM bom_interface_delete_groups
288 Where upper(entity_name) = 'BOM_BILL_OF_MTLS_INTERFACE'
289 );
290
291 stmt_num := 9.6;
292 /* Update Bill Sequence Id when there are IDs available */
293
294 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
295 SET bill_sequence_id = (SELECT bill_sequence_id
296 FROM bom_structures_b bom
297 WHERE bom.assembly_item_id = BBMI.assembly_item_id
298 AND bom.organization_id = BBMI.organization_id
299 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR))
300 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
301 AND upper(transaction_type) in (G_Delete, G_Update)
302 AND assembly_item_id is not null
303 AND organization_id is not null
304 AND
305 (
306 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
307 OR ( p_batch_id = BBMI.batch_id )
308 )
309 AND exists (SELECT 1
310 FROM bom_structures_b bom1
311 WHERE bom1.assembly_item_id = BBMI.assembly_item_id
312 AND bom1.organization_id = BBMI.organization_id
313 AND NVL(bom1.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR));
314
315 /* Commented for Performance Fix . We will have the ids before reaching this point
316 So we dont need to resolve bill_seq_id using UUs
317
318 stmt_num := 9.7;
319 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
320 SET bill_sequence_id = (SELECT bill_sequence_id
321 FROM bom_bill_of_materials bom, mtl_system_items_vl mvll
322 WHERE mvll.concatenated_segments = BBMI.item_number
323 AND mvll.organization_id = BBMI.organization_id
324 AND bom.assembly_item_id = mvll.inventory_item_id
325 AND bom.organization_id = mvll.organization_id
326 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BBMI.alternate_bom_designator,FND_API.G_MISS_CHAR))
327 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
328 AND upper(transaction_type) in (G_Delete, G_Update)
329 AND item_number is not null
330 AND organization_id is not null
331 AND bill_sequence_id is null
332 AND
333 (
334 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
335 OR ( p_batch_id = BBMI.batch_id )
336 );
337 */
338
339
340 stmt_num := 9.8;
341 /* Update structure type name to the internal name from the display name */
342 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
343 SET structure_type_name = (SELECT structure_type_name
344 FROM BOM_STRUCTURE_TYPES_VL bstv
345 WHERE decode(BBMI.structure_type_name, null, to_char(bstv.structure_type_id), bstv.display_name)
346 = decode(BBMI.structure_type_name, null, to_char(BBMI.structure_type_id), BBMI.structure_type_name))
347 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
348 AND (structure_type_name is not null OR structure_type_id is not null)
349 AND upper(transaction_type) in (G_Create,G_Update,G_NoOp)
350 AND
351 (
352 ( (p_batch_id IS NULL) AND (BBMI.batch_id IS NULL) )
353 OR ( p_batch_id = BBMI.batch_id )
354 )
355 AND exists (SELECT null
356 FROM BOM_STRUCTURE_TYPES_VL bstv
357 WHERE decode(BBMI.structure_type_name, null, to_char(bstv.structure_type_id), bstv.display_name) =
358 decode(BBMI.structure_type_name, null, to_char(BBMI.structure_type_id), BBMI.structure_type_name));
359
360 stmt_num := 10;
361 /* Load rows from bill interface into revisions interface*/
362
363 INSERT into mtl_item_revisions_interface
364 (ITEM_NUMBER,
365 ORGANIZATION_CODE,
366 REVISION,
367 EFFECTIVITY_DATE,
368 IMPLEMENTATION_DATE,
369 PROCESS_FLAG,
370 TRANSACTION_TYPE,
371 LAST_UPDATE_DATE,
372 LAST_UPDATED_BY,
373 CREATION_DATE,
374 CREATED_BY,
375 LAST_UPDATE_LOGIN,
376 REQUEST_ID,
377 PROGRAM_APPLICATION_ID,
378 PROGRAM_ID,
379 PROGRAM_UPDATE_DATE,
380 SET_PROCESS_ID)
381 select
382 item_number,
383 Organization_Code,
384 REVISION,
385 sysdate,
386 sysdate,
387 1,
388 G_Create,
389 NVL(LAST_UPDATE_DATE, SYSDATE),
390 NVL(LAST_UPDATED_BY, user_id),
391 NVL(CREATION_DATE,SYSDATE),
392 NVL(CREATED_BY, user_id),
393 NVL(LAST_UPDATE_LOGIN, user_id),
394 NVL(REQUEST_ID, req_id),
395 NVL(PROGRAM_APPLICATION_ID, prog_appid),
396 NVL(PROGRAM_ID, prog_id),
397 NVL(PROGRAM_UPDATE_DATE, sysdate),
398 NVL(BATCH_ID,0) -- Replace NULL batch id with 0 - table level default value for set_process_id
399 FROM BOM_BILL_OF_MTLS_INTERFACE
400 WHERE process_flag = 1
401 AND transaction_type = G_Create
402 AND (all_org = 1
403 OR
404 (all_org = 2 AND organization_id = org_id))
405 AND revision is not null
406 AND
407 (
408 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
409 OR ( p_batch_id = batch_id )
410 );
411
412 COMMIT;
413 stmt_num := 11;
414
415 /* Update the interface records with process_flag 3 and insert into
416 MTL_INTERFACE_ERRORS if Item number or Organization_code is missing*/
417
418 l_sysdate := SYSDATE;
419 msg_name1 := 'BOM_ORG_ID_MISSING';
420 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
421 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
422 msg_text1 := FND_MESSAGE.GET;
423 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
424 msg_text2 := FND_MESSAGE.GET;
425
426 INSERT INTO MTL_INTERFACE_ERRORS
427 (
428 TRANSACTION_ID,
429 UNIQUE_ID,
430 ORGANIZATION_ID,
431 COLUMN_NAME,
432 TABLE_NAME,
433 MESSAGE_NAME,
434 ERROR_MESSAGE,
435 LAST_UPDATE_DATE,
436 LAST_UPDATED_BY,
437 CREATION_DATE,
438 CREATED_BY,
439 LAST_UPDATE_LOGIN,
440 REQUEST_ID,
441 PROGRAM_APPLICATION_ID,
442 PROGRAM_ID,
443 PROGRAM_UPDATE_DATE
444 )
445 Select
446 BBMI.transaction_id,
447 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
448 Null,
449 null,
450 'BOM_BILL_OF_MTLS_INTERFACE',
451 decode ( BBMI.Organization_code, null, msg_name1,msg_name2),
452 decode ( BBMI.Organization_code, null, msg_text1,msg_text2),
453 NVL(LAST_UPDATE_DATE, SYSDATE),
454 NVL(LAST_UPDATED_BY, user_id),
455 NVL(CREATION_DATE,SYSDATE),
456 NVL(CREATED_BY, user_id),
457 NVL(LAST_UPDATE_LOGIN, user_id),
458 req_id,
459 NVL(PROGRAM_APPLICATION_ID, prog_appid),
460 NVL(PROGRAM_ID, prog_id),
461 NVL(PROGRAM_UPDATE_DATE, sysdate)
462
463 from BOM_BILL_OF_MTLS_INTERFACE BBMI
464 where (organization_code is null or item_number is null)
465 and transaction_id is not null
466 and process_flag =1
467 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
468 and
469 (
470 ( (p_batch_id is null) and (bbmi.batch_id is null) )
471 or ( p_batch_id = bbmi.batch_id )
472 );
473
474
475 Update BOM_BILL_OF_MTLS_INTERFACE BBMI
476 set process_flag = 3
477 where (item_number is null or Organization_code is null)
478 and transaction_id is not null
479 and process_flag =1
480 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
481 and
482 (
483 ( (p_batch_id is null) and (BBMI.batch_id is null) )
484 or ( p_batch_id = BBMI.batch_id )
485 ) ;
486
487 Commit;
488
489 return(0);
490
491
492 EXCEPTION
493 WHEN others THEN
494 err_text := 'Bom_Open_Interface_Utl(Process_Header_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
495 RETURN(SQLCODE);
496
497 END;
498
499
500 /*--------------------------Process_Comps_Info------------------------------
501
502 NAME
503 Process_Comps_Info
504 DESCRIPTION
505 Populate the user-friendly columns to Component records in the interface table
506 REQUIRES
507
508 MODIFIES
509 BOM_INVENTORY_COMPS_INTERFACE
510 MTL_INTERFACE_ERRORS
511 RETURNS
512 0 if successful
513 SQLCODE if unsuccessful
514 NOTES
515 -----------------------------------------------------------------------------*/
516
517 FUNCTION Process_Comps_Info (
518 org_id NUMBER,
519 all_org NUMBER ,
520 user_id NUMBER,
521 login_id NUMBER,
522 prog_appid NUMBER,
523 prog_id NUMBER,
524 req_id NUMBER,
525 err_text IN OUT NOCOPY VARCHAR2,
526 p_batch_id IN NUMBER
527 )
528 return INTEGER
529 IS
530 stmt_num NUMBER := 0;
531 l_sysdate DATE := SYSDATE;
532 msg_name1 varchar2(30);
533 msg_name2 varchar2(30);
534 msg_text1 varchar2(2000);
535 msg_text2 varchar2(2000);
536
537 BEGIN
538
539 stmt_num := 1;
540 /* Resolve the Component_sequence_ids for updates and deletes */
541
542 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
543 SET(bill_sequence_id, component_item_id, effectivity_date,
544 operation_seq_num, from_end_item_unit_number)
545 = (SELECT bill_sequence_id, component_item_id,
546 effectivity_date, operation_seq_num, from_end_item_unit_number
547 FROM bom_components_b BIC1
548 WHERE BIC1.component_sequence_id = BICI.component_sequence_id )
549 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
550 AND change_notice is null --added for bug 9673701
551 AND upper(transaction_type) in (G_Delete, G_Update)
552 AND component_sequence_id is not null
553 AND
554 (
555 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
556 OR ( p_batch_id = BICI.batch_id )
557 )
558 AND exists (SELECT 'x'
559 FROM bom_components_b BIC2
560 WHERE BIC2.component_sequence_id = BICI.component_sequence_id );
561
562
563 stmt_num := 2;
564 /* Resolve the Bill sequence ids for updates and deletes */
565
566 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
567 SET(assembly_item_id, organization_id, alternate_bom_designator)
568 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
569 FROM bom_structures_b BBM1
570 WHERE BBM1.bill_sequence_id = BICI.bill_sequence_id)
571 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
572 AND change_notice is null --added for bug 9673701
573 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
574 AND bill_sequence_id is not null
575 AND
576 (
577 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
578 OR ( p_batch_id = BICI.batch_id )
579 )
580 AND exists (SELECT 'x'
581 FROM bom_structures_b BBM2
582 WHERE BBM2.bill_sequence_id =BICI.bill_sequence_id);
583
584
585 stmt_num := 3;
586 /* Update Organization Code using Organization_id
587 this also needed if Organization_id is given and code is not given*/
588
589 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
590 SET organization_code = (SELECT organization_code
591 FROM MTL_PARAMETERS mp1
592 WHERE mp1.organization_id = BICI.organization_id)
593 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
594 AND change_notice is null --added for bug 9673701
595 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
596 AND organization_id is not null
597 AND
598 (
599 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
600 OR ( p_batch_id = BICI.batch_id )
601 )
602 AND exists (SELECT 'x'
603 FROM MTL_PARAMETERS mp2
604 WHERE mp2.organization_id = BICI.organization_id);
605
606
607
608 stmt_num := 4;
609 /* Update Organization_ids if organization_code is given org id is null.
610 Orgnaization_id information is needed in the next steps */
611
612 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
613 SET organization_id = (SELECT organization_id
614 FROM MTL_PARAMETERS mp1
615 WHERE mp1.organization_code = BICI.organization_code)
616 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
617 AND change_notice is null --added for bug 9673701
618 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
619 AND organization_id is null
620 AND organization_code is not null
621 AND
622 (
623 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
624 OR ( p_batch_id = BICI.batch_id )
625 );
626
627
628
629 stmt_num := 5;
630 /* Update Assembly Item name */
631
632 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
633 SET assembly_item_number = (SELECT concatenated_segments
634 FROM MTL_SYSTEM_ITEMS_KFV mvl1
635 WHERE mvl1.inventory_item_id = BICI.assembly_item_id
636 AND mvl1.organization_id = BICI.organization_id)
637 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
638 AND change_notice is null --added for bug 9673701
639 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
640 AND assembly_item_id is not null
641 AND organization_id is not null
642 AND
643 (
644 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
645 OR ( p_batch_id = BICI.batch_id )
646 )
647 AND exists (SELECT 'x'
648 FROM mtl_system_items mvl12
649 WHERE mvl12.inventory_item_id = BICI.assembly_item_id
650 AND mvl12.organization_id = BICI.organization_id);
651
652 stmt_num := 5.1;
653 /* Update the Assembly Item Id */
654
655 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
656 SET assembly_item_id = (SELECT inventory_item_id
657 FROM MTL_SYSTEM_ITEMS_KFV mvl1
658 WHERE mvl1.concatenated_segments = BICI.assembly_item_number
659 AND mvl1.organization_id = BICI.organization_id)
660 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
661 AND change_notice is null --added for bug 9673701
662 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
663 AND assembly_item_number is not null
664 AND organization_id is not null
665 AND assembly_item_id is null
666 AND
667 (
668 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
669 OR ( p_batch_id = BICI.batch_id )
670 );
671
672
673 stmt_num := 6;
674 /* Update Component Item name */
675
676 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
677 SET component_item_number = (SELECT CONCATENATED_SEGMENTS
678 FROM MTL_SYSTEM_ITEMS_KFV mvl1
679 WHERE mvl1.inventory_item_id = BICI.component_item_id
680 AND mvl1.organization_id = BICI.organization_id)
681 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
682 AND change_notice is null --added for bug 9673701
683 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
684 AND COMPONENT_ITEM_ID is not null
685 AND organization_id is not null
686 AND
687 (
688 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
689 OR ( p_batch_id = BICI.batch_id )
690 )
691 AND exists (SELECT 'x'
692 FROM mtl_system_items mvl12
693 WHERE mvl12.inventory_item_id = BICI.component_item_id
694 AND mvl12.organization_id = BICI.organization_id);
695
696 stmt_num := 6.1;
697 /* Update the component_item_id */
698 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
699 SET component_item_id = (SELECT inventory_item_id
700 FROM mtl_system_items_kfv mvll
701 WHERE mvll.concatenated_segments = BICI.component_item_number
702 AND mvll.organization_id = BICI.organization_id)
703 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
704 AND change_notice is null --added for bug 9673701
705 AND upper(transaction_type) in (G_Delete, G_Update)
706 AND component_item_number is not null
707 AND organization_id is not null
708 AND component_item_id IS null
709 AND
710 (
711 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
712 OR ( p_batch_id = BICI.batch_id )
713 );
714
715
716 stmt_num := 7;
717 /* Assign transaction ids */
718
719 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
720 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
721 transaction_type = upper(transaction_type)
722 WHERE transaction_id is null
723 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
724 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
725 AND change_notice is null --added for bug 9673701
726 AND (all_org = 1
727 OR
728 (all_org = 2 AND organization_id = org_id))
729 AND
730 (
731 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
732 OR ( p_batch_id = BICI.batch_id )
733 );
734
735
736 stmt_num := 8;
737 /* Update Supply_locator_name */
738
739 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
740 SET location_name = (SELECT concatenated_segments
741 FROM MTL_ITEM_LOCATIONS_KFV MIL1
742 WHERE MIL1.inventory_location_id = BICI.supply_locator_id
743 and MIL1.organization_id = BICI.organization_id)
744 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
745 AND change_notice is null --added for bug 9673701
746 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
747 AND supply_locator_id is not null
748 AND organization_id is not null
749 AND
750 (
751 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
752 OR ( p_batch_id = BICI.batch_id )
753 )
754 AND exists (SELECT 'x'
755 FROM MTL_ITEM_LOCATIONS mil2
756 WHERE mil2.INVENTORY_LOCATION_ID = BICI.supply_locator_id
757 and mil2.organization_id = BICI.organization_id);
758
759 stmt_num := 8.5;
760 /* Update the delete_group_name from bom_interface_delete_groups */
761 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
762 SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
763 = (SELECT DELETE_GROUP_NAME, DESCRIPTION
764 FROM bom_interface_delete_groups
765 Where upper(entity_name) = 'BOM_INVENTORY_COMPS_INTERFACE'
766 And rownum = 1)
767 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
768 AND change_notice is null --added for bug 9673701
769 AND upper(transaction_type) in (G_Delete)
770 AND organization_id is not null
771 AND delete_group_name is null
772 AND
773 (
774 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
775 OR ( p_batch_id = BICI.batch_id )
776 )
777 AND exists (SELECT 'x'
778 FROM bom_interface_delete_groups
779 Where upper(entity_name) = 'BOM_INVENTORY_COMPS_INTERFACE'
780 );
781
782
783 stmt_num := 8.6;
784 /* Update the bill_sequence_id */
785 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
786 SET bill_sequence_id = (SELECT bill_sequence_id
787 FROM bom_structures_b bom
788 WHERE bom.assembly_item_id = BICI.assembly_item_id
789 AND bom.organization_id = BICI.organization_id
790 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR))
791 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
792 AND change_notice is null --added for bug 9673701
793 AND upper(transaction_type) in (G_Delete, G_Update)
794 AND assembly_item_id is not null
795 AND organization_id is not null
796 AND
797 (
798 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
799 OR ( p_batch_id = BICI.batch_id )
800 )
801 AND exists (SELECT 'x'
802 FROM bom_structures_b bsb
803 WHERE bsb.assembly_item_id = BICI.assembly_item_id
804 AND bsb.organization_id = BICI.organization_id
805 AND NVL(BSB.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR)
806 );
807
808
809
810 stmt_num := 8.8;
811 /* Update the component_sequence_id */
812 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
813 SET COMPONENT_SEQUENCE_ID
814 = (SELECT COMPONENT_SEQUENCE_ID
815 FROM bom_components_b BIC
816 Where BIC.bill_sequence_id = BICI.bill_Sequence_id
817 And BIC.component_item_id = BICI.component_item_id
818 AND BIC.operation_seq_num = BICI.operation_seq_num
819 AND BIC.effectivity_date = BICI.effectivity_date)
820 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
821 AND change_notice is null --added for bug 9673701
822 AND upper(transaction_type) in (G_Update, G_Delete)
823 AND COMPONENT_SEQUENCE_ID is null
824 AND bill_sequence_id is not null
825 AND component_item_id is not null
826 AND
827 (
828 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
829 OR ( p_batch_id = BICI.batch_id )
830 );
831
832 stmt_num := 8.9;
833 /* Defaulting the effectivity_date to sysdate if the transaction_type is create
834 and effectivity date is null */
835 UPDATE BOM_INVENTORY_COMPS_INTERFACE
836 SET EFFECTIVITY_DATE = SYSDATE
837 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
838 AND change_notice is null --added for bug 9673701
839 AND upper(Transaction_Type) = G_Create
840 AND Effectivity_Date IS NULL
841 AND
842 (
843 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
844 OR ( p_batch_id = batch_id )
845 );
846
847 stmt_num := 9;
848 /* INSERTS ONLY - Load rows from component interface into sub comp interface*/
849 INSERT into bom_sub_comps_interface (
850 SUBSTITUTE_COMPONENT_ID,
851 SUBSTITUTE_COMP_NUMBER,
852 ORGANIZATION_ID,
853 LAST_UPDATE_DATE,
854 LAST_UPDATED_BY,
855 CREATION_DATE,
856 CREATED_BY,
857 LAST_UPDATE_LOGIN,
858 REQUEST_ID,
859 PROGRAM_APPLICATION_ID,
860 PROGRAM_ID,
861 PROGRAM_UPDATE_DATE,
862 COMPONENT_SEQUENCE_ID,
863 PROCESS_FLAG,
864 TRANSACTION_TYPE,
865 SUBSTITUTE_ITEM_QUANTITY,
866 BILL_SEQUENCE_ID,
867 ASSEMBLY_ITEM_ID,
868 ALTERNATE_BOM_DESIGNATOR,
869 COMPONENT_ITEM_ID,
870 OPERATION_SEQ_NUM,
871 EFFECTIVITY_DATE,
872 ORGANIZATION_CODE,
873 COMPONENT_ITEM_NUMBER,
874 ASSEMBLY_ITEM_NUMBER,
875 FROM_END_ITEM_UNIT_NUMBER,
876 BATCH_ID)
877 SELECT
878 SUBSTITUTE_COMP_ID,
879 SUBSTITUTE_COMP_NUMBER,
880 ORGANIZATION_ID,
881 NVL(LAST_UPDATE_DATE, SYSDATE),
882 NVL(LAST_UPDATED_BY, user_id),
883 NVL(CREATION_DATE,SYSDATE),
884 NVL(CREATED_BY, user_id),
885 NVL(LAST_UPDATE_LOGIN, user_id),
886 NVL(REQUEST_ID, req_id),
887 NVL(PROGRAM_APPLICATION_ID, prog_appid),
888 NVL(PROGRAM_ID, prog_id),
889 NVL(PROGRAM_UPDATE_DATE, sysdate),
890 COMPONENT_SEQUENCE_ID,
891 1,
892 G_Create,
893 COMPONENT_QUANTITY,
894 BILL_SEQUENCE_ID,
895 ASSEMBLY_ITEM_ID,
896 ALTERNATE_BOM_DESIGNATOR,
897 COMPONENT_ITEM_ID,
898 OPERATION_SEQ_NUM,
899 EFFECTIVITY_DATE,
900 ORGANIZATION_CODE,
901 COMPONENT_ITEM_NUMBER,
902 ASSEMBLY_ITEM_NUMBER,
903 FROM_END_ITEM_UNIT_NUMBER,
904 BATCH_ID
905 FROM bom_inventory_comps_interface
906 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
907 AND change_notice is null --added for bug 9673701
908 AND transaction_type = G_Create
909 AND (all_org = 1
910 OR
911 (all_org = 2 AND organization_id = org_id))
912 AND (substitute_comp_id is not null
913 OR
914 substitute_comp_number is not null)
915 AND
916 (
917 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
918 OR ( p_batch_id = batch_id )
919 );
920
921
922 stmt_num := 10;
923
924 /* INSERTS ONLY - Load rows from component interface into ref desgs interface*/
925
926 INSERT INTO bom_ref_desgs_interface (
927 COMPONENT_REFERENCE_DESIGNATOR,
928 LAST_UPDATE_DATE,
929 LAST_UPDATED_BY,
930 CREATION_DATE,
931 CREATED_BY,
932 LAST_UPDATE_LOGIN,
933 REQUEST_ID,
934 PROGRAM_APPLICATION_ID,
935 PROGRAM_ID,
936 PROGRAM_UPDATE_DATE,
937 COMPONENT_SEQUENCE_ID,
938 TRANSACTION_TYPE,
939 PROCESS_FLAG,
940 BILL_SEQUENCE_ID,
941 ASSEMBLY_ITEM_ID,
942 ALTERNATE_BOM_DESIGNATOR,
943 ORGANIZATION_ID,
944 COMPONENT_ITEM_ID,
945 ASSEMBLY_ITEM_NUMBER,
946 COMPONENT_ITEM_NUMBER,
947 ORGANIZATION_CODE,
948 EFFECTIVITY_DATE,
949 OPERATION_SEQ_NUM,
950 FROM_END_ITEM_UNIT_NUMBER,
951 BATCH_ID)
952 SELECT
953 REFERENCE_DESIGNATOR,
954 NVL(LAST_UPDATE_DATE, SYSDATE),
955 NVL(LAST_UPDATED_BY, user_id),
956 NVL(CREATION_DATE,SYSDATE),
957 NVL(CREATED_BY, user_id),
958 NVL(LAST_UPDATE_LOGIN, user_id),
959 NVL(REQUEST_ID, req_id),
960 NVL(PROGRAM_APPLICATION_ID, prog_appid),
961 NVL(PROGRAM_ID, prog_id),
962 NVL(PROGRAM_UPDATE_DATE, sysdate),
963 COMPONENT_SEQUENCE_ID,
964 G_Create,
965 1,
966 BILL_SEQUENCE_ID,
967 ASSEMBLY_ITEM_ID,
968 ALTERNATE_BOM_DESIGNATOR,
969 ORGANIZATION_ID,
970 COMPONENT_ITEM_ID,
971 ASSEMBLY_ITEM_NUMBER,
972 COMPONENT_ITEM_NUMBER,
973 ORGANIZATION_CODE,
974 EFFECTIVITY_DATE,
975 OPERATION_SEQ_NUM,
976 FROM_END_ITEM_UNIT_NUMBER,
977 BATCH_ID
978 FROM bom_inventory_comps_interface
979 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
980 AND change_notice is null --added for bug 9673701
981 AND transaction_type = G_Create
982 AND (all_org = 1
983 OR
984 (all_org = 2 AND organization_id = org_id))
985 AND reference_designator is not null
986 AND
987 (
988 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
989 OR ( p_batch_id = batch_id )
990 );
991 COMMIT;
992
993 stmt_num := 11;
994
995 /* Update the interface records with process_flag 3 and insert into
996 mtl_interface_errors if Item_number or Organization_code is missing*/
997
998 l_sysdate := SYSDATE;
999 msg_name1 := 'BOM_ORG_ID_MISSING';
1000 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1001 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1002 msg_text1 := FND_MESSAGE.GET;
1003 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1004 msg_text2 := FND_MESSAGE.GET;
1005 INSERT INTO mtl_interface_errors
1006 (
1007 TRANSACTION_ID,
1008 UNIQUE_ID,
1009 ORGANIZATION_ID,
1010 COLUMN_NAME,
1011 TABLE_NAME,
1012 MESSAGE_NAME,
1013 ERROR_MESSAGE,
1014 LAST_UPDATE_DATE,
1015 LAST_UPDATED_BY,
1016 CREATION_DATE,
1017 CREATED_BY,
1018 LAST_UPDATE_LOGIN,
1019 REQUEST_ID,
1020 PROGRAM_APPLICATION_ID,
1021 PROGRAM_ID,
1022 PROGRAM_UPDATE_DATE
1023 )
1024 Select
1025 BICI.transaction_id,
1026 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1027 Null,
1028 null,
1029 'BOM_INVENTORY_COMPS_INTERFACE',
1030 decode ( BICI.Organization_code, null, msg_name1,msg_name2),
1031 decode ( BICI.Organization_code, null, msg_text1,msg_text2),
1032 NVL(LAST_UPDATE_DATE, SYSDATE),
1033 NVL(LAST_UPDATED_BY, user_id),
1034 NVL(CREATION_DATE,SYSDATE),
1035 NVL(CREATED_BY, user_id),
1036 NVL(LAST_UPDATE_LOGIN, user_id),
1037 NVL(REQUEST_ID, req_id),
1038 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1039 NVL(PROGRAM_ID, prog_id),
1040 NVL(PROGRAM_UPDATE_DATE, sysdate)
1041
1042 from BOM_INVENTORY_COMPS_INTERFACE BICI
1043 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1044 and transaction_id is not null
1045 and process_flag =1
1046 AND change_notice is null --added for bug 9673701
1047 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1048 AND
1049 (
1050 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
1051 OR ( p_batch_id = BICI.batch_id )
1052 );
1053
1054
1055
1056 Update BOM_INVENTORY_COMPS_INTERFACE
1057 set process_flag = 3
1058 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1059 and transaction_id is not null
1060 and process_flag =1
1061 AND change_notice is null --added for bug 9673701
1062 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1063 AND
1064 (
1065 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1066 OR ( p_batch_id = batch_id )
1067 );
1068 Commit;
1069
1070 return (0);
1071
1072
1073 EXCEPTION
1074 WHEN others THEN
1075 err_text := 'Bom_Open_Interface_Utl(Process_component_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1076 RETURN(SQLCODE);
1077 END;
1078
1079
1080 /*--------------------------Process_Ref_Degs_Info------------------------------
1081
1082 NAME
1083 Process_Ref_Degs_Info
1084 DESCRIPTION
1085 Populate the user-friendly columns to Reference Designator records
1086 in the interface table
1087 REQUIRES
1088
1089 MODIFIES
1090 BOM_REF_DESGS_INTERFACE
1091 MTL_INTERFACE_ERRORS
1092 RETURNS
1093 0 if successful
1094 SQLCODE if unsuccessful
1095 NOTES
1096 -----------------------------------------------------------------------------*/
1097 FUNCTION Process_Ref_Degs_Info (
1098 org_id NUMBER,
1099 all_org NUMBER ,
1100 user_id NUMBER,
1101 login_id NUMBER,
1102 prog_appid NUMBER,
1103 prog_id NUMBER,
1104 req_id NUMBER,
1105 err_text IN OUT NOCOPY VARCHAR2,
1106 p_batch_id IN NUMBER
1107 )
1108 return INTEGER
1109 IS
1110 stmt_num NUMBER := 0;
1111 l_sysdate DATE := SYSDATE;
1112 msg_name1 varchar2(30);
1113 msg_name2 varchar2(30);
1114 msg_text1 varchar2(2000);
1115 msg_text2 varchar2(2000);
1116
1117 BEGIN
1118
1119 stmt_num := 1;
1120 /* Resolve the Component_sequence_id for all the records */
1121
1122 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1123 SET(bill_sequence_id, component_item_id, effectivity_date,
1124 operation_seq_num, from_end_item_unit_number)
1125 = (SELECT bill_sequence_id, component_item_id,
1126 effectivity_date, operation_seq_num, from_end_item_unit_number
1127 FROM bom_components_b BIC1
1128 WHERE BIC1.component_sequence_id = BRDI.component_sequence_id )
1129 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1130 AND change_notice is null --added for bug 9673701
1131 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1132 AND COMPONENT_SEQUENCE_ID is not null
1133 AND
1134 (
1135 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1136 OR ( p_batch_id = BRDI.batch_id )
1137 )
1138 AND exists (SELECT 'x'
1139 FROM bom_components_b BIC2
1140 WHERE BIC2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID );
1141
1142
1143 stmt_num := 2;
1144 /* Resolve the Bill sequence ids for updates and deletes */
1145
1146 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1147 SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
1148 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
1149 FROM bom_structures_b BBM1
1150 WHERE BBM1.bill_sequence_id = BRDI.bill_sequence_id)
1151 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1152 AND change_notice is null --added for bug 9673701
1153 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1154 AND bill_sequence_id is not null
1155 AND
1156 (
1157 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1158 OR ( p_batch_id = BRDI.batch_id )
1159 )
1160 AND exists (SELECT 'x'
1161 FROM bom_structures_b BBM2
1162 WHERE BBM2.bill_sequence_id = BRDI.bill_sequence_id);
1163
1164
1165 stmt_num := 3;
1166 /* Update Organization Code using Organization_id
1167 this also needed if Organization_id is given and code is not given*/
1168
1169 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1170 SET organization_code = (SELECT organization_code
1171 FROM MTL_PARAMETERS mp1
1172 WHERE mp1.organization_id = BRDI.organization_id)
1173 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1174 AND change_notice is null --added for bug 9673701
1175 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1176 AND organization_id is not null
1177 AND
1178 (
1179 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1180 OR ( p_batch_id = BRDI.batch_id )
1181 )
1182 AND exists (SELECT 'x'
1183 FROM MTL_PARAMETERS mp2
1184 WHERE mp2.organization_id = BRDI.organization_id);
1185
1186
1187
1188 stmt_num := 4;
1189 /* Update Organization_ids if organization_code is given org id is null.
1190 Orgnaization_id information is needed in the next steps */
1191
1192 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1193 SET organization_id = (SELECT organization_id
1194 FROM MTL_PARAMETERS mp1
1195 WHERE mp1.organization_code = BRDI.organization_code)
1196 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1197 AND change_notice is null --added for bug 9673701
1198 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1199 AND organization_id is null
1200 AND organization_code is not null
1201 AND
1202 (
1203 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1204 OR ( p_batch_id = BRDI.batch_id )
1205 );
1206
1207
1208
1209 stmt_num := 5;
1210 /* Update Assembly Item name */
1211
1212 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1213 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1214 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1215 WHERE mvl1.inventory_item_id = BRDI.assembly_item_id
1216 and mvl1.organization_id = BRDI.organization_id)
1217 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1218 AND change_notice is null --added for bug 9673701
1219 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1220 AND assembly_item_id is not null
1221 AND organization_id is not null
1222 AND
1223 (
1224 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1225 OR ( p_batch_id = BRDI.batch_id )
1226 )
1227 AND exists (select 'x'
1228 FROM mtl_system_items MKFV
1229 WHERE MKFV.inventory_item_id = BRDI.assembly_item_id
1230 AND MKFV.organization_id = BRDI.organization_id );
1231
1232
1233 stmt_num := 6;
1234 /* Update Component Item name */
1235
1236 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1237 SET COMPONENT_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1238 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1239 WHERE mvl1.inventory_item_id = BRDI.component_item_id
1240 AND mvl1.organization_id = BRDI.organization_id)
1241 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1242 AND change_notice is null --added for bug 9673701
1243 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1244 AND COMPONENT_ITEM_ID is not null
1245 AND organization_id is not null
1246 AND
1247 (
1248 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1249 OR ( p_batch_id = BRDI.batch_id )
1250 )
1251 AND exists (SELECT 'x'
1252 FROM mtl_system_items MKFV
1253 WHERE MKFV.inventory_item_id = BRDI.component_item_id
1254 AND MKFV.organization_id = BRDI.organization_id);
1255
1256
1257 stmt_num := 7;
1258 /* Assign transaction ids */
1259
1260 UPDATE BOM_REF_DESGS_INTERFACE BRDI
1261 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1262 transaction_type = upper(transaction_type)
1263 WHERE transaction_id is null
1264 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1265 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1266 AND change_notice is null --added for bug 9673701
1267 AND (all_org = 1
1268 OR
1269 (all_org = 2 AND organization_id = org_id))
1270 AND
1271 (
1272 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1273 OR ( p_batch_id = BRDI.batch_id )
1274 );
1275
1276 COMMIT;
1277
1278 stmt_num := 8;
1279 /* Update the interface records with process_flag 3 and insert into
1280 mtl_interface_errors if Item_number or Organization_code is missing*/
1281
1282 l_sysdate := SYSDATE;
1283 msg_name1 := 'BOM_ORG_ID_MISSING';
1284 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1285 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1286 msg_text1 := FND_MESSAGE.GET;
1287 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1288 msg_text2 := FND_MESSAGE.GET;
1289 INSERT INTO mtl_interface_errors
1290 (
1291 TRANSACTION_ID,
1292 UNIQUE_ID,
1293 ORGANIZATION_ID,
1294 COLUMN_NAME,
1295 TABLE_NAME,
1296 MESSAGE_NAME,
1297 ERROR_MESSAGE,
1298 LAST_UPDATE_DATE,
1299 LAST_UPDATED_BY,
1300 CREATION_DATE,
1301 CREATED_BY,
1302 LAST_UPDATE_LOGIN,
1303 REQUEST_ID,
1304 PROGRAM_APPLICATION_ID,
1305 PROGRAM_ID,
1306 PROGRAM_UPDATE_DATE
1307 )
1308 Select
1309 BRDI.transaction_id,
1310 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1311 Null,
1312 null,
1313 'BOM_REF_DESGS_INTERFACE',
1314 decode ( BRDI.Organization_code, null, msg_name1,msg_name2),
1315 decode ( BRDI.Organization_code, null, msg_text1,msg_text2),
1316 NVL(LAST_UPDATE_DATE, SYSDATE),
1317 NVL(LAST_UPDATED_BY, user_id),
1318 NVL(CREATION_DATE,SYSDATE),
1319 NVL(CREATED_BY, user_id),
1320 NVL(LAST_UPDATE_LOGIN, user_id),
1321 NVL(REQUEST_ID, req_id),
1322 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1323 NVL(PROGRAM_ID, prog_id),
1324 NVL(PROGRAM_UPDATE_DATE, sysdate)
1325 from BOM_REF_DESGS_INTERFACE BRDI
1326 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1327 and transaction_id is not null
1328 and process_flag =1
1329 AND change_notice is null --added for bug 9673701
1330 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1331 AND
1332 (
1333 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
1334 OR ( p_batch_id = BRDI.batch_id )
1335 );
1336
1337 Update BOM_REF_DESGS_INTERFACE
1338 set process_flag = 3
1339 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1340 and transaction_id is not null
1341 and process_flag =1
1342 AND change_notice is null --added for bug 9673701
1343 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1344 AND
1345 (
1346 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1347 OR ( p_batch_id = batch_id )
1348 );
1349 Commit;
1350
1351 return(0);
1352
1353 EXCEPTION
1354 WHEN others THEN
1355 err_text := 'Bom_Open_Interface_Utl(Process_ref_desgs_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1356 RETURN(SQLCODE);
1357 END;
1358
1359 /*--------------------------Process_Sub_Comps_Info------------------------------
1360
1361 NAME
1362 Process_Sub_Comps_Info
1363 DESCRIPTION
1364 Populate the user-friendly columns to Substitute Component records
1365 in the interface table
1366 REQUIRES
1367
1368 MODIFIES
1369 BOM_SUB_COMPS_INTERFACE
1370 MTL_INTERFACE_ERRORS
1371 RETURNS
1372 0 if successful
1373 SQLCODE if unsuccessful
1374 NOTES
1375 -----------------------------------------------------------------------------*/
1376 FUNCTION Process_Sub_Comps_Info (
1377 org_id NUMBER,
1378 all_org NUMBER ,
1379 user_id NUMBER,
1380 login_id NUMBER,
1381 prog_appid NUMBER,
1382 prog_id NUMBER,
1383 req_id NUMBER,
1384 err_text IN OUT NOCOPY VARCHAR2,
1385 p_batch_id IN NUMBER
1386 )
1387 return INTEGER
1388 IS
1389 stmt_num NUMBER := 0;
1390 l_sysdate DATE := SYSDATE;
1391 msg_name1 varchar2(30);
1392 msg_name2 varchar2(30);
1393 msg_text1 varchar2(2000);
1394 msg_text2 varchar2(2000);
1395
1396 BEGIN
1397
1398 stmt_num := 1;
1399 /* Resolve the Component_sequence_id for all the records */
1400
1401 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1402 SET(bill_sequence_id, component_item_id, effectivity_date,
1403 operation_seq_num, from_end_item_unit_number)
1404 = (select bill_sequence_id, component_item_id,
1405 EFFECTIVITY_DATE, OPERATION_SEQ_NUM, FROM_END_ITEM_UNIT_NUMBER
1406 FROM bom_components_b BIC1
1407 WHERE BIC1.COMPONENT_SEQUENCE_ID = BSCI.component_sequence_id )
1408 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1409 AND change_notice is null --added for bug 9673701
1410 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1411 AND COMPONENT_SEQUENCE_ID is not null
1412 AND
1413 (
1414 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1415 OR ( p_batch_id = BSCI.batch_id )
1416 )
1417 AND exists (SELECT 'x'
1418 FROM bom_components_b BIC2
1419 WHERE BIC2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID );
1420
1421
1422 stmt_num := 2;
1423 /* Resolve the Bill sequence ids for updates and deletes */
1424
1425 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1426 SET(assembly_item_id, organization_id, alternate_bom_designator)
1427 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
1428 FROM bom_structures_b BBM1
1429 WHERE BBM1.bill_sequence_id = BSCI.bill_sequence_id)
1430 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1431 AND change_notice is null --added for bug 9673701
1432 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1433 AND bill_sequence_id is not null
1434 AND
1435 (
1436 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1437 OR ( p_batch_id = BSCI.batch_id )
1438 )
1439 AND exists (SELECT 'x'
1440 FROM bom_structures_b BBM2
1441 WHERE BBM2.bill_sequence_id = BSCI.bill_sequence_id);
1442
1443
1444 stmt_num := 3;
1445 /* Update Organization Code using Organization_id
1446 this also needed if Organization_id is given and code is not given*/
1447
1448 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1449 SET organization_code = (SELECT organization_code
1450 FROM MTL_PARAMETERS mp1
1451 WHERE mp1.organization_id = BSCI.organization_id)
1452 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1453 AND change_notice is null --added for bug 9673701
1454 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1455 AND organization_id is not null
1456 AND
1457 (
1458 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1459 OR ( p_batch_id = BSCI.batch_id )
1460 )
1461 AND exists (SELECT 'x'
1462 FROM MTL_PARAMETERS mp2
1463 WHERE mp2.organization_id = BSCI.organization_id);
1464
1465
1466
1467 stmt_num := 4;
1468 /* Update Organization_ids if organization_code is given org id is null.
1469 Orgnaization_id information is needed in the next steps */
1470
1471 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1472 SET organization_id = (SELECT organization_id
1473 FROM MTL_PARAMETERS mp1
1474 WHERE mp1.organization_code = BSCI.organization_code)
1475 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1476 AND change_notice is null --added for bug 9673701
1477 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1478 AND organization_id is null
1479 AND organization_code is not null
1480 AND
1481 (
1482 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1483 OR ( p_batch_id = BSCI.batch_id )
1484 );
1485
1486
1487
1488 stmt_num := 5;
1489 /* Update Assembly Item name */
1490
1491 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1492 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1493 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1494 WHERE mvl1.inventory_item_id = BSCI.assembly_item_id
1495 AND mvl1.organization_id = BSCI.organization_id)
1496 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1497 AND change_notice is null --added for bug 9673701
1498 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1499 AND assembly_item_id is not null
1500 AND organization_id is not null
1501 AND
1502 (
1503 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1504 OR ( p_batch_id = BSCI.batch_id )
1505 )
1506 AND exists (SELECT 'x'
1507 FROM mtl_system_items MKFV
1508 WHERE MKFV.inventory_item_id = BSCI.assembly_item_id
1509 AND MKFV.organization_id = BSCI.organization_id);
1510
1511
1512 stmt_num := 6;
1513 /* Update Component Item name */
1514
1515 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1516 SET COMPONENT_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1517 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1518 WHERE mvl1.inventory_item_id = BSCI.component_item_id
1519 AND mvl1.organization_id = BSCI.organization_id)
1520 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1521 AND change_notice is null --added for bug 9673701
1522 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1523 AND COMPONENT_ITEM_ID is not null
1524 AND organization_id is not null
1525 AND
1526 (
1527 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1528 OR ( p_batch_id = BSCI.batch_id )
1529 )
1530 AND exists (SELECT 'x'
1531 FROM mtl_system_items MKFV
1532 WHERE MKFV.inventory_item_id = BSCI.component_item_id
1533 AND MKFV.organization_id = BSCI.organization_id);
1534
1535
1536 stmt_num := 7;
1537 /* Update Substitute Component name if Id is given */
1538
1539 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1540 SET SUBSTITUTE_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
1541 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1542 WHERE mvl1.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
1543 and mvl1.organization_id = BSCI.organization_id)
1544 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1545 AND change_notice is null --added for bug 9673701
1546 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1547 AND SUBSTITUTE_COMPONENT_ID is not null
1548 AND organization_id is not null
1549 AND
1550 (
1551 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1552 OR ( p_batch_id = BSCI.batch_id )
1553 )
1554 AND exists (SELECT 'x'
1555 FROM mtl_system_items MKFV
1556 WHERE MKFV.inventory_item_id = BSCI.substitute_component_id
1557 AND MKFV.organization_id = BSCI.organization_id);
1558
1559
1560
1561 stmt_num := 8;
1562 /* Update new Substitute Component name if Id is given */
1563
1564 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1565 SET NEW_SUB_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
1566 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1567 WHERE mvl1.inventory_item_id = BSCI.NEW_SUB_COMP_ID
1568 and mvl1.organization_id = BSCI.organization_id)
1569 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1570 AND change_notice is null --added for bug 9673701
1571 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1572 AND NEW_SUB_COMP_ID is not null
1573 AND organization_id is not null
1574 AND
1575 (
1576 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1577 OR ( p_batch_id = BSCI.batch_id )
1578 )
1579 AND exists (SELECT 'x'
1580 FROM mtl_system_items MKFV
1581 WHERE MKFV.inventory_item_id = BSCI.new_sub_comp_id
1582 AND MKFV.organization_id = BSCI.organization_id);
1583
1584 stmt_num := 9;
1585 /* Assign transaction ids */
1586
1587 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
1588 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1589 transaction_type = upper(transaction_type)
1590 WHERE transaction_id is null
1591 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1592 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1593 AND change_notice is null --added for bug 9673701
1594 AND (all_org = 1
1595 OR
1596 (all_org = 2 AND organization_id = org_id))
1597 AND
1598 (
1599 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1600 OR ( p_batch_id = BSCI.batch_id )
1601 );
1602
1603 COMMIT;
1604 stmt_num := 10;
1605 /* Update the interface records with process_flag 3 and insert into
1606 mtl_interface_errors if Item_number or Organization_code is missing*/
1607
1608 l_sysdate := SYSDATE;
1609 msg_name1 := 'BOM_ORG_ID_MISSING';
1610 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1611 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1612 msg_text1 := FND_MESSAGE.GET;
1613 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1614 msg_text2 := FND_MESSAGE.GET;
1615 INSERT INTO mtl_interface_errors
1616 (
1617 TRANSACTION_ID,
1618 UNIQUE_ID,
1619 ORGANIZATION_ID,
1620 COLUMN_NAME,
1621 TABLE_NAME,
1622 MESSAGE_NAME,
1623 ERROR_MESSAGE,
1624 LAST_UPDATE_DATE,
1625 LAST_UPDATED_BY,
1626 CREATION_DATE,
1627 CREATED_BY,
1628 LAST_UPDATE_LOGIN,
1629 REQUEST_ID,
1630 PROGRAM_APPLICATION_ID,
1631 PROGRAM_ID,
1632 PROGRAM_UPDATE_DATE
1633 )
1634 Select
1635 BSCI.transaction_id,
1636 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1637 Null,
1638 null,
1639 'BOM_INVENTORY_COMPS_INTERFACE',
1640 decode ( BSCI.Organization_code, null, msg_name1,msg_name2),
1641 decode ( BSCI.Organization_code, null, msg_text1,msg_text2),
1642 NVL(LAST_UPDATE_DATE, SYSDATE),
1643 NVL(LAST_UPDATED_BY, user_id),
1644 NVL(CREATION_DATE,SYSDATE),
1645 NVL(CREATED_BY, user_id),
1646 NVL(LAST_UPDATE_LOGIN, user_id),
1647 NVL(REQUEST_ID, req_id),
1648 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1649 NVL(PROGRAM_ID, prog_id),
1650 NVL(PROGRAM_UPDATE_DATE, sysdate)
1651
1652 from BOM_SUB_COMPS_INTERFACE BSCI
1653 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1654 and transaction_id is not null
1655 and process_flag =1
1656 AND change_notice is null --added for bug 9673701
1657 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1658 AND
1659 (
1660 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
1661 OR ( p_batch_id = BSCI.batch_id )
1662 );
1663
1664 Update BOM_SUB_COMPS_INTERFACE
1665 set process_flag = 3
1666 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1667 and transaction_id is not null
1668 and process_flag =1
1669 AND change_notice is null --added for bug 9673701
1670 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1671 AND
1672 (
1673 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1674 OR ( p_batch_id = batch_id )
1675 );
1676 Commit;
1677
1678 return(0);
1679
1680 EXCEPTION
1681 WHEN others THEN
1682 err_text := 'Bom_Open_Interface_Utl(Process_sub_comps_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1683 RETURN(SQLCODE);
1684 END;
1685
1686 /*--------------------------Process_Comp_Ops_Info------------------------------
1687
1688 NAME
1689 Process_Comp_Ops_Info
1690 DESCRIPTION
1691 Populate the user-friendly columns to Component Operations records
1692 in the interface table
1693 REQUIRES
1694
1695 MODIFIES
1696 BOM_COMPONENT_OPS_INTERFACE
1697 MTL_INTERFACE_ERRORS
1698 RETURNS
1699 0 if successful
1700 SQLCODE if unsuccessful
1701 NOTES
1702 -----------------------------------------------------------------------------*/
1703
1704 FUNCTION Process_Comp_Ops_Info (
1705 org_id NUMBER,
1706 all_org NUMBER ,
1707 user_id NUMBER,
1708 login_id NUMBER,
1709 prog_appid NUMBER,
1710 prog_id NUMBER,
1711 req_id NUMBER,
1712 err_text IN OUT NOCOPY VARCHAR2,
1713 p_batch_id IN NUMBER
1714 )
1715 return INTEGER
1716 IS
1717 stmt_num NUMBER := 0;
1718 l_sysdate DATE := SYSDATE;
1719 msg_name1 varchar2(30);
1720 msg_name2 varchar2(30);
1721 msg_text1 varchar2(2000);
1722 msg_text2 varchar2(2000);
1723
1724 BEGIN
1725
1726 stmt_num := 1;
1727 /* Resolve the Component_sequence_id for all the records */
1728
1729 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1730 SET(bill_sequence_id, component_item_id, effectivity_date,
1731 operation_seq_num, from_end_item_unit_number)
1732 = (SELECT bill_sequence_id, component_item_id,
1733 effectivity_date, operation_seq_num, from_end_item_unit_number
1734 FROM bom_components_b BIC1
1735 WHERE BIC1.component_sequence_id = BCOI.component_sequence_id )
1736 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1737 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1738 AND COMPONENT_SEQUENCE_ID is not null
1739 AND
1740 (
1741 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1742 OR ( p_batch_id = BCOI.batch_id )
1743 )
1744 AND exists (SELECT 'x'
1745 FROM BOM_INVENTORY_COMPONENTS BIC2
1746 WHERE BIC2.COMPONENT_SEQUENCE_ID = BCOI.COMPONENT_SEQUENCE_ID );
1747
1748
1749 stmt_num := 2;
1750 /* Resolve the Bill sequence ids for updates and deletes */
1751
1752 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1753 SET(assembly_item_id, organization_id, alternate_bom_designator)
1754 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
1755 FROM bom_structures_b BBM1
1756 WHERE BBM1.bill_sequence_id = BCOI.bill_sequence_id)
1757 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1758 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1759 AND bill_sequence_id is not null
1760 AND
1761 (
1762 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1763 OR ( p_batch_id = BCOI.batch_id )
1764 )
1765 AND exists (SELECT 'x'
1766 FROM bom_structures_b BBM2
1767 WHERE BBM2.bill_sequence_id = BCOI.bill_sequence_id);
1768
1769
1770 stmt_num := 3;
1771 /* Update Organization Code using Organization_id
1772 this also needed if orgnaization_id is given and code is not given*/
1773
1774 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1775 SET organization_code = (SELECT organization_code
1776 FROM MTL_PARAMETERS mp1
1777 WHERE mp1.organization_id = BCOI.organization_id)
1778 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1779 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1780 AND organization_id is not null
1781 AND
1782 (
1783 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1784 OR ( p_batch_id = BCOI.batch_id )
1785 )
1786 AND exists (SELECT 'x'
1787 FROM MTL_PARAMETERS mp2
1788 WHERE mp2.organization_id = BCOI.organization_id);
1789
1790
1791
1792 stmt_num := 4;
1793 /* Update Organization_ids if organization_code is given org id is null.
1794 Orgnaization_id information is needed in the next steps */
1795
1796 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1797 SET organization_id = (SELECT organization_id
1798 FROM MTL_PARAMETERS mp1
1799 WHERE mp1.organization_code = BCOI.organization_code)
1800 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1801 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1802 AND organization_id is null
1803 AND organization_code is not null
1804 AND
1805 (
1806 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1807 OR ( p_batch_id = BCOI.batch_id )
1808 );
1809
1810
1811
1812 stmt_num := 5;
1813 /* Update Assembly Item name */
1814
1815 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1816 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1817 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1818 WHERE mvl1.inventory_item_id = BCOI.assembly_item_id
1819 and mvl1.organization_id = BCOI.organization_id)
1820 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1821 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1822 AND assembly_item_id is not null
1823 AND organization_id is not null
1824 AND
1825 (
1826 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1827 OR ( p_batch_id = BCOI.batch_id )
1828 )
1829 AND exists (SELECT 'x'
1830 FROM mtl_system_items MKFV
1831 WHERE MKFV.inventory_item_id = BCOI.assembly_item_id
1832 AND MKFV.organization_id = BCOI.organization_id);
1833
1834
1835 stmt_num := 6;
1836 /* Update Component Item name */
1837
1838 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1839 SET COMPONENT_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1840 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1841 WHERE mvl1.inventory_item_id = BCOI.component_item_id
1842 and mvl1.organization_id = BCOI.organization_id)
1843 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1844 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1845 AND COMPONENT_ITEM_ID is not null
1846 AND organization_id is not null
1847 AND
1848 (
1849 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1850 OR ( p_batch_id = BCOI.batch_id )
1851 )
1852 AND exists (SELECT 'x'
1853 FROM mtl_system_items MKFV
1854 WHERE MKFV.inventory_item_id = BCOI.component_item_id
1855 AND MKFV.organization_id = BCOI.organization_id);
1856
1857
1858 stmt_num := 8;
1859 /* Assign transaction ids */
1860
1861 UPDATE BOM_COMPONENT_OPS_INTERFACE BCOI
1862 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1863 transaction_type = upper(transaction_type)
1864 WHERE transaction_id is null
1865 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1866 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
1867 AND (all_org = 1
1868 OR
1869 (all_org = 2 AND organization_id = org_id))
1870 AND
1871 (
1872 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1873 OR ( p_batch_id = BCOI.batch_id )
1874 );
1875
1876 COMMIT;
1877
1878 stmt_num := 9;
1879 /* Update the interface records with process_flag 3 and insert into
1880 mtl_interface_errors if Item_number or Organization_code is missing*/
1881
1882 l_sysdate := SYSDATE;
1883 msg_name1 := 'BOM_ORG_ID_MISSING';
1884 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1885 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1886 msg_text1 := FND_MESSAGE.GET;
1887 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1888 msg_text2 := FND_MESSAGE.GET;
1889 INSERT INTO mtl_interface_errors
1890 (
1891 TRANSACTION_ID,
1892 UNIQUE_ID,
1893 ORGANIZATION_ID,
1894 COLUMN_NAME,
1895 TABLE_NAME,
1896 MESSAGE_NAME,
1897 ERROR_MESSAGE,
1898 LAST_UPDATE_DATE,
1899 LAST_UPDATED_BY,
1900 CREATION_DATE,
1901 CREATED_BY,
1902 LAST_UPDATE_LOGIN,
1903 REQUEST_ID,
1904 PROGRAM_APPLICATION_ID,
1905 PROGRAM_ID,
1906 PROGRAM_UPDATE_DATE
1907 )
1908 Select
1909 BCOI.transaction_id,
1910 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1911 Null,
1912 null,
1913 'BOM_COMPONENT_OPS_INTERFACE',
1914 decode ( BCOI.Organization_code, null, msg_name1,msg_name2),
1915 decode ( BCOI.Organization_code, null, msg_text1,msg_text2),
1916 NVL(LAST_UPDATE_DATE, SYSDATE),
1917 NVL(LAST_UPDATED_BY, user_id),
1918 NVL(CREATION_DATE,SYSDATE),
1919 NVL(CREATED_BY, user_id),
1920 NVL(LAST_UPDATE_LOGIN, user_id),
1921 req_id,
1922 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1923 NVL(PROGRAM_ID, prog_id),
1924 NVL(PROGRAM_UPDATE_DATE, sysdate)
1925 from BOM_COMPONENT_OPS_INTERFACE BCOI
1926 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1927 and transaction_id is not null
1928 and process_flag =1
1929 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1930 AND
1931 (
1932 ( (p_batch_id IS NULL) AND (BCOI.batch_id IS NULL) )
1933 OR ( p_batch_id = BCOI.batch_id )
1934 );
1935
1936 Update BOM_COMPONENT_OPS_INTERFACE
1937 set process_flag = 3
1938 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1939 and transaction_id is not null
1940 and process_flag =1
1941 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
1942 AND
1943 (
1944 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1945 OR ( p_batch_id = batch_id )
1946 );
1947 Commit;
1948
1949 return(0);
1950
1951 EXCEPTION
1952 WHEN others THEN
1953 err_text := 'Bom_Open_Interface_Utl(Process_comp_ops_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1954 RETURN(SQLCODE);
1955 END;
1956
1957 FUNCTION Process_Revision_Info (
1958 org_id NUMBER,
1959 all_org NUMBER ,
1960 user_id NUMBER,
1961 login_id NUMBER,
1962 prog_appid NUMBER,
1963 prog_id NUMBER,
1964 req_id NUMBER,
1965 err_text IN OUT NOCOPY VARCHAR2,
1966 p_set_process_id IN NUMBER
1967 )return integer is
1968 stmt_num NUMBER := 0;
1969 l_sysdate DATE := SYSDATE;
1970 msg_name1 varchar2(30);
1971 msg_name2 varchar2(30);
1972 msg_text1 varchar2(2000);
1973 msg_text2 varchar2(2000);
1974 l_set_process_id NUMBER := 0;
1975 begin
1976 --if set_process_id is null then set it to 0 which is table level default value
1977 IF ( p_set_process_id IS NULL ) THEN
1978 l_set_process_id := 0;
1979 ELSE
1980 l_set_process_id := p_set_process_id;
1981 END IF;
1982
1983 stmt_num := 1;
1984
1985 /* Update Organization Code using Organization_id
1986 this also needed if Organization_id is given and code is not given*/
1987
1988 UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
1989 SET organization_code = (SELECT organization_code
1990 FROM MTL_PARAMETERS MP1
1991 WHERE mp1.organization_id = MIRI.organization_id)
1992 WHERE process_flag = 1
1993 AND change_notice is null --added for bug 9673701
1994 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1995 AND organization_id is not null
1996 AND MIRI.set_process_id = l_set_process_id
1997 AND exists (SELECT 'x'
1998 FROM MTL_PARAMETERS MP2
1999 WHERE mp2.organization_id = MIRI.organization_id);
2000
2001
2002
2003 stmt_num := 2;
2004 /* Update Organization_ids if organization code is given org id is null.
2005 Orgnaization_id information is needed in the next steps */
2006
2007 UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
2008 SET organization_id = (SELECT organization_id
2009 FROM MTL_PARAMETERS mp1
2010 WHERE mp1.organization_code = MIRI.organization_code)
2011 WHERE process_flag = 1
2012 AND change_notice is null --added for bug 9673701
2013 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
2014 AND organization_id is null
2015 AND organization_code is not null
2016 AND MIRI.set_process_id = l_set_process_id;
2017
2018
2019
2020 stmt_num := 3;
2021 /* Update Assembly Item name */
2022
2023 UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
2024 SET item_number = (SELECT concatenated_segments
2025 FROM MTL_SYSTEM_ITEMS_KFV mvl1
2026 WHERE mvl1.inventory_item_id = MIRI.inventory_item_id
2027 and mvl1.organization_id = MIRI.organization_id)
2028 WHERE process_flag = 1
2029 AND change_notice is null --added for bug 9673701
2030 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
2031 AND inventory_item_id is not null
2032 AND organization_id is not null
2033 AND MIRI.set_process_id = l_set_process_id
2034 AND exists (SELECT 'x'
2035 FROM mtl_system_items mvl2
2036 WHERE mvl2.inventory_item_id = MIRI.inventory_item_id
2037 and mvl2.organization_id = MIRI.organization_id);
2038
2039
2040
2041 stmt_num := 5;
2042 /* Assign transaction ids */
2043
2044 UPDATE MTL_ITEM_REVISIONS_INTERFACE MIRI
2045 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
2046 transaction_type = upper(transaction_type)
2047 WHERE transaction_id is null
2048 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
2049 AND process_flag = 1
2050 AND change_notice is null --added for bug 9673701
2051 AND (all_org = 1
2052 OR
2053 (all_org = 2 AND organization_id = org_id))
2054 AND MIRI.set_process_id = l_set_process_id;
2055
2056 COMMIT;
2057 stmt_num := 6;
2058
2059 /* Update the interface records with process_flag 3 and insert into
2060 MTL_INTERFACE_ERRORS if Item number or Organization_code is missing*/
2061
2062 l_sysdate := SYSDATE;
2063 msg_name1 := 'BOM_ORG_ID_MISSING';
2064 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
2065 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
2066 msg_text1 := FND_MESSAGE.GET;
2067 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
2068 msg_text2 := FND_MESSAGE.GET;
2069
2070 INSERT INTO MTL_INTERFACE_ERRORS
2071 (
2072 TRANSACTION_ID,
2073 UNIQUE_ID,
2074 ORGANIZATION_ID,
2075 COLUMN_NAME,
2076 TABLE_NAME,
2077 MESSAGE_NAME,
2078 ERROR_MESSAGE,
2079 LAST_UPDATE_DATE,
2080 LAST_UPDATED_BY,
2081 CREATION_DATE,
2082 CREATED_BY,
2083 LAST_UPDATE_LOGIN,
2084 REQUEST_ID,
2085 PROGRAM_APPLICATION_ID,
2086 PROGRAM_ID,
2087 PROGRAM_UPDATE_DATE
2088 )
2089 Select
2090 MIRI.transaction_id,
2091 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
2092 Null,
2093 null,
2094 'MTL_ITEM_REVISIONS_INTERFACE',
2095 decode ( MIRI.Organization_code, null, msg_name1,msg_name2),
2096 decode ( MIRI.Organization_code, null, msg_text1,msg_text2),
2097 NVL(LAST_UPDATE_DATE, SYSDATE),
2098 NVL(LAST_UPDATED_BY, user_id),
2099 NVL(CREATION_DATE,SYSDATE),
2100 NVL(CREATED_BY, user_id),
2101 NVL(LAST_UPDATE_LOGIN, user_id),
2102 req_id,
2103 NVL(PROGRAM_APPLICATION_ID, prog_appid),
2104 NVL(PROGRAM_ID, prog_id),
2105 NVL(PROGRAM_UPDATE_DATE, sysdate)
2106 from MTL_ITEM_REVISIONS_INTERFACE MIRI
2107 where (organization_code is null or item_number is null)
2108 and transaction_id is not null
2109 and process_flag =1
2110 AND change_notice is null --added for bug 9673701
2111 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
2112 and MIRI.set_process_id = l_set_process_id;
2113
2114
2115 Update MTL_ITEM_REVISIONS_INTERFACE MIRI
2116 set process_flag = 3
2117 where (item_number is null or Organization_code is null)
2118 and transaction_id is not null
2119 and process_flag =1
2120 AND change_notice is null --added for bug 9673701
2121 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id))
2122 and MIRI.set_process_id = l_set_process_id;
2123
2124 Commit;
2125
2126 return(0);
2127
2128
2129 EXCEPTION
2130 WHEN others THEN
2131 err_text := 'Bom_Open_Interface_Utl(Process_Revision_Info-'||stmt_num||') '||substrb(SQLERRM,1,1000);
2132 RETURN(SQLCODE);
2133
2134 end;
2135
2136 /*--------------------------Process_All_Entities------------------------------
2137
2138 NAME
2139 Process_All_Entities
2140 DESCRIPTION
2141 Process all the entities - Bill, Components, Substitute Components,
2142 Reference Designators and Component Operations
2143 It will process all the entities with null batch id.
2144 RETURNS
2145 0 if successful
2146 SQLCODE if unsuccessful
2147 NOTES
2148 -----------------------------------------------------------------------------*/
2149 FUNCTION Process_All_Entities (
2150 org_id NUMBER,
2151 all_org NUMBER ,
2152 user_id NUMBER,
2153 login_id NUMBER,
2154 prog_appid NUMBER,
2155 prog_id NUMBER,
2156 req_id NUMBER,
2157 err_text IN OUT NOCOPY VARCHAR2
2158 )
2159 return INTEGER
2160 IS
2161 l_return_status INTEGER := 0;
2162 BEGIN
2163
2164 --call the process_all_entities with null batch id.
2165 l_return_status := Process_All_Entities (
2166 org_id => org_id,
2167 all_org => all_org,
2168 user_id => user_id,
2169 login_id => login_id,
2170 prog_appid => prog_appid,
2171 prog_id => prog_id,
2172 req_id => req_id,
2173 err_text => err_text,
2174 p_batch_id => NULL
2175 );
2176
2177 RETURN l_return_status;
2178 END;
2179
2180 /*--------------------------Process_All_Entities------------------------------
2181
2182 NAME
2183 Process_All_Entities
2184 DESCRIPTION
2185 Process all the entities - Bill, Components, Substitute Components,
2186 Reference Designators and Component Operations
2187 It will process all the entities for given batch id .
2188 RETURNS
2189 0 if successful
2190 SQLCODE if unsuccessful
2191 NOTES
2192 -----------------------------------------------------------------------------*/
2193 FUNCTION Process_All_Entities (
2194 org_id NUMBER,
2195 all_org NUMBER ,
2196 user_id NUMBER,
2197 login_id NUMBER,
2198 prog_appid NUMBER,
2199 prog_id NUMBER,
2200 req_id NUMBER,
2201 err_text IN OUT NOCOPY VARCHAR2,
2202 p_batch_id IN NUMBER
2203 )
2204 return INTEGER
2205 IS
2206 l_return_status INTEGER := 0;
2207 BEGIN
2208 l_return_status := Process_Header_Info
2209 (org_id,
2210 all_org,
2211 user_id,
2212 login_id,
2213 prog_appid,
2214 prog_id,
2215 req_id,
2216 err_text,
2217 p_batch_id);
2218 IF l_return_status <> 0 THEN
2219 RETURN l_return_status;
2220 END IF;
2221
2222 /* Set PK3_value if the value for revision exists */
2223 UPDATE BOM_BILL_OF_MTLS_INTERFACE BBMI
2224 SET pk3_value = (SELECT mrb.revision_id
2225 FROM mtl_item_revisions_b mrb
2226 WHERE mrb.inventory_item_id = BBMI.Assembly_item_id
2227 AND mrb.organization_id = BBMI.organization_id
2228 AND mrb.revision = BBMI.REVISION)
2229 WHERE process_flag = 1
2230 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2231 AND Assembly_item_id is not null
2232 AND Revision is not null
2233 AND organization_id is not null
2234 AND exists (SELECT 1
2235 FROM mtl_item_revisions_b mrb
2236 WHERE mrb.inventory_item_id = BBMI.Assembly_item_id
2237 AND mrb.organization_id = BBMI.organization_id
2238 AND mrb.revision = BBMI.Revision);
2239
2240 /* If SYNC rows has valid ComponentSequenceId then update the transaction type to UPDATE */
2241 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI SET transaction_type = G_Update
2242 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2243 AND UPPER(transaction_type) = 'SYNC'
2244 AND component_sequence_id IS NOT NULL
2245 AND
2246 (
2247 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2248 OR ( p_batch_id = BICI.batch_id )
2249 )
2250 AND EXISTS (SELECT 'x'
2251 FROM BOM_INVENTORY_COMPONENTS BIC2
2252 WHERE BIC2.component_sequence_id = BICI.component_sequence_id );
2253
2254 /* If SYNC rows don't have ComponentSequenceId value then update the transaction type to CREATE */
2255 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI SET transaction_type = G_Create
2256 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2257 AND UPPER(transaction_type) = 'SYNC'
2258 AND component_sequence_id IS NULL
2259 AND
2260 (
2261 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2262 OR ( p_batch_id = BICI.batch_id )
2263 );
2264
2265 /* Update Organization_ids if organization_code is given org id is null.
2266 Orgnaization_id information is needed in the next steps */
2267
2268 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2269 SET organization_id = (SELECT organization_id
2270 FROM MTL_PARAMETERS mp1
2271 WHERE mp1.organization_code = BICI.organization_code)
2272 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2273 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
2274 AND organization_code is not null
2275 AND
2276 (
2277 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2278 OR ( p_batch_id = BICI.batch_id )
2279 )
2280 AND exists (SELECT 'x'
2281 FROM MTL_PARAMETERS mp2
2282 WHERE mp2.organization_code = BICI.organization_code);
2283
2284 /* Update the Assembly_item_number */
2285 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2286 SET assembly_item_number = (SELECT concatenated_segments
2287 FROM mtl_system_items_kfv MKFV
2288 WHERE MKFV.inventory_item_id = BICI.Assembly_item_id
2289 AND MKFV.organization_id = BICI.organization_id)
2290 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2291 AND upper(transaction_type) in (G_Delete, G_Update, G_Create,G_NoOp)
2292 AND Assembly_item_id is not null
2293 AND organization_id is not null
2294 AND
2295 (
2296 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2297 OR ( p_batch_id = BICI.batch_id )
2298 )
2299 AND exists (SELECT 'x'
2300 FROM mtl_system_items MKFV2
2301 WHERE MKFV2.inventory_item_id = BICI.Assembly_item_id
2302 AND MKFV2.organization_id = BICI.organization_id);
2303
2304
2305
2306 /* Update the Assembly_item_id */
2307 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2308 SET Assembly_item_id = (SELECT inventory_item_id
2309 FROM mtl_system_items_kfv mvll
2310 WHERE mvll.concatenated_segments = BICI.Assembly_item_number
2311 AND mvll.organization_id = BICI.organization_id)
2312 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2313 AND upper(transaction_type) in (G_Delete, G_Update, G_Create,G_NoOp)
2314 AND Assembly_item_number is not null
2315 AND organization_id is not null
2316 AND assembly_item_id is NULL
2317 AND
2318 (
2319 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2320 OR ( p_batch_id = BICI.batch_id )
2321 );
2322
2323 /* Update component_item_number */
2324 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2325 SET component_item_number = (SELECT concatenated_segments
2326 FROM mtl_system_items_kfv mvll
2327 WHERE mvll.inventory_item_id = BICI.Component_item_id
2328 AND mvll.organization_id = BICI.organization_id)
2329 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2330 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2331 AND Component_item_id is not null
2332 AND Organization_id is not null
2333 AND
2334 (
2335 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2336 OR ( p_batch_id = BICI.batch_id )
2337 )
2338 AND exists (SELECT 'x'
2339 FROM mtl_system_items MKFV
2340 WHERE MKFV.inventory_item_id = BICI.Component_item_id
2341 AND MKFV.organization_id = BICI.organization_id);
2342
2343 /* Update the component_item_id */
2344 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2345 SET component_item_id = (SELECT inventory_item_id
2346 FROM mtl_system_items_kfv mvll
2347 WHERE mvll.concatenated_segments = BICI.Component_item_number
2348 AND mvll.organization_id = BICI.organization_id)
2349 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2350 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2351 AND Component_item_number is not null
2352 AND Organization_id is not null
2353 AND Component_item_id is null
2354 AND
2355 (
2356 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2357 OR ( p_batch_id = BICI.batch_id )
2358 );
2359
2360 /* Update the bill_sequence_id */
2361 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2362 SET bill_sequence_id = (SELECT bill_sequence_id
2363 FROM bom_structures_b bom
2364 WHERE bom.assembly_item_id = BICI.assembly_item_id
2365 AND bom.organization_id = BICI.organization_id
2366 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR))
2367 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2368 AND upper(transaction_type) in (G_Delete, G_Update)
2369 AND assembly_item_number is not null
2370 AND organization_id is not null
2371 AND
2372 (
2373 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2374 OR ( p_batch_id = BICI.batch_id )
2375 )
2376 AND exists (SELECT 'x'
2377 FROM bom_structures_b bom2
2378 WHERE bom2.assembly_item_id = BICI.assembly_item_id
2379 AND bom2.organization_id = BICI.organization_id
2380 AND NVL(bom2.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BICI.alternate_bom_designator,FND_API.G_MISS_CHAR));
2381
2382 /*
2383
2384 l_return_status := Process_Comps_Info
2385 (org_id,
2386 all_org,
2387 user_id,
2388 login_id,
2389 prog_appid,
2390 prog_id,
2391 req_id,
2392 err_text,
2393 p_batch_id);
2394 IF l_return_status <> 0 THEN
2395 RETURN l_return_status;
2396 END IF;
2397 */
2398 /* Resolve the Component_sequence_ids for updates and deletes */
2399
2400 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2401 SET(component_item_id, effectivity_date,
2402 operation_seq_num, from_end_item_unit_number)
2403 = (SELECT component_item_id,
2404 effectivity_date, operation_seq_num, from_end_item_unit_number
2405 FROM BOM_INVENTORY_COMPONENTS BIC1
2406 WHERE BIC1.component_sequence_id = BICI.component_sequence_id )
2407 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2408 AND UPPER(transaction_type) IN (G_Delete, G_Update)
2409 AND component_sequence_id IS NOT NULL
2410 AND
2411 (
2412 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2413 OR ( p_batch_id = BICI.batch_id )
2414 )
2415 AND EXISTS (SELECT 'x'
2416 FROM BOM_INVENTORY_COMPONENTS BIC2
2417 WHERE BIC2.component_sequence_id = BICI.component_sequence_id );
2418
2419
2420 /* Update the component_sequence_id */
2421 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2422 SET COMPONENT_SEQUENCE_ID
2423 = (SELECT COMPONENT_SEQUENCE_ID
2424 FROM BOM_INVENTORY_COMPONENTS BIC
2425 WHERE BIC.bill_sequence_id = BICI.bill_Sequence_id
2426 AND BIC.component_item_id = BICI.component_item_id
2427 AND BIC.operation_seq_num = BICI.operation_seq_num
2428 AND BIC.effectivity_date = BICI.effectivity_date)
2429 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2430 AND UPPER(transaction_type) IN (G_Update, G_Delete)
2431 AND COMPONENT_SEQUENCE_ID IS NULL
2432 AND bill_sequence_id IS NOT NULL
2433 AND component_item_id IS NOT NULL
2434 AND
2435 (
2436 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2437 OR ( p_batch_id = BICI.batch_id )
2438 )
2439 AND EXISTS (SELECT 'x'
2440 FROM BOM_INVENTORY_COMPONENTS BIC
2441 WHERE BIC.bill_sequence_id = BICI.bill_Sequence_id
2442 AND BIC.component_item_id = BICI.component_item_id
2443 AND BIC.operation_seq_num = BICI.operation_seq_num
2444 AND BIC.effectivity_date = BICI.effectivity_date);
2445
2446
2447
2448 /* Update the From_end_item_id */
2449 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2450 SET From_End_Item_id = (SELECT inventory_item_id
2451 FROM mtl_system_items_kfv mvll
2452 WHERE mvll.concatenated_segments = BICI.From_End_Item
2453 AND mvll.organization_id = BICI.organization_id)
2454 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2455 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2456 AND From_End_Item is not null
2457 AND organization_id is not null
2458 AND
2459 (
2460 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2461 OR ( p_batch_id = BICI.batch_id )
2462 )
2463 AND exists (SELECT 'x'
2464 FROM mtl_system_items_kfv mvll2
2465 WHERE mvll2.concatenated_segments = BICI.From_End_Item
2466 AND mvll2.organization_id = BICI.organization_id);
2467
2468 /* Update the From_end_item_rev_id */
2469 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2470 SET From_end_item_rev_id = (SELECT mrb.revision_id
2471 FROM mtl_item_revisions_b mrb
2472 WHERE mrb.inventory_item_id = BICI.From_End_Item_id
2473 AND mrb.organization_id = BICI.organization_id
2474 AND mrb.revision = BICI.From_end_item_rev_code)
2475 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2476 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2477 AND From_End_Item is not null
2478 AND From_end_item_rev_code is not null
2479 AND organization_id is not null
2480 AND
2481 (
2482 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2483 OR ( p_batch_id = BICI.batch_id )
2484 )
2485 AND exists (SELECT 1
2486 FROM mtl_item_revisions_b mrb
2487 WHERE mrb.inventory_item_id = BICI.From_End_Item_id
2488 AND mrb.organization_id = BICI.organization_id
2489 AND mrb.revision = BICI.From_end_item_rev_code);
2490
2491 /* Update the To_end_item_rev_id */
2492 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2493 SET To_end_item_rev_id = (SELECT mrb.revision_id
2494 FROM mtl_item_revisions_b mrb
2495 WHERE mrb.inventory_item_id = BICI.From_End_Item_id
2496 AND mrb.organization_id = BICI.organization_id
2497 AND mrb.revision = BICI.To_end_item_rev_code)
2498 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2499 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2500 AND From_End_Item is not null
2501 AND To_end_item_rev_code is not null
2502 AND organization_id is not null
2503 AND
2504 (
2505 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2506 OR ( p_batch_id = BICI.batch_id )
2507 )
2508 AND exists (SELECT 1
2509 FROM mtl_item_revisions_b mrb
2510 WHERE mrb.inventory_item_id = BICI.From_End_Item_id
2511 AND mrb.organization_id = BICI.organization_id
2512 AND mrb.revision = BICI.To_end_item_rev_code);
2513
2514 /* Update the component_revision_id */
2515 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2516 SET Component_revision_id = (SELECT mrb.revision_id
2517 FROM mtl_item_revisions_b mrb
2518 WHERE mrb.inventory_item_id = BICI.component_item_id
2519 AND mrb.organization_id = BICI.organization_id
2520 AND mrb.revision = BICI.Component_revision_code)
2521 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2522 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2523 AND component_item_id is not null
2524 AND Component_revision_code is not null
2525 AND organization_id is not null
2526 AND
2527 (
2528 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2529 OR ( p_batch_id = BICI.batch_id )
2530 )
2531 AND exists (SELECT 1
2532 FROM mtl_item_revisions_b mrb
2533 WHERE mrb.inventory_item_id = BICI.component_item_id
2534 AND mrb.organization_id = BICI.organization_id
2535 AND mrb.revision = BICI.Component_revision_code);
2536
2537 /* Update the assembly items pk3 value */
2538 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2539 SET Parent_Revision_Id = (SELECT mrb.revision_id
2540 FROM mtl_item_revisions_b mrb
2541 WHERE mrb.inventory_item_id = BICI.Assembly_item_id
2542 AND mrb.organization_id = BICI.organization_id
2543 AND mrb.revision = BICI.Parent_revision_code)
2544 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2545 AND UPPER(transaction_type) in (G_Delete, G_Update, G_Create)
2546 AND Assembly_item_id is not null
2547 AND Parent_revision_code is not null
2548 AND organization_id is not null
2549 AND
2550 (
2551 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2552 OR ( p_batch_id = BICI.batch_id )
2553 );
2554
2555
2556 /* Update Supply_locator_name */
2557
2558 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2559 SET supply_locator_id = (SELECT inventory_location_id
2560 FROM MTL_ITEM_LOCATIONS_KFV MIL1
2561 WHERE MIL1.concatenated_segments = BICI.location_name
2562 AND MIL1.organization_id = BICI.organization_id)
2563 WHERE process_flag = 1
2564 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
2565 AND location_name is not null
2566 AND organization_id is not null
2567 AND
2568 (
2569 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2570 OR ( p_batch_id = BICI.batch_id )
2571 )
2572 AND exists (SELECT 'x'
2573 FROM MTL_ITEM_LOCATIONS_KFV mil2
2574 WHERE mil2.concatenated_segments = BICI.location_name
2575 AND mil2.organization_id = BICI.organization_id);
2576
2577 UPDATE BOM_INVENTORY_COMPS_INTERFACE BICI
2578 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
2579 transaction_type = upper(transaction_type)
2580 WHERE transaction_id is null
2581 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
2582 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2583 AND
2584 (
2585 ( (p_batch_id IS NULL) AND (BICI.batch_id IS NULL) )
2586 OR ( p_batch_id = BICI.batch_id )
2587 );
2588
2589
2590
2591 -- Reference Designator Changes for PLM Import--
2592 /*
2593 l_return_status := Process_Ref_Degs_Info
2594 (org_id,
2595 all_org,
2596 user_id,
2597 login_id,
2598 prog_appid,
2599 prog_id,
2600 req_id,
2601 err_text,
2602 p_batch_id);
2603 IF l_return_status <> 0 THEN
2604 RETURN l_return_status;
2605 END IF;
2606 */
2607
2608 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2609 SET(bill_sequence_id, component_item_id, effectivity_date,
2610 operation_seq_num, from_end_item_unit_number)
2611 = (SELECT bill_sequence_id, component_item_id,
2612 effectivity_date, operation_seq_num, from_end_item_unit_number
2613 FROM BOM_INVENTORY_COMPONENTS BIC1
2614 WHERE BIC1.component_sequence_id = BRDI.component_sequence_id )
2615 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2616 AND COMPONENT_SEQUENCE_ID is not null
2617 AND
2618 (
2619 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2620 OR ( p_batch_id = BRDI.batch_id )
2621 )
2622 AND exists (SELECT 'x'
2623 FROM BOM_INVENTORY_COMPONENTS BIC2
2624 WHERE BIC2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID );
2625
2626
2627 /* Resolve the Bill sequence ids for updates and deletes */
2628
2629 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2630 SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
2631 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
2632 FROM BOM_BILL_OF_MATERIALS BBM1
2633 WHERE BBM1.bill_sequence_id = BRDI.bill_sequence_id)
2634 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2635 AND bill_sequence_id is not null
2636 AND
2637 (
2638 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2639 OR ( p_batch_id = BRDI.batch_id )
2640 )
2641 AND exists (SELECT 'x'
2642 FROM BOM_BILL_OF_MATERIALS BBM2
2643 WHERE BBM2.bill_sequence_id = BRDI.bill_sequence_id);
2644
2645
2646 /* Update Organization Code using Organization_id
2647 this also needed if Organization_id is given and code is not given*/
2648
2649 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2650 SET organization_code = (SELECT organization_code
2651 FROM MTL_PARAMETERS mp1
2652 WHERE mp1.organization_id = BRDI.organization_id)
2653 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2654 AND organization_id is not null
2655 AND
2656 (
2657 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2658 OR ( p_batch_id = BRDI.batch_id )
2659 )
2660 AND exists (SELECT 'x'
2661 FROM MTL_PARAMETERS mp2
2662 WHERE mp2.organization_id = BRDI.organization_id);
2663
2664
2665
2666 /* Update Organization_ids if organization_code is given org id is null.
2667 Orgnaization_id information is needed in the next steps */
2668
2669 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2670 SET organization_id = (SELECT organization_id
2671 FROM MTL_PARAMETERS mp1
2672 WHERE mp1.organization_code = BRDI.organization_code)
2673 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2674 AND organization_id is null
2675 AND organization_code is not null
2676 AND
2677 (
2678 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2679 OR ( p_batch_id = BRDI.batch_id )
2680 );
2681
2682
2683
2684 /* Update Assembly Item name */
2685
2686 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2687 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
2688 FROM MTL_SYSTEM_ITEMS_KFV mvl1
2689 WHERE mvl1.inventory_item_id = BRDI.assembly_item_id
2690 AND mvl1.organization_id = BRDI.organization_id)
2691 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2692 AND assembly_item_id is not null
2693 AND organization_id is not null
2694 AND
2695 (
2696 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2697 OR ( p_batch_id = BRDI.batch_id )
2698 )
2699 AND exists (SELECT 'x'
2700 FROM mtl_system_items mvl12
2701 WHERE mvl12.inventory_item_id = BRDI.assembly_item_id
2702 AND mvl12.organization_id = BRDI.organization_id);
2703
2704 /* Update the Assembly_item_id */
2705 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2706 SET Assembly_item_id = (SELECT inventory_item_id
2707 FROM mtl_system_items_kfv mvll
2708 WHERE mvll.concatenated_segments = BRDI.Assembly_item_number
2709 AND mvll.organization_id = BRDI.organization_id)
2710 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2711 AND Assembly_item_number is not null
2712 AND organization_id is not null
2713 AND Assembly_item_id is null
2714 AND
2715 (
2716 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2717 OR ( p_batch_id = BRDI.batch_id )
2718 );
2719
2720 /* Update Component Item name */
2721 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2722 SET COMPONENT_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
2723 FROM MTL_SYSTEM_ITEMS_KFV mvl1
2724 WHERE mvl1.inventory_item_id = BRDI.component_item_id
2725 AND mvl1.organization_id = BRDI.organization_id)
2726 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2727 AND COMPONENT_ITEM_ID is not null
2728 AND organization_id is not null
2729 AND
2730 (
2731 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2732 OR ( p_batch_id = BRDI.batch_id )
2733 )
2734 AND exists (SELECT 'x'
2735 FROM mtl_system_items mvl12
2736 WHERE mvl12.inventory_item_id = BRDI.component_item_id
2737 AND mvl12.organization_id = BRDI.organization_id);
2738
2739
2740 /* Update the Component_item_id */
2741 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2742 SET Component_item_id = (SELECT inventory_item_id
2743 FROM mtl_system_items_kfv mvll
2744 WHERE mvll.concatenated_segments = BRDI.Component_item_number
2745 AND mvll.organization_id = BRDI.organization_id)
2746 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2747 AND Component_item_number is not null
2748 AND organization_id is not null
2749 AND component_item_id is null
2750 AND
2751 (
2752 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2753 OR ( p_batch_id = BRDI.batch_id )
2754 );
2755
2756 /* Set the Bill Seqeunce Ids */
2757 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2758 SET bill_sequence_id = (SELECT bill_sequence_id
2759 FROM bom_structures_b bom
2760 WHERE bom.assembly_item_id = BRDI.assembly_item_id
2761 AND bom.organization_id = BRDI.organization_id
2762 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BRDI.alternate_bom_designator,FND_API.G_MISS_CHAR))
2763 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2764 AND upper(transaction_type) in (G_Delete, G_Update, 'SYNC', G_Create)
2765 AND assembly_item_id is not null
2766 AND organization_id is not null
2767 AND bill_sequence_id is null
2768 AND
2769 (
2770 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2771 OR ( p_batch_id = BRDI.batch_id )
2772 );
2773
2774 /* Update the component_sequence_id */
2775 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2776 SET COMPONENT_SEQUENCE_ID
2777 = (SELECT COMPONENT_SEQUENCE_ID
2778 FROM bom_components_b BIC
2779 WHERE BIC.bill_sequence_id = BRDI.bill_Sequence_id
2780 AND BIC.component_item_id = BRDI.component_item_id
2781 AND BIC.operation_seq_num = BRDI.operation_seq_num
2782 AND BIC.effectivity_date = BRDI.effectivity_date)
2783 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2784 AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
2785 AND COMPONENT_SEQUENCE_ID IS NULL
2786 AND bill_sequence_id IS NOT NULL
2787 AND component_item_id IS NOT NULL
2788 AND
2789 (
2790 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2791 OR ( p_batch_id = BRDI.batch_id )
2792 );
2793
2794
2795 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2796 SET Assembly_Item_Revision_Id = (SELECT mrb.revision_id
2797 FROM mtl_item_revisions_b mrb
2798 WHERE mrb.inventory_item_id = BRDI.Assembly_item_id
2799 AND mrb.organization_id = BRDI.organization_id
2800 AND mrb.revision = BRDI.Assembly_Item_Revision_Code)
2801 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2802 AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
2803 AND Assembly_item_id IS NOT NULL
2804 AND Assembly_Item_Revision_Code IS NOT NULL
2805 AND Organization_Id IS NOT NULL
2806 AND
2807 (
2808 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2809 OR ( p_batch_id = BRDI.batch_id )
2810 );
2811
2812 /*Update the transaction_types */
2813 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2814 SET Transaction_Type = G_Update
2815 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2816 AND transaction_type = 'SYNC'
2817 AND COMPONENT_SEQUENCE_ID is not null
2818 AND
2819 (
2820 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2821 OR ( p_batch_id = BRDI.batch_id )
2822 )
2823 AND exists (SELECT 'x'
2824 FROM BOM_REFERENCE_DESIGNATORS BRDI2
2825 WHERE BRDI2.COMPONENT_SEQUENCE_ID = BRDI.COMPONENT_SEQUENCE_ID
2826 AND BRDI2.COMPONENT_REFERENCE_DESIGNATOR = BRDI.COMPONENT_REFERENCE_DESIGNATOR
2827 AND NVL(BRDI2.ACD_TYPE, 1) = NVL(BRDI.ACD_TYPE, 1) );
2828
2829 /*Update the transaction_types */
2830 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2831 SET Transaction_Type = G_Create
2832 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2833 AND transaction_type = 'SYNC'
2834 AND
2835 (
2836 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2837 OR ( p_batch_id = BRDI.batch_id )
2838 );
2839
2840 /* Assign transaction ids */
2841
2842 UPDATE BOM_REF_DESGS_INTERFACE BRDI
2843 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
2844 transaction_type = upper(transaction_type)
2845 WHERE transaction_id is null
2846 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
2847 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2848 AND (all_org = 1
2849 OR
2850 (all_org = 2 AND organization_id = org_id))
2851 AND
2852 (
2853 ( (p_batch_id IS NULL) AND (BRDI.batch_id IS NULL) )
2854 OR ( p_batch_id = BRDI.batch_id )
2855 );
2856
2857
2858 /*l_return_status := Process_Sub_Comps_Info
2859 (org_id,
2860 all_org,
2861 user_id,
2862 login_id,
2863 prog_appid,
2864 prog_id,
2865 req_id,
2866 err_text,
2867 p_batch_id);
2868 IF l_return_status <> 0 THEN
2869 RETURN l_return_status;
2870 END IF;
2871 */
2872
2873 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
2874 SET(bill_sequence_id, component_item_id, effectivity_date,
2875 operation_seq_num, from_end_item_unit_number)
2876 = (select bill_sequence_id, component_item_id,
2877 EFFECTIVITY_DATE, OPERATION_SEQ_NUM, FROM_END_ITEM_UNIT_NUMBER
2878 FROM BOM_INVENTORY_COMPONENTS BIC1
2879 WHERE BIC1.COMPONENT_SEQUENCE_ID = BSCI.component_sequence_id )
2880 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2881 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
2882 AND COMPONENT_SEQUENCE_ID is not null
2883 AND
2884 (
2885 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2886 OR ( p_batch_id = BSCI.batch_id )
2887 )
2888 AND exists (SELECT 'x'
2889 FROM BOM_INVENTORY_COMPONENTS BIC2
2890 WHERE BIC2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID );
2891
2892
2893 /* Resolve the Bill sequence ids for updates and deletes */
2894
2895 UPDATE bom_sub_comps_interface BSCI
2896 SET(assembly_item_id, organization_id, ALTERNATE_BOM_DESIGNATOR)
2897 = (SELECT assembly_item_id, organization_id , alternate_bom_designator
2898 FROM BOM_BILL_OF_MATERIALS BBM1
2899 WHERE BBM1.bill_sequence_id = BSCI.bill_sequence_id)
2900 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2901 AND bill_sequence_id is not null
2902 AND
2903 (
2904 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2905 OR ( p_batch_id = BSCI.batch_id )
2906 )
2907 AND exists (SELECT 'x'
2908 FROM BOM_BILL_OF_MATERIALS BBM2
2909 WHERE BBM2.bill_sequence_id = BSCI.bill_sequence_id);
2910
2911
2912 /* Update Organization Code using Organization_id
2913 this also needed if Organization_id is given and code is not given*/
2914
2915 UPDATE bom_sub_comps_interface BSCI
2916 SET organization_code = (SELECT organization_code
2917 FROM MTL_PARAMETERS mp1
2918 WHERE mp1.organization_id = BSCI.organization_id)
2919 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2920 AND organization_id is not null
2921 AND
2922 (
2923 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2924 OR ( p_batch_id = BSCI.batch_id )
2925 )
2926 AND exists (SELECT 'x'
2927 FROM MTL_PARAMETERS mp2
2928 WHERE mp2.organization_id = BSCI.organization_id);
2929
2930
2931
2932 /* Update Organization_ids if organization_code is given org id is null.
2933 Orgnaization_id information is needed in the next steps */
2934
2935 UPDATE bom_sub_comps_interface BSCI
2936 SET organization_id = (SELECT organization_id
2937 FROM MTL_PARAMETERS mp1
2938 WHERE mp1.organization_code = BSCI.organization_code)
2939 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2940 AND organization_id is null
2941 AND organization_code is not null
2942 AND
2943 (
2944 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2945 OR ( p_batch_id = BSCI.batch_id )
2946 );
2947
2948
2949
2950 /* Update Assembly Item name */
2951
2952 UPDATE bom_sub_comps_interface BSCI
2953 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
2954 FROM MTL_SYSTEM_ITEMS_KFV mvl1
2955 WHERE mvl1.inventory_item_id = BSCI.assembly_item_id
2956 AND mvl1.organization_id = BSCI.organization_id)
2957 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2958 AND assembly_item_id is not null
2959 AND organization_id is not null
2960 AND
2961 (
2962 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2963 OR ( p_batch_id = BSCI.batch_id )
2964 )
2965 AND exists (SELECT 'x'
2966 FROM mtl_system_items mvl12
2967 WHERE mvl12.inventory_item_id = BSCI.assembly_item_id
2968 AND mvl12.organization_id = BSCI.organization_id);
2969
2970 /* Update the Assembly_item_id */
2971 UPDATE bom_sub_comps_interface BSCI
2972 SET Assembly_item_id = (SELECT inventory_item_id
2973 FROM mtl_system_items_kfv mvll
2974 WHERE mvll.concatenated_segments = BSCI.Assembly_item_number
2975 AND mvll.organization_id = BSCI.organization_id)
2976 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2977 AND Assembly_item_number is not null
2978 AND organization_id is not null
2979 AND assembly_item_id is null
2980 AND
2981 (
2982 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2983 OR ( p_batch_id = BSCI.batch_id )
2984 );
2985
2986 /* Update Component Item name */
2987 UPDATE bom_sub_comps_interface BSCI
2988 SET COMPONENT_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
2989 FROM MTL_SYSTEM_ITEMS_KFV mvl1
2990 WHERE mvl1.inventory_item_id = BSCI.component_item_id
2991 AND mvl1.organization_id = BSCI.organization_id)
2992 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
2993 AND COMPONENT_ITEM_ID is not null
2994 AND organization_id is not null
2995 AND
2996 (
2997 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
2998 OR ( p_batch_id = BSCI.batch_id )
2999 )
3000 AND exists (SELECT 'x'
3001 FROM mtl_system_items mvl12
3002 WHERE mvl12.inventory_item_id = BSCI.component_item_id
3003 AND mvl12.organization_id = BSCI.organization_id);
3004
3005
3006 /* Update the Component_item_id */
3007 UPDATE bom_sub_comps_interface BSCI
3008 SET Component_item_id = (SELECT inventory_item_id
3009 FROM mtl_system_items_kfv mvll
3010 WHERE mvll.concatenated_segments = BSCI.Component_item_number
3011 AND mvll.organization_id = BSCI.organization_id)
3012 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3013 AND Component_item_number is not null
3014 AND organization_id is not null
3015 AND component_item_id is null
3016 AND
3017 (
3018 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3019 OR ( p_batch_id = BSCI.batch_id )
3020 );
3021
3022 /* Set the Bill Seqeunce Ids */
3023 UPDATE bom_sub_comps_interface BSCI
3024 SET bill_sequence_id = (SELECT bill_sequence_id
3025 FROM bom_bill_of_materials bom
3026 WHERE bom.assembly_item_id = BSCI.assembly_item_id
3027 AND bom.organization_id = BSCI.organization_id
3028 AND NVL(bom.alternate_bom_designator,FND_API.G_MISS_CHAR) = NVL(BSCI.alternate_bom_designator,FND_API.G_MISS_CHAR))
3029 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3030 AND upper(transaction_type) in (G_Delete, G_Update, 'SYNC', G_Create)
3031 AND assembly_item_id is not null
3032 AND organization_id is not null
3033 AND bill_sequence_id is null
3034 AND
3035 (
3036 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3037 OR ( p_batch_id = BSCI.batch_id )
3038 );
3039
3040 /* Update the component_sequence_id */
3041 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
3042 SET COMPONENT_SEQUENCE_ID
3043 = (SELECT COMPONENT_SEQUENCE_ID
3044 FROM BOM_INVENTORY_COMPONENTS BIC
3045 WHERE BIC.bill_sequence_id = BSCI.bill_Sequence_id
3046 AND BIC.component_item_id = BSCI.component_item_id
3047 AND BIC.operation_seq_num = BSCI.operation_seq_num
3048 AND BIC.effectivity_date = BSCI.effectivity_date)
3049 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3050 AND UPPER(transaction_type) IN (G_Update, G_Delete, 'SYNC', G_Create)
3051 AND COMPONENT_SEQUENCE_ID IS NULL
3052 AND bill_sequence_id IS NOT NULL
3053 AND component_item_id IS NOT NULL
3054 AND
3055 (
3056 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3057 OR ( p_batch_id = BSCI.batch_id )
3058 );
3059
3060
3061 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
3062 SET SUBSTITUTE_COMP_NUMBER = (SELECT concatenated_segments
3063 FROM MTL_SYSTEM_ITEMS_KFV mvl1
3064 WHERE mvl1.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
3065 and mvl1.organization_id = BSCI.organization_id)
3066 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3067 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
3068 AND SUBSTITUTE_COMPONENT_ID is not null
3069 AND organization_id is not null
3070 AND
3071 (
3072 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3073 OR ( p_batch_id = BSCI.batch_id )
3074 )
3075 AND exists (SELECT 'x'
3076 FROM mtl_system_items mvl12
3077 WHERE mvl12.inventory_item_id = BSCI.SUBSTITUTE_COMPONENT_ID
3078 AND mvl12.organization_id = BSCI.organization_id);
3079
3080 --Update Sub Comp Number If id is given
3081
3082 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
3083 SET SUBSTITUTE_COMPONENT_ID = (SELECT inventory_item_id
3084 FROM MTL_SYSTEM_ITEMS_KFV mvl1
3085 WHERE mvl1.concatenated_segments = BSCI.SUBSTITUTE_COMP_NUMBER
3086 and mvl1.organization_id = BSCI.organization_id)
3087 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3088 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
3089 AND substitute_comp_number is not null
3090 AND organization_id is not null
3091 AND substitute_component_id is null
3092 AND
3093 (
3094 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3095 OR ( p_batch_id = BSCI.batch_id )
3096 );
3097
3098 --Update New Sub Comp Number
3099
3100 UPDATE BOM_SUB_COMPS_INTERFACE BSCI
3101 SET NEW_SUB_COMP_NUMBER = (SELECT CONCATENATED_SEGMENTS
3102 FROM MTL_SYSTEM_ITEMS_KFV mvl1
3103 WHERE mvl1.inventory_item_id = BSCI.NEW_SUB_COMP_ID
3104 and mvl1.organization_id = BSCI.organization_id)
3105 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3106 AND upper(transaction_type) in (G_Create, G_Delete, G_Update,'SYNC')
3107 AND NEW_SUB_COMP_ID is not null
3108 AND organization_id is not null
3109 AND
3110 (
3111 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3112 OR ( p_batch_id = BSCI.batch_id )
3113 )
3114 AND exists (SELECT 'x'
3115 FROM mtl_system_items mvl12
3116 WHERE mvl12.inventory_item_id = BSCI.NEW_SUB_COMP_ID
3117 and mvl12.organization_id = BSCI.organization_id);
3118
3119
3120
3121
3122 /*Update the transaction_types */
3123 UPDATE bom_sub_comps_interface BSCI
3124 SET Transaction_Type = G_Update
3125 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3126 AND transaction_type = 'SYNC'
3127 AND COMPONENT_SEQUENCE_ID is not null
3128 AND
3129 (
3130 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3131 OR ( p_batch_id = BSCI.batch_id )
3132 )
3133 AND exists (SELECT 'x'
3134 FROM BOM_SUBSTITUTE_COMPONENTS BSCI2
3135 WHERE BSCI2.COMPONENT_SEQUENCE_ID = BSCI.COMPONENT_SEQUENCE_ID
3136 AND NVL(BSCI2.ACD_TYPE, 1) = NVL(BSCI.ACD_TYPE, 1) );
3137
3138 /*Update the transaction_types */
3139 UPDATE bom_sub_comps_interface BSCI
3140 SET Transaction_Type = G_Create
3141 WHERE (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3142 AND transaction_type = 'SYNC'
3143 AND
3144 (
3145 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3146 OR ( p_batch_id = BSCI.batch_id )
3147 );
3148
3149 /* Assign transaction ids */
3150
3151 UPDATE bom_sub_comps_interface BSCI
3152 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
3153 transaction_type = upper(transaction_type)
3154 WHERE transaction_id is null
3155 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
3156 AND (process_flag = 1 or process_flag = 5) --CM Changes for Structure Import
3157 AND (all_org = 1
3158 OR
3159 (all_org = 2 AND organization_id = org_id))
3160 AND
3161 (
3162 ( (p_batch_id IS NULL) AND (BSCI.batch_id IS NULL) )
3163 OR ( p_batch_id = BSCI.batch_id )
3164 );
3165
3166
3167
3168 l_return_status := Process_Comp_Ops_Info
3169 (org_id,
3170 all_org,
3171 user_id,
3172 login_id,
3173 prog_appid,
3174 prog_id,
3175 req_id,
3176 err_text,
3177 p_batch_id);
3178 IF l_return_status <> 0 THEN
3179 RETURN l_return_status;
3180 END IF;
3181
3182 RETURN l_return_status;
3183 END;
3184
3185 end Bom_Open_Interface_Utl;
[Home] [Help]