sql

付款单与PO单
SELECT C.SEGMENT1,D.INVOICE_NUM
FROM APPS.AP_INVOICES_ALL D,
APPS.ap_invoice_distributions_all A,
APPS.PO_DISTRIBUTIONS_ALL B,
APPS.PO_HEADERS_ALL    C
WHERE  A.ORG_ID=B.ORG_ID
AND A.PO_DISTRIBUTION_ID=B.PO_DISTRIBUTION_ID
AND B.ORG_ID=C.ORG_ID
 AND  B.po_header_id=C.po_header_id
 AND C.ORG_ID=D.ORG_ID
AND  D.INVOICE_ID= a.INVOICE_ID
AND A.ORG_ID=439

查发票全部收完款的。
select PAYMENT_SCHEDULE_ID,cch.purchase_order_num,rcta.customer_trx_id,rcta.trx_number,
        AMOUNT_DUE_REMAINING,due_date,trunc(sysdate-due_date) over_days
 from   ar.ar_payment_schedules_all apsa,
        ar.ra_customer_trx_all rcta,
        inquiry.CMS_commission_HEADERs  cch
 where apsa.customer_trx_id=rcta.customer_trx_id
 and   rcta.org_id=439
 and   AMOUNT_DUE_REMAINING=0
 --and   STATUS='OP'
 --and   (due_date and   cch.customer_trx_id=rcta.customer_trx_id
 and   to_char(apsa.last_update_date,'yyyymm')='200803'
 --and   cch.com_month=to_char(ADD_MONTHS(v_date,-1),'yyyymm');
 and not exists (select 1 from apps.ar_payment_schedules_all
 where customer_trx_id=cch.customer_trx_id and status='OP')

------------------
通过发票号找到收款单号：
 select rcta.trx_number,acra.receipt_number from
 apps.ra_customer_trx_all  rcta,
 apps.ar_receivable_applications_all araa,
 apps.ar_cash_receipts_all   acra
 where
 rcta.customer_trx_id=araa.applied_customer_trx_id
 and rcta.org_id=araa.org_id
 and araa.cash_receipt_id=acra.cash_receipt_id
 and araa.org_id=acra.org_id
 and rcta.trx_number='12948' and rcta.org_id=439

 

-----------------------------------------------------------------------------

#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
rc.customer_name 客户名称,
rc.customer_number 客户编码,
raa.attribute2 区域,
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) 逾期金额,
 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=439    
group by
apsa.org_id,
rc.customer_name,
rc.customer_number,
raa.attribute2

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12122734/viewspace-480096/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12122734/viewspace-480096/