troubleshooting Question

PO_VENDOR_SITES difficult to join to existing select

Avatar of janthonyn
janthonyn asked on
Oracle Database
1 Comment1 Solution1988 ViewsLast Modified:
Having a problem joining the po_vendor_sites table to a sql that has been working. When I run the select without the join that I have, it outputs many rows of records. When I add the join, I get 0 records output. Here is the select that works:
select pv.vendor_name
    ,pv.segment1 vendor#
    ,ai.invoice_num invoice#
    ,ai.description
    ,ai.invoice_date inv_date
    ,aid.accounting_date acctng_date
    ,to_char(aid.creation_date,'DD-MON-YYYY') entry_date
    ,fu.description entered_by
    ,aid.distribution_line_number ln#
    ,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 account_code
    ,ffvl.description entity
    ,aid.amount
    ,hou.name entering_unit
from fnd_user fu
    ,ap_invoices_all ai
    ,ap_invoice_distributions_all aid
    ,gl_code_combinations gcc
    ,hr_operating_units hou
    ,fnd_flex_values_vl ffvl
    ,po_vendors pv
where ai.invoice_id = aid.invoice_id
and aid.dist_code_combination_id = gcc.code_combination_id
and aid.org_id = hou.organization_id
and ai.vendor_id = pv.vendor_id
and gcc.segment3 = '1305'
and ffvl.flex_value = gcc.segment1
and ai.cancelled_date is null
 --Note: some flexfield values from segment2 overlap values from segment1.
 --This results in the output of duplicate rows with different descriptions in the ENTITY column
 --Conditional phrase to prevent this follows:
and ffvl.description not in ('Clinics','Set of 100 records','Clinic # 2','Clinic # 3','Clinic # 4','Clinic # 5','Clinic # 6',
          'LTAC',      'Psych','SNF','Outpatient')
and aid.created_by = fu.user_id
and fu.description = 'Use this id for data coming from feeder systems'
and aid.creation_date between '01-FEB-07' and '28-FEB-07'
--------------------------------
But this statement does not work:
select pv.vendor_name
    ,pv.segment1 vendor#
    ,pvs .vendor_site_code site
    ,ai.invoice_num invoice#
    ,ai.description
    ,ai.invoice_date inv_date
    ,aid.accounting_date acctng_date
    ,to_char(aid.creation_date,'DD-MON-YYYY') entry_date
    ,fu.description entered_by
    ,aid.distribution_line_number ln#
    ,gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 account_code
    ,ffvl.description entity
    ,aid.amount
    ,hou.name entering_unit
from fnd_user fu
    ,ap_invoices_all ai
    ,ap_invoice_distributions_all aid
    ,gl_code_combinations gcc
    ,hr_operating_units hou
    ,fnd_flex_values_vl ffvl
    ,po_vendors pv
    ,po_vendor_sites pvs
where ai.invoice_id = aid.invoice_id
and aid.dist_code_combination_id = gcc.code_combination_id
and aid.org_id = hou.organization_id
and ai.vendor_id = pv.vendor_id
and pv.vendor_id = pvs.vendor_id
and gcc.segment3 = '1305'
and ffvl.flex_value = gcc.segment1
and ai.cancelled_date is null
 --Note: some flexfield values from segment2 overlap values from segment1.
 --This results in the output of duplicate rows with different descriptions in the ENTITY column
 --Conditional phrase to prevent this follows:
and ffvl.description not in ('Clinics','Set of 100 records','Clinic # 2','Clinic # 3','Clinic # 4','Clinic # 5','Clinic # 6',
          'LTAC',      'Psych','SNF','Outpatient')
and aid.created_by = fu.user_id
and fu.description = 'Use this id for data coming from feeder systems'
and aid.creation_date between '01-FEB-07' and '28-FEB-07'
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 1 Comment.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 1 Comment.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros