arag表sql

CREATE OR REPLACE VIEW CH_ARAG_V311_NEW  AS
select   
rc.customer_name 客户名称,   
rc.customer_number 客户编码,   
raa.attribute2 区域, 
rcta.ATTRIBUTE13 分类状态,   
sum(apsa.AMOUNT_DUE_ORIGINAL)  合同总金额,   
sum(apsa.AMOUNT_DUE_REMAINING) 至今尚欠金额,   
nvl(sum(decode(sign(0-(trunc(sysdate)-trunc(apsa.due_date))),1 ,apsa.AMOUNT_DUE_REMAINING,null)),0) 未逾期金额, 
sum(decode(sign(1-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(30-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期30天,   
sum(decode(sign(31-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(60-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期60天,   
sum(decode(sign(61-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(90-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期90天,   
sum(decode(sign(91-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(120-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期120天,   
sum(decode(sign(121-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(150-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期150天,   
sum(decode(sign(151-(trunc(due_date)-trunc(sysdate))),1,null,decode(sign(180-(trunc(due_date-trunc(sysdate)))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 未逾期180天,   
sum(decode(sign(181-(trunc(due_date)-trunc(sysdate))),-1 ,apsa.AMOUNT_DUE_REMAINING,null)) 未逾期181天,
  
sum(apsa.AMOUNT_DUE_REMAINING)-nvl(sum(decode(sign(0-(trunc(sysdate)-trunc(apsa.due_date))),1 ,apsa.AMOUNT_DUE_REMAINING,null)),0) 逾期金额,  
sum(decode(sign(0-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于31天,   
sum(decode(sign(31-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(60-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于61天,   
sum(decode(sign(61-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(90-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于91天,    
sum(decode(sign(91-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(180-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于181天,   
sum(decode(sign(181-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(270-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于271天,   
sum(decode(sign(271-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(365-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于366天,   
sum(decode(sign(366-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(547-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于1年半,   
sum(decode(sign(548-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(730-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于2年,   
sum(decode(sign(731-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(1095-(trunc(sysdate)-trunc(due_date))),1,apsa.AMOUNT_DUE_REMAINING,0,apsa.AMOUNT_DUE_REMAINING,null))) as 小于3年,   
sum(decode(sign(1096-(trunc(sysdate)-trunc(due_date))),-1 ,apsa.AMOUNT_DUE_REMAINING,null)) 大于3年,
rcta.ATTRIBUTE12 合同条款,
'' 货款跟进情况, 
rcta.ATTRIBUTE10 更新日期,    
'' 销售回复本月已收到款项,   
'' 期票,   
'' 财务确认,   
'' 销售回复本月将会收到款项,   
'' 上层,   
'' LP,   
'' 零八年11月期票,   
'' 零八年12月期票,   
'' 零九年1月期票,   
'' 零九年2月期票,   
'' 零九年3月期票,   
'' 零九年4月期票,   
'' 零九年5月期票,   
'' 零九年6月期票,   
'' 零九年7月期票,   
'' 零九年8月期票,   
'' 零九年9月期票   
 from    
apps.ar_payment_schedules_all apsa,   
apps.ra_customers  rc,   
apps.ra_customer_trx_all rcta,   
apps.so_headers_all  sh,   
apps.ra_site_uses_all     rsu,   
apps.ra_addresses_all     raa   
where    
apsa.customer_id=rc.customer_id   
and apsa.customer_trx_id=rcta.customer_trx_id   
and apsa.org_id=rcta.org_id   
and    rcta.INTERFACE_HEADER_ATTRIBUTE1=to_char(sh.order_number)   
and    sh.INVOICE_TO_SITE_USE_ID=rsu.site_use_id   
and    rsu.address_id=raa.address_id   
and apsa.AMOUNT_DUE_REMAINING>0   
and apsa.org_id=439   
group by    
apsa.org_id,   
rc.customer_name,   
rc.customer_number,   
raa.attribute2,
rcta.ATTRIBUTE13,
rcta.ATTRIBUTE12,
rcta.ATTRIBUTE10

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-503370/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12122734/viewspace-503370/