ARB_USER_ACCT_SITES_V
Details
-
Schema: FUSION
-
Object owner: ARB
-
Object type: VIEW
Columns
Name |
---|
USER_ID USER_GUID USER_NAME PARTY_ID CUSTOMER_ID SET_ID CUSTOMER_SITE_USE_ID |
Query
SQL_Statement |
---|
/*SELECT distinct usr.user_id, usr.user_guid, usr.username user_name, party.party_id, accts.cust_account_id CUSTOMER_ID, site_use.set_id, site_use.site_use_id CUSTOMER_SITE_USE_ID FROM hz_parties party, hz_cust_accounts accts, per_users usr, hz_cust_acct_sites_all site, hz_cust_site_uses_all site_use WHERE party.party_id = accts.party_id AND accts.status = 'A' AND Party.party_type = 'PERSON' AND Party.status = 'A' AND accts.cust_account_id = site.cust_account_id AND site.cust_acct_site_id = site_use.cust_acct_site_id AND site_use.site_use_code = 'BILL_TO' AND site_use.status = 'A' AND party.party_id = usr.party_id AND TRUNC(sysdate) BETWEEN TRUNC(site_use.start_date) AND TRUNC(site_use.end_date)*/ /*UNION SELECT distinct usr.user_id, usr.user_guid, usr.username user_name, party.party_id, Role.cust_account_id CUSTOMER_ID, site_use.set_id, site_use.site_use_id CUSTOMER_SITE_USE_ID FROM hz_cust_account_roles Role, hz_role_responsibility Resp, hz_relationships Rel, per_users usr, hz_cust_acct_sites_all site, hz_cust_site_uses_all site_use, hz_parties party WHERE Role.status = 'A' AND Resp.cust_account_role_id = Role.cust_account_role_id AND Resp.responsibility_type = 'ORA_SELF_SERVICE_USER' AND Rel.relationship_id = Role.relationship_id AND Rel.subject_type = 'PERSON' AND (Rel.end_date is null OR Rel.end_date > sysdate) AND Rel.status = 'A' AND Resp.status_flag = 'A' AND Rel.subject_table_name = 'HZ_PARTIES' AND party.party_id = Rel.subject_id AND Role.cust_account_id = site.cust_account_id AND site.cust_acct_site_id = site_use.cust_acct_site_id AND nvl(Role.cust_acct_site_id, site_use.cust_acct_site_id) = site_use.cust_acct_site_id AND site_use.site_use_code = 'BILL_TO' AND site_use.status = 'A' AND party.party_id = usr.party_id AND TRUNC(sysdate) BETWEEN TRUNC(site_use.start_date) AND TRUNC(site_use.end_date)*/ /*UNION*/ SELECT distinct usr.user_id, usr.user_guid, usr.username user_name, party.party_id, Role.cust_account_id CUSTOMER_ID, -1 set_id, -1 CUSTOMER_SITE_USE_ID FROM hz_cust_account_roles Role, hz_role_responsibility Resp, hz_relationships Rel, per_users usr, hz_parties party WHERE Role.status = 'A' AND Role.role_type = 'CONTACT' AND Resp.cust_account_role_id = Role.cust_account_role_id AND Resp.responsibility_type = 'ORA_SELF_SERVICE_USER' AND Rel.relationship_id = Role.relationship_id AND Rel.subject_type = 'PERSON' AND (Rel.end_date is null OR Rel.end_date > sysdate) AND Rel.status = 'A' AND Resp.status_flag = 'A' AND Rel.subject_table_name = 'HZ_PARTIES' AND party.party_id = Rel.subject_id AND party.party_id = usr.party_id AND Role.cust_acct_site_id IS NULL |