[Home] [Help]
872: select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
873: row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
874: decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
875: from hz_cust_account_roles SUB,
876: hz_role_responsibility SROLES
877: where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
878: SUB.status = 'A' AND
879: SUB.CUST_ACCOUNT_ID = p_customer_id
880: AND ( SUB.CUST_ACCT_SITE_ID = p_customer_site_use_id)
888: select SUB.cust_account_role_id contact_id, SUB.CUST_ACCT_SITE_ID , SROLES.responsibility_type ,SROLES.PRIMARY_FLAG ,
889: row_number() OVER ( partition by SROLES.responsibility_type , SUB.CUST_ACCT_SITE_ID order by SROLES.PRIMARY_FLAG DESC NULLS LAST, SUB.last_update_date desc) last_update_record,
890: decode(SROLES.responsibility_type,p_contact_role_type,111,999) resp_code
891: from hz_cust_account_roles SUB,
892: hz_role_responsibility SROLES
893: where SUB.cust_account_role_id = SROLES.CUST_ACCOUNT_ROLE_ID AND
894: SUB.status = 'A' AND
895: SUB.CUST_ACCOUNT_ID = p_customer_id
896: AND (SUB.CUST_ACCT_SITE_ID IS NULL)
2248: p_customer_acct_site_id IN NUMBER ) IS
2249: SELECT hcar.CUST_ACCOUNT_ROLE_ID as contact_id
2250: FROM HZ_CUST_ACCOUNT_ROLES hcar, HZ_PARTIES hpsub, HZ_PARTIES hprel,
2251: HZ_ORG_CONTACTS hoc, HZ_RELATIONSHIPS hr, HZ_PARTY_SITES hps, FND_TERRITORIES_VL ftv,
2252: fnd_lookup_values_vl lookups,hz_role_responsibility hrr
2253: WHERE hrr.responsibility_type = 'SELF_SERVICE_USER'
2254: and hrr.cust_account_role_id = hcar.cust_account_role_id
2255: and hcar.CUST_ACCOUNT_ID = p_customer_id
2256: AND hcar.ROLE_TYPE = 'CONTACT'