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