VIEW: APPS.CS_BILL_TO_SHIP_TO_PARTY_V

Source

View Text - Preformatted

SELECT 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 ( 'US' , rel.address1 , rel.address2 , rel.address3 , rel.address4 , rel.city , rel.county , rel.state , rel.province , rel.postal_code , ftv.territory_short_name , rel.country ) , 1 , 2000 ) address , party_rel.object_id object_id FROM hz_relationships party_rel, hz_parties rel, hz_parties sub, 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 ftv.territory_code(+) = rel.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 ( 'US', sub.address1 , sub.address2 , sub.address3 , sub.address4 , sub.city , sub.county , sub.state , sub.province , sub.postal_code , ftv.territory_short_name , sub.country ) , 1 , 2000 ) address , party_rel.object_id object_id FROM hz_relationships party_rel, hz_parties sub, fnd_territories_vl ftv WHERE party_rel.subject_id = sub.party_id 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 ftv.territory_code(+) = sub.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 ( 'US' , p.address1 , p.address2 , p.address3 , p.address4 , p.city , p.county , p.state , p.province , p.postal_code , ftv.territory_short_name , p.country ) , 1 , 2000 ) address , p.party_id object_id FROM hz_parties p, fnd_territories_vl ftv WHERE ftv.territory_code(+) = p.country

View Text - HTML Formatted

SELECT 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 ( 'US'
, REL.ADDRESS1
, REL.ADDRESS2
, REL.ADDRESS3
, REL.ADDRESS4
, REL.CITY
, REL.COUNTY
, REL.STATE
, REL.PROVINCE
, REL.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, REL.COUNTRY )
, 1
, 2000 ) ADDRESS
, PARTY_REL.OBJECT_ID OBJECT_ID
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_PARTIES REL
, HZ_PARTIES SUB
, 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 FTV.TERRITORY_CODE(+) = REL.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 ( 'US'
, SUB.ADDRESS1
, SUB.ADDRESS2
, SUB.ADDRESS3
, SUB.ADDRESS4
, SUB.CITY
, SUB.COUNTY
, SUB.STATE
, SUB.PROVINCE
, SUB.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, SUB.COUNTRY )
, 1
, 2000 ) ADDRESS
, PARTY_REL.OBJECT_ID OBJECT_ID
FROM HZ_RELATIONSHIPS PARTY_REL
, HZ_PARTIES SUB
, FND_TERRITORIES_VL FTV
WHERE PARTY_REL.SUBJECT_ID = SUB.PARTY_ID
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 FTV.TERRITORY_CODE(+) = SUB.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 ( 'US'
, P.ADDRESS1
, P.ADDRESS2
, P.ADDRESS3
, P.ADDRESS4
, P.CITY
, P.COUNTY
, P.STATE
, P.PROVINCE
, P.POSTAL_CODE
, FTV.TERRITORY_SHORT_NAME
, P.COUNTRY )
, 1
, 2000 ) ADDRESS
, P.PARTY_ID OBJECT_ID
FROM HZ_PARTIES P
, FND_TERRITORIES_VL FTV
WHERE FTV.TERRITORY_CODE(+) = P.COUNTRY