declare @begdate date,@enddate date
set @begdate='2022-08-01'
set @enddate='2023-08-30'
--set @begdate='{1}'
--set @enddate='{2}'
--��۳���
select
900000000+oue.FENTRYID as FID
,'��۳���' �������
,ou.FBILLNO ���ݱ��
,CONVERT(varchar(230), ou.FDATE, 23) ����
,cul.FNAME �ͻ�
,dep_L.FNAME ����
,oue.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,oue.FREALQTY ����
,null ����
--,ouef.FSALCOSTPRICE ��۳ɱ���
--,ouef.FTAXPRICE ��˰����
--,(poe.FQTY * poef.FTAXPRICE) ��˰�ϼ�
--,*
from
T_SAL_OUTSTOCK ou --��۳���
left join T_SAL_OUTSTOCKENTRY oue on oue.FID=ou.FID--��۳����ϸ
left join T_SAL_OUTSTOCKENTRY_F ouef on ouef.FENTRYID=oue.FENTRYID
left join T_BD_MATERIAL m on oue.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=oue.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=ou.FDeliveryDeptID--����
left join T_BD_CUSTOMER_L cul on cul.FCUSTID=ou.FCustomerID--�ͻ�
where ou.FDOCUMENTSTATUS='C'--�����
and ou.FCANCELSTATUS='A' --��
and ou.FCREATEDATE between @begdate and @enddate
union all
--����˻�
select
800000000+roue.FENTRYID as fid
,'����˻�' �������
,rou.FBILLNO ���ݱ��
,CONVERT(varchar(230), rou.FDATE, 23) ����
,cul.FNAME �˻��ͻ�
,dep_L.FNAME ��沿��
,roue.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,null ����
,roue.FREALQTY ����
,ouef.FSALCOSTPRICE ��۳ɱ���
,ouef.FTAXPRICE ��˰����
from
T_SAL_RETURNSTOCK rou --����˻�
left join T_SAL_RETURNSTOCKENTRY roue on roue.FID=rou.FID --����˻����ϸ
left join T_SAL_RETURNSTOCKENTRY_F ouef on ouef.FENTRYID=roue.FENTRYID --����˻��������ϸ
left join T_BD_MATERIAL m on roue.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=roue.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=rou.FSTOCKDEPTID--����
left join T_BD_CUSTOMER_L cul on cul.FCUSTID=rou.FRETCUSTID--�ͻ�
where rou.FDOCUMENTSTATUS='C'--�����
and rou.FCANCELSTATUS='A' --��
and rou.FCREATEDATE between @begdate and @enddate
union all
--�ɹ����
select
700000000+inse.FENTRYID as fid
,'�ɹ����' �������
,ins.FBILLNO ���ݱ��
,CONVERT(varchar(230), ins.FDATE, 23) ����
--,FDeliveryDeptID ��������
,sup.FNAME ��Ӧ��
,dep_L.FNAME ��ϲ���
,inse.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,null ����
,inse.FREALQTY ����
--,null �ɱ���
--,null ��˰����
from
t_STK_InStock ins --�ɹ����
left join T_STK_INSTOCKENTRY inse on inse.FID=ins.FID --�ɹ���ⵥ�ϸ
left join T_BD_MATERIAL m on inse.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=inse.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=ins.FSTOCKDEPTID--����
left join T_BD_SUPPLIER_L sup on sup.FSUPPLIERID=ins.FSUPPLIERID--��Ӧ��
where ins.FDOCUMENTSTATUS='C'--�����
and ins.FCANCELSTATUS='A' --��
and ins.FCREATEDATE between @begdate and @enddate
union all
--�ɹ����
select
600000000+rinse.FENTRYID as fid
,'�ɹ����' �������
,rins.FBILLNO ���ݱ��
,CONVERT(varchar(230), rins.FDATE, 23) ����
--,FDeliveryDeptID ��������
,sup.FNAME ��Ӧ��
,dep_L.FNAME ��ϲ���
,rinse.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,rinse.FRMREALQTY ����
,null ����
--,null �ɱ���
--,null ��˰����
from
t_PUR_MRB rins --�ɹ����
left join T_PUR_MRBENTRY rinse on rinse.FID=rins.FID --�ɹ�����ϸ
left join T_BD_MATERIAL m on rinse.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=rinse.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=rins.FMRDEPTID--����
left join T_BD_SUPPLIER_L sup on sup.FSUPPLIERID=rins.FSUPPLIERID--��Ӧ��
where rins.FDOCUMENTSTATUS='C'--�����
and rins.FCANCELSTATUS='A' --��
and rins.FCREATEDATE between @begdate and @enddate
union all
--�������
select
500000000+msde.FENTRYID as fid
,'�������' �������
,msd.FBILLNO ���ݱ��
,CONVERT(varchar(230), msd.FDATE, 23) ����
--,FDeliveryDeptID ��������
,cul.FNAME �ͻ�
,dep_L.FNAME ��ϲ���
,msde.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,msde.FQty ����
,null ����
--,msde.FPrice �ɱ���
--,null ��˰����
from
T_STK_MISDELIVERY msd --�������
left join T_STK_MISDELIVERYENTRY msde on msde.FID=msd.FID --��������ϸ
--left join T_SAL_OUTSTOCKENTRY_F ouef on ouef.FENTRYID=oue.FENTRYID
left join T_BD_MATERIAL m on msde.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=msde.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=msd.FDEPTID--����
left join T_BD_CUSTOMER_L cul on cul.FCUSTID=msd.FCUSTID--�ͻ�
where msd.FDOCUMENTSTATUS='C'--�����
and msd.FCANCELSTATUS='A' --��
and msd.FCREATEDATE between @begdate and @enddate
union all
--������
select
400000000+msce.FENTRYID as fid
,'������' �������
,msc.FBILLNO ���ݱ��
,CONVERT(varchar(230), msc.FDATE, 23) ����
,sup.FNAME ��Ӧ��
,dep_L.FNAME ����
,msce.FSEQ �к�
,m.FNUMBER ��ϱ���
,mg_L.[FNAME] ��Ϸ���
,ml.[FNAME] ������
,ml.F_QZYN_MULLANGTEXT4 ͨ���
,ml.[FSPECIFICATION] ����ͺ�
,null as ����
,msce.FQty ����
--,msce.FPrice �ɱ���
--,null ��˰����
from
T_STK_MISCELLANEOUS msc --������
left join T_STK_MISCELLANEOUSENTRY msce on msce.FID=msc.FID--�������ϸ
left join T_BD_MATERIAL m on msce.FMATERIALID=m.FMATERIALID --���
left join T_BD_MATERIALGROUP_L mg_L on m.FMATERIALGROUP=mg_L.FID --��Ϸ���
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=msce.FMATERIALID--��϶�����
left join T_BD_DEPARTMENT_L dep_L on dep_L.FDEPTID=msc.FDEPTID--����
left join T_BD_SUPPLIER_L sup on sup.FSUPPLIERID=msc.FSUPPLIERID--��Ӧ��
where msc.FDOCUMENTSTATUS='C'--�����
and msc.FCANCELSTATUS='A' --��
and msc.FCREATEDATE between @begdate and @enddate