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'