2016 – November22 - SWIFT Data Warehouse – Revised Extract Views

New Extract views are now available in Production. These new X2 views provide for faster running of reports, extracting of data and facilitate incremental loads.

  • PS_X2_W_RECV_LN to replace PS_X_S_W_RECV_LN
  • PS_X2_W_RECV_HDR to replace PS_X_S_W_RECV_HDR
  • PS_X2_W_PROJ_TRNS to replace PS_X_S_W_PROJ_TRNS
  • PS_X2_W_FEDAIDPRJ to replace PS_X_S_W_FEDAIDPRJ
  • PS_X2_W_FEDAID to replace PS_X_S_W_FEDAID

3 new fields where added to these views to allow for incremental updates:

  • CREATED_EW_DTTM
  • DATA_ORIGIN
  • LASTUPD_EW_DTTM

The current views will continue to be available to allow agencies time to redirect their extracts and reports to the new X2 views.

The current views have been modified to address some data issues identified.

Revised View Changes:

  • PS_X_S_W_RECV_LN
  • Removes join to PS_S_W_BI_LN_NOTE that was causing duplicates for Lines with Multiple Notes and defaults ‘-‘ to fields (W_LINE_NOTE_FLAG, W_STD_NOTE_CD, W_LINE_CMNT_NOTE) that were sourced from this table.
  • Setsall Date Fields to Null if they have the default date of 1/1/1753
  • PS_X_S_W_RECV_HDR
  • Removes join to PS_S_W_BI_HDR_NOTE that was causing duplicates for Lines with Multiple Notes and defaults ‘-‘ to fields (STD_NOTE_CD, W_HDR_CMNT_NOTE,W_HDR_NOTE_FLAG) that were sourced from this table.
  • Sets all Date Fields to Null if they have the default date of 1/1/1753
  • PS_X_S_W_PROJ_TRNS
  • Removes duplicate/incorrect rows that were joining to logically deleted CA_DETAIL_PROJ & GM_AWARD rows
  • Sets all Date Fields to Null if they have the default date of 1/1/1753
  • Adds aunique key (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, RESOURCE_ID)
  • PS_X_S_W_FEDAIDPRJ
  • Removes duplicate/incorrect rows that were joining to logically deleted CA_DETAIL_PROJ rows
  • Includes rows for pre-encumbrances from LEDGERS 'KK_PRCDPRE' and 'KK_PRC_PRE' whichwere missing
  • Sets all Date Fields to Null if they have the default date of 1/1/1753
  • PS_X_S_W_FEDAID
  • Sets all Date Fields to Null if they have the default date of 1/1/1753
  • Includes a unique key (CONTRACT_NUM, BUSINESS_UNIT_PC, PROJECT_ID)

*Corrections related to duplicate rows and pre-encumbrances on X_S_W_FEDAIDPRJ impact values in this view

X2 Details

  • PS_X2_W_RECV_LN to replace PS_X_S_W_RECV_LN
  • Includes rows where an inner joined table row is logically deleted (DATA_ORIGIN = ‘D’).
  • Includes additional fields related to EPM processing (LOAD_ERROR, DATA_ORIGIN, CREATED_EW_DTTM, LASTUPD_EW_DTTM, BATCH_SID)
  • Removes fields that were sourced from PS_S_W_BI_LN_NOTE (W_LINE_NOTE_FLAG, W_STD_NOTE_CD, W_LINE_CMNT_NOTE)
  • Includes additional fields (LINE_SEQ_NUM,ACCOUNTING_DT, ACCT_ENTRY_TYPE, SRC_SYS_ID) that are part of a unique key
  • Includes a unique key (W_SOURCE_TRAN, BUSINESS_UNIT, CUST_ID, ITEM_LINE, ITEM, ITEM_SEQ_NUM, LEDGER_GROUP, LEDGER, DST_SEQ_NUM, LINE_SEQ_NUM, ACCOUNTING_DT, ACCT_ENTRY_TYPE, SRC_SYS_ID)
  • PS_X2_W_RECV_HDR to replace PS_X_S_W_RECV_HDR
  • Includes rows where an inner joined table row is logically deleted (DATA_ORIGIN = ‘D’).
  • Includes additional fields related to EPM processing (LOAD_ERROR, DATA_ORIGIN, CREATED_EW_DTTM, LASTUPD_EW_DTTM, BATCH_SID)
  • Removes fields that were sourced from PS_S_W_BI_HDR_NOTE (STD_NOTE_CD, W_HDR_CMNT_NOTE,W_HDR_NOTE_FLAG)
  • Includes a unique key (BUSINESS_UNIT, CUST_ID, ITEM, ITEM_LINE, SRC_SYS_ID)
  • PS_X2_W_PROJ_TRNS to replace PS_X_S_W_PROJ_TRNS
  • Includes additional fields related to EPM processing (LOAD_ERROR, DATA_ORIGIN, CREATED_EW_DTTM, LASTUPD_EW_DTTM, BATCH_SID)
  • Includes a unique key (BUSINESS_UNIT, PROJECT_ID, ACTIVITY_ID, RESOURCE_ID, SRC_SYS_ID)
  • PS_X2_W_FEDAIDPRJ to replace PS_X_S_W_FEDAIDPRJ
  • Includes additional fields related to EPM processing (LOAD_ERROR, DATA_ORIGIN, CREATED_EW_DTTM, LASTUPD_EW_DTTM, BATCH_SID)
  • PS_X2_W_FEDAID to replace PS_X_S_W_FEDAID
  • Includes additional fields related to EPM processing (LOAD_ERROR, DATA_ORIGIN, CREATED_EW_DTTM, LASTUPD_EW_DTTM, BATCH_SID)
  • Includes a unique key (CONTRACT_NUM, BUSINESS_UNIT_PC, PROJECT_ID, SRC_SYS_ID)

If you have any questions regarding the new SWIFT Data Warehouse Extract views, please contact the SWIFT Help Desk by phone at 651-201-8100, option 2.