VIEW: APPS.IEX_LM_ORG_EXPAND_W_ACC_V

Source

View Text - Preformatted

SELECT PARTY.PARTY_ID PARTY_ID, PARTY.PARTY_NUMBER PARTY_NUMBER, ACCOUNT.ACCOUNT_NUMBER ACCOUNT_NUMBER, account.cust_account_id cust_account_id, PARTY.PARTY_NAME PARTY_NAME, loc.ADDRESS1||' '||loc.ADDRESS2||' '||loc.ADDRESS3||' '||loc.ADDRESS4 ADDRESS, loc.ADDRESS1 ADDRESS1, loc.ADDRESS2 ADDRESS2, loc.ADDRESS3 ADDRESS3, loc.ADDRESS4 ADDRESS4, loc.CITY CITY, loc.COUNTY COUNTY, loc.STATE STATE, loc.PROVINCE PROVINCE, loc.POSTAL_CODE POSTAL_CODE, loc.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC, loc.ACTUAL_CONTENT_SOURCE ACTUAL_CONTENT_SOURCE, loc.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE, loc.ADDRESS_ERROR_CODE ADDRESS_ERROR_CODE, loc.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE, loc.ADDRESS_KEY ADDRESS_KEY, loc.ADDRESS_STYLE ADDRESS_STYLE, loc.APARTMENT_FLAG APARTMENT_FLAG, loc.APARTMENT_NUMBER APARTMENT_NUMBER, loc.APPLICATION_ID APPLICATION_ID, loc.BUILDING BUILDING, loc.CLLI_CODE CLLI_CODE, loc.CONTENT_SOURCE_TYPE CONTENT_SOURCE_TYPE, loc.COUNTRY COUNTRY, loc.CREATED_BY CREATED_BY, loc.CREATED_BY_MODULE CREATED_BY_MODULE, loc.CREATION_DATE CREATION_DATE, loc.DELIVERY_POINT_CODE DELIVERY_POINT_CODE, loc.DESCRIPTION DESCRIPTION, loc.DODAAC DODAAC, loc.FA_LOCATION_ID FA_LOCATION_ID, loc.FLOOR FLOOR, loc.GEOMETRY_STATUS_CODE GEOMETRY_STATUS_CODE, loc.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY, loc.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1, loc.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10, loc.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11, loc.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12, loc.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13, loc.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14, loc.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15, loc.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16, loc.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17, loc.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18, loc.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19, loc.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2, loc.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20, loc.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3, loc.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4, loc.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5, loc.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6, loc.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7, loc.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8, loc.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9, loc.HOUSE_NUMBER HOUSE_NUMBER, loc.LAST_UPDATE_DATE LAST_UPDATE_DATE, loc.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN, loc.LAST_UPDATED_BY LAST_UPDATED_BY, loc.LIFE_CYCLE_STATUS LIFE_CYCLE_STATUS, loc.LOC_HIERARCHY_ID LOC_HIERARCHY_ID, loc.LOCATION_DIRECTIONS LOCATION_DIRECTIONS, loc.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER, loc.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE, loc.OVERSEAS_ADDRESS_FLAG OVERSEAS_ADDRESS_FLAG, loc.PO_BOX_NUMBER PO_BOX_NUMBER, loc.POSITION POSITION, loc.POST_OFFICE POST_OFFICE, loc.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE, loc.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID, loc.PROGRAM_ID PROGRAM_ID, loc.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE, loc.REQUEST_ID REQUEST_ID, loc.ROOM ROOM, loc.RURAL_ROUTE_NUMBER RURAL_ROUTE_NUMBER, loc.RURAL_ROUTE_TYPE RURAL_ROUTE_TYPE, loc.SALES_TAX_GEOCODE SALES_TAX_GEOCODE, loc.SALES_TAX_INSIDE_CITY_LIMITS SALES_TAX_INSIDE_CITY_LIMITS, loc.SECONDARY_SUFFIX_ELEMENT SECONDARY_SUFFIX_ELEMENT, loc.SHORT_DESCRIPTION SHORT_DESCRIPTION, loc.STREET STREET, loc.STREET_NUMBER STREET_NUMBER, loc.STREET_SUFFIX STREET_SUFFIX, loc.SUITE SUITE, time. timezone_code TIME_ZONE , loc.TRAILING_DIRECTORY_CODE TRAILING_DIRECTORY_CODE, loc.VALIDATED_FLAG VALIDATED_FLAG, loc.WH_UPDATE_DATE WH_UPDATE_DATE, FTT.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME, HCP2.EMAIL_ADDRESS EMAIL_ADDRESS, CONT_POINT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE, CONT_POINT.PHONE_AREA_CODE PHONE_AREA_CODE, CONT_POINT.PHONE_NUMBER PHONE_NUMBER, CONT_POINT.PHONE_EXTENSION PHONE_EXTENSION, NVL(CONT_POINT.PHONE_COUNTRY_CODE, '   ')||'-'|| NVL(CONT_POINT.PHONE_AREA_CODE, '   ')||'-'|| CONT_POINT.PHONE_NUMBER ||' '|| CONT_POINT.PHONE_EXTENSION FULL_PHONE_NUMBER, CONT_POINT.PHONE_AREA_CODE||CONT_POINT.PHONE_NUMBER PHONE_AREA_NUMBER, PARTY.STATUS STATUS, ALK.MEANING STATUS_MEAN, PARTY.URL URL, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE, ALK1.MEANING CATEGORY_CODE, PARTY.JGZZ_FISCAL_CODE TAX_ID, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE, PARTY.DUNS_NUMBER DUNS_NUMBER, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH, PARTY.SIC_CODE SIC, PARTY.TAX_REFERENCE TAX_REFERENCE, PARTY.ATTRIBUTE1 ATTRIBUTE1, PARTY.ATTRIBUTE2 ATTRIBUTE2, PARTY.ATTRIBUTE3 ATTRIBUTE3, PARTY.ATTRIBUTE4 ATTRIBUTE4, PARTY.ATTRIBUTE5 ATTRIBUTE5, PARTY.ATTRIBUTE6 ATTRIBUTE6, PARTY.ATTRIBUTE7 ATTRIBUTE7, PARTY.ATTRIBUTE8 ATTRIBUTE8, PARTY.ATTRIBUTE9 ATTRIBUTE9, PARTY.ATTRIBUTE10 ATTRIBUTE10, PARTY.ATTRIBUTE11 ATTRIBUTE11, PARTY.ATTRIBUTE12 ATTRIBUTE12, PARTY.ATTRIBUTE13 ATTRIBUTE13, PARTY.ATTRIBUTE14 ATTRIBUTE14, PARTY.ATTRIBUTE15 ATTRIBUTE15, PARTY.ATTRIBUTE16 ATTRIBUTE16, PARTY.ATTRIBUTE17 ATTRIBUTE17, PARTY.ATTRIBUTE18 ATTRIBUTE18, PARTY.ATTRIBUTE19 ATTRIBUTE19, PARTY.ATTRIBUTE20 ATTRIBUTE20 FROM HZ_PARTIES PARTY , HZ_CONTACT_POINTS CONT_POINT , HZ_CONTACT_POINTS HCP2 , FND_TERRITORIES_TL FTT , AR_LOOKUPS ALK , AR_LOOKUPS ALK1 , HZ_CUST_ACCOUNTS ACCOUNT , HZ_PARTY_SITES SITE , HZ_LOCATIONS LOC , fnd_TIMEZONES_TL TIME, fnd_timezones_b time_b WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+) AND TIME_B.upgrade_tz_id(+)= LOC.TIMEZONE_ID AND time_b.timezone_code = time.timezone_code(+) AND TIME.LANGUAGE(+) = userenv('LANG') AND party.party_id = site.party_id(+) and site.IDENTIFYING_ADDRESS_FLAG(+) = 'Y' and PARTY.PARTY_TYPE = 'ORGANIZATION' AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE' AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID AND CONT_POINT.PRIMARY_FLAG(+) = 'Y' AND CONT_POINT.STATUS (+) = 'A' AND HCP2.CONTACT_POINT_TYPE(+) = 'EMAIL' AND HCP2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES' AND HCP2.OWNER_TABLE_ID(+) = PARTY.PARTY_ID AND loc.COUNTRY = FTT.TERRITORY_CODE(+) AND FTT.LANGUAGE(+) = userenv('LANG') AND FTT.SOURCE_LANG(+) = userenv('LANG') AND ALK.LOOKUP_TYPE (+) = 'CODE_STATUS' AND ALK.LOOKUP_CODE (+) = PARTY.STATUS AND ACCOUNT.PARTY_ID(+) = PARTY.PARTY_ID AND ALK1.LOOKUP_TYPE (+) = 'CUSTOMER_CATEGORY' AND ALK1.ENABLED_FLAG (+) = 'Y' AND ALK1.LOOKUP_CODE (+) = PARTY.CATEGORY_CODE

View Text - HTML Formatted

SELECT PARTY.PARTY_ID PARTY_ID
, PARTY.PARTY_NUMBER PARTY_NUMBER
, ACCOUNT.ACCOUNT_NUMBER ACCOUNT_NUMBER
, ACCOUNT.CUST_ACCOUNT_ID CUST_ACCOUNT_ID
, PARTY.PARTY_NAME PARTY_NAME
, LOC.ADDRESS1||' '||LOC.ADDRESS2||' '||LOC.ADDRESS3||' '||LOC.ADDRESS4 ADDRESS
, LOC.ADDRESS1 ADDRESS1
, LOC.ADDRESS2 ADDRESS2
, LOC.ADDRESS3 ADDRESS3
, LOC.ADDRESS4 ADDRESS4
, LOC.CITY CITY
, LOC.COUNTY COUNTY
, LOC.STATE STATE
, LOC.PROVINCE PROVINCE
, LOC.POSTAL_CODE POSTAL_CODE
, LOC.ADDRESS_LINES_PHONETIC ADDRESS_LINES_PHONETIC
, LOC.ACTUAL_CONTENT_SOURCE ACTUAL_CONTENT_SOURCE
, LOC.ADDRESS_EFFECTIVE_DATE ADDRESS_EFFECTIVE_DATE
, LOC.ADDRESS_ERROR_CODE ADDRESS_ERROR_CODE
, LOC.ADDRESS_EXPIRATION_DATE ADDRESS_EXPIRATION_DATE
, LOC.ADDRESS_KEY ADDRESS_KEY
, LOC.ADDRESS_STYLE ADDRESS_STYLE
, LOC.APARTMENT_FLAG APARTMENT_FLAG
, LOC.APARTMENT_NUMBER APARTMENT_NUMBER
, LOC.APPLICATION_ID APPLICATION_ID
, LOC.BUILDING BUILDING
, LOC.CLLI_CODE CLLI_CODE
, LOC.CONTENT_SOURCE_TYPE CONTENT_SOURCE_TYPE
, LOC.COUNTRY COUNTRY
, LOC.CREATED_BY CREATED_BY
, LOC.CREATED_BY_MODULE CREATED_BY_MODULE
, LOC.CREATION_DATE CREATION_DATE
, LOC.DELIVERY_POINT_CODE DELIVERY_POINT_CODE
, LOC.DESCRIPTION DESCRIPTION
, LOC.DODAAC DODAAC
, LOC.FA_LOCATION_ID FA_LOCATION_ID
, LOC.FLOOR FLOOR
, LOC.GEOMETRY_STATUS_CODE GEOMETRY_STATUS_CODE
, LOC.GLOBAL_ATTRIBUTE_CATEGORY GLOBAL_ATTRIBUTE_CATEGORY
, LOC.GLOBAL_ATTRIBUTE1 GLOBAL_ATTRIBUTE1
, LOC.GLOBAL_ATTRIBUTE10 GLOBAL_ATTRIBUTE10
, LOC.GLOBAL_ATTRIBUTE11 GLOBAL_ATTRIBUTE11
, LOC.GLOBAL_ATTRIBUTE12 GLOBAL_ATTRIBUTE12
, LOC.GLOBAL_ATTRIBUTE13 GLOBAL_ATTRIBUTE13
, LOC.GLOBAL_ATTRIBUTE14 GLOBAL_ATTRIBUTE14
, LOC.GLOBAL_ATTRIBUTE15 GLOBAL_ATTRIBUTE15
, LOC.GLOBAL_ATTRIBUTE16 GLOBAL_ATTRIBUTE16
, LOC.GLOBAL_ATTRIBUTE17 GLOBAL_ATTRIBUTE17
, LOC.GLOBAL_ATTRIBUTE18 GLOBAL_ATTRIBUTE18
, LOC.GLOBAL_ATTRIBUTE19 GLOBAL_ATTRIBUTE19
, LOC.GLOBAL_ATTRIBUTE2 GLOBAL_ATTRIBUTE2
, LOC.GLOBAL_ATTRIBUTE20 GLOBAL_ATTRIBUTE20
, LOC.GLOBAL_ATTRIBUTE3 GLOBAL_ATTRIBUTE3
, LOC.GLOBAL_ATTRIBUTE4 GLOBAL_ATTRIBUTE4
, LOC.GLOBAL_ATTRIBUTE5 GLOBAL_ATTRIBUTE5
, LOC.GLOBAL_ATTRIBUTE6 GLOBAL_ATTRIBUTE6
, LOC.GLOBAL_ATTRIBUTE7 GLOBAL_ATTRIBUTE7
, LOC.GLOBAL_ATTRIBUTE8 GLOBAL_ATTRIBUTE8
, LOC.GLOBAL_ATTRIBUTE9 GLOBAL_ATTRIBUTE9
, LOC.HOUSE_NUMBER HOUSE_NUMBER
, LOC.LAST_UPDATE_DATE LAST_UPDATE_DATE
, LOC.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
, LOC.LAST_UPDATED_BY LAST_UPDATED_BY
, LOC.LIFE_CYCLE_STATUS LIFE_CYCLE_STATUS
, LOC.LOC_HIERARCHY_ID LOC_HIERARCHY_ID
, LOC.LOCATION_DIRECTIONS LOCATION_DIRECTIONS
, LOC.OBJECT_VERSION_NUMBER OBJECT_VERSION_NUMBER
, LOC.ORIG_SYSTEM_REFERENCE ORIG_SYSTEM_REFERENCE
, LOC.OVERSEAS_ADDRESS_FLAG OVERSEAS_ADDRESS_FLAG
, LOC.PO_BOX_NUMBER PO_BOX_NUMBER
, LOC.POSITION POSITION
, LOC.POST_OFFICE POST_OFFICE
, LOC.POSTAL_PLUS4_CODE POSTAL_PLUS4_CODE
, LOC.PROGRAM_APPLICATION_ID PROGRAM_APPLICATION_ID
, LOC.PROGRAM_ID PROGRAM_ID
, LOC.PROGRAM_UPDATE_DATE PROGRAM_UPDATE_DATE
, LOC.REQUEST_ID REQUEST_ID
, LOC.ROOM ROOM
, LOC.RURAL_ROUTE_NUMBER RURAL_ROUTE_NUMBER
, LOC.RURAL_ROUTE_TYPE RURAL_ROUTE_TYPE
, LOC.SALES_TAX_GEOCODE SALES_TAX_GEOCODE
, LOC.SALES_TAX_INSIDE_CITY_LIMITS SALES_TAX_INSIDE_CITY_LIMITS
, LOC.SECONDARY_SUFFIX_ELEMENT SECONDARY_SUFFIX_ELEMENT
, LOC.SHORT_DESCRIPTION SHORT_DESCRIPTION
, LOC.STREET STREET
, LOC.STREET_NUMBER STREET_NUMBER
, LOC.STREET_SUFFIX STREET_SUFFIX
, LOC.SUITE SUITE
, TIME. TIMEZONE_CODE TIME_ZONE
, LOC.TRAILING_DIRECTORY_CODE TRAILING_DIRECTORY_CODE
, LOC.VALIDATED_FLAG VALIDATED_FLAG
, LOC.WH_UPDATE_DATE WH_UPDATE_DATE
, FTT.TERRITORY_SHORT_NAME TERRITORY_SHORT_NAME
, HCP2.EMAIL_ADDRESS EMAIL_ADDRESS
, CONT_POINT.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, CONT_POINT.PHONE_AREA_CODE PHONE_AREA_CODE
, CONT_POINT.PHONE_NUMBER PHONE_NUMBER
, CONT_POINT.PHONE_EXTENSION PHONE_EXTENSION
, NVL(CONT_POINT.PHONE_COUNTRY_CODE
, ' ')||'-'|| NVL(CONT_POINT.PHONE_AREA_CODE
, ' ')||'-'|| CONT_POINT.PHONE_NUMBER ||' '|| CONT_POINT.PHONE_EXTENSION FULL_PHONE_NUMBER
, CONT_POINT.PHONE_AREA_CODE||CONT_POINT.PHONE_NUMBER PHONE_AREA_NUMBER
, PARTY.STATUS STATUS
, ALK.MEANING STATUS_MEAN
, PARTY.URL URL
, PARTY.EMPLOYEES_TOTAL NUMBER_OF_EMPLOYEE
, ALK1.MEANING CATEGORY_CODE
, PARTY.JGZZ_FISCAL_CODE TAX_ID
, PARTY.CURR_FY_POTENTIAL_REVENUE CURRENT_FY_REVENUE
, PARTY.DUNS_NUMBER DUNS_NUMBER
, PARTY.FISCAL_YEAREND_MONTH FISCAL_YEAREND_MONTH
, PARTY.SIC_CODE SIC
, PARTY.TAX_REFERENCE TAX_REFERENCE
, PARTY.ATTRIBUTE1 ATTRIBUTE1
, PARTY.ATTRIBUTE2 ATTRIBUTE2
, PARTY.ATTRIBUTE3 ATTRIBUTE3
, PARTY.ATTRIBUTE4 ATTRIBUTE4
, PARTY.ATTRIBUTE5 ATTRIBUTE5
, PARTY.ATTRIBUTE6 ATTRIBUTE6
, PARTY.ATTRIBUTE7 ATTRIBUTE7
, PARTY.ATTRIBUTE8 ATTRIBUTE8
, PARTY.ATTRIBUTE9 ATTRIBUTE9
, PARTY.ATTRIBUTE10 ATTRIBUTE10
, PARTY.ATTRIBUTE11 ATTRIBUTE11
, PARTY.ATTRIBUTE12 ATTRIBUTE12
, PARTY.ATTRIBUTE13 ATTRIBUTE13
, PARTY.ATTRIBUTE14 ATTRIBUTE14
, PARTY.ATTRIBUTE15 ATTRIBUTE15
, PARTY.ATTRIBUTE16 ATTRIBUTE16
, PARTY.ATTRIBUTE17 ATTRIBUTE17
, PARTY.ATTRIBUTE18 ATTRIBUTE18
, PARTY.ATTRIBUTE19 ATTRIBUTE19
, PARTY.ATTRIBUTE20 ATTRIBUTE20
FROM HZ_PARTIES PARTY
, HZ_CONTACT_POINTS CONT_POINT
, HZ_CONTACT_POINTS HCP2
, FND_TERRITORIES_TL FTT
, AR_LOOKUPS ALK
, AR_LOOKUPS ALK1
, HZ_CUST_ACCOUNTS ACCOUNT
, HZ_PARTY_SITES SITE
, HZ_LOCATIONS LOC
, FND_TIMEZONES_TL TIME
, FND_TIMEZONES_B TIME_B
WHERE SITE.LOCATION_ID = LOC.LOCATION_ID(+)
AND TIME_B.UPGRADE_TZ_ID(+)= LOC.TIMEZONE_ID
AND TIME_B.TIMEZONE_CODE = TIME.TIMEZONE_CODE(+)
AND TIME.LANGUAGE(+) = USERENV('LANG')
AND PARTY.PARTY_ID = SITE.PARTY_ID(+)
AND SITE.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND PARTY.PARTY_TYPE = 'ORGANIZATION'
AND CONT_POINT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONT_POINT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONT_POINT.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND CONT_POINT.PRIMARY_FLAG(+) = 'Y'
AND CONT_POINT.STATUS (+) = 'A'
AND HCP2.CONTACT_POINT_TYPE(+) = 'EMAIL'
AND HCP2.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HCP2.OWNER_TABLE_ID(+) = PARTY.PARTY_ID
AND LOC.COUNTRY = FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+) = USERENV('LANG')
AND FTT.SOURCE_LANG(+) = USERENV('LANG')
AND ALK.LOOKUP_TYPE (+) = 'CODE_STATUS'
AND ALK.LOOKUP_CODE (+) = PARTY.STATUS
AND ACCOUNT.PARTY_ID(+) = PARTY.PARTY_ID
AND ALK1.LOOKUP_TYPE (+) = 'CUSTOMER_CATEGORY'
AND ALK1.ENABLED_FLAG (+) = 'Y'
AND ALK1.LOOKUP_CODE (+) = PARTY.CATEGORY_CODE