fin

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

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

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