• 金蝶云星空SQL 84.89 kB 2024-06-03 22:06
  • 位置: 金蝶云星空SQL / 递归展开金蝶星空BOM.sql

    --�ݹ�CTEչ�����ǿ�BOM
    
    ;with cte as
    (
    	--1����ѯ����BOM
    		select 
    		0 as BOM���,t1.fid as ���BOM����
    		,t1.FNUMBER as BOM�汾,fxwl.FNUMBER as ������ϴ���,fxwl_L.FNAME as ����������,t3.FSEQ as ��¼�к�
    		,t3.FREPLACEGROUP as ���,CAST(10000+t3.FREPLACEGROUP AS nvarchar) as ������
    		,cast(CAST(t1.fid AS nvarchar)+'-'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar(max)) as BOM�����������
    		,t3.FMATERIALID as �����������,zxwl.FNUMBER as ������ϴ���,zxwl_L.FNAME as ����������
    		,case when FMATERIALTYPE = 1 then '��׼��'
    				when FMATERIALTYPE = 2 then '������' 
    				when FMATERIALTYPE = 3 then '����' 
    				else 'δ֪����' end as �������
    		,t3.FNUMERATOR as ����,t3.FDENOMINATOR as ��ĸ,t3.FFIXSCRAPQTY as �̶����,t3.FSCRAPRATE as �䶯���,t3.FBOMID,t1.FUSEORGID
    		,0 as �Ƿ�������BOM�汾
    
    		--SUN��Ӳ���,�����ѯ
    		,t1.FMATERIALID as TOP_MATERIALID,zxwl.FMATERIALID as MATERIALID
    		,zxwl.FNUMBER as MATERIALNUMBER,zxwl_L.FNAME as MATERIALNAME,zxwl_L.FSPECIFICATION as MATERIALSPECIFICATION ,zxwl_L.FDESCRIPTION as MATERIALDESCRIPTION
    		,case when t3.FDENOMINATOR=0 then 0
    		      else t3.FNUMERATOR/t3.FDENOMINATOR end as DOSAGE
    
    
    	from dbo.T_ENG_BOM t1 join T_BD_MATERIAL fxwl on fxwl.FMATERIALID = t1.FMATERIALID                  --�ø�����BOM��
    			and t1.FFORBIDSTATUS = 'A'	                                                                --δ����״̬��BOM
    			and t1.FDOCUMENTSTATUS='C'	                                                                --�����״̬��BOM
    		join T_BD_MATERIAL_L fxwl_L on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052 --�ø�������϶����Ա�
    		join T_BD_MATERIALPRODUCE fxwl_P on fxwl_P.FMATERIALID = fxwl.FMATERIALID                       --���-���ҳǩ��'
    		join T_ENG_BOMCHILD t3 on t1.fid = t3.FID	                                                    --��������ϸ	
    		join T_BD_MATERIAL zxwl on zxwl.FMATERIALID = t3.FMATERIALID                                    --��������ϱ�	
    		join T_BD_MATERIAL_L zxwl_L on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052 --����������϶����Ա�
    	where 1=1
    		and fxwl_P.FISMAINPRD = 1 
     
    	union all
     
    	--2���ݹ��ѯ
    	select  
    		p.BOM���+1 as BOM���,P.���BOM���� as ���BOM����
    		,t1.FNUMBER as BOM�汾,fxwl.FNUMBER as ������ϴ���,fxwl_L.FNAME as ����������,t3.FSEQ as ��¼�к�
    		,t3.FREPLACEGROUP as ���,cast(p.������+'.'+CAST(10000+t3.FREPLACEGROUP AS nvarchar) as nvarchar) as ������
    		,cast(p.BOM����������� +'.'+ ( CAST(t1.FID AS nvarchar) + '-' +CAST(10000+t3.FREPLACEGROUP AS nvarchar) ) as nvarchar(max))  as BOM�������
    		,t3.FMATERIALID as �����������,zxwl.FNUMBER as ������ϴ���,zxwl_L.FNAME as ����������
    		,case when FMATERIALTYPE = 1 then '��׼��'
    				when FMATERIALTYPE = 2 then '������' 
    				when FMATERIALTYPE = 3 then '����' 
    				else 'δ֪����' end as �������
    		,t3.FNUMERATOR as ����,t3.FDENOMINATOR as ��ĸ,t3.FFIXSCRAPQTY as �̶����,t3.FSCRAPRATE as �䶯���,t3.FBOMID,t1.FUSEORGID
    		,case when p.FBOMID = t1.FID then 1 else 0 end as �Ƿ�������BOM�汾
    
    		--SUN��Ӳ���
    		,P.TOP_MATERIALID,zxwl.FMATERIALID as MATERIALID
    		,zxwl.FNUMBER as MATERIALNUMBER,zxwl_L.FNAME as MATERIALNAME,zxwl_L.FSPECIFICATION as MATERIALSPECIFICATION ,zxwl_L.FDESCRIPTION as MATERIALDESCRIPTION
    		,case when t3.FDENOMINATOR=0 then 0
    		      else t3.FNUMERATOR/t3.FDENOMINATOR end as DOSAGE
    
    	from cte P		                                                                                    --���õݹ�CTE����
    		join dbo.T_ENG_BOM t1 on t1.FMATERIALID = p.�����������                                        --���BOM��
    		join T_BD_MATERIAL fxwl on fxwl.FMATERIALID = t1.FMATERIALID       			                    --��������ϱ�
    			and t1.FFORBIDSTATUS = 'A'	                                                                --δ����״̬��BOM
    			and t1.FDOCUMENTSTATUS='C'	                                                                --�����״̬��BOM
    		join T_BD_MATERIAL_L fxwl_L on fxwl.FMATERIALID = fxwl_l.FMATERIALID and fxwl_L.FLOCALEID =2052	--��������϶����Ա�
    		join T_ENG_BOMCHILD t3 on t1.fid = t3.FID	                                                    --��������ϸ			
    		join T_BD_MATERIAL zxwl on zxwl.FMATERIALID = t3.FMATERIALID 			                        --��������ϱ�
    		join T_BD_MATERIAL_L zxwl_L on zxwl.FMATERIALID = zxwl_L.FMATERIALID and zxwl_L.FLOCALEID =2052	--��������϶����Ա�			
    )
    
    
    --select * from cte		----���Ե�һ��CTE
    ,cte2_ZuiXinZiXiangBom as		--��cte2�����ȡ��0�������BOM������BOM�汾��,Ȼ���0��ĸ�����Ϣunion��һ��
    (
    ----����
    	select 
    		t1.BOM��� as BOM�㼶,t1.���BOM����,t1.BOM�汾
    		,t1.������ϴ��� as ��ϴ���,t1.���������� as ������,0 as ��¼�к�,0 as ���,t1.������ as ������,BOM�����������,0 as �����������,'' as ������ϴ���,'' as ����������,'��㸸��' as �������,0 as ����,0 as ��ĸ,0 as �̶����,0 as �䶯���,0 as BOM����,t1.FUSEORGID,t1.�Ƿ�������BOM�汾
    		,dense_rank() over(partition by t1.���BOM����,t1.������ϴ��� order by t1.BOM�汾 desc) as BOM�汾�ŷ��
    		
    		--SUN��Ӳ���
    		,TOP_MATERIALID,t1.TOP_MATERIALID as MATERIALID
    		,t1.MATERIALNUMBER,t1.MATERIALNAME,t1.MATERIALSPECIFICATION,t1.MATERIALDESCRIPTION
    		,0 as DOSAGE
    
    	from cte t1
    	where 1=1 
    		and t1.BOM��� = 0 and t1.������ = '10001'		--�����ֻ��ʾ0��IJ�Ʒ
    		--and t1.BOM�汾 in ('1.01.003_V1.0')	--����������һ����ƷBOM�汾,��ֻ���ѯһ����Ʒ��BOM�༶չ��;����һ��ע�͵��,�Ϳ��Բ�ѯȫ����Ʒ��ϵĶ༶չ��;���滹��һ�����Ƶ����Ҫͬ����,һ������.
    
    	union 
    
    	select 
    		t1.BOM���+1 as BOM�㼶,t1.���BOM����,t1.BOM�汾
    		,t1.������ϴ��� as ��ϴ���,t1.���������� as ������,t1.��¼�к� as ��¼�к�,t1.��� as ���,t1.������ as ������,BOM�����������,0 as �����������,t1.������ϴ��� as ������ϴ���,'' as ����������,t1.������� as �������,t1.���� as ����,t1.��ĸ as ��ĸ,t1.�̶���� as �̶����,t1.�䶯��� as �䶯���,t1.FBOMID as BOM����,t1.FUSEORGID,t1.�Ƿ�������BOM�汾
    		,dense_rank() over(partition by t1.���BOM����,t1.������ϴ��� order by t1.BOM���+1,t1.�Ƿ�������BOM�汾 desc,t1.BOM�汾 desc) as BOM�汾�ŷ��	--ͨ�����ֶα�ʶ���°汾��BOM�����ո�����Ϸ��֮�󣬰�BOM�汾������У�BOM�汾�ߵ�������ž���1
    	
    	--SUN��Ӳ���
    	,TOP_MATERIALID,t1.MATERIALID
    	,t1.MATERIALNUMBER,t1.MATERIALNAME,t1.MATERIALSPECIFICATION,t1.MATERIALDESCRIPTION
    	,t1.DOSAGE
    
    	from cte t1
    	where 1=1
    		--and t1.BOM���+1 <=2	--����ͨ��BOM����ֶ�����Ƶݹ�ѭ���Ĵ��,����ﲻ�ӿ��ƣ���ϵͳĬ�������ѭ��100��
    )
    --select * from cte2_ZuiXinZiXiangBom t2		----���Եڶ���CTE
    select t2.BOM�㼶 as BOM�㼶
    		,t2.��ϴ��� as ������ϴ���,t2.������ as ������,t2.��¼�к� as ��¼�к�,t2.��� as ���,t2.������� as �������,t2.���� as ����,t2.��ĸ as ��ĸ
    		,t2.�̶���� as �̶����,t2.�䶯��� as �䶯���
    		,t2.FUSEORGID,t2.������ as ������,t2.BOM�����������,t2.BOM���� as ����BOM�汾����,t2.BOM�汾 as ���BOM,t2.���BOM����	--��һ�еĿ���ע�͵�,ֻ��Ϊ���Ų�SQL�����õ�.
    		
    		--SUN��Ӳ���
    		,TOP_MATERIALID,t2.MATERIALID
    		,t2.MATERIALNUMBER,t2.MATERIALNAME,t2.MATERIALSPECIFICATION,t2.MATERIALDESCRIPTION
    		,t2.DOSAGE
    
    from cte2_ZuiXinZiXiangBom t2
    where 1=1
    	and t2.BOM�汾�ŷ�� = 1		--ͨ����BOM�汾�ŷ������ʶ���°汾��BOM�����ո�����Ϸ��֮�󣬰�BOM�汾������У�BOM�汾�ߵ�ֵ����1
    	and ( (t2.BOM�㼶 = 0 and t2.������ = '10001' ) or (t2.BOM�㼶 > 0) )	--����Ϊ�˲�ѯ�����յĽ�.
    
    	--and t2.���BOM����=1184039
    	--and TOP_MATERIALID=1028054
    	and TOP_MATERIALID=1030047
    
    order by t2.BOM�����������
    
    
    	select * from 
    	T_BD_MATERIAL_L
    	where FMATERIALID in (1028054,1030047)
    
    	select * from 
    	T_BD_MATERIAL
    	where FMATERIALID in (1028054,1030047)
    

    Powered by kodbox V1.59

    Copyright © kodcloud.com.

    Files