SQL query to find details of active employee vendors from Oracle Account Payables

This sql will give you details of active employee vendors with their active sites with address, their active bank accounts, active employees.
This will exclude ex.employeess.

Refer post - SQL query to find details of active vendors from Oracle Account Payables
to derive active vendors excluding employee vendors


SELECT  DISTINCT
' ' DONOTIMPORT,
PAPF.EMPLOYEE_NUMBER EMPLOYEE_ID,
GCCK.SEGMENT7 DEPT_ID,
TRIM(PAPF.LAST_NAME ||', '|| PAPF.FIRST_NAME) CONTACT_NAME,
PAPF.FIRST_NAME,
PAPF.LAST_NAME,
PAPF.EMAIL_ADDRESS EMAIL1,
PVSA.ADDRESS_LINE1 ADDRESS1,
TRIM((PVSA.ADDRESS_LINE2||' '||PVSA.ADDRESS_LINE3||' '||PVSA.ADDRESS_LINE4)) ADDRESS2,
PVSA.CITY,
PVSA.STATE,
PVSA.ZIP ,
NVL(FTL.TERRITORY_SHORT_NAME,'United States') COUNTRY,
ABB.BANK_NUM ACH_BANK_ROUTING_NUMBER,
APAA.BANK_ACCOUNT_NUM ACH_ACCOUNT_NUMBER,
DECODE(APAA.BANK_ACCOUNT_TYPE,'CHECKING','CHECKING ACCOUNT','SAVINGS','SAVINGS ACCOUNT') ACH_ACCOUNT_TYPE,
PVSA.VENDOR_SITE_CODE REFERENCE
FROM PO.PO_VENDORS PV ,PO.PO_VENDOR_SITES_ALL PVSA
LEFT OUTER JOIN AP.AP_BANK_ACCOUNT_USES_ALL APAUA ON PVSA.VENDOR_ID=APAUA.VENDOR_ID AND (APAUA.END_DATE IS NULL OR APAUA.END_DATE > SYSDATE) AND (APAUA.VENDOR_SITE_ID IS NULL OR APAUA.VENDOR_SITE_ID=PVSA.VENDOR_SITE_ID)
LEFT OUTER JOIN AP.AP_BANK_ACCOUNTS_ALL APAA  ON APAUA.EXTERNAL_BANK_ACCOUNT_ID=APAA.BANK_ACCOUNT_ID --AND (APAA.INACTIVE_DATE IS NULL OR APAA.INACTIVE_DATE > SYSDATE)
LEFT OUTER JOIN AP.AP_BANK_BRANCHES ABB ON APAA.BANK_BRANCH_ID=ABB.BANK_BRANCH_ID --And (Abb.End_Date Is Null Or Abb.End_Date > Sysdate)
LEFT OUTER JOIN APPS.FND_TERRITORIES_TL FTL ON FTL.TERRITORY_CODE=PVSA.COUNTRY,
HR.PER_ALL_PEOPLE_F PAPF,
Apps.Per_Person_Types Ppt,
APPS.PER_ALL_ASSIGNMENTS_F PAAF,
APPS.GL_CODE_COMBINATIONS_KFV GCCK
WHERE PV.VENDOR_ID=PVSA.VENDOR_ID
AND PV.EMPLOYEE_ID=PAPF.PERSON_ID
AND PV.ENABLED_FLAG <>'N'
AND UPPER(PV.VENDOR_TYPE_LOOKUP_CODE) = 'EMPLOYEE'
AND PAPF.PERSON_TYPE_ID=PPT.PERSON_TYPE_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PRIMARY_FLAG = 'Y'
AND UPPER (PPT.USER_PERSON_TYPE) <>'EX-EMPLOYEE'
AND PAPF.CURRENT_EMPLOYEE_FLAG='Y'
AND GCCK.CODE_COMBINATION_ID=PAAF.DEFAULT_CODE_COMB_ID
AND (PV.END_DATE_ACTIVE IS NULL OR PV.END_DATE_ACTIVE>SYSDATE)
AND (PVSA.INACTIVE_DATE IS NULL OR PVSA.INACTIVE_DATE > SYSDATE)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
ORDER BY PAPF.EMPLOYEE_NUMBER