NC65的BOM物化视图

zhou66

为提升BOM解析查询的效率建立BOM物化视图；脚本如下：

1. create materialized view MVIEW_BOM_EXPAND
   
2. refresh complete on demand
   
3. start with to_date('21-10-2022 22:00:00', 'dd-mm-yyyy hh24:mi:ss') next to_date( concat( to_char( sysdate+1,'dd-mm-yyyy'),' 22:00:00'),'dd-mm-yyyy hh24:mi:ss') /**   创建BOM展开的物化视图MVIEW_BOM_EXPAND，每天晚上10点刷新 */
   
4. as
   
5. select
   
6.       bom.cbomid
   
7.       ,bom.vbillcode
   
8.       ,bom.hversion
   
9.       ,bom.pk_org
   
10.       ,bom.hcmaterialvid
    
11.       ,bom.hnparentnum
    
12.       ,bom.hcmeasureid
    
13.       ,bom.hnassparentnum
    
14.       ,bom.hcassmeasureid
    
15.       ,bomb.cbom_bid
    
16.       ,bomb.cmaterialvid
    
17.       ,bomb.nitemnum / bomb.ibasenum as cnum
    
18.       ,bomb.cmeasureid
    
19.       ,bomb.nassitemnum /bomb.ibasenum as cassnum
    
20.       ,bomb.cassmeasureid
    
21.     from bd_bom bom
    
22.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
23.     where bom.dr=0
    
24.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
    
25.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
    
26.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
    
27. UNION ALL
    
28.     /*第2层BOM*/
    
29.     select
    
30.       bom.cbomid
    
31.       ,bom.vbillcode
    
32.       ,bom.hversion
    
33.       ,bom.pk_org
    
34.       ,bom.hcmaterialvid
    
35.       ,bom.hnparentnum
    
36.       ,bom.hcmeasureid
    
37.       ,bom.hnassparentnum
    
38.       ,bom.hcassmeasureid
    
39.       ,bomb1.cbom_bid
    
40.       ,bomb1.cmaterialvid
    
41.       ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum)  as cnum
    
42.       ,bomb1.cmeasureid
    
43.       ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum) as cassnum
    
44.       ,bomb1.cassmeasureid
    
45.     from bd_bom bom
    
46.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
47.     inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
    
48.           and bom1.hcmeasureid = bomb.cmeasureid
    
49.           and bom1.hcassmeasureid = bomb.cassmeasureid
    
50.           and bom1.dr=0
    
51.           and bom1.fbillstatus = bom.fbillstatus
    
52.           and bom1.fbomtype = bom.fbomtype
    
53.           and bom1.hfversiontype = 1  /*1=有效版本，2=无效版本*/
    
54.           and bom1.hbdefault ='Y'    /*取默认BOM*/
    
55.     inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
    
56.     where bom.dr=0
    
57.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
    
58.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
    
59.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
    
60. 
    
61. UNION ALL
    
62.     /*第3层BOM*/
    
63.     select
    
64.       bom.cbomid
    
65.       ,bom.vbillcode
    
66.       ,bom.hversion
    
67.       ,bom.pk_org
    
68.       ,bom.hcmaterialvid
    
69.       ,bom.hnparentnum
    
70.       ,bom.hcmeasureid
    
71.       ,bom.hnassparentnum
    
72.       ,bom.hcassmeasureid
    
73.       ,bomb2.cbom_bid
    
74.       ,bomb2.cmaterialvid
    
75.       ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum)  as cnum
    
76.       ,bomb2.cmeasureid
    
77.       ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum)  * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum)  as cassnum
    
78.       ,bomb2.cassmeasureid
    
79.     from bd_bom bom
    
80.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
81.     inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
    
82.           and bom1.hcmeasureid = bomb.cmeasureid
    
83.           and bom1.hcassmeasureid = bomb.cassmeasureid
    
84.           and bom1.dr=0
    
85.           and bom1.fbillstatus = bom.fbillstatus
    
86.           and bom1.fbomtype = bom.fbomtype
    
87.           and bom1.hfversiontype = 1  /*1=有效版本，2=无效版本*/
    
88.           and bom1.hbdefault ='Y'    /*取默认BOM*/
    
89.     inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
    
90.     inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
    
91.           and bom2.hcmeasureid = bomb1.cmeasureid
    
92.           and bom2.hcassmeasureid = bomb1.cassmeasureid
    
93.           and bom2.dr=0
    
94.           and bom2.fbillstatus = bom.fbillstatus
    
95.           and bom2.fbomtype = bom.fbomtype
    
96.           and bom2.hfversiontype = 1  /*1=有效版本，2=无效版本*/
    
97.           and bom2.hbdefault ='Y'    /*取默认BOM*/
    
98.     inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
    
99.     where bom.dr=0
    
100.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
101.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
102.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
     
103. UNION ALL
     
104.     /*第4层BOM*/
     
105.     select
     
106.       bom.cbomid
     
107.       ,bom.vbillcode
     
108.       ,bom.hversion
     
109.       ,bom.pk_org
     
110.       ,bom.hcmaterialvid
     
111.       ,bom.hnparentnum
     
112.       ,bom.hcmeasureid
     
113.       ,bom.hnassparentnum
     
114.       ,bom.hcassmeasureid
     
115.       ,bomb3.cbom_bid
     
116.       ,bomb3.cmaterialvid
     
117.       ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum) as cnum
     
118.       ,bomb3.cmeasureid
     
119.       ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum)  * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum)  * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) as cassnum
     
120.       ,bomb3.cassmeasureid
     
121.     from bd_bom bom
     
122.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
     
123.     inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
124.           and bom1.hcmeasureid = bomb.cmeasureid
     
125.           and bom1.hcassmeasureid = bomb.cassmeasureid
     
126.           and bom1.dr=0
     
127.           and bom1.fbillstatus = bom.fbillstatus
     
128.           and bom1.fbomtype = bom.fbomtype
     
129.           and bom1.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
130.           and bom1.hbdefault ='Y'    /*取默认BOM*/
     
131.     inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
     
132.     inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
133.           and bom2.hcmeasureid = bomb1.cmeasureid
     
134.           and bom2.hcassmeasureid = bomb1.cassmeasureid
     
135.           and bom2.dr=0
     
136.           and bom2.fbillstatus = bom.fbillstatus
     
137.           and bom2.fbomtype = bom.fbomtype
     
138.           and bom2.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
139.           and bom2.hbdefault ='Y'    /*取默认BOM*/
     
140.     inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
     
141.     inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
142.           and bom3.hcmeasureid = bomb2.cmeasureid
     
143.           and bom3.hcassmeasureid = bomb2.cassmeasureid
     
144.           and bom3.dr=0
     
145.           and bom3.fbillstatus = bom.fbillstatus
     
146.           and bom3.fbomtype = bom.fbomtype
     
147.           and bom3.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
148.           and bom3.hbdefault ='Y'    /*取默认BOM*/
     
149.     inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
     
150.     where bom.dr=0
     
151.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
152.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
153.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
     
154. UNION ALL
     
155.     /*第5层BOM*/
     
156.     select
     
157.       bom.cbomid
     
158.       ,bom.vbillcode
     
159.       ,bom.hversion
     
160.       ,bom.pk_org
     
161.       ,bom.hcmaterialvid
     
162.       ,bom.hnparentnum
     
163.       ,bom.hcmeasureid
     
164.       ,bom.hnassparentnum
     
165.       ,bom.hcassmeasureid
     
166.       ,bomb4.cbom_bid
     
167.       ,bomb4.cmaterialvid
     
168.       ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum)  * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum) as cnum
     
169.       ,bomb4.cmeasureid
     
170.       ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum)  * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum)  * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum) as cassnum
     
171.       ,bomb4.cassmeasureid
     
172.     from bd_bom bom
     
173.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
     
174.     inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
175.           and bom1.hcmeasureid = bomb.cmeasureid
     
176.           and bom1.hcassmeasureid = bomb.cassmeasureid
     
177.           and bom1.dr=0
     
178.           and bom1.fbillstatus = bom.fbillstatus
     
179.           and bom1.fbomtype = bom.fbomtype
     
180.           and bom1.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
181.           and bom1.hbdefault ='Y'    /*取默认BOM*/
     
182.     inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
     
183.     inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
184.           and bom2.hcmeasureid = bomb1.cmeasureid
     
185.           and bom2.hcassmeasureid = bomb1.cassmeasureid
     
186.           and bom2.dr=0
     
187.           and bom2.fbillstatus = bom.fbillstatus
     
188.           and bom2.fbomtype = bom.fbomtype
     
189.           and bom2.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
190.           and bom2.hbdefault ='Y'    /*取默认BOM*/
     
191.     inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
     
192.     inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
193.           and bom3.hcmeasureid = bomb2.cmeasureid
     
194.           and bom3.hcassmeasureid = bomb2.cassmeasureid
     
195.           and bom3.dr=0
     
196.           and bom3.fbillstatus = bom.fbillstatus
     
197.           and bom3.fbomtype = bom.fbomtype
     
198.           and bom3.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
199.           and bom3.hbdefault ='Y'    /*取默认BOM*/
     
200.     inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
     
201.     inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
202.           and bom4.hcmeasureid = bomb3.cmeasureid
     
203.           and bom4.hcassmeasureid = bomb3.cassmeasureid
     
204.           and bom4.dr=0
     
205.           and bom4.fbillstatus = bom.fbillstatus
     
206.           and bom4.fbomtype = bom.fbomtype
     
207.           and bom4.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
208.           and bom4.hbdefault ='Y'    /*取默认BOM*/
     
209.     inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
     
210.     where bom.dr=0
     
211.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
212.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
213.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
     
214. UNION ALL
     
215.     /*第6层BOM*/
     
216.     select
     
217.       bom.cbomid
     
218.       ,bom.vbillcode
     
219.       ,bom.hversion
     
220.       ,bom.pk_org
     
221.       ,bom.hcmaterialvid
     
222.       ,bom.hnparentnum
     
223.       ,bom.hcmeasureid
     
224.       ,bom.hnassparentnum
     
225.       ,bom.hcassmeasureid
     
226.       ,bomb5.cbom_bid
     
227.       ,bomb5.cmaterialvid
     
228.       ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum)  * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum)  * ((bomb5.nitemnum / bomb5.ibasenum) / bom5.hnparentnum) as cnum
     
229.       ,bomb5.cmeasureid
     
230.       ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum)  * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum)  * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum)  * ((bomb5.nassitemnum /bomb5.ibasenum) / bom5.hnassparentnum) as cassnum
     
231.       ,bomb5.cassmeasureid
     
232.     from bd_bom bom
     
233.     inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
     
234.     inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
235.           and bom1.hcmeasureid = bomb.cmeasureid
     
236.           and bom1.hcassmeasureid = bomb.cassmeasureid
     
237.           and bom1.dr=0
     
238.           and bom1.fbillstatus = bom.fbillstatus
     
239.           and bom1.fbomtype = bom.fbomtype
     
240.           and bom1.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
241.           and bom1.hbdefault ='Y'    /*取默认BOM*/
     
242.     inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
     
243.     inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
244.           and bom2.hcmeasureid = bomb1.cmeasureid
     
245.           and bom2.hcassmeasureid = bomb1.cassmeasureid
     
246.           and bom2.dr=0
     
247.           and bom2.fbillstatus = bom.fbillstatus
     
248.           and bom2.fbomtype = bom.fbomtype
     
249.           and bom2.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
250.           and bom2.hbdefault ='Y'    /*取默认BOM*/
     
251.     inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
     
252.     inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
253.           and bom3.hcmeasureid = bomb2.cmeasureid
     
254.           and bom3.hcassmeasureid = bomb2.cassmeasureid
     
255.           and bom3.dr=0
     
256.           and bom3.fbillstatus = bom.fbillstatus
     
257.           and bom3.fbomtype = bom.fbomtype
     
258.           and bom3.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
259.           and bom3.hbdefault ='Y'    /*取默认BOM*/
     
260.     inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
     
261.     inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
262.           and bom4.hcmeasureid = bomb3.cmeasureid
     
263.           and bom4.hcassmeasureid = bomb3.cassmeasureid
     
264.           and bom4.dr=0
     
265.           and bom4.fbillstatus = bom.fbillstatus
     
266.           and bom4.fbomtype = bom.fbomtype
     
267.           and bom4.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
268.           and bom4.hbdefault ='Y'    /*取默认BOM*/
     
269.     inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
     
270.     inner join bd_bom bom5 on bomb4.cmaterialvid = bom5.hcmaterialvid   /*物料相等，且计量单位相同的默认BOM*/
     
271.           and bom5.hcmeasureid = bomb4.cmeasureid
     
272.           and bom5.hcassmeasureid = bomb4.cassmeasureid
     
273.           and bom5.dr=0
     
274.           and bom5.fbillstatus = bom.fbillstatus
     
275.           and bom5.fbomtype = bom.fbomtype
     
276.           and bom5.hfversiontype = 1  /*1=有效版本，2=无效版本*/
     
277.           and bom5.hbdefault ='Y'    /*取默认BOM*/
     
278.     inner join bd_bom_b bomb5 on bomb5.cbomid = bom5.cbomid
     
279.     where bom.dr=0
     
280.           and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
281.           and bom.fbomtype =  1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
282.           and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
     
283. UNION ALL
     
284. 
     

复制代码

zhou66

接上：

1. /*第7层BOM*/
   
2. select
   
3. bom.cbomid
   
4. ,bom.vbillcode
   
5. ,bom.hversion
   
6. ,bom.pk_org
   
7. ,bom.hcmaterialvid
   
8. ,bom.hnparentnum
   
9. ,bom.hcmeasureid
   
10. ,bom.hnassparentnum
    
11. ,bom.hcassmeasureid
    
12. ,bomb6.cbom_bid
    
13. ,bomb6.cmaterialvid
    
14. ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum) * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum) * ((bomb5.nitemnum / bomb5.ibasenum) / bom5.hnparentnum) * ((bomb6.nitemnum / bomb6.ibasenum) / bom6.hnparentnum) as cnum
    
15. ,bomb6.cmeasureid
    
16. ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum) * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum) * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum) * ((bomb5.nassitemnum /bomb5.ibasenum) / bom5.hnassparentnum) * ((bomb6.nassitemnum /bomb6.ibasenum) / bom6.hnassparentnum) as cassnum
    
17. ,bomb6.cassmeasureid
    
18. from bd_bom bom
    
19. inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
20. inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
21. and bom1.hcmeasureid = bomb.cmeasureid
    
22. and bom1.hcassmeasureid = bomb.cassmeasureid
    
23. and bom1.dr=0
    
24. and bom1.fbillstatus = bom.fbillstatus
    
25. and bom1.fbomtype = bom.fbomtype
    
26. and bom1.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
27. and bom1.hbdefault ='Y' /*取默认BOM*/
    
28. inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
    
29. inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
30. and bom2.hcmeasureid = bomb1.cmeasureid
    
31. and bom2.hcassmeasureid = bomb1.cassmeasureid
    
32. and bom2.dr=0
    
33. and bom2.fbillstatus = bom.fbillstatus
    
34. and bom2.fbomtype = bom.fbomtype
    
35. and bom2.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
36. and bom2.hbdefault ='Y' /*取默认BOM*/
    
37. inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
    
38. inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
39. and bom3.hcmeasureid = bomb2.cmeasureid
    
40. and bom3.hcassmeasureid = bomb2.cassmeasureid
    
41. and bom3.dr=0
    
42. and bom3.fbillstatus = bom.fbillstatus
    
43. and bom3.fbomtype = bom.fbomtype
    
44. and bom3.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
45. and bom3.hbdefault ='Y' /*取默认BOM*/
    
46. inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
    
47. inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
48. and bom4.hcmeasureid = bomb3.cmeasureid
    
49. and bom4.hcassmeasureid = bomb3.cassmeasureid
    
50. and bom4.dr=0
    
51. and bom4.fbillstatus = bom.fbillstatus
    
52. and bom4.fbomtype = bom.fbomtype
    
53. and bom4.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
54. and bom4.hbdefault ='Y' /*取默认BOM*/
    
55. inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
    
56. inner join bd_bom bom5 on bomb4.cmaterialvid = bom5.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
57. and bom5.hcmeasureid = bomb4.cmeasureid
    
58. and bom5.hcassmeasureid = bomb4.cassmeasureid
    
59. and bom5.dr=0
    
60. and bom5.fbillstatus = bom.fbillstatus
    
61. and bom5.fbomtype = bom.fbomtype
    
62. and bom5.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
63. and bom5.hbdefault ='Y' /*取默认BOM*/
    
64. inner join bd_bom_b bomb5 on bomb5.cbomid = bom5.cbomid
    
65. inner join bd_bom bom6 on bomb5.cmaterialvid = bom6.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
66. and bom6.hcmeasureid = bomb5.cmeasureid
    
67. and bom6.hcassmeasureid = bomb5.cassmeasureid
    
68. and bom6.dr=0
    
69. and bom6.fbillstatus = bom.fbillstatus
    
70. and bom6.fbomtype = bom.fbomtype
    
71. and bom6.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
72. and bom6.hbdefault ='Y' /*取默认BOM*/
    
73. inner join bd_bom_b bomb6 on bomb6.cbomid = bom6.cbomid
    
74. where bom.dr=0
    
75. and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
    
76. and bom.fbomtype = 1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
    
77. and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
    
78. UNION ALL
    
79. /*第8层BOM*/
    
80. select
    
81. bom.cbomid
    
82. ,bom.vbillcode
    
83. ,bom.hversion
    
84. ,bom.pk_org
    
85. ,bom.hcmaterialvid
    
86. ,bom.hnparentnum
    
87. ,bom.hcmeasureid
    
88. ,bom.hnassparentnum
    
89. ,bom.hcassmeasureid
    
90. ,bomb7.cbom_bid
    
91. ,bomb7.cmaterialvid
    
92. ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum) * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum) * ((bomb5.nitemnum / bomb5.ibasenum) / bom5.hnparentnum) * ((bomb6.nitemnum / bomb6.ibasenum) / bom6.hnparentnum) * ((bomb7.nitemnum / bomb7.ibasenum) / bom7.hnparentnum) as cnum
    
93. ,bomb7.cmeasureid
    
94. ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum) * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum) * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum) * ((bomb5.nassitemnum /bomb5.ibasenum) / bom5.hnassparentnum) * ((bomb6.nassitemnum /bomb6.ibasenum) / bom6.hnassparentnum) * ((bomb7.nassitemnum /bomb7.ibasenum) / bom7.hnassparentnum) as cassnum
    
95. ,bomb7.cassmeasureid
    
96. from bd_bom bom
    
97. inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
98. inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
99. and bom1.hcmeasureid = bomb.cmeasureid
    
100. and bom1.hcassmeasureid = bomb.cassmeasureid
     
101. and bom1.dr=0
     
102. and bom1.fbillstatus = bom.fbillstatus
     
103. and bom1.fbomtype = bom.fbomtype
     
104. and bom1.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
105. and bom1.hbdefault ='Y' /*取默认BOM*/
     
106. inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
     
107. inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
108. and bom2.hcmeasureid = bomb1.cmeasureid
     
109. and bom2.hcassmeasureid = bomb1.cassmeasureid
     
110. and bom2.dr=0
     
111. and bom2.fbillstatus = bom.fbillstatus
     
112. and bom2.fbomtype = bom.fbomtype
     
113. and bom2.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
114. and bom2.hbdefault ='Y' /*取默认BOM*/
     
115. inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
     
116. inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
117. and bom3.hcmeasureid = bomb2.cmeasureid
     
118. and bom3.hcassmeasureid = bomb2.cassmeasureid
     
119. and bom3.dr=0
     
120. and bom3.fbillstatus = bom.fbillstatus
     
121. and bom3.fbomtype = bom.fbomtype
     
122. and bom3.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
123. and bom3.hbdefault ='Y' /*取默认BOM*/
     
124. inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
     
125. inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
126. and bom4.hcmeasureid = bomb3.cmeasureid
     
127. and bom4.hcassmeasureid = bomb3.cassmeasureid
     
128. and bom4.dr=0
     
129. and bom4.fbillstatus = bom.fbillstatus
     
130. and bom4.fbomtype = bom.fbomtype
     
131. and bom4.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
132. and bom4.hbdefault ='Y' /*取默认BOM*/
     
133. inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
     
134. inner join bd_bom bom5 on bomb4.cmaterialvid = bom5.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
135. and bom5.hcmeasureid = bomb4.cmeasureid
     
136. and bom5.hcassmeasureid = bomb4.cassmeasureid
     
137. and bom5.dr=0
     
138. and bom5.fbillstatus = bom.fbillstatus
     
139. and bom5.fbomtype = bom.fbomtype
     
140. and bom5.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
141. and bom5.hbdefault ='Y' /*取默认BOM*/
     
142. inner join bd_bom_b bomb5 on bomb5.cbomid = bom5.cbomid
     
143. inner join bd_bom bom6 on bomb5.cmaterialvid = bom6.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
144. and bom6.hcmeasureid = bomb5.cmeasureid
     
145. and bom6.hcassmeasureid = bomb5.cassmeasureid
     
146. and bom6.dr=0
     
147. and bom6.fbillstatus = bom.fbillstatus
     
148. and bom6.fbomtype = bom.fbomtype
     
149. and bom6.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
150. and bom6.hbdefault ='Y' /*取默认BOM*/
     
151. inner join bd_bom_b bomb6 on bomb6.cbomid = bom6.cbomid
     
152. inner join bd_bom bom7 on bomb6.cmaterialvid = bom7.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
153. and bom7.hcmeasureid = bomb6.cmeasureid
     
154. and bom7.hcassmeasureid = bomb6.cassmeasureid
     
155. and bom7.dr=0
     
156. and bom7.fbillstatus = bom.fbillstatus
     
157. and bom7.fbomtype = bom.fbomtype
     
158. and bom7.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
159. and bom7.hbdefault ='Y' /*取默认BOM*/
     
160. inner join bd_bom_b bomb7 on bomb7.cbomid = bom7.cbomid
     
161. where bom.dr=0
     
162. and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
163. and bom.fbomtype = 1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
164. and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
     
165. UNION ALL
     

复制代码

zhou66

接上：

1. /*第9层BOM*/
   
2. select
   
3. bom.cbomid
   
4. ,bom.vbillcode
   
5. ,bom.hversion
   
6. ,bom.pk_org
   
7. ,bom.hcmaterialvid
   
8. ,bom.hnparentnum
   
9. ,bom.hcmeasureid
   
10. ,bom.hnassparentnum
    
11. ,bom.hcassmeasureid
    
12. ,bomb8.cbom_bid
    
13. ,bomb8.cmaterialvid
    
14. ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum) * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum) * ((bomb5.nitemnum / bomb5.ibasenum) / bom5.hnparentnum) * ((bomb6.nitemnum / bomb6.ibasenum) / bom6.hnparentnum) * ((bomb7.nitemnum / bomb7.ibasenum) / bom7.hnparentnum) * ((bomb8.nitemnum / bomb8.ibasenum) / bom8.hnparentnum) as cnum
    
15. ,bomb8.cmeasureid
    
16. ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum) * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum) * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum) * ((bomb5.nassitemnum /bomb5.ibasenum) / bom5.hnassparentnum) * ((bomb6.nassitemnum /bomb6.ibasenum) / bom6.hnassparentnum) * ((bomb7.nassitemnum /bomb7.ibasenum) / bom7.hnassparentnum) * ((bomb8.nassitemnum /bomb8.ibasenum) / bom8.hnassparentnum) as cassnum
    
17. ,bomb8.cassmeasureid
    
18. from bd_bom bom
    
19. inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
    
20. inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
21. and bom1.hcmeasureid = bomb.cmeasureid
    
22. and bom1.hcassmeasureid = bomb.cassmeasureid
    
23. and bom1.dr=0
    
24. and bom1.fbillstatus = bom.fbillstatus
    
25. and bom1.fbomtype = bom.fbomtype
    
26. and bom1.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
27. and bom1.hbdefault ='Y' /*取默认BOM*/
    
28. inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
    
29. inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
30. and bom2.hcmeasureid = bomb1.cmeasureid
    
31. and bom2.hcassmeasureid = bomb1.cassmeasureid
    
32. and bom2.dr=0
    
33. and bom2.fbillstatus = bom.fbillstatus
    
34. and bom2.fbomtype = bom.fbomtype
    
35. and bom2.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
36. and bom2.hbdefault ='Y' /*取默认BOM*/
    
37. inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
    
38. inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
39. and bom3.hcmeasureid = bomb2.cmeasureid
    
40. and bom3.hcassmeasureid = bomb2.cassmeasureid
    
41. and bom3.dr=0
    
42. and bom3.fbillstatus = bom.fbillstatus
    
43. and bom3.fbomtype = bom.fbomtype
    
44. and bom3.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
45. and bom3.hbdefault ='Y' /*取默认BOM*/
    
46. inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
    
47. inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
48. and bom4.hcmeasureid = bomb3.cmeasureid
    
49. and bom4.hcassmeasureid = bomb3.cassmeasureid
    
50. and bom4.dr=0
    
51. and bom4.fbillstatus = bom.fbillstatus
    
52. and bom4.fbomtype = bom.fbomtype
    
53. and bom4.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
54. and bom4.hbdefault ='Y' /*取默认BOM*/
    
55. inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
    
56. inner join bd_bom bom5 on bomb4.cmaterialvid = bom5.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
57. and bom5.hcmeasureid = bomb4.cmeasureid
    
58. and bom5.hcassmeasureid = bomb4.cassmeasureid
    
59. and bom5.dr=0
    
60. and bom5.fbillstatus = bom.fbillstatus
    
61. and bom5.fbomtype = bom.fbomtype
    
62. and bom5.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
63. and bom5.hbdefault ='Y' /*取默认BOM*/
    
64. inner join bd_bom_b bomb5 on bomb5.cbomid = bom5.cbomid
    
65. inner join bd_bom bom6 on bomb5.cmaterialvid = bom6.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
66. and bom6.hcmeasureid = bomb5.cmeasureid
    
67. and bom6.hcassmeasureid = bomb5.cassmeasureid
    
68. and bom6.dr=0
    
69. and bom6.fbillstatus = bom.fbillstatus
    
70. and bom6.fbomtype = bom.fbomtype
    
71. and bom6.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
72. and bom6.hbdefault ='Y' /*取默认BOM*/
    
73. inner join bd_bom_b bomb6 on bomb6.cbomid = bom6.cbomid
    
74. inner join bd_bom bom7 on bomb6.cmaterialvid = bom7.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
75. and bom7.hcmeasureid = bomb6.cmeasureid
    
76. and bom7.hcassmeasureid = bomb6.cassmeasureid
    
77. and bom7.dr=0
    
78. and bom7.fbillstatus = bom.fbillstatus
    
79. and bom7.fbomtype = bom.fbomtype
    
80. and bom7.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
81. and bom7.hbdefault ='Y' /*取默认BOM*/
    
82. inner join bd_bom_b bomb7 on bomb7.cbomid = bom7.cbomid
    
83. inner join bd_bom bom8 on bomb7.cmaterialvid = bom8.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
    
84. and bom8.hcmeasureid = bomb7.cmeasureid
    
85. and bom8.hcassmeasureid = bomb7.cassmeasureid
    
86. and bom8.dr=0
    
87. and bom8.fbillstatus = bom.fbillstatus
    
88. and bom8.fbomtype = bom.fbomtype
    
89. and bom8.hfversiontype = 1 /*1=有效版本，2=无效版本*/
    
90. and bom8.hbdefault ='Y' /*取默认BOM*/
    
91. inner join bd_bom_b bomb8 on bomb8.cbomid = bom8.cbomid
    
92. where bom.dr=0
    
93. and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
    
94. and bom.fbomtype = 1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
    
95. and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/
    
96. UNION ALL
    
97. /*第10层BOM*/
    
98. select
    
99. bom.cbomid
    
100. ,bom.vbillcode
     
101. ,bom.hversion
     
102. ,bom.pk_org
     
103. ,bom.hcmaterialvid
     
104. ,bom.hnparentnum
     
105. ,bom.hcmeasureid
     
106. ,bom.hnassparentnum
     
107. ,bom.hcassmeasureid
     
108. ,bomb9.cbom_bid
     
109. ,bomb9.cmaterialvid
     
110. ,(bomb.nitemnum / bomb.ibasenum) * ((bomb1.nitemnum / bomb1.ibasenum) / bom1.hnparentnum) * ((bomb2.nitemnum / bomb2.ibasenum) / bom2.hnparentnum) * ((bomb3.nitemnum / bomb3.ibasenum) / bom3.hnparentnum) * ((bomb4.nitemnum / bomb4.ibasenum) / bom4.hnparentnum) * ((bomb5.nitemnum / bomb5.ibasenum) / bom5.hnparentnum) * ((bomb6.nitemnum / bomb6.ibasenum) / bom6.hnparentnum) * ((bomb7.nitemnum / bomb7.ibasenum) / bom7.hnparentnum) * ((bomb8.nitemnum / bomb8.ibasenum) / bom8.hnparentnum) * ((bomb9.nitemnum / bomb9.ibasenum) / bom9.hnparentnum) as cnum
     
111. ,bomb9.cmeasureid
     
112. ,(bomb.nassitemnum /bomb.ibasenum) * ((bomb1.nassitemnum /bomb1.ibasenum) / bom1.hnassparentnum) * ((bomb2.nassitemnum /bomb2.ibasenum) / bom2.hnassparentnum) * ((bomb3.nassitemnum /bomb3.ibasenum) / bom3.hnassparentnum) * ((bomb4.nassitemnum /bomb4.ibasenum) / bom4.hnassparentnum) * ((bomb5.nassitemnum /bomb5.ibasenum) / bom5.hnassparentnum) * ((bomb6.nassitemnum /bomb6.ibasenum) / bom6.hnassparentnum) * ((bomb7.nassitemnum /bomb7.ibasenum) / bom7.hnassparentnum) * ((bomb8.nassitemnum /bomb8.ibasenum) / bom8.hnassparentnum) * ((bomb9.nassitemnum /bomb9.ibasenum) / bom9.hnassparentnum) as cassnum
     
113. ,bomb9.cassmeasureid
     
114. from bd_bom bom
     
115. inner join bd_bom_b bomb on bomb.cbomid= bom.cbomid
     
116. inner join bd_bom bom1 on bomb.cmaterialvid = bom1.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
117. and bom1.hcmeasureid = bomb.cmeasureid
     
118. and bom1.hcassmeasureid = bomb.cassmeasureid
     
119. and bom1.dr=0
     
120. and bom1.fbillstatus = bom.fbillstatus
     
121. and bom1.fbomtype = bom.fbomtype
     
122. and bom1.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
123. and bom1.hbdefault ='Y' /*取默认BOM*/
     
124. inner join bd_bom_b bomb1 on bomb1.cbomid = bom1.cbomid
     
125. inner join bd_bom bom2 on bomb1.cmaterialvid = bom2.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
126. and bom2.hcmeasureid = bomb1.cmeasureid
     
127. and bom2.hcassmeasureid = bomb1.cassmeasureid
     
128. and bom2.dr=0
     
129. and bom2.fbillstatus = bom.fbillstatus
     
130. and bom2.fbomtype = bom.fbomtype
     
131. and bom2.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
132. and bom2.hbdefault ='Y' /*取默认BOM*/
     
133. inner join bd_bom_b bomb2 on bomb2.cbomid = bom2.cbomid
     
134. inner join bd_bom bom3 on bomb2.cmaterialvid = bom3.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
135. and bom3.hcmeasureid = bomb2.cmeasureid
     
136. and bom3.hcassmeasureid = bomb2.cassmeasureid
     
137. and bom3.dr=0
     
138. and bom3.fbillstatus = bom.fbillstatus
     
139. and bom3.fbomtype = bom.fbomtype
     
140. and bom3.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
141. and bom3.hbdefault ='Y' /*取默认BOM*/
     
142. inner join bd_bom_b bomb3 on bomb3.cbomid = bom3.cbomid
     
143. inner join bd_bom bom4 on bomb3.cmaterialvid = bom4.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
144. and bom4.hcmeasureid = bomb3.cmeasureid
     
145. and bom4.hcassmeasureid = bomb3.cassmeasureid
     
146. and bom4.dr=0
     
147. and bom4.fbillstatus = bom.fbillstatus
     
148. and bom4.fbomtype = bom.fbomtype
     
149. and bom4.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
150. and bom4.hbdefault ='Y' /*取默认BOM*/
     
151. inner join bd_bom_b bomb4 on bomb4.cbomid = bom4.cbomid
     
152. inner join bd_bom bom5 on bomb4.cmaterialvid = bom5.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
153. and bom5.hcmeasureid = bomb4.cmeasureid
     
154. and bom5.hcassmeasureid = bomb4.cassmeasureid
     
155. and bom5.dr=0
     
156. and bom5.fbillstatus = bom.fbillstatus
     
157. and bom5.fbomtype = bom.fbomtype
     
158. and bom5.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
159. and bom5.hbdefault ='Y' /*取默认BOM*/
     
160. inner join bd_bom_b bomb5 on bomb5.cbomid = bom5.cbomid
     
161. inner join bd_bom bom6 on bomb5.cmaterialvid = bom6.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
162. and bom6.hcmeasureid = bomb5.cmeasureid
     
163. and bom6.hcassmeasureid = bomb5.cassmeasureid
     
164. and bom6.dr=0
     
165. and bom6.fbillstatus = bom.fbillstatus
     
166. and bom6.fbomtype = bom.fbomtype
     
167. and bom6.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
168. and bom6.hbdefault ='Y' /*取默认BOM*/
     
169. inner join bd_bom_b bomb6 on bomb6.cbomid = bom6.cbomid
     
170. inner join bd_bom bom7 on bomb6.cmaterialvid = bom7.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
171. and bom7.hcmeasureid = bomb6.cmeasureid
     
172. and bom7.hcassmeasureid = bomb6.cassmeasureid
     
173. and bom7.dr=0
     
174. and bom7.fbillstatus = bom.fbillstatus
     
175. and bom7.fbomtype = bom.fbomtype
     
176. and bom7.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
177. and bom7.hbdefault ='Y' /*取默认BOM*/
     
178. inner join bd_bom_b bomb7 on bomb7.cbomid = bom7.cbomid
     
179. inner join bd_bom bom8 on bomb7.cmaterialvid = bom8.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
180. and bom8.hcmeasureid = bomb7.cmeasureid
     
181. and bom8.hcassmeasureid = bomb7.cassmeasureid
     
182. and bom8.dr=0
     
183. and bom8.fbillstatus = bom.fbillstatus
     
184. and bom8.fbomtype = bom.fbomtype
     
185. and bom8.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
186. and bom8.hbdefault ='Y' /*取默认BOM*/
     
187. inner join bd_bom_b bomb8 on bomb8.cbomid = bom8.cbomid
     
188. inner join bd_bom bom9 on bomb8.cmaterialvid = bom9.hcmaterialvid /*物料相等，且计量单位相同的默认BOM*/
     
189. and bom9.hcmeasureid = bomb8.cmeasureid
     
190. and bom9.hcassmeasureid = bomb8.cassmeasureid
     
191. and bom9.dr=0
     
192. and bom9.fbillstatus = bom.fbillstatus
     
193. and bom9.fbomtype = bom.fbomtype
     
194. and bom9.hfversiontype = 1 /*1=有效版本，2=无效版本*/
     
195. and bom9.hbdefault ='Y' /*取默认BOM*/
     
196. inner join bd_bom_b bomb9 on bomb9.cbomid = bom9.cbomid
     
197. where bom.dr=0
     
198. and bom.fbillstatus = 1 /*-1=自由，0=审批不通过，1=审批通过，2=审批中*/
     
199. and bom.fbomtype = 1 /*1=生产BOM，2=包装BOM，3=配置BOM*/
     
200. and bom.hfversiontype =1 /*1=有效版本，2=无效版本*/;

复制代码