Supplier SQL query

Query to get Supplier data from Oracle R12

SELECT   aps.segment1 "SupplierNum",
         assa.vendor_site_code "Address Book Identifier ",
         aps.vendor_name "Supplier Company Name ",
         --Hou.name "Operating Unit Name",
         Assa.address_line1 "Supplier Address Line1",
         Assa.address_line2 "Supplier Address Line2",
         assa.address_line3 "Supplier Address Line3",
         assa.address_line4 "Supplier Address Line4",
         assa.city "Supplier city",
         assa.state || assa.PROVINCE "Supplier State/Province",
         assa.country "Supp_country_Code",
         assa.zip "Supp_postal_Code",
         apsite_rmt.ADDRESS_LINE1 "Supp_Remit_to_Address_Line_1",
         apsite_rmt.ADDRESS_LINE2 "Supp_Remit_to_Address_Line_2",
         apsite_rmt.ADDRESS_LINE3 "Supp_Remit_to_Address_Line_3",
         NULL "Supp_Remit_to_Address_Line_4",
         apsite_rmt.CITY "Supp_Remit_to_City",
         apsite_rmt.STATE || apsite_rmt.PROVINCE "Supp_Remit_to_State/Province",
         apsite_rmt.ZIP "sup_remit_Supplier_Postal_Code",
         apsite_rmt.COUNTRY "Supplier Remit to Country Code",
         sup_person.first_name Sup_Contact_First_Name,
         sup_person.last_name Sup_Contact_last_Name,
         sup_person.email Sup_Contact_email,
         sup_person.phone Sup_Contact_phone,
         parent_supp.segment1 "Parent Supplier Number" ,
         parent_supp.vendor_site_code "Parent Address Book Identifier",
         parent_supp.vendor_name "Parent Company Name",
         parent_supp.ADDRESS_LINE1 "Address Line 1",
         parent_supp.ADDRESS_LINE2 "Address Line 2",
         parent_supp.ADDRESS_LINE3 "Address Line 3",
         NULL "Address Line 4",
         parent_supp.CITY "Supplier City",
         parent_supp.STATE || parent_supp.PROVINCE "Supplier State/Province",
         parent_supp.COUNTRY "Supplier Country Code",
         parent_supp.ZIP "Supplier Postal Code",
--         hzp_rmt.party_name REMIT_SUPPLIER_NAME,
         --apsite_rmt.vendor_site_code REMIT_SUPPLIER_SITE_NAME,
          aps.NUM_1099       "supplier_tax_id",
          null              "Supp_For_TaxID",
          null              "GIIN",
         assa.DUNS_NUMBER              "Dunn_number",
         apt.name Payment_Terms,
         sup_payment.PAYMENT_METHOD_CODE PAYMENT_METHOD,
        --aia.invoice_currency_code  Payment_currency_of_location
         sup_payment.invoice_currency_code Payment_currency_of_location,
         sup_payment.PAYMENT_CURRENCY_CODE Payment_currency_to_supplier,
         decode(aps.END_DATE_ACTIVE,null,'ACTIVE','IN ACTIVE') "Supplier active code ",
         flv.meaning "Supplier Type",
        null purch_loc_account,
        null purch_loc_comp_name,
        poc.ADDRESS_LINE_1 purch_loc_address1,
                 poc.ADDRESS_LINE_2 purch_loc_address2,
                 poc.ADDRESS_LINE_3  purch_loc_address3,
                 poc.TOWN_OR_CITY    purch_loc_city,
                 poc.REGION_2        purch_loc_region,
                 poc.POSTAL_CODE     purch_loc_postalcode,
                 poc.COUNTRY         purch_loc_country
  FROM   apps.ap_suppliers aps,
         apps.ap_supplier_sites_all assa,
         apps.FND_LOOKUP_VALUES flv,
        -- apps.ap_suppliers parent_sup,
       --  apps.ap_supplier_sites_all parent_site,
         apps.hz_parties hp,
         apps.IBY_EXT_PAYEE_RELATIONSHIPS Remit,
         apps.hz_parties hzp_inv,
         apps.hz_parties hzp_rmt,
         apps.ap_supplier_sites_all apsite_inv,
         apps.ap_supplier_sites_all apsite_rmt,
         apps.ap_terms apt,
        -- apps.ap_invoices_all aia,
         (SELECT   person.party_id,
                   Person.person_first_name first_name,
                   Person.person_last_name last_name,
                   Pty_rel.primary_phone_number phone,
                   Pty_rel.email_address email
            FROM   apps.Ap_supplier_sites_all ass,
                   apps.Ap_supplier_contacts apsc,
                   apps.Hz_parties person,
                   apps.Hz_parties pty_rel
           WHERE       1 = 1
                   AND apsc.per_party_id = person.party_id
                   AND apsc.rel_party_id = pty_rel.party_id
                   AND apsc.org_party_site_id = ass.party_site_id) sup_person,
         (SELECT  distinct                                                   
                iba.PAYMENT_METHOD_CODE,
                   iba.PAYMENT_CURRENCY_CODE,
                   aia.invoice_currency_code,
                   aia.vendor_id
            -- ,ass.segment1
            FROM   apps.ap_invoices_all aia,
                   apps.ap_invoice_payments_all aipa,
                   apps.iby_payments_all iba,
                   apps.ap_checks_all aca,
                   apps.ap_suppliers ass,
                   ap_supplier_sites_all assa
           WHERE       1 = 1
                   AND iba.payment_id = aca.payment_id
                   AND aca.check_id = aipa.check_id
                   AND aia.invoice_id = aipa.invoice_id
                   AND iba.org_id = aia.org_id
                   AND aia.org_id = aipa.org_id
                   AND aia.vendor_id = ass.vendor_id
                   AND iba.EXT_BANK_ACCT_OWNER_PARTY_ID = ass.party_id
                   and iba.EXT_BANK_ACCOUNT_NUMBER is not null
                   AND iba.PAYMENT_METHOD_CODE IN ('CHECK', 'WIRE', 'ACH')
                   and aia.org_id=assa.org_id) sup_payment,
          (select
                 ADDRESS_LINE_1,
                 ADDRESS_LINE_2,
                 ADDRESS_LINE_3,
                 TOWN_OR_CITY,
                 hl.REGION_2,
                 hl.POSTAL_CODE,
                 hl.COUNTRY,
                 hou.ORGANIZATION_ID
              from
                  --apps.hz_parties hp,
                  --apps.hz_party_sites hps,
                   hr_all_organization_units hou,
                    hr_locations hl
             where hou.LOCATION_ID = hl.LOCATION_ID)POC,
(select  parent_sup.segment1,parent_sup.vendor_id,
         parent_sup.vendor_name ,
         parent_site.vendor_site_code,
         parent_site.ADDRESS_LINE1 ,
         parent_site.ADDRESS_LINE2 ,
         parent_site.ADDRESS_LINE3 ,
         NULL parent_Supplier_Address_Line_4,
         parent_site.CITY ,
         parent_site.STATE ,
         parent_site.ZIP ,
         parent_site.PROVINCE ,
         parent_site.COUNTRY
from apps.ap_suppliers parent_sup,
         apps.ap_supplier_sites_all parent_site,
         apps.ap_suppliers aps1
where aps1.parent_vendor_id = parent_sup.vendor_id(+)
     AND parent_sup.vendor_id = parent_site.vendor_id) parent_supp
 WHERE       1 = 1
         AND aps.vendor_id = assa.vendor_id
         AND flv.lookup_type(+) = 'VENDOR TYPE'
         AND aps.VENDOR_TYPE_LOOKUP_CODE = flv.LOOKUP_CODE(+)
         AND aps.PARTY_ID = hp.party_id
----         AND aps.parent_vendor_id = parent_sup.vendor_id(+)
----         AND parent_sup.vendor_id = parent_site.vendor_id
         AND hp.PARTY_TYPE = 'ORGANIZATION'
        AND Remit.party_id(+) = aps.party_id
         AND Remit.remit_party_id = hzp_rmt.party_id(+)
         AND Remit.supplier_site_id = apsite_inv.vendor_site_id(+)
         AND Remit.remit_supplier_site_id = apsite_rmt.vendor_site_id(+)
         AND hzp_inv.party_id=aps.party_id
         AND aps.PARTY_ID = sup_person.party_id(+)
         AND aps.terms_id = apt.term_id(+)
         and poc.organization_id=assa.org_id
         and aps.parent_vendor_id=parent_supp.vendor_id(+)
         AND aps.vendor_id = sup_payment.vendor_id(+)
    order by 1;


Query to get Supplier invoices data from Oracle R12

select
aps.SEGMENT1 "Supplier ID",
aia.INVOICE_NUM "Voucher Number",
aip.ACCOUNTING_DATE "Payment Date",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Local Invoice Amount",
aia.DISCOUNT_AMOUNT_TAKEN "Local Discount Amount ",
aia.INVOICE_CURRENCY_CODE "Local Invoice Currency",
aia.INVOICE_NUM "Invoice Number",
aip.PAYMENT_NUM "Payment Number",
aia.PAYMENT_CURRENCY_CODE "Supplier Invoice Currency",
apa.DUE_DATE  "Payment Due Date",
0 "Supplier Invoice Amount",
0 "Supplier Discount Amount",
(INVOICE_AMOUNT - AMOUNT_PAID ) "Local out invoice amount",
0 "Supplier out invoice amount",
decode(AIA.PAYMENT_STATUS_FLAG, 'Y','PAID','N','UNPAID','P','PARTIAL PAID') "Invoice Status",
null "PO Location Account Number",
POC.name "PO Location Company Name",
POC.ADDRESS_LINE_1 "PO Location Address Line 1",
POC.ADDRESS_LINE_2 "PO Location Address Line 2",
POC.ADDRESS_LINE_3 "PO Location Address Line 3",
POC.TOWN_OR_CITY "PO Location City",
POC.REGION_2 "PO Location State/Province",
POC.POSTAL_CODE "PO Location Postal Code",
POC.COUNTRY "PO Location Country Code"
from apps.ap_invoices_all aia,
apps.ap_suppliers aps,
apps.ap_payment_schedules_all apa,
apps.AP_INVOICE_PAYMENTS_ALL aip,
(select
hou.name,
ADDRESS_LINE_1,
ADDRESS_LINE_2,
ADDRESS_LINE_3,
TOWN_OR_CITY,
hl.REGION_2,
hl.POSTAL_CODE,
hl.COUNTRY,
hou.ORGANIZATION_ID
from
hr_all_organization_units hou,
hr_locations hl
where hou.LOCATION_ID = hl.LOCATION_ID
) POC
where aia.vendor_id = aps.vendor_id
and aia.INVOICE_ID= apa.INVOICE_ID
and aia.INVOICE_ID= aip.INVOICE_ID
and POC.ORGANIZATION_ID = aia.org_id
;