Oracle AP Invoice Payment Check Join Question

From: C r a i g e r s <nospam.craigers_at_ukrm.org>
Date: 2000/09/21
Message-ID: <8qdsbt$qbv@gap.cco.caltech.edu>#1/1

Hello thereI
have a Oracle AP Invoice:Payment:Check Join Question

From this query:

SELECT VENDOR_NAME SUPPLIER, I.INVOICE_NUM, I.INVOICE_DATE INVOICE_DATE,

     to_char(I.CREATION_DATE, 'DD-MON-YYYY') INVOICE_CREATION_DATE,
     D.DISTRIBUTION_LINE_NUMBER DIST_NUMBER, D.AMOUNT DIST_AMT,
     C.AMOUNT DIST_AMT_PAID, D.DIST_CODE_COMBINATION_ID GL_CCID
FROM AP_INVOICES_ALL I, AP_INVOICE_DISTRIBUTIONS_ALL D,
     AP_INVOICE_PAYMENTS P, AP_CHECKS C

WHERE I.INVOICE_ID = D.INVOICE_ID
AND INVOICE_NUM = '999999' -- small recordset for test

AND I.INVOICE_ID = P.INVOICE_ID
AND P.CHECK_ID = C.CHECK_ID
AND I.CREATION_DATE > nvl(TO_DATE(:p_create_date, 'DD-MON-YYYY'),

'30-SEP-00')

AND I.INVOICE_DATE < nvl(TO_DATE(:p_invoice_date, 'DD-MON-YYYY'),
'01-OCT-00')
The result set appears like this:

BOOKS|990999|10/14/99|999999|10/13/99|10/14/99|1|244.64|1019.25|66789
                                     This col--------------------^^^^^^^

I am trying to get it to appear like this: ACADEMIC

BOOKS|990999|10/14/99|999999|10/13/99|10/14/99|1|244.64|244.64|66789
                                     This col--------------------^^^^^^

Is there a way to present the DIST_AMT_PAID as NOT the total check amount, but simply the dist_amt that has been paid? I guess I am asking where would I make the join, and I have examined the pertinent tables and cannot see an obvious answer.

My hunch is to NOT perform a calculation to obtain the result, as that seems like not the right way to go about it, but maybe that's wrong. Any help would be appreciated. Thanks.

--
D   o   t   a   c   i   o   n
U  S  A    C  a  l  i  f  o  r  n  i   a    L  o  s     A  n  g  e  l  e  $

The main reason Santa is so jolly is because he knows where all the bad
girls live.
      -- George Carlin

Received on Thu Sep 21 2000 - 00:00:00 CDT