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