AP EBTAX US State Registration Report

 AP EBTAX US State Registration Report

SELECT ps.segment1                                               Vendor_Number,

       hp.party_name      Vendor_Name,

       hp.party_number  a                                         party_number,

       Zr.tax_jurisdiction_code

       Tax_Jurisdiction,

       Zr.registration_status_code

       Registration_Status,

       Zr.self_assess_flag

       Self_Assess_Flag,

       ps.auto_tax_calc_flag

       Auto_Tax_Calc_Flag,

       To_char(ZR.effective_from, 'DD-MON-YYYY HH24:MI:SS AM')   EFFECTIVE_FROM,

       To_char(ZR.effective_to, 'DD-MON-YYYY HH24:MI:SS AM')     EFFECTIVE_TO,

       To_char(ZR.last_update_date, 'DD-MON-YYYY HH24:MI:SS AM') LAST_UPDT_DATE,

       Usr.user_id

       Last_Updt_User_Id,

       Usr.username                                              Last_Updt_Name,

       hps.party_site_number

       party_site_number,

       (SELECT DISTINCT Initcap(meaning)

        FROM   fnd_lookups

        WHERE  lookup_type = 'POZ_VENDOR_TYPE'

               AND Lower(lookup_code) = Lower(ps.vendor_type_lookup_code)

               AND ROWNUM = 1)                                   Supplier_Type,

       psav.party_site_name                                      Address_name,

       pssv.vendor_site_code                                     Supplier_Site,

       zr.registration_number                                    Supplier_trn,

       ps.creation_source,

       zptp.party_type_code

FROM   poz_suppliers ps,

       hz_parties hp,

       poz_supplier_sites_v pssv,

       hz_party_sites hps,

       poz_supplier_address_v psav,

       zx_party_tax_profile zptp,

       zx_registrations zr,

       per_users Usr

WHERE  ps.party_id = hp.party_id

       AND ps.vendor_id = pssv.vendor_id

       AND pssv.party_site_id = psav.party_site_id

       AND pssv.party_site_id = hps.party_site_id

       AND zptp.party_id(+) = pssv.party_site_id

       AND zptp.party_tax_profile_id = zr.party_tax_profile_id

       AND Usr.username = Zr.last_updated_by

       AND zptp.party_type_code = 'THIRD_PARTY_SITE'

       --AND (Al1.Book_Name IN (:P_Book_Type_Code) OR COALESCE(:P_Book_Type_Code,null) IS Null)

       AND Nvl(Zr.tax_jurisdiction_code, 'X') =

           Nvl(:p_Tax_Jurisdiction_Code, Nvl(Zr.tax_jurisdiction_code, 'X'))

       AND ( ps.segment1 IN ( :p_vendor_number )

              OR Coalesce(:p_vendor_number, NULL) IS NULL )

       -- AND Pv.Segment1 = nvl(:p_vendor_number,Pv.Segment1)

       AND ( hp.party_name IN ( :p_vendor_name )

              OR Coalesce(:p_vendor_name, NULL) IS NULL )

-- AND hp.party_Name = nvl(:p_vendor_name,hp.party_Name)

ORDER  BY hp.party_name