declare @begdate date,@enddate date
set @begdate='2023-02-01'
set @enddate='2025-01-01'
select
m.FMATERIALID as FID
,org.FNUMBER ʹ����֯����
,orgl.FNAME ʹ����֯
,m.FNUMBER ��ϱ���
,ml.[FNAME] ������
,ml.[FSPECIFICATION] ����ͺ�
,mgl.FNAME ��Ϸ���
,(
select sum(FBaseQty)
from T_STK_INVENTORY ti --��ʱ���
left join T_BD_MATERIAL tm on tm.FMATERIALID=ti.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ti.FSTOCKORGID --�����֯
where tm.FNUMBER=m.FNUMBER --��ϱ���һ��
and torg.FNUMBER=org.FNUMBER--�����֯һ
) as ��ʱ��� --��ϱ���,��֯����һ��
,(
select top 1 ouef.FCOSTPRICE
from T_SAL_OUTSTOCK ou
left join T_SAL_OUTSTOCKENTRY oue on ou.FID=oue.FID
left join T_SAL_OUTSTOCKENTRY_F ouef on oue.FENTRYID=ouef.FENTRYID--����
left join T_BD_MATERIAL tm on tm.FMATERIALID=oue.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ou.FSTOCKORGID --������֯
where ou.FDOCUMENTSTATUS ='C'
and ou.FCANCELSTATUS='A'
and tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and ouef.FCOSTPRICE<>0
and ou.FDATE>=@begdate
and ou.FDATE<=@enddate
order by ou.FDATE desc
) as �ɱ�����
,(
select top 1
poif.FTAXPRICE
--poie.F_GOLD_DECIMAL
from t_STK_InStock poi --�ɹ���ⵥ
left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
where
tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and poi.FDOCUMENTSTATUS = 'C'--�����
and poi.FCANCELSTATUS<> 'B' --δȡ�
--and tm.FNUMBER <>'ZPY00000004'
--and po.FDATE between @begdate and @enddat
order by poi.FDATE desc
) ���ɹ���
,CONVERT(varchar(230),(
select top 1
poi.FDATE
from t_STK_InStock poi --�ɹ���ⵥ
left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
where
tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and poi.FDOCUMENTSTATUS = 'C'--�����
and poi.FCANCELSTATUS<> 'B' --δȡ�
order by poi.FDATE desc
), 23) ���ɹ��������
,DATEDIFF(dd,
(
select top 1
poi.FDATE
from t_STK_InStock poi --�ɹ���ⵥ
left join T_STK_INSTOCKENTRY poie on poi.fid=poie.FID ---�ɹ������ϸ
--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
left join T_BD_MATERIAL tm on tm.FMATERIALID=poie.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=poi.FPURCHASEORGID --��֯���ɹ���֯
where
tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and poi.FDOCUMENTSTATUS = 'C'--�����
and poi.FCANCELSTATUS<> 'B' --δȡ�
order by poi.FDATE desc
)
,GETDATE()) δ�ɹ����
,CONVERT(varchar(230),(
select top 1
sout.FDATE
from T_SAL_OUTSTOCK sout --��۳���
left join T_SAL_OUTSTOCKENTRY soute on sout.fid=soute.FID ---�ϸ
--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
left join T_BD_MATERIAL tm on tm.FMATERIALID=soute.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=sout.FStockOrgId --��֯��������֯
where
tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and sout.FDOCUMENTSTATUS = 'C'--�����
and sout.FCANCELSTATUS<> 'B' --δȡ�
order by sout.FDATE desc
), 23) ����۳�������
,DATEDIFF(dd,
(
select top 1
sout.FDATE
from T_SAL_OUTSTOCK sout --��۳���
left join T_SAL_OUTSTOCKENTRY soute on sout.fid=soute.FID ---�ϸ
--left join T_STK_INSTOCKENTRY_F poif on poif.FENTRYID=poie.FENTRYID
left join T_BD_MATERIAL tm on tm.FMATERIALID=soute.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=sout.FStockOrgId --��֯��������֯
where
tm.FNUMBER=m.FNUMBER
and torg.FNUMBER=org.FNUMBER
and sout.FDOCUMENTSTATUS = 'C'--�����
and sout.FCANCELSTATUS<> 'B' --δȡ�
order by sout.FDATE desc
)
,GETDATE()) ������
,(
select sum(FQTY)
from t_PUR_POOrder po --�ɹ�����
left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
where
pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
and po.FDOCUMENTSTATUS = 'C'--�����
--and po.FCLOSESTATUS='A' --δ�ر�
and po.FCANCELSTATUS<> 'B' --δȡ�
and po.FDATE between @begdate and @enddate
) �ۼƲɹ��������
,(
select sum(por.FSTOCKBASESTOCKINQTY)
from t_PUR_POOrder po --�ɹ�����
left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
left join T_PUR_POORDERENTRY_R por on poe.FENTRYID=por.FENTRYID --���������
left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
where
pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
and po.FDOCUMENTSTATUS = 'C'--�����
--and po.FCLOSESTATUS='A' --δ�ر�
and po.FCANCELSTATUS<> 'B' --δȡ�
and po.FDATE between @begdate and @enddate
) �ۼƲɹ�������
,(
select sum(por.FREMAINSTOCKINQTY)
from t_PUR_POOrder po --�ɹ�����
left join t_PUR_POOrderEntry poe on po.fid=poe.FID ---�ɹ������ϸ
left join T_PUR_POORDERENTRY_R por on poe.FENTRYID=por.FENTRYID --���������
left join T_BD_MATERIAL pm on pm.FMATERIALID=poe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS porg on porg.FORGID=po.FPURCHASEORGID --��֯���ɹ���֯
where
pm.FNUMBER=m.FNUMBER --�ɹ��������=��ϱ���
and porg.FNUMBER=org.FNUMBER--�����ɹ���֯����=�����֯����
and po.FDOCUMENTSTATUS = 'C'--�����
and po.FCLOSESTATUS='A' --δ�ر�
and po.FCANCELSTATUS<> 'B' --δȡ�
and po.FDATE between @begdate and @enddate
) ʣ��δ�����
--Ԥ�Ƴ�����۶���������������FSTOCKBASEQTY+�ۼ��˻�����������������FSTOCKBASEREBACKQTY-�ۼƳ���������������FSTOCKBASESTOCKOUTQTY
,(
select sum(soe.FQTY)
from T_SAL_ORDER so
left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
--left join T_SAL_ORDERENTRY_R soer on soe.FENTRYID=soer.FENTRYID
left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
where
sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
and so.FDOCUMENTSTATUS='C'--�Ѿ����
--and so.FCLOSESTATUS='A' --δ�ر�
and so.FCANCELSTATUS<> 'B' --δȡ�
and so.FDATE between @begdate and @enddate
) �ۼ���۶������
,(
select sum(soee.FSTOCKBASESTOCKOUTQTY)
from T_SAL_ORDER so
left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
left join T_SAL_ORDERENTRY_E soee on soe.FENTRYID=soee.FENTRYID
left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
where
sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
and so.FDOCUMENTSTATUS='C'--�Ѿ����
--and so.FCLOSESTATUS='A' --δ�ر�
and so.FCANCELSTATUS<> 'B' --δȡ�
and so.FDATE between @begdate and @enddate
) �ۼ���۳������
,(
select sum(soee.FSTOCKBASEREBACKQTY)
from T_SAL_ORDER so
left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
left join T_SAL_ORDERENTRY_E soee on soe.FENTRYID=soee.FENTRYID
left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
where
sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
and so.FDOCUMENTSTATUS='C'--�Ѿ����
--and so.FCLOSESTATUS='A' --δ�ر�
and so.FCANCELSTATUS<> 'B' --δȡ�
and so.FDATE between @begdate and @enddate
) �ۼ�����˻����
,(
select sum(soer.FREMAINOUTQTY)
from T_SAL_ORDER so
left join T_SAL_ORDERENTRY soe on soe.FID=so.fid
left join T_SAL_ORDERENTRY_R soer on soe.FENTRYID=soer.FENTRYID
left join T_BD_MATERIAL sm on sm.FMATERIALID=soe.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS sorg on sorg.FORGID=so.FSALEORGID --��֯�������֯
where
sm.FNUMBER=m.FNUMBER --��۶������=��ϱ���
and sorg.FNUMBER=org.FNUMBER--���������֯����=�����֯����
and so.FDOCUMENTSTATUS='C'--�Ѿ����
--and so.FCLOSESTATUS='A' --δ�ر�
and so.FCANCELSTATUS<> 'B' --δȡ�
and so.FDATE between @begdate and @enddate
) ʣ��δ�����
,cast(ms.FMINSTOCK as decimal(23,2)) �����
,cast(ms.FSAFESTOCK as decimal(23,2)) ��ȫ���
,cast(
(select sum(FPRICEQTY)
from T_AP_PAYABLEENTRY ape
left join T_AP_PAYABLE ap on ap.FID=ape.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
where ap.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and ap.FDATE between @begdate and @enddate
)
as decimal(23,2))
�½�����
,cast(
(select sum(FPRICEQTY)
from T_AP_PAYABLEENTRY ape
left join T_AP_PAYABLE ap on ap.FID=ape.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
where ap.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and datediff(year,ap.FDATE,@enddate)=0
)
as decimal(23,2))
������
,cast(
(select sum(FPRICEQTY)
from T_AP_PAYABLEENTRY ape
left join T_AP_PAYABLE ap on ap.FID=ape.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=ape.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ap.FSETTLEORGID --��֯��������֯
where ap.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and datediff(year,ap.FDATE,@enddate)=1
)
as decimal(23,2))
��������
,cast(
(select sum(FPRICEQTY)
from t_AR_receivableEntry are
left join t_AR_receivable ar on ar.FID=are.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
where ar.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and ar.FDATE between @begdate and @enddate
)
as decimal(23,2))
�³�����
,cast(
(select sum(FPRICEQTY)
from t_AR_receivableEntry are
left join t_AR_receivable ar on ar.FID=are.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
where ar.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and datediff(year,ar.FDATE,@enddate)=0
)
as decimal(23,2))
������
,cast(
(select sum(FPRICEQTY)
from t_AR_receivableEntry are
left join t_AR_receivable ar on ar.FID=are.FID
left join T_BD_MATERIAL tm on tm.FMATERIALID=are.FMATERIALID --��ϱ�ȡ��ϱ���
left join T_ORG_ORGANIZATIONS torg on torg.FORGID=ar.FSETTLEORGID --��֯��������֯
where ar.FDOCUMENTSTATUS='C'
and tm.FNUMBER=m.FNUMBER --��ϱ���
and torg.FNUMBER=org.FNUMBER--��֯����
and datediff(year,ar.FDATE,@enddate)=1
)
as decimal(23,2))
��������
into #t1
from
T_BD_MATERIAL m --������
left join T_BD_MATERIAL_L ml on ml.FMATERIALID=m.FMATERIALID --������
left join T_BD_MATERIALGROUP_L mgl on mgl.FID=m.FMATERIALGROUP --��Ϸ���
left join t_BD_MaterialStock ms on ms.FMATERIALID=m.FMATERIALID --��ϲֿ�ҳǩ
left join T_ORG_ORGANIZATIONS org on org.FORGID=m.FUSEORGID --��֯��ȡʹ����֯����
left join T_ORG_ORGANIZATIONS_L orgl on orgl.FORGID=m.FUSEORGID and orgl.FLOCALEID=2052--��֯�������չ��ȡ��֯���
where m.FDOCUMENTSTATUS='C'
and m.FFORBIDSTATUS='A'
select
CASE
WHEN isnull(�ɱ�����,0)=0 and isnull(���ɹ���,0)<>0 then (��ʱ���*���ɹ���)
WHEN isnull(�ɱ�����,0)<>0 then (��ʱ���*�ɱ�����)
else 0 end
as �ɱ����
,*
from #t1
drop table #t1