[Home] [Help]
PACKAGE BODY: APPS.BOM_RTG_OI_UTIL
Source
1 PACKAGE BODY BOM_RTG_OI_UTIL AS
2 /* $Header: BOMUROIB.pls 120.3.12000000.2 2007/04/11 09:55:50 shchandr ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUROIB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_RTG_OI_UTIL
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 13-DEC-02 Deepak Jebar Initial Creation
21 -- 15-JUN-05 Abhishek Bhardwaj Added Batch Id
22 --
23 ***************************************************************************/
24
25 G_Create constant varchar2(10) := 'CREATE'; -- transaction type
26 G_Update constant varchar2(10) := 'UPDATE'; -- transaction type
27 G_Delete constant varchar2(10) := 'DELETE'; -- transaction type
28 G_RtgDelEntity constant varchar2(30) := 'BOM_OP_ROUTINGS_INTERFACE';
29 G_OprDelEntity constant varchar2(30) := 'BOM_OP_SEQUENCES_INTERFACE';
30
31 /*--------------------------Process_Header_Info------------------------------
32
33 NAME
34 Process_Rtg_header
35 DESCRIPTION
36 Populate the user-friendly columns to routing record in the interface table
37 REQUIRES
38
39 MODIFIES
40 BOM_OP_ROUTINGS_INTERFACE
41 MTL_INTERFACE_ERRORS
42 RETURNS
43 0 if successful
44 SQLCODE if unsuccessful
45 NOTES
46 -----------------------------------------------------------------------------*/
47
48 FUNCTION Process_Rtg_header (
49 org_id NUMBER,
50 all_org NUMBER,
51 user_id NUMBER,
52 login_id NUMBER,
53 prog_appid NUMBER,
54 prog_id NUMBER,
55 req_id NUMBER,
56 err_text IN OUT NOCOPY VARCHAR2,
57 p_batch_id NUMBER
58 )
59 return INTEGER
60 IS
61 stmt_num NUMBER := 0;
62 msg_name1 varchar2(30);
63 msg_name2 varchar2(30);
64 msg_text1 varchar2(2000);
65 msg_text2 varchar2(2000);
66
67 BEGIN
68 stmt_num := 1;
69 /* Resolve the routing sequence ids for updates and deletes */
70
71 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
72 SET(assembly_item_id, organization_id, alternate_routing_designator)
73 = (SELECT assembly_item_id, organization_id , alternate_routing_designator
74 FROM BOM_OPERATIONAL_ROUTINGS BOR1
75 WHERE BOR1.routing_sequence_id = BORI.routing_sequence_id)
76 WHERE process_flag = 1
77 AND upper(transaction_type) in (G_Delete, G_Update)
78 AND routing_sequence_id is not null
79 AND
80 (
81 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
82 OR ( p_batch_id = BORI.batch_id )
83 )
84 AND exists (SELECT 'x'
85 FROM BOM_OPERATIONAL_ROUTINGS BOR2
86 WHERE BOR2.routing_sequence_id = BORI.routing_sequence_id);
87
88 stmt_num := 2;
89
90 /* Update Organization Code using Organization_id
91 this also needed if Organization_id is given and code is not given*/
92
93 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
94 SET organization_code = (SELECT organization_code
95 FROM MTL_PARAMETERS MP1
96 WHERE mp1.organization_id = BORI.organization_id)
97 WHERE process_flag = 1
98 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
99 AND organization_id is not null
100 AND organization_code is NULL -- Bug #3411601
101 AND
102 (
103 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
104 OR ( p_batch_id = BORI.batch_id )
105 )
106 AND exists (SELECT 'x'
107 FROM MTL_PARAMETERS MP2
108 WHERE mp2.organization_id = BORI.organization_id);
109
110 stmt_num := 3;
111 /* Update Organization_ids if organization code is given org id is null.
112 Orgnaization_id information is needed in the next steps */
113
114 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
115 SET organization_id = (SELECT organization_id
116 FROM MTL_PARAMETERS mp1
117 WHERE mp1.organization_code = BORI.organization_code)
118 WHERE process_flag = 1
119 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
120 AND organization_id is null
121 AND organization_code is not null
122 AND
123 (
124 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
125 OR ( p_batch_id = BORI.batch_id )
126 );
127
128 stmt_num := 4;
129 /* Update Assembly Item name */
130
131 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
132 SET assembly_item_number = (SELECT concatenated_segments
133 FROM MTL_SYSTEM_ITEMS_KFV mvl1
134 WHERE mvl1.inventory_item_id = BORI.assembly_item_id
135 and mvl1.organization_id = BORI.organization_id)
136 WHERE process_flag = 1
137 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
138 AND assembly_item_id is not null
139 AND organization_id is not null
140 AND
141 (
142 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
143 OR ( p_batch_id = BORI.batch_id )
144 )
145 AND exists (SELECT 'x'
146 FROM MTL_SYSTEM_ITEMS mvl2
147 WHERE mvl2.inventory_item_id = BORI.assembly_item_id
148 and mvl2.organization_id = BORI.organization_id);
149
150
151
152 stmt_num := 5;
153 /* Assign transaction ids */
154
155 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
156 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
157 WHERE transaction_id is null
158 --Bug 3411601 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
159 AND upper(transaction_type) in (G_Create, G_Update, G_Delete, 'NO_OP')
160 AND process_flag = 1
161 AND
162 (
163 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
164 OR ( p_batch_id = BORI.batch_id )
165 )
166 AND (all_org = 1
167 OR
168 (all_org = 2 AND organization_id = org_id));
169
170 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
171 SET transaction_type = upper(transaction_type)
172 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
173 AND process_flag = 1
174 AND
175 (
176 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
177 OR ( p_batch_id = BORI.batch_id )
178 )
179 AND (all_org = 1
180 OR
181 (all_org = 2 AND organization_id = org_id));
182
183
184 stmt_num := 6;
185 /* Assign Common Assembly Item id if common_routing_sequence_id is given
186 and a routing exists with that routing_sequence_id */
187
188 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
189 SET(common_assembly_item_id)
190 = (SELECT assembly_item_id
191 FROM BOM_OPERATIONAL_ROUTINGS BOR1
192 WHERE BOR1.routing_sequence_id = BORI.common_routing_sequence_id)
193 WHERE process_flag = 1
194 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
195 AND common_routing_sequence_id is not null
196 AND
197 (
198 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
199 OR ( p_batch_id = BORI.batch_id )
200 )
201 AND exists (SELECT 'x'
202 FROM BOM_OPERATIONAL_ROUTINGS BOR2
203 WHERE BOR2.routing_sequence_id = BORI.common_routing_sequence_id);
204
205 stmt_num :=7;
206
207 /* Update Assembly Item name */
208
209 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
210 SET common_item_number = (SELECT concatenated_segments
211 FROM MTL_SYSTEM_ITEMS_KFV mvl1
212 WHERE mvl1.inventory_item_id = BORI.common_assembly_item_id
213 and mvl1.organization_id = BORI.organization_id)
214 WHERE process_flag = 1
215 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
216 AND common_assembly_item_id is not null
217 AND organization_id is not null
218 AND
219 (
220 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
221 OR ( p_batch_id = BORI.batch_id )
222 )
223 AND exists (SELECT 'x'
224 FROM MTL_SYSTEM_ITEMS mvl2
225 WHERE mvl2.inventory_item_id = BORI.common_assembly_item_id
226 and mvl2.organization_id = BORI.organization_id);
227
228
229 stmt_num := 8 ;
230 /* Update the line code from line_id */
231 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
232 SET line_code = (SELECT line_code
233 FROM WIP_LINES wl1
234 WHERE wl1.LINE_ID = BORI.LINE_ID -- Bug Fix 3782414
235 AND wl1.organization_id = BORI.organization_id)
236 WHERE process_flag = 1
237 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
238 AND line_id is not null
239 AND organization_id is not null
240 AND
241 (
242 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
243 OR ( p_batch_id = BORI.batch_id )
244 )
245 AND exists (SELECT 'x'
246 FROM WIP_LINES wl2
247 WHERE wl2.organization_id = BORI.organization_id
248 AND nvl(wl2.disable_date, trunc(sysdate) + 1) > trunc(sysdate));
249
250 stmt_num := 9;
251 /* Update the delete_group_name from bom_interface_delete_groups */
252 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
253 SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
254 = (SELECT DELETE_GROUP_NAME, DESCRIPTION
255 FROM bom_interface_delete_groups
256 Where upper(entity_name) = G_RtgDelEntity
257 And rownum = 1)
258 WHERE process_flag = 1
259 AND upper(transaction_type) in (G_Delete)
260 AND organization_id is not null
261 AND delete_group_name is null
262 AND
263 (
264 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
265 OR ( p_batch_id = BORI.batch_id )
266 )
267 AND exists (SELECT 'x'
268 FROM bom_interface_delete_groups
269 Where upper(entity_name) = G_RtgDelEntity
270 );
271
272 stmt_num := 10;
273 /* Update Supply_locator_name */
274
275 UPDATE BOM_OP_ROUTINGS_INTERFACE BORI
276 SET location_name = (SELECT concatenated_segments
277 FROM MTL_ITEM_LOCATIONS_KFV MIL1
278 WHERE MIL1.inventory_location_id = BORI.COMPLETION_LOCATOR_ID
279 and MIL1.organization_id = BORI.organization_id)
280 WHERE process_flag = 1
281 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
282 AND completion_locator_id is not null
283 AND organization_id is not null
284 AND
285 (
286 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
287 OR ( p_batch_id = BORI.batch_id )
288 )
289 AND exists (SELECT 'x'
290 FROM MTL_ITEM_LOCATIONS_KFV mil2
291 WHERE mil2.INVENTORY_LOCATION_ID = BORI.completion_locator_id
292 and mil2.organization_id = BORI.organization_id);
293
294
295 stmt_num := 11;
296 /* Load rows from routing interface into revisions interface*/
297 -- Bug 5970070. Adding 1 min to the effectivity date and implementation date
298 -- as the revision entered by user should be created after default revision.
299 INSERT into MTL_RTG_ITEM_REVS_INTERFACE
300 (INVENTORY_ITEM_NUMBER,
301 ORGANIZATION_CODE,
302 PROCESS_REVISION,
303 EFFECTIVITY_DATE,
304 IMPLEMENTATION_DATE,
305 PROCESS_FLAG,
306 TRANSACTION_TYPE,
307 LAST_UPDATE_DATE,
308 LAST_UPDATED_BY,
309 CREATION_DATE,
310 CREATED_BY,
311 LAST_UPDATE_LOGIN,
312 REQUEST_ID,
313 PROGRAM_APPLICATION_ID,
314 PROGRAM_ID,
315 PROGRAM_UPDATE_DATE,
316 BATCH_ID
317 )
318 select
319 assembly_item_number,
320 Organization_Code,
321 upper(PROCESS_REVISION),
322 sysdate + 1/1440,
323 sysdate + 1/1440,
324 1,
325 G_Create,
326 NVL(LAST_UPDATE_DATE, SYSDATE),
327 NVL(LAST_UPDATED_BY, user_id),
328 NVL(CREATION_DATE,SYSDATE),
329 NVL(CREATED_BY, user_id),
330 NVL(LAST_UPDATE_LOGIN, user_id),
331 NVL(REQUEST_ID, req_id),
332 NVL(PROGRAM_APPLICATION_ID, prog_appid),
333 NVL(PROGRAM_ID, prog_id),
334 NVL(PROGRAM_UPDATE_DATE, sysdate),
335 BATCH_ID
336 FROM BOM_OP_ROUTINGS_INTERFACE
337 WHERE process_flag = 1
341 ( (p_batch_id is null) AND (batch_id is null) )
338 AND transaction_type = G_Create
339 AND
340 (
342 OR ( p_batch_id = batch_id )
343 )
344 AND (all_org = 1
345 OR
346 (all_org = 2 AND organization_id = org_id))
347 AND process_revision is not null;
348
349 COMMIT;
350 stmt_num := 12;
351
352 /* Update the interface records with process_flag 3 and insert into
353 MTL_INTERFACE_ERRORS if Item number or Organization_code is missing*/
354
355 msg_name1 := 'BOM_ORG_ID_MISSING';
356 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
357 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
358 msg_text1 := FND_MESSAGE.GET;
359 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
360 msg_text2 := FND_MESSAGE.GET;
361
362 INSERT INTO MTL_INTERFACE_ERRORS
363 (
364 TRANSACTION_ID,
365 UNIQUE_ID,
366 ORGANIZATION_ID,
367 COLUMN_NAME,
368 TABLE_NAME,
369 MESSAGE_NAME,
370 ERROR_MESSAGE,
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 )
381 Select
382 BORI.transaction_id,
383 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
384 Null,
385 null,
386 'BOM_OP_ROUTINGS_INTERFACE',
387 decode ( BORI.Organization_code, null, msg_name1,msg_name2),
388 decode ( BORI.Organization_code, null, msg_text1,msg_text2),
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 req_id,
395 NVL(PROGRAM_APPLICATION_ID, prog_appid),
396 NVL(PROGRAM_ID, prog_id),
397 NVL(PROGRAM_UPDATE_DATE, sysdate)
398 from BOM_OP_ROUTINGS_INTERFACE BORI
399 where (organization_code is null or assembly_item_number is null)
400 and transaction_id is not null
401 and process_flag =1
402 and
403 (
404 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
405 OR ( p_batch_id = batch_id )
406 )
407 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
408
409 Update BOM_OP_ROUTINGS_INTERFACE BORI
410 set process_flag = 3
411 where (assembly_item_number is null or Organization_code is null)
412 and transaction_id is not null
413 and process_flag = 1
414 and
415 (
416 ( (p_batch_id is null) AND (batch_id is null) )
417 OR ( p_batch_id = batch_id )
418 )
419 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
420
421 Commit;
422
423 return(0);
424
425
426 EXCEPTION
427 WHEN others THEN
428 err_text := 'Bom_Rtg_OI_Util(Process_Rtg_header-'||stmt_num||') '||substrb(SQLERRM,1,1000);
429 RETURN(SQLCODE);
430
431 END Process_Rtg_header;
432
433
434 /*--------------------------Process_Op_Seqs------------------------------
435
436 NAME
437 Process_Op_Seqs
438 DESCRIPTION
439 Populate the user-friendly columns to operation records in the interface table
440 REQUIRES
441
442 MODIFIES
443 BOM_OP_SEQUENCES_INTERFACE
444 MTL_INTERFACE_ERRORS
445 RETURNS
446 0 if successful
447 SQLCODE if unsuccessful
448 NOTES
449 -----------------------------------------------------------------------------*/
450
451 FUNCTION Process_Op_Seqs (
452 org_id NUMBER,
453 all_org NUMBER ,
454 user_id NUMBER,
455 login_id NUMBER,
456 prog_appid NUMBER,
457 prog_id NUMBER,
458 req_id NUMBER,
459 err_text IN OUT NOCOPY VARCHAR2,
460 p_batch_id NUMBER
461 )
462 return INTEGER
463 IS
464 stmt_num NUMBER := 0;
465 msg_name1 varchar2(30);
466 msg_name2 varchar2(30);
467 msg_text1 varchar2(2000);
468 msg_text2 varchar2(2000);
469
470 BEGIN
471 stmt_num := 1;
472 /* Resolve the routing_sequence_ids for updates and deletes */
473
474 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
475 SET(routing_sequence_id, effectivity_date,
476 operation_seq_num, operation_type)
477 = (SELECT routing_sequence_id, effectivity_date, operation_seq_num, operation_type
478 FROM BOM_OPERATION_SEQUENCES BOS1
479 WHERE BOS1.operation_sequence_id = BOSI.operation_sequence_id)
480 WHERE process_flag = 1
481 AND upper(transaction_type) in (G_Delete, G_Update)
482 AND operation_sequence_id is not null
483 AND
484 (
485 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
486 OR ( p_batch_id = BOSI.batch_id )
487 )
488 AND exists (SELECT 'x'
489 FROM BOM_OPERATION_SEQUENCES BOS2
493 stmt_num := 2;
490 WHERE BOS2.operation_sequence_id = BOSI.operation_sequence_id );
491
492
494 /* Resolve the assembly item ids */
495
496 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
497 SET(assembly_item_id, organization_id, alternate_routing_designator)
498 = (SELECT assembly_item_id, organization_id , alternate_routing_designator
499 FROM BOM_OPERATIONAL_ROUTINGS BOR1
500 WHERE BOR1.routing_sequence_id = BOSI.routing_sequence_id)
501 WHERE process_flag = 1
502 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
503 AND routing_sequence_id is not null
504 AND
505 (
506 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
507 OR ( p_batch_id = BOSI.batch_id )
508 )
509 AND exists (SELECT 'x'
510 FROM BOM_OPERATIONAL_ROUTINGS BOR2
511 WHERE BOR2.routing_sequence_id = BOSI.routing_sequence_id);
512
513
514 stmt_num := 3;
515 /* Update Organization Code using Organization_id
516 this also needed if Organization_id is given and code is not given*/
517
518 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
519 SET organization_code = (SELECT organization_code
520 FROM MTL_PARAMETERS mp1
521 WHERE mp1.organization_id = BOSI.organization_id)
522 WHERE process_flag = 1
523 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
524 AND organization_id is not null
525 AND
526 (
527 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
528 OR ( p_batch_id = BOSI.batch_id )
529 )
530 AND exists (SELECT 'x'
531 FROM MTL_PARAMETERS mp2
532 WHERE mp2.organization_id = BOSI.organization_id);
533
534
535
536 stmt_num := 4;
537 /* Update Organization_ids if organization_code is given org id is null.
538 Orgnaization_id information is needed in the next steps */
539
540 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
541 SET organization_id = (SELECT organization_id
542 FROM MTL_PARAMETERS mp1
543 WHERE mp1.organization_code = BOSI.organization_code)
544 WHERE process_flag = 1
545 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
546 AND organization_id is null
547 AND organization_code is not null
548 AND
549 (
550 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
551 OR ( p_batch_id = BOSI.batch_id )
552 );
553
554
555
556 stmt_num := 5;
557 /* Update Assembly Item name */
558
559 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
560 SET assembly_item_number = (SELECT concatenated_segments
561 FROM MTL_SYSTEM_ITEMS_KFV mvl1
562 WHERE mvl1.inventory_item_id = BOSI.assembly_item_id
563 and mvl1.organization_id = BOSI.organization_id)
564 WHERE process_flag = 1
565 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
566 AND assembly_item_id is not null
567 AND organization_id is not null
568 AND
569 (
570 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
571 OR ( p_batch_id = BOSI.batch_id )
572 )
573 AND exists (SELECT 'x'
574 FROM MTL_SYSTEM_ITEMS mvl2
575 WHERE mvl2.inventory_item_id = BOSI.assembly_item_id
576 and mvl2.organization_id = BOSI.organization_id);
577
578
579 stmt_num := 6;
580 /* Assign transaction ids */
581
582 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
583 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
584 WHERE transaction_id is null
585 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
586 AND process_flag = 1
587 AND
588 (
589 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
590 OR ( p_batch_id = BOSI.batch_id )
591 )
592 AND (all_org = 1
593 OR
594 (all_org = 2 AND organization_id = org_id));
595
596 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
597 SET transaction_type = upper(transaction_type)
598 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
599 AND process_flag = 1
600 AND
601 (
602 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
603 OR ( p_batch_id = BOSI.batch_id )
604 )
605 AND (all_org = 1
606 OR
607 (all_org = 2 AND organization_id = org_id));
608
609
610 stmt_num := 7;
611 /* Update the operation code from the standard operation id */
612
613 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
614 SET operation_code = (SELECT operation_code
615 FROM BOM_STANDARD_OPERATIONS bso
616 WHERE bso.standard_operation_id = BOSI.standard_operation_id
617 and bso.organization_id = BOSI.organization_id)
618 WHERE process_flag = 1
622 AND
619 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
620 AND standard_operation_id is not null
621 AND organization_id is not null
623 (
624 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
625 OR ( p_batch_id = BOSI.batch_id )
626 )
627 AND exists (SELECT 'x'
628 FROM BOM_STANDARD_OPERATIONS bso
629 WHERE bso.standard_operation_id = BOSI.standard_operation_id
630 and bso.organization_id = BOSI.organization_id);
631
632 stmt_num := 8;
633 /* Update the department code from the department id */
634
635 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
636 SET department_code = (SELECT department_code
637 FROM BOM_DEPARTMENTS bd
638 WHERE bd.department_id = BOSI.department_id
639 and bd.organization_id = BOSI.organization_id)
640 WHERE process_flag = 1
641 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
642 AND department_id is not null
643 AND organization_id is not null
644 AND
645 (
646 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
647 OR ( p_batch_id = BOSI.batch_id )
648 )
649 AND exists (SELECT 'x'
650 FROM BOM_STANDARD_OPERATIONS bso
651 WHERE bso.department_id = BOSI.department_id
652 and bso.organization_id = BOSI.organization_id);
653
654 stmt_num := 9;
655 /* Resolve the line_op_seq_ids and process_op_seq_ids */
656
657 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
658 SET(line_op_seq_number, line_op_code)
659 = (SELECT bos1.operation_seq_num, bso1.operation_code
660 FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
661 WHERE BOS1.operation_sequence_id = BOSI.line_op_seq_id
662 AND BSO1.organization_id = BOSI.organization_id
663 AND BOS1.standard_operation_id = BSO1.standard_operation_id)
664 WHERE process_flag = 1
665 AND upper(transaction_type) in (G_Delete, G_Update)
666 AND line_op_seq_id is not null
667 AND
668 (
669 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
670 OR ( p_batch_id = BOSI.batch_id )
671 )
672 AND exists (SELECT 'x'
673 FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
674 WHERE BOS1.operation_sequence_id = BOSI.line_op_seq_id
675 AND BSO1.organization_id = BOSI.organization_id
676 AND BOS1.standard_operation_id = BSO1.standard_operation_id);
677
678 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
679 SET(process_seq_number, process_code)
680 = (SELECT bos1.operation_seq_num, bso1.operation_code
681 FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
682 WHERE BOS1.operation_sequence_id = BOSI.process_op_seq_id
683 AND BSO1.organization_id = BOSI.organization_id
684 AND BOS1.standard_operation_id = BSO1.standard_operation_id)
685 WHERE process_flag = 1
686 AND upper(transaction_type) in (G_Delete, G_Update)
687 AND process_op_seq_id is not null
688 AND
689 (
690 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
691 OR ( p_batch_id = BOSI.batch_id )
692 )
693 AND exists (SELECT 'x'
694 FROM BOM_OPERATION_SEQUENCES BOS1, BOM_STANDARD_OPERATIONS BSO1
695 WHERE BOS1.operation_sequence_id = BOSI.process_op_seq_id
696 AND BSO1.organization_id = BOSI.organization_id
697 AND BOS1.standard_operation_id = BSO1.standard_operation_id);
698
699 stmt_num := 10;
700 /* Update the delete_group_name from bom_interface_delete_groups */
701 UPDATE BOM_OP_SEQUENCES_INTERFACE BOSI
702 SET (DELETE_GROUP_NAME, DG_DESCRIPTION)
703 = (SELECT DELETE_GROUP_NAME, DESCRIPTION
704 FROM bom_interface_delete_groups
705 Where upper(entity_name) = G_OprDelEntity
706 And rownum = 1)
707 WHERE process_flag = 1
708 AND upper(transaction_type) in (G_Delete)
709 AND organization_id is not null
710 AND
711 (
712 ( (p_batch_id IS NULL) AND (BOSI.batch_id IS NULL) )
713 OR ( p_batch_id = BOSI.batch_id )
714 )
715 AND exists (SELECT 'x'
716 FROM bom_interface_delete_groups
717 Where upper(entity_name) = G_RtgDelEntity
718 );
719
720 stmt_num := 11;
721
722 /* INSERTS ONLY - Load rows from operation interface into resource interface*/
723 INSERT into bom_op_resources_interface (
724 RESOURCE_ID,
725 RESOURCE_CODE,
726 ORGANIZATION_ID,
727 ORGANIZATION_CODE,
728 LAST_UPDATE_DATE,
729 LAST_UPDATED_BY,
730 CREATION_DATE,
731 CREATED_BY,
732 LAST_UPDATE_LOGIN,
733 REQUEST_ID,
734 PROGRAM_APPLICATION_ID,
735 PROGRAM_ID,
736 PROGRAM_UPDATE_DATE,
737 OPERATION_SEQUENCE_ID,
738 OPERATION_SEQ_NUM,
739 ASSEMBLY_ITEM_NUMBER,
740 ASSEMBLY_ITEM_ID,
741 ALTERNATE_ROUTING_DESIGNATOR,
742 EFFECTIVITY_DATE,
746 BATCH_ID)
743 RESOURCE_SEQ_NUM,
744 PROCESS_FLAG,
745 TRANSACTION_TYPE,
747 SELECT
748 RESOURCE_ID1,
749 RESOURCE_CODE1,
750 ORGANIZATION_ID,
751 ORGANIZATION_CODE,
752 NVL(LAST_UPDATE_DATE, SYSDATE),
753 NVL(LAST_UPDATED_BY, user_id),
754 NVL(CREATION_DATE,SYSDATE),
755 NVL(CREATED_BY, user_id),
756 NVL(LAST_UPDATE_LOGIN, user_id),
757 NVL(REQUEST_ID, req_id),
758 NVL(PROGRAM_APPLICATION_ID, prog_appid),
759 NVL(PROGRAM_ID, prog_id),
760 NVL(PROGRAM_UPDATE_DATE, sysdate),
761 OPERATION_SEQUENCE_ID,
762 OPERATION_SEQ_NUM,
763 ASSEMBLY_ITEM_NUMBER,
764 ASSEMBLY_ITEM_ID,
765 ALTERNATE_ROUTING_DESIGNATOR,
766 EFFECTIVITY_DATE,
767 10,
768 1,
769 G_Create,
770 BATCH_ID
771 FROM BOM_OP_SEQUENCES_INTERFACE
772 WHERE process_flag = 1
773 AND transaction_type = G_Create
774 AND
775 (
776 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
777 OR ( p_batch_id = batch_id )
778 )
779 AND (all_org = 1
780 OR
781 (all_org = 2 AND organization_id = org_id))
782 AND (RESOURCE_CODE1 is not null
783 OR
784 RESOURCE_ID1 is not null);
785
786 INSERT into bom_op_resources_interface (
787 RESOURCE_ID,
788 RESOURCE_CODE,
789 ORGANIZATION_ID,
790 ORGANIZATION_CODE,
791 LAST_UPDATE_DATE,
792 LAST_UPDATED_BY,
793 CREATION_DATE,
794 CREATED_BY,
795 LAST_UPDATE_LOGIN,
796 REQUEST_ID,
797 PROGRAM_APPLICATION_ID,
798 PROGRAM_ID,
799 PROGRAM_UPDATE_DATE,
800 OPERATION_SEQUENCE_ID,
801 OPERATION_SEQ_NUM,
802 ASSEMBLY_ITEM_NUMBER,
803 ASSEMBLY_ITEM_ID,
804 ALTERNATE_ROUTING_DESIGNATOR,
805 EFFECTIVITY_DATE,
806 RESOURCE_SEQ_NUM,
807 PROCESS_FLAG,
808 TRANSACTION_TYPE,
809 BATCH_ID)
810 SELECT
811 RESOURCE_ID2,
812 RESOURCE_CODE2,
813 ORGANIZATION_ID,
814 ORGANIZATION_CODE,
815 NVL(LAST_UPDATE_DATE, SYSDATE),
816 NVL(LAST_UPDATED_BY, user_id),
817 NVL(CREATION_DATE,SYSDATE),
818 NVL(CREATED_BY, user_id),
819 NVL(LAST_UPDATE_LOGIN, user_id),
820 NVL(REQUEST_ID, req_id),
821 NVL(PROGRAM_APPLICATION_ID, prog_appid),
822 NVL(PROGRAM_ID, prog_id),
823 NVL(PROGRAM_UPDATE_DATE, sysdate),
824 OPERATION_SEQUENCE_ID,
825 OPERATION_SEQ_NUM,
826 ASSEMBLY_ITEM_NUMBER,
827 ASSEMBLY_ITEM_ID,
828 ALTERNATE_ROUTING_DESIGNATOR,
829 EFFECTIVITY_DATE,
830 20,
831 1,
832 G_Create,
833 BATCH_ID
834 FROM BOM_OP_SEQUENCES_INTERFACE
835 WHERE process_flag = 1
836 AND transaction_type = G_Create
837 AND
838 (
839 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
840 OR ( p_batch_id = batch_id )
841 )
842 AND (all_org = 1
843 OR
844 (all_org = 2 AND organization_id = org_id))
845 AND (RESOURCE_CODE2 is not null
846 OR
847 RESOURCE_ID2 is not null);
848
849 INSERT into bom_op_resources_interface (
850 RESOURCE_ID,
851 RESOURCE_CODE,
852 ORGANIZATION_ID,
853 ORGANIZATION_CODE,
854 LAST_UPDATE_DATE,
855 LAST_UPDATED_BY,
856 CREATION_DATE,
857 CREATED_BY,
858 LAST_UPDATE_LOGIN,
859 REQUEST_ID,
860 PROGRAM_APPLICATION_ID,
861 PROGRAM_ID,
862 PROGRAM_UPDATE_DATE,
863 OPERATION_SEQUENCE_ID,
864 OPERATION_SEQ_NUM,
865 ASSEMBLY_ITEM_NUMBER,
866 ASSEMBLY_ITEM_ID,
867 ALTERNATE_ROUTING_DESIGNATOR,
868 EFFECTIVITY_DATE,
869 RESOURCE_SEQ_NUM,
870 PROCESS_FLAG,
871 TRANSACTION_TYPE,
872 BATCH_ID)
873 SELECT
874 RESOURCE_ID3,
875 RESOURCE_CODE3,
876 ORGANIZATION_ID,
877 ORGANIZATION_CODE,
878 NVL(LAST_UPDATE_DATE, SYSDATE),
879 NVL(LAST_UPDATED_BY, user_id),
880 NVL(CREATION_DATE,SYSDATE),
881 NVL(CREATED_BY, user_id),
882 NVL(LAST_UPDATE_LOGIN, user_id),
883 NVL(REQUEST_ID, req_id),
884 NVL(PROGRAM_APPLICATION_ID, prog_appid),
885 NVL(PROGRAM_ID, prog_id),
886 NVL(PROGRAM_UPDATE_DATE, sysdate),
887 OPERATION_SEQUENCE_ID,
888 OPERATION_SEQ_NUM,
889 ASSEMBLY_ITEM_NUMBER,
890 ASSEMBLY_ITEM_ID,
891 ALTERNATE_ROUTING_DESIGNATOR,
892 EFFECTIVITY_DATE,
893 30,
894 1,
895 G_Create,
896 BATCH_ID
897 FROM BOM_OP_SEQUENCES_INTERFACE
898 WHERE process_flag = 1
902 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
899 AND transaction_type = G_Create
900 AND
901 (
903 OR ( p_batch_id = batch_id )
904 )
905 AND (all_org = 1
906 OR
907 (all_org = 2 AND organization_id = org_id))
908 AND (RESOURCE_CODE3 is not null
909 OR
910 RESOURCE_ID3 is not null);
911
912
913 COMMIT;
914
915 stmt_num := 11;
916
917 /* Update the interface records with process_flag 3 and insert into
918 mtl_interface_errors if Item_number or Organization_code is missing*/
919
920 msg_name1 := 'BOM_ORG_ID_MISSING';
921 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
922 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
923 msg_text1 := FND_MESSAGE.GET;
924 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
925 msg_text2 := FND_MESSAGE.GET;
926 INSERT INTO mtl_interface_errors
927 (
928 TRANSACTION_ID,
929 UNIQUE_ID,
930 ORGANIZATION_ID,
931 COLUMN_NAME,
932 TABLE_NAME,
933 MESSAGE_NAME,
934 ERROR_MESSAGE,
935 LAST_UPDATE_DATE,
936 LAST_UPDATED_BY,
937 CREATION_DATE,
938 CREATED_BY,
939 LAST_UPDATE_LOGIN,
940 REQUEST_ID,
941 PROGRAM_APPLICATION_ID,
942 PROGRAM_ID,
943 PROGRAM_UPDATE_DATE
944 )
945 Select
946 BOSI.transaction_id,
947 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
948 Null,
949 null,
950 'BOM_OP_SEQUENCES_INTERFACE',
951 decode ( BOSI.Organization_code, null, msg_name1,msg_name2),
952 decode ( BOSI.Organization_code, null, msg_text1,msg_text2),
953 NVL(LAST_UPDATE_DATE, SYSDATE),
954 NVL(LAST_UPDATED_BY, user_id),
955 NVL(CREATION_DATE,SYSDATE),
956 NVL(CREATED_BY, user_id),
957 NVL(LAST_UPDATE_LOGIN, user_id),
958 NVL(REQUEST_ID, req_id),
959 NVL(PROGRAM_APPLICATION_ID, prog_appid),
960 NVL(PROGRAM_ID, prog_id),
961 NVL(PROGRAM_UPDATE_DATE, sysdate)
962 from BOM_OP_SEQUENCES_INTERFACE BOSI
963 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
964 and transaction_id is not null
965 and process_flag = 1
966 and
967 (
968 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
969 OR ( p_batch_id = batch_id )
970 )
971 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
972
973 Update BOM_OP_SEQUENCES_INTERFACE
974 set process_flag = 3
975 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
976 and transaction_id is not null
977 and process_flag = 1
978 and
979 (
980 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
981 OR ( p_batch_id = batch_id )
982 )
983 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
984 Commit;
985
986 return (0);
987
988
989 EXCEPTION
990 WHEN others THEN
991 err_text := 'Bom_Rtg_OI_Util(Process_Op_Seqs-'||stmt_num||') '||substrb(SQLERRM,1,1000);
992 RETURN(SQLCODE);
993 END Process_Op_Seqs;
994
995
996 /*--------------------------Process_Op_Resources------------------------------
997
998 NAME
999 Process_Op_Resources
1000 DESCRIPTION
1001 Populate the user-friendly columns to Operation Resources records
1002 in the interface table
1003 REQUIRES
1004
1005 MODIFIES
1006 BOM_OP_RESOURCES_INTERFACE
1007 MTL_INTERFACE_ERRORS
1008 RETURNS
1009 0 if successful
1010 SQLCODE if unsuccessful
1011 NOTES
1012 -----------------------------------------------------------------------------*/
1013 FUNCTION Process_Op_Resources (
1014 org_id NUMBER,
1015 all_org NUMBER ,
1016 user_id NUMBER,
1017 login_id NUMBER,
1018 prog_appid NUMBER,
1019 prog_id NUMBER,
1020 req_id NUMBER,
1021 err_text IN OUT NOCOPY VARCHAR2,
1022 p_batch_id NUMBER
1023 )
1024 return INTEGER
1025 IS
1026 stmt_num NUMBER := 0;
1027 msg_name1 varchar2(30);
1028 msg_name2 varchar2(30);
1029 msg_text1 varchar2(2000);
1030 msg_text2 varchar2(2000);
1031
1032 BEGIN
1033 stmt_num := 1;
1034 /* Resolve the operation_sequence_id for all the records */
1035
1036 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1037 SET(routing_sequence_id, effectivity_date, operation_seq_num)
1038 = (SELECT routing_sequence_id, effectivity_date, operation_seq_num
1039 FROM BOM_OPERATION_SEQUENCES BOS1
1040 WHERE BOS1.operation_sequence_id = BORI.operation_sequence_id )
1041 WHERE process_flag = 1
1042 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1043 AND OPERATION_SEQUENCE_ID is not null
1044 AND
1045 (
1046 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1047 OR ( p_batch_id = BORI.batch_id )
1048 )
1049 AND exists (SELECT 'x'
1050 FROM BOM_OPERATION_SEQUENCES BOS2
1051 WHERE BOS2.OPERATION_SEQUENCE_ID = BORI.OPERATION_SEQUENCE_ID);
1052
1053
1057 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1054 stmt_num := 2;
1055 /* Resolve the routing sequence ids */
1056
1058 SET(assembly_item_id, organization_id, alternate_routing_designator)
1059 = (SELECT assembly_item_id, organization_id, alternate_routing_designator
1060 FROM BOM_OPERATIONAL_ROUTINGS BOR1
1061 WHERE BOR1.routing_sequence_id = BORI.routing_sequence_id)
1062 WHERE process_flag = 1
1063 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1064 AND routing_sequence_id is not null
1065 AND
1066 (
1067 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1068 OR ( p_batch_id = BORI.batch_id )
1069 )
1070 AND exists (SELECT 'x'
1071 FROM BOM_OPERATIONAL_ROUTINGS BOR2
1072 WHERE BOR2.routing_sequence_id = BORI.routing_sequence_id);
1073
1074
1075 stmt_num := 3;
1076 /* Update Organization Code using Organization_id
1077 this also needed if Organization_id is given and code is not given*/
1078
1079 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1080 SET organization_code = (SELECT organization_code
1081 FROM MTL_PARAMETERS mp1
1082 WHERE mp1.organization_id = BORI.organization_id)
1083 WHERE process_flag = 1
1084 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1085 AND organization_id is not null
1086 AND
1087 (
1088 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1089 OR ( p_batch_id = BORI.batch_id )
1090 )
1091 AND exists (SELECT 'x'
1092 FROM MTL_PARAMETERS mp2
1093 WHERE mp2.organization_id = BORI.organization_id);
1094
1095
1096
1097 stmt_num := 4;
1098 /* Update Organization_ids if organization_code is given org id is null.
1099 Orgnaization_id information is needed in the next steps */
1100
1101 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1102 SET organization_id = (SELECT organization_id
1103 FROM MTL_PARAMETERS mp1
1104 WHERE mp1.organization_code = BORI.organization_code)
1105 WHERE process_flag = 1
1106 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1107 AND organization_id is null
1108 AND organization_code is not null
1109 AND
1110 (
1111 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1112 OR ( p_batch_id = BORI.batch_id )
1113 );
1114
1115
1116
1117 stmt_num := 5;
1118 /* Update Assembly Item name */
1119
1120 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1121 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1122 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1123 WHERE mvl1.inventory_item_id = BORI.assembly_item_id
1124 and mvl1.organization_id = BORI.organization_id)
1125 WHERE process_flag = 1
1126 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1127 AND assembly_item_id is not null
1128 AND organization_id is not null
1129 AND
1130 (
1131 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1132 OR ( p_batch_id = BORI.batch_id )
1133 )
1134 AND exists (SELECT 'x'
1135 FROM MTL_SYSTEM_ITEMS mvl2
1136 WHERE mvl2.inventory_item_id = BORI.assembly_item_id
1137 and mvl2.organization_id = BORI.organization_id);
1138
1139
1140 stmt_num := 6;
1141 /* Update resource code */
1142
1143 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1144 SET resource_code = (SELECT resource_code
1145 FROM BOM_RESOURCES br
1146 WHERE br.resource_id = BORI.resource_id
1147 and br.organization_id = BORI.organization_id)
1148 WHERE process_flag = 1
1149 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1150 AND resource_id is not null
1151 AND organization_id is not null
1152 AND
1153 (
1154 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1155 OR ( p_batch_id = BORI.batch_id )
1156 )
1157 AND exists (SELECT 'x'
1158 FROM BOM_RESOURCES br
1159 WHERE br.resource_id = BORI.resource_id
1160 and br.organization_id = BORI.organization_id);
1161
1162
1163 stmt_num := 7;
1164 /* Assign transaction ids */
1165
1166 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1167 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1168 WHERE transaction_id is null
1169 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1170 AND process_flag = 1
1171 AND
1172 (
1173 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1174 OR ( p_batch_id = BORI.batch_id )
1175 )
1176 AND (all_org = 1
1177 OR
1178 (all_org = 2 AND organization_id = org_id));
1179
1180 UPDATE BOM_OP_RESOURCES_INTERFACE BORI
1184 AND
1181 SET transaction_type = upper(transaction_type)
1182 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
1183 AND process_flag = 1
1185 (
1186 ( (p_batch_id IS NULL) AND (BORI.batch_id IS NULL) )
1187 OR ( p_batch_id = BORI.batch_id )
1188 )
1189 AND (all_org = 1
1190 OR
1191 (all_org = 2 AND organization_id = org_id));
1192
1193 COMMIT;
1194
1195 stmt_num := 8;
1196 /* Update the interface records with process_flag 3 and insert into
1197 mtl_interface_errors if Item_number or Organization_code is missing*/
1198
1199 msg_name1 := 'BOM_ORG_ID_MISSING';
1200 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1201 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1202 msg_text1 := FND_MESSAGE.GET;
1203 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1204 msg_text2 := FND_MESSAGE.GET;
1205 INSERT INTO mtl_interface_errors
1206 (
1207 TRANSACTION_ID,
1208 UNIQUE_ID,
1209 ORGANIZATION_ID,
1210 COLUMN_NAME,
1211 TABLE_NAME,
1212 MESSAGE_NAME,
1213 ERROR_MESSAGE,
1214 LAST_UPDATE_DATE,
1215 LAST_UPDATED_BY,
1216 CREATION_DATE,
1217 CREATED_BY,
1218 LAST_UPDATE_LOGIN,
1219 REQUEST_ID,
1220 PROGRAM_APPLICATION_ID,
1221 PROGRAM_ID,
1222 PROGRAM_UPDATE_DATE
1223 )
1224 Select
1225 BORI.transaction_id,
1226 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1227 Null,
1228 null,
1229 'BOM_OP_RESOURCES_INTERFACE',
1230 decode ( BORI.Organization_code, null, msg_name1,msg_name2),
1231 decode ( BORI.Organization_code, null, msg_text1,msg_text2),
1232 NVL(LAST_UPDATE_DATE, SYSDATE),
1233 NVL(LAST_UPDATED_BY, user_id),
1234 NVL(CREATION_DATE,SYSDATE),
1235 NVL(CREATED_BY, user_id),
1236 NVL(LAST_UPDATE_LOGIN, user_id),
1237 NVL(REQUEST_ID, req_id),
1238 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1239 NVL(PROGRAM_ID, prog_id),
1240 NVL(PROGRAM_UPDATE_DATE, sysdate)
1241 from BOM_OP_RESOURCES_INTERFACE BORI
1242 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1243 and transaction_id is not null
1244 and process_flag =1
1245 and
1246 (
1247 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1248 OR ( p_batch_id = batch_id )
1249 )
1250 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1251
1252 Update BOM_OP_RESOURCES_INTERFACE
1253 set process_flag = 3
1254 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1255 and transaction_id is not null
1256 and process_flag =1
1257 and
1258 (
1259 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1260 OR ( p_batch_id = batch_id )
1261 )
1262 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1263 Commit;
1264
1265 return(0);
1266
1267 EXCEPTION
1268 WHEN others THEN
1269 err_text := 'Bom_Rtg_OI_Util(Process_Op_Resources-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1270 RETURN(SQLCODE);
1271 END Process_Op_Resources;
1272
1273 /*--------------------------Process_Sub_Op_Resources------------------------------
1274
1275 NAME
1276 Process_Sub_Op_Resources
1277 DESCRIPTION
1278 Populate the user-friendly columns to Substitute Resource records
1279 in the interface table
1280 REQUIRES
1281
1282 MODIFIES
1283 BOM_SUB_OP_RESOURCES_INTERFACE
1284 MTL_INTERFACE_ERRORS
1285 RETURNS
1286 0 if successful
1287 SQLCODE if unsuccessful
1288 NOTES
1289 -----------------------------------------------------------------------------*/
1290 FUNCTION Process_Sub_Op_Resources (
1291 org_id NUMBER,
1292 all_org NUMBER ,
1293 user_id NUMBER,
1294 login_id NUMBER,
1295 prog_appid NUMBER,
1296 prog_id NUMBER,
1297 req_id NUMBER,
1298 err_text IN OUT NOCOPY VARCHAR2,
1299 p_batch_id NUMBER
1300 )
1301 return INTEGER
1302 IS
1303 stmt_num NUMBER := 0;
1304 msg_name1 varchar2(30);
1305 msg_name2 varchar2(30);
1306 msg_text1 varchar2(2000);
1307 msg_text2 varchar2(2000);
1308
1309 BEGIN
1310 stmt_num := 1;
1311 /* Resolve the operation_sequence_id for all the records */
1312
1313 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1314 SET(routing_sequence_id, effectivity_date, operation_seq_num)
1315 = (select routing_sequence_id, EFFECTIVITY_DATE, OPERATION_SEQ_NUM
1316 FROM BOM_OPERATION_SEQUENCES BOS1
1317 WHERE BOS1.OPERATION_SEQUENCE_ID = BSORI.operation_sequence_id)
1318 WHERE process_flag = 1
1319 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1320 AND OPERATION_SEQUENCE_ID is not null
1321 AND
1322 (
1323 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1324 OR ( p_batch_id = BSORI.batch_id )
1325 )
1326 AND exists (SELECT 'x'
1327 FROM BOM_OPERATION_SEQUENCES BOS2
1328 WHERE BOS2.OPERATION_SEQUENCE_ID = BSORI.OPERATION_SEQUENCE_ID);
1329
1330
1331 stmt_num := 2;
1332 /* Resolve the routing sequence ids */
1333
1334 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1338 WHERE BOR1.routing_sequence_id = BSORI.routing_sequence_id)
1335 SET(assembly_item_id, organization_id, alternate_routing_designator)
1336 = (SELECT assembly_item_id, organization_id , alternate_routing_designator
1337 FROM BOM_OPERATIONAL_ROUTINGS BOR1
1339 WHERE process_flag = 1
1340 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1341 AND routing_sequence_id is not null
1342 AND
1343 (
1344 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1345 OR ( p_batch_id = BSORI.batch_id )
1346 )
1347 AND exists (SELECT 'x'
1348 FROM BOM_OPERATIONAL_ROUTINGS BOR2
1349 WHERE BOR2.routing_sequence_id = BSORI.routing_sequence_id);
1350
1351
1352 stmt_num := 3;
1353 /* Update Organization Code using Organization_id
1354 this also needed if Organization_id is given and code is not given*/
1355
1356 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1357 SET organization_code = (SELECT organization_code
1358 FROM MTL_PARAMETERS mp1
1359 WHERE mp1.organization_id = BSORI.organization_id)
1360 WHERE process_flag = 1
1361 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1362 AND organization_id is not null
1363 AND
1364 (
1365 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1366 OR ( p_batch_id = BSORI.batch_id )
1367 )
1368 AND exists (SELECT 'x'
1369 FROM MTL_PARAMETERS mp2
1370 WHERE mp2.organization_id = BSORI.organization_id);
1371
1372
1373
1374 stmt_num := 4;
1375 /* Update Organization_ids if organization_code is given org id is null.
1376 Orgnaization_id information is needed in the next steps */
1377
1378 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1379 SET organization_id = (SELECT organization_id
1380 FROM MTL_PARAMETERS mp1
1381 WHERE mp1.organization_code = BSORI.organization_code)
1382 WHERE process_flag = 1
1383 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1384 AND organization_id is null
1385 AND organization_code is not null
1386 AND
1387 (
1388 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1389 OR ( p_batch_id = BSORI.batch_id )
1390 );
1391
1392
1393
1394 stmt_num := 5;
1395 /* Update Assembly Item name */
1396
1397 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1398 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1399 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1400 WHERE mvl1.inventory_item_id = BSORI.assembly_item_id
1401 and mvl1.organization_id = BSORI.organization_id)
1402 WHERE process_flag = 1
1403 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1404 AND assembly_item_id is not null
1405 AND organization_id is not null
1406 AND
1407 (
1408 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1409 OR ( p_batch_id = BSORI.batch_id )
1410 )
1411 AND exists (SELECT 'x'
1412 FROM MTL_SYSTEM_ITEMS mvl2
1413 WHERE mvl2.inventory_item_id = BSORI.assembly_item_id
1414 and mvl2.organization_id = BSORI.organization_id);
1415
1416
1417 stmt_num := 6;
1418 /* Update resource code */
1419
1420 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1421 SET sub_resource_code = (SELECT resource_code
1422 FROM BOM_RESOURCES br
1423 WHERE br.resource_id = BSORI.resource_id
1424 and br.organization_id = BSORI.organization_id)
1425 WHERE process_flag = 1
1426 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1427 AND resource_id is not null
1428 AND organization_id is not null
1429 AND
1430 (
1431 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1432 OR ( p_batch_id = BSORI.batch_id )
1433 )
1434 AND exists (SELECT 'x'
1435 FROM BOM_RESOURCES br
1436 WHERE br.resource_id = BSORI.resource_id
1437 and br.organization_id = BSORI.organization_id);
1438
1439
1440 stmt_num := 7;
1441 /* Assign transaction ids */
1442
1443 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1444 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1445 WHERE transaction_id is null
1446 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1447 AND process_flag = 1
1448 AND
1449 (
1450 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1451 OR ( p_batch_id = BSORI.batch_id )
1452 )
1453 AND (all_org = 1
1454 OR
1455 (all_org = 2 AND organization_id = org_id));
1456
1457 UPDATE BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1458 SET transaction_type = upper(transaction_type)
1459 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
1460 AND process_flag = 1
1461 AND
1462 (
1466 AND (all_org = 1
1463 ( (p_batch_id IS NULL) AND (BSORI.batch_id IS NULL) )
1464 OR ( p_batch_id = BSORI.batch_id )
1465 )
1467 OR
1468 (all_org = 2 AND organization_id = org_id));
1469
1470 COMMIT;
1471 stmt_num := 8;
1472 /* Update the interface records with process_flag 3 and insert into
1473 mtl_interface_errors if Item_number or Organization_code is missing*/
1474
1475 msg_name1 := 'BOM_ORG_ID_MISSING';
1476 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1477 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1478 msg_text1 := FND_MESSAGE.GET;
1479 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1480 msg_text2 := FND_MESSAGE.GET;
1481 INSERT INTO mtl_interface_errors
1482 (
1483 TRANSACTION_ID,
1484 UNIQUE_ID,
1485 ORGANIZATION_ID,
1486 COLUMN_NAME,
1487 TABLE_NAME,
1488 MESSAGE_NAME,
1489 ERROR_MESSAGE,
1490 LAST_UPDATE_DATE,
1491 LAST_UPDATED_BY,
1492 CREATION_DATE,
1493 CREATED_BY,
1494 LAST_UPDATE_LOGIN,
1495 REQUEST_ID,
1496 PROGRAM_APPLICATION_ID,
1497 PROGRAM_ID,
1498 PROGRAM_UPDATE_DATE
1499 )
1500 Select
1501 BSORI.transaction_id,
1502 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1503 Null,
1504 null,
1505 'BOM_SUB_OP_RESOURCES_INTERFACE',
1506 decode ( BSORI.Organization_code, null, msg_name1,msg_name2),
1507 decode ( BSORI.Organization_code, null, msg_text1,msg_text2),
1508 NVL(LAST_UPDATE_DATE, SYSDATE),
1509 NVL(LAST_UPDATED_BY, user_id),
1510 NVL(CREATION_DATE,SYSDATE),
1511 NVL(CREATED_BY, user_id),
1512 NVL(LAST_UPDATE_LOGIN, user_id),
1513 NVL(REQUEST_ID, req_id),
1514 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1515 NVL(PROGRAM_ID, prog_id),
1516 NVL(PROGRAM_UPDATE_DATE, sysdate)
1517 from BOM_SUB_OP_RESOURCES_INTERFACE BSORI
1518 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1519 and transaction_id is not null
1520 and process_flag =1
1521 and
1522 (
1523 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1524 OR ( p_batch_id = batch_id )
1525 )
1526 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1527
1528 Update BOM_SUB_OP_RESOURCES_INTERFACE
1529 set process_flag = 3
1530 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1531 and transaction_id is not null
1532 and process_flag =1
1533 and
1534 (
1535 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1536 OR ( p_batch_id = batch_id )
1537 )
1538 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1539 Commit;
1540
1541 return(0);
1542
1543 EXCEPTION
1544 WHEN others THEN
1545 err_text := 'Bom_Rtg_OI_Util(Process_Sub_Op_Resources-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1546 RETURN(SQLCODE);
1547 END Process_Sub_Op_Resources;
1548
1549 /*--------------------------Process_Op_Nwks------------------------------
1550
1551 NAME
1552 Process_Op_Nwks
1553 DESCRIPTION
1554 Populate the user-friendly columns to Operation network records
1555 in the interface table
1556 REQUIRES
1557
1558 MODIFIES
1559 BOM_OP_NETWORKS_INTERFACE
1560 MTL_INTERFACE_ERRORS
1561 RETURNS
1562 0 if successful
1563 SQLCODE if unsuccessful
1564 NOTES
1565 -----------------------------------------------------------------------------*/
1566
1567 FUNCTION Process_Op_Nwks (
1568 org_id NUMBER,
1569 all_org NUMBER ,
1570 user_id NUMBER,
1571 login_id NUMBER,
1572 prog_appid NUMBER,
1573 prog_id NUMBER,
1574 req_id NUMBER,
1575 err_text IN OUT NOCOPY VARCHAR2,
1576 p_batch_id NUMBER
1577 )
1578 return INTEGER
1579 IS
1580 stmt_num NUMBER := 0;
1581 msg_name1 varchar2(30);
1582 msg_name2 varchar2(30);
1583 msg_text1 varchar2(2000);
1584 msg_text2 varchar2(2000);
1585
1586 BEGIN
1587 stmt_num := 1;
1588 /* Resolve the from_op_seq_id and to_op_seq_id for all the records */
1589
1590 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1591 SET(routing_sequence_id, from_op_seq_number, from_start_effective_date)
1592 = (SELECT routing_sequence_id, operation_seq_num, effectivity_date
1593 FROM BOM_OPERATION_SEQUENCES BOS1
1594 WHERE BOS1.operation_sequence_id = BONI.from_Op_seq_id)
1595 WHERE process_flag = 1
1596 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1597 AND BONI.from_Op_seq_id is not null
1598 AND
1599 (
1600 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1601 OR ( p_batch_id = BONI.batch_id )
1602 )
1603 AND exists (SELECT 'x'
1604 FROM BOM_OPERATION_SEQUENCES BOS2
1605 WHERE BOS2.OPERATION_SEQUENCE_ID = BONI.FROM_OP_SEQ_ID );
1606
1607 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1608 SET(routing_sequence_id, to_op_seq_number, to_start_effective_date)
1609 = (SELECT routing_sequence_id, operation_seq_num, effectivity_date
1610 FROM BOM_OPERATION_SEQUENCES BOS1
1614 AND BONI.to_Op_seq_id is not null
1611 WHERE BOS1.operation_sequence_id = BONI.to_Op_seq_id)
1612 WHERE process_flag = 1
1613 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1615 AND
1616 (
1617 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1618 OR ( p_batch_id = BONI.batch_id )
1619 )
1620 AND exists (SELECT 'x'
1621 FROM BOM_OPERATION_SEQUENCES BOS2
1622 WHERE BOS2.OPERATION_SEQUENCE_ID = BONI.TO_OP_SEQ_ID );
1623
1624
1625 stmt_num := 2;
1626 /* Resolve the routing sequence ids for updates and deletes */
1627
1628 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1629 SET(assembly_item_id, organization_id, alternate_routing_designator)
1630 = (SELECT assembly_item_id, organization_id , alternate_routing_designator
1631 FROM BOM_OPERATIONAL_ROUTINGS BOR1
1632 WHERE BOR1.routing_sequence_id = BONI.routing_sequence_id)
1633 WHERE process_flag = 1
1634 AND upper(transaction_type) in (G_Delete, G_Update, G_Create)
1635 AND routing_sequence_id is not null
1636 AND
1637 (
1638 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1639 OR ( p_batch_id = BONI.batch_id )
1640 )
1641 AND exists (SELECT 'x'
1642 FROM BOM_OPERATIONAL_ROUTINGS BOR2
1643 WHERE BOR2.routing_sequence_id = BONI.routing_sequence_id);
1644
1645
1646 stmt_num := 3;
1647 /* Update Organization Code using Organization_id
1648 this also needed if orgnaization_id is given and code is not given*/
1649
1650 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1651 SET organization_code = (SELECT organization_code
1652 FROM MTL_PARAMETERS mp1
1653 WHERE mp1.organization_id = BONI.organization_id)
1654 WHERE process_flag = 1
1655 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1656 AND organization_id is not null
1657 AND
1658 (
1659 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1660 OR ( p_batch_id = BONI.batch_id )
1661 )
1662 AND exists (SELECT 'x'
1663 FROM MTL_PARAMETERS mp2
1664 WHERE mp2.organization_id = BONI.organization_id);
1665
1666
1667
1668 stmt_num := 4;
1669 /* Update Organization_ids if organization_code is given org id is null.
1670 Orgnaization_id information is needed in the next steps */
1671
1672 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1673 SET organization_id = (SELECT organization_id
1674 FROM MTL_PARAMETERS mp1
1675 WHERE mp1.organization_code = BONI.organization_code)
1676 WHERE process_flag = 1
1677 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1678 AND organization_id is null
1679 AND organization_code is not null
1680 AND
1681 (
1682 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1683 OR ( p_batch_id = BONI.batch_id )
1684 );
1685
1686
1687
1688 stmt_num := 5;
1689 /* Update Assembly Item name */
1690
1691 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1692 SET ASSEMBLY_ITEM_NUMBER = (SELECT CONCATENATED_SEGMENTS
1693 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1694 WHERE mvl1.inventory_item_id = BONI.assembly_item_id
1695 and mvl1.organization_id = BONI.organization_id)
1696 WHERE process_flag = 1
1697 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1698 AND assembly_item_id is not null
1699 AND organization_id is not null
1700 AND
1701 (
1702 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1703 OR ( p_batch_id = BONI.batch_id )
1704 )
1705 AND exists (SELECT 'x'
1706 FROM MTL_SYSTEM_ITEMS mvl2
1707 WHERE mvl2.inventory_item_id = BONI.assembly_item_id
1708 and mvl2.organization_id = BONI.organization_id);
1709
1710
1711 stmt_num := 6;
1712 /* Assign transaction ids */
1713 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1714 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1715 WHERE transaction_id is null
1716 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1717 AND process_flag = 1
1718 AND
1719 (
1720 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1721 OR ( p_batch_id = BONI.batch_id )
1722 )
1723 AND (all_org = 1
1724 OR
1725 (all_org = 2 AND organization_id = org_id));
1726
1727 UPDATE BOM_OP_NETWORKS_INTERFACE BONI
1728 SET transaction_type = upper(transaction_type)
1729 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
1730 AND process_flag = 1
1731 AND
1732 (
1733 ( (p_batch_id IS NULL) AND (BONI.batch_id IS NULL) )
1734 OR ( p_batch_id = BONI.batch_id )
1735 )
1736 AND (all_org = 1
1737 OR
1738 (all_org = 2 AND organization_id = org_id));
1739
1740 COMMIT;
1741
1745
1742 stmt_num := 7;
1743 /* Update the interface records with process_flag 3 and insert into
1744 mtl_interface_errors if Item_number or Organization_code is missing*/
1746 msg_name1 := 'BOM_ORG_ID_MISSING';
1747 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1748 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1749 msg_text1 := FND_MESSAGE.GET;
1750 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1751 msg_text2 := FND_MESSAGE.GET;
1752 INSERT INTO mtl_interface_errors
1753 (
1754 TRANSACTION_ID,
1755 UNIQUE_ID,
1756 ORGANIZATION_ID,
1757 COLUMN_NAME,
1758 TABLE_NAME,
1759 MESSAGE_NAME,
1760 ERROR_MESSAGE,
1761 LAST_UPDATE_DATE,
1762 LAST_UPDATED_BY,
1763 CREATION_DATE,
1764 CREATED_BY,
1765 LAST_UPDATE_LOGIN,
1766 REQUEST_ID,
1767 PROGRAM_APPLICATION_ID,
1768 PROGRAM_ID,
1769 PROGRAM_UPDATE_DATE
1770 )
1771 Select
1772 BONI.transaction_id,
1773 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1774 Null,
1775 null,
1776 'BOM_OP_NETWORKS_INTERFACE',
1777 decode ( BONI.Organization_code, null, msg_name1,msg_name2),
1778 decode ( BONI.Organization_code, null, msg_text1,msg_text2),
1779 NVL(LAST_UPDATE_DATE, SYSDATE),
1780 NVL(LAST_UPDATED_BY, user_id),
1781 NVL(CREATION_DATE,SYSDATE),
1782 NVL(CREATED_BY, user_id),
1783 NVL(LAST_UPDATE_LOGIN, user_id),
1784 req_id,
1785 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1786 NVL(PROGRAM_ID, prog_id),
1787 NVL(PROGRAM_UPDATE_DATE, sysdate)
1788 from BOM_OP_NETWORKS_INTERFACE BONI
1789 where (organization_code is null or ASSEMBLY_ITEM_NUMBER is null)
1790 and transaction_id is not null
1791 and process_flag =1
1792 and
1793 (
1794 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1795 OR ( p_batch_id = batch_id )
1796 )
1797 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1798
1799 Update BOM_OP_NETWORKS_INTERFACE
1800 set process_flag = 3
1801 where (ASSEMBLY_ITEM_NUMBER is null or Organization_code is null)
1802 and transaction_id is not null
1803 and process_flag =1
1804 and
1805 (
1806 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
1807 OR ( p_batch_id = batch_id )
1808 )
1809 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
1810 Commit;
1811
1812 return(0);
1813
1814 EXCEPTION
1815 WHEN others THEN
1816 err_text := 'Bom_Rtg_OI_Util(Process_Op_Nwks-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1817 RETURN(SQLCODE);
1818 END Process_Op_Nwks;
1819
1820 /*--------------------------Process_Rtg_Revisions------------------------------
1821
1822 NAME
1823 Process_Rtg_Revisions
1824 DESCRIPTION
1825 Populate the user-friendly columns to unique index records
1826 in the interface table
1827 REQUIRES
1828
1829 MODIFIES
1830 MTL_RTG_ITEM_REVS_INTERFACE
1831 MTL_INTERFACE_ERRORS
1832 RETURNS
1833 0 if successful
1834 SQLCODE if unsuccessful
1835 NOTES
1836 -----------------------------------------------------------------------------*/
1837
1838 FUNCTION Process_Rtg_Revisions (
1839 org_id NUMBER,
1840 all_org NUMBER ,
1841 user_id NUMBER,
1842 login_id NUMBER,
1843 prog_appid NUMBER,
1844 prog_id NUMBER,
1845 req_id NUMBER,
1846 err_text IN OUT NOCOPY VARCHAR2,
1847 p_batch_id NUMBER
1848 )return integer is
1849 stmt_num NUMBER := 0;
1850 msg_name1 varchar2(30);
1851 msg_name2 varchar2(30);
1852 msg_text1 varchar2(2000);
1853 msg_text2 varchar2(2000);
1854
1855 begin
1856 stmt_num := 1;
1857 /* Update Organization Code using Organization_id
1858 this also needed if Organization_id is given and code is not given*/
1859
1860 UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
1861 SET organization_code = (SELECT organization_code
1862 FROM MTL_PARAMETERS MP1
1863 WHERE mp1.organization_id = MRIRI.organization_id)
1864 WHERE process_flag = 1
1865 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1866 AND organization_id is not null
1867 AND
1868 (
1869 ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
1870 OR ( p_batch_id = MRIRI.batch_id )
1871 )
1872 AND exists (SELECT 'x'
1873 FROM MTL_PARAMETERS MP2
1874 WHERE mp2.organization_id = MRIRI.organization_id);
1875
1876
1877
1878 stmt_num := 2;
1879 /* Update Organization_ids if organization code is given org id is null.
1880 Orgnaization_id information is needed in the next steps */
1881
1882 UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
1883 SET organization_id = (SELECT organization_id
1884 FROM MTL_PARAMETERS mp1
1885 WHERE mp1.organization_code = MRIRI.organization_code)
1886 WHERE process_flag = 1
1887 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1888 AND organization_id is null
1889 AND organization_code is not null
1890 AND
1891 (
1892 ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
1893 OR ( p_batch_id = MRIRI.batch_id )
1894 );
1895
1896
1897
1901 UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
1898 stmt_num := 3;
1899 /* Update Assembly Item name */
1900
1902 SET inventory_item_number = (SELECT concatenated_segments
1903 FROM MTL_SYSTEM_ITEMS_KFV mvl1
1904 WHERE mvl1.inventory_item_id = MRIRI.inventory_item_id
1905 and mvl1.organization_id = MRIRI.organization_id)
1906 WHERE process_flag = 1
1907 AND upper(transaction_type) in (G_Create, G_Delete, G_Update)
1908 AND inventory_item_id is not null
1909 AND organization_id is not null
1910 AND
1911 (
1912 ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
1913 OR ( p_batch_id = MRIRI.batch_id )
1914 )
1915 AND exists (SELECT 'x'
1916 FROM MTL_SYSTEM_ITEMS mvl2
1917 WHERE mvl2.inventory_item_id = MRIRI.inventory_item_id
1918 and mvl2.organization_id = MRIRI.organization_id);
1919
1920
1921
1922 stmt_num := 4;
1923 /* Assign transaction ids */
1924
1925 UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
1926 SET transaction_id = MTL_SYSTEM_ITEMS_INTERFACE_S.nextval
1927 WHERE transaction_id is null
1928 AND upper(transaction_type) in (G_Create, G_Update, G_Delete)
1929 AND process_flag = 1
1930 AND
1931 (
1932 ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
1933 OR ( p_batch_id = MRIRI.batch_id )
1934 )
1935 AND (all_org = 1
1936 OR
1937 (all_org = 2 AND organization_id = org_id));
1938
1939 UPDATE MTL_RTG_ITEM_REVS_INTERFACE MRIRI
1940 SET transaction_type = upper(transaction_type),
1941 process_revision = upper(process_revision) -- bug 3756121
1942 WHERE upper(transaction_type) in (G_Create, G_Update, G_Delete)
1943 AND process_flag = 1
1944 AND
1945 (
1946 ( (p_batch_id IS NULL) AND (MRIRI.batch_id IS NULL) )
1947 OR ( p_batch_id = MRIRI.batch_id )
1948 )
1949 AND (all_org = 1
1950 OR
1951 (all_org = 2 AND organization_id = org_id));
1952
1953 COMMIT;
1954 stmt_num := 5;
1955
1956 /* Update the interface records with process_flag 3 and insert into
1957 MTL_INTERFACE_ERRORS if Item number or Organization_code is missing*/
1958
1959 msg_name1 := 'BOM_ORG_ID_MISSING';
1960 msg_name2 := 'BOM_ASSY_ITEM_MISSING';
1961 FND_MESSAGE.SET_NAME('BOM', 'BOM_ORG_ID_MISSING');
1962 msg_text1 := FND_MESSAGE.GET;
1963 FND_MESSAGE.SET_NAME('BOM', 'BOM_ASSY_ITEM_MISSING');
1964 msg_text2 := FND_MESSAGE.GET;
1965
1966 INSERT INTO MTL_INTERFACE_ERRORS
1967 (
1968 TRANSACTION_ID,
1969 UNIQUE_ID,
1970 ORGANIZATION_ID,
1971 COLUMN_NAME,
1972 TABLE_NAME,
1973 MESSAGE_NAME,
1974 ERROR_MESSAGE,
1975 LAST_UPDATE_DATE,
1976 LAST_UPDATED_BY,
1977 CREATION_DATE,
1978 CREATED_BY,
1979 LAST_UPDATE_LOGIN,
1980 REQUEST_ID,
1981 PROGRAM_APPLICATION_ID,
1982 PROGRAM_ID,
1983 PROGRAM_UPDATE_DATE
1984 )
1985 Select
1986 MRIRI.transaction_id,
1987 MTL_SYSTEM_ITEMS_INTERFACE_S.nextval,
1988 Null,
1989 null,
1990 'MTL_RTG_ITEM_REVS_INTERFACE',
1991 decode ( MRIRI.Organization_code, null, msg_name1,msg_name2),
1992 decode ( MRIRI.Organization_code, null, msg_text1,msg_text2),
1993 NVL(LAST_UPDATE_DATE, SYSDATE),
1994 NVL(LAST_UPDATED_BY, user_id),
1995 NVL(CREATION_DATE,SYSDATE),
1996 NVL(CREATED_BY, user_id),
1997 NVL(LAST_UPDATE_LOGIN, user_id),
1998 req_id,
1999 NVL(PROGRAM_APPLICATION_ID, prog_appid),
2000 NVL(PROGRAM_ID, prog_id),
2001 NVL(PROGRAM_UPDATE_DATE, sysdate)
2002 from MTL_RTG_ITEM_REVS_INTERFACE MRIRI
2003 where (organization_code is null or inventory_item_number is null)
2004 and transaction_id is not null
2005 and process_flag =1
2006 and
2007 (
2008 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
2009 OR ( p_batch_id = batch_id )
2010 )
2011 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
2012
2013 Update MTL_RTG_ITEM_REVS_INTERFACE MRIRI
2014 set process_flag = 3
2015 where (inventory_item_number is null or Organization_code is null)
2016 and transaction_id is not null
2017 and process_flag =1
2018 and
2019 (
2020 ( (p_batch_id IS NULL) AND (batch_id IS NULL) )
2021 OR ( p_batch_id = batch_id )
2022 )
2023 and (all_org = 1 OR (all_org = 2 AND organization_id = org_id)) ;
2024
2025 Commit;
2026
2027 return(0);
2028
2029 EXCEPTION
2030 WHEN others THEN
2031 err_text := 'Bom_Rtg_OI_Util(Process_Rtg_Revisions-'||stmt_num||') '||substrb(SQLERRM,1,1000);
2032 RETURN(SQLCODE);
2033
2034 end Process_Rtg_Revisions;
2035 end Bom_Rtg_OI_Util;