view \function\行转列的方法。

最新推荐文章于 2023-05-09 00:12:25 发布

转载最新推荐文章于 2023-05-09 00:12:25 发布 · 161 阅读

本文详细介绍了一种针对CH集团的帐龄报表实现方案。该方案通过创建临时视图AR_TEMP_V并定义函数AR_TEMP_F来计算不同合同的到期款项，并进一步通过SQL查询汇总展示按合同、客户及逾期天数划分的应收款项详情。

--------------CH集团帐龄报表 CH_ARAGINGNEW
#1
CREATE OR REPLACE VIEW AR_TEMP_V ( CUSTOMER_NUMBER,
CUSTOMER_NAME, CONTRACT_NUMBER, CATEGORY, REMARK_1,
REMARK_2, UPDATE_DATE, DEP_DATE, LAST_UPDATE_DATE,
AREA, AREA1, AREA2, HEADER_ID,
SALESPERSON, ORG_ID, PAYMENT_SCHEDULE_ID, DUE_DATE,
AMOUNT_DUE_ORIGINAL, AMOUNT_CREDITED, AMOUNT_DUE_REMAINING, STATUS,
INVOICE_CURRENCY_CODE, CLASS, GL_DATE, ARAA_APPLID_AMT
) AS select
rc.customer_number,
rc.customer_name,
--rcta.ATTRIBUTE2 contract_no,
sha.ATTRIBUTE2 contract_number,
--rcta.TRX_NUMBER,
rcta.ATTRIBUTE13 category,
rcta.ATTRIBUTE12 remark_1,
rcta.ATTRIBUTE11 remark_2,
rcta.ATTRIBUTE10 update_date,
dep.dep_date,
--ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE(rcta.CUSTOMER_TRX_ID, rcta.TERM_ID, rcta.TRX_DATE) term_due_date,
--rcta.TRX_DATE
rt.LAST_UPDATE_DATE,
raa.attribute2 area,
raa.attribute4 area1,
raa.attribute5 area2,
sha.header_id,
rsa.NAME salesperson,
apsa.org_id,
apsa.PAYMENT_SCHEDULE_ID,
apsa.DUE_DATE,
apsa.AMOUNT_DUE_ORIGINAL+nvl(apsa.AMOUNT_CREDITED,0) AMOUNT_DUE_ORIGINAL,
nvl(apsa.AMOUNT_CREDITED,0) AMOUNT_CREDITED,
apsa.AMOUNT_DUE_REMAINING,
apsa.STATUS,
apsa.INVOICE_CURRENCY_CODE,
apsa.CLASS,
apsa.gl_date,
nvl(app.araa_applid_amt,0) araa_applid_amt
from
ar.ar_payment_schedules_all apsa,
ar.ra_customers rc,
ar.ra_customer_trx_all rcta,
oe.so_headers_all sha,
ar.RA_ADDRESSES_ALL raa,
ar.ra_site_uses_all rsua,
(select sha1.ATTRIBUTE2 attr2,max(rt1.LAST_UPDATE_DATE) LAST_UPDATE_DATE
   from ar.ra_terms rt1,oe.so_headers_all sha1
   where sha1.terms_id=rt1.term_id(+)
   group by sha1.ATTRIBUTE2) rt,
ar.ra_salesreps_all rsa,
(select
     sum(decode(araa.APPLICATION_TYPE,'CASH',araa.AMOUNT_APPLIED,decode(araa.CUSTOMER_TRX_ID,null,araa.AMOUNT_APPLIED,0))) araa_applid_amt,
     araa.APPLIED_PAYMENT_SCHEDULE_ID,
     ARAA.ORG_ID araa_org_id
   from
     ar.ar_receivable_applications_all araa
   where
     araa.DISPLAY='Y'
     AND araa.STATUS='APP'
     and araa.gl_DATE<=nvl(sysdate,sysdate)+1
   group by araa.APPLIED_PAYMENT_SCHEDULE_ID,ARAA.ORG_ID ) app,
   (select
org_id,
header_id,
max(ACTUAL_DEPARTURE_DATE) dep_date
from
apps.ch_oracleso_serial_v
group by org_id,header_id) dep
where
apsa.customer_id=rc.customer_id and
apsa.org_id=rcta.org_id and
apsa.customer_trx_id=rcta.customer_trx_id and
rcta.org_id=sha.org_id(+) and
rcta.interface_header_attribute1=to_char(sha.order_number(+)) and
sha.org_id=raa.org_id(+) and
sha.customer_id=raa.customer_id(+) and
sha.org_id=rsua.org_id(+) and
sha.INVOICE_TO_SITE_USE_ID=rsua.SITE_USE_ID(+) and
sha.ATTRIBUTE2=rt.ATTR2(+) and

nvl(rsua.ADDRESS_ID,-1)=nvl(raa.ADDRESS_ID,-1) and
rsua.SITE_USE_CODE(+)='BILL_TO' and
sha.org_id=rsa.org_id(+) and
sha.salesrep_id=rsa.salesrep_id(+) and
apsa.PAYMENT_SCHEDULE_ID=app.APPLIED_PAYMENT_SCHEDULE_ID(+) and
apsa.org_id=app.araa_org_id(+) and
apsa.AMOUNT_DUE_ORIGINAL>0 and
apsa.AMOUNT_DUE_ORIGINAL+nvl(apsa.AMOUNT_CREDITED,0) <>0 and
apsa.gl_date<=nvl(sysdate,sysdate)+1 and
sha.org_id=dep.org_id and
sha.header_id=dep.header_id

---------------
#2

CREATE OR REPLACE FUNCTION ar_temp_f(
                        P_org_id      in number,
                        P_contract      in varchar2
                       )
                 return number IS

V_amount number DEFAULT NULL;

BEGIN
       select
       sum(round((((nvl(sla.ordered_quantity,0)-nvl(sla.cancelled_quantity,0))
*nvl(sla.selling_price,0))*(1+nvl(decode(avta.AMOUNT_INCLUDES_TAX_FLAG,'Y',0,avta.tax_rate),0)/100)),2))
into v_amount
from oe.so_headers_all sha,oe.so_lines_all sla,
       ar.ar_vat_tax_all avta
where sha.header_id=sla.header_id
and sla.ORDERED_QUANTITY-nvl(sla.CANCELLED_QUANTITY,0)>0
   and sla.org_id=avta.org_id(+)
   and   sla.tax_code=avta.tax_code(+)
   and sha.org_id=P_ORG_ID
and sha.ORDER_CATEGORY='R'
and nvl(avta.END_DATE,sysdate+1)>sysdate
and not exists
     (select 'x'    from oe.so_headers_all sha1,oe.so_lines_all sla1
    where sha1.header_id=sla1.header_id
       and sha1.org_id=sla1.org_id
       and sla1.ORDERED_QUANTITY-nvl(sla1.CANCELLED_QUANTITY,0)>0
       and sha1.ORDER_CATEGORY='RMA'
       and sla1.org_id=sha.org_id
        and sla1.RETURN_REFERENCE_ID=sla.line_id)
  and sha.attribute2=p_contract
  group by sha.attribute2;
return(V_amount);
exception
    when others then
     RETURN(NULL);
end ar_temp_f;
/
------------------------------------------
#3
select contract_number,
remark_1,
update_date,
dep_date,
category,
remark_2,
min(due_date),
area,
area2,
salesperson,
customer_number,
customer_name,
ar_temp_f(org_id,contract_number) contract_amount,
sum(decode(sign(0-(trunc(sysdate)-trunc(due_date))),1 ,amount_due_original-nvl(araa_applid_amt,0),null)) 小于0天,
sum(decode(sign(1-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于31天,
sum(decode(sign(31-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(60-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于61天,
sum(decode(sign(61-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(90-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于91天,
sum(decode(sign(91-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(180-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于181天,
sum(decode(sign(181-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(270-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于271天,
sum(decode(sign(271-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(365-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于366天,
sum(decode(sign(366-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(547-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于1年半,
sum(decode(sign(548-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(730-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于2年,
sum(decode(sign(731-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(1095-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于3年,
sum(decode(sign(1096-(trunc(sysdate)-trunc(due_date))),-1 ,amount_due_original-nvl(araa_applid_amt,0),null)) 大于3年
from ar_temp_v
where org_id=439 and contract_number='CH2008/0854' --and amount_due_original>nvl(araa_applid_amt,0)
group by
   contract_number,
remark_1,
update_date,
dep_date,
category,
remark_2,
area,
area2,
salesperson,
customer_number,
customer_name,
ar_temp_f(org_id,contract_number)
---------------------------------------
#

select contract_number 合同号,
remark_1 合同条款,
update_date 更新日期,
dep_date 出机日期,
category 分类状态,
remark_2 逾期原因,
min(due_date) 到款日期,
area 区域,
area2 办事处,
salesperson 销售员,
customer_number 客户编号,
customer_name 客户名称,
ar_temp_f(org_id,contract_number) 合同金额,
(nvl(sum(decode(sign(0-(trunc(sysdate)-trunc(due_date))),1 ,amount_due_original-nvl(araa_applid_amt,0),null)),0) +
nvl(sum(decode(sign(1-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(31-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(60-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(61-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(90-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(91-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(180-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(181-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(270-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(271-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(365-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(366-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(547-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(548-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(730-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(731-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(1095-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(1096-(trunc(sysdate)-trunc(due_date))),-1 ,amount_due_original-nvl(araa_applid_amt,0),null)),0)
) 至今尚欠金额
sum(decode(sign(0-(trunc(sysdate)-trunc(due_date))),1 ,amount_due_original-nvl(araa_applid_amt,0),null)) 未逾期金额,
(
nvl(sum(decode(sign(1-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(31-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(60-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(61-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(90-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(91-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(180-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(181-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(270-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(271-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(365-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0)+
nvl(sum(decode(sign(366-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(547-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(548-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(730-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(731-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(1095-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))),0) +
nvl(sum(decode(sign(1096-(trunc(sysdate)-trunc(due_date))),-1 ,amount_due_original-nvl(araa_applid_amt,0),null)),0)
) 逾期金额
sum(decode(sign(1-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(30-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于31天,
sum(decode(sign(31-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(60-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于61天,
sum(decode(sign(61-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(90-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于91天,
sum(decode(sign(91-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(180-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于181天,
sum(decode(sign(181-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(270-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于271天,
sum(decode(sign(271-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(365-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于366天,
sum(decode(sign(366-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(547-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于1年半,
sum(decode(sign(548-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(730-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于2年,
sum(decode(sign(731-(trunc(sysdate)-trunc(due_date))),1,null,decode(sign(1095-(trunc(sysdate)-trunc(due_date))),1,amount_due_original-nvl(araa_applid_amt,0),null))) as 小于3年,
sum(decode(sign(1096-(trunc(sysdate)-trunc(due_date))),-1 ,amount_due_original-nvl(araa_applid_amt,0),null)) 大于3年
from ar_temp_v
where org_id=439 --and contract_number='CH2008/0854' --and amount_due_original>nvl(araa_applid_amt,0)
group by
contract_number,
remark_1,
update_date,
dep_date,
category,
remark_2,
area,
area2,
salesperson,
customer_number,
customer_name,
ar_temp_f(org_id,contract_number)