1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Join Condition Missing...

Discussion in 'Oracle Apps Technical' started by jagadekara, May 12, 2014.

  1. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi All,

    I have below query which is giving duplicate records,

    So please check is there any join condition missed out?

    SELECT pv.vendor_name,
    pv.segment1 vendor_number,
    sites.vendor_site_code vendor_site_code,
    pv.pay_group_lookup_code vendor_paygroup,
    sites.pay_group_lookup_code SITE_PAYGROUP,
    pv.vat_code vendor_vat_code,
    sites.vat_code site_vat_code,
    pv.vat_registration_num VENDOR_REG_NUM,
    sites.vat_registration_num SITE_REG_NUM,
    vendor_terms.name VENDOR_TERMS,
    site_terms.name SITE_TERMS,
    pv.payment_method_lookup_code SUPPLIER_PAY_METHOD,
    sites.payment_method_lookup_code SITE_PAY_METHOD,
    -- accts.ext_bank_account_id,
    accts_v.bank_account_name vendor_bank_account_name,
    accts_v.masked_iban AS vendor_iban,
    accts_v.currency_code vendor_currency_code,
    uses_v.order_of_preference vendor_order_of_preference,
    uses_v.start_date vendor_start_date,
    uses_v.end_date vendor_end_date,
    fc.name AS currency_name,
    bank_v.party_name AS vendor_bank_name,
    accts_v.masked_bank_account_num AS vendor_bank_account_number,
    branch_v.bank_branch_name vendor_bank_branch_name,
    branch_v.branch_number AS vendor_branch_name,
    bankProfile_v.bank_or_branch_number AS vendor_bank_number,
    branch_v.eft_swift_code AS vendor_eft_swift_code,
    accts_v.bank_account_type as vendor_bank_account_type
    FROM apps.IBY_PMT_INSTR_USES_ALL uses_v,
    apps.IBY_EXTERNAL_PAYEES_ALL payee,
    apps.IBY_EXT_BANK_ACCOUNTS accts_v,
    apps.FND_CURRENCIES_VL fc,
    apps.HZ_PARTIES bank_v,
    apps.HZ_ORGANIZATION_PROFILES bankProfile_v,
    apps.CE_BANK_BRANCHES_V branch_v,
    apps.ap_suppliers pv,
    apps.ap_supplier_sites_all sites,
    apps.ap_terms vendor_terms,
    apps.ap_terms site_terms
    WHERE 1=1
    --AND pv.vendor_name = 'BARCLAYS BANK PLC'
    and pv.vendor_id = sites.vendor_id
    and pv.terms_id = vendor_terms.term_id
    and sites.terms_id = site_terms.term_id(+)
    and uses_v.instrument_type = 'BANKACCOUNT'
    AND payee.ext_payee_id = uses_v.ext_pmt_party_id
    AND payee.payee_party_id = pv.party_id
    AND payee.payment_function = 'PAYABLES_DISB'
    AND payee.party_site_id = sites.party_site_id
    AND payee.org_id = sites.org_id
    AND payee.supplier_site_id = sites.vendor_site_id
    AND uses_v.instrument_id = accts_v.ext_bank_account_id
    AND fc.currency_code(+) = accts_V.currency_code
    AND SYSDATE BETWEEN NVL (accts_v.start_date, SYSDATE)
    AND NVL (accts_v.end_date, SYSDATE)
    AND SYSDATE BETWEEN NVL (uses_v.start_date, SYSDATE)
    AND NVL (uses_v.end_date, SYSDATE)
    AND accts_v.bank_id = bank_v.party_id(+)
    AND accts_v.bank_id = bankProfile_V.party_id(+)
    AND accts_v.branch_id = branch_v.branch_party_id(+)
    AND SYSDATE BETWEEN TRUNC(bankProfile_v.effective_start_date(+)) AND NVL(TRUNC(bankProfile_v.effective_end_date(+)),SYSDATE + 1)
    ORDER BY ORDER_OF_PREFERENCE ASC
     
  2. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    I suppose your query will be used to get the Supplier details from EBS and is being run on a specific client instance, I mean not on Vision instance. At first sight, I don't see any missing joins but we all know the complexity and intricacies of Oracle Apps. tables and data model which may lead to query working fine in 1 instance and not giving the expected results in another.

    I tried your query on my local Vision instance and out of over 600 suppliers, I could not see any duplicate row being output.

    Can you please tell us more on the details and particularities of the suppliers which are coming out in duplicate or is it systematically for all your suppliers ? Is it for suppliers having multiple sites ? Bank accounts at Supplier or Site level ? Multiple currency accounts ...etc ?
    An extract of duplicate lines (+ correct lines) of your query output may help (if it's not confidential ... or you may hide the confidential parts)...

    Thanks & Regards,
    Rajen.
     
  3. rajenb

    rajenb Forum Expert

    Messages:
    361
    Likes Received:
    114
    Trophy Points:
    655
    Location:
    Mauritius
    Hi Jagadekara,

    After checking your mail and outlined requirements, I created a couple of suppliers having the profiles indicated, but couldn't reproduce all the issues raised:

    1) Supplier + Supplier site + Banking details at site level: report output looks OK
    2) Supplier + Supplier site + Banking details at supplier level: report does not output these bank accounts.
    3) Supplier + Supplier site + Banking details at both supplier and supplier site level: report output lists only bank accounts at site level

    The following SQL can be used to retrieve the bank details at supplier level (I adapted the same query and removed ap_supplier_sites_all table and added IBY_ACCOUNT_OWNERS table with the necessary joins) :

    Code (SQL):
    SELECT pv.vendor_name,
      pv.segment1 vendor_number,
      'NO Supplier site',
      pv.pay_group_lookup_code vendor_paygroup,
      'NO SITE_PAYGROUP',
      pv.vat_code vendor_vat_code,
      'NO site_vat_code',
      pv.vat_registration_num VENDOR_REG_NUM,
      'NO SITE_VAT_REG_NUM',
      vendor_terms.NAME VENDOR_TERMS,
      'NO SITE_TERMS',
      pv.payment_method_lookup_code SUPPLIER_PAY_METHOD,
      'NO SITE_PAY_METHOD',
      accts_v.bank_account_name vendor_bank_account_name,
      accts_v.masked_iban AS vendor_iban,
      accts_v.currency_code vendor_currency_code,
      uses_v.order_of_preference vendor_order_of_preference,
      uses_v.start_date vendor_start_date,
      uses_v.end_date vendor_end_date,
      fc.name                         AS currency_name,
      bank_v.party_name               AS vendor_bank_name,
      accts_v.masked_bank_account_num AS vendor_bank_account_number,
      branch_v.bank_branch_name vendor_bank_branch_name,
      branch_v.branch_number              AS vendor_branch_name,
      bankProfile_v.bank_or_branch_number AS vendor_bank_number,
      branch_v.eft_swift_code             AS vendor_eft_swift_code,
      accts_v.bank_account_type           AS vendor_bank_account_type
    FROM apps.IBY_PMT_INSTR_USES_ALL uses_v,
      apps.IBY_EXTERNAL_PAYEES_ALL payee,
      apps.IBY_EXT_BANK_ACCOUNTS accts_v,
      apps.iby_account_owners iao,
      apps.FND_CURRENCIES_VL fc,
      apps.HZ_PARTIES bank_v,
      apps.HZ_ORGANIZATION_PROFILES bankProfile_v,
      apps.CE_BANK_BRANCHES_V branch_v,
      apps.ap_suppliers pv,
      apps.ap_terms vendor_terms
    WHERE 1            =1
    --AND pv.vendor_name = 'Advantage Corp'
    AND pv.terms_id            = vendor_terms.term_id
    AND uses_v.instrument_type = 'BANKACCOUNT'
    AND pv.party_id = iao.account_owner_party_id
    AND accts_v.ext_bank_account_id = iao.ext_bank_account_id
    AND payee.ext_payee_id     = uses_v.ext_pmt_party_id
    AND payee.payee_party_id   = pv.party_id
    AND payee.payment_function = 'PAYABLES_DISB'
    AND payee.org_type IS NULL
    AND payee.supplier_site_id IS NULL
    AND uses_v.instrument_id   = accts_v.ext_bank_account_id
    AND fc.currency_code(+)    = accts_V.currency_code
    AND SYSDATE BETWEEN NVL (accts_v.start_date, SYSDATE) AND NVL (accts_v.end_date, SYSDATE)
    AND SYSDATE BETWEEN NVL (uses_v.start_date, SYSDATE) AND NVL (uses_v.end_date, SYSDATE)
    AND accts_v.bank_id   = bank_v.party_id(+)
    AND accts_v.bank_id   = bankProfile_V.party_id(+)
    AND accts_v.branch_id = branch_v.branch_party_id(+)
    AND SYSDATE BETWEEN TRUNC(bankProfile_v.effective_start_date(+)) AND NVL(TRUNC(bankProfile_v.effective_end_date(+)),SYSDATE + 1)
    ORDER BY pv.vendor_name,
      ORDER_OF_PREFERENCE ASC;
    I kept the same number of columns and just replaced it by plain text so that you may eventually UNION this SQL with yours to get the complete picture.

    Hope this helps for you to build up on it.

    Regards,
    Rajen
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Thanks Rajen,

    Finally the query is:

    Code (SQL):
    SELECT pv.vendor_name,
           pv.segment1 vendor_number,
           sites.vendor_site_code vendor_site_code,
           pv.pay_group_lookup_code vendor_paygroup,
           sites.pay_group_lookup_code SITE_PAYGROUP,
           pv.vat_code vendor_vat_code,
           sites.vat_code site_vat_code,
           pv.vat_registration_num VENDOR_REG_NUM,
           sites.vat_registration_num SITE_REG_NUM,
           vendor_terms.name VENDOR_TERMS,
           site_terms.name SITE_TERMS,
           pv.payment_method_lookup_code SUPPLIER_PAY_METHOD,
           sites.payment_method_lookup_code SITE_PAY_METHOD,
     --      accts.ext_bank_account_id,
                     accts_v.bank_account_name vendor_bank_account_name,
                     accts_v.masked_iban AS vendor_iban,
                     accts_v.currency_code vendor_currency_code,
                     uses_v.order_of_preference vendor_order_of_preference,
                     uses_v.start_date vendor_start_date,
                     uses_v.end_date vendor_end_date,
                     fc.name AS currency_name,
                     bank_v.party_name AS vendor_bank_name,
                     accts_v.masked_bank_account_num AS vendor_bank_account_number,
                     branch_v.bank_branch_name vendor_bank_branch_name,
                     branch_v.branch_number AS vendor_branch_name,
                     bankProfile_v.bank_or_branch_number AS vendor_bank_number,
                     branch_v.eft_swift_code AS vendor_eft_swift_code,
                     accts_v.bank_account_type AS vendor_bank_account_type
                FROM apps.IBY_PMT_INSTR_USES_ALL uses_v,
                     apps.IBY_EXTERNAL_PAYEES_ALL payee,
                     apps.IBY_EXT_BANK_ACCOUNTS accts_v,
                     apps.FND_CURRENCIES_VL fc,
                     apps.HZ_PARTIES bank_v,
                     apps.HZ_ORGANIZATION_PROFILES bankProfile_v,
                     apps.CE_BANK_BRANCHES_V branch_v,
                     apps.ap_suppliers pv,
                     apps.ap_supplier_sites_all sites,
                     apps.ap_terms vendor_terms,
                     apps.ap_terms site_terms
               WHERE 1=1
                   --AND    pv.vendor_name = 'BARCLAYS BANK PLC'
                    AND pv.vendor_id = sites.vendor_id
                     AND pv.terms_id = vendor_terms.term_id
                     AND sites.terms_id = site_terms.term_id(+)
                     AND uses_v.instrument_type = 'BANKACCOUNT'
                     AND payee.ext_payee_id = uses_v.ext_pmt_party_id
                     AND payee.payee_party_id = pv.party_id
                     AND payee.payment_function = 'PAYABLES_DISB'
                     AND payee.party_site_id = sites.party_site_id
                     AND payee.org_id = sites.org_id
                     AND payee.supplier_site_id = sites.vendor_site_id
                     AND uses_v.instrument_id = accts_v.ext_bank_account_id
                     AND fc.currency_code(+) = accts_V.currency_code
                     AND SYSDATE BETWEEN NVL (accts_v.start_date, SYSDATE)
                                     AND NVL (accts_v.end_date, SYSDATE)
                     AND SYSDATE BETWEEN NVL (uses_v.start_date, SYSDATE)
                                     AND NVL (uses_v.end_date, SYSDATE)                                
                     AND accts_v.bank_id = bank_v.party_id(+)
                     AND accts_v.bank_id = bankProfile_V.party_id(+)
                     AND accts_v.branch_id = branch_v.branch_party_id(+)
                    AND SYSDATE BETWEEN TRUNC(bankProfile_v.effective_start_date(+))  AND NVL(TRUNC(bankProfile_v.effective_end_date(+)),SYSDATE + 1)

    UNION

    SELECT pv.vendor_name,
      pv.segment1 vendor_number,
      'NO Supplier site',
      pv.pay_group_lookup_code vendor_paygroup,
      'NO SITE_PAYGROUP',
      pv.vat_code vendor_vat_code,
      'NO site_vat_code',
      pv.vat_registration_num VENDOR_REG_NUM,
      'NO SITE_VAT_REG_NUM',
      vendor_terms.NAME VENDOR_TERMS,
      'NO SITE_TERMS',
      pv.payment_method_lookup_code SUPPLIER_PAY_METHOD,
      'NO SITE_PAY_METHOD',
      accts_v.bank_account_name vendor_bank_account_name,
      accts_v.masked_iban AS vendor_iban,
      accts_v.currency_code vendor_currency_code,
      uses_v.order_of_preference vendor_order_of_preference,
      uses_v.start_date vendor_start_date,
      uses_v.end_date vendor_end_date,
      fc.name                         AS currency_name,
      bank_v.party_name               AS vendor_bank_name,
      accts_v.masked_bank_account_num AS vendor_bank_account_number,
      branch_v.bank_branch_name vendor_bank_branch_name,
      branch_v.branch_number              AS vendor_branch_name,
      bankProfile_v.bank_or_branch_number AS vendor_bank_number,
      branch_v.eft_swift_code             AS vendor_eft_swift_code,
      accts_v.bank_account_type           AS vendor_bank_account_type
    FROM apps.IBY_PMT_INSTR_USES_ALL uses_v,
      apps.IBY_EXTERNAL_PAYEES_ALL payee,
      apps.IBY_EXT_BANK_ACCOUNTS accts_v,
      apps.iby_account_owners iao,
      apps.FND_CURRENCIES_VL fc,
      apps.HZ_PARTIES bank_v,
      apps.HZ_ORGANIZATION_PROFILES bankProfile_v,
      apps.CE_BANK_BRANCHES_V branch_v,
      apps.ap_suppliers pv,
      apps.ap_terms vendor_terms
    WHERE 1 =1
    --AND pv.vendor_name = 'Advantage Corp'
    AND pv.terms_id            = vendor_terms.term_id
    AND uses_v.instrument_type = 'BANKACCOUNT'
    AND pv.party_id = iao.account_owner_party_id
    AND accts_v.ext_bank_account_id = iao.ext_bank_account_id
    AND payee.ext_payee_id     = uses_v.ext_pmt_party_id
    AND payee.payee_party_id   = pv.party_id
    AND payee.payment_function = 'PAYABLES_DISB'
    AND payee.org_type IS NULL
    AND payee.supplier_site_id IS NULL
    AND uses_v.instrument_id   = accts_v.ext_bank_account_id
    AND fc.currency_code(+) = accts_V.currency_code
    AND SYSDATE BETWEEN NVL (accts_v.start_date, SYSDATE) AND NVL (accts_v.end_date, SYSDATE)
    AND SYSDATE BETWEEN NVL (uses_v.start_date, SYSDATE) AND NVL (uses_v.end_date, SYSDATE)
    AND accts_v.bank_id   = bank_v.party_id(+)
    AND accts_v.bank_id   = bankProfile_V.party_id(+)
    AND accts_v.branch_id = branch_v.branch_party_id(+)
    AND SYSDATE BETWEEN TRUNC(bankProfile_v.effective_start_date(+)) AND NVL(TRUNC(bankProfile_v.effective_end_date(+)),SYSDATE + 1)
    ORDER BY 1 ASC;