eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.AMS_PARTIES_V

Source

View Text - Preformatted


SELECT prsn.party_id ,prsn.party_id ,prsn.party_name ,prsn.person_last_name , prsn.person_first_name ,prsn.person_middle_name ,prsn.known_as ,prsn.status , cpph.phone_country_code || decode(cpph.phone_area_code, null, '', ' '||cpph.phone_area_code) || decode(cpph.phone_number, null, '', ' '||cpph.phone_number) || decode(cpph.phone_extension, null, '', ' ext. '||cpph.phone_extension) ,cpph.phone_country_code ,cpph.phone_area_code , cpph.phone_number ,cpph.phone_extension ,cpph.phone_line_type ,prsn.email_address , prsn.address1 || decode(prsn.address2, null, '', ' '||prsn.address2) || decode(prsn.address3, null, '', ' '||prsn.address3) || decode(prsn.address4, null, '', ' '||prsn.address4) || ', ' ||prsn.city ||', '||prsn.state||', '||prsn.postal_code||', '||prsn.country ,prsn.address1 || decode(prsn.address2, null, '', ' '||prsn.address2) || decode(prsn.address3, null, '', ' '|| prsn.address3) || decode(prsn.address4, null, '', ' '||prsn.address4) ,prsn.address1 , prsn.address2 ,prsn.address3 ,prsn.address4 ,prsn.state ,prsn.city ,prsn.province , prsn.county ,prsn.postal_code ,prsn.country , prsn.last_ordered_date ,0 ,'' ,'' , 0 ,'' , prsn.creation_date , prsn.last_update_date ,prsn.created_by ,prsn.last_updated_by from hz_parties prsn ,hz_contact_points cpph WHERE prsn.party_type = 'PERSON' and prsn.status = 'A' and prsn.party_id = cpph.owner_table_id (+) and cpph.owner_table_name (+) = 'HZ_PARTIES' and cpph.contact_point_type (+) = 'PHONE' and cpph.primary_flag (+) = 'Y' union SELECT prsn.party_id ,pr.party_id ,prsn.party_name ,prsn.person_last_name , prsn.person_first_name ,prsn.person_middle_name ,prsn.known_as ,pr.status , cpph.phone_country_code || DECODE(cpph.phone_area_code, NULL, '', ' '||cpph.phone_area_code) || DECODE(cpph.phone_number, NULL, '', ' '||cpph.phone_number) || DECODE(cpph.phone_extension, NULL, '', ' ext. '||cpph.phone_extension) , cpph.phone_country_code ,cpph.phone_area_code ,cpph.phone_number ,cpph.phone_extension , cpph.phone_line_type ,relparty.email_address ,relparty.address1 || DECODE(relparty.address2, NULL, '', ' '||relparty.address2) || DECODE(relparty.address3, NULL, '', ' '||relparty.address3) || DECODE(relparty.address4, NULL, '', ' '||relparty.address4) || ', '||relparty.city || ', '||relparty.state||', '||relparty.postal_code||', '||relparty.country ,relparty.address1 || DECODE(relparty.address2, NULL, '', ' '||relparty.address2) || DECODE(relparty.address3, NULL, '', ' '||relparty.address3) || DECODE(relparty.address4, NULL, '', ' '||relparty.address4) ,relparty.address1 ,relparty.address2 ,relparty.address3 ,relparty.address4 , relparty.state ,relparty.city ,relparty.province ,relparty.county ,relparty.postal_code , relparty.country , prsn.last_ordered_date ,pr.object_id ,party.party_type ,party.party_name , pr.relationship_id ,pr.relationship_type , prsn.creation_date ,prsn.last_update_date ,prsn.created_by ,prsn.last_updated_by FROM hz_parties prsn ,hz_parties party ,hz_parties relparty ,hz_contact_points cpph ,hz_org_contacts c ,hz_relationships pr WHERE prsn.party_type = 'PERSON' AND pr.status='A' AND prsn.status='A' AND party.status='A' AND prsn.party_id = pr.subject_id AND pr.object_id = party.party_id AND pr.party_id = relparty.party_id AND pr.party_id = cpph.owner_table_id (+) AND cpph.owner_table_name (+) = 'HZ_PARTIES' AND cpph.contact_point_type (+) = 'PHONE' AND cpph.primary_flag (+) = 'Y' AND pr.object_type='ORGANIZATION' AND pr.relationship_code IN('CONTACT','CONTACT_OF' ,'EMPLOYEE_OF') AND pr.directional_flag='F' AND c.party_relationship_id=pr.relationship_id AND trunc(pr.start_date)<=trunc(sysdate) AND trunc(NVL(pr.end_date,SYSDATE))>=trunc(sysdate)

View Text - HTML Formatted

SELECT PRSN.PARTY_ID
, PRSN.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PRSN.STATUS
, CPPH.PHONE_COUNTRY_CODE || DECODE(CPPH.PHONE_AREA_CODE
, NULL
, ''
, ' '||CPPH.PHONE_AREA_CODE) || DECODE(CPPH.PHONE_NUMBER
, NULL
, ''
, ' '||CPPH.PHONE_NUMBER) || DECODE(CPPH.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||CPPH.PHONE_EXTENSION)
, CPPH.PHONE_COUNTRY_CODE
, CPPH.PHONE_AREA_CODE
, CPPH.PHONE_NUMBER
, CPPH.PHONE_EXTENSION
, CPPH.PHONE_LINE_TYPE
, PRSN.EMAIL_ADDRESS
, PRSN.ADDRESS1 || DECODE(PRSN.ADDRESS2
, NULL
, ''
, ' '||PRSN.ADDRESS2) || DECODE(PRSN.ADDRESS3
, NULL
, ''
, ' '||PRSN.ADDRESS3) || DECODE(PRSN.ADDRESS4
, NULL
, ''
, ' '||PRSN.ADDRESS4) || '
, ' ||PRSN.CITY ||'
, '||PRSN.STATE||'
, '||PRSN.POSTAL_CODE||'
, '||PRSN.COUNTRY
, PRSN.ADDRESS1 || DECODE(PRSN.ADDRESS2
, NULL
, ''
, ' '||PRSN.ADDRESS2) || DECODE(PRSN.ADDRESS3
, NULL
, ''
, ' '|| PRSN.ADDRESS3) || DECODE(PRSN.ADDRESS4
, NULL
, ''
, ' '||PRSN.ADDRESS4)
, PRSN.ADDRESS1
, PRSN.ADDRESS2
, PRSN.ADDRESS3
, PRSN.ADDRESS4
, PRSN.STATE
, PRSN.CITY
, PRSN.PROVINCE
, PRSN.COUNTY
, PRSN.POSTAL_CODE
, PRSN.COUNTRY
, PRSN.LAST_ORDERED_DATE
, 0
, ''
, ''
, 0
, ''
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
FROM HZ_PARTIES PRSN
, HZ_CONTACT_POINTS CPPH
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PRSN.STATUS = 'A'
AND PRSN.PARTY_ID = CPPH.OWNER_TABLE_ID (+)
AND CPPH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CPPH.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CPPH.PRIMARY_FLAG (+) = 'Y' UNION SELECT PRSN.PARTY_ID
, PR.PARTY_ID
, PRSN.PARTY_NAME
, PRSN.PERSON_LAST_NAME
, PRSN.PERSON_FIRST_NAME
, PRSN.PERSON_MIDDLE_NAME
, PRSN.KNOWN_AS
, PR.STATUS
, CPPH.PHONE_COUNTRY_CODE || DECODE(CPPH.PHONE_AREA_CODE
, NULL
, ''
, ' '||CPPH.PHONE_AREA_CODE) || DECODE(CPPH.PHONE_NUMBER
, NULL
, ''
, ' '||CPPH.PHONE_NUMBER) || DECODE(CPPH.PHONE_EXTENSION
, NULL
, ''
, ' EXT. '||CPPH.PHONE_EXTENSION)
, CPPH.PHONE_COUNTRY_CODE
, CPPH.PHONE_AREA_CODE
, CPPH.PHONE_NUMBER
, CPPH.PHONE_EXTENSION
, CPPH.PHONE_LINE_TYPE
, RELPARTY.EMAIL_ADDRESS
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4) || '
, '||RELPARTY.CITY || '
, '||RELPARTY.STATE||'
, '||RELPARTY.POSTAL_CODE||'
, '||RELPARTY.COUNTRY
, RELPARTY.ADDRESS1 || DECODE(RELPARTY.ADDRESS2
, NULL
, ''
, ' '||RELPARTY.ADDRESS2) || DECODE(RELPARTY.ADDRESS3
, NULL
, ''
, ' '||RELPARTY.ADDRESS3) || DECODE(RELPARTY.ADDRESS4
, NULL
, ''
, ' '||RELPARTY.ADDRESS4)
, RELPARTY.ADDRESS1
, RELPARTY.ADDRESS2
, RELPARTY.ADDRESS3
, RELPARTY.ADDRESS4
, RELPARTY.STATE
, RELPARTY.CITY
, RELPARTY.PROVINCE
, RELPARTY.COUNTY
, RELPARTY.POSTAL_CODE
, RELPARTY.COUNTRY
, PRSN.LAST_ORDERED_DATE
, PR.OBJECT_ID
, PARTY.PARTY_TYPE
, PARTY.PARTY_NAME
, PR.RELATIONSHIP_ID
, PR.RELATIONSHIP_TYPE
, PRSN.CREATION_DATE
, PRSN.LAST_UPDATE_DATE
, PRSN.CREATED_BY
, PRSN.LAST_UPDATED_BY
FROM HZ_PARTIES PRSN
, HZ_PARTIES PARTY
, HZ_PARTIES RELPARTY
, HZ_CONTACT_POINTS CPPH
, HZ_ORG_CONTACTS C
, HZ_RELATIONSHIPS PR
WHERE PRSN.PARTY_TYPE = 'PERSON'
AND PR.STATUS='A'
AND PRSN.STATUS='A'
AND PARTY.STATUS='A'
AND PRSN.PARTY_ID = PR.SUBJECT_ID
AND PR.OBJECT_ID = PARTY.PARTY_ID
AND PR.PARTY_ID = RELPARTY.PARTY_ID
AND PR.PARTY_ID = CPPH.OWNER_TABLE_ID (+)
AND CPPH.OWNER_TABLE_NAME (+) = 'HZ_PARTIES'
AND CPPH.CONTACT_POINT_TYPE (+) = 'PHONE'
AND CPPH.PRIMARY_FLAG (+) = 'Y'
AND PR.OBJECT_TYPE='ORGANIZATION'
AND PR.RELATIONSHIP_CODE IN('CONTACT'
, 'CONTACT_OF'
, 'EMPLOYEE_OF')
AND PR.DIRECTIONAL_FLAG='F'
AND C.PARTY_RELATIONSHIP_ID=PR.RELATIONSHIP_ID
AND TRUNC(PR.START_DATE)<=TRUNC(SYSDATE)
AND TRUNC(NVL(PR.END_DATE
, SYSDATE))>=TRUNC(SYSDATE)