Use the Below Query to get the Vendor Details.
SELECT PO.SEGMENT1 VENDOR_NUMBER, PO.VENDOR_NAME, PO.VENDOR_NAME_ALT, PO.VENDOR_TYPE_LOOKUP_CODE, POS.VENDOR_SITE_CODE ,VENDOR_SITE_CODE_ALT ,POS.ADDRESS_LINE1 ,POS.ADDRESS_LINE2 ,POS.ADDRESS_LINE3 ,POS.ADDRESS_LINE4 ,POS.CITY ,POS.COUNTRY ,POS.ZIP ,POS.COUNTY ,POS.STATE ,POS.INVOICE_CURRENCY_CODE ,POS.PAYMENT_CURRENCY_CODE ,POS.PAYMENT_METHOD_LOOKUP_CODE ,POS.PAY_GROUP_LOOKUP_CODE ,HR.NAME ,INACTIVE_DATE ,GLCC.CONCATENATED_SEGMENTS LIABILITY_ACCOUNT ,GLCCP.CONCATENATED_SEGMENTS PREPAY_ACCOUNT ,POS.EMAIL_ADDRESS FROM AP_SUPPLIERS PO, AP_SUPPLIER_SITES_ALL POS, GL_CODE_COMBINATIONS_KFV GLCC, GL_CODE_COMBINATIONS_KFV GLCCP, HR_OPERATING_UNITS HR WHERE PO.VENDOR_ID=POS.VENDOR_ID AND GLCC.CODE_COMBINATION_ID=POS.ACCTS_PAY_CODE_COMBINATION_ID AND GLCCP.CODE_COMBINATION_ID=POS.PREPAY_CODE_COMBINATION_ID AND HR.ORGANIZATION_ID=POS.ORG_ID --AND PO.SEGMENT1 BETWEEN NVL(:P_SUPPLIER_NUMBER_FROM,PO.SEGMENT1) AND NVL(:P_SUPPLIER_NUMBER_TO,PO.SEGMENT1) AND PO.VENDOR_NAME=NVL(&P_VENDOR_NAME,PO.VENDOR_NAME) ORDER BY 1;
Query for Supplier Bank Details
SELECT DISTINCT pv.vendor_name vendor_name, pv.segment1 vendor_number,
pvs.vendor_site_code vendor_site_code,
aba.bank_account_name bank_account_name,
aba.bank_account_num bank_account_num,
aba.currency_code currency_code,
abau.primary_flag primary_flag, abb.bank_name bank_name,
abb.bank_number bank_number,
abb.bank_branch_name bank_branch_name, abb.bank_num bank_num
FROM ap_bank_account_uses_all abau,
ap_bank_accounts_all aba,
ap_bank_branches abb,
po_vendors pv,
po_vendor_sites_all pvs
WHERE abau.external_bank_account_id = aba.bank_account_id
AND aba.bank_branch_id = abb.bank_branch_id
AND abau.vendor_id = pv.vendor_id
AND abau.vendor_id = pvs.vendor_id(+)
AND abau.vendor_site_id = pvs.vendor_site_id(+)
Approved Supplier List Query in Oracle Apps
SELECT hou.NAME operating_unit, asl.vendor_business_type,
pov.segment1 vendor_code, pov.vendor_name, sites.vendor_site_code,
msi.segment1 item_code, msi.description item_desc,
using_organization_id, owning_organization_id,plc.displayed_field , past.status Supplier_Status
FROM po_approved_supplier_list asl,
po_vendors pov,
po_vendor_sites_all sites,
mtl_system_items_b msi,
hr_operating_units hou,
po_lookup_codes plc,
po_asl_statuses past
WHERE asl.vendor_id = pov.vendor_id
AND asl.vendor_site_id = sites.vendor_site_id
AND msi.inventory_item_id = asl.item_id
AND msi.organization_id = asl.using_organization_id
AND sites.org_id = hou.organization_id
and asl.vendor_business_type = plc.lookup_code
AND asl.asl_status_id = past.status_id
AND plc.lookup_type = 'ASL_VENDOR_BUSINESS_TYPE'
and past.status='Approved';