Avatar of janthonyn
janthonyn

asked on 

PO_VENDOR_SITES difficult to join to existing select

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'
Oracle Database

Avatar of undefined
Last Comment
ram_0218
ASKER CERTIFIED SOLUTION
Avatar of ram_0218
ram_0218
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo