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'
ASKER CERTIFIED SOLUTION
ram_0218

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Log in to continue reading
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform for $9.99/mo
View membership options
Unlock 1 Answer and 1 Comment.
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
The Value of Experts Exchange in My Daily IT Life

Experts Exchange (EE) has become my company's go-to resource to get answers. I've used EE to make decisions, solve problems and even save customers. OutagesIO has been a challenging project and... Keep reading >>

Mike

Owner of Outages.IO
Phoenix, Arizona, United States
Member Since 2016
Join a full scale community that combines the best parts of other tools into one platform.
Unlock 1 Answer and 1 Comment.
View membership options
“All of life is about relationships, and EE has made a virtual community a real community. It lifts everyone's boat.”
William Peck

Member since 2004