VIEW: APPS.PV_PRTNR_SALES_TEAM_V

Source

View Text - Preformatted

SELECT acc.last_update_Date , hzpp.party_id person_id , acc.team_leader_flag, acc.freeze_flag freeze_flag, hloc.city city , hloc.postal_code postal_code , hloc.state state , hloc.province province , hloc.county county , hloc.country country , hloc.address1 address1 , hloc.address2 address2 , hloc.address3 address3 , hloc.address4 address4, acc.address_id address_id , acc.customer_id customer_id, acc.salesforce_id salesforce_id, hzcp.phone_line_type phone_line_type, hzcp.PHONE_AREA_CODE, hzcp.PHONE_COUNTRY_CODE phone_country_code, hzcp.PHONE_NUMBER, hzcp.PHONE_EXTENSION phone_Extension , acc.access_id access_id , sg.group_name sales_group_name , hz1.person_last_name, hz1.person_middle_name, hz1.person_first_name, fndu.email_address email_address, hzoc.job_title, acc.sales_group_id sales_group_id, acc.lead_id, acc.sales_lead_id, acc.salesforce_role_code salesforce_role_code, acc.salesforce_relationship_code salesforce_relationship_code, hp.party_name , hzpp.object_id party_id , ptorg.party_name, hzoc.job_title_code, hzpp.subject_id person_party_id from as_accesses_all acc , hz_parties hp , hz_relationships hzpp , hz_parties ptorg , hz_org_contacts hzoc , hz_parties hz1 , jtf_rs_groups_vl sg, hz_contact_points hzcp, fnd_user fndu, jtf_rs_resource_extns jtfre, hz_party_sites psite, hz_locations hloc WHERE acc.partner_cont_party_id = hp.party_id and acc.partner_cont_party_id = hzpp.party_id and hzpp.relationship_id = hzoc.party_relationship_id and hzpp.object_id = ptorg.party_id and ptorg.party_type = 'ORGANIZATION' and hzpp.subject_id = hz1.party_id and hz1.party_type = 'PERSON' and acc.salesforce_id = jtfre.resource_id and jtfre.user_id = fndu.user_id and acc.sales_group_id = sg.group_id (+) and hzpp.party_id = hzcp.owner_Table_id(+) and hzcp.owner_table_name(+) = 'HZ_PARTIES' and hzcp.PRIMARY_FLAG(+) = 'Y' and hzcp.CONTACT_POINT_TYPE(+) = 'PHONE' and acc.address_id = psite.party_site_id (+) and psite.location_id = hloc.location_id (+)

View Text - HTML Formatted

SELECT ACC.LAST_UPDATE_DATE
, HZPP.PARTY_ID PERSON_ID
, ACC.TEAM_LEADER_FLAG
, ACC.FREEZE_FLAG FREEZE_FLAG
, HLOC.CITY CITY
, HLOC.POSTAL_CODE POSTAL_CODE
, HLOC.STATE STATE
, HLOC.PROVINCE PROVINCE
, HLOC.COUNTY COUNTY
, HLOC.COUNTRY COUNTRY
, HLOC.ADDRESS1 ADDRESS1
, HLOC.ADDRESS2 ADDRESS2
, HLOC.ADDRESS3 ADDRESS3
, HLOC.ADDRESS4 ADDRESS4
, ACC.ADDRESS_ID ADDRESS_ID
, ACC.CUSTOMER_ID CUSTOMER_ID
, ACC.SALESFORCE_ID SALESFORCE_ID
, HZCP.PHONE_LINE_TYPE PHONE_LINE_TYPE
, HZCP.PHONE_AREA_CODE
, HZCP.PHONE_COUNTRY_CODE PHONE_COUNTRY_CODE
, HZCP.PHONE_NUMBER
, HZCP.PHONE_EXTENSION PHONE_EXTENSION
, ACC.ACCESS_ID ACCESS_ID
, SG.GROUP_NAME SALES_GROUP_NAME
, HZ1.PERSON_LAST_NAME
, HZ1.PERSON_MIDDLE_NAME
, HZ1.PERSON_FIRST_NAME
, FNDU.EMAIL_ADDRESS EMAIL_ADDRESS
, HZOC.JOB_TITLE
, ACC.SALES_GROUP_ID SALES_GROUP_ID
, ACC.LEAD_ID
, ACC.SALES_LEAD_ID
, ACC.SALESFORCE_ROLE_CODE SALESFORCE_ROLE_CODE
, ACC.SALESFORCE_RELATIONSHIP_CODE SALESFORCE_RELATIONSHIP_CODE
, HP.PARTY_NAME
, HZPP.OBJECT_ID PARTY_ID
, PTORG.PARTY_NAME
, HZOC.JOB_TITLE_CODE
, HZPP.SUBJECT_ID PERSON_PARTY_ID
FROM AS_ACCESSES_ALL ACC
, HZ_PARTIES HP
, HZ_RELATIONSHIPS HZPP
, HZ_PARTIES PTORG
, HZ_ORG_CONTACTS HZOC
, HZ_PARTIES HZ1
, JTF_RS_GROUPS_VL SG
, HZ_CONTACT_POINTS HZCP
, FND_USER FNDU
, JTF_RS_RESOURCE_EXTNS JTFRE
, HZ_PARTY_SITES PSITE
, HZ_LOCATIONS HLOC
WHERE ACC.PARTNER_CONT_PARTY_ID = HP.PARTY_ID
AND ACC.PARTNER_CONT_PARTY_ID = HZPP.PARTY_ID
AND HZPP.RELATIONSHIP_ID = HZOC.PARTY_RELATIONSHIP_ID
AND HZPP.OBJECT_ID = PTORG.PARTY_ID
AND PTORG.PARTY_TYPE = 'ORGANIZATION'
AND HZPP.SUBJECT_ID = HZ1.PARTY_ID
AND HZ1.PARTY_TYPE = 'PERSON'
AND ACC.SALESFORCE_ID = JTFRE.RESOURCE_ID
AND JTFRE.USER_ID = FNDU.USER_ID
AND ACC.SALES_GROUP_ID = SG.GROUP_ID (+)
AND HZPP.PARTY_ID = HZCP.OWNER_TABLE_ID(+)
AND HZCP.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND HZCP.PRIMARY_FLAG(+) = 'Y'
AND HZCP.CONTACT_POINT_TYPE(+) = 'PHONE'
AND ACC.ADDRESS_ID = PSITE.PARTY_SITE_ID (+)
AND PSITE.LOCATION_ID = HLOC.LOCATION_ID (+)