Apps R12 Supplier with Tax Number Query

   SELECT DISTINCT

         AP.VENDOR_ID,

         AP.SEGMENT1 VENDOR_NUMBER,

         AP.VENDOR_NAME,

         NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation') SUPPLIER_TYPE,

         AP.START_DATE_ACTIVE,

         AP.END_DATE_ACTIVE,

         --         hp.duns_number ISO_NUMBER,

         hp.duns_number_C ISO_NUMBER,

         hp.mission_statement REG_NUMBER_DOF,

         --         hp.*,

         AP.STANDARD_INDUSTRY_CLASS LICENCE_NUMBER,

         APS.VENDOR_SITE_CODE,

         APS.VENDOR_SITE_ID,

         APS.ADDRESS_LINE1,

         AP.CREATION_DATE,

         NVL (

            (SELECT FULL_NAME

               FROM FND_USER A, PER_ALL_PEOPLE_F B

              WHERE     A.EMPLOYEE_ID = B.PERSON_ID

                    AND SYSDATE BETWEEN EFFECTIVE_START_DATE

                                    AND EFFECTIVE_END_DATE

                    AND A.USER_ID = AP.CREATED_BY),

            U.USER_NAME)

            AS "CREATED_BY",

         AP.LAST_UPDATE_DATE,

         NVL (

            (SELECT FULL_NAME

               FROM FND_USER C, PER_ALL_PEOPLE_F D

              WHERE     C.EMPLOYEE_ID = D.PERSON_ID

                    AND SYSDATE BETWEEN EFFECTIVE_START_DATE

                                    AND EFFECTIVE_END_DATE

                    AND C.USER_ID = AP.CREATED_BY),

            U2.USER_NAME)

            AS "LAST_UPDATED_BY",

         (SELECT HCP.EMAIL_ADDRESS

            FROM HZ_PARTY_SITES HPS, HZ_CONTACT_POINTS HCP, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.CONTACT_POINT_TYPE = 'EMAIL'

                 AND ROWNUM = 1)

            EMAIL,

         (SELECT HCP.PHONE_AREA_CODE || ' ' || HCP.PHONE_NUMBER PHONE

            FROM HZ_CONTACT_POINTS HCP, HZ_PARTY_SITES HPS, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.PRIMARY_FLAG = 'Y'

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HCP.STATUS = 'A'

                 AND HCP.CONTACT_POINT_TYPE = 'PHONE'

                 AND HCP.PHONE_LINE_TYPE = 'GEN'

                 AND ROWNUM = 1)

            PHONE,

         (SELECT HCP.PHONE_AREA_CODE || ' ' || HCP.PHONE_NUMBER PHONE

            FROM HZ_CONTACT_POINTS HCP, HZ_PARTY_SITES HPS, AP_SUPPLIERS ASS

           WHERE     1 = 1

                 AND ASS.VENDOR_NAME = AP.VENDOR_NAME

                 AND HPS.PARTY_ID = ASS.PARTY_ID

                 AND HCP.OWNER_TABLE_NAME = 'HZ_PARTY_SITES'

                 AND HCP.OWNER_TABLE_ID = HPS.PARTY_SITE_ID

                 AND HCP.STATUS = 'A'

                 AND HCP.CONTACT_POINT_TYPE = 'PHONE'

                 AND HCP.PHONE_LINE_TYPE = 'FAX'

                 AND ROWNUM = 1)

            FAX,

         ieb.bank_name "BANK NAME",

         ieba.BANK_ACCOUNT_NUM "BANK ACCOUNT NUMBER",

         ieba.BANK_ACCOUNT_NAME "BANK ACCOUNT NAME",

         ieba.IBAN "BANK_IBAN"

           ,AP.PARTY_ID

           ,APS.PARTY_SITE_ID

           ,ap.VAT_REGISTRATION_NUM

    FROM PER_ALL_PEOPLE_F PER,

         AP_SUPPLIERS AP,

         hz_parties hp,

         AP_SUPPLIER_SITES_ALL APS,

         apps.iby_ext_bank_accounts ieba,

         apps.iby_account_owners iao,

         apps.iby_ext_banks_v ieb,

         FND_USER U,

         FND_USER U2

   WHERE     1 = 1

         AND PER.PERSON_ID(+) = AP.EMPLOYEE_ID

         AND ap.party_id = hp.party_id

         AND AP.VENDOR_ID = APS.VENDOR_ID(+)

         AND AP.SEGMENT1 >= NVL ( :p_from_vendor_id, AP.SEGMENT1)

         AND AP.SEGMENT1 <= NVL ( :p_to_vendor_id, AP.SEGMENT1)

         AND U.USER_ID = AP.CREATED_BY

         AND U2.USER_ID = AP.LAST_UPDATED_BY

         AND iao.account_owner_party_id(+) = ap.party_id

         AND ieba.ext_bank_account_id(+) = iao.ext_bank_account_id

         AND iao.PRIMARY_FLAG(+) = 'Y'

         AND ieba.ext_bank_account_id(+) = iao.ext_bank_account_id

         AND ieba.bank_id = ieb.bank_party_id(+)

         AND  AP.END_DATE_ACTIVE IS NULL ---ADDED ON 03-09-2020 BY TAREK

         AND NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation') = NVL (:p_supplier_type , NVL(AP.VENDOR_TYPE_LOOKUP_CODE,'Organisation'))

ORDER BY 1