UPDATE_AccountFirmographic
Technical Description of UPDATE_AccountFirmographic within Prod OMNIA Partners EDW at 25-Mar-2025 12:25:05
Purpose: | UPDATE_AccountFirmographic |
Date Created: | 2022-09-18 19:53:44 |
Date Last Updated: | |
Script Type: | PowerShell (64-bit) script |
Notes: | |
#--==============================================================================
#-- DBMS Name : SNOWFLAKE Custom*
#-- Block Name : UPDATE_AccountFirmographic
#-- Template : wsl_snowflake_pscript_perm
#-- Template Version : 8.3.1.0
#-- Description : Update the Dimension table AccountFirmographic
#-- Generated by : WhereScape RED Version 8.6.6.1 (build 220216-231917)
#-- Generated for : Omnia Partners
#-- Generated on : Sunday, September 18, 2022 at 19:53:33
#-- Author : Aaron Anzalone
#--==============================================================================
#-- Notes / History
#--
Import-module -Name WslPowershellCommon -DisableNameChecking
Import-module -Name WslPowershellSnowflake -DisableNameChecking
$tgtConn = New-Object System.Data.Odbc.OdbcConnection
Hide-Window
#--============================================================================
#-- General Variables
#--============================================================================
$sequence = ${env:WSL_SEQUENCE}
$jobName = ${env:WSL_JOB_NAME}
$taskName = ${env:WSL_TASK_NAME}
$jobId = ${env:WSL_JOB_KEY}
$taskId = ${env:WSL_TASK_KEY}
$return_Msg = "AccountFirmographic updated."
$status = 1
#--============================================================================
#-- Main
#--============================================================================
$step = 100
[int64]$mergeCount = 0
if ( $status -eq 1 ){
#--============================================================================
#-- Refresh zero key records
#--============================================================================
$step = 200
$sql = @"
MERGE INTO [TABLEOWNER].[AccountFirmographic]
USING (
SELECT CAST(NULL AS number) AS Level1ForceId
, CAST(NULL AS number) AS ForceId
, 'Unknown' AS Level1StrategicAccountReason
, 'Unknown' AS Level1AccountType
, 'Unknown' AS AccountType
, 'Unknown' AS RecordDesignation
, 'Unknown' AS UniverseName
, 'Unknown' AS UniverseProvider
, 'Unknown' AS UniverseUltimateParentId
, 'Unknown' AS UniverseId
, CAST(NULL AS number) AS CustomerSize
, 'Unknown' AS CustomerSizeCode
, 'Unknown' AS CustomerSizeSuffix
, 'Unknown' AS UniverseInstitutionName
, 'Unknown' AS UniverseInstitutionState
, 'Unknown' AS UniverseInstitutionCity
, CAST(NULL AS number) AS LocationSize
, CAST(NULL AS number) AS LocationUltimateParentId
, CAST(NULL AS number) AS LocationParentId
, CAST(NULL AS number) AS LocationId
, 'Unknown' AS LocationInstitutionName
, 'Unknown' AS LocationSizeCode
, CAST(NULL AS number) AS LocationGROUP
, 'Unknown' AS LocationUNIV
, 'Unknown' AS LocationFILETYPE
, 'Unknown' AS LocationAddress
, 'Unknown' AS LocationCity
, 'Unknown' AS LocationState
, 'Unknown' AS LocationZip
, 'Unknown' AS UniverseCustomerSizeSuffix
, CAST(NULL AS number) AS UniverseParentId
, CAST(NULL AS number) AS UniverseGROUP
, 'Unknown' AS UniverseUNIV
, 'Unknown' AS UniverseFILETYPE
, 'Unknown' AS dss_record_source
, CAST(NULL AS timestamp) AS dss_load_date
, 1 AS dss_source_system_key
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSCreateTime
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSUpdateTime
FROM DUAL
) AS src
ON AccountFirmographicKey = 0
WHEN MATCHED THEN UPDATE
SET AccountFirmographic.Level1ForceId = src.Level1ForceId
, AccountFirmographic.Level1StrategicAccountReason = src.Level1StrategicAccountReason
, AccountFirmographic.Level1AccountType = src.Level1AccountType
, AccountFirmographic.AccountType = src.AccountType
, AccountFirmographic.RecordDesignation = src.RecordDesignation
, AccountFirmographic.UniverseName = src.UniverseName
, AccountFirmographic.UniverseProvider = src.UniverseProvider
, AccountFirmographic.UniverseUltimateParentId = src.UniverseUltimateParentId
, AccountFirmographic.UniverseId = src.UniverseId
, AccountFirmographic.CustomerSize = src.CustomerSize
, AccountFirmographic.CustomerSizeCode = src.CustomerSizeCode
, AccountFirmographic.CustomerSizeSuffix = src.CustomerSizeSuffix
, AccountFirmographic.UniverseInstitutionName = src.UniverseInstitutionName
, AccountFirmographic.UniverseInstitutionState = src.UniverseInstitutionState
, AccountFirmographic.UniverseInstitutionCity = src.UniverseInstitutionCity
, AccountFirmographic.LocationSize = src.LocationSize
, AccountFirmographic.LocationUltimateParentId = src.LocationUltimateParentId
, AccountFirmographic.LocationParentId = src.LocationParentId
, AccountFirmographic.LocationId = src.LocationId
, AccountFirmographic.LocationInstitutionName = src.LocationInstitutionName
, AccountFirmographic.LocationSizeCode = src.LocationSizeCode
, AccountFirmographic.LocationGROUP = src.LocationGROUP
, AccountFirmographic.LocationUNIV = src.LocationUNIV
, AccountFirmographic.LocationFILETYPE = src.LocationFILETYPE
, AccountFirmographic.LocationAddress = src.LocationAddress
, AccountFirmographic.LocationCity = src.LocationCity
, AccountFirmographic.LocationState = src.LocationState
, AccountFirmographic.LocationZip = src.LocationZip
, AccountFirmographic.UniverseCustomerSizeSuffix = src.UniverseCustomerSizeSuffix
, AccountFirmographic.UniverseParentId = src.UniverseParentId
, AccountFirmographic.UniverseGROUP = src.UniverseGROUP
, AccountFirmographic.UniverseUNIV = src.UniverseUNIV
, AccountFirmographic.UniverseFILETYPE = src.UniverseFILETYPE
, AccountFirmographic.dss_record_source = src.dss_record_source
, AccountFirmographic.dss_load_date = src.dss_load_date
, AccountFirmographic.dss_source_system_key = src.dss_source_system_key
, AccountFirmographic.DSSUpdateTime = src.DSSUpdateTime
WHEN NOT MATCHED THEN INSERT
( AccountFirmographicKey
, Level1ForceId
, ForceId
, Level1StrategicAccountReason
, Level1AccountType
, AccountType
, RecordDesignation
, UniverseName
, UniverseProvider
, UniverseUltimateParentId
, UniverseId
, CustomerSize
, CustomerSizeCode
, CustomerSizeSuffix
, UniverseInstitutionName
, UniverseInstitutionState
, UniverseInstitutionCity
, LocationSize
, LocationUltimateParentId
, LocationParentId
, LocationId
, LocationInstitutionName
, LocationSizeCode
, LocationGROUP
, LocationUNIV
, LocationFILETYPE
, LocationAddress
, LocationCity
, LocationState
, LocationZip
, UniverseCustomerSizeSuffix
, UniverseParentId
, UniverseGROUP
, UniverseUNIV
, UniverseFILETYPE
, dss_record_source
, dss_load_date
, dss_source_system_key
, DSSCreateTime
, DSSUpdateTime
)
VALUES
( 0
, src.Level1ForceId
, src.ForceId
, src.Level1StrategicAccountReason
, src.Level1AccountType
, src.AccountType
, src.RecordDesignation
, src.UniverseName
, src.UniverseProvider
, src.UniverseUltimateParentId
, src.UniverseId
, src.CustomerSize
, src.CustomerSizeCode
, src.CustomerSizeSuffix
, src.UniverseInstitutionName
, src.UniverseInstitutionState
, src.UniverseInstitutionCity
, src.LocationSize
, src.LocationUltimateParentId
, src.LocationParentId
, src.LocationId
, src.LocationInstitutionName
, src.LocationSizeCode
, src.LocationGROUP
, src.LocationUNIV
, src.LocationFILETYPE
, src.LocationAddress
, src.LocationCity
, src.LocationState
, src.LocationZip
, src.UniverseCustomerSizeSuffix
, src.UniverseParentId
, src.UniverseGROUP
, src.UniverseUNIV
, src.UniverseFILETYPE
, src.dss_record_source
, src.dss_load_date
, src.dss_source_system_key
, src.DSSCreateTime
, src.DSSUpdateTime
)
;
"@
$SnowflakeResult = Run-Snowflake-RedSQL -sql $sql -dsn ${env:WSL_TGT_DSN} -uid ${env:WSL_TGT_USER} -pwd ${env:WSL_TGT_PWD} -failureMsg "Failed zero key merge into table AccountFirmographic step $step" -odbcConn $tgtConn
$mergeCount = $mergeCount + $SnowflakeResult[1]
$return_Msg = $SnowflakeResult[2]
}
if ( $status -eq 1 ){
#--============================================================================
#-- Refresh data
#--============================================================================
$step = 300
$sql = @"
MERGE INTO [TABLEOWNER].[AccountFirmographic]
USING (
-- NEW ROWS
SELECT DISTINCT MAXKEY.MAXKEY AS MAXKEY
, LoadvwCustomerFirmographics.Level1ForceId AS Level1ForceId
, LoadvwCustomerFirmographics.CustomerId AS ForceId
, LoadvwCustomerFirmographics.Level1Segmentation AS Level1StrategicAccountReason
, LoadvwCustomerFirmographics.Level1AccountType AS Level1AccountType
, LoadvwCustomerFirmographics.ClassificationId AS AccountType
, LoadvwCustomerFirmographics.RecordDesignation AS RecordDesignation
, LoadvwCustomerFirmographics.UniverseName AS UniverseName
, LoadvwCustomerFirmographics.UniverseProvider AS UniverseProvider
, LoadvwCustomerFirmographics.UniverseUltimateParentId AS UniverseUltimateParentId
, LoadvwCustomerFirmographics.UniverseId AS UniverseId
, LoadvwCustomerFirmographics.CustomerSize AS CustomerSize
, LoadvwCustomerFirmographics.CustomerSizeCode AS CustomerSizeCode
, LoadvwCustomerFirmographics.CustomerSizeSuffix AS CustomerSizeSuffix
, LoadvwCustomerFirmographics.UniverseInstitutionName AS UniverseInstitutionName
, LoadvwCustomerFirmographics.UniverseInstitutionState AS UniverseInstitutionState
, LoadvwCustomerFirmographics.UniverseInstitutionCity AS UniverseInstitutionCity
, LoadvwCustomerFirmographics.LocationSize AS LocationSize
, LoadvwCustomerFirmographics.LocationUltimateParentId AS LocationUltimateParentId
, LoadvwCustomerFirmographics.LocationParentId AS LocationParentId
, LoadvwCustomerFirmographics.LocationId AS LocationId
, LoadvwCustomerFirmographics.LocationInstitutionName AS LocationInstitutionName
, LoadvwCustomerFirmographics.LocationSizeCode AS LocationSizeCode
, LoadvwCustomerFirmographics.LocationGROUP AS LocationGROUP
, LoadvwCustomerFirmographics.LocationUNIV AS LocationUNIV
, LoadvwCustomerFirmographics.LocationFILETYPE AS LocationFILETYPE
, LoadvwCustomerFirmographics.LocationAddress AS LocationAddress
, LoadvwCustomerFirmographics.LocationCity AS LocationCity
, LoadvwCustomerFirmographics.LocationState AS LocationState
, LoadvwCustomerFirmographics.LocationZip AS LocationZip
, LoadvwCustomerFirmographics.UniverseCustomerSizeSuffix AS UniverseCustomerSizeSuffix
, LoadvwCustomerFirmographics.UniverseParentId AS UniverseParentId
, LoadvwCustomerFirmographics.UniverseGROUP AS UniverseGROUP
, LoadvwCustomerFirmographics.UniverseUNIV AS UniverseUNIV
, LoadvwCustomerFirmographics.UniverseFILETYPE AS UniverseFILETYPE
, LoadvwCustomerFirmographics.dss_record_source AS dss_record_source
, LoadvwCustomerFirmographics.dss_load_date AS dss_load_date
, CAST(NULL AS integer) AS dss_source_system_key
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSCreateTime
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSUpdateTime
FROM [TABLEOWNER].[LoadvwCustomerFirmographics] LoadvwCustomerFirmographics
LEFT OUTER JOIN [TABLEOWNER].[AccountFirmographic] AccountFirmographic
ON LoadvwCustomerFirmographics.CustomerId = AccountFirmographic.ForceId
CROSS JOIN ( SELECT MAX(AccountFirmographicKey) AS maxkey FROM [TABLEOWNER].[AccountFirmographic]) AS maxkey
WHERE AccountFirmographic.ForceId IS NULL
UNION ALL
-- CHANGED ROWS
SELECT DISTINCT 0 AS MAXKEY
, LoadvwCustomerFirmographics.Level1ForceId AS Level1ForceId
, LoadvwCustomerFirmographics.CustomerId AS ForceId
, LoadvwCustomerFirmographics.Level1Segmentation AS Level1StrategicAccountReason
, LoadvwCustomerFirmographics.Level1AccountType AS Level1AccountType
, LoadvwCustomerFirmographics.ClassificationId AS AccountType
, LoadvwCustomerFirmographics.RecordDesignation AS RecordDesignation
, LoadvwCustomerFirmographics.UniverseName AS UniverseName
, LoadvwCustomerFirmographics.UniverseProvider AS UniverseProvider
, LoadvwCustomerFirmographics.UniverseUltimateParentId AS UniverseUltimateParentId
, LoadvwCustomerFirmographics.UniverseId AS UniverseId
, LoadvwCustomerFirmographics.CustomerSize AS CustomerSize
, LoadvwCustomerFirmographics.CustomerSizeCode AS CustomerSizeCode
, LoadvwCustomerFirmographics.CustomerSizeSuffix AS CustomerSizeSuffix
, LoadvwCustomerFirmographics.UniverseInstitutionName AS UniverseInstitutionName
, LoadvwCustomerFirmographics.UniverseInstitutionState AS UniverseInstitutionState
, LoadvwCustomerFirmographics.UniverseInstitutionCity AS UniverseInstitutionCity
, LoadvwCustomerFirmographics.LocationSize AS LocationSize
, LoadvwCustomerFirmographics.LocationUltimateParentId AS LocationUltimateParentId
, LoadvwCustomerFirmographics.LocationParentId AS LocationParentId
, LoadvwCustomerFirmographics.LocationId AS LocationId
, LoadvwCustomerFirmographics.LocationInstitutionName AS LocationInstitutionName
, LoadvwCustomerFirmographics.LocationSizeCode AS LocationSizeCode
, LoadvwCustomerFirmographics.LocationGROUP AS LocationGROUP
, LoadvwCustomerFirmographics.LocationUNIV AS LocationUNIV
, LoadvwCustomerFirmographics.LocationFILETYPE AS LocationFILETYPE
, LoadvwCustomerFirmographics.LocationAddress AS LocationAddress
, LoadvwCustomerFirmographics.LocationCity AS LocationCity
, LoadvwCustomerFirmographics.LocationState AS LocationState
, LoadvwCustomerFirmographics.LocationZip AS LocationZip
, LoadvwCustomerFirmographics.UniverseCustomerSizeSuffix AS UniverseCustomerSizeSuffix
, LoadvwCustomerFirmographics.UniverseParentId AS UniverseParentId
, LoadvwCustomerFirmographics.UniverseGROUP AS UniverseGROUP
, LoadvwCustomerFirmographics.UniverseUNIV AS UniverseUNIV
, LoadvwCustomerFirmographics.UniverseFILETYPE AS UniverseFILETYPE
, AccountFirmographic.dss_record_source AS dss_record_source
, AccountFirmographic.dss_load_date AS dss_load_date
, CAST(NULL AS integer) AS dss_source_system_key
, AccountFirmographic.DSSCreateTime AS DSSCreateTime
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSUpdateTime
FROM [TABLEOWNER].[LoadvwCustomerFirmographics] LoadvwCustomerFirmographics
INNER JOIN [TABLEOWNER].[AccountFirmographic] AccountFirmographic
ON LoadvwCustomerFirmographics.CustomerId = AccountFirmographic.ForceId
-- EXCEPT FOR EXISTING ROWS TO NOT PROCESS UNCHANGED ROWS
MINUS
SELECT 0 AS MAXKEY
, AccountFirmographic.Level1ForceId
, AccountFirmographic.ForceId
, AccountFirmographic.Level1StrategicAccountReason
, AccountFirmographic.Level1AccountType
, AccountFirmographic.AccountType
, AccountFirmographic.RecordDesignation
, AccountFirmographic.UniverseName
, AccountFirmographic.UniverseProvider
, AccountFirmographic.UniverseUltimateParentId
, AccountFirmographic.UniverseId
, AccountFirmographic.CustomerSize
, AccountFirmographic.CustomerSizeCode
, AccountFirmographic.CustomerSizeSuffix
, AccountFirmographic.UniverseInstitutionName
, AccountFirmographic.UniverseInstitutionState
, AccountFirmographic.UniverseInstitutionCity
, AccountFirmographic.LocationSize
, AccountFirmographic.LocationUltimateParentId
, AccountFirmographic.LocationParentId
, AccountFirmographic.LocationId
, AccountFirmographic.LocationInstitutionName
, AccountFirmographic.LocationSizeCode
, AccountFirmographic.LocationGROUP
, AccountFirmographic.LocationUNIV
, AccountFirmographic.LocationFILETYPE
, AccountFirmographic.LocationAddress
, AccountFirmographic.LocationCity
, AccountFirmographic.LocationState
, AccountFirmographic.LocationZip
, AccountFirmographic.UniverseCustomerSizeSuffix
, AccountFirmographic.UniverseParentId
, AccountFirmographic.UniverseGROUP
, AccountFirmographic.UniverseUNIV
, AccountFirmographic.UniverseFILETYPE
, AccountFirmographic.dss_record_source
, AccountFirmographic.dss_load_date
, AccountFirmographic.dss_source_system_key
, AccountFirmographic.DSSCreateTime
, CAST(CURRENT_TIMESTAMP AS TIMESTAMP) AS DSSUpdateTime
FROM [TABLEOWNER].[AccountFirmographic] AccountFirmographic
) AS src
ON AccountFirmographic.ForceId = src.ForceId
WHEN MATCHED THEN UPDATE
SET AccountFirmographic.Level1ForceId = src.Level1ForceId
, AccountFirmographic.Level1StrategicAccountReason = src.Level1StrategicAccountReason
, AccountFirmographic.Level1AccountType = src.Level1AccountType
, AccountFirmographic.AccountType = src.AccountType
, AccountFirmographic.RecordDesignation = src.RecordDesignation
, AccountFirmographic.UniverseName = src.UniverseName
, AccountFirmographic.UniverseProvider = src.UniverseProvider
, AccountFirmographic.UniverseUltimateParentId = src.UniverseUltimateParentId
, AccountFirmographic.UniverseId = src.UniverseId
, AccountFirmographic.CustomerSize = src.CustomerSize
, AccountFirmographic.CustomerSizeCode = src.CustomerSizeCode
, AccountFirmographic.CustomerSizeSuffix = src.CustomerSizeSuffix
, AccountFirmographic.UniverseInstitutionName = src.UniverseInstitutionName
, AccountFirmographic.UniverseInstitutionState = src.UniverseInstitutionState
, AccountFirmographic.UniverseInstitutionCity = src.UniverseInstitutionCity
, AccountFirmographic.LocationSize = src.LocationSize
, AccountFirmographic.LocationUltimateParentId = src.LocationUltimateParentId
, AccountFirmographic.LocationParentId = src.LocationParentId
, AccountFirmographic.LocationId = src.LocationId
, AccountFirmographic.LocationInstitutionName = src.LocationInstitutionName
, AccountFirmographic.LocationSizeCode = src.LocationSizeCode
, AccountFirmographic.LocationGROUP = src.LocationGROUP
, AccountFirmographic.LocationUNIV = src.LocationUNIV
, AccountFirmographic.LocationFILETYPE = src.LocationFILETYPE
, AccountFirmographic.LocationAddress = src.LocationAddress
, AccountFirmographic.LocationCity = src.LocationCity
, AccountFirmographic.LocationState = src.LocationState
, AccountFirmographic.LocationZip = src.LocationZip
, AccountFirmographic.UniverseCustomerSizeSuffix = src.UniverseCustomerSizeSuffix
, AccountFirmographic.UniverseParentId = src.UniverseParentId
, AccountFirmographic.UniverseGROUP = src.UniverseGROUP
, AccountFirmographic.UniverseUNIV = src.UniverseUNIV
, AccountFirmographic.UniverseFILETYPE = src.UniverseFILETYPE
, AccountFirmographic.dss_record_source = src.dss_record_source
, AccountFirmographic.dss_load_date = src.dss_load_date
, AccountFirmographic.dss_source_system_key = src.dss_source_system_key
, AccountFirmographic.DSSUpdateTime = src.DSSUpdateTime
WHEN NOT MATCHED THEN INSERT
( AccountFirmographicKey
, Level1ForceId
, ForceId
, Level1StrategicAccountReason
, Level1AccountType
, AccountType
, RecordDesignation
, UniverseName
, UniverseProvider
, UniverseUltimateParentId
, UniverseId
, CustomerSize
, CustomerSizeCode
, CustomerSizeSuffix
, UniverseInstitutionName
, UniverseInstitutionState
, UniverseInstitutionCity
, LocationSize
, LocationUltimateParentId
, LocationParentId
, LocationId
, LocationInstitutionName
, LocationSizeCode
, LocationGROUP
, LocationUNIV
, LocationFILETYPE
, LocationAddress
, LocationCity
, LocationState
, LocationZip
, UniverseCustomerSizeSuffix
, UniverseParentId
, UniverseGROUP
, UniverseUNIV
, UniverseFILETYPE
, dss_record_source
, dss_load_date
, dss_source_system_key
, DSSCreateTime
, DSSUpdateTime
)
VALUES
( ROW_NUMBER() OVER(ORDER BY NVL(src.maxkey,0) DESC, src.ForceId) + NVL(src.maxkey,0)
, src.Level1ForceId
, src.ForceId
, src.Level1StrategicAccountReason
, src.Level1AccountType
, src.AccountType
, src.RecordDesignation
, src.UniverseName
, src.UniverseProvider
, src.UniverseUltimateParentId
, src.UniverseId
, src.CustomerSize
, src.CustomerSizeCode
, src.CustomerSizeSuffix
, src.UniverseInstitutionName
, src.UniverseInstitutionState
, src.UniverseInstitutionCity
, src.LocationSize
, src.LocationUltimateParentId
, src.LocationParentId
, src.LocationId
, src.LocationInstitutionName
, src.LocationSizeCode
, src.LocationGROUP
, src.LocationUNIV
, src.LocationFILETYPE
, src.LocationAddress
, src.LocationCity
, src.LocationState
, src.LocationZip
, src.UniverseCustomerSizeSuffix
, src.UniverseParentId
, src.UniverseGROUP
, src.UniverseUNIV
, src.UniverseFILETYPE
, src.dss_record_source
, src.dss_load_date
, src.dss_source_system_key
, src.DSSCreateTime
, src.DSSUpdateTime
)
;
"@
$SnowflakeResult = Run-Snowflake-RedSQL -sql $sql -dsn ${env:WSL_TGT_DSN} -uid ${env:WSL_TGT_USER} -pwd ${env:WSL_TGT_PWD} -failureMsg "Failed data merge into table AccountFirmographic step $step" -odbcConn $tgtConn
$mergeCount = $mergeCount + $SnowflakeResult[1]
$return_Msg = $SnowflakeResult[2]
}
#--=====================================================
#-- All Done report the results
#--=====================================================
$step = 400
#-- WsWrkTask call to updated row counts in Ws_Wrk_Task_Run/Log
$metaResult = WsWrkTask -Replaced $mergeCount
if ( $metaResult -lt -1 ){
$status = -2
$return_Msg = "Failure executing WsWrkTask. $mergeCount records merged."
}
#-- Work out return message
if ( $status -eq 1 ) {
$return_Msg = "AccountFirmographic updated. "
$return_Msg += "$mergeCount records merged. "
}
$status
$return_Msg
$tgtConn.Dispose()
Return to Top