imp_yunwei/获取应收应付的sql.txt

------应付------
select 
dp.deptname 制单部门 , a.create_by_name  制单人 , a.create_time 单据日期 ,
a.fno 采购合同号 ,  a.serviceType 业务类型 ,a.fours , a.foursname 我方名称 , a.supName 供方名称 ,
a.curcy 合同币别 , a.rate 折人民币汇率  , a.rateUs  合同执行美元汇率 , a.totalAmt 合同金额 ,
bz.poamt 开票金额,
ifnull(yf.appamt,0)  已付款金额 ,  
ifnull(bz.poamt,0)- ifnull(yf.appamt,0) 应付金额,-- 发票金额-已付款金额+退款=应付金额
ifnull(tk.appamt,0) 已收退款金额,
ifnull(dljs.poamt,0) 代理结算金额,
 ifnull(zg.poamt,0)- ifnull(zgcx.amt,0) 未冲销暂估金额 ,
fy.appamt  费用付款金额 , fybz.poamt 费用发票金额 , ifnull(fybz.poamt,0)- ifnull(fy.appamt,0) 费用应付金额 


from  purchaseContract a 
--采购入库
left join ( select a2.pono , sum(a2.poamt) poamt  from mdkcctrl a1 left join  mdkcdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=22  group by a2.pono ) rk  on a.fno=rk.pono
--货款付款
left join ( select a2.pono , sum(a2.appAmt) appAmt  from PayMgrctrl a1 left join  paystockdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=15  
		and a1.payType in('保证金','预付款-发货前','预付款-发货后','发票后结算款')
		group by a2.pono ) yf  on a.fno=yf.pono

--退款
left join ( select a2.pono , sum(a2.claimAmt) appamt from breceiptctrl a1 left join  breceiptdtl a2 on a1.id=a2.rid
		where ifnull(a1.del_flag,0)<>1
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=19
		and ifnull(a2.PlacedForDtl,'')='1' 
		and a2.kxlx like '%退款%' 
		group by a2.pono ) tk  on a.fno=tk.pono		

			
--进口到票37		进项发票13		产品明细
left join ( select a2.pono , sum(a2.poamt) poamt  from purMultiSettle a1 left join  purStlProdDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid in (37,13)  
		and a1.placed='2'
		group by a2.pono ) bz  on a.fno=bz.pono
--货值暂估
left join ( select a2.pono , sum(a2.poamt) poamt  from provIn a1 left join  provInDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		-- and a1.fmodalid in (37,13)  
		and a1.placed='2'
		group by a2.pono ) zg  on a.fno=zg.pono		
--进口到票37		进项发票13		冲销预付款
left join ( select a2.pono , sum(a2.amt) amt  from purMultiSettle a1 left join  chargeStock a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid in (37,13)  
		and a1.placed='2'
		group by a2.pono ) zgcx  on a.fno=zgcx.pono	
--费用付款
left join ( select a2.pono , sum(a2.appAmt) appAmt  from PayMgrctrl a1 left join  paystockdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid=16  
		and a1.placed='2'
		group by a2.pono ) fy  on a.fno=fy.pono	
--费用发票登记
left join ( select a2.pono , sum(a2.poamt) poamt  from purMultiSettle a1 left join  purStlProdDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =300
		and a1.placed='2'
		group by a2.pono ) fybz  on a.fno=fybz.pono
		
-- 代理结算
left join ( select a2.pono , sum(a2.poamt) poamt  from saleSettle a1 left join  settleProdDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =39
		and a1.placed='2'
		group by a2.pono ) dljs on a.fno=dljs.pono 		
						

LEFT JOIN ( SELECT USER_CODE ,USER_name , user_desc , dept_id , user_id FROM View_User_Code ) u on a.create_by=u.USER_CODE
left join ( select id  deptid , DEPT_CODE deptcode ,DEPT_NAME deptname from SYS_DEPARTMENT ) dp on u.dept_id=dp.deptid
	
where ifnull(a.del_flag,0)<>1
and a.fno = 'SOS52090250002-P2'
and a.placed='2'
order by a.fours
------应收------
select 
dp.deptname 制单部门 , a.create_by_name  制单人 , a.create_time 单据日期 ,
a.fno 销售合同号 ,  a.serviceType 业务类型 ,a.fours , a.foursname 我方名称 , a.ctName 客商名称 ,
a.curcy 合同币别 , a.rate 折人民币汇率  , a.rateUs  合同执行美元汇率 , a.totalAmt 合同金额 ,fh.soamt 放货金额 , 
kp.soamt  销售开票金额 ,
ifnull(sk.rlamt,0) 已收款金额 ,
-- ifnull(sklc.totalAmt,0) 信用证收款 ,  
ifnull(kp.soamt,0) - ifnull(sk.rlamt,0) -ifnull(sklc.totalAmt,0)  应收金额,--发票金额-常规收款金额-信用证收款
fy.appamt  费用付款金额 , fybz.poamt 费用发票金额 , 
ifnull(fy.appamt,0)- ifnull(fybz.poamt,0) 费用应付金额,
xstk.appAmt  销售退款金额 ,
dl.poamt 代理产品金额 , dlcx1.soamt 代理产品冲销金额,
d2.poamt 代理费金额 , dlcx2.soamt 代理费冲销金额 

from  salesContract a 
--放货申请/通知
left join ( select a2.sono , sum(a2.soamt) soamt  from delivNotiReq a1 left join  delivNotiDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=450  group by a2.sono ) fh  on a.fno=fh.sono
--销售出库		
left join ( select a2.sono , sum(a2.soamt) soamt  from mdkcctrl a1 left join  mdkcdtl a2 on a1.id=a2.rid    
		where ifnull(a1.del_flag,0)<>1   
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=24  group by a2.sono ) ck  on a.fno=ck.sono		
--收款登记
left join ( select a2.sono , sum(a2.rlamt) rlamt  from breceiptctrl a1 left join  breceiptdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=19  
		and a1.payType='常规收款'
		group by a2.sono ) sk  on a.fno=sk.sono
--收证登记
left join ( select a2.sono , sum(a2.rlamt) totalAmt  from breceiptctrl a1 left join  breceiptdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1    and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=20   
		group by a2.sono ) sklc  on a.fno=sklc.sono		
--费用付款
left join ( select a2.sono , sum(a2.appAmt) appAmt  from PayMgrctrl a1 left join  paystockdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid=16  
		and a1.placed='2'
		group by a2.sono ) fy  on a.fno=fy.sono	
--费用发票登记
left join ( select a2.sono , sum(a2.poamt) poamt  from purMultiSettle a1 left join  purStlProdDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =300
		and a1.placed='2'
		group by a2.sono ) fybz  on a.fno=fybz.sono		
--销售结算开票	
left join ( select a2.sono , sum(a2.soamt) soamt  from saleSettle a1 left join  settleProdDtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =36
		and a1.placed='2'
		group by a2.sono ) kp  on a.fno=kp.sono	
		
--销售退款 
left join ( select a2.sono , sum(a2.appAmt) appAmt  from PayMgrctrl a1 left join  paystockdtl a2 on a1.id=a2.rid	
		where ifnull(a1.del_flag,0)<>1   
		and  ifnull(a2.del_flag,0)<>1
		and a1.placed='2'
		and a1.fmodalid=15  
		and a1.payType like '%退款%'
		group by a2.sono ) xstk  on a.fno=xstk.sono
		
--代理结算开票    产品明细金额
left join ( select a2.sono , sum(a2.poamt) poamt  from saleSettle a1 left join  settleProdDtl a2 on a1.id=a2.rid
		where ifnull(a1.del_flag,0)<>1	 and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =39
		and a1.placed='2'
		group by a2.sono ) dl  on a.fno=dl.sono

--代理结算开票    产品明细金额冲销金额
left join ( select a2.sono , sum(a2.amt) soamt from saleSettle a1 left join chargeReg a2 on a1.id=a2.rid
		where ifnull(a1.del_flag,0)<>1	 and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =39
		and a1.placed='2'
		and a2.fcat='1'
		group by a2.sono ) dlcx1  on a.fno=dl.sono		
--代理结算开票   代理费金额
left join ( select a2.sono , sum(a2.soamt) poamt  from saleSettle a1 left join  salesInvDtl a2 on a1.id=a2.rid
		where ifnull(a1.del_flag,0)<>1	 and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =39
		and a1.placed='2'
		group by a2.sono ) d2  on a.fno=d2.sono
--代理结算开票  代理费冲销金额
left join ( select a2.sono , sum(a2.amt) soamt from saleSettle a1 left join chargeReg a2 on a1.id=a2.rid
		where ifnull(a1.del_flag,0)<>1	 and  ifnull(a2.del_flag,0)<>1
		and a1.fmodalid =39
		and a1.placed='2'
		group by a2.sono ) dlcx2 on a.fno=dlcx2.sono
		
 
			
LEFT JOIN ( SELECT USER_CODE ,USER_name , user_desc , dept_id , user_id FROM View_User_Code ) u on a.create_by=u.USER_CODE
left join ( select id  deptid , DEPT_CODE deptcode ,DEPT_NAME deptname from SYS_DEPARTMENT ) dp on u.dept_id=dp.deptid
where ifnull(a.del_flag,0)<>1
and a.placed='2' 
--and a.fno = 'SOS52068250001'
order by a.fours;