214 lines
9.1 KiB
SQL
214 lines
9.1 KiB
SQL
Select
|
|
MONTH(A.FDATE) as '月份', --月份
|
|
ISNULL(W6.FNAME,'') as '所属区域', --所属区域
|
|
CASE WHEN ISNULL(W4.FNAME,'') = '' THEN W1.FNAME
|
|
ELSE W4.FNAME END as '销售员', --销售员
|
|
(SELECT FNAME FROM T_BD_CUSTOMER_L WHERE FCUSTID = A.FCustId) as '购货单位', --购货单位
|
|
A.FDATE as '签订日期', --签订日期
|
|
A.F_contractnumber as '订单号', --订单号
|
|
C.FNAME as '产品名称', --产品名称
|
|
C.FSPECIFICATION as '型号规格标准', --型号\规格\标准
|
|
A.F_contractnumber+C.FSPECIFICATION as '订单号型号规格标准',
|
|
C.F_114 as '型号分类',
|
|
C.F_115 as '产品分类',
|
|
C.F_116 as '产业线',
|
|
CASE WHEN A.F_VRYF_COMBO_LSN = 'DDXS' THEN '单独销售'
|
|
WHEN A.F_VRYF_COMBO_LSN = 'SXTXS' THEN '随系统销售'
|
|
WHEN A.F_VRYF_COMBO_LSN = 'XTZHT' THEN '系统主合同' ELSE ''
|
|
END as '销售类型',
|
|
CASE WHEN W5.FDATAVALUE = '仪表' THEN '单独销售' ELSE '随系统销售' END as '销售类型1',
|
|
--'' as 'OEM型号',--原取消字段
|
|
--'' as '具体参数',--原取消字段
|
|
B.FQty as '数量台', --数量/台
|
|
ISNuLL(BB.FTaxPrice,0) as '单价元台', --单价元/台
|
|
CASE WHEN ROW_NUMBER() OVER (PARTITION BY B.FENTRYID ORDER BY W3.FSEQ) = 1
|
|
THEN IsNull(BB.FAllAmount_LC,0) ELSE NULL END
|
|
as '合同总价', --合同总价
|
|
(Select FStockOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as '已交货数', --已交货数
|
|
(Select FRemainOutQty From T_SAL_ORDERENTRY_R Where FENTRYID = B.FENTRYID) as '未交货数', --未交货数
|
|
CASE WHEN (D.FOPENSTATUS)= 'A' Then '空' When (D.FOPENSTATUS)= 'B' Then '部分' When (D.FOPENSTATUS)= 'C' Then '完全' Else ''
|
|
END as '开票情况', --开票情况 A.F_VRYF_Assistant_k79
|
|
(Select FNAME From T_BAS_PREBDONE_L Where FID = A.F_projectname) as '项目名称', --项目名称
|
|
CASE WHEN --项目所属1
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 1
|
|
THEN '建筑楼宇'
|
|
WHEN --项目所属2
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 2
|
|
THEN '医疗卫生'
|
|
WHEN --项目所属3
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 3
|
|
THEN '学校'
|
|
WHEN --项目所属4
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 4
|
|
THEN '文娱康乐'
|
|
WHEN --项目所属5
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 5
|
|
THEN '市政工程'
|
|
WHEN --项目所属6
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 6
|
|
THEN '能源'
|
|
WHEN --项目所属7
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 7
|
|
THEN '工业'
|
|
WHEN --项目所属8
|
|
(Select
|
|
aq.F_Projectfield
|
|
From
|
|
T_BAS_PREBDTHREE aq
|
|
JOIN
|
|
T_BAS_PREBDTHREE_L aw
|
|
on aq.FID = aw.FID
|
|
Where aq.FID = A.F_BON) = 8
|
|
THEN '交通运输'
|
|
ELSE ''
|
|
END as '项目所属领域', --项目所属领域
|
|
CASE WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'LH'
|
|
THEN '龙湖'
|
|
WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'BL'
|
|
THEN '保利地产'
|
|
WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'HR'
|
|
THEN '华润'
|
|
WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'N'
|
|
THEN '否'
|
|
WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'HEF'
|
|
THEN '华润非集采'
|
|
WHEN --集采业务分类
|
|
A.F_PurchasingPro = 'LD'
|
|
THEN '绿地'
|
|
ELSE ''
|
|
END as '集采业务分类', --集采业务分类 LH=龙湖,BL=保利地产,HR=华润,N=否,HEF=华润非集采,LD=绿地
|
|
CASE WHEN A.FBUSINESSTYPE = 'VMI' THEN 'VMI业务' WHEN A.FBUSINESSTYPE = 'DRPTRANS' THEN '分销调拨'
|
|
WHEN A.FBUSINESSTYPE = 'STRAIGHT' THEN '直运销售' WHEN A.FBUSINESSTYPE = 'CONSIGNMENT' THEN '寄售'
|
|
WHEN A.FBUSINESSTYPE = 'NORMAL' THEN '普通销售' WHEN A.FBUSINESSTYPE = 'DRPSALE' THEN '分销购销'
|
|
WHEN A.FBUSINESSTYPE = 'RETURNSO' THEN '退货订单' ELSE ''
|
|
END as '业务类型-按产品划分',
|
|
CASE WHEN
|
|
(Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'Y' THEN '代理商'
|
|
WHEN (Select F_Authorization From T_BD_CUSTOMER where FCUSTID = A.FCustId) = 'N' THEN '直销'
|
|
ELSE '' END as '直销经销商',
|
|
CASE WHEN B.F_SPECIALOFFER = 0 THEN '否' WHEN B.F_SPECIALOFFER = 1 THEN '是' ELSE '' END as '是否特价',
|
|
CASE WHEN W6.FNAME = '产品线' or W6.FNAME = '充电桩团队' or W6.FNAME = '三电产品线'
|
|
THEN '产品线'
|
|
WHEN W6.FNAME = '数据中心' or W6.FNAME = '医院行业线'
|
|
THEN '行业线'
|
|
ELSE '区域线'
|
|
END as '业绩类型',
|
|
CASE WHEN ISNULL(W4.FNAME,'') = '' THEN ISNULL(AW1.区域,'')
|
|
ELSE ISNULL(AW4.区域,'') END as 'SBU',
|
|
(Select wa.FDATAVALUE From
|
|
T_BAS_ASSISTANTDATAENTRY aw
|
|
JOIN
|
|
T_BAS_ASSISTANTDATAENTRY_L wa
|
|
on aw.FENTRYID = wa.FENTRYID
|
|
Where wa.FENTRYID = (Select F_CreditClassification From T_BD_CUSTOMER where FCUSTID = A.FCustId)
|
|
AND FDocumentStatus = 'C' AND FFORBIDSTATUS = 'A') as '客户性质', --客户性质
|
|
ISNULL(B.F_PROJECTPROPOSAL,'') as '项目方案类型', --新增字段
|
|
--'' as '是否直签过系统', --新增字段(删除)
|
|
ISNULL(W7.F_Province,'') as '项目所属省份', --新增字段
|
|
CASE WHEN A.F_ORDERTYPE = 'YB' THEN '仪表' WHEN A.F_ORDERTYPE = 'XT' THEN '系统' ELSE ''
|
|
END as '表计或系统', --新增字段
|
|
W3.FSHAREAMOUNT as '业绩分摊' --新增字段
|
|
--B.FNOTE as '备注', --新增字段(删除)
|
|
--'' as '订单类型' --新增字段(删除)
|
|
From
|
|
T_SAL_ORDER A
|
|
JOIN
|
|
T_SAL_ORDERENTRY B
|
|
JOIN
|
|
T_SAL_ORDERENTRY_F BB
|
|
on B.FENTRYID = BB.FENTRYID
|
|
on B.FID = A.FID
|
|
LEFT JOIN
|
|
T_ORG_ORGANIZATIONS_L AB
|
|
on AB.FORGID = A.FSALEORGID
|
|
LEFT JOIN
|
|
V_BD_SALESMAN_L W1 on W1.fid = A.FSALERID --销售员
|
|
LEFT JOIN
|
|
T_PerformanceSharing W2 on W2.FSOURCEBILLNO = A.FBILLNO --业绩分摊
|
|
LEFT JOIN
|
|
T_PerformanceSharingEntry W3 on W2.FID = W3.FID --业绩分摊单据体
|
|
LEFT JOIN
|
|
V_BD_SALESMAN_L W4 on W3.FSALEID = W4.fid --销售员
|
|
LEFT JOIN
|
|
ERPTOHR.HYHRV3.dbo.v_erp_empinfo AW1 on W1.FNAME = AW1.名称 --视图
|
|
LEFT JOIN
|
|
ERPTOHR.HYHRV3.dbo.v_erp_empinfo AW4 on W4.FNAME = AW4.名称 --视图
|
|
LEFT JOIN
|
|
T_BD_DEPARTMENT_L W6 on A.FSaleDeptId = W6.FDEPTID --所属区域
|
|
LEFT JOIN
|
|
T_BAS_PREBDTHREE W7 on W7.FID = A.F_BON --商机
|
|
LEFT JOIN
|
|
(Select a2.FDATAVALUE,a2.FENTRYID From T_BAS_ASSISTANTDATAENTRY a1 JOIN T_BAS_ASSISTANTDATAENTRY_L a2
|
|
on a1.FENTRYID = a2.FENTRYID Where a1.FID = '670cc977263353') W5 on W5.FENTRYID = A.F_ORDERCATEGORY
|
|
Left JOIN
|
|
(Select aq.F_114,aq.F_115,aq.FMATERIALID,qa.FNAME,aq.F_XHDL,aq.F_116,qa.FSpecification
|
|
From
|
|
T_BD_MATERIAL aq
|
|
Left JOIN
|
|
T_BD_MATERIAL_L qa
|
|
on aq.FMATERIALID = qa.FMATERIALID) C
|
|
on B.FMaterialId = C.FMATERIALID
|
|
LEFT JOIN
|
|
(Select A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER From T_AR_RECEIVABLEENTRY A1 JOIN T_AR_RECEIVABLE A2 on A1.FID = A2.FID
|
|
Where A2.FSETACCOUNTTYPE = 3 Group by A2.FOPENSTATUS,A1.FID,A1.FORDERNUMBER) D on A.FBILLNO = D.FORDERNUMBER
|
|
--条件
|
|
Where A.FDocumentStatus = 'C'
|
|
AND ('#CurrentOrgUnitId#'='' or AB.FNAME = '#CurrentOrgUnitId#')
|
|
AND ('#FPaperContract#' = '' or A.F_contractnumber like '%#FPaperContract#%')
|
|
AND ('#FStartDate#' = '' or A.FDATE >= '#FStartDate#')
|
|
AND ('#FEndDate#' = '' or A.FDATE <= '#FEndDate#')
|
|
Order by A.FDATE desc |