VIEW: APPS.CS_BILL_TO_SHIP_TO_PARTY_V

Source

View Text - Preformatted

SELECT Decode (rel.party_type, 'PERSON', rel.person_first_name|| ' '|| rel.person_last_name, rel.party_name) party_name, rel.party_number party_number, party_rel.relationship_code party_relationship_type, rel.party_id party_id, substr(cs_std.format_address(hl.address_style, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.county, hl.state, hl.province, hl.postal_code, ftv.territory_short_name, hl.country), 1, 2000) address, party_rel.object_id object_id FROM hz_relationships party_rel, hz_parties rel, hz_parties sub, hz_party_sites hps, hz_locations hl, fnd_territories_vl ftv WHERE party_rel.party_id = rel.party_id AND rel.party_type = 'PARTY_RELATIONSHIP' AND party_rel.subject_id = sub.party_id AND sub.party_type IN ('ORGANIZATION', 'PERSON') AND rel.status = 'A' AND sub.status = 'A' AND party_rel.status = 'A' AND nvl(party_rel.start_date, SYSDATE) <= SYSDATE AND nvl(party_rel.end_date, SYSDATE) >= SYSDATE AND hps.party_id(+) = rel.party_id AND hps.identifying_address_flag (+) = 'Y' AND hl.location_id(+) = hps.location_id AND ftv.territory_code(+) = hl.country UNION SELECT decode(sub.party_type, 'PERSON', sub.person_first_name|| ' ' || sub.person_last_name, sub.party_name) party_name, sub.party_number party_number, party_rel.relationship_code party_relationship_type, sub.party_id party_id, substr(cs_std.format_address(hl.address_style, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.county, hl.state, hl.province, hl.postal_code, ftv.territory_short_name, hl.country), 1, 2000) address, party_rel.object_id object_id FROM hz_relationships party_rel, hz_parties sub, hz_party_sites hps, hz_locations hl, fnd_territories_vl ftv WHERE party_rel.subject_id = sub.party_id AND sub.party_type IN ('ORGANIZATION', 'PERSON') AND party_rel.status = 'A' AND sub.status = 'A' AND nvl(party_rel.start_date, SYSDATE) <= SYSDATE AND nvl(party_rel.end_date, SYSDATE) >= SYSDATE AND hps.party_id(+) = sub.party_id AND hps.identifying_address_flag(+) = 'Y' AND hl.location_id(+) = hps.location_id AND ftv.territory_code(+) = hl.country UNION SELECT decode(p.party_type, 'PERSON', p.person_first_name|| ' ' || p.person_last_name, p.party_name) party_name, p.party_number party_number, NULL party_relationship_type, p.party_id party_id, substr(cs_std.format_address(hl.address_style, hl.address1, hl.address2, hl.address3, hl.address4, hl.city, hl.county, hl.state, hl.province, hl.postal_code, ftv.territory_short_name, hl.country), 1, 2000) address, p.party_id object_id FROM hz_parties p, hz_party_sites hps, hz_locations hl, fnd_territories_vl ftv WHERE hps.party_id(+) = p.party_id AND hps.identifying_address_flag (+) = 'Y' AND hl.location_id(+) = hps.location_id AND ftv.territory_code(+) = hl.country

View Text - HTML Formatted

SELECT DECODE (REL.PARTY_TYPE
, 'PERSON'
, REL.PERSON_FIRST_NAME|| ' '|| REL.PERSON_LAST_NAME
, REL.PARTY_NAME) PARTY_NAME
, REL.PARTY_NUMBER PARTY_NUMBER
, PARTY_REL.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE
, REL.PARTY_ID PARTY_ID
, SUBSTR(CS_STD.FORMAT_ADDRESS(HL.ADDRESS_STYLE
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.COUNTY
, HL.STATE
, HL.PROVINCE
, HL.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, HL.COUNTRY)
, 1
, 2000) ADDRESS
, PARTY_REL.OBJECT_ID OBJECT_ID
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_PARTIES REL
, HZ_PARTIES SUB
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, FND_TERRITORIES_VL FTV
WHERE PARTY_REL.PARTY_ID = REL.PARTY_ID
AND REL.PARTY_TYPE = 'PARTY_RELATIONSHIP'
AND PARTY_REL.SUBJECT_ID = SUB.PARTY_ID
AND SUB.PARTY_TYPE IN ('ORGANIZATION'
, 'PERSON')
AND REL.STATUS = 'A'
AND SUB.STATUS = 'A'
AND PARTY_REL.STATUS = 'A'
AND NVL(PARTY_REL.START_DATE
, SYSDATE) <= SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE) >= SYSDATE
AND HPS.PARTY_ID(+) = REL.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG (+) = 'Y'
AND HL.LOCATION_ID(+) = HPS.LOCATION_ID
AND FTV.TERRITORY_CODE(+) = HL.COUNTRY UNION SELECT DECODE(SUB.PARTY_TYPE
, 'PERSON'
, SUB.PERSON_FIRST_NAME|| ' ' || SUB.PERSON_LAST_NAME
, SUB.PARTY_NAME) PARTY_NAME
, SUB.PARTY_NUMBER PARTY_NUMBER
, PARTY_REL.RELATIONSHIP_CODE PARTY_RELATIONSHIP_TYPE
, SUB.PARTY_ID PARTY_ID
, SUBSTR(CS_STD.FORMAT_ADDRESS(HL.ADDRESS_STYLE
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.COUNTY
, HL.STATE
, HL.PROVINCE
, HL.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, HL.COUNTRY)
, 1
, 2000) ADDRESS
, PARTY_REL.OBJECT_ID OBJECT_ID
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_PARTIES SUB
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, FND_TERRITORIES_VL FTV
WHERE PARTY_REL.SUBJECT_ID = SUB.PARTY_ID
AND SUB.PARTY_TYPE IN ('ORGANIZATION'
, 'PERSON')
AND PARTY_REL.STATUS = 'A'
AND SUB.STATUS = 'A'
AND NVL(PARTY_REL.START_DATE
, SYSDATE) <= SYSDATE
AND NVL(PARTY_REL.END_DATE
, SYSDATE) >= SYSDATE
AND HPS.PARTY_ID(+) = SUB.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG(+) = 'Y'
AND HL.LOCATION_ID(+) = HPS.LOCATION_ID
AND FTV.TERRITORY_CODE(+) = HL.COUNTRY UNION SELECT DECODE(P.PARTY_TYPE
, 'PERSON'
, P.PERSON_FIRST_NAME|| ' ' || P.PERSON_LAST_NAME
, P.PARTY_NAME) PARTY_NAME
, P.PARTY_NUMBER PARTY_NUMBER
, NULL PARTY_RELATIONSHIP_TYPE
, P.PARTY_ID PARTY_ID
, SUBSTR(CS_STD.FORMAT_ADDRESS(HL.ADDRESS_STYLE
, HL.ADDRESS1
, HL.ADDRESS2
, HL.ADDRESS3
, HL.ADDRESS4
, HL.CITY
, HL.COUNTY
, HL.STATE
, HL.PROVINCE
, HL.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, HL.COUNTRY)
, 1
, 2000) ADDRESS
, P.PARTY_ID OBJECT_ID
FROM HZ_PARTIES P
, HZ_PARTY_SITES HPS
, HZ_LOCATIONS HL
, FND_TERRITORIES_VL FTV
WHERE HPS.PARTY_ID(+) = P.PARTY_ID
AND HPS.IDENTIFYING_ADDRESS_FLAG (+) = 'Y'
AND HL.LOCATION_ID(+) = HPS.LOCATION_ID
AND FTV.TERRITORY_CODE(+) = HL.COUNTRY