Active suppliers query in oracle apps

Active suppliers query in oracle apps

In this post , I will share the SQL query to fetch the Active Suppliers Information's in Oracle apps. This query will provide the complete Suppliers Information's which is currently active in oracle apps. You can refer this post to get the Suppliers details.

 

 

SQL query to Fetch Active suppliers in oracle apps

 

SELECT APS.SEGMENT1,

HP.PARTY_NUMBER,

APS.VENDOR_NAME,

APS.VENDOR_TYPE_LOOKUP_CODE VENDOR_TYPE,

APS.LAST_UPDATE_DATE HEADER_LAST_UPDATE_DATE,

APSS.LAST_UPDATE_DATE SITES_LAST_UPDATE_DATE,

APS.vat_registration_num,

NVL(APS.TCA_SYNC_VAT_REG_NUM,APSS.VAT_REGISTRATION_NUM) TAX_REFERENCE,

APSS.PARTY_SITE_ID,

APSS.SHIP_TO_LOCATION_ID,

APSS.BILL_TO_LOCATION_ID,

APSS.VENDOR_SITE_ID ,

APSS.VENDOR_SITE_CODE SITE_CODE,

APSS.ADDRESS_LINE1,

APSS.ADDRESS_LINE2,

APSS.ADDRESS_LINE3,

APSS.CITY,

APSS.STATE,

APSS.COUNTY,

APSS.ZIP,

APSS.COUNTRY,

apss.address_style,

APSS.CREATION_DATE,

APSS.INACTIVE_DATE,

APS.END_DATE_ACTIVE,

APSS.ORG_ID,

(SELECT NAME FROM HR_OPERATING_UNITS WHERE ORGANIZATION_ID=APSS.ORG_ID) NAME,

APS.PARTY_ID,

APSS.PAY_SITE_FLAG,

APSS.PURCHASING_SITE_FLAG,

APSS.RFQ_ONLY_SITE_FLAG

FROM APPS.HR_EMPLOYEES HE,

APPS.HR_LOCATIONS_V SHIP_TO,

APPS.HR_LOCATIONS_V BILL_TO,

apps.AP_SUPPLIER_SITES_ALL APSS,

ap.AP_SUPPLIERS APS,

apps.hz_parties hp

WHERE HE.EMPLOYEE_ID(+) = APS.EMPLOYEE_ID

and aps.party_id=hp.party_id

AND SHIP_TO.LOCATION_ID(+) = APSS.SHIP_TO_LOCATION_ID

AND BILL_TO.LOCATION_ID = APSS.BILL_TO_LOCATION_ID

AND NVL(APSS.INACTIVE_DATE,SYSDATE+1) >= SYSDATE

AND APS.VENDOR_ID = APSS.VENDOR_ID

AND NVL(APS.END_DATE_ACTIVE,SYSDATE+1) >= SYSDATE

AND NVL(APS.ENABLED_FLAG,'Y') = 'Y'

and apss.INACTIVE_DATE is null

AND APSS.ORG_ID =:P_ORG_ID

ORDER BY APSS.ORG_ID, APS.SEGMENT1, APSS.VENDOR_SITE_CODE