VIEW: APPS.WSH_ITM_PARTY_NAMES_V

Source

View Text - Preformatted

SELECT DISTINCT hu.organization_id source_ID, 'SHIP_FROM' source_Type, hu.name party_Name, NULL party_Number from hr_all_organization_units hu, hr_locations hl, wsh_itm_parties wip where wip.party_type = 'SHIP_FROM' and hl.location_id = hu.location_id and hu.organization_id = wip.source_org_id UNION SELECT DISTINCT cust_acct.cust_account_id source_ID, 'SOLD_TO' soruce_Type, party.party_name party_Name, party.party_number party_Number FROM hz_parties party, hz_cust_accounts cust_acct, hz_locations loc, hz_party_sites party_site, wsh_itm_parties wip WHERE party.party_id = cust_acct.party_id AND wip.party_type = 'SOLD_TO' AND cust_acct.cust_account_id = wip.source_org_id AND party_site.party_id = party.party_id AND loc.location_id = party_site.location_id UNION SELECT DISTINCT site_uses.site_use_id source_ID, wip.party_type source_Type, party.party_name party_Name, party_site.party_site_number party_Number FROM hz_cust_site_uses_all site_uses, hz_cust_acct_sites_all acct_site, hz_party_sites party_site, hz_parties party, hz_locations loc, wsh_itm_parties wip WHERE site_uses.cust_acct_site_id = acct_site.cust_acct_site_id AND acct_site.party_site_id = party_site.party_site_id AND party_site.party_id = party.party_id AND loc.location_id = party_site.location_id AND wip.party_type in ('BILL_TO','SHIP_TO','DELIVER_TO','INTERMED_SHIP_TO') AND site_uses.site_use_code = wip.party_type AND site_uses.site_use_id = wip.source_org_id

View Text - HTML Formatted

SELECT DISTINCT HU.ORGANIZATION_ID SOURCE_ID
, 'SHIP_FROM' SOURCE_TYPE
, HU.NAME PARTY_NAME
, NULL PARTY_NUMBER
FROM HR_ALL_ORGANIZATION_UNITS HU
, HR_LOCATIONS HL
, WSH_ITM_PARTIES WIP
WHERE WIP.PARTY_TYPE = 'SHIP_FROM'
AND HL.LOCATION_ID = HU.LOCATION_ID
AND HU.ORGANIZATION_ID = WIP.SOURCE_ORG_ID UNION SELECT DISTINCT CUST_ACCT.CUST_ACCOUNT_ID SOURCE_ID
, 'SOLD_TO' SORUCE_TYPE
, PARTY.PARTY_NAME PARTY_NAME
, PARTY.PARTY_NUMBER PARTY_NUMBER
FROM HZ_PARTIES PARTY
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_LOCATIONS LOC
, HZ_PARTY_SITES PARTY_SITE
, WSH_ITM_PARTIES WIP
WHERE PARTY.PARTY_ID = CUST_ACCT.PARTY_ID
AND WIP.PARTY_TYPE = 'SOLD_TO'
AND CUST_ACCT.CUST_ACCOUNT_ID = WIP.SOURCE_ORG_ID
AND PARTY_SITE.PARTY_ID = PARTY.PARTY_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID UNION SELECT DISTINCT SITE_USES.SITE_USE_ID SOURCE_ID
, WIP.PARTY_TYPE SOURCE_TYPE
, PARTY.PARTY_NAME PARTY_NAME
, PARTY_SITE.PARTY_SITE_NUMBER PARTY_NUMBER
FROM HZ_CUST_SITE_USES_ALL SITE_USES
, HZ_CUST_ACCT_SITES_ALL ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_PARTIES PARTY
, HZ_LOCATIONS LOC
, WSH_ITM_PARTIES WIP
WHERE SITE_USES.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND PARTY_SITE.PARTY_ID = PARTY.PARTY_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND WIP.PARTY_TYPE IN ('BILL_TO'
, 'SHIP_TO'
, 'DELIVER_TO'
, 'INTERMED_SHIP_TO')
AND SITE_USES.SITE_USE_CODE = WIP.PARTY_TYPE
AND SITE_USES.SITE_USE_ID = WIP.SOURCE_ORG_ID