CMF_PARTY_TAXPAYER_INFO_V

Details

• Schema: FUSION

• Object owner: CMF

• 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 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 ( ( ( hl.country = 'BR' ) AND ( zpti.tax_payer_number IS NOT NULL ) ) OR ( hl.country <> 'BR' ) ) AND trunc(SYSDATE) BETWEEN nvl(zpti.effective_from,trunc(SYSDATE) - 1) AND nvl(zpti.effective_to,trunc(SYSDATE + 1) ) AND zpti.reporting_type_code(+) IN ( 'ORA_BR_CNPJ', 'ORA_BR_CPF', 'ORA_BR_OTHER' ) AND zpti.entity_id (+) = zptp.party_tax_profile_id AND zptp.party_type_code = 'THIRD_PARTY_SITE' AND 'Y' IN ( zptp.site_flag, zptp.customer_flag ) AND zptp.party_id = hps.party_site_id AND hps.party_id = hp.party_id AND hps.location_id = hl.location_id