1. Tables and Views for Financials
  2. ZX_FIRST_PARTY_ORGS_ALL_V

ZX_FIRST_PARTY_ORGS_ALL_V

Details

  • Schema: FUSION

  • Object owner: FND

  • Object type: VIEW

Columns

Name

PARTY_TAX_PROFILE_ID

PARTY_NAME

PARTY_TYPE_CODE

ADDRESS_LINE_1

ADDRESS_LINE_2

ADDRESS_LINE_3

TOWN_OR_CITY

REGION_1

REGION_2

POSTAL_CODE

COUNTRY_CODE

USE_LE_AS_SUBSCRIBER_FLAG

WHT_DATE_BASIS

ALLOW_ZERO_AMT_WHT_INV_FLAG

WHT_ROUNDING_LEVEL_CODE

WHT_ROUNDING_RULE_CODE

WHT_USE_LE_AS_SUBSCRIBER_FLAG

WHT_EFFECTIVE_FROM_USE_LE

Query

SQL_Statement

SELECT

PTP.PARTY_TAX_PROFILE_ID,

xlep.NAME PARTY_NAME,

PTP.PARTY_TYPE_CODE,

hrl.ADDRESS_LINE_1,

hrl.ADDRESS_LINE_2,

hrl.ADDRESS_LINE_3,

hrl.TOWN_OR_CITY,

hrl.REGION_1,

hrl.REGION_2,

hrl.POSTAL_CODE,

hrl.COUNTRY AS COUNTRY_CODE,

PTP.USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_DATE_BASIS,

PTP.ALLOW_ZERO_AMT_WHT_INV_FLAG,

PTP.WHT_ROUNDING_LEVEL_CODE,

PTP.WHT_ROUNDING_RULE_CODE,

PTP.WHT_USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_EFFECTIVE_FROM_USE_LE

FROM ZX_PARTY_TAX_PROFILE PTP,

HZ_PARTIES HZP,

XLE_entity_profiles xlep,

xle_registrations xler,

xle_locations_v hrl

WHERE PTP.PARTY_ID = HZP.PARTY_ID

AND PTP.PARTY_TYPE_CODE IN ('FIRST_PARTY')

AND hzp.party_id = xlep.party_id

AND xlep.legal_entity_id = xler.source_id

AND xler.source_table = 'XLE_ENTITY_PROFILES'

AND NVL(xler.IDENTIFYING_FLAG,'N') = 'Y'

AND xler.location_id = hrl.location_id

UNION ALL

SELECT PTP.PARTY_TAX_PROFILE_ID,

hro.BU_NAME PARTY_NAME,

PTP.PARTY_TYPE_CODE,

hrloc.ADDRESS_LINE_1,

hrloc.ADDRESS_LINE_2,

hrloc.ADDRESS_LINE_3,

hrloc.TOWN_OR_CITY,

hrloc.REGION_1,

hrloc.REGION_2,

hrloc.POSTAL_CODE,

hrloc.COUNTRY AS COUNTRY_CODE,

PTP.USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_DATE_BASIS,

PTP.ALLOW_ZERO_AMT_WHT_INV_FLAG,

PTP.WHT_ROUNDING_LEVEL_CODE,

PTP.WHT_ROUNDING_RULE_CODE,

PTP.WHT_USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_EFFECTIVE_FROM_USE_LE

FROM ZX_PARTY_TAX_PROFILE PTP,

FUN_ALL_Business_units_v hro,

HR_Locations_all hrloc

WHERE PTP.PARTY_TYPE_CODE = 'OU'

AND PTP.PARTY_ID = hro.BU_ID

AND hro.location_id = hrloc.location_id (+)

UNION ALL

SELECT

PTP.PARTY_TAX_PROFILE_ID,

LU.MEANING PARTY_NAME,

PTP.PARTY_TYPE_CODE,

'' ADDRESS_LINE_1,

'' ADDRESS_LINE_2,

'' ADDRESS_LINE_3,

'' TOWN_OR_CITY,

'' REGION_1,

'' REGION_2,

'' POSTAL_CODE,

'' COUNTRY_CODE,

PTP.USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_DATE_BASIS,

PTP.ALLOW_ZERO_AMT_WHT_INV_FLAG,

PTP.WHT_ROUNDING_LEVEL_CODE,

PTP.WHT_ROUNDING_RULE_CODE,

PTP.WHT_USE_LE_AS_SUBSCRIBER_FLAG,

PTP.WHT_EFFECTIVE_FROM_USE_LE

FROM ZX_PARTY_TAX_PROFILE PTP,

FND_LOOKUPS LU

WHERE PTP.PARTY_TYPE_CODE = 'GCO'

AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE

AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'