CMF_PARTY_TAXPAYER_BR_INFO_V

Details

• Schema: FUSION

• Object owner: FUSION

• Object type: VIEW

Columns

Name
PARTY_TAXPAYER_IDNTFS_ID FORMATTED_TAX_ID TAXPAYER_ID FORMATTED_TAXPAYER_NUMBER PARTY_ID PARTY_NAME PARTY_SITE_ID PARTY_SITE_NAME LOCATION_ID ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 CITY STATE COUNTRY POSTAL_CODE FORMATTED_ADDRESS SUPPLIER_FLAG CUSTOMER_FLAG

Query

SQL_Statement

select zpti.party_taxpayer_idntfs_id ,nvl(zpti.party_taxpayer_idntfs_id,-999) Formatted_Tax_id ,zpti.tax_payer_number taxpayer_id ,lpad(zpti.tax_payer_number,14,'0') formatted_taxpayer_number ,hp.party_id ,hp.party_name ,hps.party_site_id ,hps.party_site_name ,hl.location_id ,hl.address1 ,hl.address2 ,hl.address3 ,hl.address4 ,hl.city city ,hl.state state ,hl.country ,hl.postal_code ,replace(replace(hl.address1||', '||hl.address2||', '||hl.address3||', '||hl.address4||', '||hl.city||', '||hl.state||', '||hl.country||', '||hl.postal_code,',,',','),', ,',',') formatted_address ,zptp.supplier_flag ,zptp.customer_flag from zx_party_taxpayer_idntfs zpti ,zx_party_tax_profile zptp ,hz_parties hp ,hz_party_sites hps ,hz_locations hl where zpti.entity_id = zptp.party_tax_profile_id and zptp.party_id = hps.party_site_id and hps.party_id = hp.party_id and hps.location_id = hl.location_id and zpti.reporting_type_code in ('ORA_BR_CNPJ','ORA_BR_CPF','ORA_BR_OTHER') and zptp.party_type_code = 'THIRD_PARTY_SITE' and zptp.site_flag = 'Y' and trunc(sysdate) between nvl(zpti.effective_from,trunc(sysdate)-1) and nvl(zpti.effective_to,trunc(sysdate+1))