[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);
431:
432:
433:
434: /* Added for avoiding bad data during preconfig bom scenario or any data that could interfere with bom creation */
435: delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
436:
437:
438: IF PG_DEBUG <> 0 THEN
439: oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
645: /*-----------------------------------------------------------+
646: First:
647: All the chosen option items/models/Classes associated
648: with the new configuration items will be loaded into the
649: BOM_INVENTORY_COMPS_INTERFACE table.
650: +-------------------------------------------------------------*/
651:
652: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
653: lStmtNum := 30;
648: with the new configuration items will be loaded into the
649: BOM_INVENTORY_COMPS_INTERFACE table.
650: +-------------------------------------------------------------*/
651:
652: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
653: lStmtNum := 30;
654:
655: -- rkaza. bug 4524248. bom structure import enhancements. Added batch_id
656:
653: lStmtNum := 30;
654:
655: -- rkaza. bug 4524248. bom structure import enhancements. Added batch_id
656:
657: insert into BOM_INVENTORY_COMPS_INTERFACE
658: (
659: operation_seq_num,
660: component_item_id,
661: last_update_date,
863:
864:
865: lCnt := sql%rowcount ;
866: IF PG_DEBUG <> 0 THEN
867: oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
868: END IF;
869:
870:
871:
869:
870:
871:
872:
873: select count(*) into v_zero_qty_count from bom_inventory_comps_interface
874: where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
875:
876:
877: oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
900:
901: end if ;
902:
903: --Bug 7418622.FP for 7154767.pdube
904: UPDATE bom_inventory_comps_interface
905: SET disable_date = g_futuredate
906: WHERE
907: (
908: component_item_id, NVL(assembly_item_id,-1),disable_date
911: (
912: SELECT component_item_id ,
913: NVL(assembly_item_id,-1),
914: MAX(disable_date)
915: FROM bom_inventory_comps_interface
916: WHERE bill_sequence_id = lConfigBillId
917: GROUP BY component_item_id,
918: assembly_item_id
919: )
934: /*---------------------------------------------------------------+
935: Second:
936: All the standard component items associated
937: with the new configuration items will be loaded into the
938: BOM_INVENTORY_COMPS_INTERFACE table.
939: +----------------------------------------------------------------*/
940:
941: lStmtNum := 50;
942: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
938: BOM_INVENTORY_COMPS_INTERFACE table.
939: +----------------------------------------------------------------*/
940:
941: lStmtNum := 50;
942: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
943: insert into BOM_INVENTORY_COMPS_INTERFACE
944: (
945: operation_seq_num,
946: component_item_id,
939: +----------------------------------------------------------------*/
940:
941: lStmtNum := 50;
942: xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
943: insert into BOM_INVENTORY_COMPS_INTERFACE
944: (
945: operation_seq_num,
946: component_item_id,
947: last_update_date,
1153: -- Mandatory comps row will have assembly_item_id as null
1154: -- option items row will have assembly_item_id populated
1155:
1156: -- Commenting this update statement as part of Bug 7418622.FP for 7154767.pdube
1157: /*update bom_inventory_comps_interface
1158: set disable_date = g_futuredate
1159: where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1160: in ( select
1161: component_item_id,nvl(assembly_item_id,-1),max(disable_date)
1158: set disable_date = g_futuredate
1159: where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1160: in ( select
1161: component_item_id,nvl(assembly_item_id,-1),max(disable_date)
1162: from bom_inventory_comps_interface
1163: where bill_sequence_id = lConfigBillId
1164: group by component_item_id, assembly_item_id
1165: )
1166: and bill_sequence_id = lConfigBillId
1182: begin
1183: select 1 into v_overlap_check
1184: from dual
1185: where exists
1186: ( select * from bom_inventory_comps_interface
1187: where bill_sequence_id = lConfigBillId
1188: group by component_item_id, assembly_item_id
1189: having count(distinct operation_seq_num) > 1
1190: );
1211: BULK COLLECT INTO
1212: v_t_overlap_comp_item_id,
1213: v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
1214: v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
1215: from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
1216: where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
1217: --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
1218: and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
1219: and s1.effectivity_date < s2.disable_date --Bugfix 6603382
1930: Third : Get the base model row into BOM_INVENTORY_COMPONENTS
1931: +----------------------------------------------------------------*/
1932:
1933: lStmtNum := 60;
1934: insert into BOM_INVENTORY_COMPS_INTERFACE
1935: (
1936: operation_seq_num,
1937: component_item_id,
1938: last_update_date,
2091: END IF;
2092:
2093:
2094:
2095: delete from bom_inventory_comps_interface
2096: where bill_sequence_id = xBillId ;
2097:
2098: --Bugfix 11056452
2099: lCnt := sql%rowcount;
2214: -- this case the two records at 15 needs to be clubbed but not the once at 25 and 30. Going
2215: -- just by item_id will club all 4 records. We need to go by item_id and op_seq.
2216: cursor club_comp is
2217: select distinct b1.component_item_id item_id, b1.operation_seq_num
2218: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2219: where b1.bill_sequence_id = b2.bill_sequence_id
2220: and b1.component_sequence_id <> b2.component_sequence_id
2221: and b1.operation_seq_num = b2.operation_seq_num
2222: and b1.component_item_id = b2.component_item_id
2254: select effectivity_date eff_date,
2255: nvl (disable_date,g_SchShpDate) dis_date,
2256: component_quantity cmp_qty,
2257: basis_type
2258: from bom_inventory_comps_interface
2259: where bill_sequence_id = pConfigBillId
2260: and component_item_id = xItemId
2261: and operation_seq_num = xOperation_seq_num; --4244576
2262: -- bugfix 3985173
2287: lCnt number;
2288: begin
2289:
2290: /*--------------------------------------------------------------+
2291: If more than one row in the BOM_INVENTORY_COMPS_INTERFACE
2292: that contain the same bill_sequence_id, operation_seq_num and
2293: component_item_id, those rows will be combined into a
2294: single row and the accumulated COMPONENT_QUANTITY will be
2295: used in the row.
2301: -- 4244576 - Also need to get operstion_seq_num into an array.
2302:
2303: select b1.model_comp_seq_id, b1.component_item_id, b1.operation_seq_num
2304: BULK COLLECT INTO model_comp_seq_id_arr, component_item_id_arr, operation_seq_num_arr
2305: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2306: where b1.bill_sequence_id = b2.bill_sequence_id
2307: and b1.component_sequence_id <> b2.component_sequence_id
2308: and b1.operation_seq_num = b2.operation_seq_num
2309: and b1.component_item_id = b2.component_item_id
2309: and b1.component_item_id = b2.component_item_id
2310: and b1.bill_sequence_id = pConfigBillId
2311: UNION
2312: select b2.model_comp_seq_id, b2.component_item_id, b2.operation_seq_num
2313: from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2314: where b1.bill_sequence_id = b2.bill_sequence_id
2315: and b1.component_sequence_id <> b2.component_sequence_id
2316: and b1.operation_seq_num = b2.operation_seq_num
2317: and b1.component_item_id = b2.component_item_id
2349: oe_debug_pub.add( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
2350:
2351: select distinct effectivity_date
2352: BULK COLLECT INTO asc_date_arr
2353: from bom_inventory_comps_interface
2354: where bill_sequence_id = pConfigBillId
2355: and component_item_id = club_comp_rec.item_id
2356: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2357: UNION
2355: and component_item_id = club_comp_rec.item_id
2356: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2357: UNION
2358: select distinct disable_date
2359: from bom_inventory_comps_interface
2360: where bill_sequence_id = pConfigBillId
2361: and component_item_id = club_comp_rec.item_id
2362: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2363: order by 1;
2473: select b1.model_comp_seq_id, b1.component_item_id
2474: BULK COLLECT INTO
2475: basis_model_comp_seq_id_arr, basis_component_item_id_arr
2476: from
2477: bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2478: where b1.bill_sequence_id = b2.bill_sequence_id
2479: and b1.component_sequence_id <> b2.component_sequence_id
2480: and b1.operation_seq_num = b2.operation_seq_num
2481: and b1.component_item_id = b2.component_item_id
2560:
2561: select max(rowid), sum(decode(nvl(basis_type,1), 1, component_quantity, 0))
2562: + max(decode(nvl(basis_type,1), 2, component_quantity, 0)) /* LBM Project */
2563: into club_tab_arr(x4).row_id,club_tab_arr(x4).qty
2564: from bom_inventory_comps_interface
2565: where effectivity_date <= club_tab_arr(x4).eff_dt
2566: and nvl(disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
2567: and bill_sequence_id = pConfigBillId
2568: and component_item_id = club_comp_rec.item_id
2582: END IF;
2583: end loop;
2584: end if;
2585:
2586: -- Now insert into bom_inventory_comps_interface
2587:
2588: -- Modified by Renga Kannan on 09/01/06 for bug 4542461
2589: -- For the window where there is no qty the above select statement will
2590: -- return null qty. We should not insert this row into interface table.
2593:
2594: for x6 in club_tab_arr.FIRST.. club_tab_arr.LAST
2595: loop
2596: If nvl(club_tab_arr(x6).qty,0) <> 0 then
2597: insert into bom_inventory_comps_interface
2598: (
2599: component_item_id,
2600: bill_sequence_id,
2601: effectivity_date,
2726: plan_level,
2727: revised_item_sequence_id
2728: , nvl(basis_type,1), /* LBM project */
2729: cto_msutil_pub.bom_batch_id
2730: from bom_inventory_comps_interface
2731: where component_item_id = club_comp_rec.item_id
2732: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2733: and bill_sequence_id = pConfigBillId
2734: and rowid = club_tab_arr(x6).row_id;
2736: end loop;
2737: end if;
2738:
2739: -- Delete original option item rows from bici
2740: delete from bom_inventory_comps_interface
2741: where component_item_id = club_comp_rec.item_id
2742: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2743: and bill_sequence_id = pConfigBillId
2744: and created_by <> pConfigBillId;
2742: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2743: and bill_sequence_id = pConfigBillId
2744: and created_by <> pConfigBillId;
2745:
2746: -- Delete rows from bom_inventory_comps_interface where qty = 0
2747: delete from bom_inventory_comps_interface
2748: where component_item_id = club_comp_rec.item_id
2749: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2750: and bill_sequence_id = pConfigBillId
2743: and bill_sequence_id = pConfigBillId
2744: and created_by <> pConfigBillId;
2745:
2746: -- Delete rows from bom_inventory_comps_interface where qty = 0
2747: delete from bom_inventory_comps_interface
2748: where component_item_id = club_comp_rec.item_id
2749: and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2750: and bill_sequence_id = pConfigBillId
2751: and created_by = pConfigBillId
2802: IF PG_DEBUG <> 0 THEN
2803: oe_debug_pub.add('create_bom_data_ml: ' || 'p_item_num::'||to_char(p_item_num), 2);
2804: END IF;
2805:
2806: update bom_inventory_comps_interface
2807: set item_num = p_item_num
2808: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
2809:
2810: IF PG_DEBUG <> 0 THEN
2823: into p_bill_seq_id
2824: from bom_bill_of_materials
2825: where assembly_item_id =
2826: (select component_item_id
2827: from bom_inventory_comps_interface
2828: where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
2829: and organization_id = pOrgId
2830: and alternate_bom_designator is null;
2831:
3173: b.parent_bill_seq_id, --isp bom
3174: b.plan_level, --isp bom
3175: b.model_comp_seq_id --isp bom
3176: , decode(b.basis_type,1,null,b.basis_type) /* LBM Change */
3177: from bom_inventory_comps_interface b , mtl_system_items msi
3178: where b.bill_sequence_id = pConfigBillId
3179: and b.component_item_id = msi.inventory_item_id
3180: and msi.organization_id = pOrgId ;
3181:
3276: ,created_by
3277: ,last_update_login
3278: */
3279:
3280: from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
3281: where b.bill_sequence_id = pConfigBillId
3282: and ABS(b.model_comp_seq_id) = bic.component_sequence_id
3283: and bic.optional = 2 /* only mandatory components */
3284: and bic.component_sequence_id = s.component_sequence_id ;
3811: );
3812:
3813: lStmtNum := 380;
3814:
3815: /* Clean up bom_inventory_comps_interface */
3816:
3817: delete from bom_inventory_comps_interface
3818: where bill_sequence_id = pConfigBillId;
3819:
3813: lStmtNum := 380;
3814:
3815: /* Clean up bom_inventory_comps_interface */
3816:
3817: delete from bom_inventory_comps_interface
3818: where bill_sequence_id = pConfigBillId;
3819:
3820: --Bugfix 11056452
3821: lCnt := sql%rowcount;
3837: WHEN NO_DATA_FOUND THEN
3838: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3839:
3840: -- Sushant Fixed bug #3374548
3841: /* Clean up bom_inventory_comps_interface */
3842: delete from bom_inventory_comps_interface
3843: where bill_sequence_id = pConfigBillId;
3844:
3845: --Bugfix 11056452
3838: xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3839:
3840: -- Sushant Fixed bug #3374548
3841: /* Clean up bom_inventory_comps_interface */
3842: delete from bom_inventory_comps_interface
3843: where bill_sequence_id = pConfigBillId;
3844:
3845: --Bugfix 11056452
3846: lCnt := sql%rowcount;
3866: oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3867: END IF;
3868:
3869: -- Sushant Fixed bug #3374548
3870: /* Clean up bom_inventory_comps_interface */
3871: delete from bom_inventory_comps_interface
3872: where bill_sequence_id = pConfigBillId;
3873:
3874: --Bugfix 11056452
3867: END IF;
3868:
3869: -- Sushant Fixed bug #3374548
3870: /* Clean up bom_inventory_comps_interface */
3871: delete from bom_inventory_comps_interface
3872: where bill_sequence_id = pConfigBillId;
3873:
3874: --Bugfix 11056452
3875: lCnt := sql%rowcount;
3894: oe_debug_pub.add('Unexpected Error: ' || xErrorMessage);
3895: END IF;
3896:
3897: -- Sushant Fixed bug #3374548
3898: /* Clean up bom_inventory_comps_interface */
3899: delete from bom_inventory_comps_interface
3900: where bill_sequence_id = pConfigBillId;
3901:
3902: --Bugfix 11056452
3895: END IF;
3896:
3897: -- Sushant Fixed bug #3374548
3898: /* Clean up bom_inventory_comps_interface */
3899: delete from bom_inventory_comps_interface
3900: where bill_sequence_id = pConfigBillId;
3901:
3902: --Bugfix 11056452
3903: lCnt := sql%rowcount;
3922: oe_debug_pub.add('create_bom_data_ml: ' || 'other Error: ' || xErrorMessage, 1);
3923: END IF;
3924:
3925: -- Sushant Fixed bug #3374548
3926: /* Clean up bom_inventory_comps_interface */
3927: delete from bom_inventory_comps_interface
3928: where bill_sequence_id = pConfigBillId;
3929:
3930: --Bugfix 11056452
3923: END IF;
3924:
3925: -- Sushant Fixed bug #3374548
3926: /* Clean up bom_inventory_comps_interface */
3927: delete from bom_inventory_comps_interface
3928: where bill_sequence_id = pConfigBillId;
3929:
3930: --Bugfix 11056452
3931: lCnt := sql%rowcount;
3963:
3964: CURSOR c_update_item_num(p_parent_bill_seq_id number) IS
3965: select component_sequence_id,
3966: component_item_id
3967: from bom_inventory_comps_interface
3968: where parent_bill_seq_id = p_parent_bill_seq_id
3969: FOR UPDATE OF item_num;
3970:
3971: p_bill_seq_id number;
3981:
3982: --
3983: -- update item_num of child of this model
3984: --
3985: update bom_inventory_comps_interface
3986: set item_num = p_item_num
3987: where current of c_update_item_num;
3988:
3989: IF PG_DEBUG <> 0 THEN
4194: test_var number; --Bug 7418622.FP for 7154767
4195:
4196: cursor debug_cur1 is
4197: select component_item_id,component_sequence_id,operation_seq_num,effectivity_date,disable_date
4198: from bom_inventory_comps_interface
4199: where bill_sequence_id = pConfigBillId --Bugfix 6532151: So that components belonging to this bill only are picked up
4200: order by component_item_id,operation_seq_num,effectivity_Date,disable_date;
4201:
4202: l_bet_op_seq NUMBER;
4413: --Bug 7418622.FP for 7154767
4414: /*update bom_explosion_temp set disable_date = g_futuredate
4415: where ( component_item_id , operation_seq_num, nvl(assembly_item_id,-1) , disable_date) in
4416: ( select component_item_id, operation_seq_num, nvl(assembly_item_id,-1), max(disable_date)
4417: from bom_inventory_comps_interface
4418: where bill_sequence_id = pConfigBillId
4419: group by component_item_id, operation_seq_num, assembly_item_id)
4420: and disable_date <> g_futuredate ;*/
4421:
4907:
4908: lStmtNumber := 550;
4909:
4910: /*Insert into bic interface*/
4911: insert into BOM_INVENTORY_COMPS_INTERFACE
4912: ( operation_seq_num,
4913: component_item_id,
4914: last_update_date,
4915: last_updated_by,
5060: begin
5061: select 1 into v_overlap_check
5062: from dual
5063: where exists
5064: ( select * from bom_inventory_comps_interface
5065: where bill_sequence_id = pConfigBillId
5066: group by component_item_id, assembly_item_id
5067: having count(distinct operation_seq_num) > 1
5068: );
5092: BULK COLLECT INTO
5093: v_t_overlap_comp_item_id,
5094: v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
5095: v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
5096: from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
5097: where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
5098: --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
5099: and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
5100: and s1.effectivity_date < s2.disable_date --Bugfix 6603382