--�ݹ�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)