[Home] [Help]
PACKAGE BODY: APPS.BOM_REFERENCE_DESIGNATOR_API
Source
1 PACKAGE BODY Bom_Reference_Designator_Api AS
2 /* $Header: BOMOIRDB.pls 115.13 2002/05/08 12:14:16 pkm ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMOIRDB.pls |
9 | DESCRIPTION : This package contains functions used to assign, validate |
10 | and transact Reference Designator data in the |
11 | BOM_REF_DESGS_INTERFACE table. |
12 | Parameters: org_id organization_id |
13 | all_org process all orgs or just current org |
14 | 1 - all orgs |
15 | 2 - only org_id |
16 | prog_appid program application_id |
17 | prog_id program id |
18 | req_id request_id |
19 | user_id user id |
20 | login_id login id |
21 | History: |
22 | 11/22/93 Shreyas Shah creation date |
23 | 04/24/94 Julie Maeyama Modified code |
24 | 03/06/97 Julie Maeyama Created this new package |
25 +==========================================================================*/
26
27 /* ----------------------- Assign_Reference_Designator ----------------------*/
28 /*
29 NAME
30 Assign_Reference_Designator
31 DESCRIPTION
32 Assign defaults and ID's to reference designator record in the interface
33 table
34 REQUIRES
35 err_text out buffer to return error message
36 MODIFIES
37 BOM_REF_DESGS_INTERFACE
38 MTL_INTERFACE_ERRORS
39 RETURNS
40 0 if successful
41 SQLCODE if unsuccessful
42 NOTES
43 -----------------------------------------------------------------------------*/
44 FUNCTION Assign_Reference_Designator (
45 org_id NUMBER,
46 all_org NUMBER := 2,
47 user_id NUMBER,
48 login_id NUMBER,
49 prog_appid NUMBER,
50 prog_id NUMBER,
51 req_id NUMBER,
52 err_text IN OUT VARCHAR2
53 )
54 return INTEGER
55 IS
56 curr_org_id NUMBER;
57 curr_txn_id NUMBER;
58 ret_code NUMBER;
59 dummy_txn NUMBER;
60 commit_cnt NUMBER;
61 continue_loop BOOLEAN := TRUE;
62 total_recs NUMBER;
63 stmt_num NUMBER := 0;
64 X_dummy NUMBER;
65
66 /*
67 ** Null Component_Sequence_Id
68 */
69 CURSOR c1 IS
70 SELECT component_sequence_id CSI,
71 transaction_id TI, organization_id OI,
72 bill_sequence_id BSI, assembly_item_id AII,
73 assembly_item_number AIN, alternate_bom_designator ABD,
74 component_item_id CII, component_item_number CIN,
75 operation_seq_num OSN, transaction_type A,
76 to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED
77 FROM bom_ref_desgs_interface
78 WHERE process_flag = 1
79 AND transaction_type in (G_Insert, G_Update, G_Delete)
80 AND (UPPER(interface_entity_type) = 'BILL'
81 OR interface_entity_type is null)
82 AND component_sequence_id is null
83 AND (all_org = 1
84 OR
85 (all_org = 2 and organization_id = org_id))
86 AND rownum < G_rows_to_commit;
87 /*
88 ** Component_Sequence_Id filled in
89 */
90 CURSOR c2 IS
91 SELECT transaction_id TI, organization_id OI,
92 component_sequence_id CSI, transaction_type A
93 FROM bom_ref_desgs_interface
94 WHERE process_flag = 1
95 AND transaction_type in (G_Insert, G_Update, G_Delete)
96 AND (UPPER(interface_entity_type) = 'BILL'
97 OR interface_entity_type is null)
98 AND component_sequence_id is not null
99 AND (all_org = 1
100 OR
101 (all_org = 2 and organization_id = org_id))
102 AND rownum < G_rows_to_commit;
103 /*
104 ** Record passed assignment
105 */
106 /**************************** Removed code as a fix for 916428 ************
107 CURSOR c3 IS
108 SELECT component_sequence_id CSI
109 FROM bom_ref_desgs_interface
110 WHERE process_flag = 99
111 AND transaction_type in (G_Insert,G_Update)
112 AND (UPPER(interface_entity_type) = 'BILL'
113 OR interface_entity_type is null)
114 AND (all_org = 1
115 OR
116 (all_org = 2 and organization_id = org_id))
117 GROUP BY component_sequence_id;
118
119 *****************************************************************************/
120 BEGIN
121 /** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
122 stmt_num := 0.5 ;
123 LOOP
124 UPDATE bom_ref_desgs_interface
125 SET transaction_type = G_Insert
126 WHERE process_flag = 1
127 AND upper(transaction_type) = 'INSERT'
128 AND rownum < G_rows_to_commit;
129 EXIT when SQL%NOTFOUND;
130 COMMIT;
131 END LOOP;
132
133 /*
134 ** ALL RECORDS - Assign Org Id
135 */
136 stmt_num := 1;
137 LOOP
138 UPDATE bom_ref_desgs_interface ori
139 SET organization_id = (SELECT organization_id
140 FROM mtl_parameters a
141 WHERE a.organization_code = ori.organization_code)
142 WHERE process_flag = 1
143 AND upper(transaction_type) in (G_Insert, G_Delete, G_Update)
144 AND (UPPER(interface_entity_type) = 'BILL'
145 OR interface_entity_type is null)
146 AND organization_id is null
147 AND organization_code is not null
148 AND exists (SELECT organization_code
149 FROM mtl_parameters b
150 WHERE b.organization_code = ori.organization_code)
151 AND rownum < G_rows_to_commit;
152 EXIT when SQL%NOTFOUND;
153 COMMIT;
154 END LOOP;
155
156 /*
157 ** ALL RECORDS - Assign transaction ids
158 */
159 stmt_num := 2;
160 LOOP
161 UPDATE bom_ref_desgs_interface
162 SET transaction_id = mtl_system_items_interface_s.nextval,
163 transaction_type = upper(transaction_type)
164 WHERE transaction_id is null
165 AND (UPPER(interface_entity_type) = 'BILL'
166 OR interface_entity_type is null)
167 AND process_flag = 1
168 AND rownum < G_rows_to_commit;
169 EXIT when SQL%NOTFOUND;
170
171 COMMIT;
172 END LOOP;
173
174 /*
175 ** FOR ALL RECORDS - Get Component Sequence Id
176 */
177 /*
178 ** Check if organization id is null
179 */
180 stmt_num := 3;
181 continue_loop := TRUE;
182 WHILE continue_loop LOOP
183 commit_cnt := 0;
184 FOR c1rec in c1 LOOP
185 commit_cnt := commit_cnt + 1;
186 IF (c1rec.OI is null and c1rec.BSI is null) THEN
187 ret_code := INVPUOPI.mtl_log_interface_err(
188 org_id => NULL,
189 user_id => user_id,
190 login_id => login_id,
191 prog_appid => prog_appid,
192 prog_id => prog_id,
193 req_id => req_id,
194 trans_id => c1rec.TI,
195 error_text => err_text,
196 tbl_name => 'BOM_REF_DESGS_INTERFACE',
197 msg_name => 'BOM_ORG_ID_MISSING',
198 err_text => err_text);
199 UPDATE bom_ref_desgs_interface
200 SET process_flag = 3
201 WHERE transaction_id = c1rec.TI;
202
203 IF (ret_code <> 0) THEN
204 RETURN(ret_code);
205 END IF;
206 GOTO continue_loop;
207 END IF;
208
209 /*
210 ** Get assembly item id
211 */
212 stmt_num := 4;
213 IF (c1rec.AII is null and c1rec.BSI is null) THEN
214 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
215 org_id=> c1rec.OI,
216 flex_code => 'MSTK',
217 flex_name => c1rec.AIN,
218 flex_id => c1rec.AII,
219 set_id => -1,
220 err_text => err_text);
221 IF (ret_code <> 0) THEN
222 ret_code := INVPUOPI.mtl_log_interface_err(
223 org_id => NULL,
224 user_id => user_id,
225 login_id => login_id,
226 prog_appid => prog_appid,
227 prog_id => prog_id,
228 req_id => req_id,
229 trans_id => c1rec.TI,
230 error_text => err_text,
231 tbl_name => 'BOM_REF_DESGS_INTERFACE',
232 msg_name => 'BOM_ASSY_ITEM_MISSING',
233 err_text => err_text);
234 UPDATE bom_ref_desgs_interface
235 SET process_flag = 3
236 WHERE transaction_id = c1rec.TI;
237
238 IF (ret_code <> 0) THEN
239 RETURN(ret_code);
240 END IF;
241 GOTO continue_loop;
242 END IF;
243 END IF;
244
245 /*
246 ** Get bill sequence id
247 */
248 stmt_num := 5;
249 IF (c1rec.BSI is null) THEN
250 BEGIN
251 SELECT bill_sequence_id, assembly_type
252 INTO c1rec.BSI, X_dummy
253 FROM bom_bill_of_materials
254 WHERE organization_id = c1rec.OI
255 AND assembly_item_id = c1rec.AII
256 AND nvl(alternate_bom_designator, 'NONE') =
257 nvl(c1rec.ABD, 'NONE');
258 EXCEPTION
259 WHEN no_data_found THEN
260 ret_code := INVPUOPI.mtl_log_interface_err(
261 org_id => NULL,
262 user_id => user_id,
263 login_id => login_id,
264 prog_appid => prog_appid,
265 prog_id => prog_id,
266 req_id => req_id,
267 trans_id => c1rec.TI,
268 error_text => err_text,
269 tbl_name => 'BOM_REF_DESGS_INTERFACE',
270 msg_name => 'BOM_BILL_SEQ_MISSING',
271 err_text => err_text);
272 UPDATE bom_ref_desgs_interface
273 SET process_flag = 3
274 WHERE transaction_id = c1rec.TI;
275
276 IF (ret_code <> 0) THEN
277 return(ret_code);
278 END IF;
279 GOTO continue_loop;
280 END;
281 END IF;
282
283 /*
284 ** Get component item id
285 */
286 stmt_num := 6;
287 IF (c1rec.CII is null) THEN
288 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
289 org_id=> c1rec.OI,
290 flex_code => 'MSTK',
291 flex_name => c1rec.CIN,
292 flex_id => c1rec.CII,
293 set_id => -1,
294 err_text => err_text);
295 IF (ret_code <> 0) THEN
296 ret_code := INVPUOPI.mtl_log_interface_err(
297 org_id => NULL,
298 user_id => user_id,
299 login_id => login_id,
300 prog_appid => prog_appid,
301 prog_id => prog_id,
302 req_id => req_id,
303 trans_id => c1rec.TI,
304 error_text => err_text,
305 tbl_name => 'BOM_REF_DESGS_INTERFACE',
306 msg_name => 'BOM_COMP_ID_MISSING',
307 err_text => err_text);
308 UPDATE bom_ref_desgs_interface
309 SET process_flag = 3
310 WHERE transaction_id = c1rec.TI;
311
312 IF (ret_code <> 0) THEN
313 return(ret_code);
314 END IF;
315 GOTO continue_loop;
316 END IF;
317 END IF;
318
319 /*
320 ** Get component sequence id
321 */
322 stmt_num := 7;
323 BEGIN
327 WHERE bill_sequence_id = c1rec.BSI
324 SELECT component_sequence_id
325 INTO c1rec.CSI
326 FROM bom_inventory_components
328 AND component_item_id = c1rec.CII
329 AND operation_seq_num = c1rec.OSN
330 AND effectivity_date = to_date(c1rec.ED,'YYYY/MM/DD HH24:MI:SS');
331 EXCEPTION
332 WHEN no_data_found THEN
333 ret_code := INVPUOPI.mtl_log_interface_err(
334 org_id => NULL,
335 user_id => user_id,
336 login_id => login_id,
337 prog_appid => prog_appid,
338 prog_id => prog_id,
339 req_id => req_id,
340 trans_id => c1rec.TI,
341 error_text => err_text,
342 tbl_name => 'BOM_REF_DESGS_INTERFACE',
343 msg_name => 'BOM_COMP_SEQ_MISSING',
344 err_text => err_text);
345 UPDATE bom_ref_desgs_interface
346 SET process_flag = 3
347 WHERE transaction_id = c1rec.TI;
348
349 IF (ret_code <> 0) THEN
350 RETURN(ret_code);
351 END IF;
352 GOTO continue_loop;
353 END;
354
355 stmt_num := 8;
356 UPDATE bom_ref_desgs_interface
357 SET component_sequence_id = c1rec.CSI,
358 assembly_item_id = c1rec.AII,
359 component_item_id = c1rec.CII,
360 bill_sequence_id = c1rec.BSI,
361 organization_id = c1rec.OI
362 WHERE transaction_id = c1rec.TI;
363
364 <<continue_loop>>
365 NULL;
366 END LOOP;
367
368 stmt_num := 9;
369 COMMIT;
370
371 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
372 continue_loop := FALSE;
373 END IF;
374 END LOOP;
375
376 /*
377 ** FOR ALL RECORDS - Set defaults and process_flag for valid records
378 */
379 stmt_num := 10;
380 continue_loop := TRUE;
381 WHILE continue_loop LOOP
382 commit_cnt := 0;
383 FOR c2rec in c2 LOOP
384 commit_cnt := commit_cnt + 1;
385 IF (c2rec.A = G_Insert) THEN
386 stmt_num := 11;
387 UPDATE bom_ref_desgs_interface
388 SET process_flag = 2, -- Changed from 99 to 2, bug 1342291
389 acd_type = null,
390 last_update_date = nvl(last_update_date,sysdate),
391 last_updated_by = nvl(last_updated_by,user_id),
392 creation_date = nvl(creation_date,sysdate),
393 created_by = nvl(created_by,user_id),
394 last_update_login = nvl(last_update_login, user_id),
395 request_id = nvl(request_id, req_id),
396 program_application_id = nvl(program_application_id,
397 prog_appid),
398 program_id = nvl(program_id, prog_id),
399 program_update_date = nvl(program_update_date, sysdate)
400 WHERE transaction_id = c2rec.TI;
401 ELSIF (c2rec.A = G_Update) THEN
402 stmt_num := 12;
403 UPDATE bom_ref_desgs_interface
404 SET process_flag = 2, -- Changed from 99 to 2, bug 1342291
405 last_update_date = nvl(last_update_date,sysdate),
406 last_updated_by = nvl(last_updated_by,user_id),
407 last_update_login = nvl(last_update_login, user_id)
408 WHERE transaction_id = c2rec.TI;
409
410 IF (SQL%NOTFOUND) THEN
411 err_text := 'Bom_Reference_Designator_Api('||stmt_num||')'||
412 substrb(SQLERRM, 1, 60);
413 RETURN(SQLCODE);
414 END IF;
415 ELSIF (c2rec.A = G_Delete) THEN
416 stmt_num := 13;
417 UPDATE bom_ref_desgs_interface
418 SET process_flag = 2
419 WHERE transaction_id = c2rec.TI;
420
421 IF (SQL%NOTFOUND) THEN
422 err_text := 'Bom_Reference_Designator_Api('||stmt_num||')'||
423 substrb(SQLERRM, 1, 60);
424 RETURN(SQLCODE);
425 END IF;
426 END IF;
427
428 <<continue_loop2>>
429 NULL;
430 END LOOP;
431
432 stmt_num := 14;
433 COMMIT;
434
435 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
436 continue_loop := FALSE;
437 END IF;
438
439 END LOOP;
440
441 commit_cnt := 0;
442
443 /******************* Removed as a fix for bug 916428 *************************
444 stmt_num := 16;
445 FOR c3rec in c3 LOOP
446 commit_cnt := commit_cnt + 1;
447 SELECT mtl_system_items_interface_s.nextval
448 INTO dummy_txn
449 FROM sys.dual;
450
451 stmt_num := 17;
452
453 -- Only INSERTS and UPDATES have process_flag = 99
454
455 UPDATE bom_ref_desgs_interface
456 SET transaction_id = dummy_txn,
457 process_flag = 2
458 WHERE component_sequence_id = c3rec.CSI
459 AND (UPPER(interface_entity_type) = 'BILL'
460 OR interface_entity_type is null)
461 AND process_flag = 99;
462
466 END IF;
463 IF (commit_cnt = G_rows_to_commit) THEN
464 COMMIT;
465 commit_cnt := 0;
467 END LOOP;
468 ***************************************************************************/
469
470 stmt_num := 18;
471 COMMIT;
472
473 RETURN (0);
474 EXCEPTION
475 WHEN others THEN
476 err_text := 'Bom_Reference_Designator_Api(Assign-'||stmt_num||') '||substrb(SQLERRM,1,500);
477 RETURN(SQLCODE);
478 END Assign_Reference_Designator;
479
480 /* ---------------------- Verify_Unique_Designator --------------------------*/
481 /*
482 NAME
483 Verify_Unique_Designator - Verify reference designator is unique for its
484 component
485 DESCRIPTION
486 Verify that the reference designator is unique in the production table
487 Verify that the reference designator is unique in the interface table
488 REQUIRES
489 trans_id transaction_id
490 err_text out buffer to return error message
491 MODIFIES
492 RETURNS
493 0 if successful
494 SQLCODE if unsuccessful
495 NOTES
496 -----------------------------------------------------------------------------*/
497 FUNCTION Verify_Unique_Designator (
498 trans_id NUMBER,
499 err_text OUT VARCHAR2
500 )
501 return INTEGER
502 IS
503 dummy NUMBER;
504 not_unique EXCEPTION;
505 stmt_num NUMBER := 0;
506 BEGIN
507 /*
508 ** First check in prod tables.
509 ** If it's an UPDATE, then this check is unnecessary if new_designator is
510 ** not filled in.
511 */
512 stmt_num := 1;
513 BEGIN
514 SELECT 1
515 INTO dummy
516 FROM bom_reference_designators a, bom_ref_desgs_interface b
517 WHERE b.transaction_id = trans_id
518 AND (b.transaction_type = G_Insert
519 OR (b.transaction_type= G_Update
520 AND b.new_designator is not null))
521 AND (UPPER(b.interface_entity_type) = 'BILL'
522 OR b.interface_entity_type is null)
523 AND a.component_sequence_id = b.component_sequence_id
524 AND a.component_reference_designator = decode(b.transaction_type,
525 G_Insert, b.component_reference_designator, G_Update,
526 b.new_designator)
527 AND rownum = 1;
528 RAISE not_unique;
529 EXCEPTION
530 WHEN no_data_found THEN
531 NULL;
532 WHEN not_unique THEN
533 RAISE not_unique;
534 END;
535 /*
536 ** Check in interface table
537 */
538 stmt_num := 2;
539 SELECT count(*)
540 INTO dummy
541 FROM bom_ref_desgs_interface a
542 WHERE transaction_id = trans_id
543 AND (transaction_type = G_Insert
544 OR (transaction_type= G_Update AND new_designator is not null))
545 AND (UPPER(a.interface_entity_type) = 'BILL'
546 OR a.interface_entity_type is null)
547 AND exists
548 (SELECT 'same designator'
549 FROM bom_ref_desgs_interface b
550 WHERE b.rowid <> a.rowid
551 AND (b.transaction_type = G_Insert
552 OR (b.transaction_type = G_Update
553 AND b.new_designator is not null))
554 AND (UPPER(b.interface_entity_type) = 'BILL'
555 OR b.interface_entity_type is null)
556 AND decode(b.transaction_type, G_Insert,
557 b.component_reference_designator, G_Update,
558 b.new_designator) = decode(a.transaction_type, G_Insert,
559 a.component_reference_designator, G_Update,
560 a.new_designator)
561 AND b.component_sequence_id = a.component_sequence_id
562 AND b.process_flag not in (3,7))
563 AND process_flag not in (3,7);
564
565 IF (dummy > 0) THEN
566 RAISE not_unique;
567 ELSE
568 RETURN(0);
569 END IF;
570 EXCEPTION
571 WHEN Not_Unique THEN
572 err_text := 'Bom_Reference_Designator_Api(Unique) ' ||'Duplicate reference designators';
573 RETURN(9999);
574 WHEN others THEN
575 err_text := 'Bom_Reference_Designator_Api(Unique-'||stmt_num||') '||substrb(SQLERRM,1,500);
576 RETURN(SQLCODE);
577 END Verify_Unique_Designator;
578
579 /* --------------------------- Count_Designators -------------------------- */
580 /*
581 NAME
582 Count_Designators
583 DESCRIPTION
584 Ensure that the number of ref desgs is same as component quantity
585 since Quantity Related = Yes
586 REQUIRES
587 trans_id transaction_id
588 cmp_seq_id component_sequence_id
589 quantity component_quantity
590 err_text out buffer to return error message
591 MODIFIES
592 RETURNS
593 0 if successful
594 SQLCODE if unsuccessful
595 NOTES
596 -----------------------------------------------------------------------------*/
597 FUNCTION Count_Designators (
598 trans_id NUMBER,
599 cmp_seq_id NUMBER,
600 quantity NUMBER,
601 err_text OUT VARCHAR2
602 )
603 return INTEGER
604 IS
605 ref_qty NUMBER;
606 int_ref_qty NUMBER;
607 int_del_ref_qty NUMBER;
608 stmt_num NUMBER := 0;
609 BEGIN
610
611 stmt_num := 1;
612 SELECT count(*)
613 INTO ref_qty
614 FROM bom_reference_designators
618 SELECT count(*)
615 WHERE component_sequence_id = cmp_seq_id;
616
617 stmt_num := 2;
619 INTO int_ref_qty
620 FROM bom_ref_desgs_interface
621 WHERE component_sequence_id = cmp_seq_id
622 AND transaction_type = G_Insert
623 AND (UPPER(interface_entity_type) = 'BILL'
624 OR interface_entity_type is null)
625 AND process_flag not in (3,7);
626
627 stmt_num := 3;
628 SELECT count(*)
629 INTO int_del_ref_qty
630 FROM bom_ref_desgs_interface
631 WHERE component_sequence_id = cmp_seq_id
632 AND transaction_type = G_Delete
633 AND (UPPER(interface_entity_type) = 'BILL'
634 OR interface_entity_type is null)
635 AND process_flag not in (3,7);
636
637 IF (ref_qty + int_ref_qty - int_del_ref_qty <> quantity) THEN
638 err_text := 'Bom_Reference_Designator_Api(Count) ' || 'Number of ref desg not equal to component qty';
639 return (9999);
640 END IF;
641
642 RETURN(0);
643
644 EXCEPTION
645 WHEN others THEN
646 err_text := 'Bom_Reference_Designator_Api(Count-'||stmt_num||') '||substrb(SQLERRM,1,60);
647 return(SQLCODE);
648 END Count_Designators;
649
650
651 /*---------------------- Validate_Reference_Designator ---------------------*/
652 /*
653 NAME
654 Validate_Reference_Designator
655 DESCRIPTION
656 Validate component sequence id
657 Verify there are no reference designators for Planning bills or
658 non-Standard components
659 Verify reference designator is unique for a component
660 If Quantity Related, then number of reference designators must equal
661 Component Quantity
662 REQUIRES
663 err_text out buffer to return error message
664 MODIFIES
665 MTL_INTERFACE_ERRORS
666 RETURNS
667 0 if successful
668 SQLCODE if unsuccessful
669 NOTES
670 -----------------------------------------------------------------------------*/
671 FUNCTION Validate_Reference_Designator (
672 org_id NUMBER,
673 all_org NUMBER := 2,
674 user_id NUMBER,
675 login_id NUMBER,
676 prog_appid NUMBER,
677 prog_id NUMBER,
678 req_id NUMBER,
679 err_text IN OUT VARCHAR2
680 )
681 return INTEGER
682 IS
683 ret_code NUMBER;
684 stmt_num NUMBER := 0;
685 dummy NUMBER;
686 assy_id_dummy NUMBER;
687 org_id_dummy NUMBER;
688 assy_type_dummy NUMBER;
689 comp_id_dummy NUMBER;
690 commit_cnt NUMBER;
691 comp_type NUMBER;
692 continue_loop BOOLEAN := TRUE;
693 total_recs NUMBER;
694 X_creation_date DATE;
695 X_created_by NUMBER;
696 X_ref_designator_comment VARCHAR2(240);
697 X_acd_type NUMBER;
698 X_change_notice VARCHAR2(10);
699 X_attribute_category VARCHAR2(30);
700 X_attribute1 VARCHAR2(150);
701 X_attribute2 VARCHAR2(150);
702 X_attribute3 VARCHAR2(150);
703 X_attribute4 VARCHAR2(150);
704 X_attribute5 VARCHAR2(150);
705 X_attribute6 VARCHAR2(150);
706 X_attribute7 VARCHAR2(150);
707 X_attribute8 VARCHAR2(150);
708 X_attribute9 VARCHAR2(150);
709 X_attribute10 VARCHAR2(150);
710 X_attribute11 VARCHAR2(150);
711 X_attribute12 VARCHAR2(150);
712 X_attribute13 VARCHAR2(150);
713 X_attribute14 VARCHAR2(150);
714 X_attribute15 VARCHAR2(150);
715 X_request_id NUMBER;
716 X_program_application_id NUMBER;
717 X_program_id NUMBER;
718 X_program_update_date DATE;
719 X_component_quantity NUMBER;
720 X_quantity_related NUMBER;
721 /*
722 ** Get UPDATE and DELETEs for row by row processing
723 */
724 CURSOR c2 IS
725 SELECT component_sequence_id CSI, component_reference_designator CRD,
726 ref_designator_comment RDC,
727 creation_date CD, created_by CB, change_notice CN,
728 attribute_category AC, attribute1 A1, attribute2 A2,
729 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
730 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
731 attribute11 A11, attribute12 A12, attribute13 A13,
732 attribute14 A14,attribute15 A15,
733 request_id RI, program_application_id PAI, program_id PI,
734 program_update_date PUD, acd_type ACD,
735 transaction_id TI, transaction_type A
736 FROM bom_ref_desgs_interface
737 WHERE process_flag = 2
738 AND transaction_type in (G_Update, G_Delete)
739 AND (UPPER(interface_entity_type) = 'BILL'
740 OR interface_entity_type is null)
741 AND rownum < G_rows_to_commit;
742 /*
743 ** Get UPDATE and INSERTs for set processing
744 */
745 CURSOR c1 IS
746 SELECT component_sequence_id CSI, count(*) CNT,
747 transaction_id TI, assembly_item_id AII,
748 organization_id OI
749 FROM bom_ref_desgs_interface
753 OR interface_entity_type is null)
750 WHERE process_flag = 2
751 AND transaction_type in (G_Insert, G_Update)
752 AND (UPPER(interface_entity_type) = 'BILL'
754 GROUP BY transaction_id, component_sequence_id,
755 organization_id, assembly_item_id;
756 /*
757 ** Get INSERTS for set processing
758 */
759 CURSOR c3 IS
760 SELECT change_notice CN, transaction_id TI,
761 organization_id OI
762 FROM bom_ref_desgs_interface
763 WHERE process_flag = 2
764 AND transaction_type in (G_Insert)
765 AND (UPPER(interface_entity_type) = 'BILL'
766 OR interface_entity_type is null);
767
768
769 BEGIN
770 /*
771 ** FOR UPDATES and DELETES
772 */
773 continue_loop := TRUE;
774 WHILE continue_loop LOOP
775 commit_cnt := 0;
776 FOR c2rec IN c2 LOOP
777 commit_cnt := commit_cnt + 1;
778 stmt_num := 1;
779 /*
780 ** Check if implemented record exists in Production
781 */
782 BEGIN
783 SELECT brd.creation_date, brd.created_by,
784 brd.ref_designator_comment, brd.acd_type,
785 brd.change_notice,
786 brd.attribute_category, brd.attribute1,
787 brd.attribute2, brd.attribute3, brd.attribute4,
788 brd.attribute5, brd.attribute6, brd.attribute7,
789 brd.attribute8, brd.attribute9,
790 brd.attribute10, brd.attribute11, brd.attribute12,
791 brd.attribute13,
792 brd.attribute14, brd.attribute15, brd.request_id,
793 brd.program_application_id, brd.program_id,
794 brd.program_update_date, bic.quantity_related,
795 bic.component_quantity
796 INTO X_creation_date, X_created_by,
797 X_ref_designator_comment, X_acd_type,
798 X_change_notice,
799 X_attribute_category, X_attribute1,
800 X_attribute2, X_attribute3, X_attribute4, X_attribute5,
801 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
802 X_attribute10, X_attribute11, X_attribute12, X_attribute13,
803 X_attribute14, X_attribute15, X_request_id,
804 X_program_application_id, X_program_id,
805 X_program_update_date, X_quantity_related,
806 X_component_quantity
807 FROM bom_reference_designators brd,
808 bom_inventory_components bic
809 WHERE brd.component_sequence_id = c2rec.CSI
810 AND brd.component_reference_designator = c2rec.CRD
811 AND brd.component_sequence_id = bic.component_sequence_id
812 AND bic.implementation_date is not null;
813
814 EXCEPTION
815 WHEN No_Data_Found THEN
816 ret_code := INVPUOPI.mtl_log_interface_err(
817 org_id => NULL,
818 user_id => user_id,
819 login_id => login_id,
820 prog_appid => prog_appid,
821 prog_id => prog_id,
822 req_id => req_id,
823 trans_id => c2rec.TI,
824 error_text => err_text,
825 tbl_name => 'BOM_REF_DESGS_INTERFACE',
826 msg_name => 'BOM_REF_DESG_RECORD_MISSING',
827 err_text => err_text);
828
829 UPDATE bom_ref_desgs_interface
830 SET process_flag = 3
831 WHERE transaction_id = c2rec.TI;
832
833 IF (ret_code <> 0) THEN
834 return(ret_code);
835 END IF;
836 GOTO continue_loop1;
837 END;
838 /*
839 ** FOR UPDATES
840 */
841 IF (c2rec.A = G_Update) THEN
842 /*
843 ** Check if column is non-updatable and give an error if user filled it in
844 */
845 stmt_num := 2;
846 IF (c2rec.CD is not null
847 OR c2rec.CB is not null
848 OR c2rec.ACD is not null
849 OR c2rec.CN is not null) THEN
850 ret_code := INVPUOPI.mtl_log_interface_err(
851 org_id => 999999,
852 user_id => user_id,
853 login_id => login_id,
854 prog_appid => prog_appid,
855 prog_id => prog_id,
856 req_id => req_id,
857 trans_id => c2rec.TI,
858 error_text => err_text,
859 tbl_name => 'BOM_REF_DESGS_INTERFACE',
860 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
861 err_text => err_text);
862
863 UPDATE bom_ref_desgs_interface
864 SET process_flag = 3
865 WHERE transaction_id = c2rec.TI;
866
867 IF (ret_code <> 0) THEN
868 return(ret_code);
869 END IF;
870 GOTO continue_loop1;
871 END IF;
872 /*
873 ** Update interface record with production record's values
874 */
875 stmt_num := 3;
876 UPDATE bom_ref_desgs_interface
877 SET creation_date = X_creation_date,
878 created_by = X_created_by,
882 attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
879 ref_designator_comment = nvl(c2rec.RDC,
880 X_ref_designator_comment),
881 change_notice = X_change_notice,
883 X_attribute_category, c2rec.AC),
884 attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
885 X_attribute1, c2rec.A1),
886 attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
887 X_attribute2, c2rec.A2),
888 attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
889 X_attribute3, c2rec.A3),
890 attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
891 X_attribute4, c2rec.A4),
892 attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
893 X_attribute5, c2rec.A5),
894 attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
895 X_attribute6, c2rec.A6),
896 attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
897 X_attribute7, c2rec.A7),
898 attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
899 X_attribute8, c2rec.A8),
900 attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
901 X_attribute9, c2rec.A9),
902 attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
903 X_attribute10, c2rec.A10),
904 attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
905 X_attribute11, c2rec.A11),
906 attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
907 X_attribute12, c2rec.A12),
908 attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
909 X_attribute13, c2rec.A13),
910 attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
911 X_attribute14, c2rec.A14),
912 attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
913 X_attribute15, c2rec.A15),
914 request_id = decode(c2rec.RI, G_NullChar, '', NULL,
915 X_request_id, c2rec.RI),
916 program_application_id = decode(c2rec.PAI, G_NullNum,
917 '', NULL, X_program_application_id, c2rec.PAI),
918 program_id = decode(c2rec.PI, G_NullNum, '', NULL,
919 X_program_id, c2rec.PI),
920 program_update_date = decode(c2rec.PUD, G_NullDate, '',
921 NULL,X_program_update_date, c2rec.PUD)
922 WHERE transaction_id = c2rec.TI
923 AND transaction_type = G_Update;
924 ELSIF (c2rec.A = G_Delete) THEN
925 /*
926 ** Count reference designators if quantity related is Yes
927 */
928 stmt_num := 4;
929 IF (X_quantity_related = 1) THEN
930 ret_code := Count_Designators (
931 trans_id => c2rec.TI,
932 cmp_seq_id => c2rec.CSI,
933 quantity => X_component_quantity,
934 err_text => err_text);
935 IF (ret_code <> 0) THEN
936 ret_code := INVPUOPI.mtl_log_interface_err(
937 org_id => org_id,
938 user_id => user_id,
939 login_id => login_id,
940 prog_appid => prog_appid,
941 prog_id => prog_id,
942 req_id => req_id,
943 trans_id => c2rec.TI,
944 error_text => err_text,
945 tbl_name => 'BOM_REF_DESGS_INTERFACE',
946 msg_name => 'BOM_REF_DESG_COUNT_INVALID',
947 err_text => err_text);
948 UPDATE bom_ref_desgs_interface
949 SET process_flag = 3
950 WHERE transaction_id = c2rec.TI;
951
952 IF (ret_code <> 0) THEN
953 return(ret_code);
954 END IF;
955 GOTO continue_loop1;
956 END IF;
957 END IF;
958 /*
959 ** Set Process Flag to 4 for "Deletes"
960 */
961 stmt_num := 5;
962 UPDATE bom_ref_desgs_interface
963 SET process_flag = 4
964 WHERE transaction_id = c2rec.TI;
965 END IF;
966 <<continue_loop1>>
967 NULL;
968 END LOOP;
969
970 stmt_num := 6;
971 COMMIT;
972 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
973 continue_loop := FALSE;
974 END IF;
975
976 END LOOP;
977
978 commit_cnt := 0;
979
980 /*
981 ** FOR CREATE operation
982 */
983
984 FOR c3rec in c3 LOOP
985 /*
986 ** Verfify Change_Notice
987 */
988 stmt_num := 6.5;
989
990 If (c3rec.CN is not NULL) THEN
991 BEGIN
992 SELECT 1
993 INTO dummy
994 FROM eng_engineering_changes
995 WHERE organization_id = c3rec.OI
999 ret_code := INVPUOPI.mtl_log_interface_err(
996 AND change_notice = c3rec.CN;
997 EXCEPTION
998 WHEN no_data_found THEN
1000 org_id => c3rec.OI,
1001 user_id => user_id,
1002 login_id => login_id,
1003 prog_appid => prog_appid,
1004 prog_id => prog_id,
1005 req_id => req_id,
1006 trans_id => c3rec.TI,
1007 error_text => err_text,
1008 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1009 msg_name => 'ENG_PARENTECO_NOT_EXIST',
1010 err_text => err_text);
1011 UPDATE bom_ref_desgs_interface
1012 SET process_flag = 3
1013 WHERE transaction_id = c3rec.TI;
1014
1015 IF (ret_code <> 0) THEN
1016 RETURN(ret_code);
1017 END IF;
1018 GOTO continue_loop3;
1019 END;
1020 END IF;
1021
1022 /* continue the validation, not setting process_flag to 4 */
1023
1024 <<continue_loop3>>
1025 IF (commit_cnt = G_rows_to_commit) THEN
1026 COMMIT;
1027 commit_cnt := 0;
1028 END IF;
1029 END LOOP;
1030
1031
1032 commit_cnt := 0;
1033
1034 FOR c1rec in c1 LOOP
1035 /*
1036 ** Check for null ref desgs
1037 */
1038 stmt_num := 7;
1039
1040 commit_cnt := commit_cnt + 1;
1041 SELECT count(*)
1042 INTO dummy
1043 FROM bom_ref_desgs_interface
1044 WHERE transaction_id = c1rec.TI
1045 AND component_reference_designator is null;
1046
1047 IF (dummy = 0) THEN
1048 null;
1049 ELSE
1050 ret_code := INVPUOPI.mtl_log_interface_err(
1051 org_id => org_id,
1052 user_id => user_id,
1053 login_id => login_id,
1054 prog_appid => prog_appid,
1055 prog_id => prog_id,
1056 req_id => req_id,
1057 trans_id => c1rec.TI,
1058 error_text => err_text,
1059 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1060 msg_name => 'BOM_NULL_REF_DESGS',
1061 err_text => err_text);
1062 UPDATE bom_ref_desgs_interface
1063 SET process_flag = 3
1064 WHERE transaction_id = c1rec.TI;
1065
1066 IF (ret_code <> 0) THEN
1067 return(ret_code);
1068 END IF;
1069 GOTO continue_loop;
1070 END IF;
1071
1072
1073 /*
1074 ** Check if Component_Sequence_Id exists
1075 */
1076 stmt_num := 8;
1077
1078 BEGIN
1079 SELECT bbom.assembly_item_id, bbom.organization_id,
1080 bbom.assembly_type, bic.component_item_id,
1081 bic.bom_item_type, mtl.bom_item_type,
1082 bic.component_quantity, bic.quantity_related
1083 INTO assy_id_dummy, org_id_dummy, assy_type_dummy,
1084 comp_id_dummy, comp_type, dummy,
1085 X_component_quantity, X_quantity_related
1086 FROM bom_inventory_components bic,
1087 bom_bill_of_materials bbom,
1088 mtl_system_items mtl
1089 WHERE bic.component_sequence_id = c1rec.CSI
1090 AND bic.implementation_date is not null
1091 AND bbom.bill_sequence_id = bic.bill_sequence_id
1092 AND mtl.inventory_item_id = bbom.assembly_item_id
1093 AND mtl.organization_id = bbom.organization_id;
1094
1095 EXCEPTION
1096 WHEN no_data_found THEN
1097 ret_code := INVPUOPI.mtl_log_interface_err(
1098 org_id => c1rec.OI,
1099 user_id => user_id,
1100 login_id => login_id,
1101 prog_appid => prog_appid,
1102 prog_id => prog_id,
1103 req_id => req_id,
1104 trans_id => c1rec.TI,
1105 error_text => err_text,
1106 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1107 msg_name => 'BOM_COMP_SEQ_ID_INVALID',
1108 err_text => err_text);
1109 UPDATE bom_ref_desgs_interface
1110 SET process_flag = 3
1111 WHERE transaction_id = c1rec.TI;
1112
1113 IF (ret_code <> 0) THEN
1114 RETURN(ret_code);
1115 END IF;
1116 GOTO continue_loop;
1117 END;
1118 /*
1119 ** Reference Designators not allowed for planning bills or
1120 ** non-standard components or Product Families
1121 */
1122 stmt_num := 9;
1123 IF (dummy in (3,5) OR comp_type <> 4) THEN
1124 ret_code := INVPUOPI.mtl_log_interface_err(
1125 org_id => org_id_dummy,
1126 user_id => user_id,
1127 login_id => login_id,
1128 prog_appid => prog_appid,
1129 prog_id => prog_id,
1130 req_id => req_id,
1131 trans_id => c1rec.TI,
1132 error_text => err_text,
1136 UPDATE bom_ref_desgs_interface
1133 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1134 msg_name => 'BOM_NO_REF_DESGS_ALLOWED',
1135 err_text => err_text);
1137 SET process_flag = 3
1138 WHERE transaction_id = c1rec.TI;
1139
1140 IF (ret_code <> 0) THEN
1141 RETURN(ret_code);
1142 END IF;
1143 GOTO continue_loop;
1144 END IF;
1145
1146 /*
1147 ** Verify Reference Designator is unique for a component
1148 */
1149 stmt_num := 10;
1150 ret_code := Verify_Unique_Designator (
1151 trans_id => c1rec.TI,
1152 err_text => err_text);
1153 IF (ret_code <> 0) THEN
1154 ret_code := INVPUOPI.mtl_log_interface_err(
1155 org_id => org_id_dummy,
1156 user_id => user_id,
1157 login_id => login_id,
1158 prog_appid => prog_appid,
1159 prog_id => prog_id,
1160 req_id => req_id,
1161 trans_id => c1rec.TI,
1162 error_text => err_text,
1163 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1164 msg_name => 'BOM_DUPLICATE_REF_DESG',
1165 err_text => err_text);
1166 UPDATE bom_ref_desgs_interface
1167 SET process_flag = 3
1168 WHERE transaction_id = c1rec.TI;
1169
1170 IF (ret_code <> 0) THEN
1171 RETURN(ret_code);
1172 END IF;
1173 GOTO continue_loop;
1174 END IF;
1175
1176 /*
1177 ** Count reference designators if quantity related is Yes
1178 */
1179 stmt_num := 11;
1180 IF (X_quantity_related = 1) THEN
1181 ret_code := Count_Designators (
1182 trans_id => c1rec.TI,
1183 cmp_seq_id => c1rec.CSI,
1184 quantity => X_component_quantity,
1185 err_text => err_text);
1186 IF (ret_code <> 0) THEN
1187 ret_code := INVPUOPI.mtl_log_interface_err(
1188 org_id => org_id,
1189 user_id => user_id,
1190 login_id => login_id,
1191 prog_appid => prog_appid,
1192 prog_id => prog_id,
1193 req_id => req_id,
1194 trans_id => c1rec.TI,
1195 error_text => err_text,
1196 tbl_name => 'BOM_REF_DESGS_INTERFACE',
1197 msg_name => 'BOM_REF_DESG_COUNT_INVALID',
1198 err_text => err_text);
1199 UPDATE bom_ref_desgs_interface
1200 SET process_flag = 3
1201 WHERE transaction_id = c1rec.TI;
1202
1203 IF (ret_code <> 0) THEN
1204 return(ret_code);
1205 END IF;
1206 GOTO continue_loop;
1207 END IF;
1208 END IF;
1209
1210 stmt_num := 12;
1211 UPDATE bom_ref_desgs_interface
1212 SET process_flag = 4
1213 WHERE transaction_id = c1rec.TI;
1214
1215 <<continue_loop>>
1216 IF (commit_cnt = G_rows_to_commit) THEN
1217 COMMIT;
1218 commit_cnt := 0;
1219 END IF;
1220 END LOOP;
1221
1222 stmt_num := 13;
1223 COMMIT;
1224
1225 RETURN(0);
1226
1227 EXCEPTION
1228 WHEN others THEN
1229 err_text := 'Bom_Reference_Designator_Api(Validate-'||stmt_num||') '||substrb(SQLERRM,1,500);
1230 RETURN(SQLCODE);
1231 END Validate_Reference_Designator;
1232
1233
1234 /* --------------------- Transact_Reference_Designator ----------------------*/
1235 /*
1236 NAME
1237 Transact_Reference_Designator
1238 DESCRIPTION
1239 Insert, update and delete reference designator data from the interface
1240 table, BOM_REF_DESGS_INTERFACE, into the production table,
1241 BOM_REFERENCE_DESIGNATORS.
1242 REQUIRES
1243 prog_appid Program application id
1244 prog_id Program id
1245 req_id Request id
1246 user_id User id
1247 login_id Login id
1248 MODIFIES
1249 BOM_REFERENCE_DESIGNATORS
1250 BOM_REF_DESGS_INTERFACE
1251 RETURNS
1252 0 if successful
1253 SQLCODE if error
1254 NOTES
1255 -----------------------------------------------------------------------------*/
1256 FUNCTION Transact_Reference_Designator
1257 ( user_id NUMBER,
1258 login_id NUMBER,
1259 prog_appid NUMBER,
1260 prog_id NUMBER,
1261 req_id NUMBER,
1262 err_text OUT VARCHAR2)
1263 return integer
1264 IS
1265 stmt_num NUMBER := 0;
1266 continue_loop BOOLEAN := TRUE;
1267 commit_cnt NUMBER;
1268
1269 /*
1270 ** Select "Update" reference designator records
1271 */
1272 CURSOR c1 IS
1273 SELECT component_sequence_id CSI, component_reference_designator CRD,
1274 new_designator ND, ref_designator_comment RDC,
1278 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
1275 last_update_date LUD, last_updated_by LUB,
1276 last_update_login LUL,
1277 attribute_category AC, attribute1 A1, attribute2 A2,
1279 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
1280 attribute11 A11, attribute12 A12, attribute13 A13,
1281 attribute14 A14, attribute15 A15, request_id RI,
1282 program_application_id PAI, program_id PI,
1283 program_update_date PUD, transaction_id TI
1284 FROM bom_ref_desgs_interface
1285 WHERE process_flag = 4
1286 AND transaction_type = G_Update
1287 AND (UPPER(interface_entity_type) = 'BILL'
1288 OR interface_entity_type is null)
1289 AND rownum < G_rows_to_commit;
1290 /*
1291 ** Select "Delete" reference designators records
1292 */
1293 CURSOR c2 IS
1294 SELECT component_sequence_id CSI, component_reference_designator CRD,
1295 transaction_id TI
1296 FROM bom_ref_desgs_interface
1297 WHERE process_flag = 4
1298 AND transaction_type = G_Delete
1299 AND (UPPER(interface_entity_type) = 'BILL'
1300 OR interface_entity_type is null)
1301 AND rownum < G_rows_to_commit;
1302
1303 BEGIN
1304 /*
1305 ** Insert Reference Designators
1306 */
1307 stmt_num := 1;
1308 LOOP
1309 INSERT into BOM_REFERENCE_DESIGNATORS
1310 (
1311 COMPONENT_REFERENCE_DESIGNATOR,
1312 LAST_UPDATE_DATE,
1313 LAST_UPDATED_BY,
1314 CREATION_DATE,
1315 CREATED_BY,
1316 LAST_UPDATE_LOGIN,
1317 REF_DESIGNATOR_COMMENT,
1318 CHANGE_NOTICE,
1319 ATTRIBUTE_CATEGORY,
1320 ATTRIBUTE1,
1321 ATTRIBUTE2,
1322 ATTRIBUTE3,
1323 ATTRIBUTE4,
1324 ATTRIBUTE5,
1325 ATTRIBUTE6,
1326 ATTRIBUTE7,
1327 ATTRIBUTE8,
1328 ATTRIBUTE9,
1329 ATTRIBUTE10,
1330 ATTRIBUTE11,
1331 ATTRIBUTE12,
1332 ATTRIBUTE13,
1333 ATTRIBUTE14,
1334 ATTRIBUTE15,
1335 COMPONENT_SEQUENCE_ID,
1336 REQUEST_ID,
1337 PROGRAM_APPLICATION_ID,
1338 PROGRAM_ID ,
1339 PROGRAM_UPDATE_DATE
1340 )
1341 SELECT
1342 COMPONENT_REFERENCE_DESIGNATOR,
1343 LAST_UPDATE_DATE,
1344 LAST_UPDATED_BY,
1345 CREATION_DATE,
1346 CREATED_BY,
1347 LAST_UPDATE_LOGIN,
1348 REF_DESIGNATOR_COMMENT,
1349 CHANGE_NOTICE,
1350 ATTRIBUTE_CATEGORY,
1351 ATTRIBUTE1,
1352 ATTRIBUTE2,
1353 ATTRIBUTE3,
1354 ATTRIBUTE4,
1355 ATTRIBUTE5,
1356 ATTRIBUTE6,
1357 ATTRIBUTE7,
1358 ATTRIBUTE8,
1359 ATTRIBUTE9,
1360 ATTRIBUTE10,
1361 ATTRIBUTE11,
1362 ATTRIBUTE12,
1363 ATTRIBUTE13,
1364 ATTRIBUTE14,
1365 ATTRIBUTE15,
1366 COMPONENT_SEQUENCE_ID,
1367 REQUEST_ID,
1368 PROGRAM_APPLICATION_ID,
1369 PROGRAM_ID ,
1370 PROGRAM_UPDATE_DATE
1371 FROM bom_ref_desgs_interface
1372 WHERE process_flag = 4
1373 AND transaction_type = G_Insert
1374 AND (UPPER(interface_entity_type) = 'BILL'
1375 OR interface_entity_type is null)
1376 AND rownum < 500;
1377
1378 EXIT when SQL%NOTFOUND;
1379
1380 stmt_num := 2;
1381 UPDATE bom_ref_desgs_interface brdi
1382 SET process_flag = 7
1383 WHERE process_flag = 4
1384 AND transaction_type = G_Insert
1385 AND (UPPER(interface_entity_type) = 'BILL'
1386 OR interface_entity_type is null)
1387 AND exists
1388 (SELECT null
1389 FROM bom_reference_designators brd
1390 WHERE brd.component_sequence_id = brdi.component_sequence_id
1391 AND brd.component_reference_designator =
1392 brdi.component_reference_designator
1393 AND nvl(brd.acd_type,999) = nvl(brdi.acd_type,999));
1394 COMMIT;
1395
1396 END LOOP;
1397
1398
1399 /* Bug 1322500 :
1400 Moved the UPDATE bom_ref_desgs_interface brdi outside
1404 the production table, the interface table is being updated once,
1401 the LOOP as this was causing the Performance Issues when there are
1402 large number record in the interface table.
1403 This is due to the reason that for every 500 records inserted in
1405 which is not required.
1406 So moving this outside the loop, this ensures that the process flag is
1407 updated to 7 for all processed rows only once after all the rows
1408 are inserted.
1409 The 11.0 bug fix uses a HINT on BOM_REFERENCE_DESIGNATORS Table in the
1410 update stmt to force the Unique Index. This might not be acceptable in 11.5
1411 given the difference in the optimizer. This is therefore removed in 11.5.
1412 If the 11.5 instance faces performance issues, this hint can be introduced
1413 to see if there is any performance benefit.
1414 */
1415
1416 /*
1417 ** Update Reference Designators
1418 */
1419 stmt_num := 3;
1420 continue_loop := TRUE;
1421 WHILE continue_loop LOOP
1422 commit_cnt := 0;
1423 FOR c1rec IN c1 LOOP
1424 commit_cnt := commit_cnt + 1;
1425 UPDATE bom_reference_designators
1426 SET component_reference_designator = nvl(c1rec.ND, c1rec.CRD),
1427 ref_designator_comment = c1rec.RDC,
1428 last_update_date = c1rec.LUD,
1429 last_updated_by = c1rec.LUB,
1430 last_update_login = c1rec.LUL,
1431 attribute_category = c1rec.AC,
1432 attribute1 = c1rec.A1,
1433 attribute2 = c1rec.A2,
1434 attribute3 = c1rec.A3,
1435 attribute4 = c1rec.A4,
1436 attribute5 = c1rec.A5,
1437 attribute6 = c1rec.A6,
1438 attribute7 = c1rec.A7,
1439 attribute8 = c1rec.A8,
1440 attribute9 = c1rec.A9,
1441 attribute10 = c1rec.A10,
1442 attribute11 = c1rec.A11,
1443 attribute12 = c1rec.A12,
1444 attribute13 = c1rec.A13,
1445 attribute14 = c1rec.A14,
1446 attribute15 = c1rec.A15,
1447 request_id = c1rec.RI,
1448 program_application_id = c1rec.PAI,
1449 program_id = c1rec.PI,
1450 program_update_date = c1rec.PUD
1451 WHERE component_sequence_id = c1rec.CSI
1452 AND component_reference_designator = c1rec.CRD;
1453
1454 stmt_num := 4;
1455 UPDATE bom_ref_desgs_interface
1456 SET process_flag = 7
1457 WHERE transaction_id = c1rec.TI;
1458 END LOOP;
1459
1460 stmt_num := 5;
1461 COMMIT;
1462 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
1463 continue_loop := FALSE;
1464 END IF;
1465
1466 END LOOP;
1467
1468 /*
1469 ** Delete Reference Designators
1470 */
1471 stmt_num := 6;
1472 continue_loop := TRUE;
1473 WHILE continue_loop LOOP
1474 commit_cnt := 0;
1475 FOR c2rec IN c2 LOOP
1476 commit_cnt := commit_cnt + 1;
1477 DELETE FROM bom_reference_designators
1478 WHERE component_sequence_id = c2rec.CSI
1479 AND component_reference_designator = c2rec.CRD;
1480
1481 stmt_num := 7;
1482 UPDATE bom_ref_desgs_interface
1483 SET process_flag = 7
1484 WHERE transaction_id = c2rec.TI;
1485 END LOOP;
1486
1487 stmt_num := 8;
1488 COMMIT;
1489 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
1490 continue_loop := FALSE;
1491 END IF;
1492 END LOOP;
1493
1494
1495 RETURN(0);
1496
1497 EXCEPTION
1498 WHEN NO_DATA_FOUND THEN
1499 RETURN(0);
1500 WHEN OTHERS THEN
1501 ROLLBACK;
1502 err_text := 'Bom_Reference_Designator_Api(Transact-'||stmt_num||') '||substrb(SQLERRM,1,500);
1503 return(SQLCODE);
1504
1505 END Transact_Reference_Designator;
1506
1507 /* ----------------------- Import_Reference_Designator -------------------- */
1508 /*
1509 NAME
1510 Import_Reference_Designator
1511 DESCRIPTION
1512 Assign, Validate, and Transact the Reference Designator record in the
1513 interface table, BOM_REF_DESGS_INTERFACE.
1514 REQUIRES
1515 err_text out buffer to return error message
1516 MODIFIES
1517 RETURNS
1518 0 if successful
1519 SQLCODE if unsuccessful
1520 NOTES
1521 -----------------------------------------------------------------------------*/
1522 FUNCTION Import_Reference_Designator (
1523 org_id NUMBER,
1524 all_org NUMBER := 1,
1525 user_id NUMBER := -1,
1526 login_id NUMBER := -1,
1527 prog_appid NUMBER := -1,
1528 prog_id NUMBER := -1,
1529 req_id NUMBER := -1,
1530 del_rec_flag NUMBER := 1,
1531 err_text IN OUT VARCHAR2
1532 )
1533 return INTEGER
1534 IS
1535 err_msg VARCHAR2(2000);
1536 ret_code NUMBER := 1;
1537 stmt_num NUMBER;
1538 BEGIN
1539 stmt_num := 1;
1540 ret_code := Assign_Reference_Designator (
1544 login_id => login_id,
1541 org_id => org_id,
1542 all_org => all_org,
1543 user_id => user_id,
1545 prog_appid => prog_appid,
1546 prog_id => prog_id,
1547 req_id => req_id,
1548 err_text => err_msg);
1549 IF (ret_code <> 0) THEN
1550 err_text := 'Assign_Reference_Designator '||substrb(err_msg, 1,1500);
1551 ROLLBACK;
1552 RETURN(ret_code);
1553 END IF;
1554 COMMIT;
1555
1556 stmt_num := 2;
1557 ret_code := Validate_Reference_Designator (
1558 org_id => org_id,
1559 all_org => all_org,
1560 user_id => user_id,
1561 login_id => login_id,
1562 prog_appid => prog_appid,
1563 prog_id => prog_id,
1564 req_id => req_id,
1565 err_text => err_msg);
1566 IF (ret_code <> 0) THEN
1567 err_text := 'Validate_Reference_Designator '||substrb(err_msg, 1,1500);
1568 ROLLBACK;
1569 RETURN(ret_code);
1570 END IF;
1571 COMMIT;
1572
1573 stmt_num := 3;
1574 ret_code := Transact_Reference_Designator (
1575 user_id => user_id,
1576 login_id => login_id,
1577 prog_appid => prog_appid,
1578 prog_id => prog_id,
1579 req_id => req_id,
1580 err_text => err_msg);
1581
1582 IF (ret_code <> 0) THEN
1583 err_text := 'Transact_Reference_Designator '||substrb(err_msg, 1,1500);
1584 ROLLBACK;
1585 RETURN(ret_code);
1586 END IF;
1587 COMMIT;
1588
1589 stmt_num := 4;
1590 IF (del_rec_flag = 1) THEN
1591 LOOP
1592 DELETE from bom_ref_desgs_interface
1593 WHERE process_flag = 7
1594 AND (UPPER(interface_entity_type) = 'BILL'
1595 OR interface_entity_type is null)
1596 AND rownum < G_rows_to_commit;
1597
1598 EXIT when SQL%NOTFOUND;
1599 COMMIT;
1600 END LOOP;
1601 END IF;
1602
1603 RETURN(0);
1604
1605 EXCEPTION
1606 WHEN others THEN
1607 err_text := 'Bom_Reference_Designator_Api(Import-'||stmt_num||') '||substrb(SQLERRM,1,1000);
1608 RETURN(ret_code);
1609 END Import_Reference_Designator;
1610
1611
1612 END Bom_Reference_Designator_Api;