GMS_SPONSOR_ACCTS_V

Details

• Schema: FUSION

• Object owner: GMS

• Object type: VIEW

Columns

Name
CUST_ACCT_ID ACCOUNT_NUMBER PARTY_ID FEDERAL_FLAG PRIMARY_ACCT PRIMARY_LOC_FLAG PRIMARY_LOC_NUMBER SPONSOR_ACCT_DETAILS_ID SPONSOR_ID SPONSOR_ID_DR SPONSOR_ACCT_ID RELATED_SPONSOR_ACCT_ID LOC_FLAG PRIMARY_FLAG LOC_NUMBER VERSION_TYPE

Query

SQL_Statement

SELECT cs_acct.cust_account_id cust_acct_id, cs_acct.account_number, cs_acct.party_id, sp.federal_flag, df.sponsor_acct_id primary_acct, df.loc_flag primary_loc_flag, df.loc_number primary_loc_number, a.sponsor_acct_details_id, cs_acct.cust_account_id sponsor_id, decode(a.sponsor_acct_details_id, null, df.sponsor_id, a.sponsor_id) sponsor_id_dr, decode(a.sponsor_acct_details_id, null, cs_acct.cust_account_id, a.sponsor_acct_id) sponsor_acct_id, a.related_sponsor_acct_id, decode(a.sponsor_acct_details_id, null, df.loc_flag, a.LOC_FLAG) LOC_FLAG, a.primary_flag, decode(a.sponsor_acct_details_id, null, df.loc_number, a.loc_number) LOC_NUMBER, 'C' as version_type FROM (SELECT a.sponsor_acct_details_id, a.sponsor_acct_id, a.sponsor_id, a.loc_flag, a.primary_flag, a.related_sponsor_id, a.related_sponsor_acct_id, nvl(a.loc_number, b.loc_number) loc_number FROM gms_sponsor_acct_details_b a, gms_sponsor_acct_details_b b where a.related_sponsor_acct_id = b.sponsor_acct_id(+) and a.related_sponsor_id = b.sponsor_id(+)) a, HZ_CUST_ACCOUNTS cs_acct, gms_sponsors_b sp, (SELECT a.sponsor_acct_details_id, a.sponsor_acct_id, a.sponsor_id, a.loc_flag, a.primary_flag, a.related_sponsor_id, a.related_sponsor_acct_id, nvl(a.loc_number, b.loc_number) loc_number FROM gms_sponsor_acct_details_b a, gms_sponsor_acct_details_b b where a.related_sponsor_acct_id = b.sponsor_acct_id(+) and a.related_sponsor_id = b.sponsor_id(+)) df WHERE sp.party_id = cs_acct.party_id and cs_acct.cust_account_id = a.sponsor_acct_id(+) and df.sponsor_id = sp.sponsor_id and df.primary_flag = 'Y'