[Home] [Help]
PACKAGE BODY: APPS.BOM_BILL_API
Source
1 PACKAGE BODY Bom_Bill_Api AS
2 /* $Header: BOMOIBMB.pls 115.6 2002/06/14 12:33:05 pkm ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMOIBMB.pls |
9 | DESCRIPTION : This package contains functions used to assign, validate |
10 | and transact Bill of Material data in the |
11 | BOM_BILL_OF_MATLS_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 | 03/10/97 Julie Maeyama Created this new package |
23 +==========================================================================*/
24
25 /* ------------------------------ Assign_Bill -------------------------------*/
26 /*
27 NAME
28 Assign_Bill
29 DESCRIPTION
30 Assign defaults and ID's to bill record in the interface table
31 REQUIRES
32 err_text out buffer to return error message
33 MODIFIES
34 BOM_BILL_OF_MATLS_INTERFACE
35 MTL_INTERFACE_ERRORS
36 RETURNS
37 0 if successful
38 SQLCODE if unsuccessful
39 NOTES
40 -----------------------------------------------------------------------------*/
41 FUNCTION Assign_Bill (
42 org_id NUMBER,
43 all_org NUMBER := 2,
44 user_id NUMBER,
45 login_id NUMBER,
46 prog_appid NUMBER,
47 prog_id NUMBER,
48 req_id NUMBER,
49 err_text IN OUT VARCHAR2
50 )
51 return INTEGER
52 IS
53 stmt_num NUMBER := 0;
54 ret_code NUMBER;
55 commit_cnt NUMBER;
56 continue_loop BOOLEAN := TRUE;
57 X_rev_exists NUMBER := 0;
58 x_bom_item_type NUMBER;
59 /*
60 ** Select all INSERTS
61 */
62 CURSOR c1 IS
63 SELECT organization_id OI, organization_code OC,
64 assembly_item_id AII, item_number AIN,
65 common_assembly_item_id CAII, common_item_number CAIN,
66 common_organization_id COI, common_org_code COC,
67 alternate_bom_designator ABD, transaction_id TI,
68 bill_sequence_id BSI, common_bill_sequence_id CBSI,
69 revision R, last_update_date LUD, last_updated_by LUB,
70 creation_date CD, created_by CB, last_update_login LUL,
71 transaction_type A, assembly_type AST
72 FROM bom_bill_of_mtls_interface
73 WHERE process_flag = 1
74 AND transaction_type = G_Insert
75 AND (all_org = 1
76 OR
77 (all_org = 2 AND organization_id = org_id))
78 AND rownum < G_rows_to_commit;
79
80 /*
81 ** Select all UPDATEs and DELETEs
82 */
83 CURSOR c2 IS
84 SELECT organization_id OI, organization_code OC,
85 assembly_item_id AII, item_number AIN,
86 common_assembly_item_id CAII, common_item_number CAIN,
87 common_organization_id COI, common_org_code COC,
88 alternate_bom_designator ABD, transaction_id TI,
89 bill_sequence_id BSI, common_bill_sequence_id CBSI,
90 revision R, last_update_date LUD, last_updated_by LUB,
91 creation_date CD, created_by CB, last_update_login LUL,
92 transaction_type A, assembly_type AST
93 FROM bom_bill_of_mtls_interface
94 WHERE process_flag = 1
95 AND transaction_type in (G_Update, G_Delete)
96 AND (all_org = 1
97 OR
98 (all_org = 2 AND organization_id = org_id))
99 AND rownum < G_rows_to_commit;
100
101 BEGIN
102 /** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
103 stmt_num := 0.5 ;
104 LOOP
105 UPDATE bom_bill_of_mtls_interface
106 SET transaction_type = G_Insert
107 WHERE process_flag = 1
108 AND upper(transaction_type) = 'INSERT'
109 AND rownum < G_rows_to_commit;
110 EXIT when SQL%NOTFOUND;
111 COMMIT;
112 END LOOP;
113
114 /*
115 ** ALL RECORDS - Assign Org Id
116 */
117 stmt_num := 1;
118 LOOP
119 UPDATE bom_bill_of_mtls_interface ori
120 SET organization_id = (SELECT organization_id
121 FROM mtl_parameters a
122 WHERE a.organization_code = ori.organization_code)
123 WHERE process_flag = 1
124 AND upper(transaction_type) in (G_Insert, G_Delete, G_Update)
125 AND organization_id is null
126 AND organization_code is not null
127 AND exists (SELECT organization_code
128 FROM mtl_parameters b
129 WHERE b.organization_code = ori.organization_code)
130 AND rownum < G_rows_to_commit;
131 EXIT when SQL%NOTFOUND;
132 COMMIT;
133 END LOOP;
134
135 /*
136 ** FOR ALL - Assign transaction ids and bill sequence ids
137 */
138 stmt_num := 2;
139 LOOP
140 UPDATE bom_bill_of_mtls_interface ori
141 SET transaction_id = mtl_system_items_interface_s.nextval,
142 transaction_type = upper(transaction_type),
143 bill_sequence_id = decode(upper(transaction_type), G_Insert,
144 bom_inventory_components_s.nextval,
145 bill_sequence_id)
146 WHERE transaction_id is null
147 AND upper(transaction_type) in (G_Insert, G_Update, G_Delete)
148 AND process_flag = 1
149 AND rownum < G_rows_to_commit;
150 EXIT when SQL%NOTFOUND;
151 stmt_num := 3;
152 COMMIT;
153 END LOOP;
154
155 /*
156 ** FOR INSERTs - Assign values
157 */
158 WHILE continue_loop LOOP
159 commit_cnt := 0;
160 FOR c1rec IN c1 LOOP
161 commit_cnt := commit_cnt + 1;
162 x_bom_item_type := null;
163 stmt_num := 4;
164 /*
165 ** Check if Org Id is null
166 */
167 IF (c1rec.OI is null) THEN
168 ret_code := INVPUOPI.mtl_log_interface_err(
169 org_id => NULL,
170 user_id => user_id,
171 login_id => login_id,
172 prog_appid => prog_appid,
173 prog_id => prog_id,
174 req_id => req_id,
175 trans_id => c1rec.TI,
176 error_text => err_text,
177 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
178 msg_name => 'BOM_ORG_ID_MISSING',
179 err_text => err_text);
180 UPDATE bom_bill_of_mtls_interface
181 SET process_flag = 3
182 WHERE transaction_id = c1rec.TI;
183
184 GOTO continue_loop1;
185 END IF;
186 /*
187 ** Set assembly item ids
188 */
189 stmt_num := 5;
190 IF (c1rec.AII is null) THEN
191 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
192 org_id => c1rec.OI,
193 flex_code => 'MSTK',
194 flex_name => c1rec.AIN,
195 flex_id => c1rec.AII,
196 set_id => -1,
197 err_text => err_text);
198 IF (ret_code <> 0) THEN
199 ret_code := INVPUOPI.mtl_log_interface_err(
200 org_id => NULL,
201 user_id => user_id,
202 login_id => login_id,
203 prog_appid => prog_appid,
204 prog_id => prog_id,
205 req_id => req_id,
206 trans_id => c1rec.TI,
207 error_text => err_text,
208 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
209 msg_name => 'BOM_ASSY_ITEM_MISSING',
210 err_text => err_text);
211 UPDATE bom_bill_of_mtls_interface
212 SET process_flag = 3
213 WHERE transaction_id = c1rec.TI;
214
215 IF (ret_code <> 0) THEN
216 RETURN(ret_code);
217 END IF;
218 GOTO continue_loop1;
219 END IF;
220 END IF;
221 /*
222 ** Check for Product Family item
223 */
224 stmt_num := 5.1;
225 DECLARE
226 CURSOR GetBOMItemType IS
227 SELECT bom_item_type
228 FROM mtl_system_items
229 WHERE organization_id = c1rec.OI
230 AND inventory_item_id = c1rec.AII;
231 BEGIN
232 FOR c1 IN GetBOMItemType LOOP
233 x_bom_item_type := c1.bom_item_type;
234 END LOOP;
235
236 IF (x_bom_item_type is null) THEN
237 ret_code := INVPUOPI.mtl_log_interface_err(
238 org_id => NULL,
239 user_id => user_id,
240 login_id => login_id,
241 prog_appid => prog_appid,
242 prog_id => prog_id,
243 req_id => req_id,
244 trans_id => c1rec.TI,
245 error_text => err_text,
246 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
247 msg_name => 'BOM_ASSY_ITEM_MISSING',
248 err_text => err_text);
249 UPDATE bom_bill_of_mtls_interface
250 SET process_flag = 3
251 WHERE transaction_id = c1rec.TI;
252
253 IF (ret_code <> 0) THEN
254 RETURN(ret_code);
255 END IF;
256 GOTO continue_loop1;
257 ELSIF (x_bom_item_type = G_ProductFamily) THEN
258 stmt_num := 5.2;
259 /*
260 ** For Product Families - Insert revision record
261 */
262 IF (c1rec.R is not null) THEN
263 INSERT into mtl_item_revisions_interface
264 (INVENTORY_ITEM_ID,
265 ORGANIZATION_ID,
266 REVISION,
267 LAST_UPDATE_DATE,
268 LAST_UPDATED_BY,
269 CREATION_DATE,
270 CREATED_BY,
271 LAST_UPDATE_LOGIN,
272 EFFECTIVITY_DATE,
273 IMPLEMENTATION_DATE,
274 TRANSACTION_ID,
275 PROCESS_FLAG,
276 TRANSACTION_TYPE,
277 REQUEST_ID,
278 PROGRAM_APPLICATION_ID,
279 PROGRAM_ID,
280 PROGRAM_UPDATE_DATE)
281 VALUES
282 (c1rec.AII, c1rec.OI, UPPER(c1rec.R),
283 nvl(c1rec.LUD, sysdate),
284 nvl(c1rec.LUB, user_id),
285 nvl(c1rec.CD, sysdate),
286 nvl(c1rec.CB, user_id),
287 nvl(c1rec.LUL, user_id),
288 sysdate,
289 sysdate,
290 mtl_system_items_interface_s.nextval,
291 2,
292 G_Insert,
293 req_id,
294 prog_appid,
295 prog_id,
296 sysdate);
297 END IF;
298
299 stmt_num := 5.3;
300 UPDATE bom_bill_of_mtls_interface
301 SET organization_id = nvl(organization_id, c1rec.OI),
302 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
303 alternate_bom_designator = null,
304 specific_assembly_comment = null,
305 pending_from_ecn = null,
306 common_bill_sequence_id = c1rec.BSI,
307 common_organization_id = null,
308 common_assembly_item_id = null,
309 assembly_type = 1,
310 last_update_date = nvl(last_update_date, sysdate),
311 last_updated_by = nvl(last_updated_by, user_id),
312 creation_date = nvl(creation_date, sysdate),
313 created_by = nvl(created_by, user_id),
314 last_update_login = nvl(last_update_login, user_id),
315 request_id = nvl(request_id, req_id),
316 program_application_id =nvl(program_application_id,prog_appid),
317 program_id = nvl(program_id, prog_id),
318 program_update_date = nvl(program_update_date, sysdate),
319 process_flag = 2
323 err_text := 'Bom_Bill_Api('||stmt_num||')'||substrb(SQLERRM,1, 60);
320 WHERE transaction_id = c1rec.TI;
321
322 IF (SQL%NOTFOUND) THEN
324 RETURN(SQLCODE);
325 END IF;
326 GOTO continue_loop1;
327 END IF;
328 END;
329
330 IF (c1rec.COI is null) AND (c1rec.COC is not null) AND
331 (c1rec.CBSI is null) THEN
332 ret_code := INVPUOPI.mtl_pr_trans_org_id(
333 org_code => c1rec.COC,
334 org_id => c1rec.COI,
335 err_text => err_text);
336 IF (ret_code <> 0) THEN
337 ret_code := INVPUOPI.mtl_log_interface_err(
338 org_id => NULL,
339 user_id => user_id,
340 login_id => login_id,
341 prog_appid => prog_appid,
342 prog_id => prog_id,
343 req_id => req_id,
344 trans_id => c1rec.TI,
345 error_text => err_text,
346 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
347 msg_name => 'BOM_COMMON_ORG_MISSING',
348 err_text => err_text);
349 UPDATE bom_bill_of_mtls_interface
350 SET process_flag = 3
351 WHERE transaction_id = c1rec.TI;
352
353 IF (ret_code <> 0) THEN
354 RETURN(ret_code);
355 END IF;
356 GOTO continue_loop1;
357 END IF;
358 END IF;
359
360 /*
361 ** Get common organization id
362 */
363 stmt_num := 6;
364 If (c1rec.COI is null) AND (c1rec.COC is not null) AND
365 (c1rec.CBSI is null) THEN
366 ret_code := INVPUOPI.mtl_pr_trans_org_id(
367 org_code => c1rec.COC,
368 org_id => c1rec.COI,
369 err_text => err_text);
370 IF (ret_code <> 0) THEN
371 ret_code := INVPUOPI.mtl_log_interface_err(
372 org_id => NULL,
373 user_id => user_id,
374 login_id => login_id,
375 prog_appid => prog_appid,
376 prog_id => prog_id,
377 req_id => req_id,
378 trans_id => c1rec.TI,
379 error_text => err_text,
380 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
381 msg_name => 'BOM_COMMON_ORG_MISSING',
382 err_text => err_text);
383 UPDATE bom_bill_of_mtls_interface
384 SET process_flag = 3
385 WHERE transaction_id = c1rec.TI;
386
387 IF (ret_code <> 0) THEN
388 RETURN(ret_code);
389 END IF;
390 GOTO continue_loop1;
391 END IF;
392 END IF;
393 /*
394 ** Set common assembly item ids
395 */
396 stmt_num := 7;
397 IF (c1rec.caii is null AND c1rec.CAIN is not null AND
398 c1rec.CBSI is null) THEN
399 IF (c1rec.COI is null) THEN
400 c1rec.COI := c1rec.OI;
401 END IF;
402 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
403 org_id => c1rec.COI,
404 flex_code => 'MSTK',
405 flex_name => c1rec.CAIN,
406 flex_id => c1rec.CAII,
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_BILL_OF_MTLS_INTERFACE',
420 msg_name => 'BOM_CMN_ASSY_ITEM_INVALID',
421 err_text => err_text);
422 UPDATE bom_bill_of_mtls_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_loop1;
430 END IF;
431 END IF;
432 /*
433 ** Get Common bill info
434 */
435 stmt_num :=8;
436 IF (c1rec.CBSI is null) THEN
437 IF (c1rec.CAII is null) THEN
438 c1rec.CBSI := c1rec.BSI;
439 c1rec.COI := null;
440 ELSE
441 BEGIN
442 SELECT bill_sequence_id
443 INTO c1rec.CBSI
444 FROM bom_bill_of_materials
445 WHERE organization_id = nvl(c1rec.COI, c1rec.OI)
446 AND assembly_item_id = c1rec.CAII
447 AND nvl(alternate_bom_designator, 'NONE') =
451 WHEN no_data_found THEN
448 nvl(c1rec.ABD, 'NONE');
449 GOTO skip_interface1;
450 EXCEPTION
452 null;
453 END;
454
455 stmt_num := 9;
456 BEGIN
457 SELECT bill_sequence_id
458 INTO c1rec.CBSI
459 FROM bom_bill_of_mtls_interface
460 WHERE organization_id = nvl(c1rec.COI, c1rec.OI)
461 AND transaction_type = G_Insert
462 AND assembly_item_id = c1rec.CAII
463 AND nvl(alternate_bom_designator, 'NONE') =
464 nvl(c1rec.ABD, 'NONE')
465 AND process_flag not in (3,7)
466 AND rownum = 1;
467 EXCEPTION
468 WHEN no_data_found THEN
469 ret_code := INVPUOPI.mtl_log_interface_err(
470 org_id => NULL,
471 user_id => user_id,
472 login_id => login_id,
473 prog_appid => prog_appid,
474 prog_id => prog_id,
475 req_id => req_id,
476 trans_id => c1rec.TI,
477 error_text => err_text,
478 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
479 msg_name => 'BOM_CMN_BILL_SEQ_MISSING',
480 err_text => err_text);
481
482 UPDATE bom_bill_of_mtls_interface
483 SET process_flag = 3
484 WHERE transaction_id = c1rec.TI;
485
486 IF (ret_code <> 0) THEN
487 RETURN(ret_code);
488 END IF;
489 GOTO continue_loop1;
490 END;
491 END IF;
492 ELSE -- Common Bill Sequence Id given
493 stmt_num := 10;
494 BEGIN
495 SELECT assembly_item_id, organization_id
496 INTO c1rec.CAII, c1rec.COI
497 FROM bom_bill_of_materials
498 WHERE bill_sequence_id = c1rec.CBSI;
499 GOTO skip_interface1;
500 EXCEPTION
501 WHEN no_data_found THEN
502 null;
503 END;
504
505 stmt_num := 11;
506 BEGIN
507 SELECT assembly_item_id, organization_id
508 INTO c1rec.CAII, c1rec.COI
509 FROM bom_bill_of_mtls_interface
510 WHERE bill_sequence_id = c1rec.CBSI
511 AND transaction_type = G_Insert
512 AND process_flag not in (3,7)
513 AND rownum = 1;
514 EXCEPTION
515 WHEN no_data_found THEN
516 ret_code := INVPUOPI.mtl_log_interface_err(
517 org_id => NULL,
518 user_id => user_id,
519 login_id => login_id,
520 prog_appid => prog_appid,
521 prog_id => prog_id,
522 req_id => req_id,
523 trans_id => c1rec.TI,
524 error_text => err_text,
525 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
526 msg_name => 'BOM_CMN_BILL_SEQ_MISSING',
527 err_text => err_text);
528 UPDATE bom_bill_of_mtls_interface
529 SET process_flag = 3
530 WHERE transaction_id = c1rec.TI;
531
532 IF (ret_code <> 0) THEN
533 RETURN(ret_code);
534 END IF;
535 GOTO continue_loop1;
536 END;
537 END IF;
538
539 <<skip_interface1>>
540 IF (c1rec.CBSI = c1rec.BSI) THEN
541 c1rec.COI := NULL;
542 c1rec.CAII := NULL;
543 END IF;
544 /*
545 ** Insert revision record
546 */
547 stmt_num := 12;
548 IF (c1rec.R is not null) THEN
549 INSERT into mtl_item_revisions_interface
550 (INVENTORY_ITEM_ID,
551 ORGANIZATION_ID,
552 REVISION,
553 LAST_UPDATE_DATE,
554 LAST_UPDATED_BY,
555 CREATION_DATE,
556 CREATED_BY,
557 LAST_UPDATE_LOGIN,
558 EFFECTIVITY_DATE,
559 IMPLEMENTATION_DATE,
560 TRANSACTION_ID,
561 PROCESS_FLAG,
562 TRANSACTION_TYPE,
563 REQUEST_ID,
564 PROGRAM_APPLICATION_ID,
565 PROGRAM_ID,
566 PROGRAM_UPDATE_DATE)
567 VALUES
568 (c1rec.AII, c1rec.OI, UPPER(c1rec.R),
569 nvl(c1rec.LUD, sysdate),
570 nvl(c1rec.LUB, user_id),
571 nvl(c1rec.CD, sysdate),
572 nvl(c1rec.CB, user_id),
573 nvl(c1rec.LUL, user_id),
574 sysdate,
575 sysdate,
579 req_id,
576 mtl_system_items_interface_s.nextval,
577 2,
578 G_Insert,
580 prog_appid,
581 prog_id,
582 sysdate);
583 END IF;
584
585 stmt_num := 13;
586 UPDATE bom_bill_of_mtls_interface
587 SET organization_id = nvl(organization_id, c1rec.OI),
588 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
589 common_bill_sequence_id = c1rec.CBSI,
590 common_organization_id = c1rec.COI,
591 common_assembly_item_id = c1rec.CAII,
592 assembly_type = nvl(c1rec.AST, 1),
593 last_update_date = nvl(last_update_date, sysdate),
594 last_updated_by = nvl(last_updated_by, user_id),
595 creation_date = nvl(creation_date, sysdate),
596 created_by = nvl(created_by, user_id),
597 last_update_login = nvl(last_update_login, user_id),
598 request_id = nvl(request_id, req_id),
599 program_application_id =nvl(program_application_id,prog_appid),
600 program_id = nvl(program_id, prog_id),
601 program_update_date = nvl(program_update_date, sysdate),
602 process_flag = 2
603 WHERE transaction_id = c1rec.TI;
604
605 IF (SQL%NOTFOUND) THEN
606 err_text := 'Bom_Bill_Api('||stmt_num||')'||substrb(SQLERRM,1, 60);
607 RETURN(SQLCODE);
608 END IF;
609
610 GOTO continue_loop1;
611
612 <<continue_loop1>>
613 NULL;
614 END LOOP;
615
616 stmt_num := 14;
617 COMMIT;
618
619 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
620 continue_loop := FALSE;
621 END IF;
622
623 END LOOP;
624
625 /*
626 ** FOR UPDATES AND DELETES - Assign Values
627 */
628 continue_loop := TRUE;
629 WHILE continue_loop LOOP
630 commit_cnt := 0;
631 FOR c2rec IN c2 LOOP
632 commit_cnt := commit_cnt + 1;
633 x_bom_item_type := null;
634 stmt_num := 15;
635 /*
636 ** Assign primary key info
637 */
638 IF (c2rec.BSI is null) THEN
639 -- Check if Org Id is null
640 IF (c2rec.OI is null) THEN
641 ret_code := INVPUOPI.mtl_log_interface_err(
642 org_id => NULL,
643 user_id => user_id,
644 login_id => login_id,
645 prog_appid => prog_appid,
646 prog_id => prog_id,
647 req_id => req_id,
648 trans_id => c2rec.TI,
649 error_text => err_text,
650 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
651 msg_name => 'BOM_ORG_ID_MISSING',
652 err_text => err_text);
653 UPDATE bom_bill_of_mtls_interface
654 SET process_flag = 3
655 WHERE transaction_id = c2rec.TI;
656
657 IF (ret_code <> 0) THEN
658 RETURN(ret_code);
659 END IF;
660 GOTO continue_loop2;
661 END IF;
662
663 -- Get Assembly Item Id
664 stmt_num := 16;
665 IF (c2rec.AII is null) THEN
666 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
667 org_id => c2rec.OI,
668 flex_code => 'MSTK',
669 flex_name => c2rec.AIN,
670 flex_id => c2rec.AII,
671 set_id => -1,
672 err_text => err_text);
673 IF (ret_code <> 0) THEN
674 ret_code := INVPUOPI.mtl_log_interface_err(
675 org_id => c2rec.OI,
676 user_id => user_id,
677 login_id => login_id,
678 prog_appid => prog_appid,
679 prog_id => prog_id,
680 req_id => req_id,
681 trans_id => c2rec.TI,
682 error_text => err_text,
683 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
684 msg_name => 'BOM_INV_ITEM_ID_MISSING',
685 err_text => err_text);
686 UPDATE bom_bill_of_mtls_interface
687 SET process_flag = 3
688 WHERE transaction_id = c2rec.TI;
689
690 IF (ret_code <> 0) THEN
691 RETURN(ret_code);
692 END IF;
693 GOTO continue_loop2;
694 END IF;
695 END IF;
696 stmt_num := 17;
697 /*
698 ** Get Bill Sequence Id
699 */
700 BEGIN
701 SELECT bom.bill_sequence_id, bom.assembly_type,
702 msi.bom_item_type
703 INTO c2rec.BSI, c2rec.AST, x_bom_item_type
704 FROM bom_bill_of_materials bom,
705 mtl_system_items msi
706 WHERE bom.organization_id = c2rec.OI
707 AND bom.assembly_item_id = c2rec.AII
711 AND msi.inventory_item_id = bom.assembly_item_id;
708 AND nvl(bom.alternate_bom_designator, 'NONE') =
709 nvl(c2rec.ABD, 'NONE')
710 AND msi.organization_id = bom.organization_id
712 EXCEPTION
713 WHEN no_data_found THEN
714 ret_code := INVPUOPI.mtl_log_interface_err(
715 org_id => c2rec.OI,
716 user_id => user_id,
717 login_id => login_id,
718 prog_appid => prog_appid,
719 prog_id => prog_id,
720 req_id => req_id,
721 trans_id => c2rec.TI,
722 error_text => err_text,
723 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
724 msg_name => 'BOM_BILL_SEQ_MISSING',
725 err_text => err_text);
726 UPDATE bom_bill_of_mtls_interface
727 SET process_flag = 3
728 WHERE transaction_id = c2rec.TI;
729
730 IF (ret_code <> 0) THEN
731 RETURN(ret_code);
732 END IF;
733 GOTO continue_loop2;
734 END;
735 /*
736 ** Get Bill Info
737 */
738 ELSE -- bill_sequence_id is given
739 stmt_num := 18;
740 BEGIN
741 SELECT bom.assembly_item_id, bom.organization_id,
742 bom.alternate_bom_designator, bom.assembly_type,
743 msi.bom_item_type
744 INTO c2rec.AII, c2rec.OI, c2rec.ABD, c2rec.AST,
745 x_bom_item_type
746 FROM bom_bill_of_materials bom,
747 mtl_system_items msi
748 WHERE bom.bill_sequence_id = c2rec.BSI
749 AND msi.organization_id = bom.organization_id
750 AND msi.inventory_item_id = bom.assembly_item_id;
751 EXCEPTION
752 WHEN no_data_found THEN
753 ret_code := INVPUOPI.mtl_log_interface_err(
754 org_id => c2rec.OI,
755 user_id => user_id,
756 login_id => login_id,
757 prog_appid => prog_appid,
758 prog_id => prog_id,
759 req_id => req_id,
760 trans_id => c2rec.TI,
761 error_text => err_text,
762 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
763 msg_name => 'BOM_BILL_SEQ_MISSING',
764 err_text => err_text);
765 UPDATE bom_bill_of_mtls_interface
766 SET process_flag = 3
767 WHERE transaction_id = c2rec.TI;
768
769 IF (ret_code <> 0) THEN
770 RETURN(ret_code);
771 END IF;
772 GOTO continue_loop2;
773 END;
774 END IF;
775 /*
776 ** Assign Common Info ONLY for UPDATE's
777 */
778 IF (c2rec.A = G_Update) THEN
779 stmt_num := 18.1;
780 /*
781 ** For Product Families
782 */
783 IF (x_bom_item_type = G_ProductFamily) THEN
784 UPDATE bom_bill_of_mtls_interface
785 SET organization_id = c2rec.OI,
786 assembly_item_id = c2rec.AII,
787 alternate_bom_designator = c2rec.ABD,
788 bill_sequence_id = c2rec.BSI,
789 last_update_date = nvl(last_update_date, sysdate),
790 last_updated_by = nvl(last_updated_by, user_id),
791 last_update_login = nvl(last_update_login, user_id),
792 request_id = nvl(request_id, req_id),
793 program_application_id =nvl(program_application_id,prog_appid),
794 program_id = nvl(program_id, prog_id),
795 program_update_date = nvl(program_update_date, sysdate),
796 process_flag = 2
797 WHERE transaction_id = c2rec.TI;
798
799 IF (SQL%NOTFOUND) THEN
800 err_text := 'Bom_Bill_Api('||stmt_num||')'||substrb(SQLERRM,1,60);
801 RETURN(SQLCODE);
802 END IF;
803 GOTO continue_loop2;
804 END IF;
805
806 /*
807 ** Get common organization id
808 */
809 stmt_num := 19;
810 IF (c2rec.COI is null) AND (c2rec.COC is not null) AND
811 (c2rec.CBSI is null) THEN
812 ret_code := INVPUOPI.mtl_pr_trans_org_id(
813 org_code => c2rec.COC,
814 org_id => c2rec.COI,
815 err_text => err_text);
816 IF (ret_code <> 0) THEN
817 ret_code := INVPUOPI.mtl_log_interface_err(
818 org_id => NULL,
819 user_id => user_id,
820 login_id => login_id,
821 prog_appid => prog_appid,
822 prog_id => prog_id,
823 req_id => req_id,
824 trans_id => c2rec.TI,
825 error_text => err_text,
826 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
827 msg_name => 'BOM_COMMON_ORG_MISSING',
831 WHERE transaction_id = c2rec.TI;
828 err_text => err_text);
829 UPDATE bom_bill_of_mtls_interface
830 SET process_flag = 3
832
833 IF (ret_code <> 0) THEN
834 RETURN(ret_code);
835 END IF;
836 GOTO continue_loop2;
837 END IF;
838 END IF;
839 /*
840 ** Get common assembly item id
841 ** If common org id is null, set it to org id
842 */
843 stmt_num := 20;
844 IF (c2rec.CAII is null AND c2rec.CAIN is not null AND
845 c2rec.CBSI is null) THEN
846 IF (c2rec.COI is null) THEN
847 c2rec.COI := c2rec.OI;
848 END IF;
849 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
850 org_id => c2rec.COI,
851 flex_code => 'MSTK',
852 flex_name => c2rec.CAIN,
853 flex_id => c2rec.CAII,
854 set_id => -1,
855 err_text => err_text);
856 IF (ret_code <> 0) THEN
857 ret_code := INVPUOPI.mtl_log_interface_err(
858 org_id => NULL,
859 user_id => user_id,
860 login_id => login_id,
861 prog_appid => prog_appid,
862 prog_id => prog_id,
863 req_id => req_id,
864 trans_id => c2rec.TI,
865 error_text => err_text,
866 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
867 msg_name => 'BOM_CMN_ASSY_ITEM_INVALID',
868 err_text => err_text);
869 UPDATE bom_bill_of_mtls_interface
870 SET process_flag = 3
871 WHERE transaction_id = c2rec.TI;
872
873 IF (ret_code <> 0) THEN
874 RETURN(ret_code);
875 END IF;
876 GOTO continue_loop2;
877 END IF;
878 END IF;
879
880 /*
881 ** Get Common bill info
882 */
883 IF (c2rec.CBSI is null) THEN
884 IF (c2rec.CAII is null) THEN
885 c2rec.COI := null;
886 ELSE
887 stmt_num :=21;
888 BEGIN
889 SELECT bill_sequence_id
890 INTO c2rec.CBSI
891 FROM bom_bill_of_materials
892 WHERE organization_id = nvl(c2rec.COI, c2rec.OI)
893 AND assembly_item_id = c2rec.CAII
894 AND nvl(alternate_bom_designator, 'NONE') =
895 nvl(c2rec.ABD, 'NONE');
896 GOTO skip_interface2;
897 EXCEPTION
898 WHEN no_data_found THEN
899 null;
900 END;
901 stmt_num := 22;
902 BEGIN
903 SELECT bill_sequence_id
904 INTO c2rec.CBSI
905 FROM bom_bill_of_mtls_interface
906 WHERE organization_id = nvl(c2rec.COI, c2rec.OI)
907 AND transaction_type = G_Insert
908 AND assembly_item_id = c2rec.CAII
909 AND nvl(alternate_bom_designator, 'NONE') =
910 nvl(c2rec.ABD, 'NONE')
911 AND process_flag not in (3,7)
912 AND rownum = 1;
913 EXCEPTION
914 WHEN no_data_found THEN
915 ret_code := INVPUOPI.mtl_log_interface_err(
916 org_id => NULL,
917 user_id => user_id,
918 login_id => login_id,
919 prog_appid => prog_appid,
920 prog_id => prog_id,
921 req_id => req_id,
922 trans_id => c2rec.TI,
923 error_text => err_text,
924 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
925 msg_name => 'BOM_CMN_BILL_SEQ_MISSING',
926 err_text => err_text);
927
928 UPDATE bom_bill_of_mtls_interface
929 SET process_flag = 3
930 WHERE transaction_id = c2rec.TI;
931
932 IF (ret_code <> 0) THEN
933 RETURN(ret_code);
934 END IF;
935 GOTO continue_loop2;
936 END;
937 END IF;
938 ELSIF (c2rec.CBSI = G_NullNum) THEN
939 c2rec.CAII := null;
940 c2rec.COI := null;
941 ELSE -- Common Bill Sequence Id given
942 stmt_num := 23;
943 BEGIN
944 SELECT assembly_item_id, organization_id
945 INTO c2rec.CAII, c2rec.COI
946 FROM bom_bill_of_materials
947 WHERE bill_sequence_id = c2rec.CBSI;
948 GOTO skip_interface2;
949 EXCEPTION
953
950 WHEN no_data_found THEN
951 null;
952 END;
954 stmt_num := 24;
955 BEGIN
956 SELECT assembly_item_id, organization_id
957 INTO c2rec.CAII, c2rec.COI
958 FROM bom_bill_of_mtls_interface
959 WHERE bill_sequence_id = c2rec.CBSI
960 AND transaction_type = G_Insert
961 AND process_flag not in (3,7)
962 AND rownum = 1;
963 EXCEPTION
964 WHEN no_data_found THEN
965 ret_code := INVPUOPI.mtl_log_interface_err(
966 org_id => NULL,
967 user_id => user_id,
968 login_id => login_id,
969 prog_appid => prog_appid,
970 prog_id => prog_id,
971 req_id => req_id,
972 trans_id => c2rec.TI,
973 error_text => err_text,
974 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
975 msg_name => 'BOM_CMN_BILL_SEQ_MISSING',
976 err_text => err_text);
977 UPDATE bom_bill_of_mtls_interface
978 SET process_flag = 3
979 WHERE transaction_id = c2rec.TI;
980
981 IF (ret_code <> 0) THEN
982 RETURN(ret_code);
983 END IF;
984 GOTO continue_loop2;
985 END;
986 END IF;
987
988 <<skip_interface2>>
989 stmt_num := 25;
990 UPDATE bom_bill_of_mtls_interface
991 SET organization_id = c2rec.OI,
992 assembly_item_id = c2rec.AII,
993 alternate_bom_designator = c2rec.ABD,
994 bill_sequence_id = c2rec.BSI,
995 common_bill_sequence_id = c2rec.CBSI,
996 common_organization_id = c2rec.COI,
997 common_assembly_item_id = c2rec.CAII,
998 last_update_date = nvl(last_update_date, sysdate),
999 last_updated_by = nvl(last_updated_by, user_id),
1000 last_update_login = nvl(last_update_login, user_id),
1001 request_id = nvl(request_id, req_id),
1002 program_application_id =nvl(program_application_id,prog_appid),
1003 program_id = nvl(program_id, prog_id),
1004 program_update_date = nvl(program_update_date, sysdate),
1005 process_flag = 2
1006 WHERE transaction_id = c2rec.TI;
1007
1008 IF (SQL%NOTFOUND) THEN
1009 err_text := 'Bom_Bill_Api('||stmt_num||')'||substrb(SQLERRM,1,60);
1010 RETURN(SQLCODE);
1011 END IF;
1012 ELSIF (c2rec.A = G_Delete) THEN
1013 stmt_num := 26;
1014 UPDATE bom_bill_of_mtls_interface
1015 SET organization_id = c2rec.OI,
1016 assembly_item_id = c2rec.AII,
1017 alternate_bom_designator = c2rec.ABD,
1018 assembly_type = c2rec.AST,
1019 bill_sequence_id = c2rec.BSI,
1020 process_flag = 2
1021 WHERE transaction_id = c2rec.TI;
1022
1023 IF (SQL%NOTFOUND) THEN
1024 err_text := 'Bom_Bill_Api('||stmt_num||')'||substrb(SQLERRM, 1, 60);
1025 RETURN(SQLCODE);
1026 END IF;
1027 END IF;
1028 <<continue_loop2>>
1029 NULL;
1030 END LOOP;
1031
1032 stmt_num := 27;
1033 COMMIT;
1034
1035 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
1036 continue_loop := FALSE;
1037 END IF;
1038
1039 END LOOP;
1040
1041 RETURN (0);
1042 EXCEPTION
1043 WHEN others THEN
1044 err_text := 'Bom_Bill_Api(Assign-'||stmt_num||') '||substrb(SQLERRM,1,500);
1045 RETURN(SQLCODE);
1046 END Assign_Bill;
1047
1048
1049 /* ------------------------ Verify_Bom_Seq_Id_Exists --------------------- */
1050 /*
1051 NAME
1052 Verify_Bom_Seq_Id_Exists - verify for uniqueness or existence of bom
1053 sequence id
1054 DESCRIPTION
1055 Verifies if the given bom sequence id is unique in prod and
1056 interface tables
1057 REQUIRES
1058 bom_sq_id bom_sequecne_id
1059 mode_type 1 - verify uniqueness of bom
1060 2 - verify existence of bom
1061 err_text out buffer to return error message
1062 MODIFIES
1063 RETURNS
1064 0 if successful
1065 count of routings with same bom_sequence_id if any found
1066 SQLCODE if error
1067 NOTES
1068 -----------------------------------------------------------------------------*/
1069 FUNCTION Verify_Bom_Seq_Id_Exists(
1070 bom_seq_id NUMBER,
1071 mode_type NUMBER,
1072 err_text OUT VARCHAR2
1073 )
1074 return INTEGER
1075 IS
1076 cnt NUMBER := 0;
1077 NOT_UNIQUE EXCEPTION;
1078 stmt_num NUMBER := 0;
1079 BEGIN
1080 /*
1081 ** first check in prod tables
1082 */
1083 stmt_num := 1;
1084 BEGIN
1088 WHERE bill_sequence_id = bom_seq_id;
1085 SELECT bill_sequence_id
1086 INTO cnt
1087 FROM bom_bill_of_materials
1089
1090 IF (mode_type = 2) THEN
1091 RETURN(0);
1092 ELSE
1093 raise not_unique;
1094 END IF;
1095 EXCEPTION
1096 WHEN no_data_found THEN
1097 null;
1098 WHEN not_unique THEN
1099 raise not_unique;
1100 END;
1101 /*
1102 ** check in interface table
1103 */
1104 stmt_num := 2;
1105 SELECT count(*)
1106 INTO cnt
1107 FROM bom_bill_of_mtls_interface
1108 WHERE bill_sequence_id = bom_seq_id
1109 AND transaction_type = G_Insert
1110 AND process_flag = 4;
1111
1112 IF (cnt = 0) THEN
1113 IF (mode_type = 1) THEN
1114 RETURN(0);
1115 ELSE
1116 raise NO_DATA_FOUND;
1117 END IF;
1118 END IF;
1119
1120 IF (cnt > 0) THEN
1121 IF (mode_type = 1) THEN
1122 raise NOT_UNIQUE;
1123 ELSE
1124 RETURN(0);
1125 END IF;
1126 END IF;
1127
1128 EXCEPTION
1129 WHEN No_Data_Found THEN
1130 err_text := substrb('Bom_Bill_Api(Exists): Bill does not exist '||
1131 SQLERRM,1,70);
1132 RETURN(9999);
1133 WHEN Not_Unique THEN
1134 err_text := 'Bom_Bill_Api(Exists) '||'Duplicate bill sequence id';
1135 RETURN(9999);
1136 WHEN others THEN
1137 err_text := 'Bom_Bill_Api(Exists-'||stmt_num||') '|| substrb(SQLERRM,1,60);
1138 RETURN(SQLCODE);
1139 END Verify_Bom_Seq_Id_Exists;
1140
1141
1142 /* ------------------------- Verify_Duplicate_Bom -------------------------- */
1143 /*
1144 NAME
1145 Verify_duplicate_bom
1146 DESCRIPTION
1147 Verifies in the production and interface tables if bom with
1148 same alt exists. Also verifies for an alternate bom, if the
1149 primary already exists.
1150
1151 REQUIRES
1152 org_id organization_id
1153 assy_id assembly_item_id
1154 alt_desg alternate routing designator
1155 err_text out buffer to return error message
1156 MODIFIES
1157 RETURNS
1158 0 if successful
1159 cnt if bom already exists
1160 9999 if primary does not exist
1161 SQLCODE if error
1162 NOTES
1163 -----------------------------------------------------------------------------*/
1164 FUNCTION Verify_Duplicate_Bom(
1165 org_id NUMBER,
1166 assy_id NUMBER,
1167 alt_desg VARCHAR2,
1168 assy_type NUMBER,
1169 err_text OUT VARCHAR2
1170 )
1171 return INTEGER
1172 IS
1173 cnt NUMBER := 0;
1174 ALREADY_EXISTS EXCEPTION;
1175 stmt_num NUMBER := 0;
1176 BEGIN
1177 /*
1178 ** Check if Bill Exists in Production
1179 */
1180 stmt_num := 1;
1181 BEGIN
1182 SELECT 1
1183 INTO cnt
1184 FROM bom_bill_of_materials
1185 WHERE organization_id = org_id
1186 AND assembly_item_id = assy_id
1187 AND nvl(alternate_bom_designator, 'NONE') =
1188 nvl(alt_desg, 'NONE');
1189 RAISE already_exists;
1190 EXCEPTION
1191 WHEN already_exists THEN
1192 err_text := 'Bom_Bill_Api(Duplicate): Bill already exists in production';
1193 RETURN(cnt);
1194 WHEN no_data_found THEN
1195 NULL;
1196 END;
1197 /*
1198 ** Check if Bill Exists in Interface Table
1199 */
1200 stmt_num := 2;
1201 BEGIN
1202 SELECT 1
1203 INTO cnt
1204 FROM bom_bill_of_mtls_interface
1205 WHERE organization_id = org_id
1206 AND assembly_item_id = assy_id
1207 AND nvl(alternate_bom_designator, 'NONE') =
1208 nvl(alt_desg, 'NONE')
1209 AND transaction_type = G_Insert
1210 AND rownum = 1
1211 AND process_flag = 4;
1212
1213 RAISE already_exists;
1214 EXCEPTION
1215 WHEN already_exists THEN
1216 err_text := 'Bom_Bill_Api(Duplicate): Bill already exists in interface';
1217 RETURN(cnt);
1218 WHEN no_data_found THEN
1219 NULL;
1220 END;
1221
1222 /*
1223 ** For alternate bills, verify if primary exists (or will exist)
1224 ** Alternate mfg bills cannot have primary eng bills
1225 */
1226 stmt_num := 3;
1227 IF (alt_desg is not null) THEN
1228 BEGIN
1229 SELECT 1
1230 INTO cnt
1231 FROM bom_bill_of_materials
1232 WHERE organization_id = org_id
1233 AND assembly_item_id = assy_id
1234 AND alternate_bom_designator is null
1235 AND ((assy_type = 2)
1236 OR
1237 (assy_type =1 and assembly_type = 1)
1238 );
1239 RETURN(0);
1240 EXCEPTION
1241 WHEN no_data_found THEN
1242 NULL;
1243 END;
1244
1245 stmt_num := 4;
1246 BEGIN
1247 SELECT bill_sequence_id
1248 INTO cnt
1252 AND alternate_bom_designator is null
1249 FROM bom_bill_of_mtls_interface
1250 WHERE organization_id = org_id
1251 AND assembly_item_id = assy_id
1253 AND ((assy_type = 2)
1254 OR
1255 (assy_type =1 and assembly_type = 1)
1256 )
1257 AND process_flag = 4
1258 AND transaction_type = G_Insert
1259 AND rownum = 1;
1260 EXCEPTION
1261 WHEN no_data_found THEN
1262 err_text := 'Bom_Bill_Api(Duplicate): Valid primary does not exist';
1263 RETURN(9999);
1264 END;
1265 END IF;
1266
1267 RETURN(0);
1268
1269 EXCEPTION
1270 WHEN others THEN
1271 err_text := 'Bom_Bill_Api(Duplicate-'||stmt_num||') '||substrb(SQLERRM,1,60);
1272 return(SQLCODE);
1273 END Verify_Duplicate_Bom;
1274
1275
1276 /* --------------------------- Verify_Common_Bom ----------------------------*/
1277 /*
1278 NAME
1279 Verify_common_bom
1280 DESCRIPTION
1281 if bom is mfg then it cannot point to engineerging bom
1282 if common bom then bill cannot have components
1283 if inter-org common then all components items must be in both orgs
1284 Common bill's org and current bill's org must have same master org
1285 Common bill's alt must be same as current bill's alt
1286 Common bill cannot have same assembly_item_id/org_id as current bill
1287 Common bill cannot reference a common bill
1288
1289 REQUIRES
1290 bom_id bill_sequence_id
1291 cmn_bom_id common bill_seqience_id
1292 bom_type assembly_type
1293 item_id assembly item id
1294 cmn_item_id common item id
1295 org_id org id
1296 cmn_org_id common org id
1297 err_text out buffer to return error message
1298 MODIFIES
1299 RETURNS
1300 0 if successful
1301 9999 if invalid item
1302 SQLCODE if error
1303 NOTES
1304 -----------------------------------------------------------------------------*/
1305 FUNCTION Verify_common_bom(
1306 bom_id NUMBER,
1307 cmn_bom_id NUMBER,
1308 bom_type NUMBER,
1309 item_id NUMBER,
1310 cmn_item_id NUMBER,
1311 org_id NUMBER,
1312 cmn_org_id NUMBER,
1313 alt_desg VARCHAR2,
1314 err_text OUT VARCHAR2
1315 )
1316 return INTEGER
1317 IS
1318 cnt NUMBER;
1319 bit NUMBER;
1320 base_id NUMBER;
1321 ato VARCHAR2(1);
1322 pto VARCHAR2(1);
1323 MISSING_ITEMS EXCEPTION;
1324 MISSING_SUB_ITEMS EXCEPTION;
1325 stmt_num NUMBER := 0;
1326
1327 BEGIN
1328 /*
1329 ** Common bill's org and current bill's org must have same master org
1330 */
1331 stmt_num := 1;
1332 BEGIN
1333 SELECT 1
1334 INTO cnt
1335 FROM mtl_parameters mp1, mtl_parameters mp2
1336 WHERE mp1.organization_id = org_id
1337 AND mp2.organization_id = cmn_org_id
1338 AND mp1.master_organization_id = mp2.master_organization_id;
1339 EXCEPTION
1340 WHEN no_data_found THEN
1341 err_text := 'Bom_Bill_Api(Common): Invalid common master org id';
1342 RETURN(9999);
1343 END;
1344 /*
1345 ** Common bill's alt must be same as current bill's alt
1346 ** Common bill cannot have same assembly_item_id/org_id as current bill
1347 ** Common bill must be mfg bill if current bill is a mfg bill
1348 ** Common bill cannot reference a common bill
1349 ** Common bill sequence id must have the correct common_assembly_item_id
1350 ** and common_organization_id
1351 */
1352 stmt_num := 2;
1353 BEGIN
1354 SELECT bill_sequence_id
1355 INTO cnt
1356 FROM bom_bill_of_materials
1357 WHERE bill_sequence_id = cmn_bom_id
1358 AND assembly_item_id = cmn_item_id
1359 AND organization_id = cmn_org_id
1360 AND nvl(alternate_bom_designator, 'NONE') = nvl(alt_desg, 'NONE')
1361 AND common_bill_sequence_id = bill_sequence_id
1362 AND (assembly_item_id <> item_id
1363 OR
1364 organization_id <> org_id)
1365 AND ((bom_type <> 1)
1366 OR
1367 (bom_type = 1 AND assembly_type = 1));
1368 GOTO check_ops;
1369 EXCEPTION
1370 WHEN no_data_found THEN
1371 null;
1372 END;
1373
1374 stmt_num := 3;
1375 SELECT bill_sequence_id
1376 INTO cnt
1377 FROM bom_bill_of_mtls_interface
1378 WHERE bill_sequence_id = cmn_bom_id
1379 AND assembly_item_id = cmn_item_id
1380 AND organization_id = cmn_org_id
1381 AND nvl(alternate_bom_designator, 'NONE') = nvl(alt_desg, 'NONE')
1382 AND common_bill_sequence_id = bill_sequence_id
1383 AND (assembly_item_id <> item_id
1384 OR
1385 organization_id <> org_id)
1386 AND process_flag = 4
1387 AND transaction_type in (G_Insert, G_Update)
1388 AND ((bom_type <> 1)
1389 OR
1390 (bom_type = 1 AND assembly_type = 1));
1391 <<check_ops>>
1392
1393 /*
1397 IF (org_id <> cmn_org_id) THEN
1394 ** check to see if components exist in both orgs for inter-org commons
1395 */
1396 stmt_num := 4;
1398 -- Get item attributes for the bill
1399 SELECT bom_item_type, base_item_id, replenish_to_order_flag,
1400 pick_components_flag
1401 INTO bit, base_id, ato, pto
1402 FROM mtl_system_items
1403 WHERE inventory_item_id = item_id
1404 AND organization_id = org_id;
1405
1406 stmt_num := 5;
1407 SELECT count(*)
1408 INTO cnt
1409 FROM bom_inventory_components bic
1410 WHERE bic.bill_sequence_id = cmn_bom_id
1411 AND not exists
1412 (SELECT 'x'
1413 FROM mtl_system_items s
1414 WHERE s.organization_id = org_id
1415 AND s.inventory_item_id = bic.component_item_id
1416 AND ((bom_type = 1 AND s.eng_item_flag = 'N')
1417 OR (bom_type = 2))
1418 AND s.bom_enabled_flag = 'Y'
1419 AND s.inventory_item_id <> item_id
1420 AND ((bit = 1 AND s.bom_item_type <> 3)
1421 OR (bit = 2 AND s.bom_item_type <> 3)
1422 OR (bit = 3)
1423 OR (bit = 4
1424 AND (s.bom_item_type = 4
1425 OR (s.bom_item_type IN (2, 1)
1426 AND s.replenish_to_order_flag = 'Y'
1427 AND base_id IS NOT NULL
1428 AND ato = 'Y'))))
1429 AND (bit = 3
1430 OR
1431 pto = 'Y'
1432 OR
1433 s.pick_components_flag = 'N')
1434 AND (bit = 3
1435 OR
1436 NVL(s.bom_item_type, 4) <> 2
1437 OR
1438 (s.bom_item_type = 2
1439 AND ((pto = 'Y'
1440 AND s.pick_components_flag = 'Y')
1441 OR (ato = 'Y'
1442 AND s.replenish_to_order_flag = 'Y'))))
1443 AND not(bit = 4
1444 AND pto = 'Y'
1445 AND s.bom_item_type = 4
1446 AND s.replenish_to_order_flag = 'Y')
1447 );
1448
1449 IF (cnt > 0) THEN
1450 RAISE missing_items;
1451 END IF;
1452 END IF;
1453 /*
1454 ** check if substitute components exist in both orgs for inter-org commons
1455 */
1456 stmt_num := 6;
1457 IF (org_id <> cmn_org_id) THEN /* Comp and sub comp in production */
1458 SELECT count(*)
1459 INTO cnt
1460 FROM bom_inventory_components bic,
1461 bom_substitute_components bsc
1462 WHERE bic.bill_sequence_id = cmn_bom_id
1463 AND bic.component_sequence_id = bsc.component_sequence_id
1464 AND bsc.substitute_component_id not in
1465 (select msi1.inventory_item_id
1466 from mtl_system_items msi1, mtl_system_items msi2
1467 where msi1.organization_id = org_id
1468 and msi1.inventory_item_id = bsc.substitute_component_id
1469 and msi2.organization_id = cmn_org_id
1470 and msi2.inventory_item_id = msi1.inventory_item_id);
1471 IF (cnt > 0) THEN
1472 raise MISSING_SUB_ITEMS;
1473 END IF;
1474 END IF;
1475
1476 /*
1477 ** check to see if bill item and common item have same bom_item_type,
1478 ** pick_components_flag and replenish_to_order_flag
1479 ** Common item must have bom_enabled_flag = 'Y'
1480 */
1481 stmt_num := 7;
1482 BEGIN
1483 SELECT 1
1484 INTO cnt
1485 FROM mtl_system_items msi1, mtl_system_items msi2
1486 WHERE msi1.organization_id = org_id
1487 AND msi1.inventory_item_id = item_id
1488 AND msi2.organization_id = cmn_org_id
1489 AND msi2.inventory_item_id = cmn_item_id
1490 AND msi2.bom_enabled_flag = 'Y'
1491 AND msi1.bom_item_type = msi2.bom_item_type
1492 AND msi1.pick_components_flag = msi2.pick_components_flag
1493 AND msi1.replenish_to_order_flag = msi2.replenish_to_order_flag;
1494 EXCEPTION
1495 WHEN no_data_found THEN
1496 err_text := 'Bom_Bill_Api(Common): Invalid item attributes';
1497 RETURN(9999);
1498 END;
1499
1500 RETURN(0);
1501 EXCEPTION
1502 WHEN No_Data_Found THEN
1503 err_text := 'Bom_Bill_Api(Common):Invalid common bill';
1504 RETURN(9999);
1505 WHEN Missing_Items THEN
1506 err_text := 'Bom_Bill_Api(Common): Component items not in both orgs or invalid';
1507 RETURN(9999);
1508 WHEN Missing_Sub_Items THEN
1509 err_text := 'Bom_Bill_Api(Common): Substitute items not in both orgs';
1510 RETURN(9999);
1511 WHEN others THEN
1512 err_text := 'Bom_Bill_Api(Common-'||stmt_num||') '||substrb(SQLERRM,1,60);
1513 RETURN(SQLCODE);
1514 END Verify_Common_Bom;
1515
1516
1520 Validate_Bill
1517 /* ----------------------------- Validate_Bill ----------------------------- */
1518 /*
1519 NAME
1521 DESCRIPTION
1522 Validate component sequence id
1523 Validate substitute component id
1524 Verify there are no substitute components for Planning bills or
1525 non-Standard components
1526 Verify substitute component is unique for a component
1527 Verify substitute component is not the same as the bill or component
1528 Verify substitute quantity is not zero
1529 REQUIRES
1530 err_text out buffer to return error message
1531 MODIFIES
1532 MTL_INTERFACE_ERRORS
1533 RETURNS
1534 0 if successful
1535 SQLCODE if unsuccessful
1536 NOTES
1537 -----------------------------------------------------------------------------*/
1538 FUNCTION Validate_Bill (
1539 org_id NUMBER,
1540 all_org NUMBER := 2,
1541 user_id NUMBER,
1542 login_id NUMBER,
1543 prog_appid NUMBER,
1544 prog_id NUMBER,
1545 req_id NUMBER,
1546 err_text IN OUT VARCHAR2
1547 )
1548 return INTEGER
1549 IS
1550 /*
1551 ** Select all INSERTS
1552 */
1553 CURSOR C1 IS
1554 SELECT organization_id OI, bill_sequence_id BSI,
1555 assembly_item_id AII, common_bill_sequence_id CBSI,
1556 common_assembly_item_id CAII, assembly_type AST,
1557 common_organization_id COI, transaction_type A,
1558 alternate_bom_designator ABD, transaction_id TI,
1559 pending_from_ecn PFE
1560 FROM bom_bill_of_mtls_interface
1561 WHERE process_flag = 2
1562 AND transaction_type = G_Insert
1563 AND rownum < G_rows_to_commit;
1564
1565 /*
1566 ** Select all UPDATES and DELETES
1567 */
1568 CURSOR c2 is
1569 SELECT organization_id OI, bill_sequence_id BSI,
1570 assembly_item_id AII, common_bill_sequence_id CBSI,
1571 common_assembly_item_id CAII, assembly_type AST,
1572 common_organization_id COI, transaction_type A,
1573 alternate_bom_designator ABD, transaction_id TI,
1574 next_explode_date NED, creation_date CD,
1575 specific_assembly_comment SAC, created_by CB,
1576 attribute_category AC, attribute1 A1, attribute2 A2,
1577 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
1578 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
1579 attribute11 A11, attribute12 A12, attribute13 A13,
1580 attribute14 A14,attribute15 A15, pending_from_ecn PFE,
1581 request_id RI, program_application_id PAI, program_id PI,
1582 program_update_date PUD
1583 FROM bom_bill_of_mtls_interface
1584 WHERE process_flag = 2
1585 AND transaction_type in (G_Update, G_Delete)
1586 AND rownum < G_rows_to_commit;
1587 /*
1588 ** Select UPDATES for Common Bill Verification
1589 */
1590 CURSOR c3 is
1591 SELECT organization_id OI, bill_sequence_id BSI,
1592 assembly_item_id AII, common_bill_sequence_id CBSI,
1593 common_assembly_item_id CAII, assembly_type AST,
1594 common_organization_id COI, transaction_type A,
1595 alternate_bom_designator ABD, transaction_id TI
1596 FROM bom_bill_of_mtls_interface
1597 WHERE process_flag = 99
1598 AND transaction_type = G_Update
1599 AND rownum < G_rows_to_commit;
1600
1601 ret_code NUMBER;
1602 stmt_num NUMBER := 0;
1603 dummy_id NUMBER;
1604 commit_cnt NUMBER;
1605 continue_loop BOOLEAN := TRUE;
1606 x_bom_item_type NUMBER;
1607 X_creation_date DATE;
1608 X_created_by NUMBER;
1609 X_common_assembly_item_id NUMBER;
1610 X_specific_assembly_comment VARCHAR2(240);
1611 X_pending_from_ecn VARCHAR2(10);
1612 X_attribute_category VARCHAR2(30);
1613 X_attribute1 VARCHAR2(150);
1614 X_attribute2 VARCHAR2(150);
1615 X_attribute3 VARCHAR2(150);
1616 X_attribute4 VARCHAR2(150);
1617 X_attribute5 VARCHAR2(150);
1618 X_attribute6 VARCHAR2(150);
1619 X_attribute7 VARCHAR2(150);
1620 X_attribute8 VARCHAR2(150);
1621 X_attribute9 VARCHAR2(150);
1622 X_attribute10 VARCHAR2(150);
1623 X_attribute11 VARCHAR2(150);
1624 X_attribute12 VARCHAR2(150);
1625 X_attribute13 VARCHAR2(150);
1626 X_attribute14 VARCHAR2(150);
1627 X_attribute15 VARCHAR2(150);
1628 X_request_id NUMBER;
1629 X_program_application_id NUMBER;
1630 X_program_id NUMBER;
1631 X_program_update_date DATE;
1632 X_assembly_type NUMBER;
1633 X_common_bill_sequence_id NUMBER;
1634 X_common_organization_id NUMBER;
1635 X_next_explode_date DATE;
1636
1637 BEGIN
1638 /*
1639 ** FOR INSERTS - Validate
1640 */
1641 WHILE continue_loop LOOP
1642 commit_cnt := 0;
1646 stmt_num := 1;
1643 FOR c1rec IN c1 LOOP
1644 commit_cnt := commit_cnt + 1;
1645 x_bom_item_type := null;
1647 /*
1648 ** Verify org id
1649 */
1650 BEGIN
1651 SELECT organization_id
1652 INTO dummy_id
1653 FROM mtl_parameters
1654 WHERE organization_id = c1rec.OI;
1655 EXCEPTION
1656 WHEN no_data_found THEN
1657 ret_code := INVPUOPI.mtl_log_interface_err(
1658 org_id => c1rec.OI,
1659 user_id => user_id,
1660 login_id => login_id,
1661 prog_appid => prog_appid,
1662 prog_id => prog_id,
1663 req_id => req_id,
1664 trans_id => c1rec.TI,
1665 error_text => err_text,
1666 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1667 msg_name => 'BOM_INVALID_ORG_ID',
1668 err_text => err_text);
1669 UPDATE bom_bill_of_mtls_interface
1670 SET process_flag = 3
1671 WHERE transaction_id = c1rec.TI;
1672
1673 IF (ret_code <> 0) THEN
1674 RETURN(ret_code);
1675 END IF;
1676 GOTO continue_loop;
1677 END;
1678 /*
1679 ** Check for Product Family item
1680 */
1681 stmt_num := 1.1;
1682 DECLARE
1683 CURSOR GetBOMItemType IS
1684 SELECT bom_item_type
1685 FROM mtl_system_items
1686 WHERE organization_id = c1rec.OI
1687 AND inventory_item_id = c1rec.AII;
1688 BEGIN
1689 FOR c1 IN GetBOMItemType LOOP
1690 x_bom_item_type := c1.bom_item_type;
1691 END LOOP;
1692
1693 IF (x_bom_item_type is null) THEN
1694 ret_code := INVPUOPI.mtl_log_interface_err(
1695 org_id => NULL,
1696 user_id => user_id,
1697 login_id => login_id,
1698 prog_appid => prog_appid,
1699 prog_id => prog_id,
1700 req_id => req_id,
1701 trans_id => c1rec.TI,
1702 error_text => err_text,
1703 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1704 msg_name => 'BOM_ASSY_ITEM_MISSING',
1705 err_text => err_text);
1706 UPDATE bom_bill_of_mtls_interface
1707 SET process_flag = 3
1708 WHERE transaction_id = c1rec.TI;
1709
1710 IF (ret_code <> 0) THEN
1711 RETURN(ret_code);
1712 END IF;
1713 GOTO continue_loop;
1714 ELSIF (x_bom_item_type = G_ProductFamily) THEN
1715 GOTO Check_Bill_Seq_Id;
1716 END IF;
1717 END;
1718
1719 /*
1720 ** Verify Alternate Designator
1721 */
1722 stmt_num := 2;
1723 IF (c1rec.ABD is not null) THEN
1724 BEGIN
1725 SELECT 1
1726 INTO dummy_id
1727 FROM bom_alternate_designators
1728 WHERE organization_id = c1rec.OI
1729 AND alternate_designator_code = c1rec.ABD;
1730 EXCEPTION
1731 WHEN no_data_found THEN
1732 ret_code := INVPUOPI.mtl_log_interface_err(
1733 org_id => c1rec.OI,
1734 user_id => user_id,
1735 login_id => login_id,
1736 prog_appid => prog_appid,
1737 prog_id => prog_id,
1738 req_id => req_id,
1739 trans_id => c1rec.TI,
1740 error_text => err_text,
1741 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1742 msg_name => 'BOM_INVALID_ALTERNATE',
1743 err_text => err_text);
1744 UPDATE bom_bill_of_mtls_interface
1745 SET process_flag = 3
1746 WHERE transaction_id = c1rec.TI;
1747
1748 IF (ret_code <> 0) THEN
1749 RETURN(ret_code);
1750 END IF;
1751 GOTO continue_loop;
1752 END;
1753 END IF;
1754 /*
1755 ** Verify Assembly Item Id
1756 */
1757 stmt_num := 3;
1758 BEGIN
1759 SELECT 1
1760 INTO dummy_id
1761 FROM mtl_system_items
1762 WHERE organization_id = c1rec.OI
1763 AND inventory_item_id = c1rec.AII;
1764 EXCEPTION
1765 WHEN no_data_found THEN
1766 ret_code := INVPUOPI.mtl_log_interface_err(
1767 org_id => c1rec.OI,
1768 user_id => user_id,
1769 login_id => login_id,
1770 prog_appid => prog_appid,
1771 prog_id => prog_id,
1772 req_id => req_id,
1776 msg_name => 'BOM_ASSEMBLY_ITEM_INVALID',
1773 trans_id => c1rec.TI,
1774 error_text => err_text,
1775 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1777 err_text => err_text);
1778 UPDATE bom_bill_of_mtls_interface
1779 SET process_flag = 3
1780 WHERE transaction_id = c1rec.TI;
1781
1782 IF (ret_code <> 0) THEN
1783 RETURN(ret_code);
1784 END IF;
1785 GOTO continue_loop;
1786 END;
1787
1788 /*
1789 ** Verify Pending_From_Eco
1790 */
1791 stmt_num := 3.5;
1792 if ( c1rec.PFE is not null) then
1793 BEGIN
1794 SELECT 1
1795 INTO dummy_id
1796 FROM eng_engineering_changes
1797 WHERE organization_id = c1rec.OI
1798 AND change_notice = c1rec.PFE;
1799 EXCEPTION
1800 WHEN no_data_found THEN
1801 ret_code := INVPUOPI.mtl_log_interface_err(
1802 org_id => c1rec.OI,
1803 user_id => user_id,
1804 login_id => login_id,
1805 prog_appid => prog_appid,
1806 prog_id => prog_id,
1807 req_id => req_id,
1808 trans_id => c1rec.TI,
1809 error_text => err_text,
1810 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1811 msg_name => 'ENG_PARENTECO_NOT_EXIST',
1812 err_text => err_text);
1813 UPDATE bom_bill_of_mtls_interface
1814 SET process_flag = 3
1815 WHERE transaction_id = c1rec.TI;
1816
1817 IF (ret_code <> 0) THEN
1818 RETURN(ret_code);
1819 END IF;
1820 GOTO continue_loop;
1821 END;
1822 END IF;
1823
1824 /*
1825 ** Bill must be mfg or eng
1826 */
1827 stmt_num := 4;
1828 IF (c1rec.AST <> 1) AND (c1rec.AST <> 2) THEN
1829 ret_code := INVPUOPI.mtl_log_interface_err(
1830 org_id => c1rec.OI,
1831 user_id => user_id,
1832 login_id => login_id,
1833 prog_appid => prog_appid,
1834 prog_id => prog_id,
1835 req_id => req_id,
1836 trans_id => c1rec.TI,
1837 error_text => err_text,
1838 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1839 msg_name => 'BOM_ASSEMBLY_TYPE_INVALID',
1840 err_text => err_text);
1841 UPDATE bom_bill_of_mtls_interface
1842 SET process_flag = 3
1843 WHERE transaction_id = c1rec.TI;
1844
1845 IF (ret_code <> 0) THEN
1846 RETURN(ret_code);
1847 END IF;
1848 GOTO continue_loop;
1849 END IF;
1850 /*
1851 ** Verify bill seq id is unique
1852 */
1853 <<Check_Bill_Seq_Id>>
1854 stmt_num := 5;
1855 ret_code := Verify_Bom_Seq_Id_Exists(
1856 bom_seq_id => c1rec.BSI,
1857 mode_type => 1,
1858 err_text => err_text);
1859 IF (ret_code <> 0) THEN
1860 ret_code := INVPUOPI.mtl_log_interface_err(
1861 org_id => c1rec.OI,
1862 user_id => user_id,
1863 login_id => login_id,
1864 prog_appid => prog_appid,
1865 prog_id => prog_id,
1866 req_id => req_id,
1867 trans_id => c1rec.TI,
1868 error_text => err_text,
1869 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1870 msg_name => 'BOM_DUPLICATE_BILL',
1871 err_text => err_text);
1872 UPDATE bom_bill_of_mtls_interface
1873 SET process_flag = 3
1874 WHERE transaction_id = c1rec.TI;
1875
1876 IF (ret_code <> 0) THEN
1877 RETURN(ret_code);
1878 END IF;
1879 goto continue_loop;
1880 END IF;
1881
1882 /*
1883 ** Check for duplicate assy,org,alt combo
1884 ** Check for primary/alternate rule violation
1885 */
1886 stmt_num := 6;
1887 ret_code := Verify_Duplicate_Bom(
1888 org_id => c1rec.OI,
1889 assy_id => c1rec.AII,
1890 alt_desg => c1rec.ABD,
1891 assy_type => c1rec.AST,
1892 err_text => err_text);
1893 IF (ret_code <> 0) THEN
1894 ret_code := INVPUOPI.mtl_log_interface_err(
1895 org_id => c1rec.OI,
1896 user_id => user_id,
1897 login_id => login_id,
1898 prog_appid => prog_appid,
1899 prog_id => prog_id,
1900 req_id => req_id,
1901 trans_id => c1rec.TI,
1902 error_text => err_text,
1903 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1907 SET process_flag = 3
1904 msg_name => 'BOM_BILL_VALIDATION_ERR',
1905 err_text => err_text);
1906 UPDATE bom_bill_of_mtls_interface
1908 WHERE transaction_id = c1rec.TI;
1909
1910 IF (ret_code <> 0) THEN
1911 RETURN(ret_code);
1912 END IF;
1913 GOTO continue_loop;
1914 END IF;
1915
1916 /*
1917 ** Skip logic for Product Family items
1918 */
1919 IF (x_bom_item_type = G_ProductFamily) THEN
1920 GOTO Set_Process_Flag;
1921 END IF;
1922 /*
1923 ** Check assembly type and BOM enabled flag
1924 */
1925 stmt_num := 7;
1926 BEGIN
1927 SELECT 1
1928 INTO dummy_id
1929 FROM mtl_system_items
1930 WHERE organization_id = c1rec.OI
1931 AND inventory_item_id = c1rec.AII
1932 AND bom_enabled_flag = 'Y'
1933 AND ((c1rec.AST = 2)
1934 OR
1935 (c1rec.AST = 1 AND
1936 eng_item_flag = 'N'));
1937 EXCEPTION
1938 WHEN no_data_found THEN
1939 ret_code := INVPUOPI.mtl_log_interface_err(
1940 org_id => c1rec.OI,
1941 user_id => user_id,
1942 login_id => login_id,
1943 prog_appid => prog_appid,
1944 prog_id => prog_id,
1945 req_id => req_id,
1946 trans_id => c1rec.TI,
1947 error_text => err_text,
1948 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1949 msg_name => 'BOM_ASSY_TYPE_ERR',
1950 err_text => err_text);
1951 UPDATE bom_bill_of_mtls_interface
1952 SET process_flag = 3
1953 WHERE transaction_id = c1rec.TI;
1954
1955 IF (ret_code <> 0) THEN
1956 RETURN(ret_code);
1957 END IF;
1958 GOTO continue_loop;
1959 END;
1960 /*
1961 ** Check if common bill seq id exists
1962 */
1963 stmt_num := 8;
1964 IF (c1rec.BSI = c1rec.CBSI) THEN
1965 null;
1966 ELSE
1967 ret_code :=Verify_Bom_Seq_Id_Exists(
1968 bom_seq_id => c1rec.CBSI,
1969 mode_type => 2,
1970 err_text => err_text);
1971 IF (ret_code <> 0) THEN
1972 ret_code := INVPUOPI.mtl_log_interface_err(
1973 org_id => c1rec.OI,
1974 user_id => user_id,
1975 login_id => login_id,
1976 prog_appid => prog_appid,
1977 prog_id => prog_id,
1978 req_id => req_id,
1979 trans_id => c1rec.TI,
1980 error_text => err_text,
1981 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
1982 msg_name => 'BOM_COMMON_BILL_NOT_EXIST',
1983 err_text => err_text);
1984 UPDATE bom_bill_of_mtls_interface
1985 SET process_flag = 3
1986 WHERE transaction_id = c1rec.TI;
1987
1988 IF (ret_code <> 0) THEN
1989 RETURN(ret_code);
1990 END IF;
1991 GOTO continue_loop;
1992 END IF;
1993 /*
1994 ** Verify common bill attributes
1995 */
1996 stmt_num := 9;
1997 ret_code :=Verify_Common_Bom(
1998 bom_id => c1rec.BSI,
1999 cmn_bom_id => c1rec.CBSI,
2000 bom_type => c1rec.AST,
2001 item_id => c1rec.AII,
2002 cmn_item_id => c1rec.CAII,
2003 org_id => c1rec.OI,
2004 cmn_org_id => c1rec.COI,
2005 alt_desg => c1rec.ABD,
2006 err_text => err_text);
2007 IF (ret_code <> 0) THEN
2008 ret_code := INVPUOPI.mtl_log_interface_err(
2009 org_id => c1rec.OI,
2010 user_id => user_id,
2011 login_id => login_id,
2012 prog_appid => prog_appid,
2013 prog_id => prog_id,
2014 req_id => req_id,
2015 trans_id => c1rec.TI,
2016 error_text => err_text,
2017 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2018 msg_name => 'BOM_COMMON_BOM_ERROR',
2019 err_text => err_text);
2020 UPDATE bom_bill_of_mtls_interface
2021 SET process_flag = 3
2022 WHERE transaction_id = c1rec.TI;
2023
2024 IF (ret_code <> 0) THEN
2025 RETURN(ret_code);
2026 END IF;
2027 GOTO continue_loop;
2028 END IF;
2029 END IF;
2030 /*
2031 ** Set Process Flag to 4
2032 */
2033
2034 <<Set_Process_Flag>>
2035 stmt_num := 10;
2036 UPDATE bom_bill_of_mtls_interface
2037 SET process_flag = 4
2041 NULL;
2038 WHERE transaction_id = c1rec.TI;
2039
2040 <<continue_loop>>
2042 END LOOP;
2043
2044 stmt_num := 11;
2045 COMMIT;
2046
2047 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2048 continue_loop := FALSE;
2049 END IF;
2050 END LOOP;
2051
2052
2053 /*
2054 ** Update "Update" Records and validate "Delete" records
2055 */
2056 continue_loop := TRUE;
2057 WHILE continue_loop LOOP
2058 commit_cnt := 0;
2059 FOR c2rec IN c2 LOOP
2060 commit_cnt := commit_cnt + 1;
2061 x_bom_item_type := null;
2062 /*
2063 ** Check if record exists in Production
2064 */
2065 stmt_num := 12;
2066 BEGIN
2067 SELECT bom.creation_date, bom.created_by,
2068 bom.common_assembly_item_id,
2069 bom.specific_assembly_comment, bom.pending_from_ecn,
2070 bom.attribute_category, bom.attribute1,
2071 bom.attribute2, bom.attribute3, bom.attribute4,
2072 bom.attribute5,
2073 bom.attribute6, bom.attribute7, bom.attribute8,
2074 bom.attribute9,
2075 bom.attribute10, bom.attribute11, bom.attribute12,
2076 bom.attribute13,
2077 bom.attribute14, bom.attribute15, bom.request_id,
2078 bom.program_application_id, bom.program_id,
2079 bom.program_update_date,
2080 bom.assembly_type, bom.common_bill_sequence_id,
2081 bom.common_organization_id, bom.next_explode_date,
2082 msi.bom_item_type
2083 INTO X_creation_date, X_created_by, X_common_assembly_item_id,
2084 X_specific_assembly_comment, X_pending_from_ecn,
2085 X_attribute_category, X_attribute1,
2086 X_attribute2, X_attribute3, X_attribute4, X_attribute5,
2087 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
2088 X_attribute10, X_attribute11, X_attribute12, X_attribute13,
2089 X_attribute14, X_attribute15, X_request_id,
2090 X_program_application_id, X_program_id,
2091 X_program_update_date,
2092 X_assembly_type, X_common_bill_sequence_id,
2093 X_common_organization_id, X_next_explode_date,
2094 x_bom_item_type
2095 FROM bom_bill_of_materials bom,
2096 mtl_system_items msi
2097 WHERE bill_sequence_id = c2rec.BSI
2098 AND msi.organization_id = bom.organization_id
2099 AND msi.inventory_item_id = bom.assembly_item_id;
2100 EXCEPTION
2101 WHEN No_Data_Found THEN
2102 ret_code := INVPUOPI.mtl_log_interface_err(
2103 org_id => c2rec.OI,
2104 user_id => user_id,
2105 login_id => login_id,
2106 prog_appid => prog_appid,
2107 prog_id => prog_id,
2108 req_id => req_id,
2109 trans_id => c2rec.TI,
2110 error_text => err_text,
2111 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2112 msg_name => 'BOM_BILL_RECORD_MISSING',
2113 err_text => err_text);
2114
2115 UPDATE bom_bill_of_mtls_interface
2116 SET process_flag = 3
2117 WHERE transaction_id = c2rec.TI;
2118
2119 IF (ret_code <> 0) THEN
2120 return(ret_code);
2121 END IF;
2122 GOTO continue_loop1;
2123 END;
2124 /*
2125 ** ONLY for "Updates"
2126 */
2127 IF (c2rec.A = G_Update) THEN
2128 IF (x_bom_item_type <> G_ProductFamily) THEN
2129 /*
2130 ** Check if column is non-updatable
2131 */
2132 stmt_num := 13;
2133 IF (c2rec.CD is not null
2134 OR c2rec.CB is not null
2135 OR c2rec.PFE is not null
2136 OR c2rec.AST is not null
2137 OR c2rec.NED is not null) THEN
2138 ret_code := INVPUOPI.mtl_log_interface_err(
2139 org_id => c2rec.OI,
2140 user_id => user_id,
2141 login_id => login_id,
2142 prog_appid => prog_appid,
2143 prog_id => prog_id,
2144 req_id => req_id,
2145 trans_id => c2rec.TI,
2146 error_text => err_text,
2147 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2148 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
2149 err_text => err_text);
2150
2151 UPDATE bom_bill_of_mtls_interface
2152 SET process_flag = 3
2153 WHERE transaction_id = c2rec.TI;
2154
2155 IF (ret_code <> 0) THEN
2156 return(ret_code);
2157 END IF;
2158 GOTO continue_loop1;
2159 END IF;
2160 /*
2161 ** Update interface record with production record's values
2162 */
2163
2164 stmt_num := 14;
2165 UPDATE bom_bill_of_mtls_interface
2166 SET creation_date = X_creation_date,
2170 common_assembly_item_id = decode(c2rec.CBSI, null,
2167 created_by = X_created_by,
2168 assembly_type = X_assembly_type,
2169 next_explode_date = X_next_explode_date,
2171 X_common_assembly_item_id, G_NullNum, '',
2172 c2rec.CAII),
2173 common_bill_sequence_id = decode(c2rec.CBSI, null,
2174 X_common_bill_sequence_id, G_NullNum, c2rec.BSI,
2175 c2rec.CBSI),
2176 common_organization_id = decode(c2rec.CBSI, null,
2177 X_common_organization_id, G_NullNum, '', c2rec.COI),
2178 specific_assembly_comment = decode(c2rec.SAC, G_NullChar,
2179 null, null, X_specific_assembly_comment, c2rec.SAC),
2180 pending_from_ecn = X_pending_from_ecn,
2181 attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
2182 X_attribute_category, c2rec.AC),
2183 attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
2184 X_attribute1, c2rec.A1),
2185 attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
2186 X_attribute2, c2rec.A2),
2187 attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
2188 X_attribute3, c2rec.A3),
2189 attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
2190 X_attribute4, c2rec.A4),
2191 attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
2192 X_attribute5, c2rec.A5),
2193 attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
2194 X_attribute6, c2rec.A6),
2195 attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
2196 X_attribute7, c2rec.A7),
2197 attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
2198 X_attribute8, c2rec.A8),
2199 attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
2200 X_attribute9, c2rec.A9),
2201 attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
2202 X_attribute10, c2rec.A10),
2203 attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
2204 X_attribute11, c2rec.A11),
2205 attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
2206 X_attribute12, c2rec.A12),
2207 attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
2208 X_attribute13, c2rec.A13),
2209 attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
2210 X_attribute14, c2rec.A14),
2211 attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
2212 X_attribute15, c2rec.A15),
2213 request_id = decode(c2rec.RI, G_NullChar, '', NULL,
2214 X_request_id, c2rec.RI),
2215 program_application_id = decode(c2rec.PAI, G_NullNum,
2216 '', NULL, X_program_application_id, c2rec.PAI),
2217 program_id = decode(c2rec.PI, G_NullNum, '', NULL,
2218 X_program_id, c2rec.PI),
2219 program_update_date = decode(c2rec.PUD, G_NullDate, '',
2220 NULL,X_program_update_date, c2rec.PUD),
2221 process_flag = 99
2222 WHERE transaction_id = c2rec.TI;
2223 ELSE
2224 /*
2225 ** For Product Families
2226 */
2227 stmt_num := 13;
2228 IF (c2rec.CD is not null
2229 OR c2rec.CB is not null
2230 OR c2rec.CAII is not null
2231 OR c2rec.CBSI is not null
2232 OR c2rec.COI is not null
2233 OR c2rec.SAC is not null
2234 OR c2rec.PFE is not null
2235 OR c2rec.AST is not null
2236 OR c2rec.NED is not null) THEN
2237 ret_code := INVPUOPI.mtl_log_interface_err(
2238 org_id => c2rec.OI,
2239 user_id => user_id,
2240 login_id => login_id,
2241 prog_appid => prog_appid,
2242 prog_id => prog_id,
2243 req_id => req_id,
2244 trans_id => c2rec.TI,
2245 error_text => err_text,
2246 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2247 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
2248 err_text => err_text);
2249
2250 UPDATE bom_bill_of_mtls_interface
2251 SET process_flag = 3
2252 WHERE transaction_id = c2rec.TI;
2253
2254 IF (ret_code <> 0) THEN
2255 return(ret_code);
2256 END IF;
2257 GOTO continue_loop1;
2258
2259 END IF;
2260 /*
2264 stmt_num := 14;
2261 ** Update interface record with production record's values
2262 */
2263
2265 UPDATE bom_bill_of_mtls_interface
2266 SET creation_date = X_creation_date,
2267 created_by = X_created_by,
2268 assembly_type = X_assembly_type,
2269 next_explode_date = X_next_explode_date,
2270 common_assembly_item_id = X_common_assembly_item_id,
2271 common_bill_sequence_id = X_common_bill_sequence_id,
2272 common_organization_id = X_common_organization_id,
2273 specific_assembly_comment = X_specific_assembly_comment,
2274 pending_from_ecn = X_pending_from_ecn,
2275 attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
2276 X_attribute_category, c2rec.AC),
2277 attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
2278 X_attribute1, c2rec.A1),
2279 attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
2280 X_attribute2, c2rec.A2),
2281 attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
2282 X_attribute3, c2rec.A3),
2283 attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
2284 X_attribute4, c2rec.A4),
2285 attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
2286 X_attribute5, c2rec.A5),
2287 attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
2288 X_attribute6, c2rec.A6),
2289 attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
2290 X_attribute7, c2rec.A7),
2291 attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
2292 X_attribute8, c2rec.A8),
2293 attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
2294 X_attribute9, c2rec.A9),
2295 attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
2296 X_attribute10, c2rec.A10),
2297 attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
2298 X_attribute11, c2rec.A11),
2299 attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
2300 X_attribute12, c2rec.A12),
2301 attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
2302 X_attribute13, c2rec.A13),
2303 attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
2304 X_attribute14, c2rec.A14),
2305 attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
2306 X_attribute15, c2rec.A15),
2307 request_id = decode(c2rec.RI, G_NullChar, '', NULL,
2308 X_request_id, c2rec.RI),
2309 program_application_id = decode(c2rec.PAI, G_NullNum,
2310 '', NULL, X_program_application_id, c2rec.PAI),
2311 program_id = decode(c2rec.PI, G_NullNum, '', NULL,
2312 X_program_id, c2rec.PI),
2313 program_update_date = decode(c2rec.PUD, G_NullDate, '',
2314 NULL,X_program_update_date, c2rec.PUD),
2315 process_flag = 4 -- Don't pick up records in cursor c3
2316 WHERE transaction_id = c2rec.TI;
2317 END IF;
2318 ELSIF (c2rec.A = G_Delete) THEN
2319 /*
2320 ** Set Process Flag to 4 for "Deletes"
2321 */
2322 stmt_num := 15;
2323 UPDATE bom_bill_of_mtls_interface
2324 SET process_flag = 4
2325 WHERE transaction_id = c2rec.TI;
2326 END IF;
2327 <<continue_loop1>>
2328 NULL;
2329 END LOOP;
2330
2331 stmt_num := 16;
2332 COMMIT;
2333 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2334 continue_loop := FALSE;
2335 END IF;
2336
2337 END LOOP;
2338 /*
2339 ** Validate "Update" Records
2340 */
2341 continue_loop := TRUE;
2342 WHILE continue_loop LOOP
2343 commit_cnt := 0;
2344 FOR c3rec IN c3 LOOP
2345 commit_cnt := commit_cnt + 1;
2346 stmt_num := 17;
2347 /*
2348 ** Check if common bill seq id exists
2349 */
2350 IF (c3rec.BSI = c3rec.CBSI) THEN
2351 null;
2352 ELSIF (c3rec.CBSI = G_NullNum) THEN
2353 null;
2354 ELSE
2355 ret_code :=Verify_Bom_Seq_Id_Exists(
2356 bom_seq_id => c3rec.CBSI,
2357 mode_type => 2,
2358 err_text => err_text);
2359 IF (ret_code <> 0) THEN
2360 ret_code := INVPUOPI.mtl_log_interface_err(
2361 org_id => c3rec.OI,
2362 user_id => user_id,
2363 login_id => login_id,
2364 prog_appid => prog_appid,
2365 prog_id => prog_id,
2369 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2366 req_id => req_id,
2367 trans_id => c3rec.TI,
2368 error_text => err_text,
2370 msg_name => 'BOM_COMMON_BILL_NOT_EXIST',
2371 err_text => err_text);
2372 UPDATE bom_bill_of_mtls_interface
2373 SET process_flag = 3
2374 WHERE transaction_id = c3rec.TI;
2375
2376 IF (ret_code <> 0) THEN
2377 RETURN(ret_code);
2378 END IF;
2379 GOTO continue_loop2;
2380 END IF;
2381 /*
2382 ** Verify common bill attributes
2383 */
2384 stmt_num := 18;
2385 ret_code :=Verify_Common_Bom(
2386 bom_id => c3rec.BSI,
2387 cmn_bom_id => c3rec.CBSI,
2388 bom_type => c3rec.AST,
2389 item_id => c3rec.AII,
2390 cmn_item_id => c3rec.CAII,
2391 org_id => c3rec.OI,
2392 cmn_org_id => c3rec.COI,
2393 alt_desg => c3rec.ABD,
2394 err_text => err_text);
2395 IF (ret_code <> 0) THEN
2396 ret_code := INVPUOPI.mtl_log_interface_err(
2397 org_id => c3rec.OI,
2398 user_id => user_id,
2399 login_id => login_id,
2400 prog_appid => prog_appid,
2401 prog_id => prog_id,
2402 req_id => req_id,
2403 trans_id => c3rec.TI,
2404 error_text => err_text,
2405 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
2406 msg_name => 'BOM_COMMON_BOM_ERROR',
2407 err_text => err_text);
2408 UPDATE bom_bill_of_mtls_interface
2409 SET process_flag = 3
2410 WHERE transaction_id = c3rec.TI;
2411
2412 IF (ret_code <> 0) THEN
2413 RETURN(ret_code);
2414 END IF;
2415 GOTO continue_loop2;
2416 END IF;
2417 END IF;
2418 /*
2419 ** Set Process Flag to 4
2420 */
2421 stmt_num := 19;
2422 UPDATE bom_bill_of_mtls_interface
2423 SET process_flag = 4
2424 WHERE transaction_id = c3rec.TI;
2425
2426 <<continue_loop2>>
2427 NULL;
2428 END LOOP;
2429
2430 stmt_num := 20;
2431 COMMIT;
2432
2433 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2434 continue_loop := FALSE;
2435 END IF;
2436 END LOOP;
2437
2438 RETURN(0);
2439
2440 EXCEPTION
2441 WHEN others THEN
2442 err_text := 'Bom_Bill_Api(Validate-'||stmt_num||') '||substrb(SQLERRM,1,500);
2443 RETURN(SQLCODE);
2444 END Validate_Bill;
2445
2446
2447 /* ----------------------------- Transact_Bill ------------------------------*/
2448 /*
2449 NAME
2450 Transact_Bill
2451 DESCRIPTION
2452 Insert, update and delete bill data from the interface
2453 table, BOM_BILL_OF_MTLS_INTERFACE, into the production table,
2454 BOM_BILL_OF_MATERIALS.
2455 REQUIRES
2456 prog_appid Program application id
2457 prog_id Program id
2458 req_id Request id
2459 user_id User id
2460 login_id Login id
2461 MODIFIES
2462 BOM_BILL_OF_MATERIALS
2463 BOM_BILL_OF_MTLS_INTERFACE
2464 RETURNS
2465 0 if successful
2466 SQLCODE if error
2467 NOTES
2468 -----------------------------------------------------------------------------*/
2469 FUNCTION Transact_Bill
2470 ( user_id NUMBER,
2471 login_id NUMBER,
2472 prog_appid NUMBER,
2473 prog_id NUMBER,
2474 req_id NUMBER,
2475 err_text OUT VARCHAR2)
2476 return integer
2477 IS
2478 ret_code NUMBER;
2479 stmt_num NUMBER := 0;
2480 continue_loop BOOLEAN := TRUE;
2481 commit_cnt NUMBER;
2482 X_bill_group_name VARCHAR2(10);
2483 X_bill_group_description VARCHAR2(240);
2484 X_delete_group_seq_id NUMBER;
2485 X_new_group_seq_id NUMBER;
2486 X_delete_type NUMBER;
2487 X_error_message VARCHAR2(240);
2488
2489 /*
2490 ** Select "Update" bill records
2491 */
2492 CURSOR c1 IS
2493 SELECT bill_sequence_id BSI, common_assembly_item_id CAII,
2494 specific_assembly_comment SAC, common_bill_sequence_id CBSI,
2495 common_organization_id COI,
2496 last_update_date LUD, last_updated_by LUB,
2497 last_update_login LUL,
2498 attribute_category AC, attribute1 A1, attribute2 A2,
2499 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
2500 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
2501 attribute11 A11, attribute12 A12, attribute13 A13,
2502 attribute14 A14, attribute15 A15, request_id RI,
2503 program_application_id PAI, program_id PI,
2507 AND transaction_type = G_Update
2504 program_update_date PUD, transaction_id TI
2505 FROM bom_bill_of_mtls_interface
2506 WHERE process_flag = 4
2508 AND rownum < G_rows_to_commit;
2509 /*
2510 ** Select "Delete" bill records
2511 */
2512 CURSOR c2 IS
2513 SELECT bill_sequence_id BSI, assembly_type AST, organization_id OI,
2514 assembly_item_id AII, alternate_bom_designator ABD,
2515 transaction_id TI
2516 FROM bom_bill_of_mtls_interface
2517 WHERE process_flag = 4
2518 AND transaction_type = G_Delete
2519 AND rownum < G_rows_to_commit;
2520 BEGIN
2521 /*
2522 ** Insert bills
2523 */
2524 stmt_num := 1;
2525 LOOP
2526 INSERT INTO bom_bill_of_materials(
2527 assembly_item_id,
2528 organization_id,
2529 alternate_bom_designator,
2530 last_update_date,
2531 last_updated_by,
2532 creation_date,
2533 created_by,
2534 last_update_login,
2535 common_assembly_item_id,
2536 specific_assembly_comment,
2537 attribute_category,
2538 attribute1,
2539 attribute2,
2540 attribute3,
2541 attribute4,
2542 attribute5,
2543 attribute6,
2544 attribute7,
2545 attribute8,
2546 attribute9,
2547 attribute10,
2548 attribute11,
2549 attribute12,
2550 attribute13,
2551 attribute14,
2552 attribute15,
2553 assembly_type,
2554 common_bill_sequence_id,
2555 bill_sequence_id,
2556 request_id,
2557 program_application_id,
2558 program_id,
2559 program_update_date,
2560 common_organization_id,
2561 next_explode_date
2562 )
2563 SELECT
2564 assembly_item_id,
2565 organization_id,
2566 alternate_bom_designator,
2567 last_update_date,
2568 last_updated_by,
2569 creation_date,
2570 created_by,
2571 last_update_login,
2572 common_assembly_item_id,
2573 specific_assembly_comment,
2574 attribute_category,
2575 attribute1,
2576 attribute2,
2577 attribute3,
2578 attribute4,
2579 attribute5,
2580 attribute6,
2581 attribute7,
2582 attribute8,
2583 attribute9,
2584 attribute10,
2585 attribute11,
2586 attribute12,
2587 attribute13,
2588 attribute14,
2589 attribute15,
2590 assembly_type,
2591 common_bill_sequence_id,
2592 bill_sequence_id,
2593 request_id,
2594 program_application_id,
2595 program_id,
2596 program_update_date,
2597 common_organization_id,
2598 next_explode_date
2599 FROM bom_bill_of_mtls_interface
2600 WHERE process_flag = 4
2601 AND transaction_type = G_Insert
2602 AND rownum < 500;
2603
2604 EXIT when SQL%NOTFOUND;
2605
2606 stmt_num := 2;
2607 UPDATE bom_bill_of_mtls_interface bi
2608 SET process_flag = 7
2609 WHERE process_flag = 4
2610 AND transaction_type = G_Insert
2611 AND exists (SELECT null
2612 FROM bom_bill_of_materials bom
2613 WHERE bom.bill_sequence_id = bi.bill_sequence_id);
2614 stmt_num := 3;
2615 COMMIT;
2616
2617 END LOOP;
2618 /*
2619 ** Update Bills
2620 */
2621 stmt_num := 4;
2622 continue_loop := TRUE;
2623 WHILE continue_loop LOOP
2624 commit_cnt := 0;
2625 FOR c1rec IN c1 LOOP
2626 commit_cnt := commit_cnt + 1;
2627 UPDATE bom_bill_of_materials
2628 SET last_update_date = c1rec.LUD,
2629 last_updated_by = c1rec.LUB,
2630 last_update_login = c1rec.LUL,
2631 common_assembly_item_id = c1rec.CAII,
2632 specific_assembly_comment = c1rec.SAC,
2633 attribute_category = c1rec.AC,
2634 attribute1 = c1rec.A1,
2638 attribute5 = c1rec.A5,
2635 attribute2 = c1rec.A2,
2636 attribute3 = c1rec.A3,
2637 attribute4 = c1rec.A4,
2639 attribute6 = c1rec.A6,
2640 attribute7 = c1rec.A7,
2641 attribute8 = c1rec.A8,
2642 attribute9 = c1rec.A9,
2643 attribute10 = c1rec.A10,
2644 attribute11 = c1rec.A11,
2645 attribute12 = c1rec.A12,
2646 attribute13 = c1rec.A13,
2647 attribute14 = c1rec.A14,
2648 attribute15 = c1rec.A15,
2649 request_id = c1rec.RI,
2650 program_application_id = c1rec.PAI,
2651 program_id = c1rec.PI,
2652 program_update_date = c1rec.PUD,
2653 common_bill_sequence_id = c1rec.CBSI,
2654 common_organization_id = c1rec.COI
2655 WHERE bill_sequence_id = c1rec.BSI;
2656
2657 stmt_num := 5;
2658 UPDATE bom_bill_of_mtls_interface
2659 SET process_flag = 7
2660 WHERE transaction_id = c1rec.TI;
2661 END LOOP;
2662
2663 stmt_num := 6;
2664 COMMIT;
2665 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2666 continue_loop := FALSE;
2667 END IF;
2668
2669 END LOOP;
2670 /*
2671 ** Delete Bills
2672 */
2673 stmt_num := 7;
2674 continue_loop := TRUE;
2675 WHILE continue_loop LOOP
2676 commit_cnt := 0;
2677 FOR c2rec IN c2 LOOP
2678 commit_cnt := commit_cnt + 1;
2679 /*
2680 ** Get the Bill Delete Group name
2681 */
2682 IF (X_bill_group_name is null) THEN
2683 stmt_num := 8;
2684 DECLARE
2685 CURSOR GetBillGroup IS
2686 SELECT delete_group_name, description
2687 FROM bom_interface_delete_groups
2688 WHERE UPPER(entity_name) = G_DeleteEntity;
2689 BEGIN
2690 FOR X_billgroup IN GetBillGroup LOOP
2691 X_bill_group_name := X_billgroup.delete_group_name;
2692 X_bill_group_description := X_billgroup.description;
2693 END LOOP;
2694
2695 IF (X_bill_group_name is null) THEN
2696 X_error_message := FND_MESSAGE.Get_String('BOM',
2697 'BOM_BILL_DELETE_GROUP_MISSING');
2698 err_text := 'Bom_Bill_Api:'||to_char(stmt_num)||'-'||
2699 X_error_message;
2700 RETURN(-9999);
2701 END IF;
2702 END;
2703 END IF;
2704
2705 stmt_num := 9;
2706 BEGIN
2707 SELECT delete_group_sequence_id, delete_type
2708 INTO X_delete_group_seq_id, X_delete_type
2709 FROM bom_delete_groups
2710 WHERE delete_group_name = X_bill_group_name
2711 AND organization_id = c2rec.OI;
2712
2713 /* if delete group if of type routings. make it
2714 * of type bill, routings
2715 */
2716 if X_delete_type = 3 then
2717 update bom_delete_groups
2718 set delete_type = 6
2719 WHERE delete_group_name = X_bill_group_name
2720 AND organization_id = c2rec.OI;
2721
2722 COMMIT;
2723 X_delete_type := 6;
2724 end if;
2725
2726 IF (X_delete_type not in (2,6)) THEN
2727 X_error_message := FND_MESSAGE.Get_String('BOM',
2728 'BOM_DELETE_GROUP_INVALID');
2729 err_text := 'Bom_Bill_Api('||to_char(stmt_num)||') - '||
2730 X_error_message;
2731 RETURN(-9999);
2732 END IF;
2733 EXCEPTION
2734 WHEN no_data_found THEN
2735 null;
2736 END;
2737
2738 stmt_num := 10;
2739 ret_code := Modal_Delete.Delete_Manager_Oi(
2740 new_group_seq_id => X_delete_group_seq_id,
2741 name => X_bill_group_name,
2742 group_desc => X_bill_group_description,
2743 org_id => c2rec.OI,
2744 bom_or_eng => c2rec.AST,
2745 del_type => 2,
2746 ent_bill_seq_id => c2rec.BSI,
2747 ent_rtg_seq_id => null,
2748 ent_inv_item_id => c2rec.AII,
2749 ent_alt_designator => c2rec.ABD,
2750 ent_comp_seq_id => null,
2751 ent_op_seq_id => null,
2752 user_id => user_id,
2753 err_text => err_text);
2754
2755 IF (ret_code <> 0) THEN
2756 RETURN(ret_code);
2757 END IF;
2758
2759 stmt_num := 11;
2760 UPDATE bom_bill_of_mtls_interface
2761 SET process_flag = 7
2762 WHERE transaction_id = c2rec.TI;
2763
2764 END LOOP;
2765
2766 stmt_num := 12;
2767 COMMIT;
2768 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2769 continue_loop := FALSE;
2770 END IF;
2771
2772 END LOOP;
2773
2774
2775 RETURN(0);
2776
2777 EXCEPTION
2778 WHEN NO_DATA_FOUND THEN
2782 err_text := 'Bom_Bill_Api(Transact-'||stmt_num||') '||substrb(SQLERRM,1,500);
2779 RETURN(0);
2780 WHEN OTHERS THEN
2781 ROLLBACK;
2783 return(SQLCODE);
2784
2785 END Transact_Bill;
2786
2790 Import_Bill
2787 /* -------------------------------- Import_Bill ---------------------------- */
2788 /*
2789 NAME
2791 DESCRIPTION
2792 Assign, Validate, and Transact the Bill of Material record in the
2793 interface table, BOM_BILL_OF_MTLS_INTERFACE.
2794 REQUIRES
2795 err_text out buffer to return error message
2796 MODIFIES
2797 RETURNS
2798 0 if successful
2799 SQLCODE if unsuccessful
2800 NOTES
2801 -----------------------------------------------------------------------------*/
2802 FUNCTION Import_Bill (
2803 org_id NUMBER,
2804 all_org NUMBER := 1,
2805 user_id NUMBER := -1,
2806 login_id NUMBER := -1,
2807 prog_appid NUMBER := -1,
2808 prog_id NUMBER := -1,
2809 req_id NUMBER := -1,
2810 del_rec_flag NUMBER := 1,
2811 err_text IN OUT VARCHAR2
2812 )
2813 return INTEGER
2814 IS
2815 err_msg VARCHAR2(2000);
2816 ret_code NUMBER := 1;
2817 stmt_num NUMBER := 0;
2818 BEGIN
2819 stmt_num := 1;
2820 ret_code := Assign_Bill (
2821 org_id => org_id,
2822 all_org => all_org,
2823 user_id => user_id,
2824 login_id => login_id,
2825 prog_appid => prog_appid,
2826 prog_id => prog_id,
2827 req_id => req_id,
2828 err_text => err_msg);
2829 IF (ret_code <> 0) THEN
2830 err_text := 'Assign_Bill '||substrb(err_msg, 1,1500);
2831 ROLLBACK;
2832 RETURN(ret_code);
2833 END IF;
2834 COMMIT;
2835
2836 stmt_num := 2;
2837 ret_code := Validate_Bill (
2838 org_id => org_id,
2839 all_org => all_org,
2840 user_id => user_id,
2841 login_id => login_id,
2842 prog_appid => prog_appid,
2843 prog_id => prog_id,
2844 req_id => req_id,
2845 err_text => err_msg);
2846 IF (ret_code <> 0) THEN
2850 END IF;
2847 err_text := 'Validate_Bill '||substrb(err_msg, 1,1500);
2848 ROLLBACK;
2849 RETURN(ret_code);
2851 COMMIT;
2852
2853 stmt_num := 3;
2854 ret_code := Transact_Bill (
2855 user_id => user_id,
2856 login_id => login_id,
2857 prog_appid => prog_appid,
2858 prog_id => prog_id,
2859 req_id => req_id,
2860 err_text => err_msg);
2861
2862 IF (ret_code <> 0) THEN
2863 err_text := 'Transact_Bill '||substrb(err_msg, 1,1500);
2864 ROLLBACK;
2865 RETURN(ret_code);
2866 END IF;
2867 COMMIT;
2868
2869 stmt_num := 4;
2870 IF (del_rec_flag = 1) THEN
2871 LOOP
2872 DELETE from bom_bill_of_mtls_interface
2873 WHERE process_flag = 7
2874 AND rownum < G_rows_to_commit;
2875
2876 EXIT when SQL%NOTFOUND;
2877 COMMIT;
2878 END LOOP;
2879 END IF;
2880
2881 RETURN(0);
2882
2883 EXCEPTION
2884 WHEN others THEN
2885 err_text := 'Bom_Bill_Api(Import-'||stmt_num||') '||substrb(SQLERRM,1,1000);
2886 RETURN(ret_code);
2887 END Import_Bill;
2888
2889
2890 END Bom_Bill_Api;