[Home] [Help]
110: | Error Message Added CTO_ZERO_BOM_COMP for option item with zero qty on config bom.
111: |
112: |
113: | ssawant 15-JAN-04 Bugfix 3374548
114: | Added bill_sequence_id to condition to avoid corrupt data from bom_inventory_comps_interface.
115: |
116: |
117: | ssawant 29-JAN-04 Bugfix 3367823
118: | Accounted for UOM conversion in bom_inventory_components.
281: where parent_ato_line_id=xlineid
282: and parent_ato_line_id <> line_id /* to avoid selecting top model */
283: minus
284: select revised_item_sequence_id /* new column used to store line_id */
285: from bom_inventory_comps_interface
286: where bill_sequence_id = xconfigbillid
287: and greatest(sysdate, xEstRelDate ) >= effectivity_date
288: and (( disable_date is null ) or ( disable_date is not null and greatest(sysdate, xEstRelDate) <= disable_date )) ;
289:
351: --- Renga
352:
353: cursor Debug_cur is
354: select assembly_item_id,component_item_id,operation_seq_num,max(disable_date) disable_date
355: from bom_inventory_comps_interface
356: where bill_sequence_id = lconfigbillid
357: group by assembly_item_id,component_item_id,operation_seq_num;
358:
359: --- End Renga
360: /* END : New Effectivity date approach for bug 4147224 */
361:
362: cursor debug_cur1 is
363: select component_item_id,component_sequence_id,operation_seq_num,effectivity_date,disable_date
364: from bom_inventory_comps_interface
365: where bill_sequence_id = lConfigBillId --Bugfix 6603382: So that components belonging to this bill only are picked up
366: order by component_item_id,operation_seq_num,effectivity_Date,disable_date;
367: l_token2 CTO_MSG_PUB.token_tbl;
368: l_model_name varchar2(1000);
429:
430:
431:
432: /* Added for avoiding bad data during preconfig bom scenario or any data that could interfere with bom creation */
433: delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
434:
435:
436: IF PG_DEBUG <> 0 THEN
437: oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
643: /*-----------------------------------------------------------+
644: First:
645: All the chosen option items/models/Classes associated
646: with the new configuration items will be loaded into the
647: BOM_INVENTORY_COMPS_INTERFACE table.
648: +-------------------------------------------------------------*/
649:
650: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
651: lStmtNum := 30;
646: with the new configuration items will be loaded into the
647: BOM_INVENTORY_COMPS_INTERFACE table.
648: +-------------------------------------------------------------*/
649:
650: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
651: lStmtNum := 30;
652:
653: -- rkaza. bug 4524248. bom structure import enhancements. Added batch_id
654:
651: lStmtNum := 30;
652:
653: -- rkaza. bug 4524248. bom structure import enhancements. Added batch_id
654:
655: insert into BOM_INVENTORY_COMPS_INTERFACE
656: (
657: operation_seq_num,
658: component_item_id,
659: last_update_date,
861:
862:
863: lCnt := sql%rowcount ;
864: IF PG_DEBUG <> 0 THEN
865: oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
866: END IF;
867:
868:
869:
867:
868:
869:
870:
871: select count(*) into v_zero_qty_count from bom_inventory_comps_interface
872: where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
873:
874:
875: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
909: /*---------------------------------------------------------------+
910: Second:
911: All the standard component items associated
912: with the new configuration items will be loaded into the
913: BOM_INVENTORY_COMPS_INTERFACE table.
914: +----------------------------------------------------------------*/
915:
916: lStmtNum := 50;
917: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
913: BOM_INVENTORY_COMPS_INTERFACE table.
914: +----------------------------------------------------------------*/
915:
916: lStmtNum := 50;
917: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
918: insert into BOM_INVENTORY_COMPS_INTERFACE
919: (
920: operation_seq_num,
921: component_item_id,
914: +----------------------------------------------------------------*/
915:
916: lStmtNum := 50;
917: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
918: insert into BOM_INVENTORY_COMPS_INTERFACE
919: (
920: operation_seq_num,
921: component_item_id,
922: last_update_date,
1127: -- operating sequence.
1128: -- Mandatory comps row will have assembly_item_id as null
1129: -- option items row will have assembly_item_id populated
1130:
1131: update bom_inventory_comps_interface
1132: set disable_date = g_futuredate
1133: where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1134: in ( select
1135: component_item_id,nvl(assembly_item_id,-1),max(disable_date)
1132: set disable_date = g_futuredate
1133: where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1134: in ( select
1135: component_item_id,nvl(assembly_item_id,-1),max(disable_date)
1136: from bom_inventory_comps_interface
1137: where bill_sequence_id = lConfigBillId
1138: group by component_item_id, assembly_item_id
1139: )
1140: and bill_sequence_id = lConfigBillId
1156: begin
1157: select 1 into v_overlap_check
1158: from dual
1159: where exists
1160: ( select * from bom_inventory_comps_interface
1161: where bill_sequence_id = lConfigBillId
1162: group by component_item_id, assembly_item_id
1163: having count(distinct operation_seq_num) > 1
1164: );
1185: BULK COLLECT INTO
1186: v_t_overlap_comp_item_id,
1187: v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
1188: v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
1189: from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
1190: where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
1191: --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
1192: and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
1193: and s1.effectivity_date < s2.disable_date --Bugfix 6603382
1904: Third : Get the base model row into BOM_INVENTORY_COMPONENTS
1905: +----------------------------------------------------------------*/
1906:
1907: lStmtNum := 60;
1908: insert into BOM_INVENTORY_COMPS_INTERFACE
1909: (
1910: operation_seq_num,
1911: component_item_id,
1912: last_update_date,
2065: END IF;
2066:
2067:
2068:
2069: delete from bom_inventory_comps_interface
2070: where bill_sequence_id = xBillId ;
2071:
2072: xBillId := null ;
2073:
2174: -- this case the two records at 15 needs to be clubbed but not the once at 25 and 30. Going
2175: -- just by item_id will club all 4 records. We need to go by item_id and op_seq.
2176: cursor club_comp is
2177: select distinct b1.component_item_id item_id, b1.operation_seq_num
2178: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2179: where b1.bill_sequence_id = b2.bill_sequence_id
2180: and b1.component_sequence_id <> b2.component_sequence_id
2181: and b1.operation_seq_num = b2.operation_seq_num
2182: and b1.component_item_id = b2.component_item_id
2214: select effectivity_date eff_date,
2215: nvl (disable_date,g_SchShpDate) dis_date,
2216: component_quantity cmp_qty,
2217: basis_type
2218: from bom_inventory_comps_interface
2219: where bill_sequence_id = pConfigBillId
2220: and component_item_id = xItemId
2221: and operation_seq_num = xOperation_seq_num; --4244576
2222: -- bugfix 3985173
2244: l_org_name varchar2(1000);
2245: begin
2246:
2247: /*--------------------------------------------------------------+
2248: If more than one row in the BOM_INVENTORY_COMPS_INTERFACE
2249: that contain the same bill_sequence_id, operation_seq_num and
2250: component_item_id, those rows will be combined into a
2251: single row and the accumulated COMPONENT_QUANTITY will be
2252: used in the row.
2258: -- 4244576 - Also need to get operstion_seq_num into an array.
2259:
2260: select b1.model_comp_seq_id, b1.component_item_id, b1.operation_seq_num
2261: BULK COLLECT INTO model_comp_seq_id_arr, component_item_id_arr, operation_seq_num_arr
2262: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2263: where b1.bill_sequence_id = b2.bill_sequence_id
2264: and b1.component_sequence_id <> b2.component_sequence_id
2265: and b1.operation_seq_num = b2.operation_seq_num
2266: and b1.component_item_id = b2.component_item_id
2266: and b1.component_item_id = b2.component_item_id
2267: and b1.bill_sequence_id = pConfigBillId
2268: UNION
2269: select b2.model_comp_seq_id, b2.component_item_id, b2.operation_seq_num
2270: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2271: where b1.bill_sequence_id = b2.bill_sequence_id
2272: and b1.component_sequence_id <> b2.component_sequence_id
2273: and b1.operation_seq_num = b2.operation_seq_num
2274: and b1.component_item_id = b2.component_item_id
2306: oe_debug_pub.add( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
2307:
2308: select distinct effectivity_date
2309: BULK COLLECT INTO asc_date_arr
2310: from bom_inventory_comps_interface
2311: where bill_sequence_id = pConfigBillId
2312: and component_item_id = club_comp_rec.item_id
2313: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2314: UNION
2312: and component_item_id = club_comp_rec.item_id
2313: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2314: UNION
2315: select distinct disable_date
2316: from bom_inventory_comps_interface
2317: where bill_sequence_id = pConfigBillId
2318: and component_item_id = club_comp_rec.item_id
2319: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2320: order by 1;
2413: select b1.model_comp_seq_id, b1.component_item_id
2414: BULK COLLECT INTO
2415: basis_model_comp_seq_id_arr, basis_component_item_id_arr
2416: from
2417: bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2418: where b1.bill_sequence_id = b2.bill_sequence_id
2419: and b1.component_sequence_id <> b2.component_sequence_id
2420: and b1.operation_seq_num = b2.operation_seq_num
2421: and b1.component_item_id = b2.component_item_id
2500:
2501: select max(rowid), sum(decode(nvl(basis_type,1), 1, component_quantity, 0))
2502: + max(decode(nvl(basis_type,1), 2, component_quantity, 0)) /* LBM Project */
2503: into club_tab_arr(x4).row_id,club_tab_arr(x4).qty
2504: from bom_inventory_comps_interface
2505: where effectivity_date <= club_tab_arr(x4).eff_dt
2506: and nvl(disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
2507: and bill_sequence_id = pConfigBillId
2508: and component_item_id = club_comp_rec.item_id
2522: END IF;
2523: end loop;
2524: end if;
2525:
2526: -- Now insert into bom_inventory_comps_interface
2527:
2528: -- Modified by Renga Kannan on 09/01/06 for bug 4542461
2529: -- For the window where there is no qty the above select statement will
2530: -- return null qty. We should not insert this row into interface table.
2533:
2534: for x6 in club_tab_arr.FIRST.. club_tab_arr.LAST
2535: loop
2536: If nvl(club_tab_arr(x6).qty,0) <> 0 then
2537: insert into bom_inventory_comps_interface
2538: (
2539: component_item_id,
2540: bill_sequence_id,
2541: effectivity_date,
2666: plan_level,
2667: revised_item_sequence_id
2668: , nvl(basis_type,1), /* LBM project */
2669: cto_msutil_pub.bom_batch_id
2670: from bom_inventory_comps_interface
2671: where component_item_id = club_comp_rec.item_id
2672: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2673: and bill_sequence_id = pConfigBillId
2674: and rowid = club_tab_arr(x6).row_id;
2676: end loop;
2677: end if;
2678:
2679: -- Delete original option item rows from bici
2680: delete from bom_inventory_comps_interface
2681: where component_item_id = club_comp_rec.item_id
2682: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2683: and bill_sequence_id = pConfigBillId
2684: and created_by <> pConfigBillId;
2682: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2683: and bill_sequence_id = pConfigBillId
2684: and created_by <> pConfigBillId;
2685:
2686: -- Delete rows from bom_inventory_comps_interface where qty = 0
2687: delete from bom_inventory_comps_interface
2688: where component_item_id = club_comp_rec.item_id
2689: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2690: and bill_sequence_id = pConfigBillId
2683: and bill_sequence_id = pConfigBillId
2684: and created_by <> pConfigBillId;
2685:
2686: -- Delete rows from bom_inventory_comps_interface where qty = 0
2687: delete from bom_inventory_comps_interface
2688: where component_item_id = club_comp_rec.item_id
2689: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2690: and bill_sequence_id = pConfigBillId
2691: and created_by = pConfigBillId
2742: IF PG_DEBUG <> 0 THEN
2743: oe_debug_pub.add('create_bom_data_ml: ' || 'p_item_num::'||to_char(p_item_num), 2);
2744: END IF;
2745:
2746: update bom_inventory_comps_interface
2747: set item_num = p_item_num
2748: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
2749:
2750: IF PG_DEBUG <> 0 THEN
2763: into p_bill_seq_id
2764: from bom_bill_of_materials
2765: where assembly_item_id =
2766: (select component_item_id
2767: from bom_inventory_comps_interface
2768: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
2769: and organization_id = pOrgId
2770: and alternate_bom_designator is null;
2771:
3097: b.parent_bill_seq_id, --isp bom
3098: b.plan_level, --isp bom
3099: b.model_comp_seq_id --isp bom
3100: , decode(b.basis_type,1,null,b.basis_type) /* LBM Change */
3101: from bom_inventory_comps_interface b , mtl_system_items msi
3102: where b.bill_sequence_id = pConfigBillId
3103: and b.component_item_id = msi.inventory_item_id
3104: and msi.organization_id = pOrgId ;
3105:
3200: ,created_by
3201: ,last_update_login
3202: */
3203:
3204: from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
3205: where b.bill_sequence_id = pConfigBillId
3206: and ABS(b.model_comp_seq_id) = bic.component_sequence_id
3207: and bic.optional = 2 /* only mandatory components */
3208: and bic.component_sequence_id = s.component_sequence_id ;
3702: );
3703:
3704: lStmtNum := 380;
3705:
3706: /* Clean up bom_inventory_comps_interface */
3707:
3708: delete from bom_inventory_comps_interface
3709: where bill_sequence_id = pConfigBillId;
3710:
3704: lStmtNum := 380;
3705:
3706: /* Clean up bom_inventory_comps_interface */
3707:
3708: delete from bom_inventory_comps_interface
3709: where bill_sequence_id = pConfigBillId;
3710:
3711: return(1);
3712:
3714: WHEN NO_DATA_FOUND THEN
3715: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3716:
3717: -- Sushant Fixed bug #3374548
3718: /* Clean up bom_inventory_comps_interface */
3719: delete from bom_inventory_comps_interface
3720: where bill_sequence_id = pConfigBillId;
3721:
3722: return(0);
3715: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3716:
3717: -- Sushant Fixed bug #3374548
3718: /* Clean up bom_inventory_comps_interface */
3719: delete from bom_inventory_comps_interface
3720: where bill_sequence_id = pConfigBillId;
3721:
3722: return(0);
3723:
3729: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3730: END IF;
3731:
3732: -- Sushant Fixed bug #3374548
3733: /* Clean up bom_inventory_comps_interface */
3734: delete from bom_inventory_comps_interface
3735: where bill_sequence_id = pConfigBillId;
3736:
3737:
3730: END IF;
3731:
3732: -- Sushant Fixed bug #3374548
3733: /* Clean up bom_inventory_comps_interface */
3734: delete from bom_inventory_comps_interface
3735: where bill_sequence_id = pConfigBillId;
3736:
3737:
3738: return(0);
3743: oe_debug_pub.add('Unexpected Error: ' || xErrorMessage);
3744: END IF;
3745:
3746: -- Sushant Fixed bug #3374548
3747: /* Clean up bom_inventory_comps_interface */
3748: delete from bom_inventory_comps_interface
3749: where bill_sequence_id = pConfigBillId;
3750:
3751: return(0);
3744: END IF;
3745:
3746: -- Sushant Fixed bug #3374548
3747: /* Clean up bom_inventory_comps_interface */
3748: delete from bom_inventory_comps_interface
3749: where bill_sequence_id = pConfigBillId;
3750:
3751: return(0);
3752:
3757: oe_debug_pub.add('create_bom_data_ml: ' || 'other Error: ' || xErrorMessage, 1);
3758: END IF;
3759:
3760: -- Sushant Fixed bug #3374548
3761: /* Clean up bom_inventory_comps_interface */
3762: delete from bom_inventory_comps_interface
3763: where bill_sequence_id = pConfigBillId;
3764:
3765: return(0);
3758: END IF;
3759:
3760: -- Sushant Fixed bug #3374548
3761: /* Clean up bom_inventory_comps_interface */
3762: delete from bom_inventory_comps_interface
3763: where bill_sequence_id = pConfigBillId;
3764:
3765: return(0);
3766:
3784:
3785: CURSOR c_update_item_num(p_parent_bill_seq_id number) IS
3786: select component_sequence_id,
3787: component_item_id
3788: from bom_inventory_comps_interface
3789: where parent_bill_seq_id = p_parent_bill_seq_id
3790: FOR UPDATE OF item_num;
3791:
3792: p_bill_seq_id number;
3802:
3803: --
3804: -- update item_num of child of this model
3805: --
3806: update bom_inventory_comps_interface
3807: set item_num = p_item_num
3808: where current of c_update_item_num;
3809:
3810: IF PG_DEBUG <> 0 THEN
4252:
4253: update bom_explosion_temp set disable_date = g_futuredate
4254: where ( component_item_id , operation_seq_num, nvl(assembly_item_id,-1) , disable_date) in
4255: ( select component_item_id, operation_seq_num, nvl(assembly_item_id,-1), max(disable_date)
4256: from bom_inventory_comps_interface
4257: where bill_sequence_id = pConfigBillId
4258: group by component_item_id, operation_seq_num, assembly_item_id)
4259: and disable_date <> g_futuredate ;
4260:
4580:
4581: lStmtNumber := 550;
4582:
4583: /*Insert into bic interface*/
4584: insert into BOM_INVENTORY_COMPS_INTERFACE
4585: ( operation_seq_num,
4586: component_item_id,
4587: last_update_date,
4588: last_updated_by,
4741: begin
4742: select 1 into v_overlap_check
4743: from dual
4744: where exists
4745: ( select * from bom_inventory_comps_interface
4746: where bill_sequence_id = pConfigBillId
4747: group by component_item_id, assembly_item_id
4748: having count(distinct operation_seq_num) > 1
4749: );
4762: BULK COLLECT INTO
4763: v_t_overlap_comp_item_id,
4764: v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
4765: v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
4766: from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
4767: where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
4768: --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
4769: and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
4770: and s1.effectivity_date < s2.disable_date --Bugfix 6603382