select distinct
x1.单据类型,x1.补充代码,x1.合同号,x1.交货日期 , ISNULL(x1.部门划分,X1.部门) as '综合部门' ,x1.部门 '原部门' ,x1.部门划分 ,x1.销售员,X1.客户编号 ,x1.客户名称
,C0.U_CardGroup AS '客户类型' ,x2.预计收款RMB ,sum(x1.RMB总价) AS '销售金额RMB' ,x1.年份,x1.月份,x1.单据币别 --,sum(X1.原币总价) AS '销售金额原币'
,x2.审批表状态,x2.部门 AS '审批表来源',x2.预计收支结余RMB
,CASE when isnull(X2.预计收款RMB,0)=0 and x4.受订总数量 is not null then sum(X1.交货数量) /x4.受订总数量 -----项目预计毛利为0时,按数量计算比例,否则按金额算
WHEN (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END)) >1 THEN 1
WHEN (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END)) <-1 THEN -1
ELSE (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END))
END as '比例'
,(CASE when isnull(X2.预计收款RMB,0)=0 and x4.受订总数量 is not null then sum(X1.交货数量) /x4.受订总数量
WHEN (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END)) >1 THEN 1
WHEN (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END)) <-1 THEN -1
ELSE (SUM(x1.RMB总价)/(CASE WHEN X2.预计收款RMB =0 THEN 0.0001 ELSE isnull(X2.预计收款RMB,0.0001) END))
END )*X2.预计收支结余RMB as '有效毛利RMB'
,C0.U_CardGroup AS '客户属性',x2.合同类型
FROM
(----物料销货明细
SELECT x1.单据类型,x1.补充代码,x1.合同号,x1.交货日期 , x1.部门 ,x1.部门划分 ,x1.销售员,x1.客户编号,x1.客户名称 ,X1.交货数量
,x1.RMB总价 ,x1.年份,x1.月份,x1.单据币别,X1.原币总价
FROM ZY_TB_Sales_Details X1
INNER JOIN [ZY_VIEW_XM] X0 ON X0.ConCode =X1.合同号
WHERE left(X1.部门,3)<>'进出口'
UNION ALL------费用型项目合同
SELECT '空建费用应收发票' '单据类型' ,T0.SupplCode ,T0.NumAtCard ,t0.DocDate,T0.U_Department, T0.U_departmentation ,T2.SlpName ,T0.CardCode ,T0.CardName,T1.Quantity
,T1.PriceAfVAT *t1.Quantity, YEAR(T0.DocDate) , MONTH(T0.DocDate) ,T0.DocCur ,T0.DocTotal
--T0.DocEntry,T0.NumAtCard ,T1.BaseEntry ,T1.TrgetEntry
FROM OINV T0
INNER JOIN INV1 T1 ON T1.DocEntry=T0.DocEntry
INNER JOIN OSLP T2 ON T2.SlpCode =T0.SlpCode
INNER JOIN
(
SELECT T0.U_ConCode --,COUNT(T1.U_SOEntry) ,T0.U_ConStatus
FROM [@AVA_DS_OCTR] T0
JOIN [@AVA_DS_CTR1] T1 ON T1.DocEntry=T0.DocEntry
WHERE T0.Canceled ='N'
AND T0.CreateDate>'2020.07.01'
AND T0.U_ConStatus<>'01'
GROUP BY T0.U_ConCode,T0.U_ConStatus
HAVING COUNT(T1.U_SOEntry)=0
) T10 ON T10.U_ConCode =T0.NumAtCard
WHERE T0.CANCELED ='N'
AND T1.BaseEntry IS NULL ---空间应收发票
AND T1.TrgetEntry IS NULL ---下游单据为空(贷项或取消单都有下游单据)
) X1
LEFT JOIN
(---子表X2,除进出口部外,各项目合同毛利
select '工业部' as '部门'
,t0.DocEntry ,t0.U_ConCode
,CASE when T0.U_ConStatus ='01' then '草稿'
when T0.U_ConStatus ='02' then '审批中'
when T0.U_ConStatus ='03' then '已批准'
when T0.U_ConStatus ='04' then '已拒绝'
when T0.U_ConStatus ='05' then '已结算'
end as '审批表状态'
,T0.U_SalesTotal AS '预计收款RMB'
,t0.U_PurchaseTotal as '预计付款RMB'
,T0.U_FHtype AS '合同类型'
,T0.U_GrsProfit AS '预计收支结余RMB'
FROM [@AVA_DS_OCTR] t0
where t0.Canceled='N'
) X2 ON X2.U_ConCode =X1.合同号
LEFT JOIN OCRD C0 ON C0.CardCode =X1.客户编号
LEFT JOIN
(----受订单总数量
select t0.NumAtCard as '合同号', SUM(T1.Quantity) as '受订总数量'
from ORDR t0
inner join RDR1 t1 on t1.DocEntry =t0.DocEntry
inner join [@AVA_CONTRACT_FORM] t2 on t2.U_CONTRACT =t0.NumAtCard
where T0.CANCELED ='N'
--AND t0.NumAtCard ='2018ZYGY18078'
group by t0.NumAtCard
)x4 on x4.合同号 =X2.U_ConCode
WHERE LEFT(X1.部门,3) <>'进出口'
AND (X1.交货日期 >='2024-07-15') and (X1.交货日期 <='2025-07-15')
group by
x1.单据类型,x1.补充代码,x1.合同号,x1.交货日期,x1.部门,x1.销售员,x1.客户名称 ,c0.U_CardGroup ,x2.预计收款RMB
,x1.年份,x1.月份,x1.单据币别,x2.审批表状态,x2.部门,x2.预计收支结余RMB,x4.受订总数量,
c0.U_CardGroup ,x2.合同类型 ,X1.部门划分,X1.客户编号 将代码结果按照客户名称分组 汇总有效毛利RMB
最新发布