ARB_USER_ACCT_SITES

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

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

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