SQL写出带扩展数量的BOM多级清单

 

直接用SQL写出带扩展数量的BOM多级清单。
某项目需要，不能写函数，必须直接用SQL，性能差点没关系。
客户BOM有副产品，也有为0的情况，也就是组件数量有正、负、0，所以情况稍微麻烦了点

 

只用SQL的原因可能为：

因为没有权限去修改和定义程序。
我们唯一能用的手段就是Discover Report，因此需要用SQL来实现多级BOM展开。

 

代码

 1  with  t  as (
 2  SELECT  boms.organization_id,
 3         boms.organization_id  ||   ' > '   ||  connect_by_root assembly_number  ||  sys_connect_by_path(boms.component_number,  ' > ' ) code_chain,
 4         boms.organization_id  ||   ' > '   ||  connect_by_root bill_sequence_id  ||  sys_connect_by_path(boms.component_sequence_id,  ' > ' ) id_chain,
 5         connect_by_root assembly_number assembly_number,
 6         boms.assembly_description,
 7          LEVEL  bom_level,
 8         boms.component_number component_number,
 9         lpad( '   ' , ( LEVEL   -   1 )  *   2 ,  '   ' )  ||  boms.component_number ind_component_number,
10         boms.component_description,
11         boms.primary_uom_code uom,
12         boms.component_quantity component_quantity,
13         boms.planning_factor,
14         boms.component_yield_factor,
15         boms.effectivity_date
16     FROM  ( SELECT  bom1.organization_id,
17                 bom1.assembly_item_id,
18                 mst1.segment1 assembly_number,
19                 mst1.description assembly_description,
20                 bom1.bill_sequence_id bill_sequence_id,
21                 bom1.alternate_bom_designator assembly_alternate,
22                 bomc.component_sequence_id,
23                 bomc.component_item_id,
24                 mstc.segment1 component_number,
25                 mstc.description component_description,
26                 mstc.primary_uom_code,
27                 bomc.component_quantity,
28                 bomc.effectivity_date,
29                 bomc.planning_factor,
30                 bomc.component_yield_factor,
31                 bomc.supply_subinventory
32             FROM  apps.bom_bill_of_materials    bom1,
33                 inv.mtl_system_items_b        mst1,
34                 apps.bom_inventory_components bomc,
35                 inv.mtl_system_items_b        mstc
36            WHERE  bom1.organization_id  =  mst1.organization_id
37              AND  bom1.assembly_item_id  =  mst1.inventory_item_id
38              AND  bom1.bill_sequence_id  =  bomc.bill_sequence_id
39              AND  bom1.organization_id  =  mstc.organization_id
40              AND  bomc.component_item_id  =  mstc.inventory_item_id
41                 -- Item
42              AND  mst1.bom_enabled_flag  =   ' Y '
43              AND  mst1.bom_item_type  IN  ( 1 ,  2 ,  3 ,  4 )  -- Dependent
44                 -- BOM Header
45              AND  bom1.assembly_type  =   1   -- 1 Manufature,2 ENG
46              AND  nvl(bom1.effectivity_control,  1 )  <=   3
47                 -- BOM Line
48              AND  nvl(bomc.disable_date, SYSDATE)  >=  SYSDATE
49              AND  bomc.effectivity_date  <=  SYSDATE
50              AND  bomc.implementation_date  IS   NOT   NULL
51              AND  nvl(bomc.eco_for_production,  2 )  =   2
52                 -- Filters
53              AND  mst1.organization_id  =   207
54              AND  bom1.alternate_bom_designator  IS   NULL ) boms
55  CONNECT  BY  PRIOR boms.organization_id  =  boms.organization_id
56          AND  PRIOR boms.component_item_id  =  boms.assembly_item_id
57  )
58  SELECT  t1.organization_id,
59         t1.code_chain,
60         t1.assembly_number,
61         t1.assembly_description,
62         t1.bom_level,
63         t1.component_number,
64         t1.component_description,
65         t1.uom,
66         t1.ind_component_number,
67         t1.id_chain,
68         t1.component_quantity,
69         ( SELECT   power ( 10 ,
70                        SUM ( CASE
71                              WHEN  t2.component_quantity  =   0   THEN
72                               0
73                              ELSE
74                               log ( 10 ,  abs (t2.component_quantity))
75                            END ))  *  decode(MOD( COUNT (decode( sign (t2.component_quantity),  - 1 ,  1 )),  2 ),  1 ,  - 1 ,  1 )  *
76                 ( CASE
77                     WHEN   COUNT (decode(t2.component_quantity,  0 ,  1 ))  >=   1   THEN
78                      0
79                     ELSE
80                      1
81                   END )
82             FROM  t t2
83            WHERE  t1.id_chain  LIKE  t2.id_chain  ||   ' % ' ) extended_quantity,
84         t1.planning_factor,
85         t1.component_yield_factor,
86         t1.effectivity_date
87     FROM  t t1
88  ORDER   BY  t1.id_chain;

 

 

 

转载：http://bbs.itjaj.com/thread-2488-1-8.html