I have a problem with some Oracle SQL code I'm running and I can't for the life of me see what the problem is. I have this code that works:
create or replace
PROCEDURE XP_CLOSE_SCORECARD (pSC_VER_ID IN NUMBER)
AS
---------------------------------------------------------------------------------------------------
-- XP_CLOSE_SCORECARD
--
-- DESCRIPTION:
-- Called when SC wkflw is marked complete.
--
-- CALLED BY:
-- Scorecard workflow
--
-- MODIFICATION HISTORY:
-- 2008-06-24 CJW Setup.
-- 2008-08-22 CJW Change to pull from views.
-- 2009-02-09 CJW Enhancements.
-- 2010-08-18 EC BVD update. 'STD_INDS_CODE' changed to SIC_COD9 code
-- 2013-01-17 EC Version 7 updates. Remvoed logging to another table as cuases problems for Smartest to Support and investigate error.
---------------------------------------------------------------------------------------------------
vCP_ID NUMBER;
vErrCode NUMBER(38);
vErrMsg VARCHAR2(250);
vPROCNAME VARCHAR2(250);
BEGIN
vPROCNAME := 'XP_CLOSE_SCORECARD';
---------------------------------------------------------------------------------------------------
-- Copy data to OBX_CRM
---------------------------------------------------------------------------------------------------
select CP_ID INTO vCP_ID from sc_version sv inner join scorecard sc on sc.id = sv.scorecard_id
and sv.id = pSC_VER_ID;
INSERT INTO OBX_CRM_V2
(
OBX_CRM_ID,
CPTY_KEY,
CPTY_NAME,
DB_NUMBER,
SIC_CODE,
CPTY_SCORE,
APPROVED_LIMIT,
SUGGESTED_LIMIT,
LIMIT_EXPIRATION,
LIMIT_COMMENT,
APPROVAL_0,APPROVAL_0_DT,
APPROVAL_1,APPROVAL_1_DT,
APPROVAL_2,APPROVAL_2_DT,
APPROVAL_3,APPROVAL_3_DT,
APPROVAL_4,APPROVAL_4_DT,
APPROVAL_5,APPROVAL_5_DT,
APPROVAL_6,APPROVAL_6_DT,
APPROVAL_7,APPROVAL_7_DT,
CRDT_INS,
CRDT_INS_LIMIT,
CRDT_INS_COVERAGE,
CRDT_INS_EXPIRY,
CPTY_COMMENT,
CREATED_BY,
CREATED_TS
)
SELECT
NULL AS OBX_CRM_ID,
TO_CHAR(CP.COUNTERPARTY_NUMBER) AS CPTY_KEY,
TO_CHAR(CP.NAME) AS CPTY_NAME,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10079) AS DB_NUMBER,
(SELECT TO_CHAR(MAX(D.ELEMENT_VALUE)) FROM DNB_COMPANY_DETAIL D WHERE D.COMP_ID = CP.ID
AND D.ELEMENT_CODE = 'SIC_COD9') AS SIC_CODE,
(SELECT TO_CHAR(SCORE) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS CPTY_SCORE,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10071) AS APPROVED_LIMIT,
(SELECT TO_NUMBER(SUGG_CRED_LIM) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS SUGGESTED_LIMIT,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10086) AS LIMIT_EXPIRATION,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRT_APC') AND OWNER_ID = CP.ID) AS LIMIT_COMMENT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10077) AS APPROVAL_0,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10088) AS APPROVAL_0_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10130) AS APPROVAL_1,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10131) AS APPROVAL_1_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10072) AS APPROVAL_2,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10085) AS APPROVAL_2_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10070) AS APPROVAL_3,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10083) AS APPROVAL_3_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10075) AS APPROVAL_4,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10082) AS APPROVAL_4_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10078) AS APPROVAL_5,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10081) AS APPROVAL_5_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10073) AS APPROVAL_6,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10080) AS APPROVAL_6_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10128) AS APPROVAL_7,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10127) AS APPROVAL_7_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS') AND OWNER_ID = CP.ID) AS CRDT_INS,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_AMT') AND OWNER_ID = CP.ID) AS CRDT_INS_LIMIT,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_COV') AND OWNER_ID = CP.ID) AS CRDT_INS_COVERAGE,
(SELECT TO_DATE(VALUE_DATE) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_VAL') AND OWNER_ID = CP.ID) AS CRDT_INS_EXPIRY,
TO_CHAR(CP.COMMENTS) AS CPTY_COMMENT,
TO_CHAR(vPROCNAME) AS CREATED_BY,
SYSDATE AS CREATED_TS
FROM
COUNTERPARTY CP
WHERE
CP.ID = vCP_ID
;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
END;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
And this code that doesn't:
create or replace
PROCEDURE XP_CLOSE_SCORECARD (pSC_VER_ID IN NUMBER)
AS
---------------------------------------------------------------------------------------------------
-- XP_CLOSE_SCORECARD
--
-- DESCRIPTION:
-- Called when SC wkflw is marked complete.
--
-- CALLED BY:
-- Scorecard workflow
--
-- MODIFICATION HISTORY:
-- 2008-06-24 CJW Setup.
-- 2008-08-22 CJW Change to pull from views.
-- 2009-02-09 CJW Enhancements.
-- 2010-08-18 EC BVD update. 'STD_INDS_CODE' changed to SIC_COD9 code
-- 2013-01-17 EC Version 7 updates. Remvoed logging to another table as cuases problems for Smartest to Support and investigate error.
---------------------------------------------------------------------------------------------------
vCP_ID NUMBER;
vErrCode NUMBER(38);
vErrMsg VARCHAR2(250);
vPROCNAME VARCHAR2(250);
BEGIN
vPROCNAME := 'XP_CLOSE_SCORECARD';
---------------------------------------------------------------------------------------------------
-- Copy data to OBX_CRM
---------------------------------------------------------------------------------------------------
select CP_ID INTO vCP_ID from sc_version sv inner join scorecard sc on sc.id = sv.scorecard_id
and sv.id = pSC_VER_ID;
INSERT INTO OBX_CRM_V2
(
OBX_CRM_ID,
CPTY_KEY,
CPTY_NAME,
DB_NUMBER,
SIC_CODE,
CPTY_SCORE,
APPROVED_LIMIT,
SUGGESTED_LIMIT,
LIMIT_EXPIRATION,
LIMIT_COMMENT,
APPROVAL_0,APPROVAL_0_DT,
APPROVAL_1,APPROVAL_1_DT,
APPROVAL_2,APPROVAL_2_DT,
APPROVAL_3,APPROVAL_3_DT,
APPROVAL_4,APPROVAL_4_DT,
APPROVAL_5,APPROVAL_5_DT,
APPROVAL_6,APPROVAL_6_DT,
APPROVAL_7,APPROVAL_7_DT,
CRDT_INS,
CRDT_INS_LIMIT,
CRDT_INS_COVERAGE,
CRDT_INS_EXPIRY,
CPTY_COMMENT,
CREATED_BY,
CREATED_TS,
S_RAIVA,
S_RCSDA,
S_RCEDA,
S_ARPT,
S_ARPM,
S_GAEVA
)
SELECT
NULL AS OBX_CRM_ID,
TO_CHAR(CP.COUNTERPARTY_NUMBER) AS CPTY_KEY,
TO_CHAR(CP.NAME) AS CPTY_NAME,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10079) AS DB_NUMBER,
(SELECT TO_CHAR(MAX(D.ELEMENT_VALUE)) FROM DNB_COMPANY_DETAIL D WHERE D.COMP_ID = CP.ID
AND D.ELEMENT_CODE = 'SIC_COD9') AS SIC_CODE,
(SELECT TO_CHAR(SCORE) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS CPTY_SCORE,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10071) AS APPROVED_LIMIT,
(SELECT TO_NUMBER(SUGG_CRED_LIM) FROM SC_VERSION S WHERE S.ID = pSC_VER_ID ) AS SUGGESTED_LIMIT,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10086) AS LIMIT_EXPIRATION,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRT_APC') AND OWNER_ID = CP.ID) AS LIMIT_COMMENT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10077) AS APPROVAL_0,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10088) AS APPROVAL_0_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10130) AS APPROVAL_1,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10131) AS APPROVAL_1_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10072) AS APPROVAL_2,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10085) AS APPROVAL_2_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10070) AS APPROVAL_3,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10083) AS APPROVAL_3_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10075) AS APPROVAL_4,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10082) AS APPROVAL_4_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10078) AS APPROVAL_5,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10081) AS APPROVAL_5_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10073) AS APPROVAL_6,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10080) AS APPROVAL_6_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10128) AS APPROVAL_7,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10127) AS APPROVAL_7_DT,
(SELECT TO_CHAR(VALUE_STRING) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS') AND OWNER_ID = CP.ID) AS CRDT_INS,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_AMT') AND OWNER_ID = CP.ID) AS CRDT_INS_LIMIT,
(SELECT CASE
WHEN value_float IS NULL
THEN TO_NUMBER(REPLACE(value_string, ',',''))
ELSE TO_NUMBER(value_float)
END FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_COV') AND OWNER_ID = CP.ID) AS CRDT_INS_COVERAGE,
(SELECT TO_DATE(VALUE_DATE) FROM ATTRIBUTE_VALUE V
WHERE ATTRIBUTE_ID = (SELECT ID FROM ATTRIBUTE A WHERE NAME = 'S_CRDT_INS_VAL') AND OWNER_ID = CP.ID) AS CRDT_INS_EXPIRY,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10286) AS S_RCSDA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10287) AS S_RCEDA,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10288) AS S_ARPT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10289) AS S_ARPM,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA,
TO_CHAR(CP.COMMENTS) AS CPTY_COMMENT,
TO_CHAR(vPROCNAME) AS CREATED_BY,
SYSDATE AS CREATED_TS
FROM
COUNTERPARTY CP
WHERE
CP.ID = vCP_ID
;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
END;
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
The difference between the two is the addition of:
S_RAIVA,
S_RCSDA,
S_RCEDA,
S_ARPT,
S_ARPM,
S_GAEVA
Near the top, and the below near the bottom.
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10285) AS S_RAIVA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10286) AS S_RCSDA,
(SELECT TO_DATE(VALUE_DATE) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10287) AS S_RCEDA,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10288) AS S_ARPT,
(SELECT TO_CHAR(VALUE_STRING) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10289) AS S_ARPM,
(SELECT TO_NUMBER(VALUE_NUMBER) FROM SC_ELEMENT S WHERE S.SC_VERSION_ID = pSC_VER_ID AND S.DATA_ELEMENT_ID = 10290) AS S_GAEVA,
I get the error:
Error(38,1): PL/SQL: ORA-00932: inconsistent datatypes: expected DATE got NUMBER
when I try to compile it but I don't understand why. Line 38 is just:
INSERT INTO OBX_CRM_V2 and doesn't give me any explanation of where the problem exists, and the extra lines conform to the same syntax as the code that works, as far as I can see. Any help would be greatly appreciated as I'm a complete novice at this.
