付款单与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