Oracle逾期账款分析视图-优快云博客

REATE OR REPLACE VIEW CH_ARAG_V368_NEW ( 客户名称,
客户编码, 区域, 分类状态, 合同总金额,
至今尚欠金额, 未逾期金额, 逾期金额, 小于31天,
小于61天, 小于91天, 小于181天, 小于271天,
小于366天, 小于1年半, 小于2年, 小于3年,
大于3年, 合同条款, 货款跟进情况, 更新日期,
销售回复本月已收到款项, 期票, 财务确认, 销售回复本月将会收到款项,
上层, LP, 零八年11月期票, 零八年12月期票,
零九年1月期票, 零九年2月期票, 零九年3月期票, 零九年4月期票,
零九年5月期票, 零九年6月期票, 零九年7月期票, 零九年8月期票,
零九年9月期票 ) 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(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(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,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,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,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,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,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,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,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,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,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=255
group by
apsa.org_id,
rc.customer_name,
rc.customer_number,
raa.attribute2,
rcta.ATTRIBUTE13,
rcta.ATTRIBUTE12,
rcta.ATTRIBUTE10
-----------------------------------