[Home] [Help]
PACKAGE BODY: APPS.BOM_COMPONENT_API
Source
1 PACKAGE BODY Bom_Component_Api AS
2 /* $Header: BOMOICMB.pls 115.31 2002/12/05 19:01:19 sanmani ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1997 Oracle Corporation Redwood Shores, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMOICMB.pls |
9 | DESCRIPTION : This package contains functions used to assign, validate |
10 | and transact Compoennt data in the |
11 | BOM_INVENTORY_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 | 03/17/97 Julie Maeyama Created this new package |
23 +==========================================================================*/
24
25 /* --------------------------- Assign_Component -----------------------------*/
26 /*
27 NAME
28 Assign_Component
29 DESCRIPTION
30 Assign defaults and ID's to Component record in the interface table
31 REQUIRES
32 err_text out buffer to return error message
33 MODIFIES
34 BOM_INVENTORY_COMPS_INTERFACE
35 MTL_INTERFACE_ERRORS
36 RETURNS
37 0 if successful
38 SQLCODE if unsuccessful
39 NOTES
40 -----------------------------------------------------------------------------*/
41 FUNCTION Assign_Component (
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 NOCOPY VARCHAR2
50 )
51 return INTEGER
52 IS
53 stmt_num NUMBER := 0;
54 ret_code NUMBER;
55 commit_cnt NUMBER;
56 curr_org_code VARCHAR2(3);
57 default_wip_value VARCHAR2(1);
58 continue_loop BOOLEAN := TRUE;
59 X_dummy NUMBER;
60 x_bom_item_type NUMBER;
61 x_current_date DATE;
62 x_rollup_flag VARCHAR2(1);
63 x_atp_flag VARCHAR2(1);
64 x_atp_comp_flag VARCHAR2(1);
65 x_check_atp_default NUMBER;
66 x_pick_components VARCHAR2(1);
67 /*
68 ** Select all INSERTS
69 */
70 CURSOR c1 IS
71 SELECT organization_code OC, organization_id OI,
72 assembly_item_id AII, assembly_item_number AIN,
73 alternate_bom_designator ABD, bill_sequence_id BSI,
74 component_sequence_id CSI, transaction_id TI,
75 component_item_id CII, component_item_number CIN,
76 location_name LN, supply_locator_id SLI,
77 operation_seq_num OSN,
78 to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED,
79 bom_item_type BIT, transaction_type A, WIP_SUPPLY_TYPE WST,
80 supply_subinventory SS
81 FROM bom_inventory_comps_interface
82 WHERE process_flag = 1
83 AND transaction_type = G_Insert
84 AND (UPPER(interface_entity_type) = 'BILL'
85 OR interface_entity_type is null)
86 AND (all_org = 1
87 OR
88 (all_org = 2 and organization_id = org_id))
89 AND rownum < G_rows_to_commit;
90
91 /*
92 ** Select UPDATES and DELETES
93 */
94 CURSOR c2 IS
95 SELECT organization_code OC, organization_id OI,
96 assembly_item_id AII, assembly_item_number AIN,
97 alternate_bom_designator ABD, bill_sequence_id BSI,
98 component_sequence_id CSI, transaction_id TI,
99 component_item_id CII, component_item_number CIN,
100 location_name LN, supply_locator_id SLI,
101 operation_seq_num OSN, assembly_type AST,
102 to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED,
103 bom_item_type BIT, transaction_type A
104 FROM bom_inventory_comps_interface
105 WHERE process_flag = 1
106 AND transaction_type in (G_UPDATE, G_DELETE)
107 AND (UPPER(interface_entity_type) = 'BILL'
108 OR interface_entity_type is null)
109 AND (all_org = 1
110 OR
111 (all_org = 2 and organization_id = org_id))
112 AND rownum < G_rows_to_commit;
113
114 BEGIN
115 /** G_INSERT is 'CREATE'. Update 'INSERT' to 'CREATE' **/
116 stmt_num := 0.5 ;
117 LOOP
118 UPDATE bom_inventory_comps_interface
119 SET transaction_type = G_Insert
120 WHERE process_flag = 1
121 AND upper(transaction_type) = 'INSERT'
122 AND rownum < G_rows_to_commit;
123 EXIT when SQL%NOTFOUND;
124 COMMIT;
125 END LOOP;
126
127 /*
128 ** ALL RECORDS - Assign Org Id
129 */
130 stmt_num := 1;
131 LOOP
132 UPDATE bom_inventory_comps_interface ori
133 SET organization_id = (SELECT organization_id
134 FROM mtl_parameters a
135 WHERE a.organization_code = ori.organization_code)
136 WHERE process_flag = 1
137 AND upper(transaction_type) in (G_Insert, G_Delete, G_Update)
138 AND (UPPER(ori.interface_entity_type) = 'BILL'
139 OR ori.interface_entity_type is null)
140 AND organization_id is null
141 AND organization_code is not null
142 AND exists (SELECT organization_code
143 FROM mtl_parameters b
144 WHERE b.organization_code = ori.organization_code)
145 AND rownum < G_rows_to_commit;
146 EXIT when SQL%NOTFOUND;
147 COMMIT;
148 END LOOP;
149
150 /*
151 ** FOR INSERTS - Assign transaction ids
152 */
153 stmt_num := 1;
154 LOOP
155 UPDATE bom_inventory_comps_interface
156 SET transaction_id = mtl_system_items_interface_s.nextval,
157 component_sequence_id = bom_inventory_components_s.nextval,
158 transaction_type = upper(transaction_type)
159 WHERE transaction_id is null
160 AND process_flag = 1
161 AND upper(transaction_type) = G_Insert
162 AND (UPPER(interface_entity_type) = 'BILL'
163 OR interface_entity_type is null)
164 AND rownum < G_rows_to_commit;
165 EXIT when SQL%NOTFOUND;
166
167 stmt_num := 2;
168 COMMIT;
169 END LOOP;
170
171 /*
172 ** FOR UPDATES and DELETES - Assign transaction ids
173 */
174 stmt_num := 1;
175 LOOP
176 UPDATE bom_inventory_comps_interface
177 SET transaction_id = mtl_system_items_interface_s.nextval,
178 transaction_type = upper(transaction_type)
179 WHERE transaction_id is null
180 AND process_flag = 1
181 AND upper(transaction_type) in (G_UPDATE, G_DELETE)
182 AND (UPPER(interface_entity_type) = 'BILL'
183 OR interface_entity_type is null)
184 AND rownum < G_rows_to_commit;
185 EXIT when SQL%NOTFOUND;
186
187 stmt_num := 2;
188 COMMIT;
189 END LOOP;
190
191 /*
192 ** FOR INSERTS - Assign values
193 */
194 WHILE continue_loop LOOP
195 commit_cnt := 0;
196 FOR c1rec IN c1 LOOP
197 commit_cnt := commit_cnt + 1;
198 x_bom_item_type := null;
199 x_rollup_flag := null;
200 x_atp_flag := null;
201 x_atp_comp_flag := null;
202 x_pick_components := null;
203 stmt_num := 3;
204 /*
205 ** Check if Org ID null
206 */
207 IF (c1rec.OI is null
208 AND (c1rec.BSI is null OR c1rec.CII is null)) THEN
209 ret_code := INVPUOPI.mtl_log_interface_err(
210 org_id => NULL,
211 user_id => user_id,
212 login_id => login_id,
213 prog_appid => prog_appid,
214 prog_id => prog_id,
215 req_id => req_id,
216 trans_id => c1rec.TI,
217 error_text => err_text,
218 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
219 msg_name => 'BOM_ORG_ID_MISSING',
220 err_text => err_text);
221 UPDATE bom_inventory_comps_interface
222 SET process_flag = 3
223 WHERE transaction_id = c1rec.TI;
224
225 GOTO continue_loop;
226 END if;
227 stmt_num := 4;
228
229 /*
230 ** Get Assembly Id
231 */
232 stmt_num := 5;
233 IF (c1rec.AII is null AND c1rec.BSI is null) THEN
234 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
235 org_id => c1rec.OI,
236 flex_code => 'MSTK',
237 flex_name => c1rec.AIN,
238 flex_id => c1rec.AII,
239 set_id => -1,
240 err_text => err_text);
241 IF (ret_code <> 0) THEN
242 ret_code := INVPUOPI.mtl_log_interface_err(
243 org_id => NULL,
244 user_id => user_id,
245 login_id => login_id,
246 prog_appid => prog_appid,
247 prog_id => prog_id,
248 req_id => req_id,
252 msg_name => 'BOM_ASSY_ITEM_MISSING',
249 trans_id => c1rec.TI,
250 error_text => err_text,
251 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
253 err_text => err_text);
254 UPDATE bom_inventory_comps_interface
255 SET process_flag = 3
256 WHERE transaction_id = c1rec.TI;
257
258 IF (ret_code <> 0) THEN
259 RETURN(ret_code);
260 END IF;
261 GOTO continue_loop;
262 END IF;
263 END IF;
264
265 /*
266 ** Get Bill Sequence Id
267 */
268 IF (c1rec.BSI is null) THEN
269 stmt_num := 7;
270 BEGIN
271 SELECT bom.bill_sequence_id, msi.bom_item_type,
272 msi.atp_components_flag
273 INTO c1rec.BSI, x_bom_item_type, x_atp_comp_flag
274 FROM bom_bill_of_materials bom,
275 mtl_system_items msi
276 WHERE bom.organization_id = c1rec.OI
277 AND bom.assembly_item_id = c1rec.AII
278 AND nvl(bom.alternate_bom_designator, 'NONE') =
279 nvl(c1rec.ABD, 'NONE')
280 AND msi.organization_id = bom.organization_id
281 AND msi.inventory_item_id = bom.assembly_item_id;
282 EXCEPTION
283 WHEN no_data_found THEN
284 ret_code := INVPUOPI.mtl_log_interface_err(
285 org_id => c1rec.OI,
286 user_id => user_id,
287 login_id => login_id,
288 prog_appid => prog_appid,
289 prog_id => prog_id,
290 req_id => req_id,
291 trans_id => c1rec.TI,
292 error_text => err_text,
293 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
294 msg_name => 'BOM_BILL_SEQ_MISSING',
295 err_text => err_text);
296 UPDATE bom_inventory_comps_interface
297 SET process_flag = 3
298 WHERE transaction_id = c1rec.TI;
299
300 IF (ret_code <> 0) THEN
301 RETURN(ret_code);
302 END IF;
303 GOTO continue_loop;
304 END;
305 /*
306 ** Get Bill Info
307 */
308 ELSE /* Needed for verify */
309 stmt_num := 8;
310 BEGIN
311 SELECT bom.assembly_item_id, bom.organization_id,
312 bom.alternate_bom_designator, msi.bom_item_type,
313 msi.atp_components_flag
314 INTO c1rec.AII, c1rec.OI, c1rec.ABD, x_bom_item_type,
315 x_atp_comp_flag
316 FROM bom_bill_of_materials bom,
317 mtl_system_items msi
318 WHERE bom.bill_sequence_id = c1rec.BSI
319 AND msi.organization_id = bom.organization_id
320 AND msi.inventory_item_id = bom.assembly_item_id;
321 EXCEPTION
322 WHEN no_data_found THEN
323 ret_code := INVPUOPI.mtl_log_interface_err(
324 org_id => NULL,
325 user_id => user_id,
326 login_id => login_id,
327 prog_appid => prog_appid,
328 prog_id => prog_id,
329 req_id => req_id,
330 trans_id => c1rec.TI,
331 error_text => err_text,
332 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
333 msg_name => 'BOM_BILL_SEQ_MISSING',
334 err_text => err_text);
335 UPDATE bom_inventory_comps_interface
336 SET process_flag = 3
337 WHERE transaction_id = c1rec.TI;
338
339 IF (ret_code <> 0) THEN
340 RETURN(ret_code);
341 END IF;
342 GOTO continue_loop;
343 END;
344 END IF;
345 /*
346 ** Get Component Id
347 */
348 stmt_num := 9;
349 IF (c1rec.CII is null) THEN
350 ret_code := INVPUOPI.mtl_pr_trans_prod_item(
351 c1rec.CIN,
352 c1rec.OI,
353 c1rec.CII,
354 err_text);
355 IF (ret_code <> 0) THEN
356 ret_code := INVPUOPI.mtl_log_interface_err(
357 org_id => NULL,
358 user_id => user_id,
359 login_id => login_id,
360 prog_appid => prog_appid,
364 error_text => err_text,
361 prog_id => prog_id,
362 req_id => req_id,
363 trans_id => c1rec.TI,
365 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
366 msg_name => 'BOM_COMP_ID_MISSING',
367 err_text => err_text);
368 UPDATE bom_inventory_comps_interface
369 SET process_flag = 3
370 WHERE transaction_id = c1rec.TI;
371
372 IF (ret_code <> 0) THEN
373 RETURN(ret_code);
374 END IF;
375 GOTO continue_loop;
376 END IF;
377 END IF;
378
379 /*
380 ** Set WIP_SUPPLY_TYPE to 1 if Profile BOM:DEFAULT_WIP_VALUES set to YES.
381 */
382 stmt_num := 9.5;
383 IF (c1rec.WST is null) THEN
384 BOMPRFIL.bom_pr_get_profile(
385 appl_short_name => 'BOM',
386 profile_name => 'BOM:DEFAULT_WIP_VALUES',
387 user_id => user_id,
388 resp_appl_id => prog_appid,
389 resp_id => 702,
390 profile_value => default_wip_value,
391 return_code => ret_code,
392 return_message => err_text);
393 IF (default_wip_value = '1')
394 THEN
395 BEGIN
396 -- If the profile value is Yes i.e 1, then
397 -- get the wip values from item master
398 SELECT wip_supply_type, wip_supply_subinventory,
399 wip_supply_locator_id
400 INTO c1rec.wst, c1rec.SS, c1rec.SLI
401 FROM mtl_system_items
402 WHERE organization_id = c1rec.OI
403 AND inventory_item_id = c1rec.CII;
404
405 EXCEPTION
406 WHEN NO_DATA_FOUND THEN
407 ret_code := INVPUOPI.mtl_log_interface_err(
408 org_id => NULL,
409 user_id => user_id,
410 login_id => login_id,
411 prog_appid => prog_appid,
412 prog_id => prog_id,
413 req_id => req_id,
414 trans_id => c1rec.TI,
415 error_text => err_text,
416 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
417 msg_name => 'BOM_COMP_ID_MISSING',
418 err_text => err_text);
419
420 UPDATE bom_inventory_comps_interface
421 SET process_flag = 3
422 WHERE transaction_id = c1rec.TI;
423 IF (ret_code <> 0) THEN
424 RETURN(ret_code);
425 END IF;
426 GOTO continue_loop;
427 END;
428 END IF;
429
430 IF (ret_code <> 0) THEN
431 err_text := 'Bom_Component_Api(Assign-'||stmt_num||') '||
432 err_text;
433 RETURN(ret_code);
434 END IF;
435 END IF;
436
437
438 /*
439 ** Check if Component exists in Item Master
440 */
441 BEGIN
442 stmt_num := 10;
443 BEGIN
444 SELECT bom_item_type, default_include_in_rollup_flag,
445 atp_flag, pick_components_flag
446 INTO c1rec.BIT, x_rollup_flag, x_atp_flag, x_pick_components
447 FROM mtl_system_items
448 WHERE organization_id = c1rec.OI
449 AND inventory_item_id = c1rec.CII;
450 EXCEPTION
451 WHEN no_data_found THEN
452 ret_code := INVPUOPI.mtl_log_interface_err(
453 org_id => NULL,
454 user_id => user_id,
455 login_id => login_id,
456 prog_appid => prog_appid,
457 prog_id => prog_id,
458 req_id => req_id,
459 trans_id => c1rec.TI,
460 error_text => err_text,
461 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
462 msg_name => 'BOM_COMP_ID_MISSING',
463 err_text => err_text);
464 UPDATE bom_inventory_comps_interface
465 SET process_flag = 3
466 WHERE transaction_id = c1rec.TI;
467
468 IF (ret_code <> 0) THEN
469 RETURN(ret_code);
470 END IF;
471 GOTO continue_loop;
472 WHEN others THEN
473 err_text := 'Bom_Component_Api(Assign-'||stmt_num||') '||
474 substrb(SQLERRM, 1, 60);
475 RETURN(SQLCODE);
476 END;
477 END;
478 /*
479 ** Check if bill of Product Family
480 */
481 IF (x_bom_item_type = G_ProductFamily) THEN
482 GOTO update_member;
483 END IF;
484 /*
485 ** Check if Effective Date null
486 */
487 IF (c1rec.ED is null) THEN
488 ret_code := INVPUOPI.mtl_log_interface_err(
489 org_id => NULL,
490 user_id => user_id,
491 login_id => login_id,
492 prog_appid => prog_appid,
496 error_text => err_text,
493 prog_id => prog_id,
494 req_id => req_id,
495 trans_id => c1rec.TI,
497 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
498 msg_name => 'BOM_EFF_DATE_MISSING',
499 err_text => err_text);
500 UPDATE bom_inventory_comps_interface
501 SET process_flag = 3
502 WHERE transaction_id = c1rec.TI;
503
504 GOTO continue_loop;
505 END IF;
506 /*
507 ** Get Supply Locator Id
508 */
509 stmt_num := 10.1;
510 IF (c1rec.SLI is null AND c1rec.LN is not null) THEN
511 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
512 org_id => c1rec.OI,
513 flex_code => 'MTLL',
514 flex_name => c1rec.LN,
515 flex_id => c1rec.SLI,
516 set_id => -1,
517 err_text => err_text);
518 IF (ret_code <> 0) THEN
519 ret_code := INVPUOPI.mtl_log_interface_err(
520 org_id => NULL,
521 user_id => user_id,
522 login_id => login_id,
523 prog_appid => prog_appid,
524 prog_id => prog_id,
525 req_id => req_id,
526 trans_id => c1rec.TI,
527 error_text => err_text,
528 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
529 msg_name => 'BOM_LOCATION_NAME_INVALID',
530 err_text => err_text);
531 UPDATE bom_inventory_comps_interface
532 SET process_flag = 3
533 WHERE transaction_id = c1rec.TI;
534
535 IF (ret_code <> 0) THEN
536 RETURN(ret_code);
537 END IF;
538 GOTO continue_loop;
539 END IF;
540 END IF;
541 /*
542 ** Update Component with defaults and derived values
543 */
544 stmt_num := 11;
545
546 /* Bug 2243418 */
547 /*
548 IF (x_atp_comp_flag = 'Y' AND x_atp_flag = 'Y') THEN
549 x_check_atp_default := 1;
550 ELSE
551 x_check_atp_default := 2;
552 END IF;
553 */
554 IF (x_atp_flag = 'N') THEN
555 x_check_atp_default := 2;
556 ELSE
557 x_check_atp_default := 1;
558 END IF;
559
560 /* end Bug 2243418 */
561 UPDATE bom_inventory_comps_interface
562 SET component_item_id = nvl(component_item_id, c1rec.CII),
563 item_num = nvl(item_num, 1),
564 component_quantity = nvl(component_quantity, 1),
565 component_yield_factor = nvl(component_yield_factor, 1),
566 implementation_date = effectivity_date,
567 planning_factor = nvl(planning_factor, 100),
568 quantity_related = nvl(quantity_related, 2),
569 so_basis = nvl(so_basis, 2),
570 optional = nvl(optional, 2),
571 mutually_exclusive_options = nvl(mutually_exclusive_options,2),
572 include_in_cost_rollup = nvl(include_in_cost_rollup, decode(nvl(x_rollup_flag, 'Y'),'Y', 1, 2)),
573 check_atp = nvl(check_atp, x_check_atp_default),
574 required_to_ship = nvl(required_to_ship, 2),
575 required_for_revenue = nvl(required_for_Revenue, 2),
576 include_on_ship_docs = nvl(include_on_ship_docs, 2),
577 include_on_bill_docs = nvl(include_on_bill_docs, 2),
578 low_quantity = nvl(low_quantity, nvl(high_quantity,null)),
579 high_quantity = nvl(high_quantity,nvl(low_quantity,null)),
580 bill_sequence_id = nvl(bill_Sequence_id, c1rec.BSI),
581 pick_components = decode(x_pick_components, 'Y', 1, 2),
582 wip_supply_type = NVL(wip_supply_type, c1rec.wst),
583 supply_subinventory = NVL(supply_subinventory, c1rec.ss),
584 supply_locator_id = nvl(supply_locator_id, c1rec.SLI),
585 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
586 alternate_bom_designator = nvl(alternate_bom_designator,
587 c1rec.ABD),
588 organization_id = nvl(organization_id, c1rec.OI),
589 creation_date = nvl(creation_date, sysdate),
590 created_by = nvl(created_by, user_id),
591 last_update_date = nvl(last_update_date, sysdate),
592 last_updated_by = nvl(last_updated_by, user_id),
593 last_update_login = nvl(last_update_login, user_id),
594 request_id = nvl(request_id, req_id),
595 program_application_id =nvl(program_application_id,prog_appid),
596 program_id = nvl(program_id, prog_id),
597 program_update_date = nvl(program_update_date, sysdate),
598 process_flag = 2,
599 bom_item_type = c1rec.BIT
600 WHERE transaction_id = c1rec.TI;
601
602 GOTO continue_loop;
603 <<update_member>>
604 /*
605 ** Update Product Family Member with defaults and derived values
606 */
607 x_current_date := trunc(sysdate);
611 x_check_atp_default := 2;
608 IF (x_atp_comp_flag = 'Y' AND x_atp_flag = 'Y') THEN
609 x_check_atp_default := 1;
610 ELSE
612 END IF;
613 stmt_num := 11.1;
614
615 UPDATE bom_inventory_comps_interface
616 SET component_item_id = nvl(component_item_id, c1rec.CII),
617 bill_sequence_id = nvl(bill_Sequence_id, c1rec.BSI),
618 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
619 alternate_bom_designator = nvl(alternate_bom_designator,
620 c1rec.ABD),
621 organization_id = nvl(organization_id, c1rec.OI),
622 operation_seq_num = 1,
623 item_num = 1,
624 component_quantity = 1,
625 component_yield_factor = 1,
626 planning_factor = nvl(planning_factor,100),
627 quantity_related = 2,
628 so_basis = 2,
629 optional = 2,
630 mutually_exclusive_options = 2,
631 required_to_ship = 2,
632 required_for_revenue = 2,
633 include_on_ship_docs = 2,
634 effectivity_date = nvl(trunc(effectivity_date),
635 x_current_date),
636 implementation_date = nvl(trunc(effectivity_date),
637 x_current_date),
638 include_in_cost_rollup = decode(nvl(x_rollup_flag,
639 'Y'),'Y', 1, 2),
640 check_atp = x_check_atp_default,
641 pick_components = decode(x_pick_components,
642 'Y', 1, 2),
643 bom_item_type = c1rec.BIT,
644 supply_locator_id = null,
645 low_quantity = null,
646 high_quantity = null,
647 change_notice = null,
648 shipping_allowed = null,
649 acd_type = null,
650 old_component_sequence_id = null,
651 wip_supply_type = null,
652 supply_subinventory = null,
653 operation_lead_time_percent = null,
654 revised_item_sequence_id = null,
655 cost_factor = null,
656 substitute_comp_id = null,
657 substitute_comp_number = null,
658 reference_designator = null,
659 creation_date = nvl(creation_date, sysdate),
660 created_by = nvl(created_by, user_id),
661 last_update_date = nvl(last_update_date, sysdate),
662 last_updated_by = nvl(last_updated_by, user_id),
663 last_update_login = nvl(last_update_login, user_id),
664 request_id = nvl(request_id, req_id),
665 program_application_id =nvl(program_application_id,prog_appid),
666 program_id = nvl(program_id, prog_id),
667 program_update_date = nvl(program_update_date, sysdate),
668 process_flag = 2
669 WHERE transaction_id = c1rec.TI;
670 <<continue_loop>>
671 NULL;
672 END LOOP;
673
674 stmt_num := 13;
675 COMMIT;
676 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
677 continue_loop := FALSE;
678 END IF;
679
680 END LOOP;
681
682 /*
683 ** FOR UPDATES and DELETES - Assign values
684 */
685
686 continue_loop := TRUE;
687 WHILE continue_loop LOOP
688 commit_cnt := 0;
689 FOR c2rec IN c2 LOOP
690 commit_cnt := commit_cnt + 1;
691 stmt_num := 3;
692 x_bom_item_type := null;
693 /*
694 ** Assign primary key info
695 */
696 IF (c2rec.CSI is null) THEN
697 IF (c2rec.BSI is null) THEN
698 -- Check if Org Id is null
699 IF (c2rec.OI is null) THEN
700 ret_code := INVPUOPI.mtl_log_interface_err(
701 org_id => NULL,
702 user_id => user_id,
703 login_id => login_id,
704 prog_appid => prog_appid,
705 prog_id => prog_id,
706 req_id => req_id,
707 trans_id => c2rec.TI,
708 error_text => err_text,
709 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
710 msg_name => 'BOM_ORG_ID_MISSING',
711 err_text => err_text);
712 UPDATE bom_inventory_comps_interface
713 SET process_flag = 3
714 WHERE transaction_id = c2rec.TI;
715
716 IF (ret_code <> 0) THEN
717 RETURN(ret_code);
718 END IF;
719 GOTO continue_loop1;
720 END IF;
721 /*
722 ** Get Assembly Id
723 */
724 -- Get Assembly Item Id
725 stmt_num := 3;
726 IF (c2rec.AII is null) THEN
727 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
728 org_id => c2rec.OI,
729 flex_code => 'MSTK',
730 flex_name => c2rec.AIN,
731 flex_id => c2rec.AII,
732 set_id => -1,
733 err_text => err_text);
734 IF (ret_code <> 0) THEN
738 login_id => login_id,
735 ret_code := INVPUOPI.mtl_log_interface_err(
736 org_id => c2rec.OI,
737 user_id => user_id,
739 prog_appid => prog_appid,
740 prog_id => prog_id,
741 req_id => req_id,
742 trans_id => c2rec.TI,
743 error_text => err_text,
744 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
745 msg_name => 'BOM_INV_ITEM_ID_MISSING',
746 err_text => err_text);
747 UPDATE bom_inventory_comps_interface
748 SET process_flag = 3
749 WHERE transaction_id = c2rec.TI;
750
751 IF (ret_code <> 0) THEN
752 RETURN(ret_code);
753 END IF;
754 GOTO continue_loop1;
755 END IF;
756 END IF;
757 /*
758 ** Get Bill Sequence Id
759 */
760 stmt_num := 7;
761 BEGIN
762 SELECT bom.bill_sequence_id, bom.assembly_type,
763 msi.bom_item_type
764 INTO c2rec.BSI, c2rec.AST, x_bom_item_type
765 FROM bom_bill_of_materials bom,
766 mtl_system_items msi
767 WHERE bom.organization_id = c2rec.OI
768 AND bom.assembly_item_id = c2rec.AII
769 AND nvl(bom.alternate_bom_designator, 'NONE') =
770 nvl(c2rec.ABD, 'NONE')
771 AND msi.organization_id = bom.organization_id
772 AND msi.inventory_item_id = bom.assembly_item_id;
773 EXCEPTION
774 WHEN no_data_found THEN
775 ret_code := INVPUOPI.mtl_log_interface_err(
776 org_id => c2rec.OI,
777 user_id => user_id,
778 login_id => login_id,
779 prog_appid => prog_appid,
780 prog_id => prog_id,
781 req_id => req_id,
782 trans_id => c2rec.TI,
783 error_text => err_text,
784 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
785 msg_name => 'BOM_BILL_SEQ_MISSING',
786 err_text => err_text);
787 UPDATE bom_inventory_comps_interface
788 SET process_flag = 3
789 WHERE transaction_id = c2rec.TI;
790
791 IF (ret_code <> 0) THEN
792 RETURN(ret_code);
793 END IF;
794 GOTO continue_loop1;
795 END;
796 /*
797 ** Get Bill Info
798 */
799 ELSE -- Bill Seq Id is given
800 stmt_num := 8;
801 BEGIN
802 SELECT bom.assembly_item_id, bom.organization_id,
803 bom.alternate_bom_designator, bom.assembly_type,
804 msi.bom_item_type
805 INTO c2rec.AII, c2rec.OI, c2rec.ABD, c2rec.AST,
806 x_bom_item_type
807 FROM bom_bill_of_materials bom,
808 mtl_system_items msi
809 WHERE bom.bill_sequence_id = c2rec.BSI
810 AND msi.organization_id = bom.organization_id
811 AND msi.inventory_item_id = bom.assembly_item_id;
812 EXCEPTION
813 WHEN no_data_found THEN
814 ret_code := INVPUOPI.mtl_log_interface_err(
815 org_id => NULL,
816 user_id => user_id,
817 login_id => login_id,
818 prog_appid => prog_appid,
819 prog_id => prog_id,
820 req_id => req_id,
821 trans_id => c2rec.TI,
822 error_text => err_text,
823 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
824 msg_name => 'BOM_BILL_SEQ_MISSING',
825 err_text => err_text);
826 UPDATE bom_inventory_comps_interface
827 SET process_flag = 3
828 WHERE transaction_id = c2rec.TI;
829
830 IF (ret_code <> 0) THEN
831 RETURN(ret_code);
832 END IF;
833 GOTO continue_loop1;
834 END;
835 END IF;
836 /*
837 ** Get Component Id
838 */
839 stmt_num := 9;
840 IF (c2rec.CII is null) THEN
841 ret_code := INVPUOPI.mtl_pr_trans_prod_item(
842 c2rec.CIN,
843 c2rec.OI,
844 c2rec.CII,
845 err_text);
846 IF (ret_code <> 0) THEN
847 ret_code := INVPUOPI.mtl_log_interface_err(
848 org_id => NULL,
849 user_id => user_id,
850 login_id => login_id,
851 prog_appid => prog_appid,
852 prog_id => prog_id,
853 req_id => req_id,
854 trans_id => c2rec.TI,
858 err_text => err_text);
855 error_text => err_text,
856 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
857 msg_name => 'BOM_COMP_ID_MISSING',
859 UPDATE bom_inventory_comps_interface
860 SET process_flag = 3
861 WHERE transaction_id = c2rec.TI;
862
863 IF (ret_code <> 0) THEN
864 RETURN(ret_code);
865 END IF;
866 GOTO continue_loop1;
867 END IF;
868 END IF;
869 /*
870 ** Get Component Seq Id
871 */
872 stmt_num := 10;
873 BEGIN
874 SELECT component_sequence_id
875 INTO c2rec.CSI
876 FROM bom_inventory_components
877 WHERE bill_sequence_id = c2rec.BSI
878 AND component_item_id = c2rec.CII
879 AND operation_seq_num = decode(x_bom_item_type,
880 G_ProductFamily, 1, c2rec.OSN)
881 AND effectivity_date = to_date(c2rec.ED,
882 'YYYY/MM/DD HH24:MI:SS');
883 EXCEPTION
884 WHEN no_data_found THEN
885 ret_code := INVPUOPI.mtl_log_interface_err(
886 org_id => NULL,
887 user_id => user_id,
888 login_id => login_id,
889 prog_appid => prog_appid,
890 prog_id => prog_id,
891 req_id => req_id,
892 trans_id => c2rec.TI,
893 error_text => err_text,
894 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
895 msg_name => 'BOM_COMP_SEQ_MISSING',
896 err_text => err_text);
897 UPDATE bom_inventory_comps_interface
898 SET process_flag = 3
899 WHERE transaction_id = c2rec.TI;
900
901 IF (ret_code <> 0) THEN
902 RETURN(ret_code);
903 END IF;
904 GOTO continue_loop1;
905 END;
906 /*
907 ** Get Bill and Component Info
908 */
909 ELSE -- Component_Sequence_Id is given
910 BEGIN
911 SELECT bbom.assembly_item_id, bbom.organization_id,
912 bbom.bill_sequence_id,
913 bbom.alternate_bom_designator, bbom.assembly_type,
914 bic.component_item_id, msi.bom_item_type
915 INTO c2rec.AII, c2rec.OI, c2rec.BSI, c2rec.ABD, c2rec.AST,
916 c2rec.CII, x_bom_item_type
917 FROM mtl_system_items msi,
918 bom_bill_of_materials bbom,
919 bom_inventory_components bic
920 WHERE bbom.bill_sequence_id = bic.bill_sequence_id
921 AND bic.component_sequence_id = c2rec.CSI
922 AND msi.organization_id = bbom.organization_id
923 AND msi.inventory_item_id = bbom.assembly_item_id;
924 EXCEPTION
925 WHEN no_data_found THEN
926 ret_code := INVPUOPI.mtl_log_interface_err(
927 org_id => NULL,
928 user_id => user_id,
929 login_id => login_id,
930 prog_appid => prog_appid,
931 prog_id => prog_id,
932 req_id => req_id,
933 trans_id => c2rec.TI,
934 error_text => err_text,
935 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
936 msg_name => 'BOM_COMP_SEQ_MISSING',
937 err_text => err_text);
938 UPDATE bom_inventory_comps_interface
939 SET process_flag = 3
940 WHERE transaction_id = c2rec.TI;
941
942 IF (ret_code <> 0) THEN
943 RETURN(ret_code);
944 END IF;
945 GOTO continue_loop1;
946 END;
947 END IF;
948 /*
949 ** FOR UPDATES - Assign Supply Locator
950 */
951 IF (c2rec.A = G_UPDATE) THEN
952 /*
953 ** For Product Family Members
954 */
955 IF (x_bom_item_type = G_ProductFamily) THEN
956 stmt_num := 10.1;
957 UPDATE bom_inventory_comps_interface
958 SET component_sequence_id = c2rec.CSI,
959 component_item_id = c2rec.CII,
960 bill_sequence_id = c2rec.BSI,
961 organization_id = c2rec.OI,
962 assembly_item_id = c2rec.AII,
963 alternate_bom_designator = c2rec.ABD,
964 assembly_type = c2rec.AST,
965 last_update_date = nvl(last_update_date, sysdate),
966 last_updated_by = nvl(last_updated_by, user_id),
967 last_update_login = nvl(last_update_login, user_id),
968 request_id = nvl(request_id, req_id),
969 program_application_id =nvl(program_application_id,
970 prog_appid),
971 program_id = nvl(program_id, prog_id),
972 program_update_date = nvl(program_update_date, sysdate),
976 IF (SQL%NOTFOUND) THEN
973 process_flag = 2
974 WHERE transaction_id = c2rec.TI;
975
977 err_text := 'Bom_Component_Api(' ||stmt_num|| ')'||
978 substrb(SQLERRM,1,60);
979 RETURN(SQLCODE);
980 END IF;
981 GOTO continue_loop1;
982 ELSE
983 /*
984 ** Get Supply Locator Id
985 */
986 stmt_num := 6;
987 IF (c2rec.SLI is null AND c2rec.LN is not null) THEN
988 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
989 org_id => c2rec.OI,
990 flex_code => 'MTLL',
991 flex_name => c2rec.LN,
992 flex_id => c2rec.SLI,
993 set_id => -1,
994 err_text => err_text);
995 IF (ret_code <> 0) THEN
996 ret_code := INVPUOPI.mtl_log_interface_err(
997 org_id => NULL,
998 user_id => user_id,
999 login_id => login_id,
1000 prog_appid => prog_appid,
1001 prog_id => prog_id,
1002 req_id => req_id,
1003 trans_id => c2rec.TI,
1004 error_text => err_text,
1005 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1006 msg_name => 'BOM_LOCATION_NAME_INVALID',
1007 err_text => err_text);
1008 UPDATE bom_inventory_comps_interface
1009 SET process_flag = 3
1010 WHERE transaction_id = c2rec.TI;
1011
1012 IF (ret_code <> 0) THEN
1013 RETURN(ret_code);
1014 END IF;
1015 GOTO continue_loop1;
1016 END IF;
1017 END IF;
1018 END IF;
1019 /*
1020 ** Update "Update" record
1021 */
1022 stmt_num := 11;
1023 UPDATE bom_inventory_comps_interface
1024 SET component_sequence_id = c2rec.CSI,
1025 component_item_id = c2rec.CII,
1026 bill_sequence_id = c2rec.BSI,
1027 organization_id = c2rec.OI,
1028 assembly_item_id = c2rec.AII,
1029 alternate_bom_designator = c2rec.ABD,
1030 assembly_type = c2rec.AST,
1031 supply_locator_id = c2rec.SLI,
1032 implementation_date = nvl(new_effectivity_date, NULL),
1033 last_update_date = nvl(last_update_date, sysdate),
1034 last_updated_by = nvl(last_updated_by, user_id),
1035 last_update_login = nvl(last_update_login, user_id),
1036 request_id = nvl(request_id, req_id),
1037 program_application_id =nvl(program_application_id,
1038 prog_appid),
1039 program_id = nvl(program_id, prog_id),
1040 program_update_date = nvl(program_update_date, sysdate),
1041 process_flag = 2
1042 WHERE transaction_id = c2rec.TI;
1043
1044 IF (SQL%NOTFOUND) THEN
1045 err_text := 'Bom_Component_Api(' ||stmt_num|| ')'||
1046 substrb(SQLERRM,1,60);
1047 RETURN(SQLCODE);
1048 END IF;
1049 ELSIF (c2rec.A = G_DELETE) THEN
1050 stmt_num := 8;
1051 UPDATE bom_inventory_comps_interface
1052 SET component_sequence_id = c2rec.CSI,
1053 component_item_id = c2rec.CII,
1054 bill_sequence_id = c2rec.BSI,
1055 organization_id = c2rec.OI,
1056 assembly_item_id = c2rec.AII,
1057 alternate_bom_designator = c2rec.ABD,
1058 assembly_type = c2rec.AST,
1059 process_flag = 2
1060 WHERE transaction_id = c2rec.TI;
1061
1062 IF (SQL%NOTFOUND) THEN
1063 err_text := 'Bom_Component_Api('||stmt_num||')'||
1064 substrb(SQLERRM, 1, 60);
1065 RETURN(SQLCODE);
1066 END IF;
1067 END IF;
1068
1069 <<continue_loop1>>
1070 NULL;
1071 END LOOP;
1072
1073 stmt_num := 13;
1074 COMMIT;
1075 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
1076 continue_loop := FALSE;
1077 END IF;
1078
1079 END LOOP;
1080
1081 /*
1082 ** INSERTS ONLY - Load rows from component interface into sub comp interface
1083 */
1084 stmt_num := 1;
1085 INSERT into bom_sub_comps_interface (
1086 SUBSTITUTE_COMPONENT_ID,
1087 SUBSTITUTE_COMP_NUMBER,
1088 ORGANIZATION_ID,
1089 LAST_UPDATE_DATE,
1090 LAST_UPDATED_BY,
1091 CREATION_DATE,
1092 CREATED_BY,
1093 LAST_UPDATE_LOGIN,
1094 REQUEST_ID,
1095 PROGRAM_APPLICATION_ID,
1096 PROGRAM_ID,
1097 PROGRAM_UPDATE_DATE,
1098 COMPONENT_SEQUENCE_ID,
1099 PROCESS_FLAG,
1100 TRANSACTION_TYPE,
1101 SUBSTITUTE_ITEM_QUANTITY)
1102 SELECT
1103 SUBSTITUTE_COMP_ID,
1107 NVL(LAST_UPDATED_BY, user_id),
1104 SUBSTITUTE_COMP_NUMBER,
1105 ORGANIZATION_ID,
1106 NVL(LAST_UPDATE_DATE, SYSDATE),
1108 NVL(CREATION_DATE,SYSDATE),
1109 NVL(CREATED_BY, user_id),
1110 NVL(LAST_UPDATE_LOGIN, user_id),
1111 NVL(REQUEST_ID, req_id),
1112 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1113 NVL(PROGRAM_ID, prog_id),
1114 NVL(PROGRAM_UPDATE_DATE, sysdate),
1115 COMPONENT_SEQUENCE_ID,
1116 1,
1117 G_Insert,
1118 COMPONENT_QUANTITY
1119 FROM bom_inventory_comps_interface
1120 WHERE process_flag = 2
1121 AND transaction_type = G_Insert
1122 AND (UPPER(interface_entity_type) = 'BILL'
1123 OR interface_entity_type is null)
1124 AND (substitute_comp_id is not null
1125 OR
1126 substitute_comp_number is not null);
1127
1128 COMMIT;
1129
1130 /*
1131 ** INSERTS ONLY - Load rows from component interface into ref desgs interface
1132 */
1133 stmt_num := 1;
1134 INSERT INTO bom_ref_desgs_interface (
1135 COMPONENT_REFERENCE_DESIGNATOR,
1136 LAST_UPDATE_DATE,
1137 LAST_UPDATED_BY,
1138 CREATION_DATE,
1139 CREATED_BY,
1140 LAST_UPDATE_LOGIN,
1141 REQUEST_ID,
1142 PROGRAM_APPLICATION_ID,
1143 PROGRAM_ID,
1144 PROGRAM_UPDATE_DATE,
1145 COMPONENT_SEQUENCE_ID,
1146 TRANSACTION_TYPE,
1147 PROCESS_FLAG)
1148 SELECT
1149 REFERENCE_DESIGNATOR,
1150 NVL(LAST_UPDATE_DATE, SYSDATE),
1151 NVL(LAST_UPDATED_BY, user_id),
1152 NVL(CREATION_DATE,SYSDATE),
1153 NVL(CREATED_BY, user_id),
1154 NVL(LAST_UPDATE_LOGIN, user_id),
1155 NVL(REQUEST_ID, req_id),
1156 NVL(PROGRAM_APPLICATION_ID, prog_appid),
1157 NVL(PROGRAM_ID, prog_id),
1158 NVL(PROGRAM_UPDATE_DATE, sysdate),
1159 COMPONENT_SEQUENCE_ID,
1160 G_Insert,
1161 1
1162 FROM bom_inventory_comps_interface
1163 WHERE process_flag = 2
1164 AND transaction_type = G_Insert
1165 AND (UPPER(interface_entity_type) = 'BILL'
1166 OR interface_entity_type is null)
1167 AND reference_designator is not null;
1168
1169 COMMIT;
1170
1171 RETURN (0);
1172 EXCEPTION
1173 WHEN others THEN
1174 err_text := 'Bom_Component_Api(Assign-'||stmt_num||') '||substrb(SQLERRM,1,500);
1175 RETURN(SQLCODE);
1176 END Assign_Component;
1177
1178 /*---------------------- Verify_Component_Count ------------------------*/
1179
1180 /* Bug: 2372788 Component count under a bill cannot exceed 9999 */
1181
1182 FUNCTION Verify_Component_Count
1183 ( bill_seq_id IN NUMBER,
1184 err_text OUT NOCOPY VARCHAR2
1185 )
1186 return NUMBER IS
1187
1188 l_total NUMBER := 0;
1189
1190 BEGIN
1191
1192 SELECT count(*) INTO l_total FROM bom_inventory_components WHERE
1193 bill_sequence_id = bill_seq_id;
1194
1195 IF l_total > 9999 THEN
1196 err_text := 'Bom_Component_Api(ComponentCount): Total number of components exceeds 9999 for this bill';
1197 Return 9999;
1198 END IF;
1199
1200 err_text := NULL;
1201 Return 0;
1202
1203 EXCEPTION WHEN others THEN
1204 err_text := 'Bom_Component_Api(ComponentCount) '||substrb(SQLERRM,1,60);
1205 RETURN(SQLCODE);
1206
1207 END Verify_Component_Count;
1208
1209
1210 /*---------------------- Verify_Unique_Component ------------------------*/
1211 /*
1212 NAME
1213 Verify_Unique_Component
1214 DESCRIPTION
1215 verifies if the given component sequence id is unique in prod and
1216 interface tables
1217 REQUIRES
1218 cmp_seq_id component sequence id
1219 exist_flag 1 - check for existence
1220 2 - check for uniqueness
1221 err_text out buffer to return error message
1222 MODIFIES
1223 RETURNS
1224 0 if successful
1225 SQLCODE if unsuccessful
1226 NOTES
1227 -----------------------------------------------------------------------------*/
1228 FUNCTION Verify_Unique_Component (
1229 cmp_seq_id NUMBER,
1230 exist_flag NUMBER,
1231 err_text OUT NOCOPY VARCHAR2
1232 )
1233 return INTEGER
1234 IS
1235 dummy NUMBER;
1236 NOT_UNIQUE EXCEPTION;
1237 stmt_num NUMBER := 0;
1238 BEGIN
1239 /*
1240 ** First check in prod tables
1241 */
1242 stmt_num := 1;
1243 BEGIN
1244 SELECT 1
1245 INTO dummy
1246 FROM bom_inventory_components
1247 WHERE component_sequence_id = cmp_seq_id;
1248
1249 IF (exist_flag = 1) THEN
1250 RETURN(0);
1251 ELSE
1252 RAISE not_unique;
1253 END IF;
1254 EXCEPTION
1255 WHEN no_data_found THEN
1256 IF (exist_flag = 2) THEN
1257 null;
1258 ELSE
1259 err_text := substrb('Bom_Component_Api(Unique): Component does not
1260 exist '||SQLERRM,1,70);
1261 RETURN(9999);
1265 END;
1262 END IF;
1263 WHEN not_unique THEN
1264 RAISE not_unique;
1266
1267 /*
1268 ** Check in interface table
1269 */
1270 stmt_num := 2;
1271 SELECT count(*)
1272 INTO dummy
1273 FROM bom_inventory_comps_interface
1274 WHERE component_sequence_id = cmp_seq_id
1275 AND (UPPER(interface_entity_type) = 'BILL'
1276 OR interface_entity_type is null)
1277 AND process_flag = 4;
1278
1279 IF (dummy = 0) THEN
1280 IF (exist_flag = 2) THEN
1281 RETURN(0);
1282 ELSE
1283 RAISE no_data_found;
1284 END IF;
1285 END IF;
1286
1287 IF (dummy > 0) THEN
1288 IF (exist_flag = 2) THEN
1289 RAISE not_unique;
1290 ELSE
1291 RETURN(0);
1292 END IF;
1293 END IF;
1294
1295 EXCEPTION
1296 WHEN No_Data_Found THEN
1297 err_text := substrb('Bom_Component_Api(Unique): Component does not exist '||SQLERRM,1,70);
1298 RETURN(9999);
1299 WHEN Not_Unique THEN
1300 err_text := 'Bom_Component_Api(Unique) '||'Duplicate component sequence ids';
1301 RETURN(9999);
1302 WHEN others THEN
1303 err_text := 'Bom_Component_Api(Unique-'||stmt_num||') '||substrb(SQLERRM,1,60);
1304 RETURN(SQLCODE);
1305 END Verify_unique_component;
1306
1307
1308 /*--------------------- Verify_Duplicate_Component ----------------------*/
1309 /*
1310 NAME
1311 Verify_duplicate_component - verify if there is another component
1312 with the same bill, effective date, and operation seq num.
1313 DESCRIPTION
1314 Verifies in the production and interface tables if component with
1315 the same bill, effective date, and operation seq num exists.
1316 REQUIRES
1317 bill_seq_id bill sequence id
1318 eff_date effectivity date
1319 cmp_item_id component item id
1320 op_seq operation seq
1321 err_text out buffer to return error message
1322 MODIFIES
1323 RETURNS
1324 0 if successful
1325 cnt if component already exists
1326 SQLCODE if error
1327 NOTES
1328 -----------------------------------------------------------------------------*/
1329 FUNCTION Verify_Duplicate_Component(
1330 bill_seq_id NUMBER,
1331 eff_date VARCHAR2,
1332 cmp_item_id NUMBER,
1333 op_seq NUMBER,
1334 act VARCHAR2,
1335 comp_seq_id NUMBER,
1336 err_text OUT NOCOPY VARCHAR2
1337 )
1338 return INTEGER
1339 IS
1340 cnt NUMBER := 0;
1341 ALREADY_EXISTS EXCEPTION;
1342 stmt_num NUMBER := 0;
1343 BEGIN
1344 stmt_num := 1;
1345 BEGIN
1346 SELECT component_sequence_id
1347 INTO cnt
1348 FROM bom_inventory_components
1349 WHERE bill_sequence_id = bill_seq_id
1350 AND effectivity_date = to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')
1351 AND component_item_id = cmp_item_id
1352 AND operation_seq_num = op_seq
1353 AND ((act = G_UPDATE AND component_sequence_id <> comp_seq_id)
1354 OR
1355 (act = G_Insert));
1356 RAISE already_exists;
1357 EXCEPTION
1358 WHEN already_exists THEN
1359 err_text := 'Bom_Component_Api(Duplicate): Component already exists in production';
1360 RETURN(cnt);
1361 WHEN no_data_found THEN
1362 null;
1363 END;
1364
1365 stmt_num := 2;
1366 BEGIN
1367 SELECT component_sequence_id
1368 INTO cnt
1369 FROM bom_inventory_comps_interface
1370 WHERE bill_sequence_id = bill_seq_id
1371 AND effectivity_date = to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')
1372 AND component_item_id = cmp_item_id
1373 AND operation_seq_num = op_seq
1374 AND rownum = 1
1375 AND transaction_type in (G_Insert, G_UPDATE)
1376 AND (UPPER(interface_entity_type) = 'BILL'
1377 OR interface_entity_type is null)
1378 AND process_flag = 4;
1379
1380 RAISE already_exists;
1381 EXCEPTION
1382 WHEN already_exists THEN
1383 err_text := 'Bom_Component_Api(Duplicate): Component already exists in interface';
1384 RETURN(cnt);
1385 WHEN no_data_found THEN
1386 null;
1387 END;
1388 RETURN(0);
1389
1390 EXCEPTION
1391 WHEN others THEN
1392 err_text := 'Bom_Component_Api(Duplicate-'||stmt_num||') '||substrb(SQLERRM,1,60);
1393 RETURN(SQLCODE);
1394 END Verify_Duplicate_Component;
1395
1396
1397 /* ---------------------------- Verify_Overlaps --------------------------- */
1398 /*
1399 NAME
1400 Verify_Overlaps
1401 DESCRIPTION
1402 Verify the current component does not have overlapping effectivity
1403 REQUIRES
1404 bom_id bill sequence id
1405 op_num operation sequence number
1406 cmp_id component item id
1407 eff_date effectivity date
1408 dis_date disable date
1409 err_text out buffer to return error message
1410 MODIFIES
1411 RETURNS
1412 0 if successful
1413 SQLCODE if unsuccessful
1414 NOTES
1418 op_num NUMBER,
1415 -----------------------------------------------------------------------------*/
1416 FUNCTION Verify_Overlaps (
1417 bom_id NUMBER,
1419 cmp_id NUMBER,
1420 eff_date VARCHAR2,
1421 dis_date VARCHAR2,
1422 act VARCHAR2,
1423 comp_seq_id NUMBER,
1424 err_text OUT NOCOPY VARCHAR2
1425 )
1426 return INTEGER
1427 IS
1428 dummy NUMBER;
1429 OVERLAP EXCEPTION;
1430 stmt_num NUMBER := 0;
1431 BEGIN
1432 stmt_num := 1;
1433 SELECT count(*)
1434 INTO dummy
1435 FROM bom_inventory_components
1436 WHERE bill_sequence_id = bom_id
1437 AND component_item_id = cmp_id
1438 AND operation_seq_num = op_num
1439 AND implementation_date is not null
1440 AND ((act = G_UPDATE AND component_sequence_id <> comp_seq_id)
1441 OR
1442 (act = G_Insert))
1443 AND ((dis_date is null
1444 AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
1445 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1))
1446 OR
1447 (dis_date is not null
1448 AND to_date(dis_date,'YYYY/MM/DD HH24:MI:SS') > effectivity_date
1449 AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
1450 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')+1)))
1451 AND not exists -- Added for Bug 1929222
1452 ( SELECT null
1453 FROM bom_inventory_comps_interface
1454 WHERE bill_sequence_id = bom_id
1455 AND process_flag = 4
1456 AND component_item_id = cmp_id
1457 AND operation_seq_num = op_num
1458 AND implementation_date is not null
1459 AND transaction_type = G_UPDATE
1460 AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') >=
1461 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS')+1)
1462 );
1463
1464 IF (dummy <> 0) THEN
1465 RAISE OVERLAP;
1466 END IF;
1467
1468 stmt_num := 2;
1469 SELECT count(*)
1470 INTO dummy
1471 FROM bom_inventory_comps_interface
1472 WHERE bill_sequence_id = bom_id
1473 AND process_flag = 4
1474 AND transaction_type in (G_Insert, G_UPDATE)
1475 AND (UPPER(interface_entity_type) = 'BILL'
1476 OR interface_entity_type is null)
1477 AND component_item_id = cmp_id
1478 AND operation_seq_num = op_num
1479 AND implementation_date is not null
1480 AND ((dis_date is null
1481 AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
1482 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1))
1483 OR
1484 (dis_date is not null
1485 AND to_date(dis_date,'YYYY/MM/DD HH24:MI:SS') > effectivity_date
1486 AND to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') <
1487 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI:SS') +1)));
1488 IF (dummy <> 0) THEN
1489 RAISE overlap;
1490 END IF;
1491
1492 RETURN(0);
1493 EXCEPTION
1494 WHEN Overlap THEN
1495 err_text := 'Component causes overlapping effectivity';
1496 RETURN(9999);
1497 WHEN others THEN
1498 err_text := 'Bom_Component_Api(Overlap-'||stmt_num||') '||substrb(SQLERRM,1,60);
1499 RETURN(SQLCODE);
1500 END Verify_Overlaps;
1501
1502
1503 /*------------------------- Verify_Item_Attributes -------------------------*/
1504 /*
1505 NAME
1506 Verify_Item_Attributes
1507 DESCRIPTION
1508 Component must be bom enabled
1509 Component item cannot be same as assembly item
1510 Mfg bills must have mfg component items
1511
1512 Component Types
1513
1514 Bill PTO ATO PTO ATO ATO PTO Standard
1515 Types Model Model OC OC Planning Item Item Item
1516 ------------- ------------------------------------------------------------
1517 PTO Model Yes Yes Yes No No Yes Yes Yes
1518 ATO Model No Yes No Yes No Yes No Yes
1519 PTO OC Yes Yes Yes No No Yes Yes Yes
1520 ATO OC No Yes No Yes No Yes No Yes
1521 Planning Yes Yes Yes Yes Yes Yes Yes Yes
1522 ATO Item No No No No No Yes No Yes
1523 PTO Item No No No No No No Yes Yes
1524 Standard Item No No No No No Yes No Yes
1525 Config Item No Yes No Yes No Yes No Yes
1526
1527 REQUIRES
1528 org_id organization id
1529 cmp_id component item id
1530 assy_id assembly item id
1531 eng_bill engineering bill (1=no, 2=yes)
1532 err_text out buffer to return error message
1533 MODIFIES
1534 MTL_INTERFACE_ERRORS
1535 RETURNS
1536 0 if successful
1537 SQLCODE if unsuccessful
1538 NOTES
1539 -----------------------------------------------------------------------------*/
1540 FUNCTION Verify_Item_Attributes (
1541 org_id NUMBER,
1545 err_text OUT NOCOPY VARCHAR2
1542 cmp_id NUMBER,
1543 assy_id NUMBER,
1544 eng_bill NUMBER,
1546 )
1547 return INTEGER
1548 IS
1549 ret_code NUMBER;
1550 stmt_num NUMBER := 0;
1551 dummy NUMBER;
1552 l_atp_comps_flag VARCHAR2(1);
1553 l_atp_flag VARCHAR2(1);
1554 assy_atp_components_flag mtl_system_items.atp_components_flag%type;
1555 assy_wip_supply_type mtl_system_items.wip_supply_type%type;
1556 assy_replenish_to_order_flag mtl_system_items.replenish_to_order_flag%type;
1557 assy_pick_components_flag mtl_system_items.pick_components_flag%type;
1558
1559 BEGIN
1560 stmt_num := 1;
1561 SELECT 1
1562 INTO dummy
1563 FROM mtl_system_items assy, mtl_system_items comp
1564 WHERE comp.organization_id = org_id
1565 AND assy.organization_id = org_id
1566 AND comp.inventory_item_id = cmp_id
1567 AND assy.inventory_item_id = assy_id
1568 AND comp.bom_enabled_flag = 'Y'
1569 AND comp.inventory_item_id <> assy.inventory_item_id
1570 AND ((eng_bill = 1 and comp.eng_item_flag = 'N')
1571 OR (eng_bill = 2))
1572 AND ((assy.bom_item_type = 1 and comp.bom_item_type <> 3)
1573 OR (assy.bom_item_type = 2 and comp.bom_item_type <> 3)
1574 OR (assy.bom_item_type = 3)
1575 OR (assy.bom_item_type = 4
1576 AND (comp.bom_item_type = 4
1577 OR (comp.bom_item_type in (2,1)
1578 AND comp.replenish_to_order_flag = 'Y'
1579 AND assy.base_item_id is not null
1580 AND assy.replenish_to_order_flag = 'Y'))))
1581 AND (assy.bom_item_type = 3
1582 OR assy.pick_components_flag = 'Y'
1583 OR comp.pick_components_flag = 'N')
1584 AND (assy.bom_item_type = 3
1585 OR comp.bom_item_type <> 2
1586 OR (comp.bom_item_type = 2
1587 AND ((assy.pick_components_flag = 'Y'
1588 AND comp.pick_components_flag = 'Y')
1589 OR (assy.replenish_to_order_flag = 'Y'
1590 AND comp.replenish_to_order_flag = 'Y'))))
1591 AND not(assy.bom_item_type = 4
1592 AND assy.pick_components_flag = 'Y'
1593 AND comp.bom_item_type = 4
1594 AND comp.replenish_to_order_flag = 'Y');
1595
1596 RETURN(0);
1597 -- Starting with R11, the ATP_Flag can have additional values R and C
1598 -- apart from Y and N
1599 -- Starting with 11i, even ATP Components flag has additional values
1600 --which are similar to ATP flag. To incorporate these values for
1601 -- multi-level ATP we also release the update allowed constraint
1602 -- on Check_ATP
1603
1604
1605 -- ATP Components flag for an item indicates whether an item's child components should be
1606 -- ATP checked. A component c1 (ATP Check = Material) can be on a subassembly that does not
1607 -- need to do atp check for components and hence has ATP Components of subassy is set to No. In
1608 -- current validation c1 cannot be added onto the subassy because we restrict that.
1609
1610 -- We will now release the restriction on the ATP Check and ATP Components flag. This will allow the
1611 -- users to control what can and cannot be structured on a bill. If the item level attribute for a
1612 -- component is ATP Check = Yes, BOM will allow the user to turn it off at the component level.
1613 -- The default value will be copied from the item.
1614
1615 /*
1616 BEGIN
1617
1618 SELECT atp_components_flag,
1619 wip_supply_type,
1620 replenish_to_order_flag,
1621 pick_components_flag
1622 INTO assy_atp_components_flag,
1623 assy_wip_supply_type,
1624 assy_replenish_to_order_flag,
1625 assy_pick_components_flag
1626 FROM mtl_system_items
1627 WHERE inventory_item_id = assy_id
1628 AND organization_id = org_id;
1629
1630 SELECT atp_components_flag,
1631 atp_flag
1632 INTO l_atp_comps_flag,
1633 l_atp_flag
1634 FROM mtl_system_items msi
1635 WHERE inventory_item_id = cmp_id
1636 AND organization_id = org_id;
1637
1638 IF((assy_atp_components_flag = 'N' AND
1639 ( nvl(assy_wip_supply_type,1) = 6 OR
1640 assy_replenish_to_order_flag = 'Y' OR
1641 assy_pick_components_flag = 'Y'
1642 )
1643 ) AND
1644 ( l_atp_comps_flag IN ('Y','C', 'R', 'N') OR
1645 l_atp_flag IN ('Y', 'R','C','N' )
1646 )
1647 ) OR
1648 assy_atp_components_flag IN ('Y','R','C')
1649 THEN
1650 -- Do nothing since this is permitted
1651 -- If the Assembly item is Phantom or an ATO or PTO and has ATP
1652 -- Components as 'N'
1653 -- Even then we will allow ATP components
1654 RETURN(0);
1655 ELSIF (assy_atp_components_flag = 'N' AND
1656 (l_atp_comps_flag = 'N' AND l_atp_flag IN ('N','Y'))
1657 )
1658 THEN
1659 -- Even in this case do nothing since both the flags are N and
1660 -- hence is a valid combination
1661 RETURN(0);
1662 END IF;
1663 err_text := 'Component ATP flag item attributes invalid';
1667
1664 RETURN(9999);
1665
1666 */
1668 /*
1669 SELECT 1
1670 INTO dummy
1671 FROM mtl_system_items assy, mtl_system_items comp
1672 WHERE comp.organization_id = org_id
1673 AND assy.organization_id = org_id
1674 AND comp.inventory_item_id = cmp_id
1675 AND assy.inventory_item_id = assy_id
1676 AND (comp.atp_components_flag = 'Y' OR
1677 comp.atp_flag = 'Y')
1678 AND assy.atp_components_flag = 'N'
1679 AND (nvl(assy.wip_supply_type,1) = 6
1680 OR assy.replenish_to_order_flag = 'Y'
1681 OR assy.pick_components_flag = 'Y');
1682 err_text := 'Component ATP flag item attributes invalid';
1683 RETURN(9999);
1684 */
1685 -- END;
1686
1687 EXCEPTION
1688 WHEN No_Data_Found THEN
1689 err_text := 'Component and assembly item attributes invalid';
1690 return(9999);
1691 WHEN others THEN
1692 err_text := 'Bom_Component_Api(Attributes-'||stmt_num||') '||substrb(SQLERRM,1,60);
1693 return(SQLCODE);
1694 END Verify_Item_Attributes;
1695
1696
1697 /* ----------------------------- Valid_Op_Seqs ----------------------------- */
1698 /*
1699 NAME
1700 Valid_op_seqs - validate the operation seq nums
1701 DESCRIPTION
1702 verify if op seq is valid. For alternate bills, op seq can be of same
1703 alternate or primary if alternate does not exist
1704 REQUIRES
1705 err_text out buffer to return error message
1706 MODIFIES
1707 MTL_INTERFACE_ERRORS
1708 RETURNS
1709 0 if successful
1710 SQLCODE if unsuccessful
1711 NOTES
1712 -----------------------------------------------------------------------------*/
1713 FUNCTION Valid_Op_Seqs (
1714 org_id NUMBER,
1715 assy_id NUMBER,
1716 alt_desg VARCHAR2,
1717 op_seq NUMBER,
1718 err_text OUT NOCOPY VARCHAR2
1719 )
1720 return INTEGER
1721 IS
1722 ret_code NUMBER;
1723 stmt_num NUMBER := 0;
1724 dummy NUMBER;
1725
1726 BEGIN
1727 stmt_num := 1;
1728 SELECT bom_item_type
1729 INTO dummy
1730 FROM mtl_system_items
1731 WHERE organization_id = org_id
1732 AND inventory_item_id = assy_id;
1733
1734 IF (dummy = 3 and op_seq <> 1) THEN
1735 err_text := 'Planning bom cannot have routing';
1736 RETURN (9999);
1737 END IF;
1738
1739 /*
1740 Bug 1322959 :
1741 commented the effectivity Date < sysdate as This was causing the error
1742 invalid operation sequence. When the Operation sequence is defined
1743 as effective in a future date form BOMFDBOM was allowing to assign this
1744 operating sequence number for any components where as the Interface
1745 was giving error. Made the following fix to ensure the similar
1746 behaviour in both form and Interface.
1747 The fix allows picking the operation Sequences which are effective
1748 in future also.
1749 */
1750
1751 stmt_num := 2;
1752 IF (op_seq <> 1) THEN
1753 SELECT distinct operation_seq_num
1754 INTO dummy
1755 FROM bom_operation_sequences a, bom_operational_routings b
1756 WHERE b.organization_id = org_id
1757 AND b.assembly_item_id = assy_id
1758 AND operation_seq_num = op_seq
1759 -- AND a.effectivity_date < sysdate
1760 AND NVL(a.disable_date,sysdate+1) > sysdate
1761 AND b.common_routing_sequence_id = a.routing_sequence_id
1762 AND ((alt_desg is null and b.alternate_routing_designator is null)
1763 OR
1764 (alt_desg is not null
1765 AND
1766 ((b.alternate_routing_designator = alt_desg)
1767 or
1768 (b.alternate_routing_designator is null
1769 AND not exists
1770 (SELECT 'No alt routing'
1771 FROM bom_operational_routings c
1772 WHERE c.organization_id = org_id
1773 AND c.assembly_item_id = assy_id
1774 AND c.alternate_routing_designator = alt_desg)))));
1775 END IF;
1776 RETURN(0);
1777
1778 EXCEPTION
1779 WHEN No_Data_Found THEN
1780 err_text := 'Invalid operation seq num';
1781 RETURN (9999);
1782 WHEN others THEN
1783 err_text := 'Bom_Component_Api(OpSeq-'||stmt_num||') '||substrb(SQLERRM,1,60);
1784 RETURN(SQLCODE);
1785 END Valid_Op_Seqs;
1786
1787
1788 /* -------------------------- Validate_Component ------------------------- */
1789 /*
1790 NAME
1791 Validate_Component
1792 DESCRIPTION
1793
1794 REQUIRES
1795 err_text out buffer to return error message
1796 MODIFIES
1797 MTL_INTERFACE_ERRORS
1798 RETURNS
1799 0 if successful
1800 SQLCODE if unsuccessful
1801 NOTES
1802 -----------------------------------------------------------------------------*/
1803 FUNCTION Validate_Component (
1804 org_id NUMBER,
1805 all_org NUMBER := 2,
1806 user_id NUMBER,
1807 login_id NUMBER,
1808 prog_appid NUMBER,
1809 prog_id NUMBER,
1813 return INTEGER
1810 req_id NUMBER,
1811 err_text IN OUT NOCOPY VARCHAR2
1812 )
1814 IS
1815 ret_code NUMBER;
1816 ret_code_error EXCEPTION; -- ret_code <> 0
1817 stmt_num NUMBER := 0;
1818 commit_cnt NUMBER;
1819 dummy VARCHAR2(50);
1820 eng_bill NUMBER;
1821 oe_install VARCHAR2(1);
1822 inv_asst VARCHAR2(1);
1823 r_subinv NUMBER;
1824 r_loc NUMBER;
1825 loc_ctl NUMBER;
1826 org_loc NUMBER;
1827 sub_loc_code NUMBER;
1828 X_expense_to_asset_transfer NUMBER;
1829 ref_qty NUMBER := 0;
1830 int_ref_qty NUMBER := 0;
1831 go_on BOOLEAN;
1832 continue_loop EXCEPTION;
1833 continue_loop2 EXCEPTION;
1834 write_loc_error EXCEPTION;
1835 write_subinv_error EXCEPTION;
1836 update_comp EXCEPTION;
1837 X_creation_date DATE;
1838 X_created_by NUMBER;
1839 X_operation_seq_num NUMBER;
1840 X_item_num NUMBER;
1841 X_component_quantity NUMBER;
1842 X_component_yield_factor NUMBER;
1843 X_component_remarks VARCHAR2(240);
1844 X_effectivity_date DATE;
1845 X_change_notice VARCHAR2(10);
1846 X_implementation_date DATE;
1847 X_disable_date DATE;
1848 X_attribute_category VARCHAR2(30);
1849 X_attribute1 VARCHAR2(150);
1850 X_attribute2 VARCHAR2(150);
1851 X_attribute3 VARCHAR2(150);
1852 X_attribute4 VARCHAR2(150);
1853 X_attribute5 VARCHAR2(150);
1854 X_attribute6 VARCHAR2(150);
1855 X_attribute7 VARCHAR2(150);
1856 X_attribute8 VARCHAR2(150);
1857 X_attribute9 VARCHAR2(150);
1858 X_attribute10 VARCHAR2(150);
1859 X_attribute11 VARCHAR2(150);
1860 X_attribute12 VARCHAR2(150);
1861 X_attribute13 VARCHAR2(150);
1862 X_attribute14 VARCHAR2(150);
1863 X_attribute15 VARCHAR2(150);
1864 X_request_id NUMBER;
1865 X_program_application_id NUMBER;
1866 X_program_id NUMBER;
1867 X_program_update_date DATE;
1868 X_planning_factor NUMBER;
1869 X_quantity_related NUMBER;
1870 X_so_basis NUMBER;
1871 X_optional NUMBER;
1872 X_mutually_exclusive_options NUMBER;
1873 X_include_in_cost_rollup NUMBER;
1874 X_check_atp NUMBER;
1875 X_shipping_allowed NUMBER;
1876 X_required_to_ship NUMBER;
1877 X_required_for_revenue NUMBER;
1878 X_include_on_ship_docs NUMBER;
1879 X_include_on_bill_docs NUMBER;
1880 X_low_quantity NUMBER;
1881 X_high_quantity NUMBER;
1882 X_acd_type NUMBER;
1883 X_old_component_sequence_id NUMBER;
1884 X_wip_supply_type NUMBER;
1885 X_pick_components NUMBER;
1886 X_supply_subinventory VARCHAR2(10);
1887 X_supply_locator_id NUMBER;
1888 X_operation_lead_time_percent NUMBER;
1889 X_cost_factor NUMBER;
1890 X_bom_item_type NUMBER;
1891 X_revised_item_sequence_id NUMBER;
1892 X_component_item_id NUMBER;
1893 X_bill_sequence_id NUMBER;
1894 x_bill_type NUMBER; -- BOM Item type of the bill
1895 x_assembly_item_id NUMBER;
1896 x_valid_comp NUMBER;
1897 l_pud DATE; -- Program Update Date
1898 /*
1899 ** Select all INSERTS
1900 */
1901 CURSOR c1 IS
1902 SELECT component_sequence_id CSI, bill_sequence_id BSI,
1903 transaction_id TI, transaction_type A,
1904 to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
1905 effectivity_date EDD,
1906 to_char(disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
1907 to_char(implementation_date,'YYYY/MM/DD HH24:MI:SS') ID,
1908 operation_seq_num OSN, supply_locator_id SLI,
1909 supply_subinventory SS,
1910 msic.organization_id OI, component_item_id CII,
1911 assembly_item_id AII, alternate_bom_designator ABD,
1912 planning_factor PF, optional O, check_atp CATP,
1913 msic.atp_flag AF, so_basis SB, required_for_revenue RFR,
1914 required_to_ship RTS, mutually_exclusive_options MEO,
1915 low_quantity LQ, high_quantity HQ,change_notice CN,
1916 quantity_related QR, include_in_cost_rollup ICR,
1917 shipping_allowed SA, include_on_ship_docs ISD,
1918 component_yield_factor CYF, ici.wip_supply_type WST,
1919 component_quantity CQ, msic.bom_item_type BITC,
1920 msic.pick_components_flag PCF, msia.bom_item_type BITA,
1921 msia.pick_components_flag PCFA,
1922 msia.replenish_to_order_flag RTOF,
1926 FROM mtl_system_items msic,
1923 msic.replenish_to_order_flag RTOFC,
1924 msia.atp_components_flag ACF,
1925 msic.ato_forecast_control AFC
1927 mtl_system_items msia,
1928 bom_inventory_comps_interface ici
1929 WHERE process_flag = 2
1930 AND transaction_type = G_Insert
1931 AND (UPPER(ici.interface_entity_type) = 'BILL'
1932 OR ici.interface_entity_type is null)
1933 AND msic.organization_id = ici.organization_id
1934 AND msia.organization_id = ici.organization_id
1935 AND msic.inventory_item_id = ici.component_item_id
1936 AND msia.inventory_item_id = ici.assembly_item_id;
1937 /*
1938 ** Select all UPDATES and DELETES
1939 */
1940 CURSOR c2 IS
1941 SELECT ici.component_sequence_id CSI, ici.bill_sequence_id BSI,
1942 ici.transaction_id TI, ici.acd_type ACD,
1943 ici.transaction_type A,
1944 to_char(ici.effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
1945 ici.effectivity_date EDD, ici.item_num INUM,
1946 to_char(ici.disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
1947 ici.disable_date DDD,
1948 ici.implementation_date ID,
1949 ici.operation_seq_num OSN, ici.supply_locator_id SLI,
1950 ici.supply_subinventory SS, ici.creation_date CD,
1951 ici.created_by CB, ici.change_notice CN,
1952 ici.old_component_sequence_id OCSI,
1953 ici.new_effectivity_date NED,
1954 ici.include_in_cost_rollup IICR, ici.check_atp CA,
1955 ici.pick_components PC, ici.operation_lead_time_percent OLTP,
1956 ici.revised_item_sequence_id RISI, ici.bom_item_type BIT,
1957 ici.new_operation_seq_num NOSN, ici.component_remarks CR,
1958 msic.organization_id OI, ici.component_item_id CII,
1959 ici.assembly_item_id AII, ici.alternate_bom_designator ABD,
1960 ici.planning_factor PF, ici.optional O, ici.check_atp CATP,
1961 msic.atp_flag AF, ici.so_basis SB,
1962 ici.required_for_revenue RFR, ici.include_on_ship_docs IOSD,
1963 ici.required_to_ship RTS, ici.mutually_exclusive_options MEO,
1964 ici.low_quantity LQ, ici.high_quantity HQ,
1965 ici.quantity_related QR, ici.include_in_cost_rollup ICR,
1966 ici.shipping_allowed SA, ici.include_on_ship_docs ISD,
1967 ici.component_yield_factor CYF, ici.wip_supply_type WST,
1968 ici.component_quantity CQ, ici.attribute_category AC,
1969 ici.attribute1 A1, ici.attribute2 A2, ici.attribute3 A3,
1970 ici.attribute4 A4, ici.attribute5 A5, ici.attribute6 A6,
1971 ici.attribute7 A7, ici.attribute8 A8, ici.attribute9 A9,
1972 ici.attribute10 A10, ici.attribute11 A11, ici.attribute12 A12,
1973 ici.attribute13 A13, ici.attribute14 A14, ici.attribute15 A15,
1974 ici.request_id RI, ici.program_application_id PAI,
1975 ici.program_update_date PUD, ici.program_id PI,
1976 msic.bom_item_type BITC,
1977 msic.pick_components_flag PCF, msia.bom_item_type BITA,
1978 msia.pick_components_flag PCFA,
1979 msia.replenish_to_order_flag RTOF,
1980 msic.replenish_to_order_flag RTOFC,
1981 msia.atp_components_flag ACF,
1982 msic.ato_forecast_control AFC
1983 FROM mtl_system_items msic,
1984 mtl_system_items msia,
1985 bom_inventory_comps_interface ici
1986 WHERE process_flag = 2
1987 AND transaction_type in (G_UPDATE, G_DELETE)
1988 AND (UPPER(ici.interface_entity_type) = 'BILL'
1989 OR ici.interface_entity_type is null)
1990 AND msic.organization_id = ici.organization_id
1991 AND msia.organization_id = ici.organization_id
1992 AND msic.inventory_item_id = ici.component_item_id
1993 AND msia.inventory_item_id = ici.assembly_item_id;
1994 /*
1995 ** Select all UPDATES with process_flag = 99
1996 */
1997 CURSOR c3 IS
1998 SELECT ici.component_sequence_id CSI, ici.bill_sequence_id BSI,
1999 ici.transaction_id TI, ici.transaction_type A,
2000 ici.implementation_date ID,
2001 to_char(ici.effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
2002 ici.effectivity_date EDD, ici.item_num INUM,
2003 to_char(ici.disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
2004 ici.operation_seq_num OSN, ici.supply_locator_id SLI,
2005 ici.supply_subinventory SS,
2006 msic.organization_id OI, ici.component_item_id CII,
2007 ici.assembly_item_id AII, ici.alternate_bom_designator ABD,
2008 ici.planning_factor PF, ici.optional O, ici.check_atp CATP,
2009 msic.atp_flag AF, ici.so_basis SB,
2010 ici.required_for_revenue RFR,
2011 ici.required_to_ship RTS, ici.mutually_exclusive_options MEO,
2012 ici.low_quantity LQ, ici.high_quantity HQ,
2013 ici.quantity_related QR, ici.include_in_cost_rollup ICR,
2014 ici.shipping_allowed SA, ici.include_on_ship_docs ISD,
2015 ici.component_yield_factor CYF, ici.wip_supply_type WST,
2016 ici.component_quantity CQ, msic.bom_item_type BITC,
2020 msic.replenish_to_order_flag RTOFC,
2017 msic.pick_components_flag PCF, msia.bom_item_type BITA,
2018 msia.pick_components_flag PCFA,
2019 msia.replenish_to_order_flag RTOF,
2021 msia.atp_components_flag ACF,
2022 msic.ato_forecast_control AFC
2023 FROM mtl_system_items msic,
2024 mtl_system_items msia,
2025 bom_inventory_comps_interface ici
2026 WHERE ici.process_flag = 99
2027 AND ici.transaction_type = G_Update
2028 AND (UPPER(ici.interface_entity_type) = 'BILL'
2029 OR ici.interface_entity_type is null)
2030 AND msic.organization_id = ici.organization_id
2031 AND msia.organization_id = ici.organization_id
2032 AND msic.inventory_item_id = ici.component_item_id
2033 AND msia.inventory_item_id = ici.assembly_item_id;
2034
2035 BEGIN
2036
2037 /*
2038 ** FOR UPDATES and DELETES
2039 */
2040 go_on := TRUE;
2041 WHILE go_on LOOP
2042 commit_cnt := 0;
2043 FOR c2rec IN c2 LOOP
2044 commit_cnt := commit_cnt + 1;
2045 stmt_num := 1;
2046 /*
2047 ** Check if implemented record exists in Production
2048 */
2049 stmt_num := 2;
2050 BEGIN
2051 SELECT creation_date, created_by, operation_seq_num,item_num,
2052 component_quantity, component_yield_factor,
2053 component_remarks, effectivity_date, change_notice,
2054 implementation_date, disable_date, component_item_id,
2055 attribute_category, attribute1,
2056 attribute2, attribute3, attribute4, attribute5,
2057 attribute6, attribute7, attribute8, attribute9,
2058 attribute10, attribute11, attribute12, attribute13,
2059 attribute14, attribute15, request_id,
2060 program_application_id, program_id, program_update_date,
2061 planning_factor, quantity_related, so_basis, optional,
2062 mutually_exclusive_options, include_in_cost_rollup,
2063 check_atp, shipping_allowed, required_to_ship,
2064 required_for_revenue, include_on_ship_docs,
2065 include_on_bill_docs, low_quantity, high_quantity,
2066 acd_type, old_component_sequence_id, wip_supply_type,
2067 pick_components, supply_subinventory, supply_locator_id,
2068 operation_lead_time_percent, cost_factor, bom_item_type,
2069 revised_item_sequence_id, bill_sequence_id
2070 INTO X_creation_date, X_created_by, X_operation_seq_num,
2071 X_item_num, X_component_quantity, X_component_yield_factor,
2072 X_component_remarks, X_effectivity_date, X_change_notice,
2073 X_implementation_date, X_disable_date, X_component_item_id,
2074 X_attribute_category, X_attribute1,
2075 X_attribute2, X_attribute3, X_attribute4, X_attribute5,
2076 X_attribute6, X_attribute7, X_attribute8, X_attribute9,
2077 X_attribute10, X_attribute11, X_attribute12, X_attribute13,
2078 X_attribute14, X_attribute15, X_request_id,
2079 X_program_application_id, X_program_id,
2080 X_program_update_date,
2081 X_planning_factor, X_quantity_related, X_so_basis,
2082 X_optional, X_mutually_exclusive_options,
2083 X_include_in_cost_rollup, X_check_atp, X_shipping_allowed,
2084 X_required_to_ship, X_required_for_revenue,
2085 X_include_on_ship_docs, X_include_on_bill_docs,
2086 X_low_quantity, X_high_quantity, X_acd_type,
2087 X_old_component_sequence_id, X_wip_supply_type,
2088 X_pick_components, X_supply_subinventory,
2089 X_supply_locator_id, X_operation_lead_time_percent,
2090 X_cost_factor, X_bom_item_type,
2091 X_revised_item_sequence_id, X_bill_sequence_id
2092 FROM bom_inventory_components
2093 WHERE component_sequence_id = c2rec.CSI
2094 AND implementation_date is NOT NULL;
2095 EXCEPTION
2096 WHEN No_Data_Found THEN
2097 ret_code := INVPUOPI.mtl_log_interface_err(
2098 org_id => c2rec.OI,
2099 user_id => user_id,
2100 login_id => login_id,
2101 prog_appid => prog_appid,
2102 prog_id => prog_id,
2103 req_id => req_id,
2104 trans_id => c2rec.TI,
2105 error_text => err_text,
2106 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2107 msg_name => 'BOM_COMP_RECORD_MISSING',
2108 err_text => err_text);
2109
2110 UPDATE bom_inventory_comps_interface
2111 SET process_flag = 3
2112 WHERE transaction_id = c2rec.TI;
2113
2114 IF (ret_code <> 0) THEN
2115 return(ret_code);
2116 END IF;
2117 GOTO skip_loop;
2118 END;
2119 /*
2120 Added to Fix the Bug : 11277093
2121 The Decode statement in UPDATE is making the time part of the
2125 -- decode(c2rec.PUD, G_NullDate, '',NULL,X_program_update_date,c2rec.PUD),
2122 Program Update to 12 Mid Night. To correct this the decode has been
2123 exploded into If then Else here
2124 */
2126 --
2127 IF (c2rec.PUD = G_NullDate) THEN
2128 l_pud := '';
2129 ELSIF (c2rec.PUD is NULL) THEN
2130 l_pud := X_program_update_date;
2131 ELSE
2132 l_pud := c2rec.PUD;
2133 END IF;
2134 /*
2135 Bug No : 1279729
2136 Added validation check for Item Number
2137 Maximum allowed is 9999
2138 */
2139 IF (c2rec.INUM > 9999) then
2140 ret_code := INVPUOPI.mtl_log_interface_err(
2141 org_id => NULL,
2142 user_id => user_id,
2143 login_id => login_id,
2144 prog_appid => prog_appid,
2145 prog_id => prog_id,
2146 req_id => req_id,
2147 trans_id => c2rec.TI,
2148 error_text => err_text,
2149 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2150 msg_name => 'BOM_ITEM_NUM_INVALID',
2151 err_text => err_text);
2152
2153 UPDATE bom_inventory_comps_interface
2154 SET process_flag = 3
2155 WHERE transaction_id = c2rec.TI;
2156
2157 IF (ret_code <> 0) THEN
2158 return(ret_code);
2159 END IF;
2160 GOTO skip_loop;
2161 END IF;
2162
2163 /*
2164 ** ONLY for "Updates"
2165 */
2166 IF (c2rec.A = G_UPDATE) THEN
2167 /*
2168 ** For Product Family Members
2169 */
2170 IF (c2rec.BITA = G_ProductFamily) THEN
2171 /*
2172 ** Check if column is non-updatable and give error if user filled it in
2173 */
2174 IF (c2rec.CD is not null -- creation date
2175 OR c2rec.CB is not null -- created by
2176 OR c2rec.CN is not null -- change notice
2177 OR c2rec.SA is not null -- shipping allowed
2178 OR c2rec.OCSI is not null -- old comp seq id
2179 OR c2rec.PC is not null -- pick components
2180 OR c2rec.OLTP is not null -- op lead time percent
2181 OR c2rec.RISI is not null -- rev item seq id
2182 OR c2rec.BIT is not null -- bom item type
2183 OR c2rec.ACD is not null -- acd type
2184 OR (c2rec.OSN <> 1 AND
2185 c2rec.OSN is NOT NULL) -- operation_seq_num
2186 OR c2rec.INUM is not null -- item_num
2187 OR c2rec.CQ is not null -- component quantity
2188 OR c2rec.CYF is not null -- component yield factor
2189 OR c2rec.ID is not null -- implementation date
2190 OR c2rec.QR is not null -- quantity related
2191 OR c2rec.SB is not null -- so basis
2192 OR c2rec.O is not null -- optional
2193 OR c2rec.MEO is not null -- mutually exclusive options
2194 OR c2rec.ICR is not null -- include in cost rollup
2195 OR c2rec.CA is not null -- check atp
2196 OR c2rec.RTS is not null -- required to ship
2197 OR c2rec.RFR is not null -- required for revenue
2198 OR c2rec.ISD is not null -- include on ship docs
2199 OR c2rec.LQ is not null -- low quantity
2200 OR c2rec.HQ is not null -- high quantity
2201 OR c2rec.WST is not null -- wip supply type
2202 OR c2rec.SS is not null -- supply subinventory
2203 OR c2rec.SLI is not null -- supply locator id
2204 ) THEN
2205 ret_code := INVPUOPI.mtl_log_interface_err(
2206 org_id => c2rec.OI,
2207 user_id => user_id,
2208 login_id => login_id,
2209 prog_appid => prog_appid,
2210 prog_id => prog_id,
2211 req_id => req_id,
2212 trans_id => c2rec.TI,
2213 error_text => err_text,
2214 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2215 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
2216 err_text => err_text);
2217
2218 UPDATE bom_inventory_comps_interface
2219 SET process_flag = 3
2220 WHERE transaction_id = c2rec.TI;
2221
2222 IF (ret_code <> 0) THEN
2223 return(ret_code);
2224 END IF;
2225
2226 GOTO skip_loop;
2227
2228 END IF;
2229 /*
2230 ** Update interface record with production record's values
2231 */
2232 stmt_num := 6;
2233 UPDATE bom_inventory_comps_interface
2234 SET operation_seq_num = X_operation_seq_num,
2235 component_item_id = X_component_item_id,
2236 creation_date = X_creation_date,
2237 created_by = X_created_by,
2238 item_num = X_item_num,
2242 X_component_remarks, G_NullChar, '', c2rec.CR),
2239 component_quantity = X_component_quantity,
2240 component_yield_factor = X_component_yield_factor,
2241 component_remarks = decode(c2rec.CR, null,
2243 effectivity_date = nvl(trunc(c2rec.NED),
2244 X_effectivity_date),
2245 change_notice = X_change_notice,
2246 implementation_date = nvl(trunc(c2rec.NED),
2247 X_effectivity_date),
2248 disable_date = decode(c2rec.DDD, null,
2249 X_disable_date, G_NullDate, '', c2rec.DDD),
2250 planning_factor = nvl(c2rec.PF, X_planning_factor),
2251 quantity_related = X_quantity_related,
2252 so_basis = X_so_basis,
2253 optional = X_optional,
2254 mutually_exclusive_options = X_mutually_exclusive_options,
2255 include_in_cost_rollup = X_include_in_cost_rollup,
2256 check_atp = X_check_atp,
2257 shipping_allowed = X_shipping_allowed,
2258 required_to_ship = X_required_to_ship,
2259 required_for_revenue = X_required_for_revenue,
2260 include_on_ship_docs = X_include_on_ship_docs,
2261 include_on_bill_docs = X_include_on_bill_docs,
2262 low_quantity = X_low_quantity,
2263 high_quantity = X_high_quantity,
2264 acd_type = X_acd_type,
2265 old_component_sequence_id = X_old_component_sequence_id,
2266 bill_sequence_id = X_bill_sequence_id,
2267 wip_supply_type = X_wip_supply_type,
2268 pick_components = X_pick_components,
2269 supply_subinventory = X_supply_subinventory,
2270 supply_locator_id = X_supply_locator_id,
2271 operation_lead_time_percent = X_operation_lead_time_percent,
2272 revised_item_sequence_id = X_revised_item_sequence_id,
2273 cost_factor = X_cost_factor,
2274 bom_item_type = X_bom_item_type,
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 = l_pud,
2314 process_flag = 99
2315 WHERE transaction_id = c2rec.TI;
2316 ELSE
2317 /*
2318 ** For components
2319 */
2320
2321 /*
2322 ** Check if column is non-updatable and give error if user filled it in
2323 */
2324 IF (c2rec.CD is not null -- creation date
2325 OR c2rec.CB is not null -- created by
2329 OR c2rec.PC is not null -- pick components
2326 OR c2rec.CN is not null -- change notice
2327 OR c2rec.SA is not null -- shipping allowed
2328 OR c2rec.OCSI is not null -- old comp seq id
2330 OR c2rec.OLTP is not null -- op lead time percent
2331 OR c2rec.RISI is not null -- rev item seq id
2332 OR c2rec.BIT is not null -- bom item type
2333 OR c2rec.ACD is not null) THEN -- acd type
2334 ret_code := INVPUOPI.mtl_log_interface_err(
2335 org_id => c2rec.OI,
2336 user_id => user_id,
2337 login_id => login_id,
2338 prog_appid => prog_appid,
2339 prog_id => prog_id,
2340 req_id => req_id,
2341 trans_id => c2rec.TI,
2342 error_text => err_text,
2343 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2344 msg_name => 'BOM_COLUMN_NOT_UPDATABLE',
2345 err_text => err_text);
2346
2347 UPDATE bom_inventory_comps_interface
2348 SET process_flag = 3
2349 WHERE transaction_id = c2rec.TI;
2350
2351 IF (ret_code <> 0) THEN
2352 return(ret_code);
2353 END IF;
2354
2355 GOTO skip_loop;
2356 END IF;
2357 /*
2358 ** Update interface record with production record's values
2359 */
2360 stmt_num := 6;
2361 UPDATE bom_inventory_comps_interface
2362 SET operation_seq_num = nvl(c2rec.NOSN, X_operation_seq_num),
2363 component_item_id = X_component_item_id,
2364 creation_date = X_creation_date,
2365 created_by = X_created_by,
2366 item_num = nvl(c2rec.INUM, X_item_num),
2367 component_quantity = nvl(c2rec.CQ, X_component_quantity),
2368 component_yield_factor = nvl(c2rec.CYF,
2369 X_component_yield_factor),
2370 component_remarks = decode(c2rec.CR, null,
2371 X_component_remarks, G_NullChar, '', c2rec.CR),
2372 effectivity_date = nvl(c2rec.NED, X_effectivity_date),
2373 change_notice = X_change_notice,
2374 implementation_date = nvl(c2rec.ID, X_implementation_date),
2375 disable_date = decode(c2rec.DDD, null,
2376 X_disable_date, G_NullDate, '', c2rec.DDD),
2377 planning_factor = nvl(c2rec.PF, X_planning_factor),
2378 quantity_related = nvl(c2rec.QR, X_quantity_related),
2379 so_basis = nvl(c2rec.SB, X_so_basis),
2380 optional = nvl(c2rec.O, X_optional),
2381 mutually_exclusive_options = nvl(c2rec.MEO,
2382 X_mutually_exclusive_options),
2383 include_in_cost_rollup = nvl(c2rec.IICR,
2384 X_include_in_cost_rollup),
2385 check_atp = nvl(c2rec.CA, X_check_atp),
2386 shipping_allowed = X_shipping_allowed,
2387 required_to_ship = nvl(c2rec.RTS, X_required_to_ship),
2388 required_for_revenue = nvl(c2rec.RFR,
2389 X_required_for_revenue),
2390 include_on_ship_docs = nvl(c2rec.IOSD,
2391 X_include_on_ship_docs),
2392 include_on_bill_docs = X_include_on_bill_docs,
2393 low_quantity = decode(c2rec.LQ, G_NullNum, '', null,
2394 X_low_quantity, c2rec.LQ),
2395 high_quantity = decode(c2rec.HQ, G_NullNum, '', null,
2396 X_high_quantity, c2rec.HQ),
2397 acd_type = X_acd_type,
2398 old_component_sequence_id = X_old_component_sequence_id,
2399 bill_sequence_id = X_bill_sequence_id,
2400 wip_supply_type = decode(c2rec.WST, null,
2401 X_wip_supply_type, G_NullNum, '', c2rec.WST),
2402 pick_components = X_pick_components,
2403 supply_subinventory = decode(c2rec.SS, null,
2404 X_supply_subinventory, G_NullChar, '', c2rec.SS),
2405 supply_locator_id = decode(c2rec.SLI, null,
2406 X_supply_locator_id, G_NullNum, '', c2rec.SLI),
2407 -- operation_lead_time_percent = X_operation_lead_time_percent,
2408 operation_lead_time_percent = NULL, -- for bug 1804509
2409 revised_item_sequence_id = X_revised_item_sequence_id,
2410 cost_factor = X_cost_factor,
2411 bom_item_type = X_bom_item_type,
2412 attribute_category = decode(c2rec.AC, G_NullChar, '', NULL,
2413 X_attribute_category, c2rec.AC),
2414 attribute1 = decode(c2rec.A1, G_NullChar, '', NULL,
2415 X_attribute1, c2rec.A1),
2416 attribute2 = decode(c2rec.A2, G_NullChar, '', NULL,
2417 X_attribute2, c2rec.A2),
2418 attribute3 = decode(c2rec.A3, G_NullChar, '', NULL,
2419 X_attribute3, c2rec.A3),
2423 X_attribute5, c2rec.A5),
2420 attribute4 = decode(c2rec.A4, G_NullChar, '', NULL,
2421 X_attribute4, c2rec.A4),
2422 attribute5 = decode(c2rec.A5, G_NullChar, '', NULL,
2424 attribute6 = decode(c2rec.A6, G_NullChar, '', NULL,
2425 X_attribute6, c2rec.A6),
2426 attribute7 = decode(c2rec.A7, G_NullChar, '', NULL,
2427 X_attribute7, c2rec.A7),
2428 attribute8 = decode(c2rec.A8, G_NullChar, '', NULL,
2429 X_attribute8, c2rec.A8),
2430 attribute9 = decode(c2rec.A9, G_NullChar, '', NULL,
2431 X_attribute9, c2rec.A9),
2432 attribute10 = decode(c2rec.A10, G_NullChar, '', NULL,
2433 X_attribute10, c2rec.A10),
2434 attribute11 = decode(c2rec.A11, G_NullChar, '', NULL,
2435 X_attribute11, c2rec.A11),
2436 attribute12 = decode(c2rec.A12, G_NullChar, '', NULL,
2437 X_attribute12, c2rec.A12),
2438 attribute13 = decode(c2rec.A13, G_NullChar, '', NULL,
2439 X_attribute13, c2rec.A13),
2440 attribute14 = decode(c2rec.A14, G_NullChar, '', NULL,
2441 X_attribute14, c2rec.A14),
2442 attribute15 = decode(c2rec.A15, G_NullChar, '', NULL,
2443 X_attribute15, c2rec.A15),
2444 request_id = decode(c2rec.RI, G_NullChar, '', NULL,
2445 X_request_id, c2rec.RI),
2446 program_application_id = decode(c2rec.PAI, G_NullNum,
2447 '', NULL, X_program_application_id, c2rec.PAI),
2448 program_id = decode(c2rec.PI, G_NullNum, '', NULL,
2449 X_program_id, c2rec.PI),
2450 program_update_date =l_pud,
2451 process_flag = 99
2452 WHERE transaction_id = c2rec.TI;
2453 END IF; -- End checking if Member or Component
2454 ELSIF (c2rec.A = G_DELETE) THEN
2455 /*
2456 ** Set Process Flag to 4 for "Deletes"
2457 */
2458 stmt_num := 10;
2459 UPDATE bom_inventory_comps_interface
2460 SET process_flag = 4
2461 WHERE transaction_id = c2rec.TI;
2462 END IF;
2463 <<skip_loop>>
2464 NULL;
2465 END LOOP;
2466 stmt_num := 7;
2467 COMMIT;
2468 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
2469 go_on := FALSE;
2470 END IF;
2471
2472 END LOOP;
2473
2474 /*
2475 ** FOR UPDATES - Validate
2476 */
2477 FOR c3rec IN c3 LOOP
2478 BEGIN
2479 stmt_num := 1;
2480 /*
2481 ** Verify uniqueness of bill seq id,effective date,op seq, and component item
2482 */
2483 ret_code := Verify_Duplicate_Component (
2484 bill_seq_id => c3rec.BSI,
2485 eff_date => c3rec.ED,
2486 cmp_item_id => c3rec.CII,
2487 op_seq => c3rec.OSN,
2488 act => c3rec.A,
2489 comp_seq_id => c3rec.CSI,
2490 err_text => err_text);
2491 IF (ret_code <> 0) THEN
2492 ret_code := INVPUOPI.mtl_log_interface_err(
2493 org_id => c3rec.OI,
2494 user_id => user_id,
2495 login_id => login_id,
2496 prog_appid => prog_appid,
2497 prog_id => prog_id,
2498 req_id => req_id,
2499 trans_id => c3rec.TI,
2500 error_text => err_text,
2501 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2502 msg_name => 'BOM_COMPONENT_DUPLICATE',
2503 err_text => err_text);
2504 UPDATE bom_inventory_comps_interface
2505 SET process_flag = 3
2506 WHERE transaction_id = c3rec.TI;
2507
2508 IF (ret_code <> 0) THEN
2509 RAISE ret_code_error;
2510 END IF;
2511 RAISE continue_loop2;
2512 END IF;
2513 /*
2514 Bug No : 1279729
2515 Added validation check for Item Number
2516 Maximum allowed is 9999
2517 */
2518 IF (c3rec.INUM > 9999) then
2519 ret_code := INVPUOPI.mtl_log_interface_err(
2520 org_id => NULL,
2521 user_id => user_id,
2522 login_id => login_id,
2523 prog_appid => prog_appid,
2524 prog_id => prog_id,
2525 req_id => req_id,
2526 trans_id => c3rec.TI,
2527 error_text => err_text,
2528 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2529 msg_name => 'BOM_ITEM_NUM_INVALID',
2530 err_text => err_text);
2531
2532 UPDATE bom_inventory_comps_interface
2533 SET process_flag = 3
2534 WHERE transaction_id = c3rec.TI;
2535
2539 RAISE continue_loop2;
2536 IF (ret_code <> 0) THEN
2537 RAISE ret_code_error;
2538 END IF;
2540 END IF;
2541
2542 /*
2543 ** Make sure there is no overlapping components
2544 */
2545 stmt_num := 4;
2546 IF (c3rec.ID is not null) THEN
2547 ret_code :=Verify_Overlaps (
2548 bom_id => c3rec.BSI,
2549 op_num => c3rec.OSN,
2550 cmp_id => c3rec.CII,
2551 eff_date => c3rec.ED,
2552 dis_date => c3rec.DD,
2553 act => c3rec.A,
2554 comp_seq_id => c3rec.CSI,
2555 err_text => err_text);
2556 IF (ret_code <> 0) THEN
2557 ret_code := INVPUOPI.mtl_log_interface_err(
2558 org_id => c3rec.OI,
2559 user_id => user_id,
2560 login_id => login_id,
2561 prog_appid => prog_appid,
2562 prog_id => prog_id,
2563 req_id => req_id,
2564 trans_id => c3rec.TI,
2565 error_text => err_text,
2566 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2567 msg_name => 'BOM_IMPL_COMP_OVERLAP',
2568 err_text => err_text);
2569 UPDATE bom_inventory_comps_interface
2570 SET process_flag = 3
2571 WHERE transaction_id = c3rec.TI;
2572
2573 IF (ret_code <> 0) THEN
2574 RAISE ret_code_error;
2575 END IF;
2576 RAISE continue_loop2;
2577 END IF;
2578 END IF;
2579 /*
2580 ** Effectivity date check
2581 */
2582 stmt_num := 5;
2583 IF (to_date(c3rec.ED,'YYYY/MM/DD HH24:MI:SS') >
2584 to_date(c3rec.DD,'YYYY/MM/DD HH24:MI:SS')) THEN
2585 ret_code := INVPUOPI.mtl_log_interface_err(
2586 org_id => c3rec.OI,
2587 user_id => user_id,
2588 login_id => login_id,
2589 prog_appid => prog_appid,
2590 prog_id => prog_id,
2591 req_id => req_id,
2592 trans_id => c3rec.TI,
2593 error_text => err_text,
2594 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2595 msg_name => 'BOM_EFFECTIVE_DATE_ERR',
2596 err_text => err_text);
2597 UPDATE bom_inventory_comps_interface
2598 SET process_flag = 3
2599 WHERE transaction_id = c3rec.TI;
2600
2601 IF (ret_code <> 0) THEN
2602 RAISE ret_code_error;
2603 END IF;
2604 RAISE continue_loop2;
2605 END IF;
2606
2607 IF (c3rec.BITA = G_ProductFamily) THEN
2608 /*
2609 ** Planning factor cannot be zero for Members
2610 */
2611 IF (c3rec.PF = 0) THEN
2612 ret_code := INVPUOPI.mtl_log_interface_err(
2613 org_id => c3rec.OI,
2614 user_id => user_id,
2615 login_id => login_id,
2616 prog_appid => prog_appid,
2617 prog_id => prog_id,
2618 req_id => req_id,
2619 trans_id => c3rec.TI,
2620 error_text => err_text,
2621 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2622 msg_name => 'BOM_PLANNING_FACTOR',
2623 err_text => err_text);
2624 UPDATE bom_inventory_comps_interface
2625 SET process_flag = 3
2626 WHERE transaction_id = c3rec.TI;
2627
2628 IF (ret_code <> 0) THEN
2629 RAISE ret_code_error;
2630 END IF;
2631 RAISE continue_loop2;
2632 END IF;
2633 RAISE Update_Comp;
2634 END IF; -- Check for Product Family Member
2635
2636 /*
2637 ** Check for validity of operation sequences
2638 */
2639 stmt_num := 9;
2640 ret_code := Valid_Op_Seqs (
2641 org_id => c3rec.OI,
2642 assy_id => c3rec.AII,
2643 alt_desg => c3rec.ABD,
2644 op_seq => c3rec.OSN,
2645 err_text => err_text);
2646
2647 if(ret_code = 0 and c3rec.BITA <> G_ProductFamily) then -- This code is for bug 1804509
2648 update bom_inventory_comps_interface
2649 set operation_lead_time_percent =
2650 (select operation_lead_time_percent
2651 FROM bom_operation_sequences bos
2652 WHERE c3rec.OSN = bos.operation_seq_num
2653 AND bos.ROUTING_SEQUENCE_ID =
2654 (select COMMON_ROUTING_SEQUENCE_ID
2655 from BOM_OPERATIONAL_ROUTINGS bor
2656 where bor.ASSEMBLY_ITEM_ID = c3rec.AII
2657 and bor.ORGANIZATION_ID = c3rec.OI
2658 and NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, NVL(c3rec.ABD, 'NONE')) = NVL(c3rec.ABD, 'NONE')
2659 AND (c3rec.ABD IS NULL
2660 OR (c3rec.ABD IS NOT NULL
2661 AND ( bor.ALTERNATE_ROUTING_DESIGNATOR = c3rec.ABD
2662 OR NOT EXISTS
2666 AND bor2.ORGANIZATION_ID = c3rec.OI
2663 (SELECT NULL
2664 FROM BOM_OPERATIONAL_ROUTINGS bor2
2665 WHERE bor2.ASSEMBLY_ITEM_ID = c3rec.AII
2667 AND bor2.ALTERNATE_ROUTING_DESIGNATOR = c3rec.ABD)))))
2668 AND bos.EFFECTIVITY_DATE < sysdate
2669 AND NVL(TRUNC(bos.DISABLE_DATE), TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
2670 WHERE transaction_id = c3rec.TI;
2671 end if; -- Code for bug 1804509 ends here
2672
2673
2674 IF (ret_code <> 0) THEN
2675 ret_code := INVPUOPI.mtl_log_interface_err(
2676 org_id => c3rec.OI,
2677 user_id => user_id,
2678 login_id => login_id,
2679 prog_appid => prog_appid,
2680 prog_id => prog_id,
2681 req_id => req_id,
2682 trans_id => c3rec.TI,
2683 error_text => err_text,
2684 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2685 msg_name => 'BOM_INVALID_OP_SEQ',
2686 err_text => err_text);
2687 UPDATE bom_inventory_comps_interface
2688 SET process_flag = 3
2689 WHERE transaction_id = c3rec.TI;
2690
2691 IF (ret_code <> 0) THEN
2692 RAISE ret_code_error;
2693 END IF;
2694 RAISE continue_loop2;
2695 END IF;
2696
2697 /*
2698 ** Planning_factor can be <>100 only if a
2699 ** assembly_item bom_item_type = planning bill or
2700 ** assembly_item bom_item_type = model/OC and component is optional or
2701 ** assembly_item bom_item_type = model/OC and component is mandatory and
2702 ** component's forecast control = Consume and derive
2703 */
2704 stmt_num := 11;
2705 IF (c3rec.PF <> 100) THEN
2706 IF (c3rec.BITA = 3 OR
2707 ((c3rec.BITA = 1 OR c3rec.BITA = 2) AND c3rec.O = 1) OR
2708 ((c3rec.BITA = 1 OR c3rec.BITA = 2) AND c3rec.O = 2 AND
2709 c3rec.AFC = 2)) THEN
2710 null;
2711 ELSE
2712 err_text := 'Planning percentage must be 100';
2713 ret_code := INVPUOPI.mtl_log_interface_err(
2714 org_id => c3rec.OI,
2715 user_id => user_id,
2716 login_id => login_id,
2717 prog_appid => prog_appid,
2718 prog_id => prog_id,
2719 req_id => req_id,
2720 trans_id => c3rec.TI,
2721 error_text => err_text,
2722 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2723 msg_name => 'BOM_PLANNING_FACTOR_ERR',
2724 err_text => err_text);
2725 UPDATE bom_inventory_comps_interface
2726 SET process_flag = 3
2727 WHERE transaction_id = c3rec.TI;
2728
2729 IF (ret_code <> 0) THEN
2730 RAISE ret_code_error;
2731 END IF;
2732 RAISE continue_loop2;
2733 END IF;
2734 END IF;
2735 /*
2736 ** If component is an ATO Standard item and the bill is a PTO Model or
2737 ** PTO Option Class, then Optional must be Yes
2738 */
2739 stmt_num := 12;
2740 IF (c3rec.BITC = 4 AND c3rec.RTOFC = 'Y' AND c3rec.BITA in (1,2)
2741 AND c3rec.PCFA = 'Y' AND c3rec.O = 2) THEN
2742 ret_code := INVPUOPI.mtl_log_interface_err(
2743 org_id => c3rec.OI,
2744 user_id => user_id,
2745 login_id => login_id,
2746 prog_appid => prog_appid,
2747 prog_id => prog_id,
2748 req_id => req_id,
2749 trans_id => c3rec.TI,
2750 error_text => err_text,
2751 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2752 msg_name => 'BOM_OPTIONAL_ERR',
2753 err_text => err_text);
2754 UPDATE bom_inventory_comps_interface
2755 SET process_flag = 3
2756 WHERE transaction_id = c3rec.TI;
2757
2758 IF (ret_code <> 0) THEN
2759 RAISE ret_code_error;
2760 END IF;
2761 RAISE continue_loop2;
2762 END IF;
2763 /*
2764 ** If planning bill then
2765 ** yield must be 1 and order entry values should be defaulted
2766 */
2767 stmt_num := 13;
2768 IF (c3rec.BITA = 3) THEN
2769 UPDATE bom_inventory_comps_interface
2770 SET component_yield_factor = 1,
2771 check_atp = 2,
2772 include_on_ship_docs = 2,
2773 so_basis = 2,
2774 mutually_exclusive_options = 2,
2775 required_to_ship = 2,
2776 required_for_revenue = 2,
2777 low_quantity = NULL,
2778 high_quantity = NULL
2779 WHERE transaction_id = c3rec.TI;
2780 END IF;
2781
2782 err_text := NULL;
2783 /*
2784 ** Validate component details
2785 */
2786 stmt_num := 14;
2787 IF (c3rec.QR not in (1,2)) THEN
2788 err_text := 'QUANTITY_RELATED must be 1 or 2';
2792 err_text := 'WIP_SUPPLY_TYPE must be 1 or 2 or 3 or 4 or 5 or 6';
2789 END IF;
2790
2791 IF (c3rec.WST is not NULL) and (c3rec.WST not in (1,2,3,4,5,6)) THEN
2793 END IF;
2794
2795 IF (c3rec.SB not in (1,2)) THEN
2796 err_text := 'SO_BASIS must be 1 or 2';
2797 END IF;
2798
2799 IF (c3rec.O not in(1,2)) THEN
2800 err_text := 'OPTIONAL must be 1 or 2';
2801 END IF;
2802
2803 IF (c3rec.MEO not in(1,2)) THEN
2804 err_text := 'MUTUALLY_EXCLUSIVE_OPTIONS must be 1 or 2';
2805 END IF;
2806
2807 IF (c3rec.ICR not in(1,2)) THEN
2808 err_text := 'INCLUDE_IN_COST_ROLLUP must be 1 or 2';
2809 END IF;
2810
2811 IF (c3rec.CATP not in(1,2)) THEN
2812 err_text := 'CHECK_ATP must be 1 or 2';
2813 END IF;
2814
2815 IF (c3rec.RTS not in(1,2)) THEN
2816 err_text := 'REQUIRED_TO_SHIP must be 1 or 2';
2817 END IF;
2818
2819 IF (c3rec.RFR not in(1,2)) THEN
2820 err_text := 'REQUIRED_FOR_REVENUE must be 1 or 2';
2821 END IF;
2822
2823 IF (c3rec.ISD not in(1,2)) THEN
2824 err_text := 'INCLUDE_ON_SHIP_DOCS must be 1 or 2';
2825 END IF;
2826
2827 /* Commented for Bug 2243418 */
2828 /*
2829 IF (c3rec.CATP = 1 and not(c3rec.AF in ('Y', 'C', 'R')
2830 AND c3rec.ACF = 'Y' and c3rec.CQ > 0)) THEN
2831 err_text := 'Component cannot have ATP check';
2832 END IF;
2833 */
2834 IF (c3rec.BITA <> 1 and c3rec.BITA <> 2 and c3rec.O = 1) THEN
2835 err_text := 'Component cannot be optional';
2836 END IF;
2837
2838 IF (c3rec.BITC <> 2 and c3rec.SB = 1) THEN
2839 err_text := 'Basis must be None';
2840 END IF;
2841
2842 IF (c3rec.RTOF = 'Y' and c3rec.RFR = 1) THEN
2843 err_text := 'An ATO item cannot be required for revenue';
2844 END IF;
2845
2846 IF (c3rec.RTOF = 'Y' and c3rec.RTS = 1) THEN
2847 err_text := 'An ATO item cannot be required to ship';
2848 END IF;
2849
2850 IF (c3rec.MEO = 1 and c3rec.BITC <>2) THEN
2851 err_text := 'Component cannot be mutually exclusive';
2852 END IF;
2853
2854 IF (c3rec.LQ > c3rec.CQ) and (c3rec.LQ is not null) THEN
2855 err_text := 'Low quantity must be less than or equal to component quantity';
2856 END IF;
2857
2858 IF (c3rec.HQ < c3rec.CQ) and (c3rec.HQ is not null) THEN
2859 err_text := 'High quantity must be greater than or equal to component quantity';
2860 END IF;
2861
2862 IF (c3rec.CYF <> 1 and c3rec.BITC = 2) THEN
2863 err_text := 'Component yield factor must be 1';
2864 END IF;
2865
2866 IF (c3rec.CYF <= 0) THEN
2867 err_text := 'Component yield factor must be greater than zero';
2868 END IF;
2869 /* Bug No : 2235454
2870 Description :
2871 BOM form is allowing the model bill as component to the assembly Item with the
2872 wip supply type is NULL. But the import process is erroring out.
2873 There is an update in the pld: BOMFMBM1.pld
2874 -- R11 onwards a Model/Option Class will not be forced to have
2875 -- a Wip_supply_type of Phantom.
2876 -- But the user would still see a warning.
2877
2878 IF (c3rec.BITC = 1 or c3rec.BITC = 2) and (c3rec.WST <> 6) THEN
2879 err_text := 'WIP supply type must be Phantom';
2880 END IF;
2881 */
2882 IF (((c3rec.CATP = 1) or (c3rec.QR = 1) or
2883 (c3rec.BITC = 2 and c3rec.PCF = 'Y'))
2884 and c3rec.CQ < 0) THEN
2885 err_text := 'Component quantity cannot be negative';
2886 END IF;
2887
2888 IF (c3rec.QR = 1) and (c3rec.CQ <> round(c3rec.CQ)) THEN
2889 err_text := 'Component quantity must be an integer value';
2890 END IF;
2891
2892 IF (c3rec.QR = 1) and (c3rec.CQ <> round(c3rec.CQ) ) THEN
2893 err_text := 'Component quantity must be an integer value';
2894 END IF;
2895
2896 /* Check if Order Entry is installed */
2897
2898 stmt_num := 15;
2899 BEGIN
2900 SELECT distinct 'I'
2901 INTO oe_install
2902 FROM fnd_product_installations
2903 WHERE application_id = 300
2904 AND status = 'I';
2905
2906 IF (oe_install = 'I') and (c3rec.CQ <> round(c3rec.CQ)) and
2907 (c3rec.PCFA = 'Y') THEN
2908 err_text := 'Component quantity must be an integer value';
2909 END IF;
2910 EXCEPTION
2911 WHEN no_data_found THEN
2912 null;
2913 END;
2914
2915 IF (err_text is not null) THEN
2916 ret_code := INVPUOPI.mtl_log_interface_err(
2917 org_id => c3rec.OI,
2918 user_id => user_id,
2919 login_id => login_id,
2920 prog_appid => prog_appid,
2921 prog_id => prog_id,
2922 req_id => req_id,
2923 trans_id => c3rec.TI,
2924 error_text => err_text,
2928 UPDATE bom_inventory_comps_interface
2925 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
2926 msg_name => 'BOM_COMPONENT_ERROR',
2927 err_text => err_text);
2929 SET process_flag = 3
2930 WHERE transaction_id = c3rec.TI;
2931
2932 IF (ret_code <> 0) THEN
2933 RAISE ret_code_error;
2934 END IF;
2935 RAISE continue_loop2;
2936 END IF;
2937
2938 /*
2939 ** Validate subinventory
2940 */
2941 stmt_num := 16;
2942 IF (c3rec.SLI is not null and c3rec.SS is null) THEN
2943 RAISE write_loc_error;
2944 END IF;
2945
2946 IF (c3rec.SLI is null and c3rec.SS is null) THEN
2947 GOTO check_quantity_related;
2948 END IF;
2949
2950 SELECT inventory_asset_flag,restrict_subinventories_code,
2951 restrict_locators_code, location_control_code
2952 INTO inv_asst, r_subinv, r_loc, loc_ctl
2953 FROM mtl_system_items
2954 WHERE inventory_item_id = c3rec.CII
2955 AND organization_id = c3rec.OI;
2956 /*
2957 ** If item locator control is null, set to 1 (no loc control)
2958 */
2959 IF (loc_ctl is null) THEN
2960 loc_ctl := 1;
2961 END IF;
2962 /*
2963 ** If subinv is not restricted and locator is, then make
2964 ** locator unrestricted
2965 */
2966
2967 IF (r_subinv = 2) and (r_loc = 1) THEN
2968 r_loc := 2;
2969 END IF;
2970 /*
2971 ** Check if subinventory is valid
2972 */
2973
2974 /*
2975 ** Get value of profile INV:EXPENSE_TO_ASSET_TRANSFER
2976 */
2977 stmt_num := 17;
2978 BOMPRFIL.bom_pr_get_profile(
2979 appl_short_name => 'INV',
2980 profile_name => 'INV:EXPENSE_TO_ASSET_TRANSFER',
2981 user_id => user_id,
2982 resp_appl_id => prog_appid,
2983 resp_id => 401,
2984 profile_value => X_expense_to_asset_transfer,
2985 return_code => ret_code,
2986 return_message => err_text);
2987
2988 IF (ret_code <> 0) THEN
2989 RETURN(ret_code);
2990 END IF;
2991
2992 IF (r_subinv = 2) THEN /* non-restricted subinventory */
2993 IF (X_expense_to_asset_transfer = 1) THEN
2994 stmt_num := 18;
2995 BEGIN
2996 SELECT locator_type
2997 INTO sub_loc_code
2998 FROM mtl_secondary_inventories
2999 WHERE secondary_inventory_name = c3rec.SS
3000 AND organization_id = c3rec.OI
3001 AND nvl(disable_date,TRUNC(c3rec.EDD)+1) >
3002 TRUNC(c3rec.EDD)
3003 AND quantity_tracked = 1;
3004 EXCEPTION
3005 WHEN no_data_found THEN
3006 RAISE write_subinv_error;
3007 END;
3008 ELSE
3009 stmt_num := 19;
3010 BEGIN
3011 SELECT locator_type
3012 INTO sub_loc_code
3013 FROM mtl_secondary_inventories
3014 WHERE secondary_inventory_name = c3rec.SS
3015 AND organization_id = c3rec.OI
3016 AND nvl(disable_date,TRUNC(c3rec.EDD)+1) >
3017 TRUNC(c3rec.EDD)
3018 AND quantity_tracked = 1
3019 AND ((inv_asst = 'Y' and asset_inventory = 1)
3020 or
3021 (inv_asst = 'N'));
3022 EXCEPTION
3023 WHEN no_data_found THEN
3024 RAISE write_subinv_error;
3025 END;
3026 END IF;
3027 ELSE /* restricted subinventory */
3028 IF (X_expense_to_asset_transfer = 1) THEN
3029 stmt_num := 20;
3030 BEGIN
3031 SELECT locator_type
3032 INTO sub_loc_code
3033 FROM mtl_secondary_inventories sub,
3034 mtl_item_sub_inventories item
3035 WHERE item.organization_id = sub.organization_id
3036 AND item.secondary_inventory =
3037 sub.secondary_inventory_name
3038 AND item.inventory_item_id = c3rec.CII
3039 AND sub.secondary_inventory_name = c3rec.SS
3040 AND sub.organization_id = c3rec.OI
3041 AND nvl(sub.disable_date,TRUNC(c3rec.EDD)+1) >
3042 TRUNC(c3rec.EDD)
3043 AND sub.quantity_tracked = 1;
3044 EXCEPTION
3045 WHEN no_data_found THEN
3046 RAISE write_subinv_error;
3047 END;
3048 ELSE
3049 stmt_num := 21;
3050 BEGIN
3051 SELECT locator_type
3052 INTO sub_loc_code
3053 FROM mtl_secondary_inventories sub,
3054 mtl_item_sub_inventories item
3055 WHERE item.organization_id = sub.organization_id
3059 AND sub.secondary_inventory_name = c3rec.SS
3056 AND item.secondary_inventory =
3057 sub.secondary_inventory_name
3058 AND item.inventory_item_id = c3rec.CII
3060 AND sub.organization_id = c3rec.OI
3061 AND nvl(sub.disable_date,TRUNC(c3rec.EDD)+1) >
3062 TRUNC(c3rec.EDD)
3063 AND sub.quantity_tracked = 1
3064 AND ((inv_asst = 'Y' and sub.asset_inventory = 1)
3065 or
3066 (inv_asst = 'N'));
3067 EXCEPTION
3068 WHEN no_data_found THEN
3069 RAISE write_subinv_error;
3070 END;
3071 END IF;
3072 END IF;
3073 /*
3074 ** Validate locator
3075 */
3076 /* Org level */
3077
3078 stmt_num := 22;
3079 SELECT stock_locator_control_code
3080 INTO org_loc
3081 FROM mtl_parameters
3082 WHERE organization_id = c3rec.OI;
3083
3084 IF (org_loc = 1) and (c3rec.SLI is not null) THEN
3085 RAISE write_loc_error;
3086 END IF;
3087
3088 IF ((org_loc = 2) or (org_loc = 3))and (c3rec.SLI is null) THEN
3089 RAISE write_loc_error;
3090 END IF;
3091
3092 IF ((org_loc = 2) or (org_loc = 3)) and (c3rec.SLI is not null) THEN
3093 IF (r_loc = 2) THEN /* non-restricted locator */
3094 stmt_num := 23;
3095 BEGIN
3096 SELECT 'loc exists'
3097 INTO dummy
3098 FROM mtl_item_locations
3099 WHERE inventory_location_id = c3rec.SLI
3100 AND organization_id = c3rec.OI
3101 AND subinventory_code = c3rec.SS
3102 AND nvl(disable_date,trunc(c3rec.EDD)+1) >
3103 trunc(c3rec.EDD);
3104 EXCEPTION
3105 WHEN no_data_found THEN
3106 RAISE write_loc_error;
3107 END;
3108 ELSE /* restricted locator */
3109 stmt_num := 24;
3110 BEGIN
3111 SELECT 'restricted loc exists'
3112 INTO dummy
3113 FROM mtl_item_locations loc,
3114 mtl_secondary_locators item
3115 WHERE loc.inventory_location_id = c3rec.SLI
3116 AND loc.organization_id = c3rec.OI
3117 AND loc.subinventory_code = c3rec.SS
3118 AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
3119 trunc(c3rec.EDD)
3120 AND loc.inventory_location_id = item.secondary_locator
3121 AND loc.organization_id = item.organization_id
3122 AND item.inventory_item_id = c3rec.CII;
3123 EXCEPTION
3124 WHEN no_data_found THEN
3125 RAISE write_loc_error;
3126 END;
3127 END IF;
3128 END IF;
3129
3130 IF (org_loc not in (1,2,3,4) and c3rec.SLI is not null) THEN
3131 RAISE write_loc_error;
3132 END IF;
3133
3134 /* Subinv level */
3135
3136 IF (org_loc = 4 and sub_loc_code = 1 and c3rec.SLI is not null) THEN
3137 RAISE write_loc_error;
3138 END IF;
3139
3140 stmt_num := 25;
3141 IF (org_loc = 4) THEN
3142 IF ((sub_loc_code = 2) or (sub_loc_code = 3))
3143 and (c3rec.SLI is null) THEN
3144 RAISE write_loc_error;
3145 END IF;
3146
3147 IF ((sub_loc_code = 2) or (sub_loc_code = 3))
3148 and (c3rec.SLI is not null) THEN
3149 /* non-restricted locator */
3150 IF (r_loc = 2) THEN
3151 BEGIN
3152 SELECT 'loc exists'
3153 INTO dummy
3154 FROM mtl_item_locations
3155 WHERE inventory_location_id = c3rec.SLI
3156 AND organization_id = c3rec.OI
3157 AND subinventory_code = c3rec.SS
3158 AND nvl(disable_date,trunc(c3rec.EDD)+1) >
3159 trunc(c3rec.EDD);
3160 EXCEPTION
3161 WHEN no_data_found THEN
3162 RAISE write_loc_error;
3163 END;
3164 /* restricted locator */
3165 ELSE
3166 stmt_num := 26;
3167 BEGIN
3168 SELECT 'restricted loc exists'
3169 INTO dummy
3170 FROM mtl_item_locations loc,
3171 mtl_secondary_locators item
3172 WHERE loc.inventory_location_id = c3rec.SLI
3173 AND loc.organization_id = c3rec.OI
3174 AND loc.subinventory_code = c3rec.SS
3175 AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
3176 trunc(c3rec.EDD)
3177 AND loc.inventory_location_id = item.secondary_locator
3178 AND loc.organization_id = item.organization_id
3179 AND item.inventory_item_id = c3rec.CII;
3180 EXCEPTION
3184 END IF;
3181 WHEN no_data_found THEN
3182 RAISE write_loc_error;
3183 END;
3185 END IF;
3186
3187 IF (sub_loc_code not in (1,2,3,5) and c3rec.SLI is not null) THEN
3188 RAISE write_loc_error;
3189 END IF;
3190 END IF;
3191
3192 /*
3193 ** Item level
3194 */
3195
3196 stmt_num := 27;
3197 IF (org_loc = 4 and sub_loc_code = 5 and loc_ctl = 1
3198 and c3rec.SLI is not null) THEN
3199 RAISE write_loc_error;
3200 END IF;
3201
3202 IF (org_loc = 4 and sub_loc_code = 5) THEN
3203 IF ((loc_ctl = 2) or (loc_ctl = 3))
3204 and (c3rec.SLI is null) THEN
3205 RAISE write_loc_error;
3206 END IF;
3207
3208 IF ((loc_ctl = 2) or (loc_ctl = 3))
3209 and (c3rec.SLI is not null) THEN
3210 /* non-restricted locator */
3211 IF (r_loc = 2) THEN
3212 BEGIN
3213 SELECT 'loc exists'
3214 INTO dummy
3215 FROM mtl_item_locations
3216 WHERE inventory_location_id = c3rec.SLI
3217 AND organization_id = c3rec.OI
3218 AND subinventory_code = c3rec.SS
3219 AND nvl(disable_date,trunc(c3rec.EDD)+1) >
3220 trunc(c3rec.EDD);
3221 EXCEPTION
3222 WHEN no_data_found THEN
3223 RAISE write_loc_error;
3224 END;
3225 ELSE
3226 /* restricted locator */
3227 stmt_num := 28;
3228 BEGIN
3229 SELECT 'restricted loc exists'
3230 INTO dummy
3231 FROM mtl_item_locations loc,
3232 mtl_secondary_locators item
3233 WHERE loc.inventory_location_id = c3rec.SLI
3234 AND loc.organization_id = c3rec.OI
3235 AND loc.subinventory_code = c3rec.SS
3236 AND nvl(loc.disable_date,trunc(c3rec.EDD)+1) >
3237 trunc(c3rec.EDD)
3238 AND loc.inventory_location_id = item.secondary_locator
3239 AND loc.organization_id = item.organization_id
3240 AND item.inventory_item_id = c3rec.CII;
3241 EXCEPTION
3242 WHEN no_data_found THEN
3243 RAISE write_loc_error;
3244 END;
3245 END IF;
3246 END IF;
3247
3248 IF (loc_ctl not in (1,2,3) and c3rec.SLI is not null) THEN
3249 raise write_loc_error;
3250 END IF;
3251 END IF;
3252
3253 /*
3254 ** If Quantity Related = Yes, then the number of reference designators
3255 ** must equal the Quantity
3256 */
3257
3258 <<check_quantity_related>>
3259 stmt_num := 12;
3260 IF (c3rec.QR = 1) THEN
3261 stmt_num := 4;
3262 SELECT count(*)
3263 INTO ref_qty
3264 FROM bom_reference_designators
3265 WHERE component_sequence_id = c3rec.CSI;
3266
3267 stmt_num := 4;
3268 SELECT count(*)
3269 INTO int_ref_qty
3270 FROM bom_ref_desgs_interface
3271 WHERE component_sequence_id = c3rec.CSI
3272 AND transaction_type = G_Insert
3273 AND process_flag = 4;
3274
3275 IF (ref_qty + int_ref_qty <> c3rec.CQ) THEN
3276 ret_code := INVPUOPI.mtl_log_interface_err(
3277 org_id => c3rec.OI,
3278 user_id => user_id,
3279 login_id => login_id,
3280 prog_appid => prog_appid,
3281 prog_id => prog_id,
3282 req_id => req_id,
3283 trans_id => c3rec.TI,
3284 error_text => err_text,
3285 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3286 msg_name => 'BOM_QUANTITY_RELATED_INVALID',
3287 err_text => err_text);
3288 UPDATE bom_inventory_comps_interface
3289 SET process_flag = 3
3290 WHERE transaction_id = c3rec.TI;
3291
3292 IF (ret_code <> 0) THEN
3293 RAISE ret_code_error;
3294 END IF;
3295 RAISE continue_loop2;
3296 END IF;
3297 END IF;
3298 /*
3299 ** Set process flag to 4
3300 */
3301 RAISE update_comp;
3302
3303 IF (mod(c3%rowcount, G_rows_to_commit) = 0) THEN
3304 COMMIT;
3305 END IF;
3306
3307 EXCEPTION
3308 WHEN Write_Loc_Error THEN
3309 ret_code := INVPUOPI.mtl_log_interface_err(
3310 org_id => org_id,
3311 user_id => user_id,
3312 login_id => login_id,
3313 prog_appid => prog_appid,
3314 prog_id => prog_id,
3315 req_id => req_id,
3319 msg_name => 'BOM_LOCATOR_INVALID',
3316 trans_id => c3rec.TI,
3317 error_text => err_text,
3318 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3320 err_text => err_text);
3321 UPDATE bom_inventory_comps_interface
3322 SET process_flag = 3
3323 WHERE transaction_id = c3rec.TI;
3324
3325 IF (ret_code <> 0) THEN
3326 RAISE ret_code_error;
3327 END IF;
3328
3329 WHEN Write_Subinv_Error THEN
3330 ret_code := INVPUOPI.mtl_log_interface_err(
3331 org_id => org_id,
3332 user_id => user_id,
3333 login_id => login_id,
3334 prog_appid => prog_appid,
3335 prog_id => prog_id,
3336 req_id => req_id,
3337 trans_id => c3rec.TI,
3338 error_text => err_text,
3339 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3340 msg_name => 'BOM_SUBINV_INVALID',
3341 err_text => err_text);
3342 UPDATE bom_inventory_comps_interface
3343 SET process_flag = 3
3344 WHERE transaction_id = c3rec.TI;
3345
3346 IF (ret_code <> 0) THEN
3347 RAISE ret_code_error;
3348 END IF;
3349
3350 WHEN Update_Comp THEN
3351 stmt_num := 29;
3352 UPDATE bom_inventory_comps_interface
3353 SET process_flag = 4
3354 WHERE transaction_id = c3rec.TI;
3355
3356 WHEN Continue_Loop2 THEN
3357 IF (mod(c3%rowcount, G_rows_to_commit) = 0) THEN
3358 COMMIT;
3359 END IF;
3360 END; -- each component
3361
3362 END LOOP; -- cursor
3363
3364
3365 /*
3366 ** FOR INSERTS - Validate
3367 */
3368
3369 /*
3370 ** Verify for uniqueness of component seq ID
3371 */
3372 FOR c1rec IN c1 LOOP
3373 BEGIN
3374 x_bill_type := null;
3375 x_assembly_item_id := null;
3376 x_valid_comp := null;
3377
3378 stmt_num := 1;
3379
3380 ret_code := Verify_Component_Count (
3381 bill_seq_id => c1rec.BSI,
3382 err_text => err_text);
3383 IF (ret_code <> 0) THEN
3384 ret_code := INVPUOPI.mtl_log_interface_err(
3385 org_id => c1rec.OI,
3386 user_id => user_id,
3387 login_id => login_id,
3388 prog_appid => prog_appid,
3389 prog_id => prog_id,
3390 req_id => req_id,
3391 trans_id => c1rec.TI,
3392 error_text => err_text,
3393 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3394 msg_name => 'BOM_COMP_COUNT_EXCEEDS_LIMIT',
3395 err_text => err_text);
3396 UPDATE bom_inventory_comps_interface
3397 SET process_flag = 3
3398 WHERE transaction_id = c1rec.TI;
3399
3400 IF (ret_code <> 0) THEN
3401 RAISE ret_code_error;
3402 END IF;
3403 RAISE continue_loop;
3404 END IF;
3405
3406 stmt_num := 1.5;
3407
3408 ret_code := Verify_unique_component (
3409 cmp_seq_id => c1rec.CSI,
3410 exist_flag => 2,
3411 err_text => err_text);
3412 IF (ret_code <> 0) THEN
3413 ret_code := INVPUOPI.mtl_log_interface_err(
3414 org_id => c1rec.OI,
3415 user_id => user_id,
3416 login_id => login_id,
3417 prog_appid => prog_appid,
3418 prog_id => prog_id,
3419 req_id => req_id,
3420 trans_id => c1rec.TI,
3421 error_text => err_text,
3422 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3423 msg_name => 'BOM_COMP_SEQ_ID_DUPLICATE',
3424 err_text => err_text);
3425 UPDATE bom_inventory_comps_interface
3426 SET process_flag = 3
3427 WHERE transaction_id = c1rec.TI;
3428
3429 IF (ret_code <> 0) THEN
3430 RAISE ret_code_error;
3431 END IF;
3432 RAISE continue_loop;
3433 END IF;
3434 /*
3435 ** Verify uniqueness of bill seq id,effective date,op seq, and component item
3436 */
3437 stmt_num := 2;
3438 ret_code := Verify_Duplicate_Component (
3439 bill_seq_id => c1rec.BSI,
3440 eff_date => c1rec.ED,
3441 cmp_item_id => c1rec.CII,
3442 op_seq => c1rec.OSN,
3443 act => c1rec.A,
3444 comp_seq_id => 1,
3445 err_text => err_text);
3446 IF (ret_code <> 0) THEN
3447 ret_code := INVPUOPI.mtl_log_interface_err(
3448 org_id => c1rec.OI,
3452 prog_id => prog_id,
3449 user_id => user_id,
3450 login_id => login_id,
3451 prog_appid => prog_appid,
3453 req_id => req_id,
3454 trans_id => c1rec.TI,
3455 error_text => err_text,
3456 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3457 msg_name => 'BOM_COMPONENT_DUPLICATE',
3458 err_text => err_text);
3459 UPDATE bom_inventory_comps_interface
3460 SET process_flag = 3
3461 WHERE transaction_id = c1rec.TI;
3462
3463 IF (ret_code <> 0) THEN
3464 RAISE ret_code_error;
3465 END IF;
3466 RAISE continue_loop;
3467 END IF;
3468
3469 /*
3470 ** Make sure there is no overlapping components
3471 */
3472 stmt_num := 4;
3473 IF (c1rec.ID is not null) THEN
3474 ret_code := Verify_Overlaps (
3475 bom_id => c1rec.BSI,
3476 op_num => c1rec.OSN,
3477 cmp_id => c1rec.CII,
3478 eff_date => c1rec.ED,
3479 dis_date => c1rec.DD,
3480 act => c1rec.A,
3481 comp_seq_id => 1,
3482 err_text => err_text);
3483 IF (ret_code <> 0) THEN
3484 ret_code := INVPUOPI.mtl_log_interface_err(
3485 org_id => c1rec.OI,
3486 user_id => user_id,
3487 login_id => login_id,
3488 prog_appid => prog_appid,
3489 prog_id => prog_id,
3490 req_id => req_id,
3491 trans_id => c1rec.TI,
3492 error_text => err_text,
3493 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3494 msg_name => 'BOM_IMPL_COMP_OVERLAP',
3495 err_text => err_text);
3496 UPDATE bom_inventory_comps_interface
3497 SET process_flag = 3
3498 WHERE transaction_id = c1rec.TI;
3499
3500 IF (ret_code <> 0) THEN
3501 RAISE ret_code_error;
3502 END IF;
3503 RAISE continue_loop;
3504 END IF;
3505 END IF;
3506 /*
3507 ** Effectivity date check
3508 */
3509 stmt_num := 4.1;
3510 IF (to_date(c1rec.ED,'YYYY/MM/DD HH24:MI:SS') >
3511 to_date(c1rec.DD,'YYYY/MM/DD HH24:MI:SS')) THEN
3512 ret_code := INVPUOPI.mtl_log_interface_err(
3513 org_id => c1rec.OI,
3514 user_id => user_id,
3515 login_id => login_id,
3516 prog_appid => prog_appid,
3517 prog_id => prog_id,
3518 req_id => req_id,
3519 trans_id => c1rec.TI,
3520 error_text => err_text,
3521 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3522 msg_name => 'BOM_EFFECTIVE_DATE_ERR',
3523 err_text => err_text);
3524 UPDATE bom_inventory_comps_interface
3525 SET process_flag = 3
3526 WHERE transaction_id = c1rec.TI;
3527
3528 IF (ret_code <> 0) THEN
3529 RAISE ret_code_error;
3530 END IF;
3531 RAISE continue_loop;
3532 END IF;
3533 /*
3534 ** Check if Member of a Product Family
3535 */
3536
3537 stmt_num := 4.2;
3538 DECLARE
3539 CURSOR GetBOMItemType IS
3540 SELECT bom_item_type, assembly_item_id
3541 FROM mtl_system_items msi,
3542 bom_bill_of_materials bom
3543 WHERE msi.organization_id = bom.organization_id
3544 AND msi.inventory_item_id = bom.assembly_item_id
3545 AND bom.bill_sequence_id = c1rec.BSI;
3546 BEGIN
3547 FOR c1 IN GetBOMItemType LOOP
3548 x_bill_type := c1.bom_item_type;
3549 x_assembly_item_id := c1.assembly_item_id;
3550 END LOOP;
3551
3552 IF (x_bill_type is null) THEN
3553 ret_code := INVPUOPI.mtl_log_interface_err(
3554 org_id => NULL,
3555 user_id => user_id,
3556 login_id => login_id,
3557 prog_appid => prog_appid,
3558 prog_id => prog_id,
3559 req_id => req_id,
3560 trans_id => c1rec.TI,
3561 error_text => err_text,
3562 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3563 msg_name => 'BOM_ASSY_ITEM_MISSING',
3564 err_text => err_text);
3565 UPDATE bom_bill_of_mtls_interface
3566 SET process_flag = 3
3567 WHERE transaction_id = c1rec.TI;
3568
3569 IF (ret_code <> 0) THEN
3570 RETURN(ret_code);
3571 END IF;
3572 RAISE continue_loop;
3573 ELSIF (x_bill_type = G_ProductFamily) THEN
3574 /*
3575 ** Planning factor cannot be zero for Members
3576 */
3577
3578 /***************** This check is not required *****************************
3579 * Fixed Bug: 916428
3580 *
3581 IF (c1rec.PF = 0) THEN
3582 ret_code := INVPUOPI.mtl_log_interface_err(
3583 org_id => c1rec.OI,
3584 user_id => user_id,
3585 login_id => login_id,
3586 prog_appid => prog_appid,
3587 prog_id => prog_id,
3588 req_id => req_id,
3589 trans_id => c1rec.TI,
3590 error_text => err_text,
3591 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3592 msg_name => 'BOM_PLANNING_FACTOR',
3593 err_text => err_text);
3594 UPDATE bom_inventory_comps_interface
3595 SET process_flag = 3
3596 WHERE transaction_id = c1rec.TI;
3597
3598 IF (ret_code <> 0) THEN
3599 RAISE ret_code_error;
3600 END IF;
3601 RAISE continue_loop;
3602 END IF;
3603 *
3604 *****************************************************************************/
3605
3606 /*
3607 ** Check Member item attributes
3608 */
3609 stmt_num := 4.3;
3610 DECLARE
3611 CURSOR ItemIsValid IS
3612 SELECT inventory_item_id
3613 FROM mtl_system_items
3614 WHERE organization_id = c1rec.OI
3615 AND inventory_item_id = c1rec.CII
3616 AND bom_enabled_flag = 'Y'
3617 AND eng_item_flag = 'N'
3618 AND bom_item_type <> G_ProductFamily
3619 AND product_family_item_id is null
3620 AND c1rec.CII <> x_assembly_item_id;
3621 BEGIN
3622 FOR c1 IN ItemIsValid LOOP
3623 x_valid_comp := c1.inventory_item_id;
3624 END LOOP;
3625
3626 IF (x_valid_comp is null) THEN
3627 ret_code := INVPUOPI.mtl_log_interface_err(
3628 org_id => NULL,
3629 user_id => user_id,
3630 login_id => login_id,
3631 prog_appid => prog_appid,
3632 prog_id => prog_id,
3633 req_id => req_id,
3634 trans_id => c1rec.TI,
3635 error_text => err_text,
3636 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3637 msg_name => 'BOM_INVALID_ITEM_ATTRIBUTES',
3638 err_text => err_text);
3639 UPDATE bom_inventory_comps_interface
3640 SET process_flag = 3
3641 WHERE transaction_id = c1rec.TI;
3642
3643 IF (ret_code <> 0) THEN
3644 RETURN(ret_code);
3645 END IF;
3646 RAISE continue_loop;
3647 END IF; -- Check if member is invalid
3648 END;
3649 RAISE Update_Comp;
3650 END IF; -- Checking BOM Item Type of parent
3651 END; -- Check for Product Family Member
3652
3653 /*
3654 ** Verify that the bill is not a common bill. If so it cannot have
3655 ** components
3656 */
3657 stmt_num := 5;
3658 BEGIN
3659 SELECT 'Is pointing to a common'
3660 INTO dummy
3661 FROM bom_bill_of_materials
3662 WHERE bill_sequence_id = c1rec.BSI
3663 AND common_bill_sequence_id <> c1rec.BSI;
3664
3665 ret_code := INVPUOPI.mtl_log_interface_err(
3666 org_id => c1rec.OI,
3667 user_id => user_id,
3668 login_id => login_id,
3669 prog_appid => prog_appid,
3670 prog_id => prog_id,
3671 req_id => req_id,
3672 trans_id => c1rec.TI,
3673 error_text => err_text,
3674 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3675 msg_name => 'BOM_COMMON_COMP',
3676 err_text => err_text);
3677 UPDATE bom_inventory_comps_interface
3678 SET process_flag = 3
3679 WHERE transaction_id = c1rec.TI;
3680
3681 IF (ret_code <> 0) THEN
3682 RAISE ret_code_error;
3683 END IF;
3684 RAISE continue_loop;
3685 EXCEPTION
3686 WHEN no_data_found THEN
3687 null;
3688 END;
3689
3690 /*
3691 ** If bill is a Common for other bills, then make sure Component Item
3692 ** exists in those orgs
3693 */
3694 stmt_num := 6;
3695 BEGIN
3696 SELECT 1
3697 INTO dummy
3698 FROM bom_bill_of_materials bbom
3699 WHERE bbom.common_bill_sequence_id = c1rec.BSI
3700 AND bbom.organization_id <> bbom.common_organization_id
3704 WHERE msi.organization_id = bbom.organization_id
3701 AND not exists
3702 (SELECT null
3703 FROM mtl_system_items msi
3705 AND msi.inventory_item_id = c1rec.CII
3706 AND msi.bom_enabled_flag = 'Y'
3707 AND ((bbom.assembly_type = 2)
3708 OR
3709 (bbom.assembly_type = 1
3710 AND msi.eng_item_flag = 'N')))
3711 AND rownum < 2;
3712
3713 err_text := 'Component item does not exist in common organizations or has incorrect attributes';
3714 ret_code := INVPUOPI.mtl_log_interface_err(
3715 org_id => c1rec.OI,
3716 user_id => user_id,
3717 login_id => login_id,
3718 prog_appid => prog_appid,
3719 prog_id => prog_id,
3720 req_id => req_id,
3721 trans_id => c1rec.TI,
3722 error_text => err_text,
3723 tbl_name => 'BOM_INV_COMPS_INTERFACE',
3724 msg_name => 'BOM_COMP_COMMON_INVALID',
3725 err_text => err_text);
3726 UPDATE bom_inventory_comps_interface
3727 SET process_flag = 3
3728 WHERE transaction_id = c1rec.TI;
3729
3730 IF (ret_code <> 0) THEN
3731 RAISE ret_code_error;
3732 END IF;
3733 RAISE continue_loop;
3734 EXCEPTION
3735 WHEN no_data_found THEN
3736 null;
3737 END;
3738
3739 /*
3740 ** Verify the validity of item attributes
3741 */
3742 stmt_num := 7;
3743 DECLARE
3744 CURSOR CheckBOM IS
3745 SELECT assembly_type
3746 FROM bom_bill_of_materials
3747 WHERE bill_sequence_id = c1rec.BSI;
3748 BEGIN
3749 eng_bill := null;
3750 FOR X_bill IN CheckBOM LOOP
3751 eng_bill := X_Bill.assembly_type;
3752 END LOOP;
3753 END;
3754
3755 stmt_num := 8;
3756 ret_code := Verify_Item_Attributes (
3757 org_id => c1rec.OI,
3758 cmp_id => c1rec.CII,
3759 eng_bill => eng_bill,
3760 assy_id => c1rec.AII,
3761 err_text => err_text);
3762 IF (ret_code <> 0) THEN
3763 ret_code := INVPUOPI.mtl_log_interface_err(
3764 org_id => c1rec.OI,
3765 user_id => user_id,
3766 login_id => login_id,
3767 prog_appid => prog_appid,
3768 prog_id => prog_id,
3769 req_id => req_id,
3770 trans_id => c1rec.TI,
3771 error_text => err_text,
3772 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3773 msg_name => 'BOM_INVALID_ITEM_ATTRIBUTES',
3774 err_text => err_text);
3775 UPDATE bom_inventory_comps_interface
3776 SET process_flag = 3
3777 WHERE transaction_id = c1rec.TI;
3778
3779 IF (ret_code <> 0) THEN
3780 RAISE ret_code_error;
3781 END IF;
3782 RAISE continue_loop;
3783 END IF;
3784 /*
3785 ** Check for validity of operation sequences
3786 */
3787 stmt_num := 9;
3788 ret_code := Valid_Op_Seqs (
3789 org_id => c1rec.OI,
3790 assy_id => c1rec.AII,
3791 alt_desg => c1rec.ABD,
3792 op_seq => c1rec.OSN,
3793 err_text => err_text);
3794 --bugFix 1851537 Begin
3795 if (ret_code=0) then
3796 update bom_inventory_comps_interface
3797 set operation_lead_time_percent =
3798 (select operation_lead_time_percent
3799 FROM bom_operation_sequences bos
3800 WHERE c1rec.OSN = bos.operation_seq_num
3801 AND bos.ROUTING_SEQUENCE_ID =
3802 (select COMMON_ROUTING_SEQUENCE_ID
3803 from BOM_OPERATIONAL_ROUTINGS bor
3804 where bor.ASSEMBLY_ITEM_ID = c1rec.AII
3805 and bor.ORGANIZATION_ID = c1rec.OI
3806 and NVL(bor.ALTERNATE_ROUTING_DESIGNATOR, NVL(c1rec.ABD, 'NONE')) = NVL(c1rec.ABD,'NONE')
3807 AND (c1rec.ABD IS NULL
3808 OR (c1rec.ABD IS NOT NULL
3809 AND ( bor.ALTERNATE_ROUTING_DESIGNATOR = c1rec.ABD
3810 OR NOT EXISTS
3811 (SELECT NULL
3812 FROM BOM_OPERATIONAL_ROUTINGS bor2
3813 WHERE bor2.ASSEMBLY_ITEM_ID = c1rec.AII
3814 AND bor2.ORGANIZATION_ID = c1rec.OI
3815 AND bor2.ALTERNATE_ROUTING_DESIGNATOR = c1rec.
3816 ABD)))))
3817 AND bos.EFFECTIVITY_DATE < sysdate
3818 AND NVL(TRUNC(bos.DISABLE_DATE), TRUNC(SYSDATE)+1) > TRUNC(SYSDATE))
3819 WHERE transaction_id = c1rec.TI;
3820 end if;
3821 --bugFix 1851537 End
3822
3826 user_id => user_id,
3823 IF (ret_code <> 0) THEN
3824 ret_code := INVPUOPI.mtl_log_interface_err(
3825 org_id => c1rec.OI,
3827 login_id => login_id,
3828 prog_appid => prog_appid,
3829 prog_id => prog_id,
3830 req_id => req_id,
3831 trans_id => c1rec.TI,
3832 error_text => err_text,
3833 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3834 msg_name => 'BOM_INVALID_OP_SEQ',
3835 err_text => err_text);
3836 UPDATE bom_inventory_comps_interface
3837 SET process_flag = 3
3838 WHERE transaction_id = c1rec.TI;
3839
3840 IF (ret_code <> 0) THEN
3841 RAISE ret_code_error;
3842 END IF;
3843 RAISE continue_loop;
3844 END IF;
3845
3846 /*
3847 ** Planning_factor can be <>100 only if a
3848 ** assembly_item bom_item_type = planning bill or
3849 ** assembly_item bom_item_type = model/OC and component is optional or
3850 ** assembly_item bom_item_type = model/OC and component is mandatory and
3851 ** component's forecast control = Consume and derive
3852 */
3853 stmt_num := 11;
3854 IF (c1rec.PF <> 100) THEN
3855 IF (c1rec.BITA = 3 OR
3856 ((c1rec.BITA = 1 OR c1rec.BITA = 2) AND c1rec.O = 1) OR
3857 ((c1rec.BITA = 1 OR c1rec.BITA = 2) AND c1rec.O = 2 AND
3858 c1rec.AFC = 2)) THEN
3859 null;
3860 ELSE
3861 err_text := 'Planning percentage must be 100';
3862 ret_code := INVPUOPI.mtl_log_interface_err(
3863 org_id => c1rec.OI,
3864 user_id => user_id,
3865 login_id => login_id,
3866 prog_appid => prog_appid,
3867 prog_id => prog_id,
3868 req_id => req_id,
3869 trans_id => c1rec.TI,
3870 error_text => err_text,
3871 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3872 msg_name => 'BOM_PLANNING_FACTOR_ERR',
3873 err_text => err_text);
3874 UPDATE bom_inventory_comps_interface
3875 SET process_flag = 3
3876 WHERE transaction_id = c1rec.TI;
3877
3878 IF (ret_code <> 0) THEN
3879 RAISE ret_code_error;
3880 END IF;
3881 RAISE continue_loop;
3882 END IF;
3883 END IF;
3884
3885 /*
3886 ** Verfify Change_Notice
3887 */
3888 stmt_num := 11.5;
3889
3890 If (c1rec.CN is not NULL) THEN
3891 BEGIN
3892 SELECT 1
3893 INTO dummy
3894 FROM eng_engineering_changes
3895 WHERE organization_id = c1rec.OI
3896 AND change_notice = c1rec.CN;
3897 EXCEPTION
3898 WHEN no_data_found THEN
3899 ret_code := INVPUOPI.mtl_log_interface_err(
3900 org_id => c1rec.OI,
3901 user_id => user_id,
3902 login_id => login_id,
3903 prog_appid => prog_appid,
3904 prog_id => prog_id,
3905 req_id => req_id,
3906 trans_id => c1rec.TI,
3907 error_text => err_text,
3908 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3909 msg_name => 'ENG_PARENTECO_NOT_EXIST',
3910 err_text => err_text);
3911 UPDATE bom_inventory_comps_interface
3912 SET process_flag = 3
3913 WHERE transaction_id = c1rec.TI;
3914
3915 IF (ret_code <> 0) THEN
3916 RAISE ret_code_error;
3917 END IF;
3918 RAISE continue_loop;
3919 END;
3920 END IF;
3921 /*
3922
3923 /*
3924 ** If component is an ATO Standard item and the bill is a PTO Model or
3925 ** PTO Option Class, then Optional must be Yes
3926 */
3927 stmt_num := 12;
3928 IF (c1rec.BITC = 4 AND c1rec.RTOFC = 'Y' AND c1rec.BITA in (1,2)
3929 AND c1rec.PCFA = 'Y' AND c1rec.O = 2) THEN
3930 ret_code := INVPUOPI.mtl_log_interface_err(
3931 org_id => c1rec.OI,
3932 user_id => user_id,
3933 login_id => login_id,
3934 prog_appid => prog_appid,
3935 prog_id => prog_id,
3936 req_id => req_id,
3937 trans_id => c1rec.TI,
3938 error_text => err_text,
3939 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
3940 msg_name => 'BOM_OPTIONAL_ERR',
3941 err_text => err_text);
3942 UPDATE bom_inventory_comps_interface
3943 SET process_flag = 3
3944 WHERE transaction_id = c1rec.TI;
3945
3949 RAISE continue_loop;
3946 IF (ret_code <> 0) THEN
3947 RAISE ret_code_error;
3948 END IF;
3950 END IF;
3951 /*
3952 ** If planning bill then
3953 ** yield must be 1 and order entry values should be defaulted
3954 */
3955 stmt_num := 13;
3956 IF (c1rec.BITA = 3) THEN
3957 UPDATE bom_inventory_comps_interface
3958 SET component_yield_factor = 1,
3959 check_atp = 2,
3960 include_on_ship_docs = 2,
3961 so_basis = 2,
3962 mutually_exclusive_options = 2,
3963 required_to_ship = 2,
3964 required_for_revenue = 2,
3965 low_quantity = NULL,
3966 high_quantity = NULL
3967 WHERE transaction_id = c1rec.TI;
3968 END IF;
3969
3970 err_text := NULL;
3971 /*
3972 ** Validate component details
3973 */
3974 stmt_num := 14;
3975 IF (c1rec.QR not in (1,2)) THEN
3976 err_text := 'QUANTITY_RELATED must be 1 or 2';
3977 END IF;
3978
3979 IF (c1rec.WST is not null) and (c1rec.WST not in (1,2,3,4,5,6)) THEN
3980 err_text := 'WIP_SUPPLY_TYPE must be 1 or 2 or 3 or 4 or 5 or 6';
3981 END IF;
3982
3983 IF (c1rec.SB not in (1,2)) THEN
3984 err_text := 'SO_BASIS must be 1 or 2';
3985 END IF;
3986
3987 IF (c1rec.O not in(1,2)) THEN
3988 err_text := 'OPTIONAL must be 1 or 2';
3989 END IF;
3990
3991 IF (c1rec.MEO not in(1,2)) THEN
3992 err_text := 'MUTUALLY_EXCLUSIVE_OPTIONS must be 1 or 2';
3993 END IF;
3994
3995 IF (c1rec.ICR not in(1,2)) THEN
3996 err_text := 'INCLUDE_IN_COST_ROLLUP must be 1 or 2';
3997 END IF;
3998
3999 IF (c1rec.CATP not in(1,2)) THEN
4000 err_text := 'CHECK_ATP must be 1 or 2';
4001 END IF;
4002
4003 IF (c1rec.RTS not in(1,2)) THEN
4004 err_text := 'REQUIRED_TO_SHIP must be 1 or 2';
4005 END IF;
4006
4007 IF (c1rec.RFR not in(1,2)) THEN
4008 err_text := 'REQUIRED_FOR_REVENUE must be 1 or 2';
4009 END IF;
4010
4011 IF (c1rec.ISD not in(1,2)) THEN
4012 err_text := 'INCLUDE_ON_SHIP_DOCS must be 1 or 2';
4013 END IF;
4014 /* Commented for Bug 2243418 */
4015 /*
4016 IF (c1rec.CATP = 1 and not(c1rec.AF in ( 'Y', 'C', 'R')
4017 AND c1rec.ACF = 'Y' and c1rec.CQ > 0)) THEN
4018 err_text := 'Component cannot have ATP check';
4019 END IF;
4020 */
4021 IF (c1rec.BITA <> 1 and c1rec.BITA <> 2 and c1rec.O = 1) THEN
4022 err_text := 'Component cannot be optional';
4023 END IF;
4024
4025 IF (c1rec.BITC <> 2 and c1rec.SB = 1) THEN
4026 err_text := 'Basis must be None';
4027 END IF;
4028
4029 IF (c1rec.RTOF = 'Y' and c1rec.RFR = 1) THEN
4030 err_text := 'An ATO item cannot be required for revenue';
4031 END IF;
4032
4033 IF (c1rec.RTOF = 'Y' and c1rec.RTS = 1) THEN
4034 err_text := 'An ATO item cannot be required to ship';
4035 END IF;
4036
4037 IF (c1rec.MEO = 1 and c1rec.BITC <>2) THEN
4038 err_text := 'Component cannot be mutually exclusive';
4039 END IF;
4040
4041 IF (c1rec.LQ > c1rec.CQ) and (c1rec.LQ is not null) THEN
4042 err_text :=
4043 'Low quantity must be less than or equal to component quantity';
4044 END IF;
4045
4046 IF (c1rec.HQ < c1rec.CQ) and (c1rec.HQ is not null) THEN
4047 err_text :=
4048 'High quantity must be greater than or equal to component quantity';
4049 END IF;
4050
4051 IF (c1rec.CYF <> 1 and c1rec.BITC = 2) THEN
4052 err_text := 'Component yield factor must be 1';
4053 END IF;
4054
4055 IF (c1rec.CYF <= 0) THEN
4056 err_text := 'Component yield factor must be greater than zero';
4057 END IF;
4058 /*
4059 Bug No : 2235454
4060 Description :
4061 BOM form is allowing the model bill as component to the assembly Item with the
4062 wip supply type is NULL. But the import process is erroring out.
4063 There is an update in the pld: BOMFMBM1.pld
4064 -- R11 onwards a Model/Option Class will not be forced to have
4065 -- a Wip_supply_type of Phantom.
4066 -- But the user would still see a warning.
4067
4068 IF (c1rec.BITC = 1 or c1rec.BITC = 2) and (c1rec.WST <> 6) THEN
4069 err_text := 'WIP supply type must be Phantom';
4070 END IF;
4071 */
4072 IF (((c1rec.CATP = 1) or (c1rec.QR = 1) or
4073 (c1rec.BITC = 2 and c1rec.PCF = 'Y'))
4074 and c1rec.CQ < 0) THEN
4075 err_text := 'Component quantity cannot be negative';
4076 END IF;
4077
4078 IF (c1rec.QR = 1) and (c1rec.CQ <> round(c1rec.CQ)) THEN
4079 err_text := 'Component quantity must be an integer value';
4080 END IF;
4081
4082 IF (c1rec.QR = 1) and (c1rec.CQ <> round(c1rec.CQ) ) THEN
4083 err_text := 'Component quantity must be an integer value';
4087
4084 END IF;
4085
4086 /* Check if Order Entry is installed */
4088 stmt_num := 15;
4089 BEGIN
4090 SELECT distinct 'I'
4091 INTO oe_install
4092 FROM fnd_product_installations
4093 WHERE application_id = 300
4094 AND status = 'I';
4095
4096 IF (oe_install = 'I') and (c1rec.CQ <> round(c1rec.CQ)) and
4097 (c1rec.PCFA = 'Y') THEN
4098 err_text := 'Component quantity must be an integer value';
4099 END IF;
4100 EXCEPTION
4101 WHEN no_data_found THEN
4102 null;
4103 END;
4104
4105 IF (err_text is not null) THEN
4106 ret_code := INVPUOPI.mtl_log_interface_err(
4107 org_id => c1rec.OI,
4108 user_id => user_id,
4109 login_id => login_id,
4110 prog_appid => prog_appid,
4111 prog_id => prog_id,
4112 req_id => req_id,
4113 trans_id => c1rec.TI,
4114 error_text => err_text,
4115 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
4116 msg_name => 'BOM_COMPONENT_ERROR',
4117 err_text => err_text);
4118 UPDATE bom_inventory_comps_interface
4119 SET process_flag = 3
4120 WHERE transaction_id = c1rec.TI;
4121
4122 IF (ret_code <> 0) THEN
4123 RAISE ret_code_error;
4124 END IF;
4125 RAISE continue_loop;
4126 END IF;
4127
4128 /*
4129 ** Validate subinventory
4130 */
4131 stmt_num := 16;
4132 IF (c1rec.SLI is not null and c1rec.SS is null) THEN
4133 RAISE write_loc_error;
4134 END IF;
4135
4136 IF (c1rec.SLI is null and c1rec.SS is null) THEN
4137 RAISE update_comp;
4138 END IF;
4139
4140 SELECT inventory_asset_flag,restrict_subinventories_code,
4141 restrict_locators_code, location_control_code
4142 INTO inv_asst, r_subinv, r_loc, loc_ctl
4143 FROM mtl_system_items
4144 WHERE inventory_item_id = c1rec.CII
4145 AND organization_id = c1rec.OI;
4146 /*
4147 ** If item locator control is null, set to 1 (no loc control)
4148 */
4149 IF (loc_ctl is null) THEN
4150 loc_ctl := 1;
4151 END IF;
4152 /*
4153 /*
4154 ** If subinv is not restricted and locator is, then make
4155 ** locator unrestricted
4156 */
4157
4158 IF (r_subinv = 2) and (r_loc = 1) THEN
4159 r_loc := 2;
4160 END IF;
4161 /*
4162 ** Check if subinventory is valid
4163 */
4164
4165 /*
4166 ** Get value of profile INV:EXPENSE_TO_ASSET_TRANSFER
4167 */
4168 stmt_num := 17;
4169 BOMPRFIL.bom_pr_get_profile(
4170 appl_short_name => 'INV',
4171 profile_name => 'INV:EXPENSE_TO_ASSET_TRANSFER',
4172 user_id => user_id,
4173 resp_appl_id => prog_appid,
4174 resp_id => 401,
4175 profile_value => X_expense_to_asset_transfer,
4176 return_code => ret_code,
4177 return_message => err_text);
4178 IF (ret_code <> 0) THEN
4179 RETURN(ret_code);
4180 END IF;
4181
4182 IF (r_subinv = 2) THEN /* non-restricted subinventory */
4183 IF (X_expense_to_asset_transfer = 1) THEN
4184 stmt_num := 18;
4185 BEGIN
4186 SELECT locator_type
4187 INTO sub_loc_code
4188 FROM mtl_secondary_inventories
4189 WHERE secondary_inventory_name = c1rec.SS
4190 AND organization_id = c1rec.OI
4191 AND nvl(disable_date,TRUNC(c1rec.EDD)+1) >
4192 TRUNC(c1rec.EDD)
4193 AND quantity_tracked = 1;
4194 EXCEPTION
4195 WHEN no_data_found THEN
4196 RAISE write_subinv_error;
4197 END;
4198 ELSE
4199 stmt_num := 19;
4200 BEGIN
4201 SELECT locator_type
4202 INTO sub_loc_code
4203 FROM mtl_secondary_inventories
4204 WHERE secondary_inventory_name = c1rec.SS
4205 AND organization_id = c1rec.OI
4206 AND nvl(disable_date,TRUNC(c1rec.EDD)+1) >
4207 TRUNC(c1rec.EDD)
4208 AND quantity_tracked = 1
4209 AND ((inv_asst = 'Y' and asset_inventory = 1)
4210 or
4211 (inv_asst = 'N'));
4212 EXCEPTION
4213 WHEN no_data_found THEN
4214 RAISE write_subinv_error;
4215 END;
4216 END IF;
4217 ELSE /* restricted subinventory */
4218 IF (X_expense_to_asset_transfer = 1) THEN
4219 stmt_num := 20;
4220 BEGIN
4221 SELECT locator_type
4222 INTO sub_loc_code
4226 AND item.secondary_inventory =
4223 FROM mtl_secondary_inventories sub,
4224 mtl_item_sub_inventories item
4225 WHERE item.organization_id = sub.organization_id
4227 sub.secondary_inventory_name
4228 AND item.inventory_item_id = c1rec.CII
4229 AND sub.secondary_inventory_name = c1rec.SS
4230 AND sub.organization_id = c1rec.OI
4231 AND nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
4232 TRUNC(c1rec.EDD)
4233 AND sub.quantity_tracked = 1;
4234 EXCEPTION
4235 WHEN no_data_found THEN
4236 RAISE write_subinv_error;
4237 END;
4238 ELSE
4239 stmt_num := 21;
4240 BEGIN
4241 SELECT locator_type
4242 INTO sub_loc_code
4243 FROM mtl_secondary_inventories sub,
4244 mtl_item_sub_inventories item
4245 WHERE item.organization_id = sub.organization_id
4246 AND item.secondary_inventory =
4247 sub.secondary_inventory_name
4248 AND item.inventory_item_id = c1rec.CII
4249 AND sub.secondary_inventory_name = c1rec.SS
4250 AND sub.organization_id = c1rec.OI
4251 AND nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
4252 TRUNC(c1rec.EDD)
4253 AND sub.quantity_tracked = 1
4254 AND ((inv_asst = 'Y' and sub.asset_inventory = 1)
4255 or
4256 (inv_asst = 'N'));
4257 EXCEPTION
4258 WHEN no_data_found THEN
4259 RAISE write_subinv_error;
4260 END;
4261 END IF;
4262 END IF;
4263 /*
4264 ** Validate locator
4265 */
4266 /* Org level */
4267 stmt_num := 22;
4268 SELECT stock_locator_control_code
4269 INTO org_loc
4270 FROM mtl_parameters
4271 WHERE organization_id = c1rec.OI;
4272
4273 IF (org_loc = 1) and (c1rec.SLI is not null) THEN
4274 RAISE write_loc_error;
4275 END IF;
4276
4277 IF ((org_loc = 2) or (org_loc = 3))and (c1rec.SLI is null) THEN
4278 RAISE write_loc_error;
4279 END IF;
4280
4281 IF ((org_loc = 2) or (org_loc = 3)) and (c1rec.SLI is not null) THEN
4282 IF (r_loc = 2) THEN /* non-restricted locator */
4283 stmt_num := 23;
4284 BEGIN
4285 SELECT 'loc exists'
4286 INTO dummy
4287 FROM mtl_item_locations
4288 WHERE inventory_location_id = c1rec.SLI
4289 AND organization_id = c1rec.OI
4290 AND subinventory_code = c1rec.SS
4291 AND nvl(disable_date,trunc(c1rec.EDD)+1) >
4292 trunc(c1rec.EDD);
4293 EXCEPTION
4294 WHEN no_data_found THEN
4295 RAISE write_loc_error;
4296 END;
4297 ELSE /* restricted locator */
4298 stmt_num := 24;
4299 BEGIN
4300 SELECT 'restricted loc exists'
4301 INTO dummy
4302 FROM mtl_item_locations loc,
4303 mtl_secondary_locators item
4304 WHERE loc.inventory_location_id = c1rec.SLI
4305 AND loc.organization_id = c1rec.OI
4306 AND loc.subinventory_code = c1rec.SS
4307 AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
4308 trunc(c1rec.EDD)
4309 AND loc.inventory_location_id = item.secondary_locator
4310 AND loc.organization_id = item.organization_id
4311 AND item.inventory_item_id = c1rec.CII;
4312 EXCEPTION
4313 WHEN no_data_found THEN
4314 RAISE write_loc_error;
4315 END;
4316 END IF;
4317 END IF;
4318
4319 IF (org_loc not in (1,2,3,4) and c1rec.SLI is not null) THEN
4320 RAISE write_loc_error;
4321 END IF;
4322
4323 /* Subinv level */
4324 IF (org_loc = 4 and sub_loc_code = 1 and c1rec.SLI is not null) THEN
4325 RAISE write_loc_error;
4326 END IF;
4327
4328 stmt_num := 25;
4329 IF (org_loc = 4) THEN
4330 IF ((sub_loc_code = 2) or (sub_loc_code = 3))
4331 and (c1rec.SLI is null) THEN
4332 RAISE write_loc_error;
4333 END IF;
4334
4335 IF ((sub_loc_code = 2) or (sub_loc_code = 3))
4336 and (c1rec.SLI is not null) THEN
4337 /* non-restricted locator */
4338 IF (r_loc = 2) THEN
4339 BEGIN
4340 SELECT 'loc exists'
4341 INTO dummy
4342 FROM mtl_item_locations
4343 WHERE inventory_location_id = c1rec.SLI
4344 AND organization_id = c1rec.OI
4348 EXCEPTION
4345 AND subinventory_code = c1rec.SS
4346 AND nvl(disable_date,trunc(c1rec.EDD)+1) >
4347 trunc(c1rec.EDD);
4349 WHEN no_data_found THEN
4350 RAISE write_loc_error;
4351 END;
4352 /* restricted locator */
4353 ELSE
4354 stmt_num := 26;
4355 BEGIN
4356 SELECT 'restricted loc exists'
4357 INTO dummy
4358 FROM mtl_item_locations loc,
4359 mtl_secondary_locators item
4360 WHERE loc.inventory_location_id = c1rec.SLI
4361 AND loc.organization_id = c1rec.OI
4362 AND loc.subinventory_code = c1rec.SS
4363 AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
4364 trunc(c1rec.EDD)
4365 AND loc.inventory_location_id = item.secondary_locator
4366 AND loc.organization_id = item.organization_id
4367 AND item.inventory_item_id = c1rec.CII;
4368 EXCEPTION
4369 WHEN no_data_found THEN
4370 RAISE write_loc_error;
4371 END;
4372 END IF;
4373 END IF;
4374
4375 IF (sub_loc_code not in (1,2,3,5) and c1rec.SLI is not null) THEN
4376 RAISE write_loc_error;
4377 END IF;
4378 END IF;
4379
4380 /*
4381 ** Item level
4382 */
4383 stmt_num := 27;
4384 IF (org_loc = 4 and sub_loc_code = 5 and loc_ctl = 1
4385 and c1rec.SLI is not null) THEN
4386 RAISE write_loc_error;
4387 END IF;
4388
4389 IF (org_loc = 4 and sub_loc_code = 5) THEN
4390 IF ((loc_ctl = 2) or (loc_ctl = 3))
4391 and (c1rec.SLI is null) THEN
4392 RAISE write_loc_error;
4393 END IF;
4394
4395 IF ((loc_ctl = 2) or (loc_ctl = 3))
4396 and (c1rec.SLI is not null) THEN
4397 /* non-restricted locator */
4398 IF (r_loc = 2) THEN
4399 BEGIN
4400 SELECT 'loc exists'
4401 INTO dummy
4402 FROM mtl_item_locations
4403 WHERE inventory_location_id = c1rec.SLI
4404 AND organization_id = c1rec.OI
4405 AND subinventory_code = c1rec.SS
4406 AND nvl(disable_date,trunc(c1rec.EDD)+1) >
4407 trunc(c1rec.EDD);
4408 EXCEPTION
4409 WHEN no_data_found THEN
4410 RAISE write_loc_error;
4411 END;
4412 ELSE
4413 /* restricted locator */
4414 stmt_num := 28;
4415 BEGIN
4416 SELECT 'restricted loc exists'
4417 INTO dummy
4418 FROM mtl_item_locations loc,
4419 mtl_secondary_locators item
4420 WHERE loc.inventory_location_id = c1rec.SLI
4421 AND loc.organization_id = c1rec.OI
4422 AND loc.subinventory_code = c1rec.SS
4423 AND nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
4424 trunc(c1rec.EDD)
4425 AND loc.inventory_location_id = item.secondary_locator
4426 AND loc.organization_id = item.organization_id
4427 AND item.inventory_item_id = c1rec.CII;
4428 EXCEPTION
4429 WHEN no_data_found THEN
4430 RAISE write_loc_error;
4431 END;
4432 END IF;
4433 END IF;
4434
4435 IF (loc_ctl not in (1,2,3) and c1rec.SLI is not null) THEN
4436 RAISE write_loc_error;
4437 END IF;
4438 END IF;
4439
4440 RAISE update_comp;
4441
4442 IF (mod(c1%rowcount, G_rows_to_commit) = 0) THEN
4443 COMMIT;
4444 END IF;
4445
4446 EXCEPTION
4447 WHEN Write_Loc_Error THEN
4448 ret_code := INVPUOPI.mtl_log_interface_err(
4449 org_id => org_id,
4450 user_id => user_id,
4451 login_id => login_id,
4452 prog_appid => prog_appid,
4453 prog_id => prog_id,
4454 req_id => req_id,
4455 trans_id => c1rec.TI,
4456 error_text => err_text,
4457 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
4458 msg_name => 'BOM_LOCATOR_INVALID',
4459 err_text => err_text);
4460 UPDATE bom_inventory_comps_interface
4461 SET process_flag = 3
4462 WHERE transaction_id = c1rec.TI;
4463
4464 IF (ret_code <> 0) THEN
4465 RAISE ret_code_error;
4466 END IF;
4467
4468 WHEN Write_Subinv_Error THEN
4472 login_id => login_id,
4469 ret_code := INVPUOPI.mtl_log_interface_err(
4470 org_id => org_id,
4471 user_id => user_id,
4473 prog_appid => prog_appid,
4474 prog_id => prog_id,
4475 req_id => req_id,
4476 trans_id => c1rec.TI,
4477 error_text => err_text,
4478 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
4479 msg_name => 'BOM_SUBINV_INVALID',
4480 err_text => err_text);
4481 UPDATE bom_inventory_comps_interface
4482 SET process_flag = 3
4483 WHERE transaction_id = c1rec.TI;
4484
4485 IF (ret_code <> 0) THEN
4486 RAISE ret_code_error;
4487 END IF;
4488
4489 WHEN Update_Comp THEN
4490 stmt_num := 29;
4491 UPDATE bom_inventory_comps_interface
4492 SET process_flag = 4
4493 WHERE transaction_id = c1rec.TI;
4494
4495 WHEN Continue_Loop THEN
4496 IF (mod(c1%rowcount, G_rows_to_commit) = 0) THEN
4497 COMMIT;
4498 END IF;
4499
4500 END; -- each component
4501
4502 END LOOP; -- cursor
4503
4504
4505 RETURN(0);
4506
4507 EXCEPTION
4508 WHEN others THEN
4509 err_text := 'Bom_Component_Api(Validate-'||stmt_num||') '||substrb(SQLERRM,1,500);
4510 RETURN(SQLCODE);
4511 END Validate_Component;
4512
4513
4514 /* -------------------------- Transact_Component --------------------------*/
4515 /*
4516 NAME
4517 Transact_Component
4518 DESCRIPTION
4519 Insert, update and delete component data from the interface
4520 table, BOM_INVENTORY_COMPS_INTERFACE, into the production table,
4521 BOM_INVENTORY_COMPONENTS.
4522 REQUIRES
4523 prog_appid Program application id
4524 prog_id Program id
4525 req_id Request id
4526 user_id User id
4527 login_id Login id
4528 MODIFIES
4529 BOM_INVENTORY_COMPONENTS
4530 BOM_INVENTORY_COMPS_INTERFACE
4531 RETURNS
4532 0 if successful
4533 SQLCODE if error
4534 NOTES
4535 -----------------------------------------------------------------------------*/
4536 FUNCTION Transact_Component
4537 ( user_id NUMBER,
4538 login_id NUMBER,
4539 prog_appid NUMBER,
4540 prog_id NUMBER,
4541 req_id NUMBER,
4542 err_text OUT NOCOPY VARCHAR2)
4543 return integer
4544 IS
4545 stmt_num NUMBER := 0;
4546 continue_loop BOOLEAN := TRUE;
4547 commit_cnt NUMBER;
4548 X_comp_group_name VARCHAR2(10);
4549 X_comp_group_description VARCHAR2(240);
4550 X_delete_group_seq_id NUMBER;
4551 X_new_group_seq_id NUMBER;
4552 X_delete_type NUMBER;
4553 X_error_message VARCHAR2(240);
4554 l_members_still_exist VARCHAR2(10);
4555 /*
4556 ** Select "CREATE" product family member and component records
4557 */
4558 CURSOR c0 IS
4559 SELECT bic.operation_seq_num OSN, bic.component_item_id CII,
4560 bic.last_update_date LUD, bic.organization_id OI,
4561 bic.last_updated_by LUB, bic.creation_date CD, bic.created_by CB,
4562 bic.last_update_login LUL,
4563 bic.item_num INUM, bic.component_quantity CQ,
4564 bic.component_yield_factor CYF,
4565 bic.component_remarks CR, bic.effectivity_date ED,
4566 bic.change_notice CN,
4567 bic.implementation_date ID, bic.disable_date DD,
4568 bic.attribute_category AC,
4569 bic.attribute1 A1, bic.attribute2 A2, bic.attribute3 A3,
4570 bic.attribute4 A4,
4571 bic.attribute5 A5,
4572 bic.attribute6 A6, bic.attribute7 A7, bic.attribute8 A8,
4573 bic.attribute9 A9,
4574 bic.attribute10 A10,
4575 bic.attribute11 A11, bic.attribute12 A12, bic.attribute13 A13,
4576 bic.attribute14 A14, bic.attribute15 A15,
4577 bic.planning_factor PF, bic.quantity_related QR, bic.so_basis SB,
4578 bic.optional O,
4579 bic.mutually_exclusive_options MEO,
4580 bic.include_in_cost_rollup ICR,
4581 bic.check_atp CA,
4582 bic.shipping_allowed SA, bic.required_to_ship RTS,
4583 bic.required_for_revenue RFR,
4584 bic.include_on_ship_docs ISD, bic.low_quantity LQ,
4585 bic.high_quantity HQ,
4586 bic.component_sequence_id CSI, bic.bill_sequence_id BSI,
4587 bic.request_id RI,
4588 bic.program_application_id PAI, bic.program_id PI,
4589 bic.program_update_date PUD,
4590 bic.wip_supply_type WST, bic.supply_locator_id SLI,
4591 bic.supply_subinventory SS, bic.transaction_id TI,
4592 msi2.bom_item_type BIT, msi1.bom_item_type CBIT,
4593 bom.assembly_item_id AII,
4594 bic.operation_lead_time_percent OLTP --1851537
4595 FROM
4596 bom_bill_of_materials bom,
4597 mtl_system_items msi1,
4598 mtl_system_items msi2,
4599 bom_inventory_comps_interface bic
4600 WHERE bic.process_flag = 4
4604 OR bic.interface_entity_type is null)
4601 AND bic.transaction_type = G_Insert
4602 AND rownum < G_rows_to_commit
4603 AND (UPPER(bic.interface_entity_type) = 'BILL'
4605 AND bic.bill_sequence_id = bom.bill_sequence_id
4606 AND bom.assembly_item_id = msi2.inventory_item_id
4607 AND bom.organization_id = msi2.organization_id
4608 AND bic.component_item_id = msi1.inventory_item_id
4609 AND bom.organization_id = msi1.organization_id;
4610 /*
4611 ** Select "Update" component records
4612 */
4613 CURSOR c1 IS
4614 SELECT component_sequence_id CSI, last_update_date LUD,
4615 last_updated_by LUB, last_update_login LUL,
4616 operation_seq_num OSN,
4617 operation_lead_time_percent OLTP, -- For bug 1804509
4618 item_num INUM, component_quantity CQ,
4619 component_yield_factor CYF, component_remarks CR,
4620 effectivity_date ED, implementation_date ID, disable_date DD,
4621 planning_factor PF, quantity_related QR, so_basis SB,
4622 optional O, mutually_exclusive_options MEO,
4623 include_in_cost_rollup IICR, check_atp CA, required_to_ship RTS,
4624 required_for_revenue RFR, include_on_ship_docs IOSD,
4625 low_quantity LQ, high_quantity HQ, wip_supply_type WST,
4626 supply_subinventory SS, supply_locator_id SLI,
4627 attribute_category AC, attribute1 A1, attribute2 A2,
4628 attribute3 A3, attribute4 A4, attribute5 A5, attribute6 A6,
4629 attribute7 A7, attribute8 A8, attribute9 A9, attribute10 A10,
4630 attribute11 A11, attribute12 A12, attribute13 A13,
4631 attribute14 A14, attribute15 A15, request_id RI,
4632 program_application_id PAI, program_id PI,
4633 program_update_date PUD, transaction_id TI
4634 FROM bom_inventory_comps_interface
4635 WHERE process_flag = 4
4636 AND transaction_type = G_UPDATE
4637 AND (UPPER(interface_entity_type) = 'BILL'
4638 OR interface_entity_type is null)
4639 AND rownum < G_rows_to_commit;
4640 /*
4641 ** Select "Delete" component records
4642 */
4643 CURSOR c2 IS
4644 SELECT bic.bill_sequence_id BSI, bic.assembly_type AST,
4645 bic.organization_id OI,
4646 bic.assembly_item_id AII, bic.alternate_bom_designator ABD,
4647 bic.component_sequence_id CSI, bic.transaction_id TI,
4648 msi.bom_item_type BIT, msic.bom_item_type BITC,
4649 bic.component_item_id CII
4650 FROM mtl_system_items msi,
4651 mtl_system_items msic,
4652 bom_inventory_comps_interface bic
4653 WHERE bic.process_flag = 4
4654 AND bic.transaction_type = G_DELETE
4655 AND (UPPER(bic.interface_entity_type) = 'BILL'
4656 OR bic.interface_entity_type is null)
4657 AND msi.organization_id = bic.organization_id
4658 AND msi.inventory_item_id = bic.assembly_item_id
4659 AND msic.organization_id = bic.organization_id
4660 AND msic.inventory_item_id = bic.component_item_id
4661 AND rownum < G_rows_to_commit;
4662
4663 BEGIN
4664 /*
4665 ** Insert Components
4666 */
4667 stmt_num := 20;
4668 continue_loop := TRUE;
4669 WHILE continue_loop LOOP
4670 commit_cnt := 0;
4671 FOR c0rec IN c0 LOOP
4672 commit_cnt := commit_cnt + 1;
4673 INSERT INTO bom_inventory_components
4674 (
4675 OPERATION_SEQ_NUM,
4676 COMPONENT_ITEM_ID,
4677 LAST_UPDATE_DATE,
4678 LAST_UPDATED_BY,
4679 CREATION_DATE,
4680 CREATED_BY,
4681 LAST_UPDATE_LOGIN,
4682 ITEM_NUM,
4683 COMPONENT_QUANTITY,
4684 COMPONENT_YIELD_FACTOR,
4685 COMPONENT_REMARKS,
4686 EFFECTIVITY_DATE,
4687 CHANGE_NOTICE,
4688 IMPLEMENTATION_DATE,
4689 DISABLE_DATE,
4690 ATTRIBUTE_CATEGORY,
4691 OPERATION_LEAD_TIME_PERCENT, --1851537
4692 ATTRIBUTE1,
4693 ATTRIBUTE2,
4694 ATTRIBUTE3,
4695 ATTRIBUTE4,
4696 ATTRIBUTE5,
4697 ATTRIBUTE6,
4698 ATTRIBUTE7,
4699 ATTRIBUTE8,
4700 ATTRIBUTE9,
4701 ATTRIBUTE10,
4702 ATTRIBUTE11,
4703 ATTRIBUTE12,
4704 ATTRIBUTE13,
4705 ATTRIBUTE14,
4706 ATTRIBUTE15,
4707 PLANNING_FACTOR,
4708 QUANTITY_RELATED,
4709 SO_BASIS,
4710 OPTIONAL,
4711 MUTUALLY_EXCLUSIVE_OPTIONS,
4712 INCLUDE_IN_COST_ROLLUP,
4713 CHECK_ATP,
4714 SHIPPING_ALLOWED,
4715 REQUIRED_TO_SHIP,
4716 REQUIRED_FOR_REVENUE,
4717 INCLUDE_ON_SHIP_DOCS,
4718 LOW_QUANTITY,
4722 REQUEST_ID,
4719 HIGH_QUANTITY,
4720 COMPONENT_SEQUENCE_ID,
4721 BILL_SEQUENCE_ID,
4723 PROGRAM_APPLICATION_ID,
4724 PROGRAM_ID,
4725 PROGRAM_UPDATE_DATE,
4726 WIP_SUPPLY_TYPE,
4727 SUPPLY_LOCATOR_ID,
4728 SUPPLY_SUBINVENTORY,
4729 BOM_ITEM_TYPE
4730 )
4731 VALUES(
4732 c0rec.OSN,
4733 c0rec.CII,
4734 c0rec.LUD,
4735 c0rec.LUB,
4736 c0rec.CD,
4737 c0rec.CB,
4738 c0rec.LUL,
4739 c0rec.INUM,
4740 c0rec.CQ,
4741 c0rec.CYF,
4742 c0rec.CR,
4743 c0rec.ED,
4744 c0rec.CN,
4745 c0rec.ID,
4746 c0rec.DD,
4747 c0rec.AC,
4748 c0rec.OLTP, --1851537
4749 c0rec.A1,
4750 c0rec.A2,
4751 c0rec.A3,
4752 c0rec.A4,
4753 c0rec.A5,
4754 c0rec.A6,
4755 c0rec.A7,
4756 c0rec.A8,
4757 c0rec.A9,
4758 c0rec.A10,
4759 c0rec.A11,
4760 c0rec.A12,
4761 c0rec.A13,
4762 c0rec.A14,
4763 c0rec.A15,
4764 c0rec.PF,
4765 c0rec.QR,
4766 c0rec.SB,
4767 c0rec.O,
4768 c0rec.MEO,
4769 c0rec.ICR,
4770 c0rec.CA,
4771 c0rec.SA,
4772 c0rec.RTS,
4773 c0rec.RFR,
4774 c0rec.ISD,
4775 c0rec.LQ,
4776 c0rec.HQ,
4777 c0rec.CSI,
4778 c0rec.BSI,
4779 c0rec.RI,
4780 c0rec.PAI,
4781 c0rec.PI,
4782 c0rec.PUD,
4783 c0rec.WST,
4784 c0rec.SLI,
4785 c0rec.SS,
4786 c0rec.CBIT);
4787 /*
4788 ** If product family member is added, need to update PRODUCT_FAMILY_ID
4789 ** in mtl_system_items.
4790 */
4791 IF (c0rec.BIT = G_ProductFamily) THEN
4792 BEGIN
4793 UPDATE mtl_system_items
4794 SET product_family_item_id = c0rec.AII
4795 WHERE inventory_item_id = c0rec.CII
4796 AND organization_id = c0rec.OI;
4797 END;
4798 END IF;
4799
4800 UPDATE bom_inventory_comps_interface
4801 SET process_flag = 7
4802 WHERE transaction_id = c0rec.TI;
4803 END LOOP;
4804
4805 stmt_num := 65;
4806 COMMIT;
4807 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
4808 continue_loop := FALSE;
4809 END IF;
4810 END LOOP;
4811
4812 /*
4813 ** Update Components
4814 */
4815 stmt_num := 63;
4816 continue_loop := TRUE;
4817 WHILE continue_loop LOOP
4818 commit_cnt := 0;
4819 FOR c1rec IN c1 LOOP
4820 commit_cnt := commit_cnt + 1;
4821 UPDATE bom_inventory_components
4822 SET last_update_date = c1rec.LUD,
4823 last_updated_by = c1rec.LUB,
4824 last_update_login = c1rec.LUL,
4825 operation_seq_num = c1rec.OSN,
4826 operation_lead_time_percent = c1rec.OLTP, -- For bug 1804509
4827 item_num = c1rec.INUM,
4828 component_quantity = c1rec.CQ,
4829 component_yield_factor = c1rec.CYF,
4830 component_remarks = c1rec.CR,
4831 effectivity_date = c1rec.ED,
4832 implementation_date = c1rec.ID,
4833 disable_date = c1rec.DD,
4834 planning_factor = c1rec.PF,
4835 quantity_related = c1rec.QR,
4836 so_basis = c1rec.SB,
4837 optional = c1rec.O,
4838 mutually_exclusive_options = c1rec.MEO,
4839 include_in_cost_rollup = c1rec.IICR,
4840 check_atp = c1rec.CA,
4841 required_to_ship = c1rec.RTS,
4842 required_for_revenue = c1rec.RFR,
4843 include_on_ship_docs = c1rec.IOSD,
4844 low_quantity = c1rec.LQ,
4845 high_quantity = c1rec.HQ,
4846 wip_supply_type = c1rec.WST,
4850 attribute1 = c1rec.A1,
4847 supply_subinventory = c1rec.SS,
4848 supply_locator_id = c1rec.SLI,
4849 attribute_category = c1rec.AC,
4851 attribute2 = c1rec.A2,
4852 attribute3 = c1rec.A3,
4853 attribute4 = c1rec.A4,
4854 attribute5 = c1rec.A5,
4855 attribute6 = c1rec.A6,
4856 attribute7 = c1rec.A7,
4857 attribute8 = c1rec.A8,
4858 attribute9 = c1rec.A9,
4859 attribute10 = c1rec.A10,
4860 attribute11 = c1rec.A11,
4861 attribute12 = c1rec.A12,
4862 attribute13 = c1rec.A13,
4863 attribute14 = c1rec.A14,
4864 attribute15 = c1rec.A15,
4865 request_id = c1rec.RI,
4866 program_application_id = c1rec.PAI,
4867 program_id = c1rec.PI,
4868 program_update_date = c1rec.PUD
4869 WHERE component_sequence_id = c1rec.CSI;
4870
4871 stmt_num := 64;
4872 UPDATE bom_inventory_comps_interface
4873 SET process_flag = 7
4874 WHERE transaction_id = c1rec.TI;
4875 END LOOP;
4876
4877 stmt_num := 65;
4878 COMMIT;
4879 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
4880 continue_loop := FALSE;
4881 END IF;
4882
4883 END LOOP;
4884 /*
4885 ** Delete Components
4886 */
4887 stmt_num := 33;
4888 continue_loop := TRUE;
4889
4890 WHILE continue_loop LOOP
4891 commit_cnt := 0;
4892 FOR c2rec IN c2 LOOP
4893 commit_cnt := commit_cnt + 1;
4894 IF (c2rec.BIT = G_ProductFamily) THEN
4895 /*
4896 ** For Members, delete specific Allocation record.
4897 ** Also null out product family id in item master if
4898 ** this member has no more Allocations for this Product Family.
4899 */
4900 DELETE FROM bom_inventory_components
4901 WHERE component_sequence_id = c2rec.CSI;
4902
4903 BEGIN
4904 SELECT 'yes'
4905 INTO l_members_still_exist
4906 FROM bom_inventory_components
4907 WHERE bill_sequence_id = c2rec.BSI
4908 AND component_item_id = c2rec.CII
4909 AND rownum = 1;
4910 EXCEPTION
4911 WHEN no_data_found THEN
4912 UPDATE mtl_system_items
4913 SET product_family_item_id = null
4914 WHERE inventory_item_id = c2rec.CII
4915 AND organization_id = c2rec.OI;
4916 END;
4917
4918 ELSE
4919 /*
4920 ** Get the Component Delete Group name
4921 */
4922 IF (X_comp_group_name is null) THEN
4923 DECLARE
4924 CURSOR GetCompGroup IS
4925 SELECT delete_group_name, description
4926 FROM bom_interface_delete_groups
4927 WHERE UPPER(entity_name) = G_DeleteEntity;
4928 BEGIN
4929 FOR X_compgroup IN GetCompGroup LOOP
4930 X_comp_group_name := X_compgroup.delete_group_name;
4931 X_comp_group_description := X_compgroup.description;
4932 END LOOP;
4933
4934 IF (X_comp_group_name is null) THEN
4935 X_error_message := FND_MESSAGE.Get_String('BOM',
4936 'BOM_COMP_DELETE_GROUP_MISSING');
4937 err_text := 'Bom_Component_Api:'||to_char(stmt_num)||
4938 '- '||X_error_message;
4939 RETURN(-9999);
4940 END IF;
4941 END;
4942 END IF;
4943
4944 X_delete_group_seq_id := null;
4945 BEGIN
4946 SELECT delete_group_sequence_id, delete_type
4947 INTO X_delete_group_seq_id, X_delete_type
4948 FROM bom_delete_groups
4949 WHERE delete_group_name = X_comp_group_name
4950 AND organization_id = c2rec.OI;
4951
4952 IF (X_delete_type <> 4) THEN
4953 X_error_message := FND_MESSAGE.Get_String('BOM',
4954 'BOM_DELETE_GROUP_INVALID');
4955 err_text := 'Bom_Component_Api('||to_char(stmt_num)||
4956 ') - '||X_error_message;
4957 RETURN(-9999);
4958 END IF;
4959
4960 EXCEPTION
4961 WHEN no_data_found THEN
4962 null;
4963 END;
4964
4965 X_new_group_seq_id := Modal_Delete.Delete_Manager_Oi(
4966 new_group_seq_id => X_delete_group_seq_id,
4967 name => X_comp_group_name,
4968 group_desc => X_comp_group_description,
4969 org_id => c2rec.OI,
4970 bom_or_eng => c2rec.AST,
4971 del_type => 4,
4972 ent_bill_seq_id => c2rec.BSI,
4973 ent_rtg_seq_id => null,
4977 ent_op_seq_id => null,
4974 ent_inv_item_id => c2rec.AII,
4975 ent_alt_designator => c2rec.ABD,
4976 ent_comp_seq_id => c2rec.CSI,
4978 user_id => user_id,
4979 err_text => err_text);
4980 END IF; -- Check if Member or Component
4981
4982 stmt_num := 34;
4983 UPDATE bom_inventory_comps_interface
4984 SET process_flag = 7
4985 WHERE transaction_id = c2rec.TI;
4986 END LOOP;
4987
4988 stmt_num := 35;
4989 COMMIT;
4990 IF (commit_cnt < (G_rows_to_commit - 1)) THEN
4991 continue_loop := FALSE;
4992 END IF;
4993
4994 END LOOP;
4995
4996 RETURN(0);
4997
4998 EXCEPTION
4999 WHEN no_data_found THEN
5000 RETURN(0);
5001 WHEN OTHERS THEN
5002 ROLLBACK;
5003 err_text := 'Bom_Component_Api(Transact-'||stmt_num||') '||substrb(SQLERRM,1,500);
5004 return(SQLCODE);
5005
5006 END Transact_Component;
5007
5008 /* ----------------------------- Import_Component -------------------------- */
5009 /*
5010 NAME
5011 Import_Component
5012 DESCRIPTION
5013 Assign, Validate, and Transact the Component record in the
5014 interface table, BOM_INVENTORY_COMPS_INTERFACE.
5015 REQUIRES
5016 err_text out buffer to return error message
5017 MODIFIES
5018 RETURNS
5019 0 if successful
5020 SQLCODE if unsuccessful
5021 NOTES
5022 -----------------------------------------------------------------------------*/
5023 FUNCTION Import_Component (
5024 org_id NUMBER,
5025 all_org NUMBER := 1,
5026 user_id NUMBER := -1,
5027 login_id NUMBER := -1,
5028 prog_appid NUMBER := -1,
5029 prog_id NUMBER := -1,
5030 req_id NUMBER := -1,
5031 del_rec_flag NUMBER := 1,
5032 err_text IN OUT NOCOPY VARCHAR2
5033 )
5034 return INTEGER
5035 IS
5036 err_msg VARCHAR2(2000);
5037 ret_code NUMBER := 1;
5038 stmt_num NUMBER := 0;
5039 BEGIN
5040 stmt_num := 1;
5041 ret_code := Assign_Component (
5042 org_id => org_id,
5043 all_org => all_org,
5044 user_id => user_id,
5045 login_id => login_id,
5046 prog_appid => prog_appid,
5047 prog_id => prog_id,
5048 req_id => req_id,
5049 err_text => err_msg);
5050 IF (ret_code <> 0) THEN
5051 err_text := 'Assign_Component '||substrb(err_msg, 1,1500);
5052 ROLLBACK;
5053 RETURN(ret_code);
5054 END IF;
5055 COMMIT;
5056
5057 stmt_num := 2;
5058 ret_code := Validate_Component (
5059 org_id => org_id,
5060 all_org => all_org,
5061 user_id => user_id,
5062 login_id => login_id,
5063 prog_appid => prog_appid,
5064 prog_id => prog_id,
5065 req_id => req_id,
5066 err_text => err_msg);
5067 IF (ret_code <> 0) THEN
5068 err_text := 'Validate_Component '||substrb(err_msg, 1,1500);
5069 ROLLBACK;
5070 RETURN(ret_code);
5071 END IF;
5072 COMMIT;
5073
5074 stmt_num := 3;
5075 ret_code := Transact_Component (
5076 user_id => user_id,
5077 login_id => login_id,
5078 prog_appid => prog_appid,
5079 prog_id => prog_id,
5080 req_id => req_id,
5081 err_text => err_msg);
5082
5083 IF (ret_code <> 0) THEN
5084 err_text := 'Transact_Component '||substrb(err_msg, 1,1500);
5085 ROLLBACK;
5086 RETURN(ret_code);
5087 END IF;
5088 COMMIT;
5089
5090 stmt_num := 4;
5091 IF (del_rec_flag = 1) THEN
5092 LOOP
5093 DELETE from bom_inventory_comps_interface
5094 WHERE process_flag = 7
5095 AND (UPPER(interface_entity_type) = 'BILL'
5096 OR interface_entity_type is null)
5097 AND rownum < G_rows_to_commit;
5098
5099 EXIT when SQL%NOTFOUND;
5100 COMMIT;
5101 END LOOP;
5102 END IF;
5103
5104 RETURN(0);
5105
5106 EXCEPTION
5107 WHEN others THEN
5108 err_text := 'Bom_Component_Api(Import-'||stmt_num||') '||substrb(SQLERRM,1,1000);
5109 RETURN(ret_code);
5110 END Import_Component;
5111
5112
5113 END Bom_Component_Api;