自己写的一段有关应付帐龄分析的SQL，请参考

bhbb2000

select aps.org_id,pv.vendor_name vendor_name,pv.segment1 vendor_number,gcc.segment3 code_number,ffvt.Description code_desc,
ppa.name project_name,aia.invoice_num invoice_number,Sum(aps.amount_remaining) sum_ap,          --
sum(decode(ap_pre_analysis.account(aia.terms_date),'1',aps.amount_remaining,0)) half_year,    --
sum(decode(ap_pre_analysis.account(aia.terms_date),'2',aps.amount_remaining,0)) one_year,     --
sum(decode(ap_pre_analysis.account(aia.terms_date),'3',aps.amount_remaining,0)) two_year,     --
sum(decode(ap_pre_analysis.account(aia.terms_date),'4',aps.amount_remaining,0)) three_year,   --
sum(decode(ap_pre_analysis.account(aia.terms_date),'5',aps.amount_remaining,0)) three_year_up --
from ap_payment_schedules_all aps,ap_invoices_all aia,po_vendors pv,pa_projects_all ppa,
     po_vendor_sites_all ps,gl_code_combinations gcc,fnd_flex_values ffv,fnd_flex_values_tl ffvt
where aps.invoice_id = aia.invoice_id
and aia.vendor_id = pv.vendor_id(+)
and ppa.project_id(+) = aia.project_id
and (aia.payment_status_flag = 'N' or aia.payment_status_flag = 'P')
And pv.vendor_id = ps.vendor_id And ps.prepay_code_combination_id = gcc.code_combination_id
And gcc.segment3 = ffv.flex_value And ffv.flex_value_id(+) = ffvt.flex_value_id
And ffvt.Language = 'ZHS'
group by pv.vendor_name,gcc.segment3,ffvt.description,aps.org_id,pv.segment1,ppa.name,aia.invoice_num
Having Sum(aps.amount_remaining)<>0


create or replace package body ap_pre_analysis is

  -- Private type declarations
  function account(term_date date) return varchar2 is
    term_y number;
    term_m number;
    sys_y number;
    sys_m number;
    a_num number;
    f varchar2(10);
  begin
    select to_number(to_char(term_date,'yyyy')) into term_y from dual;
    select to_number(to_char(term_date,'mm')) into term_m from dual;
    select to_number(to_char(sysdate,'yyyy')) into sys_y from dual;
    select to_number(to_char(sysdate,'mm')) into sys_m from dual;
   
    if term_y = sys_y then
       if sys_m-term_m <= 6 then f := 1;
       else f := 2;
       end if;
    else
       if sys_y - term_y = 1 then
          if sys_m+(12-term_m) < 6 then f := 1;
          else
             if sys_m< term_m then f := 2;
             else f := 3;
             end if;
          end if;
       else
          if sys_y - term_y = 2 then
             if sys_m < term_m then f := 3;
             else f := 4;
             end if;
          else
             if sys_y - term_y = 3 then
                if sys_m < term_m then f := 4;
                else f:= 5;
                end if;
             else
                f := 5;
             end if;
          end if;
       end if;
    end if;
   
    return(f);
  End account;
end ap_pre_analysis;