按月查询客户余额报表的步骤和SQL脚本
来源:互联网 发布:mac充电器怎么拆 编辑:程序博客网 时间:2024/06/10 17:34
(1) 收款总额
SELECT SUM (DECODE (acra.currency_code, 'CNY', acra.amount, acra.amount * acra.exchange_rate) )
FROM ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrha,
ra_site_uses_all rsua
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID
AND rsua.site_use_code = 'BILL_TO'
AND acrha.gl_date <= to_date('20030228','yyyymmdd')
AND ((
( acra.receipt_method_id = 1042 ----票据类收款的id
AND acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED') ----根据用户何时确定作为收款为准,我这儿应收票据一旦确认就算收款)
AND nvl(acrha.current_record_flag,'Y') = 'Y')
)
OR (acra.receipt_method_id <> 1042 AND nvl(acrha.current_record_flag,'N') = 'Y'))
AND EXISTS (SELECT 'A'
FROM ar_cash_receipt_history_all T
WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
AND T.current_record_flag = 'Y'
AND T.status != 'REVERSED')
AND acra.org_id = 1
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND UPPER (acrha.status) != 'REVERSED'
(2)开单总额
SELECT SUM (DECODE (rcta.invoice_currency_code, 'CNY', rctla.extended_amount, rctla.extended_amount * rcta.exchange_rate))
FROM ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
AND UPPER (rctta.post_to_gl) = 'Y'
AND UPPER (rctta.accounting_affect_flag) = 'Y'
AND rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
AND UPPER (rctlgda.account_class) = 'REC'
AND UPPER (rctlgda.latest_rec_flag) = 'Y'
AND rctlgda.customer_trx_id = rcta.customer_trx_id
AND UPPER (rcta.complete_flag) = DECODE (
UPPER ('n')
, 'Y', UPPER (rcta.complete_flag)
, 'N', 'Y'
)
AND rsua.site_use_code = 'BILL_TO'
(3)开票已核销额
SELECT SUM (DECODE(rcta.invoice_currency_code,'CNY',
DECODE (
UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
, 'CMCM'
, -1 * NVL (araa.amount_applied, 0)
, NVL (araa.amount_applied, 0)
),
DECODE (
UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
, 'CMCM'
, -1 * NVL (araa.amount_applied, 0)
, NVL (araa.amount_applied, 0)
) * rcta.exchange_rate
)
)
FROM ra_customer_trx_all rcta
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
AND UPPER (rctta.post_to_gl) = 'Y'
AND UPPER (rctta.accounting_affect_flag) = 'Y'
AND rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
AND UPPER (rctlgda.account_class) = 'REC'
AND UPPER (rctlgda.latest_rec_flag) = 'Y'
AND rctlgda.customer_trx_id = rcta.customer_trx_id
AND UPPER (rcta.complete_flag) = DECODE (
UPPER ('n')
, 'Y', UPPER (rcta.complete_flag)
, 'N', 'Y'
)
AND rsua.site_use_code = 'BILL_TO' -- 2002/08/22
AND (araa.applied_customer_trx_id = rcta.customer_trx_id
OR araa.customer_trx_id = rcta.customer_trx_id
)
AND araa.display = 'Y'
AND araa.gl_date <= to_date('20030331','yyyymmdd')
(4)收款已核销额
SELECT acra.cash_receipt_id,acra.receipt_number,decode(acra.currency_code,'CNY',
NVL (araa.amount_applied*nvl(araa.trans_to_receipt_rate,1), 0),
NVL (araa.amount_applied, 0)*acra.exchange_rate*nvl(araa.trans_to_receipt_rate,1))
FROM ar_cash_receipts_all acra
, ar_cash_receipt_history_all acrha
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rsua.site_use_code = 'BILL_TO' -- added by Devy on 2002/07/30
AND acrha.gl_date <= to_date('20030331','yyyymmdd')
AND ((
( acra.receipt_method_id = 1042
AND acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED')
AND nvl(acrha.current_record_flag,'Y') = 'Y'
)
)
OR (acra.receipt_method_id <> 1042
AND nvl(acrha.current_record_flag,'N') = 'Y'))
AND EXISTS (SELECT 'A'
FROM ar_cash_receipt_history_all T
WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
AND T.current_record_flag = 'Y'
AND T.status != 'REVERSED')
AND acra.org_id = 1
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND UPPER (acrha.status) != 'REVERSED'
AND araa.cash_receipt_id = acra.cash_receipt_id
AND araa.display = 'Y'
AND araa.gl_date <= to_date('20030331','yyyymmdd') -- NO SPECIFY PERIOD_NAME
AND araa.applied_customer_trx_id <> -1
(5)汇兑损益
create or replace view AR_EXCHANGE_GAIN_LOSS_V as
select app.cash_receipt_id,sum(APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO,APP.ACCTD_AMOUNT_APPLIED_FROM)) EXCHANGE_GAIN_LOSS
FROM AR_RECEIVABLE_APPLICATIONS APP
where app.display = 'Y'
group by app.cash_receipt_id
(6)当期开单总额,当期收款总额
在计算总额时限定rctlgda.gl_date的起始日期
(7)当期开单核销额,当期收款核销额
在计算总额时限定araa.gl_date的起始日期
(8)公式:期末余额 =(开单总额-已核销额)-(收款总额-已核销额)
期初余额 = 期末余额 - (当期开单总额-当期开单核销额)+ (当期收款总额 - 当期收款核销额)
SELECT SUM (DECODE (acra.currency_code, 'CNY', acra.amount, acra.amount * acra.exchange_rate) )
FROM ar_cash_receipts_all acra,
ar_cash_receipt_history_all acrha,
ra_site_uses_all rsua
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID
AND rsua.site_use_code = 'BILL_TO'
AND acrha.gl_date <= to_date('20030228','yyyymmdd')
AND ((
( acra.receipt_method_id = 1042 ----票据类收款的id
AND acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED') ----根据用户何时确定作为收款为准,我这儿应收票据一旦确认就算收款)
AND nvl(acrha.current_record_flag,'Y') = 'Y')
)
OR (acra.receipt_method_id <> 1042 AND nvl(acrha.current_record_flag,'N') = 'Y'))
AND EXISTS (SELECT 'A'
FROM ar_cash_receipt_history_all T
WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
AND T.current_record_flag = 'Y'
AND T.status != 'REVERSED')
AND acra.org_id = 1
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND UPPER (acrha.status) != 'REVERSED'
(2)开单总额
SELECT SUM (DECODE (rcta.invoice_currency_code, 'CNY', rctla.extended_amount, rctla.extended_amount * rcta.exchange_rate))
FROM ra_customer_trx_all rcta
, ra_customer_trx_lines_all rctla
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rcta.customer_trx_id = rctla.customer_trx_id
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
AND UPPER (rctta.post_to_gl) = 'Y'
AND UPPER (rctta.accounting_affect_flag) = 'Y'
AND rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
AND UPPER (rctlgda.account_class) = 'REC'
AND UPPER (rctlgda.latest_rec_flag) = 'Y'
AND rctlgda.customer_trx_id = rcta.customer_trx_id
AND UPPER (rcta.complete_flag) = DECODE (
UPPER ('n')
, 'Y', UPPER (rcta.complete_flag)
, 'N', 'Y'
)
AND rsua.site_use_code = 'BILL_TO'
(3)开票已核销额
SELECT SUM (DECODE(rcta.invoice_currency_code,'CNY',
DECODE (
UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
, 'CMCM'
, -1 * NVL (araa.amount_applied, 0)
, NVL (araa.amount_applied, 0)
),
DECODE (
UPPER (UPPER (rctta.TYPE) ) || UPPER (araa.application_type)
, 'CMCM'
, -1 * NVL (araa.amount_applied, 0)
, NVL (araa.amount_applied, 0)
) * rcta.exchange_rate
)
)
FROM ra_customer_trx_all rcta
, ra_cust_trx_types_all rctta
, ra_cust_trx_line_gl_dist_all rctlgda
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE rcta.bill_to_customer_id = 4481
AND rcta.bill_to_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = RCTA.BILL_TO_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
AND UPPER (rctta.TYPE) IN ('INV', 'CM', 'DM')
AND UPPER (rctta.post_to_gl) = 'Y'
AND UPPER (rctta.accounting_affect_flag) = 'Y'
AND rctlgda.gl_date <= to_date('20030331','yyyymmdd')
AND rcta.org_id = 1
AND UPPER (rctlgda.account_class) = 'REC'
AND UPPER (rctlgda.latest_rec_flag) = 'Y'
AND rctlgda.customer_trx_id = rcta.customer_trx_id
AND UPPER (rcta.complete_flag) = DECODE (
UPPER ('n')
, 'Y', UPPER (rcta.complete_flag)
, 'N', 'Y'
)
AND rsua.site_use_code = 'BILL_TO' -- 2002/08/22
AND (araa.applied_customer_trx_id = rcta.customer_trx_id
OR araa.customer_trx_id = rcta.customer_trx_id
)
AND araa.display = 'Y'
AND araa.gl_date <= to_date('20030331','yyyymmdd')
(4)收款已核销额
SELECT acra.cash_receipt_id,acra.receipt_number,decode(acra.currency_code,'CNY',
NVL (araa.amount_applied*nvl(araa.trans_to_receipt_rate,1), 0),
NVL (araa.amount_applied, 0)*acra.exchange_rate*nvl(araa.trans_to_receipt_rate,1))
FROM ar_cash_receipts_all acra
, ar_cash_receipt_history_all acrha
, ra_site_uses_all rsua
, ar_receivable_applications_all araa
WHERE acra.pay_from_customer = 4481
AND acra.customer_site_use_id = rsua.site_use_id
AND RSUA.SITE_USE_ID = acra.CUSTOMER_SITE_USE_ID -------- added by Devy on 2002/08/27
AND rsua.site_use_code = 'BILL_TO' -- added by Devy on 2002/07/30
AND acrha.gl_date <= to_date('20030331','yyyymmdd')
AND ((
( acra.receipt_method_id = 1042
AND acrha.status NOT IN( 'REMITTED','CLEARED','RISK_ELIMINATED')
AND nvl(acrha.current_record_flag,'Y') = 'Y'
)
)
OR (acra.receipt_method_id <> 1042
AND nvl(acrha.current_record_flag,'N') = 'Y'))
AND EXISTS (SELECT 'A'
FROM ar_cash_receipt_history_all T
WHERE T.CASH_RECEIPT_ID = acrha.cash_receipt_id
AND T.current_record_flag = 'Y'
AND T.status != 'REVERSED')
AND acra.org_id = 1
AND acra.cash_receipt_id = acrha.cash_receipt_id
AND UPPER (acrha.status) != 'REVERSED'
AND araa.cash_receipt_id = acra.cash_receipt_id
AND araa.display = 'Y'
AND araa.gl_date <= to_date('20030331','yyyymmdd') -- NO SPECIFY PERIOD_NAME
AND araa.applied_customer_trx_id <> -1
(5)汇兑损益
create or replace view AR_EXCHANGE_GAIN_LOSS_V as
select app.cash_receipt_id,sum(APP.ACCTD_AMOUNT_APPLIED_FROM - NVL(APP.ACCTD_AMOUNT_APPLIED_TO,APP.ACCTD_AMOUNT_APPLIED_FROM)) EXCHANGE_GAIN_LOSS
FROM AR_RECEIVABLE_APPLICATIONS APP
where app.display = 'Y'
group by app.cash_receipt_id
(6)当期开单总额,当期收款总额
在计算总额时限定rctlgda.gl_date的起始日期
(7)当期开单核销额,当期收款核销额
在计算总额时限定araa.gl_date的起始日期
(8)公式:期末余额 =(开单总额-已核销额)-(收款总额-已核销额)
期初余额 = 期末余额 - (当期开单总额-当期开单核销额)+ (当期收款总额 - 当期收款核销额)
- 按月查询客户余额报表的步骤和SQL脚本
- SQL 按月份查询报表
- 查询月末余额不足一百的客户信息
- 按月统计订单数量报表SQL语句(CASE 和DECODE的使用)
- SQL 按月查询
- sql按月进行查询
- sql日期按月份和年份分组查询
- sql 数据库中按月份的组查询
- 按月、周、日查询统计数据量的sql语句
- 用友--客户科目余额表sql语句:注意两条语句结果集的合并
- 本日、本周、本月和按月取数据的SQL语句
- sql查询做的报表实现自定义过滤和排序
- SQL 时间截按月分组查询
- SQL统计查询(按月份)
- SQL 语句按月份统计查询
- 按月统计的sql语句
- sql的复杂查询步骤
- sql查询单个项目的全年总和,按月份统计怎么实现?
- 类变量的初始化时机(摘录自java突破程序员基本功德16课)
- struts2 18拦截器详解(十) --- ModelDrivenInterceptor
- java基础——重写和重载
- liun系统命令大全学习笔记(附加命令使用方法)
- char、varchar、nchar、nvarchar的区别
- 按月查询客户余额报表的步骤和SQL脚本
- 探讨android 导航栏中的recent_app界面
- (使用STL自带的排序功能进行排序7.3.2)POJ 2092 Grandpa is Famous(结构体排序)
- java.lang.OutOfMemoryError: PermGen space及其解决方法
- iOS开发-一些面试算法总结
- Python学习小节1
- 调试没有符号的驱动时如何断在入口点处
- GoAgent原理与实现(二):本地代理的C#试验
- HDU 4418 Time travel (概率,高斯消元)