Query to get Active suppliers-Category and Sites

 SELECT 

TO_CHAR(SUP.CREATION_DATE,'DD-MON-YY HH24:MI:SS','nls_date_language=american') CREATION_DATE

,SUP.segment1 supplier_number  

,HP.party_name supplier_name

,SUP.VENDOR_TYPE_LOOKUP_CODE  Supplier_Type

,pii.ORGANIZATION_TYPE

,pvs.VENDOR_SITE_CODE site

,hp.PARTY_NUMBER Customer_Number

,sup.TAXPAYER_COUNTRY

,pvs.PAYMENT_CURRENCY_CODE

,pii.INCOME_TAX_ID Tax_Payer_ID

,sup_tax.TAX_REGIME_CODE vat_code

,pii.VAT_REGISTRATION_NUM

,pvs.INACTIVE_DATE

,pzg.TAX_REG_COUNTRY_CODE

,sup_tax.tax_registration_number 

,(SELECT zr.registration_number

   FROM ZX_REGISTRATIONS zr,

ZX_PARTY_TAX_PROFILE zptn

  WHERE 1=1

AND zr.party_tax_profile_id = zptn.party_tax_profile_id

AND SYSDATE  BETWEEN NVL(zr.effective_from,SYSDATE) AND NVL(zr.effective_to,SYSDATE+1)

AND zptn.party_id = (SELECT DISTINCT hps.party_site_id 

   FROM HZ_PARTY_SITES hps

  WHERE hps.party_id = hp.party_id

AND hps.party_site_id = PVS.party_site_id

)

and rownum<2) tax_registration_number1

, (

  SELECT zr.registration_number

   FROM ZX_REGISTRATIONS zr,

ZX_PARTY_TAX_PROFILE zptn

  WHERE 1=1

AND zr.party_tax_profile_id = zptn.party_tax_profile_id

AND SYSDATE  BETWEEN NVL(zr.effective_from,SYSDATE) AND NVL(zr.effective_to,SYSDATE+1)

AND zptn.party_id = hp.party_id

and rownum<2

  )tax_registration_number2

, (

   SELECT zpt.rep_registration_number 

   FROM ZX_PARTY_TAX_PROFILE zpt

   WHERE 1=1

AND zpt.party_id = hp.party_id

and rownum<2

  )tax_registration_number3

,TO_CHAR(sup_tax.EFFECTIVE_FROM,'DD-MON-YY HH24:MI:SS','nls_date_language=american') EFFECTIVE_FROM

,hzl.ADDRESS1

,hzl.ADDRESS2

,hzl.ADDRESS3

,hzl.ADDRESS4

,hzl.CITY

,hzl.STATE

,hzl.POSTAL_CODE

,hzl.PROVINCE

,SUP.vendor_id

,( SELECT 

   DECODE(psps.purchasing_cat_flag,'N',pbct.category_name,'Y',ect.category_name) category_name

   FROM por_browse_categories_tl pbct,

        egp_categories_tl ect,

        poz_sup_products_services psps

  WHERE psps.category_id= pbct.category_id(+)

    AND psps.category_id = ect.category_id(+)

    AND pbct.language(+)=userenv('LANG')

    AND ect.language(+)=userenv('LANG')

    AND psps.VENDOR_ID = sup.vendor_id

AND rownum<2

) category_name

FROM 

(SELECT 

 ZR.TAX_REGIME_CODE

,ZR.REGISTRATION_NUMBER tax_registration_number

,ZR.EFFECTIVE_FROM

,TAX.party_id

 FROM zx_party_tax_profile tax

     ,zx_registrations ZR

 WHERE

 1=1

AND tax.party_type_code = 'THIRD_PARTY'

AND ZR.PARTY_TAX_PROFILE_ID = TAX.PARTY_TAX_PROFILE_ID 

) sup_tax

,hz_parties HP

,poz_suppliers SUP

,POZ_SUPPLIER_SITES_ALL_M PVS

,HZ_LOCATIONS hzl

,HZ_PARTY_SITES hps

,POZ_SUPPLIERS_PII pii

,poz_supplier_registrations  pzg

WHERE 1=1 

AND sup_tax.party_id(+) = HP.party_id

AND HP.party_id = SUP.party_id

AND hzl.location_id = pvs.location_id 

AND hps.party_site_id = pvs.party_site_id

AND hps.party_id = hp.party_id

AND pii.vendor_id = sup.vendor_id

AND pzg.vendor_id(+) = sup.vendor_id

--AND SUP.segment1 IN ( '10220044','10002954')