Supplier / Customer Tax Profiles Information

Supplier / Customer Tax Profiles Information
I have been working in a data migration project, and we have migrated customers and suppliers.

We have also uploaded tax profiles for them, i had to write SQL scripts to check/reconcile the tax profiles.

And these are them. Hopefully they are useful to any of you looking for the same.


--Customers and their tax profiles

SELECT A.customer_name,
  DECODE(A.customer_type, 'I', 'Internal', 'R', 'External') customer_type,
  a1.profile_class_name,
  a.creation_date,
  b.party_name,
  b.party_number,
  c.party_type_code,
  d.creation_date,
  d.class_category,
  d.class_code,
  d.start_date_active,
  e.tax_regime_code,
  e.registration_number,
  e.registration_status_code,
  e.effective_from,
  --A.vat_code,
  A.tax_reference,
  b1.party_site_name,
  b1.party_site_number,
  c1.party_type_code site_party_type_code,
  e1.tax_regime_code site_tax_regime_code,
  e1.registration_number site_registration_number,
  e1.registration_status_code site_registration_status_code,
  e1.effective_from site_effective_from,
  d1.class_category site_class_category,
  d1.class_code site_class_code,
  d1.start_date_active site_start_date_active
FROM ar_customers A,
  ar_customer_profiles_v a1,
  hz_parties b,
  hz_party_sites b1,
  zx_party_tax_profile c,
  zx_party_tax_profile c1,
  hz_code_assignments d,
  hz_code_assignments d1,
  zx_registrations e,
  zx_registrations e1
WHERE A.orig_system_reference =b.orig_system_reference
AND A.customer_id             =a1.customer_id(+)
AND b.party_id                =c.party_id(+)
AND c.party_tax_profile_id    =d.owner_table_id(+)
AND c.party_type_code(+)      ='THIRD_PARTY'
AND c.party_tax_profile_id    =e.party_tax_profile_id(+)
AND d.class_category(+)       ='XXXX'
AND b.party_id                =b1.party_id
AND b1.party_site_id          =c1.party_id(+)
AND c1.party_type_code(+)     ='THIRD_PARTY_SITE'
AND c1.party_tax_profile_id   =d1.owner_table_id(+)
AND c1.party_tax_profile_id   =e1.party_tax_profile_id(+);



--Suppliers and their tax profiles



SELECT --A.vendor_name,

  A.segment1 Suuplier_No,

  A.vendor_name Suuplier_Name,

  A.VENDOR_NAME_ALT Supp_ARABIC_NAME,

  A.ATTRIBUTE1 SUPP_Check_Name,

  --b.party_name,

  A.vendor_type_lookup_code,

  --b.party_number,

  c.party_type_code,

  c.process_for_applicability_flag allow_tax_applicability,

  c.allow_offset_tax_flag allow_offset_tax,

  d.class_category,

  d.class_code,

  d.start_date_active,

  d.end_date_active,

  e.tax_regime_code,

  e.registration_number,

  e.registration_status_code,

  e.effective_from,

  e.effective_to,

  A.vat_code,

  b1.party_site_name,

  b1.party_site_number,

  c1.party_type_code site_party_type_code,

  c1.process_for_applicability_flag site_allow_tax_applicability,

  c1.allow_offset_tax_flag site_allow_offset_tax,

  d1.class_category site_class_category,

  d1.class_code site_class_code,

  d1.start_date_active site_start_date_active,

  e1.tax_regime_code site_tax_regime_code,

  e1.registration_number site_registration_number,

  e1.registration_status_code site_registration_status_code,

  e1.effective_from site_effective_from,

  e1.effective_to site_effective_to

FROM ap_suppliers A,

  hz_parties b,

  hz_party_sites b1,

  zx_party_tax_profile c,

  zx_party_tax_profile c1,

  hz_code_assignments d,

  hz_code_assignments d1,

  zx_registrations e,

  zx_registrations e1

WHERE A.party_id                   =b.party_id

AND b.party_id                     =c.party_id(+)

AND c.party_type_code(+)           ='THIRD_PARTY'

AND c.party_tax_profile_id         =d.owner_table_id(+)

AND c.party_tax_profile_id         =e.party_tax_profile_id(+)

AND d.class_category(+)            =' XXXX '

AND A.vendor_type_lookup_code NOT IN ('EMPLOYEE', 'INTERNAL')

AND b.party_id                     =b1.party_id(+)

AND b1.party_site_id               =c1.party_id(+)

AND c1.party_type_code(+)          ='THIRD_PARTY_SITE'

AND c1.party_tax_profile_id        =d1.owner_table_id(+)

AND c1.party_tax_profile_id        =e1.party_tax_profile_id(+)

and A.segment1='1542'

  --and b.party_name='James Fleming'

ORDER BY d.class_category DESC,

  e.registration_status_code,

  b.party_name;