【oracle】ERP系统账龄表sql

原创已于 2023-07-04 15:58:34 修改 · 471 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #sql #数据库

于 2023-07-04 15:56:41 首次发布

SQL 专栏收录该内容

1 篇文章

订阅专栏

该SQL查询用于生成账龄表，显示不同时间段（如15天、30天至3年以上）的应收账款总额，基于记账日期和核销状态等条件。它筛选了记账状态为20，发票状态为55，未核销金额大于0，未被逻辑删除，无红冲标记，且核销状态为10或20的记录。

-- 账龄表sql
SELECT
SETTLE_COMPANY_CODE 客商代码,
SETTLE_COMPANY_NAME 客商名称,
sum(NOT_WRITTEN_AMOUNT) 应收账款,
SUM(CASE WHEN KEEP_ACCOUNT_DATE  >= to_date(sysdate-15)  THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "15天",
SUM(CASE WHEN  to_date(sysdate-14) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-29)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "30天",
SUM(CASE WHEN to_date(sysdate-29) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-44)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "45天",
SUM(CASE WHEN to_date(sysdate-44) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-59)  THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "60天",
SUM(CASE WHEN  to_date(sysdate-59) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-74)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "75天",
SUM(CASE WHEN to_date(sysdate-74) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-89)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "90天",
SUM(CASE WHEN to_date(sysdate-89) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-119)  THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "120天",
SUM(CASE WHEN to_date(sysdate-119) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-179)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "180天",
SUM(CASE WHEN to_date(sysdate-179) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-364)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "1年",
SUM(CASE WHEN to_date(sysdate-364) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-729)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "2年",
SUM(CASE WHEN to_date(sysdate-729) >=KEEP_ACCOUNT_DATE  and KEEP_ACCOUNT_DATE >= to_date(sysdate-1094)   THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "3年",
SUM(CASE WHEN to_date(sysdate-1094) >= KEEP_ACCOUNT_DATE  THEN NOT_WRITTEN_AMOUNT ELSE 0 END) AS "3年以上"
FROM
MGWL.T_AR_RECEIPT_INVOICE where  KEEP_ACCOUNT_STATUS='20' and INVOICE_STATUS='55'and NOT_WRITTEN_AMOUNT >0
      AND IS_DELETED = '0'
      AND RED_REVERSE = 'NI'
      AND VERIFICATION_STATUS IN ( '10', '20' )
GROUP BY
SETTLE_COMPANY_NAME,SETTLE_COMPANY_CODE;


-- KEEP_ACCOUNT_STATUS：记账状态
-- NOT_WRITTEN_AMOUNT：发票未核销金额
-- IS_DELETED：是否逻辑删除
-- RED_REVERSE：红冲标记
-- VERIFICATION_STATUS：核销状态```