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

------应收（新）------
-----新应收------
-----新应收------
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) 常规收款不含LC ,
ifnull(sklc.rlamt,0) 常规收款LC , 
ifnull(skSZ.totalAmt,0 ) 认证金额 ,
xstk.appAmt  销售退款金额 ,

ifnull(kp.soamt,0) - ifnull(sk.rlamt,0) -ifnull(sklc.rlamt,0) + ifnull(xstk.appAmt,0) 非代理应收金额,--发票金额-常规收款金额-信用证收款

ifnull(dl.poamt,0) - ifnull(sk.rlamt,0) - ifnull(sklc.rlamt,0)+ ifnull(xstk.appAmt,0) 真代理业务应收金额 , 

( ifnull(kp.soamt,0)+ ifnull(dl.poamt,0))   - ifnull(sk.rlamt,0) -ifnull(sklc.rlamt,0) + ifnull(xstk.appAmt,0) 应收金额,--发票金额-常规收款金额-信用证收款


fy.appamt  费用付款金额 , fybz.poamt 费用发票金额 , 
ifnull(fy.appamt,0)- ifnull(fybz.poamt,0) 费用应付金额,

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='常规收款'
		and ifnull(a1.rcptMethod,'')<>'LC'
		group by a2.sono ) sk  on a.fno=sk.sono
 
--收款登记lc
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='常规收款'
		and ifnull(a1.rcptMethod,'')='LC'
		group by a2.sono ) sklc  on a.fno=sklc.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 ) skSZ  on a.fno=skSZ.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 = 'SOS52131250002'
order by a.fours;