Thursday, 4 January 2018

How to Find the Supplier extraction script for EBS Tax

SELECT     ap.segment1 vendor_code,
           ap.vendor_name Suppliers_Name,
           aps.vendor_site_code Supplier_Site_Code,
           ap.VENDOR_TYPE_LOOKUP_CODE Type_Code,
           aps.address_line1,
           aps.address_line2,
           aps.address_line3,
           aps.city,
           aps.state,
           aps.zip,
           aps.Country,
           apc.AREA_CODE,
           apc.PHONE,
           apc.FAX_AREA_CODE,
           apc.FAX,
           apc.EMAIL_ADDRESS,
           ap.PAYMENT_CURRENCY_CODE CURRENCY_CODE,
           (Select Name from hr_operating_units where organization_id=aps.ORG_ID) OU_NAME,
           aps.vat_registration_num,
           ap.offset_tax_flag offset_tax_flag_header,
           aps.auto_tax_calc_flag,
           aps.offset_tax_flag offset_tax_flag_line,
           aps.attribute2 free_field
    FROM   ap_suppliers ap,
           ap_supplier_sites_all aps,
           ap_supplier_contacts apc
   WHERE       ap.vendor_id = aps.vendor_id
           AND apc.VENDOR_CONTACT_ID(+) = ap.VENDOR_ID
           AND ap.VENDOR_TYPE_LOOKUP_CODE <>  'EMPLOYEE'
           AND aps.vendor_site_code NOT IN 'OFFICE'
           AND ap.enabled_flag ='Y'
ORDER BY   ap.vendor_name,ap.segment1

How to Find the Customer extraction script for EBS Tax

SELECT hp.party_name,
       hp.party_number,
       hca.account_number,
       hca.cust_account_id,
       hp.party_id,
       hps.party_site_id,
       hcsu.cust_acct_site_id,
       hps.location_id,
       hl.address1,
       hl.address2,
       hl.address3,
       hl.city,
       hl.state,
       ter.nls_territory,
       hl.postal_code,
       hl.province,
       hcsu.site_use_code,
       hcsu.site_use_id,
       hcsa.bill_to_flag,
       (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REC) RECEIVABLES_AC,
       (SELECT concatenated_segments FROM gl_code_combinations_kfv WHERE code_combination_id=hcsu.GL_ID_REV) REVENUE_AC,
       (Select Name from hr_operating_units where organization_id = hcsa.ORG_ID) OU_NAME,
       zp.REP_REGISTRATION_NUMBER,
       zr.TAX_REGIME_CODE, zr.tax,
       zr.REGISTRATION_NUMBER
FROM hz_parties hp,
     hz_party_sites hps,
     hz_locations hl,
     hz_cust_accounts_all hca,
     hz_cust_acct_sites_all hcsa,
     hz_cust_site_uses_all hcsu,
     fnd_territories ter,
     zx_party_tax_profile zp,
     zx_registrations zr
WHERE hca.STATUS ='A' --Active    
      AND hp.party_id = hps.party_id
      AND hps.location_id = hl.location_id
      AND hp.party_id = hca.party_id
      AND hcsa.party_site_id = hps.party_site_id
      AND hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
      AND hca.cust_account_id = hcsa.cust_account_id
      AND hl.country = ter.territory_code
       AND hps.party_site_id = zp.party_id
---       AND zp.PARTY_TYPE_CODE = 'THIRD_PARTY'
       AND PARTY_TYPE_CODE = 'THIRD_PARTY_SITE'
       AND zr.party_tax_profile_id(+)= zp.party_tax_profile_id
Order By party_name