IDS_IDT_CONTACT_STG.SQL

The following sample SQL script can be used for incremental data load.

/*
============================================================================
 Need to change TBLO before executing the scripts on target database.
============================================================================
*/
SET TERMOUT ON
SET FEEDBACK OFF
SET VERIFY OFF
SET TIME OFF
SET TIMING OFF
SET ECHO OFF
SET PAUSE OFF
SET PAGESIZE 50
DROP MATERIALIZED VIEW CONTACTS_SNAPSHOT_VIEW;
CREATE MATERIALIZED VIEW CONTACTS_SNAPSHOT_VIEW AS
SELECT
   T1.CONTACT_ID CONTACT_ID,
   T2.FST_NAME || ' ' || LAST_NAME NAME,
   T2.MID_NAME MIDDLE_NAME,
   T3.ROW_ID   ADDRESS_ID,
   T3.CITY     CITY,
   T3.COUNTRY COUNTRY,
   T3.ZIPCODE  POSTAL_CODE,
   T3.STATE   STATE,
   T3.ADDR    STREETADDRESS,
   T3.ADDR_LINE_2 ADDRESS_LINE2,
   DECODE(T2.PR_PER_ADDR_ID,T3.ROW_ID,'Y','N') PRIMARY_FLAG,
   T4.NAME    ACCOUNT,
   T2.BIRTH_DT BirthDate,
   T2.CELL_PH_NUM  CellularPhone,
   T2.EMAIL_ADDR  EmailAddress,
   T2.HOME_PH_NUM  HomePhone,
   T2.SOC_SECURITY_NUM  SocialSecurityNumber,
   T2.WORK_PH_NUM  WorkPhone,
   FLOOR((ROWNUM-1)/&BATCHSIZE)+1 BATCH_NUM
FROM
   dbo.S_CON_ADDR T1,
   dbo.S_CONTACT T2,
   dbo.S_ADDR_PER T3,
   dbo.S_ORG_EXT T4
WHERE
   T1.CONTACT_ID = T2.ROW_ID AND
   T1.ADDR_PER_ID      = T3.ROW_ID AND
--    OR (T1.ADDR_PER_ID IS NULL))  Do we need contacts with no address?
--Comment the following line for Multiple address match option
--    T2.PR_PER_ADDR_ID   = T3.ROW_ID (+) AND
    T2.PR_DEPT_OU_ID    = T4.PAR_ROW_ID (+)
/
SELECT '============================================================================' 
|| CHR(10) ||
       '                  REPORT ON CONTACTS SNAPSHOT' || CHR(10) ||
       '============================================================================' 
|| CHR(10)  "  "
       FROM DUAL
/
SELECT BATCH_NUM BATCH, COUNT(*)  "NUMBER OF RECORDS"
FROM CONTACTS_SNAPSHOT_VIEW
GROUP BY BATCH_NUM
ORDER BY BATCH_NUM
/