Updating Preferred Contact Method on Legacy Values
In this Oracle Utilities Customer to Meter release, a new feature is introduced that is used by certain system processes to determine the preferred contact method (person phone/email) being used.
If moving to using person contacts, some contact method values on case and customer contact are suppressed and the existing records are displayed as “<invalid value>”.
These scripts update the preferred contact method on cases and customer contacts from legacy values to person contact.
1. Update Case Preferred Contact Method from Email to Primary Email Person Contact.
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
2. Update Case Preferred Contact Method from Fax to Primary Fax Person Contact.
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
3. Update Case Preferred Contact Method from Phone to Primary Phone Person Contact.
UPDATE CI_CASE X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
AND X.CASE_ID = A.CASE_ID
)
WHERE X.CASE_ID IN
(SELECT A.CASE_ID
FROM CI_CASE A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.CONTACT_PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
)
--AND X.CASE_ID BETWEEN '0000000000' AND '9999999999';
AND X.CASE_ID BETWEEN '0000000000' AND '0000000000';
4. Update Customer Contact Preferred Contact Method from Email to Primary Email Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'EM'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'EMAIL'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
5. Update Customer Contact Preferred Contact Method from Fax to Primary Fax Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'FAX'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'FAX'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
6. Update Customer Contact Preferred Contact Method from Phone to Primary Phone Person Contact.
UPDATE CI_CC X
SET X.CONTACT_METH_FLG = 'C1PC',
X.C1_CONTACT_ID =
(SELECT B.C1_CONTACT_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
AND X.CC_ID = A.CC_ID
)
WHERE X.CC_ID IN
(SELECT A.CC_ID
FROM CI_CC A,
CISADM.C1_PER_CONTDET B,
CISADM.C1_COMM_RTE_TYPE C
WHERE CONTACT_METH_FLG = 'PH'
AND A.PER_ID = B.PER_ID
AND B.COMM_RTE_TYPE_CD = C.COMM_RTE_TYPE_CD
AND B.CND_PRIMARY_FLG = 'C1YS'
AND C.COMM_RTE_METH_FLG = 'PHONE'
)
--AND X.CC_ID BETWEEN '0000000000' AND '9999999999';
AND X.CC_ID BETWEEN '0000000000' AND '0000000000';
7. Identify the remaining cases that need to be investigated manually.
SELECT *
FROM CI_CASE
WHERE CONTACT_METH_FLG <> ' '
AND CONTACT_METH_FLG NOT IN ('N/A','POST','C1PC');
8. Identify the remaining Customer Contacts that need to be investigated manually.
SELECT *
FROM CI_CC
WHERE CONTACT_METH_FLG <> ' '
AND CONTACT_METH_FLG NOT IN ('N/A','POST','C1PC');