Supplier_Sites

SELECT   DISTINCT                    --replace(hr.name,'BILT','APIL') OU_NAME,
         b.Org_id ORG_ID,
         UPPER(hr.name) OU_NAME,
         UPPER (A.VENDOR_NAME) S_VENDOR_NAME,
         A.VENDOR_ID S_OLD_REFERENCE,
         UPPER (a.VENDOR_TYPE_LOOKUP_CODE) VENDOR_TYPE_LOOKUP_CODE,
         A.SEGMENT1 VENDOR_NUMBER,
         UPPER (B.VENDOR_SITE_CODE) S_SITE_CODE,
         UPPER (B.COUNTRY) S_COUNTRY,
         UPPER (B.ADDRESS_LINE1) S_ADDRESS_LINE1,
         UPPER (B.ADDRESS_LINE2) S_ADDRESS_LINE2,
         UPPER (B.ADDRESS_LINE3) S_ADDRESS_LINE3,
         UPPER (B.ADDRESS_LINE4) S_ADDRESS_LINE4,
         UPPER (B.CITY) S_CITY,
         UPPER (B.COUNTY) S_COUNTY,
         UPPER (B.STATE) S_STATE,
         B.ZIP S_POSTAL_CODE,
         UPPER (B.PURCHASING_SITE_FLAG) S_PURCHASING_SITE,
         UPPER (B.PAY_SITE_FLAG) S_PAYMENT_SITE,
         B.RFQ_ONLY_SITE_FLAG S_RFQ_ONLY_SITE,
         B.AREA_CODE S_AREA_CODE,
         B.PHONE S_PHONE,
         B.FAX_AREA_CODE S_FAX_AREA_CODE,
         B.FAX S_FAX,
         UPPER (B.EMAIL_ADDRESS) S_EMAIL_ADDRESS,
         UPPER (B.TERMS_DATE_BASIS) S_TERMS_DATE_BASIS,
         D1.CONCATENATED_SEGMENTS S_ACCTS_PAY_CODE_COMBINATION,
         D2.CONCATENATED_SEGMENTS S_PREPAY_CODE_COMBINATION,
         B.PAY_GROUP_LOOKUP_CODE S_PAY_GROUP_LOOKUP_CODE,
         UPPER (C.NAME) S_PAYMENT_TERMS,
         UPPER (B.PAY_DATE_BASIS_LOOKUP_CODE) S_PAY_DATE_BASIS_LOOKUP_CODE,
         UPPER (B.INVOICE_CURRENCY_CODE) S_INVOICE_CURRENCY_CODE,
         UPPER (B.PAYMENT_CURRENCY_CODE) S_PAYMENT_CURRENCY_CODE,
         UPPER (B.ATTRIBUTE_CATEGORY) S_ATTRIBUTE_CATEGORY,
         UPPER (B.ATTRIBUTE1) S_ATTRIBUTE1,
         UPPER (B.ATTRIBUTE2) S_ATTRIBUTE2,
         UPPER (B.ATTRIBUTE3) S_ATTRIBUTE3,
         UPPER (B.ATTRIBUTE4) S_ATTRIBUTE4,
         B.VENDOR_SITE_ID Old_ref_site_id,
         UPPER (B.ATTRIBUTE6) S_ATTRIBUTE6,
         UPPER (B.ATTRIBUTE7) S_ATTRIBUTE7,
         UPPER (B.ATTRIBUTE8) S_ATTRIBUTE8,
         UPPER (B.ATTRIBUTE9) S_ATTRIBUTE9,
         UPPER (B.ATTRIBUTE10) S_ATTRIBUTE10,
         UPPER (B.ATTRIBUTE11) S_ATTRIBUTE11,
         UPPER (B.ATTRIBUTE12) S_ATTRIBUTE12,
         UPPER (B.ATTRIBUTE13) S_ATTRIBUTE13,
         UPPER (B.ATTRIBUTE14) S_ATTRIBUTE14,
         UPPER (B.PAY_ON_RECEIPT_SUMMARY_CODE) S_PAY_ON_RECEIPT_SUMMARY_CODE,
         UPPER (B.PAY_ON_CODE) S_PAY_ON_CODE,
         DECODE (B.MATCH_OPTION, 'R', 'Receipt', 'P', 'Purchase Order', NULL)
            S_MATCH_OPTION,
         B.CREATE_DEBIT_MEMO_FLAG S_CREATE_DEBIT_MEMO_FLAG,
         UPPER (B.PAYMENT_METHOD_LOOKUP_CODE) S_PAYMENT_METHOD_LOOKUP_CODE
  FROM   apps.PO_VENDORS A,
         apps.PO_VENDOR_SITES_ALL B,
         apps.AP_TERMS C,
         apps.GL_CODE_COMBINATIONS_KFV D1,
         apps.GL_CODE_COMBINATIONS_KFV D2,
         apps.hr_operating_units hr
 WHERE       A.VENDOR_ID = B.VENDOR_ID
         AND A.TERMS_ID = C.TERM_ID(+)
         AND B.ACCTS_PAY_CODE_COMBINATION_ID = D1.CODE_COMBINATION_ID(+)
         AND B.PREPAY_CODE_COMBINATION_ID = D2.CODE_COMBINATION_ID(+)
         AND a.END_DATE_ACTIVE IS NULL
         AND b.INACTIVE_DATE IS NULL
         AND b.org_id = hr.organization_id
         --AND hr.SET_OF_BOOKS_ID=5
         and a.vendor_name='AIRBREAK SWITCHGEARS'
        
         AND a.vendor_id IN (15685, 18484, 51547, 64242, 66436, 69531)
        
         --and hr.set_of_books_id=5
         --and a.VENDOR_TYPE_LOOKUP_CODE NOT IN ('Ex-Employee', 'EX-EMPLOYEE','Employee','EMPLOYEE','CONSULTANT')    --'Employee','EMPLOYEE','EMPLOYEERM',
         --and a.VENDOR_TYPE_LOOKUP_CODE IN ('Ex-Employee', 'EX-EMPLOYEE')
         --AND b.org_id IN (1280, 1202, 1203, 1204, 1205)                 -- = :
                
        
        
         --and upper(a.vendor_name) like '%PRASA%'
        
                  AND a.vendor_id IN
                  (SELECT   vendor_id
                     FROM   po_vendors
                    WHERE   vendor_id IN
                                  (SELECT   VENDOR_NO FROM xx_vendor_mst_200))
           order by to_number(a.segment1)          
          
           AND a.vendor_id IN
                    (SELECT   vendor_id
                       FROM   po_vendors
                      WHERE   VENDOR_NAME IN
                                    (SELECT   a1.VENDOR_NAME
                                       FROM   xx_vendor_dtls_90 a1,
                                              xx_vendor_dtls_90 a2
                                      WHERE   a1.vendor_name LIKE
                                                    '%'
                                                 || ''
                                                 || a2.vendor_name
                                                 || ''
                                                 || '%'))
ORDER BY   TO_NUMBER (a.segment1)
--Select distinct VENDOR_TYPE_LOOKUP_COD
E from po_vendors