Updating Customer Contact Account and Premise
In this Oracle Utilities Digital Asset Management release, the customer contact capability has been enhanced so that customer contacts can be linked to a person, account and/or premise. This section contains sample SQL that upgrading implementations can use to update the account ID and premise ID fields on existing customer contacts.
All Oracle Utilities Digital Asset Management system processes that create customer contacts have been enhanced to populate account and/or premise. Not all of these areas retain a link to the customer contact created and the SQL provided is just a sample.
These scripts update Customer Contact Account and Premise from the following sources:
• Customer Contact Characteristics
• Collection Events
• Severance Events
• Write off Events
• Overdue Process Logs
The other Oracle Utilities Digital Asset Management process enhanced are algorithm types delivered for the below plug-in spots. Examine your organization’s customer contacts to determine if you should update customer contacts created from the following:
• FA Remark Algorithm
• Meter read Remark Algorithm
• Case Type Enter Status Algorithm
• Customer Class Order Completion
• SA Type SA Stop
• SA Type - SA Activation
• Service Credit Membership Type - Membership Activation
• Service Credit Membership Type - Membership Creation
• Campaign - Order Completion
• Lead Event Type (BO) - Lead Event Completion
• Notification Type (BO) - Create Notification
Since existing customer contact records are all person-based, all account updates should occur before premise updates to ensure the premise is associated with the account.
These are intended to be run in the following order:
1. Update Account ID from characteristic.
Note the following about this SQL:
• This cannot handle two characteristic types that are both linked to Acct FK Ref with different char values. If this exists such as FROM_ACCT and TO_ACCT with different values, this will not update.
• Limited to accounts linked to CC person to not violate new CC validations.
• Change FK Ref if your implementation has introduced new FK Ref values for ACCT.
UPDATE CI_CC X
SET X.ACCT_ID =
(SELECT DISTINCT(a.CHAR_VAL_FK1)
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'ACCT'
AND a.CC_ID = X.CC_ID
AND EXISTS
(SELECT'x'
FROM CI_ACCT_PER D
WHERE D.PER_ID = C.PER_ID
AND D.ACCT_ID = a.CHAR_VAL_FK1
)
)
WHERE CC_ID =
(SELECT A.CC_ID
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'ACCT'
AND a.CC_ID = X.CC_ID
AND EXISTS
(SELECT'x'
FROM CI_ACCT_PER D
WHERE D.PER_ID = C.PER_ID
AND D.ACCT_ID = a.CHAR_VAL_FK1
)
)
AND 1 =
(SELECT COUNT (DISTINCT a.CHAR_VAL_FK1)
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'ACCT'
AND a.CC_ID = X.CC_ID
)
AND X.ACCT_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
2. Update Account ID from Coll event.
UPDATE CI_CC X
SET X.ACCT_ID =
(SELECT a.acct_id
FROM CI_COLL_PROC A,
CI_COLL_EVT_CC C
WHERE a.coll_proc_id = c.coll_proc_ID
AND c.cc_id = x.cc_id
)
WHERE X.CC_ID IN
(SELECT CC_ID FROM CI_COLL_EVT_CC
)
AND X.ACCT_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
3. Update Account ID from Sev event.
UPDATE CI_CC X
SET X.ACCT_ID =
(SELECT a.acct_id
FROM CI_SA A,
CI_SEV_PROC B,
CI_SEV_EVT_CC C
WHERE a.SA_ID = B.SA_ID
AND B.sev_proc_id = c.sev_proc_id
AND c.cc_id = x.cc_id
)
WHERE X.CC_ID IN
(SELECT CC_ID FROM CI_SEV_EVT_CC
)
AND X.ACCT_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
4. Update Premise ID from Sev event.
Using premise linked to SP linked to FA linked to same Sev Proc First.
UPDATE CI_CC X
SET X.PREM_ID =
(SELECT DISTINCT h.prem_id
FROM CI_SEV_EVT_CC D,
CI_SEV_EVT_FA E,
CI_FA G,
CI_SP H
WHERE D.SEV_PROC_ID = E.SEV_PROC_ID
AND e.fa_id = g.fa_id
AND g.sp_id = h.sp_id
AND d.cc_id = x.cc_id
)
WHERE X.CC_ID IN
(SELECT CC_ID
FROM CI_SEV_EVT_CC D,
CI_SEV_EVT_FA E
WHERE D.SEV_PROC_ID = E.SEV_PROC_ID
)
AND X.PREM_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
5. Update Premise ID from Sev event.
Using premise linked to SA via Char Premise second.
Note: There is no SQL that will try to find a premise via SASP. The likelihood of more than one premise is too high.
UPDATE CI_CC X
SET X.PREM_ID =
(SELECT NVL(a.char_prem_id,NULL)
FROM CI_SA A,
CI_SEV_PROC B,
CI_SEV_EVT_CC C
WHERE a.SA_ID = B.SA_ID
AND B.sev_proc_id = c.sev_proc_id
AND A.CHAR_PREM_ID <> ' '
AND c.cc_id = x.cc_id
)
WHERE X.CC_ID IN
(SELECT CC_ID FROM CI_SEV_EVT_CC
)
AND X.PREM_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
6. Update Account ID from WO event.
UPDATE CI_CC X
SET X.ACCT_ID =
(SELECT a.acct_id
FROM CI_WO_PROC A,
CI_WO_EVT_CC C
WHERE a.wo_proc_id = c.wo_proc_ID
AND c.cc_id = x.cc_id
)
WHERE X.CC_ID IN
(SELECT CC_ID FROM CI_WO_EVT_CC
)
AND X.ACCT_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
7. Update Account ID from Overdue and Cut Processes via the Overdue Process Logs.
NOTE: You must hardcode the Char Type Code your implementation has introduced.
UPDATE CI_CC X
SET X.ACCT_ID =
(SELECT b.acct_id
FROM CI_OD_PROC_LOG A,
CI_OD_PROC B
WHERE a.char_type_cd = 'CCID'
AND a.OD_PROC_ID = B.OD_PROC_ID
AND trim(a.char_val_fk1) = x.cc_id
)
WHERE X.CC_ID IN
(SELECT a.char_val_fk1
FROM CI_OD_PROC_LOG A,
CI_OD_PROC B
WHERE a.char_type_cd = 'CCID'
AND a.OD_PROC_ID = B.OD_PROC_ID
)
AND X.ACCT_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
8. Update Premise ID from CC Char.
Some notes about this SQL:
• This cannot handle two characteristic types that are both linked to Prem FK Ref with different char values. If this exists such as OLD_PREM and NEW_PREM with different values, this will not update.
• Limited to premise associated with account if acct is populated on the CC to not violate new CC validations.
• Change FK Ref if your implementation has introduced new FK Ref values for PREM.
UPDATE CI_CC X
SET X.PREM_ID =
(SELECT DISTINCT(a.CHAR_VAL_FK1)
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'PREM'
AND a.CC_ID = X.CC_ID
AND (C.ACCT_ID IS NULL
OR C.ACCT_ID IS NOT NULL
AND a.CHAR_VAL_FK1 IN
(SELECT E.char_prem_id FROM CI_SA E WHERE E.ACCT_ID = C.ACCT_ID
UNION
SELECT H.PREM_ID
FROM CI_SA F,
CI_SA_SP G,
CI_SP H
WHERE f.ACCT_ID = C.ACCT_ID
AND F.SA_ID = G.SA_ID
AND G.SP_ID = H.SP_ID
) )
)
WHERE CC_ID =
(SELECT A.CC_ID
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'PREM'
AND a.CC_ID = X.CC_ID
AND (C.ACCT_ID IS NULL
OR C.ACCT_ID IS NOT NULL
AND a.CHAR_VAL_FK1 IN
(SELECT E.char_prem_id FROM CI_SA E WHERE E.ACCT_ID = C.ACCT_ID
UNION
SELECT H.PREM_ID
FROM CI_SA F,
CI_SA_SP G,
CI_SP H
WHERE f.ACCT_ID = C.ACCT_ID
AND F.SA_ID = G.SA_ID
AND G.SP_ID = H.SP_ID
) )
)
AND 1 =
(SELECT COUNT (DISTINCT a.CHAR_VAL_FK1)
FROM CI_CC_CHAR A,
CI_CHAR_TYPE B,
CI_CC C
WHERE a.CHAR_TYPE_CD = B.CHAR_TYPE_CD
AND A.CC_ID = C.CC_ID
AND B.FK_REF_CD <> ' '
AND b.fk_ref_cd = 'PREM'
AND a.CC_ID = X.CC_ID
)
AND X.PREM_ID IS NULL
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';