VIEW: APPS.CSP_RS_ADDRESS_UI_V

Source

View Text - Preformatted

SELECT rcr.resource_type, rcr.resource_id, pla.location_id hr_loc_id, hzl.location_id hz_loc_id, hzl.time_zone, htv.name, 'N' timezone_modified, (SELECT inner_cus.primary_flag FROM hz_cust_site_uses inner_cus, po_location_associations_all inner_pla WHERE inner_cus.site_use_id = inner_pla.site_use_id AND inner_pla.location_id = pla.location_id AND inner_cus.site_use_code = 'SHIP_TO' ) primary_flag, 'N' primary_flag_modified, (SELECT DECODE(inner_cus.status, 'I', 'Y' , 'N') FROM hz_cust_site_uses inner_cus, po_location_associations_all inner_pla WHERE inner_cus.site_use_id = inner_pla.site_use_id AND inner_pla.location_id = pla.location_id AND inner_cus.site_use_code = 'SHIP_TO' ) inactive_flag, 'N' inactive_modified, DECODE(NVL(rcr.default_trip_start, -999), hzl.location_id, 'Y', 'N') default_trip_flag, 'N' default_trip_modified, hra.address_line_1, hra.address_line_2, hra.address_line_3, hra.town_or_city, hra.postal_code, hra.country, ter.territory_short_name country_name, hra.region_1, pla.org_id FROM csp_rs_cust_relations rcr, hz_cust_acct_sites cac, hz_cust_site_uses cus, po_location_associations pla, hr_locations_all hra, hz_party_sites ps, hz_locations hzl, hz_timezones_vl htv, FND_TERRITORIES_VL ter WHERE rcr.customer_id = cac.cust_account_id AND cac.cust_acct_site_id = cus.cust_acct_site_id AND cus.site_use_code = 'SHIP_TO' AND cus.site_use_id = pla.site_use_id AND pla.location_id = hra.location_id AND cac.party_site_id = ps.party_site_id AND hzl.location_id = ps.location_id AND hzl.time_zone = htv.timezone_id(+) AND ter.TERRITORY_CODE = hra.country

View Text - HTML Formatted

SELECT RCR.RESOURCE_TYPE
, RCR.RESOURCE_ID
, PLA.LOCATION_ID HR_LOC_ID
, HZL.LOCATION_ID HZ_LOC_ID
, HZL.TIME_ZONE
, HTV.NAME
, 'N' TIMEZONE_MODIFIED
, (SELECT INNER_CUS.PRIMARY_FLAG
FROM HZ_CUST_SITE_USES INNER_CUS
, PO_LOCATION_ASSOCIATIONS_ALL INNER_PLA
WHERE INNER_CUS.SITE_USE_ID = INNER_PLA.SITE_USE_ID
AND INNER_PLA.LOCATION_ID = PLA.LOCATION_ID
AND INNER_CUS.SITE_USE_CODE = 'SHIP_TO' ) PRIMARY_FLAG
, 'N' PRIMARY_FLAG_MODIFIED
, (SELECT DECODE(INNER_CUS.STATUS
, 'I'
, 'Y'
, 'N')
FROM HZ_CUST_SITE_USES INNER_CUS
, PO_LOCATION_ASSOCIATIONS_ALL INNER_PLA
WHERE INNER_CUS.SITE_USE_ID = INNER_PLA.SITE_USE_ID
AND INNER_PLA.LOCATION_ID = PLA.LOCATION_ID
AND INNER_CUS.SITE_USE_CODE = 'SHIP_TO' ) INACTIVE_FLAG
, 'N' INACTIVE_MODIFIED
, DECODE(NVL(RCR.DEFAULT_TRIP_START
, -999)
, HZL.LOCATION_ID
, 'Y'
, 'N') DEFAULT_TRIP_FLAG
, 'N' DEFAULT_TRIP_MODIFIED
, HRA.ADDRESS_LINE_1
, HRA.ADDRESS_LINE_2
, HRA.ADDRESS_LINE_3
, HRA.TOWN_OR_CITY
, HRA.POSTAL_CODE
, HRA.COUNTRY
, TER.TERRITORY_SHORT_NAME COUNTRY_NAME
, HRA.REGION_1
, PLA.ORG_ID
FROM CSP_RS_CUST_RELATIONS RCR
, HZ_CUST_ACCT_SITES CAC
, HZ_CUST_SITE_USES CUS
, PO_LOCATION_ASSOCIATIONS PLA
, HR_LOCATIONS_ALL HRA
, HZ_PARTY_SITES PS
, HZ_LOCATIONS HZL
, HZ_TIMEZONES_VL HTV
, FND_TERRITORIES_VL TER
WHERE RCR.CUSTOMER_ID = CAC.CUST_ACCOUNT_ID
AND CAC.CUST_ACCT_SITE_ID = CUS.CUST_ACCT_SITE_ID
AND CUS.SITE_USE_CODE = 'SHIP_TO'
AND CUS.SITE_USE_ID = PLA.SITE_USE_ID
AND PLA.LOCATION_ID = HRA.LOCATION_ID
AND CAC.PARTY_SITE_ID = PS.PARTY_SITE_ID
AND HZL.LOCATION_ID = PS.LOCATION_ID
AND HZL.TIME_ZONE = HTV.TIMEZONE_ID(+)
AND TER.TERRITORY_CODE = HRA.COUNTRY