Thursday, 4 January 2018

Supplier Information including GST

The below query fetches all the supplier information including GST Info


select (select name from hr_operating_units where organization_id=jpr.org_id) Operating_unit,
        APS.VENDOR_NAME supplier_name,
        APSA. VENDOR_SITE_CODE  Supplier_Site_name,
        APSA.STATE,
        (SELECT GEOGRAPHY_NAME FROM hz_geographies WHERE geography_type = 'COUNTRY' AND GEOGRAPHY_CODE=APSA.COUNTRY) COUNTRY,
       (select regime_name from jai_regimes where REGIME_ID=jprl.REGIME_ID) Regime,
       jprl.REGISTRATION_TYPE_CODE PRIMARY_REGISTRATION_NAME,
       jprl.REGISTRATION_NUMBER PRIMARY_REGISTRATION_NUMBER,
       jprl.SEC_REGISTRATION_TYPE_CODE SECONDARY_REGISTRATION_NAME,
       jprl.SECONDARY_REGISTRATION_NUMBER SECONDARY_REGISTRATION_NUMBER,
       jprl.EFFECTIVE_FROM start_date,
       jpa.REPORTING_TYPE_NAME,
       (select REPORTING_CODE from jai_reporting_codes where REPORTING_CODE_ID=jpa.REPORTING_CODE_ID) Reporting_Code,
       jpa.EFFECTIVE_FROM Reporting_Start_Date   
from jai_party_regs jpr,
     jai_party_reg_lines jprl,
     jai_reporting_associations jpa,
     ap_suppliers aps,
     ap_supplier_sites_all apsa
where JPR.PARTY_REG_ID=JPRL.PARTY_REG_ID
  and jpa.entity_id=JPR.PARTY_REG_ID
  and jpr.PARTY_ID=APS.VENDOR_ID
  and jpr.PARTY_SITE_ID=APSA.VENDOR_SITE_ID
  AND APS.VENDOR_ID=APSA.VENDOR_ID
  and SUPPLIER_FLAG='Y'
  and PARTY_TYPE_CODE='THIRD_PARTY_SITE'
  and ENTITY_CODE='THIRD_PARTY'
  and ENTITY_SOURCE_TABLE='JAI_PARTY_REGS'
  and SITE_FLAG='Y';
 
 

No comments:

Post a Comment