--------------------------------------------------------
--  File created - Tuesday-April-11-2023   
--------------------------------------------------------
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_ACCOUNT_HIER
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_ACCOUNT_HIER" ("HRCH_CTGY_ID", "HRCH_CTGY_NM", "HRCH_ID", "HRCH_NM", "LVL_MIN_NUM", "LVL_MAX_NUM", "LEAF_ID", "HRCH_1_ID", "HRCH_1_NAME", "HRCH_1_NUMBER", "HRCH_1_DESC", "HRCH_2_ID", "HRCH_2_NAME", "HRCH_2_NUMBER", "HRCH_2_DESC", "HRCH_3_ID", "HRCH_3_NAME", "HRCH_3_NUMBER", "HRCH_3_DESC", "HRCH_4_ID", "HRCH_4_NAME", "HRCH_4_NUMBER", "HRCH_4_DESC", "HRCH_5_ID", "HRCH_5_NAME", "HRCH_5_NUMBER", "HRCH_5_DESC", "HRCH_6_ID", "HRCH_6_NAME", "HRCH_6_NUMBER", "HRCH_6_DESC", "HRCH_7_ID", "HRCH_7_NAME", "HRCH_7_NUMBER", "HRCH_7_DESC", "HRCH_8_ID", "HRCH_8_NAME", "HRCH_8_NUMBER", "HRCH_8_DESC", "HRCH_9_ID", "HRCH_9_NAME", "HRCH_9_NUMBER", "HRCH_9_DESC", "HRCH_10_ID", "HRCH_10_NAME", "HRCH_10_NUMBER", "HRCH_10_DESC", "HRCH_11_ID", "HRCH_11_NAME", "HRCH_11_NUMBER", "HRCH_11_DESC", "HRCH_12_ID", "HRCH_12_NAME", "HRCH_12_NUMBER", "HRCH_12_DESC", "HRCH_13_ID", "HRCH_13_NAME", "HRCH_13_NUMBER", "HRCH_13_DESC", "HRCH_14_ID", "HRCH_14_NAME", "HRCH_14_NUMBER", "HRCH_14_DESC", "HRCH_15_ID", "HRCH_15_NAME", "HRCH_15_NUMBER", "HRCH_15_DESC", "W$_INSERT_DT", "W$_UPDATE_DT", "DATA_SRC_NM") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    --DW_SAR_X_SF_HRCH_SEQ.NEXTVAL HRCH_DIM_ID,
    'ACCT' HRCH_CTGY_id, 
    'Account' HRCH_CTGY_NM, 
    'SFDC' HRCH_id, 
    'Salesforce Account Tree' HRCH_NM, 
    0 LVL_MIN_NUM, 
    15 LVL_MAX_NUM, 
    coalesce(lev15.id,lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) LEAF_ID, 
    lev01.id HRCH_1_id, 
    lev01.NAME HRCH_1_NAME, 
    lev01.ACCOUNTNUMBER HRCH_1_NUMBER, 
    lev01.DESCRIPTION HRCH_1_DESC,
    coalesce(lev02.id,lev01.id) HRCH_2_id , 
    coalesce(lev02.NAME,lev01.NAME) HRCH_2_NAME, 
    coalesce(lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_2_NUMBER, 
    coalesce(lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_2_DESC,
    coalesce(lev03.id,lev02.id,lev01.id) HRCH_3_id , 
    coalesce(lev03.NAME,lev02.NAME,lev01.NAME) HRCH_3_NAME, 
    coalesce(lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_3_NUMBER,
    coalesce(lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_3_DESC,
    coalesce(lev04.id,lev03.id,lev02.id,lev01.id) HRCH_4_id , 
    coalesce(lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_4_NAME, 
    coalesce(lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_4_NUMBER,
    coalesce(lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_4_DESC,
    coalesce(lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_5_id , 
    coalesce(lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_5_NAME, 
    coalesce(lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_5_NUMBER,
    coalesce(lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_5_DESC,
    coalesce(lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_6_id , 
    coalesce(lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_6_NAME, 
    coalesce(lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_6_NUMBER,
    coalesce(lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_6_DESC,
    coalesce(lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_7_id , 
    coalesce(lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_7_NAME, 
    coalesce(lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_7_NUMBER,
    coalesce(lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_7_DESC,
    coalesce(lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_8_id , 
    coalesce(lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_8_NAME, 
    coalesce(lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_8_NUMBER,
    coalesce(lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_8_DESC,
    coalesce(lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_9_id , 
    coalesce(lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_9_NAME, 
    coalesce(lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_9_NUMBER,
    coalesce(lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_9_DESC,
    coalesce(lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_10_id , 
    coalesce(lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_10_NAME, 
    coalesce(lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_10_NUMBER,
    coalesce(lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_10_DESC,
    coalesce(lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_11_id , 
    coalesce(lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_11_NAME, 
    coalesce(lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_11_NUMBER,
    coalesce(lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_11_DESC,
    coalesce(lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_12_id , 
    coalesce(lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_12_NAME, 
    coalesce(lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_12_NUMBER,
    coalesce(lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_12_DESC,
    coalesce(lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_13_id , 
    coalesce(lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_13_NAME, 
    coalesce(lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_13_NUMBER,
    coalesce(lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_13_DESC,
    coalesce(lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_14_id , 
    coalesce(lev14.NAME,lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_14_NAME, 
    coalesce(lev14.ACCOUNTNUMBER,lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_14_NUMBER,
    coalesce(lev14.DESCRIPTION,lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_14_DESC,
    coalesce(lev15.id,lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_15_id , 
    coalesce(lev15.NAME,lev14.NAME,lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_15_NAME, 
    coalesce(lev15.ACCOUNTNUMBER,lev14.ACCOUNTNUMBER,lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_15_NUMBER,
    coalesce(lev15.DESCRIPTION,lev14.DESCRIPTION,lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_15_DESC,
    SYSDATE W$_INSERT_DT, 
    SYSDATE W$_UPDATE_DT,
    'SALESFORCE' DATA_SRC_NM
FROM oax$oac.dw_sar_x_sf_account lev01
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev02 ON lev01.id = lev02.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev03 ON lev02.id = lev03.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev04 ON lev03.id = lev04.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev05 ON lev04.id = lev05.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev06 ON lev05.id = lev06.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev07 ON lev06.id = lev07.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev08 ON lev07.id = lev08.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev09 ON lev08.id = lev09.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev10 ON lev09.id = lev10.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev11 ON lev10.id = lev11.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev12 ON lev11.id = lev11.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev13 ON lev12.id = lev12.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev14 ON lev12.id = lev13.Parentid
    LEFT OUTER JOIN oax$oac.dw_sar_x_sf_account lev15 ON lev14.id = lev14.Parentid
WHERE 
    lev01.Parentid IS NULL
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_ACCOUNT_HIER" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_CAMPAIGN_MEMBER
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_CAMPAIGN_MEMBER" ("CAMPAIGN_MEMBER_ID", "LEAD_OR_CONTACT_ID", "CAMPAIGN_MEMBER_CONTACT_ID", "CAMPAIGN_ID", "CAMPAIGN_OWNER_ID", "CAMPAIGN_END_DATE", "CAMPAIGN_START_DATE", "CAMPAIGN_CREATED_DATE", "CAMPAIGN_ACTUAL_COST", "CAMPAIGN_BUDGETED_COST", "CAMPAIGN_EXPECTED_REVENUE", "LEAD_IS_CONVERTED_FLAG", "CAMPAIGN_MEMEBR_HAS_RESPONDED_FLAG", "CAMPAIGN_ACTIVE_FLAG", "CAMPAIGN_NUMBER_OF_CONTACTS", "CAMPAIGN_NUMBER_OF_COMVERTED_LEADS", "CAMPAIGN_NUMBER_OF_LEADS", "CAMPAIGN_NUMBER_OF_OPPTY", "CAMPAIGN_NUMBER_OF_RESPONSES", "CAMPAIGN_NUMBER_OF_WON_OPPTY", "CAMPAIGN_NUMBER_OF_SENT_IN") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
        camp_memb.id                  AS campaign_member_id,
        camp_memb.leadorcontactid     AS lead_or_contact_id,
        camp_memb.contactid           AS campaign_member_contact_id,
        camp_memb.campaignid          AS campaign_id,
        camp.ownerid                  AS campaign_owner_id,
        camp.enddate                  campaign_end_date,
        camp.startdate                campaign_start_date,
        camp.createddate              campaign_created_date,
        TO_NUMBER(camp.actualcost)               campaign_actual_cost,
        TO_NUMBER(camp.budgetedcost)             campaign_budgeted_cost,
        TO_NUMBER(camp.expectedrevenue)          campaign_expected_revenue,
        lead.isconverted              lead_is_converted_flag,
        camp_memb.hasresponded        campaign_memebr_has_responded_flag,
        camp.isactive                 campaign_active_flag,
        TO_NUMBER(camp.numberofcontacts)         campaign_number_of_contacts,
        TO_NUMBER(camp.numberofconvertedleads)   campaign_number_of_comverted_leads,
        TO_NUMBER(camp.numberofleads)            campaign_number_of_leads,
        TO_NUMBER(camp.numberofopportunities)    campaign_number_of_oppty,
        TO_NUMBER(camp.numberofresponses)        campaign_number_of_responses,
        TO_NUMBER(camp.numberofwonopportunities) campaign_number_of_won_oppty,
        TO_NUMBER(camp.numbersent)               campaign_number_of_sent_in
    FROM
        oax$oac.dw_sar_x_sf_campaign_member camp_memb
        LEFT OUTER JOIN oax$oac.dw_sar_x_sf_campaign        camp ON camp.id = camp_memb.campaignid
        LEFT OUTER JOIN oax$oac.dw_sar_x_sf_lead            lead ON camp_memb.leadorcontactid = lead.id
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_CAMPAIGN_MEMBER" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_CAMPAIGN_OPPORTUNITY
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_CAMPAIGN_OPPORTUNITY" ("CAMPAIGN_ID", "CAMPAIGN_OWNER_ID", "CAMPAIGN_END_DATE", "CAMPAIGN_START_DATE", "CAMPAIGN_CREATED_DATE", "CAMPAIGN_OPPORTUNITY_ID", "CAMPAIGN_OPPORTUNITY_ACCOUNT_ID", "CAMPAIGN_OPPORTUNITY_CONTACT_ID", "CAMPAIGN_OPPORTUNITY_CREATED_DATE", "CAMPAIGN_OPPORTUNITY_CLOSE_DATE", "CAMPAIGN_OPPORTUNITY_IS_WON_FLAG", "CAMPAIGN_OPPORTUNITY_EXPECTED_REVENUE", "CAMPAIGN_OPPORTUNITY_AMOUNT", "CAMPAIGN_OPPORTUNITY_QUANTITY", "CAMPAIGN_EXPECTED_REVENUE", "CAMPAIGN_ACTUAL_COST", "CAMPAIGN_AMONT_WON_OPPTY", "CAMPAIGN_AMONT_ALL_OPPTY") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    camp.id CAMPAIGN_ID,
    camp.ownerid CAMPAIGN_OWNER_ID,
    camp.enddate CAMPAIGN_END_DATE,
    camp.STARTdate CAMPAIGN_START_DATE,
    camp.createddate CAMPAIGN_CREATED_DATE,
    oppty.ID CAMPAIGN_OPPORTUNITY_ID,
    oppty.accountid CAMPAIGN_OPPORTUNITY_ACCOUNT_ID,
    oppty.contactid CAMPAIGN_OPPORTUNITY_CONTACT_ID,
    oppty.createddate CAMPAIGN_OPPORTUNITY_CREATED_DATE,
    oppty.closedate CAMPAIGN_OPPORTUNITY_CLOSE_DATE,
    oppty.iswon CAMPAIGN_OPPORTUNITY_IS_WON_FLAG,
    oppty.expectedrevenue CAMPAIGN_OPPORTUNITY_EXPECTED_REVENUE,
    oppty.amount CAMPAIGN_OPPORTUNITY_AMOUNT,
    oppty.totalopportunityquantity CAMPAIGN_OPPORTUNITY_QUANTITY,
    camp.expectedresponse CAMPAIGN_EXPECTED_REVENUE,
    camp.actualcost CAMPAIGN_ACTUAL_COST,
    camp.amountwonopportunities CAMPAIGN_AMONT_WON_OPPTY,
    camp.amountallopportunities CAMPAIGN_AMONT_ALL_OPPTY

FROM
    OAX$OAC.DW_SAR_X_SF_CAMPAIGN camp
LEFT OUTER JOIN OAX$OAC.DW_SAR_X_SF_OPPORTUNITY oppty on oppty.campaignid = camp.id
WHERE oppty.id is not null
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_CAMPAIGN_OPPORTUNITY" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_EVENT_OPPORTUNITY
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_EVENT_OPPORTUNITY" ("EVENT_ID", "EVENT_ASSIGNEE_ID", "EVENT_CREATED_DATE", "EVENT_OPPORTUNITY_ID", "EVENT_OPPORTUNITY_OWNER_ID", "EVENT_OPPORTUNITY_ACCOUNT_ID", "EVENT_OPPORTUNITY_CONTACT_ID", "EVENT_OPPORTUNITY_CLOSE_DATE", "EVENT_OPPORTUNITY_CREATED_DATE", "EVENT_OPPORTUNITY_IS_CLOSED_FLAG", "EVENT_END_DATE", "EVENT_DURATION", "EVENT_END_DATE_TIME", "EVENT_START_DATE_TIME") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT 
    event.id EVENT_ID,
    event.ownerid EVENT_ASSIGNEE_ID,
    event.createddate EVENT_CREATED_DATE,
    oppty.id EVENT_OPPORTUNITY_ID,
    oppty.OWNERID EVENT_OPPORTUNITY_OWNER_ID,
    oppty.ACCOUNTID EVENT_OPPORTUNITY_ACCOUNT_ID,
    oppty.contactid EVENT_OPPORTUNITY_CONTACT_ID,
    oppty.closedate EVENT_OPPORTUNITY_CLOSE_DATE,
    oppty.createddate EVENT_OPPORTUNITY_CREATED_DATE,
    oppty.isclosed EVENT_OPPORTUNITY_IS_CLOSED_FLAG,
    event.enddate EVENT_END_DATE,
    event.durationinminutes EVENT_DURATION,
    event.enddatetime EVENT_END_DATE_TIME,
    event.startdatetime EVENT_START_DATE_TIME
FROM 
    OAX$OAC.DW_SAR_X_SF_EVENT event
LEFT OUTER JOIN OAX$OAC.DW_SAR_X_SF_OPPORTUNITY oppty on event.whatid = oppty.id
WHERE oppty.id is not null
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_EVENT_OPPORTUNITY" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_OPPORTUNITY_OWNER
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_OWNER" ("OPPTY_OWNER_ID", "OPPTY_OWNER_NAME", "OPPTY_OWNER_USERNAME", "OPPTY_OWNER_ALIAS", "OPPTY_OWNER_EMAIL", "OPPTY_OWNER_EMPLOYEE_NUMBER", "OPPTY_OWNER_DEDERATION_ID", "OPPTY_OWNER_TITLE", "OPPTY_OWNER_COMPANY", "OPPTY_OWNER_DEPARTMENT", "OPPTY_OWNER_DIVISION", "OPPTY_OWNER_ACTIVE") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  select distinct
    --oppty.id OPPTY_ID,
    owner.id OPPTY_OWNER_ID,
    owner.LASTNAME || ',' || owner.FIRSTNAME OPPTY_OWNER_NAME,
    owner.username OPPTY_OWNER_USERNAME,
    --userrole.NAME OPPTY_OWNER_ROLE,
    owner.alias OPPTY_OWNER_ALIAS, 
    owner.EMAIL OPPTY_OWNER_EMAIL,
    owner.EMPLOYEENUMBER OPPTY_OWNER_EMPLOYEE_NUMBER,
    owner.FEDERATIONIDENTIFIER OPPTY_OWNER_DEDERATION_ID,
    owner.TITLE OPPTY_OWNER_TITLE,
    owner.COMPANYNAME OPPTY_OWNER_COMPANY,
    owner.DEPARTMENT OPPTY_OWNER_DEPARTMENT,
    owner.DIVISION OPPTY_OWNER_DIVISION, 
    --profile.NAME OPPTY_OWNER_PROFILE,
    owner.ISACTIVE OPPTY_OWNER_ACTIVE
from 
    OAX$oac.dw_sar_x_sf_opportunity oppty,
    OAX$oac.dw_sar_x_SF_USER owner--,
left outer join OAX$oac.dw_sar_x_SF_USER manager on owner.MANAGERID  = manager.id
left outer join OAX$oac.dw_sar_x_SF_USER_ROLE userrole on owner.userroleid = userrole.id
left outer join OAX$oac.dw_sar_x_SF_USER_PROFILE profile on owner.PROFILEID = profile.id
where
    oppty.ownerid = owner.id
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_OWNER" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_OPPORTUNITY_PRODUCT
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_PRODUCT" ("CREATEDBYID", "CREATEDDATE", "DESCRIPTION", "ID", "ISDELETED", "LASTMODIFIEDBYID", "LASTMODIFIEDDATE", "LASTREFERENCEDDATE", "LASTVIEWEDDATE", "LISTPRICE", "NAME", "OPPORTUNITYID", "PRICEBOOKENTRYID", "PRODUCT2ID", "PRODUCTCODE", "QUANTITY", "SERVICEDATE", "SORTORDER", "SYSTEMMODSTAMP", "TOTALPRICE", "UNITPRICE", "SOURCE_RECORD_ID", "OPPTY_OWNER_ID", "OPPTY_ACCOUNT_ID", "OPPTY_CAMPAIGN_ID", "OPPTY_CURRENCY_ISO_CODE") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  select 

oppty_prod.CREATEDBYID,
oppty_prod.CREATEDDATE,
oppty_prod.DESCRIPTION,
oppty_prod.ID,
oppty_prod.ISDELETED,
oppty_prod.LASTMODIFIEDBYID,
oppty_prod.LASTMODIFIEDDATE,
oppty_prod.LASTREFERENCEDDATE,
oppty_prod.LASTVIEWEDDATE,
oppty_prod.LISTPRICE,
oppty_prod.NAME,
oppty_prod.OPPORTUNITYID,
oppty_prod.PRICEBOOKENTRYID,
oppty_prod.PRODUCT2ID,
oppty_prod.PRODUCTCODE,
oppty_prod.QUANTITY,
oppty_prod.SERVICEDATE,
oppty_prod.SORTORDER,
oppty_prod.SYSTEMMODSTAMP,
oppty_prod.TOTALPRICE,
oppty_prod.UNITPRICE,
oppty_prod.SOURCE_RECORD_ID,
oppty.ownerid OPPTY_OWNER_ID,
oppty.accountid OPPTY_ACCOUNT_ID,
oppty.campaignid OPPTY_CAMPAIGN_ID,
oppty.currencyisocode OPPTY_CURRENCY_ISO_CODE

from 
oax$oac.dw_sar_x_sf_opportunity_product oppty_prod
LEFT OUTER JOIN oax$oac.dw_sar_x_sf_opportunity oppty on oppty_prod.opportunityid = oppty.id
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_PRODUCT" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_OPPORTUNITY_REVENUE_LINE
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_REVENUE_LINE" ("OPPTY_ACCOUNT_ID", "OPPTY_ID", "OPPTY_OWNER_ID", "OPPTY_CAMPAIGN_ID", "OPPTY_CLOSED_DATE", "OPPTY_CREATED_DATE", "OPPTY_LINE_ITEM_ID", "OPPTY_PROD_CREATE_DATE", "OPPTY_PROD_PRICE_BOOK_ID", "OPPTY_PROD_PRODUCT_ID", "OPPTY_CURRENCY_ISO_CODE", "OPPTY_LINE_ITEM_SCHED_ID", "OPPTY_CONTACT_ID", "OPPTY_PROD_QUANTITY", "OPPTY_PROD_LIST_PRICE", "OPPTY_PROD_TOTAL_PRICE", "OPPTY_PROD_UNIT_PRICE", "OPPTY_LINBE_ITEM_SCHED_QTY", "OPPTY_LINBE_ITEM_SCHED_DATE", "OPPTY_LINBE_ITEM_SCHED_REV") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
        oppty.accountid                oppty_account_id,
        oppty_prod.opportunityid       oppty_id,
        oppty.ownerid                  oppty_owner_id,
        oppty.campaignid               oppty_campaign_id,
        oppty.closedate                oppty_closed_date,
        oppty.createddate              oppty_created_date,
        oppty_prod.id                  oppty_line_item_id,
        oppty_prod.createddate         oppty_prod_create_date,
        oppty_prod.pricebookentryid    oppty_prod_price_book_id,
        oppty_prod.product2id          oppty_prod_product_id,
        oppty.currencyisocode          oppty_currency_iso_code,
        oppty_line_item_sched.id       oppty_line_item_sched_id,
        oppty.contactid                oppty_contact_id,
        oppty_prod.quantity            oppty_prod_quantity,
        oppty_prod.listprice           oppty_prod_list_price,
        oppty_prod.totalprice          oppty_prod_total_price,
        oppty_prod.unitprice           oppty_prod_unit_price,
        oppty_line_item_sched.quantity oppty_linbe_item_sched_qty,
        oppty_line_item_sched.SCHEDULEDATE oppty_linbe_item_sched_date,
        TO_NUMBER(oppty_line_item_sched.revenue)  oppty_linbe_item_sched_rev
    FROM
        oax$oac.dw_sar_x_sf_opportunity_product         oppty_prod
        LEFT OUTER JOIN oax$oac.dw_sar_x_sf_opportunity                 oppty ON oppty_prod.opportunityid = oppty.id
        LEFT OUTER JOIN oax$oac.dw_sar_x_sf_opportunity_line_item_sched oppty_line_item_sched ON oppty_line_item_sched.opportunitylineitemid =
        oppty_prod.id
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_OPPORTUNITY_REVENUE_LINE" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_PARTNER
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_PARTNER" ("OPPORTUNITYID", "ACCOUNTTOID", "PARTNER_NAME", "PARTNER_TYPE", "PARTNER_RATING", "PARTNER_ROLE", "PARTNER_OWNER", "PARTNER_OWNER_USERNAME", "PARTNER_OWNER_ALIAS", "PARTNER_OWNER_PARENT_ACCOUNT", "ISPRIMARY") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  select 
    partner.id OPPORTUNITYID,
    partner.ACCOUNTTOID, 
    acct.NAME PARTNER_NAME,
    acct.TYPE PARTNER_TYPE,
    acct.RATING PARTNER_RATING,
    partner.ROLE PARTNER_ROLE,
    owner.LASTNAME || ',' || owner.FIRSTNAME PARTNER_OWNER,
    owner.username PARTNER_OWNER_USERNAME,
    owner.alias PARTNER_OWNER_ALIAS,
    paracct.NAME PARTNER_OWNER_PARENT_ACCOUNT,
    ISPRIMARY
from  
    OAX$oac.dw_sar_x_SF_partner partner,
    OAX$oac.dw_sar_x_SF_ACCOUNT  acct,
    OAX$oac.dw_sar_x_SF_USER owner,
    OAX$oac.dw_sar_x_SF_ACCOUNT paracct
where
    partner.ACCOUNTTOID = acct.id
    and owner.id = acct.ownerid
    and acct.parentid = paracct.id
    and partner.ISPRIMARY= 'true'
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_PARTNER" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_TASK_OPPORTUNITY
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_TASK_OPPORTUNITY" ("TASK_ID", "TASK_ASSIGNEE_ID", "TASK_CREATED_DATE", "TASK_OPPORTUNITY_ID", "TASK_OPPORTUNITY_OWNER_ID", "TASK_OPPORTUNITY_ACCOUNT_ID", "TASK_OPPORTUNITY_CONTACT_ID", "TASK_OPPORTUNITY_CLOSE_DATE", "TASK_OPPORTUNITY_CREATED_DATE", "TASK_OPPORTUNITY_IS_CLOSED_FLAG", "CALL_DURATION", "TASK_COMPLETED_DATE_TIME") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT 
    task.id TASK_ID,
    task.ownerid TASK_ASSIGNEE_ID,
    task.createddate TASK_CREATED_DATE,
    oppty.id TASK_OPPORTUNITY_ID,
    oppty.OWNERID TASK_OPPORTUNITY_OWNER_ID,
    oppty.ACCOUNTID TASK_OPPORTUNITY_ACCOUNT_ID,
    oppty.contactid TASK_OPPORTUNITY_CONTACT_ID,
    oppty.closedate TASK_OPPORTUNITY_CLOSE_DATE,
    oppty.createddate TASK_OPPORTUNITY_CREATED_DATE,
    oppty.isclosed TASK_OPPORTUNITY_IS_CLOSED_FLAG,
    task.calldurationinseconds CALL_DURATION,
    task.completeddatetime TASK_COMPLETED_DATE_TIME
FROM 
    OAX$OAC.DW_SAR_X_SF_task task
LEFT OUTER JOIN OAX$OAC.DW_SAR_X_SF_OPPORTUNITY oppty on task.whatid = oppty.id
WHERE oppty.id is not null
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_TASK_OPPORTUNITY" TO "OAX$OAC";
--------------------------------------------------------
--  DDL for View V_DW_SAR_X_SF_TERRITORY_HIER
--------------------------------------------------------

  CREATE OR REPLACE FORCE EDITIONABLE VIEW "OAX_USER"."V_DW_SAR_X_SF_TERRITORY_HIER" ("HRCH_CTGY_ID", "HRCH_CTGY_NM", "HRCH_ID", "HRCH_NM", "LVL_MIN_NUM", "LVL_MAX_NUM", "LEAF_ID", "HRCH_1_ID", "HRCH_1_NAME", "HRCH_1_DESC", "HRCH_2_ID", "HRCH_2_NAME", "HRCH_2_DESC", "HRCH_3_ID", "HRCH_3_NAME", "HRCH_3_DESC", "HRCH_4_ID", "HRCH_4_NAME", "HRCH_4_DESC", "HRCH_5_ID", "HRCH_5_NAME", "HRCH_5_DESC", "HRCH_6_ID", "HRCH_6_NAME", "HRCH_6_DESC", "HRCH_7_ID", "HRCH_7_NAME", "HRCH_7_DESC", "HRCH_8_ID", "HRCH_8_NAME", "HRCH_8_DESC", "HRCH_9_ID", "HRCH_9_NAME", "HRCH_9_DESC", "HRCH_10_ID", "HRCH_10_NAME", "HRCH_10_DESC", "HRCH_11_ID", "HRCH_11_NAME", "HRCH_11_DESC", "HRCH_12_ID", "HRCH_12_NAME", "HRCH_12_DESC", "HRCH_13_ID", "HRCH_13_NAME", "HRCH_13_DESC", "HRCH_14_ID", "HRCH_14_NAME", "HRCH_14_DESC", "HRCH_15_ID", "HRCH_15_NAME", "HRCH_15_DESC", "W$_INSERT_DT", "W$_UPDATE_DT", "DATA_SRC_NM") DEFAULT COLLATION "USING_NLS_COMP"  AS 
  SELECT
    --DW_SAR_X_SF_HRCH_SEQ.NEXTVAL HRCH_DIM_ID,
    'TERR' HRCH_CTGY_id, 
    'Territory' HRCH_CTGY_NM, 
    'SFDC' HRCH_id, 
    'Salesforce Territory Tree' HRCH_NM, 
    0 LVL_MIN_NUM, 
    15 LVL_MAX_NUM, 
    coalesce(lev15.id,lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) LEAF_ID, 
    lev01.id HRCH_1_id, 
    lev01.NAME HRCH_1_NAME, 
    --lev01.ACCOUNTNUMBER HRCH_1_NUMBER, 
    lev01.DESCRIPTION HRCH_1_DESC,
    coalesce(lev02.id,lev01.id) HRCH_2_id , 
    coalesce(lev02.NAME,lev01.NAME) HRCH_2_NAME, 
    --coalesce(lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_2_NUMBER, 
    coalesce(lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_2_DESC,
    coalesce(lev03.id,lev02.id,lev01.id) HRCH_3_id , 
    coalesce(lev03.NAME,lev02.NAME,lev01.NAME) HRCH_3_NAME, 
    --coalesce(lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_3_NUMBER,
    coalesce(lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_3_DESC,
    coalesce(lev04.id,lev03.id,lev02.id,lev01.id) HRCH_4_id , 
    coalesce(lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_4_NAME, 
    --coalesce(lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_4_NUMBER,
    coalesce(lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_4_DESC,
    coalesce(lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_5_id , 
    coalesce(lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_5_NAME, 
    --coalesce(lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_5_NUMBER,
    coalesce(lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_5_DESC,
    coalesce(lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_6_id , 
    coalesce(lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_6_NAME, 
    --coalesce(lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_6_NUMBER,
    coalesce(lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_6_DESC,
    coalesce(lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_7_id , 
    coalesce(lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_7_NAME, 
    --coalesce(lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_7_NUMBER,
    coalesce(lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_7_DESC,
    coalesce(lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_8_id , 
    coalesce(lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_8_NAME, 
    --coalesce(lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_8_NUMBER,
    coalesce(lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_8_DESC,
    coalesce(lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_9_id , 
    coalesce(lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_9_NAME, 
    --coalesce(lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_9_NUMBER,
    coalesce(lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_9_DESC,
    coalesce(lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_10_id , 
    coalesce(lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_10_NAME, 
    --coalesce(lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_10_NUMBER,
    coalesce(lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_10_DESC,
    coalesce(lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_11_id , 
    coalesce(lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_11_NAME, 
    --coalesce(lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_11_NUMBER,
    coalesce(lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_11_DESC,
    coalesce(lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_12_id , 
    coalesce(lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_12_NAME, 
    --coalesce(lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_12_NUMBER,
    coalesce(lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_12_DESC,
    coalesce(lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_13_id , 
    coalesce(lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_13_NAME, 
    --coalesce(lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_13_NUMBER,
    coalesce(lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_13_DESC,
    coalesce(lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_14_id , 
    coalesce(lev14.NAME,lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_14_NAME, 
    --coalesce(lev14.ACCOUNTNUMBER,lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_14_NUMBER,
    coalesce(lev14.DESCRIPTION,lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_14_DESC,
    coalesce(lev15.id,lev14.id,lev13.id,lev12.id,lev11.id,lev10.id,lev09.id,lev08.id,lev07.id,lev06.id,lev05.id,lev04.id,lev03.id,lev02.id,lev01.id) HRCH_15_id , 
    coalesce(lev15.NAME,lev14.NAME,lev13.NAME,lev12.NAME,lev11.NAME,lev10.NAME,lev09.NAME,lev08.NAME,lev07.NAME,lev06.NAME,lev05.NAME,lev04.NAME,lev03.NAME,lev02.NAME,lev01.NAME) HRCH_15_NAME, 
    --coalesce(lev15.ACCOUNTNUMBER,lev14.ACCOUNTNUMBER,lev13.ACCOUNTNUMBER,lev12.ACCOUNTNUMBER,lev11.ACCOUNTNUMBER,lev10.ACCOUNTNUMBER,lev09.ACCOUNTNUMBER,lev08.ACCOUNTNUMBER,lev07.ACCOUNTNUMBER,lev06.ACCOUNTNUMBER,lev05.ACCOUNTNUMBER,lev04.ACCOUNTNUMBER,lev03.ACCOUNTNUMBER,lev02.ACCOUNTNUMBER,lev01.ACCOUNTNUMBER) HRCH_15_NUMBER,
    coalesce(lev15.DESCRIPTION,lev14.DESCRIPTION,lev13.DESCRIPTION,lev12.DESCRIPTION,lev11.DESCRIPTION,lev10.DESCRIPTION,lev09.DESCRIPTION,lev08.DESCRIPTION,lev07.DESCRIPTION,lev06.DESCRIPTION,lev05.DESCRIPTION,lev04.DESCRIPTION,lev03.DESCRIPTION,lev02.DESCRIPTION,lev01.DESCRIPTION) HRCH_15_DESC,
    SYSDATE W$_INSERT_DT, 
    SYSDATE W$_UPDATE_DT,
    'SALESFORCE' DATA_SRC_NM
FROM oax$oac.DW_SAR_X_SF_TERRITORY lev01
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev02 ON lev01.id = lev02.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev03 ON lev02.id = lev03.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev04 ON lev03.id = lev04.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev05 ON lev04.id = lev05.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev06 ON lev05.id = lev06.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev07 ON lev06.id = lev07.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev08 ON lev07.id = lev08.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev09 ON lev08.id = lev09.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev10 ON lev09.id = lev10.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev11 ON lev10.id = lev11.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev12 ON lev11.id = lev11.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev13 ON lev12.id = lev12.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev14 ON lev12.id = lev13.PARENTTERRITORY2ID
    LEFT OUTER JOIN oax$oac.DW_SAR_X_SF_TERRITORY lev15 ON lev14.id = lev14.PARENTTERRITORY2ID
WHERE 
    lev01.PARENTTERRITORY2ID IS NULL
;
  GRANT SELECT ON "OAX_USER"."V_DW_SAR_X_SF_TERRITORY_HIER" TO "OAX$OAC";
