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