• 金蝶云星空SQL 85.47 kB 2024-06-03 22:06
  • Position: 金蝶云星空SQL / 出入库单据(销售出库、销售退货、采购入库、采购退料)[星空].sql

    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
    

    Powered by kodbox V1.66

    Copyright © kodcloud.com.

    Files