• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 593
  • Last Modified:

Need help with a left join in a sql for paid and unpaid invoices in Oracle Payables

Trying to include invoices that are open or only partially paid in a query that now shows only paid invoices. This is my current sql and despite the left joins, it doesn't output any invoices known to be unpaid

select hou.name operating_unit
    ,pv.vendor_name
    ,pv.segment1 vendor#
    ,ac.check_number check#
    ,ac.check_date
    ,ac.amount check_Amt
    ,ai.invoice_num Inv#
    ,ai.invoice_date
    ,ai.invoice_amount Inv_Amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from ap_checks_all ac
    join ap_invoice_payments_all aip
        on ac.check_id = aip.check_id
    join hr_operating_units hou
        on ac.org_id = hou.organization_id
    left join ap_invoices_all ai
        on ai.invoice_id = aip.invoice_id
        and ai.cancelled_date is null
    left join po_vendors pv
        on pv.vendor_id = ac.vendor_id
        and pv.vendor_id = ai.vendor_id
    where ac.void_date is null
and trunc(ai.invoice_date) >= '01-JAN-06'
    and pv.segment1 in ('33115','4597','27865')
group by hou.name
    ,pv.vendor_name
    ,pv.segment1
    ,ac.check_number
    ,ac.check_date
    ,ac.status_lookup_code
    ,ac.amount
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid
    ,aip.amount
    ,aip.accounting_date
0
janthonyn
Asked:
janthonyn
  • 8
  • 6
  • 4
  • +2
2 Solutions
 
dqmqCommented:
Probably because this line does not test true with nulls in invoice_date:

and trunc(ai.invoice_date) >= '01-JAN-06'

try replacing it with:

and (ai.invoice_date IS NULL or trunc(ai.invoice_date) >= '01-JAN-06')

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this is primarily because you had a WHERE clause with conditions on the columns of those tables you have left joined to, which makes it implicitely a inner join...
the remark of dqmq might be helpful also, you might want to integrate it also, if the below is not enough

select hou.name operating_unit
    ,pv.vendor_name
    ,pv.segment1 vendor#
    ,ac.check_number check#
    ,ac.check_date
    ,ac.amount check_Amt
    ,ai.invoice_num Inv#
    ,ai.invoice_date
    ,ai.invoice_amount Inv_Amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from ap_checks_all ac
    join ap_invoice_payments_all aip
        on ac.check_id = aip.check_id
    join hr_operating_units hou
        on ac.org_id = hou.organization_id
    left join ap_invoices_all ai
        on ai.invoice_id = aip.invoice_id
        and ai.cancelled_date is null
        and trunc(ai.invoice_date) >= '01-JAN-06'
    left join po_vendors pv
        on pv.vendor_id = ac.vendor_id
        and pv.vendor_id = ai.vendor_id
        and pv.segment1 in ('33115','4597','27865')
    where ac.void_date is null
group by hou.name
    ,pv.vendor_name
    ,pv.segment1
    ,ac.check_number
    ,ac.check_date
    ,ac.status_lookup_code
    ,ac.amount
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid
    ,aip.amount
    ,aip.accounting_date
0
 
dqmqCommented:
AngelIII is right, I didn't solve the whole problem.  Exactly the same issue (about not testing true with the nulls resulting from a left join) applies to pv.segment1. So you need to do this

and ( pvs.segment1 is null or pv.segment1 in ('33115','4597','27865'))
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
janthonynAuthor Commented:
So far nothing you suggest has worked. Haven't figured out why, but here's a question. Why would any invoice_date or vendor number (pv.segment1)  be null? What I need is for records with null check_dates to return
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please change this:
        and trunc(ai.invoice_date) >= '01-JAN-06'

into:
        and ai.invoice_date >= TO_DATE('01-JAN-06', 'DD-MON-YY')
0
 
janthonynAuthor Commented:
Here is what my query looks like now and I'm still not seeing any non-paid invoices:

select hou.name operating_unit
    ,pv.vendor_name
    ,pv.segment1 vendor#
    ,ac.check_number check#
    ,to_char(ac.check_date,'DD-MON-YY') chk_dt
    ,ac.amount check_Amt
    ,ai.invoice_num inv#
    ,ai.invoice_date inv_dt
    ,ai.invoice_amount inv_amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from ap_checks_all ac
    join ap_invoice_payments_all aip
        on ac.check_id = aip.check_id
    left join ap_invoices_all ai
        on ai.invoice_id = aip.invoice_id
        and ai.cancelled_date is null
            join hr_operating_units hou
        on ai.org_id = hou.organization_id
    left join po_vendors pv
        on pv.vendor_id = ac.vendor_id
        and pv.vendor_id = ai.vendor_id
    where ac.void_date is null
--and ac.check_date is null or trunc(ac.check_date) >= '01-JAN-06'
  and ai.invoice_date >= to_date('01-JAN-06','DD-MON-YY')
    and pv.segment1 in ('33115','4597','27865')
group by hou.name
    ,pv.vendor_name
    ,pv.segment1
    ,ac.check_number
    ,ac.check_date
    ,ac.status_lookup_code
    ,ac.amount
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think, we need to find out now, how do you identify unpaid invoices (in plain english).
0
 
janthonynAuthor Commented:
It all has to do with if a check or wire has been processed for a certain invoice, thus establishing an association with an invoice_id and corresponding payment.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>if a check or wire has been processed
how do you identify that, speaking column names and values?
0
 
dqmqCommented:
>Why would any invoice_date or vendor number (pv.segment1)  be null?

Because you did an outer (left) join, which means that ify you have a payment or a check without an invoice you get nulls for the invoice columns and the vendor columns.  On second thought, that DOES sound silly, so I think maybe the problem is that you are using the wrong kind of join.


Try this:

select hou.name operating_unit
    ,pv.vendor_name
    ,pv.segment1 vendor#
    ,ac.check_number check#
    ,to_char(ac.check_date,'DD-MON-YY') chk_dt
    ,ac.amount check_Amt
    ,ai.invoice_num inv#
    ,ai.invoice_date inv_dt
    ,ai.invoice_amount inv_amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from  ap_invoices_all ai
  left join ap_invoice_payments_all aip
        on ai.invoice_id = aip.invoice_id
  left join ap_checks_all ac
        on ac.check_id = aip.check_id
  left join hr_operating_units hou
        on ai.org_id = hou.organization_id
  left join po_vendors pv
        on pv.vendor_id = ai.vendor_id
where ac.void_date is null
    and ai.cancelled_date is null
    and trunc(ac.check_date) >= '01-JAN-06'
    and (pv.segment1 in ('33115','4597','27865') or pv.segment1 is null)
group by hou.name
    ,pv.vendor_name
    ,pv.segment1
    ,ac.check_number
    ,ac.check_date
    ,ac.status_lookup_code
    ,ac.amount
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid
0
 
janthonynAuthor Commented:
dqmq,

Your sql is closer to the right answer. It output fully paid and partially paid invoices with the associated checks. But I'm still looking for invoices with no checks whatsoever. There are many of those. Open invoices. With nothing paid on an invoice, there are no records in the ap_checks_all table.
0
 
dqmqCommented:
So, we've come 'round-about to my first advice.   Basically, if a field is null, you have to test for that condition explicitly--none of the comparison operators yield true or false when one of the operands is null.


Try this:

select hou.name operating_unit
    ,pv.vendor_name
    ,pv.segment1 vendor#
    ,ac.check_number check#
    ,to_char(ac.check_date,'DD-MON-YY') chk_dt
    ,ac.amount check_Amt
    ,ai.invoice_num inv#
    ,ai.invoice_date inv_dt
    ,ai.invoice_amount inv_amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from  ap_invoices_all ai
  left join ap_invoice_payments_all aip
        on ai.invoice_id = aip.invoice_id
  left join ap_checks_all ac
        on ac.check_id = aip.check_id
  left join hr_operating_units hou
        on ai.org_id = hou.organization_id
  left join po_vendors pv
        on pv.vendor_id = ai.vendor_id
where ac.void_date is null
    and ai.cancelled_date is null
    and (trunc(ac.check_date) >= '01-JAN-06' or ac.check_date is null)
    and (pv.segment1 in ('33115','4597','27865') or pv.segment1 is null)
group by hou.name
    ,pv.vendor_name
    ,pv.segment1
    ,ac.check_number
    ,ac.check_date
    ,ac.status_lookup_code
    ,ac.amount
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid
0
 
janthonynAuthor Commented:
Thanks for this latest attempt. Still not getting records without checks. All unpaid invoices for these vendors are missing from the query output.
0
 
dqmqCommented:
OK, let's see if I have this straight.  You have records in ap_invoices_all table that are not showing up in the results. Is that correct?  

Now, I suggest temporarily commenting out a couple of the conditions to see if the invoices show up.  



where
            ac.void_date is null
--    and ai.cancelled_date is null
    and (trunc(ac.check_date) >= '01-JAN-06' or ac.check_date is null)
--    and (pv.segment1 in ('33115','4597','27865') or pv.segment1 is null)
0
 
janthonynAuthor Commented:
dqmq

The invoices that aren't paid aren't cancelled, so commenting out the ai.cancelled_date is null condition won't get to the problem with the joins. The pv.segment1 column is the vendor number column and restricts the query to those vendors.
0
 
dqmqCommented:
I don't think there is a problem with the joins.  Really, given all the left joins, the only possibility remaining is that vendorid is not the right column to join on.  There's little left besides the "where clause" to explain why you are not seeing the records.  I mean, with all those left joins, every invoice should display except those excluded by the where conditions. So, I just want to rule out the possibility that either of those conditions is having unintended/unexpected results.  

If you like, we can start small and build up. Does this return the invoices you are looking for:

select
     pv.vendor_name
    ,pv.segment1 vendor#
    ,ai.invoice_num inv#
    ,ai.invoice_date inv_dt
    ,ai.invoice_amount inv_amt
    ,sum(ai.invoice_amount - ai.amount_paid) bal_open
from  ap_invoices_all ai
  left join po_vendors pv
        on pv.vendor_id = ai.vendor_id
where
    ai.cancelled_date is null
    and (pv.segment1 in ('33115','4597','27865') or pv.segment1 is null)
group by
     pv.vendor_name
    ,pv.segment1
    ,ai.invoice_num
    ,ai.invoice_date
    ,ai.invoice_amount
    ,ai.amount_paid



If no, then fix the where conditions.
If yes, then add a left join to one table at a time (gradually building up to your desired query) until they disappear.  That process should help you hone in on the problem.
0
 
janthonynAuthor Commented:
I have a hunch that the problem this query has eminates from not having included the ap_invoice_sites_all table. I will try adding a left join with this table. Also, someone told me that Oracle syntax uses the words "LEFT OUTER JOIN" and doesn't work well when using just "LEFT JOIN". I'm going to try both changes and see what gives.  
0
 
janthonynAuthor Commented:
Split is OK. Please close this ticket. I'll keep searching for an answer.
0
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
 
anandagrawal444Commented:

 Here the objective of the user is to find the unpaid and partially paid invoices. For this he is joining so many tables. Actually this objective can be achieved only through a single table AP_Invoices_All. This table is having the columns for invoice amount as well as the amount paid. So the desired result can be directly retrieved from this table.

For retrieving vendor details, PO_vendors can be used.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 6
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now