Liquibase migration with oracle pl/sql function gets PLS-00103

sanjaykdalvi · January 20, 2022, 12:03pm

Dear Team,

I’m trying to add simple oracle function, in SQLDeveloper it compiles and works, but when I add it to liquibase migration, update command doesn’t give any error. However changes are not getting reflected and gives below message

Package Body XXXXAAA
Error (1022,36): PLS-00103: Encountered the symbol “end-of-life” when expecting one of the following: ;The symbol “;” was substituted fir “end-of-life” to continue.

Change Log file:

SQL File
create or replace PACKAGE BODY PKG_FINS_DASHBOARDTHRESHOLD_Test AS

PROCEDURE SP_FINSDASHTHRESHOLDINSERT
(
pMode VARCHAR:=NULL,
pDashThresholdID NUMBER:=NULL,
pDashboard VARCHAR:=NULL,
pAcquiringChannel NUMBER:=NULL,
pThresholdField VARCHAR:=NULL,
pThresholdValue VARCHAR:=NULL,
pOperators VARCHAR:=NULL,
pStatus VARCHAR:=NULL,
pAddedBy VARCHAR:=NULL,
pIpaddress VARCHAR:=NULL,
pOutValue out VARCHAR2,
pOutMsg out VARCHAR2
) As

– insert into debug values (‘PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 5 - vTXNTYPE:’ || UPPER(TRIM(vTXNTYPE)) || ’ - vDASHBOARDTYPE:’ || UPPER(TRIM(vDASHBOARDTYPE)) || ’ - vTotalField:’ || vTotalField || ’ - v_ACQID :’ || v_ACQID || ’ - vDASHBOARDTYPE :’ || vDASHBOARDTYPE);
– commit;
BEGIN
SELECT HRS0,HRS1,HRS2,HRS3,HRS4,HRS5,HRS6,HRS7,HRS8,HRS9,HRS10,HRS11,HRS12,HRS13,HRS14,HRS15,HRS16,HRS17,HRS18,HRS19,HRS20,HRS21,HRS22,HRS23
INTO vTotal0,vTotal1,vTotal2,vTotal3,vTotal4,vTotal5,vTotal6,vTotal7,vTotal8,vTotal9,vTotal10,vTotal11,vTotal12,vTotal13,vTotal14,vTotal15,vTotal16,vTotal17,vTotal18,vTotal19,vTotal20,vTotal21,vTotal22,vTotal23
FROM FINS_DASHBOARD_VIEW WHERE UPPER(TRIM(ACQ_CHANNEL)) = UPPER(TRIM(v_ACQID))
AND UPPER(TRIM(TXNTYPE))=UPPER(TRIM(vTXNTYPE)) AND UPPER(TRIM(DASHBOARDTYPE))=UPPER(TRIM(vDASHBOARDTYPE)) AND ROW_LABEL = vTotalField;–Total OTP Hit
EXCEPTION
WHEN OTHERS THEN
V_MSQLERRM := SUBSTR(SQLERRM, 1, 1000);
– insert into debug values ('PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 5.1 '||V_MSQLERRM);
– commit;
END;

--insert into debug values ('PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 6');
--commit; 
BEGIN
  SELECT HRS0,HRS1,HRS2,HRS3,HRS4,HRS5,HRS6,HRS7,HRS8,HRS9,HRS10,HRS11,HRS12,HRS13,HRS14,HRS15,HRS16,HRS17,HRS18,HRS19,HRS20,HRS21,HRS22,HRS23
  INTO vSuccess0,vSuccess1,vSuccess2,vSuccess3,vSuccess4,vSuccess5,vSuccess6,vSuccess7,vSuccess8,vSuccess9,vSuccess10,vSuccess11,vSuccess12,vSuccess13,vSuccess14,vSuccess15,vSuccess16,vSuccess17,vSuccess18,vSuccess19,vSuccess20,vSuccess21,vSuccess22,vSuccess23
  FROM FINS_DASHBOARD_VIEW WHERE UPPER(TRIM(ACQ_CHANNEL)) = UPPER(TRIM(v_ACQID))
  AND UPPER(TRIM(TXNTYPE))=UPPER(TRIM(vTXNTYPE)) AND UPPER(TRIM(DASHBOARDTYPE))=UPPER(TRIM(vDASHBOARDTYPE)) AND ROW_LABEL = vSuccessField;--Successful OTP
EXCEPTION
WHEN OTHERS THEN
V_MSQLERRM := SUBSTR(SQLERRM, 1, 1000);
--insert into debug values ('PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 6.1 '||V_MSQLERRM);
--commit; 
END;

--insert into debug values ('PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 7');
--commit; 

vDecline0 := TO_NUMBER(vTotal0) - TO_NUMBER(vSuccess0);
vDecline1 := TO_NUMBER(vTotal1) - TO_NUMBER(vSuccess1);
vDecline2 := TO_NUMBER(vTotal2) - TO_NUMBER(vSuccess2);
vDecline3 := TO_NUMBER(vTotal3) - TO_NUMBER(vSuccess3);
vDecline4 := TO_NUMBER(vTotal4) - TO_NUMBER(vSuccess4);
vDecline5 := TO_NUMBER(vTotal5) - TO_NUMBER(vSuccess5);
vDecline6 := TO_NUMBER(vTotal6) - TO_NUMBER(vSuccess6);
vDecline7 := TO_NUMBER(vTotal7) - TO_NUMBER(vSuccess7);
vDecline8 := TO_NUMBER(vTotal8) - TO_NUMBER(vSuccess8);
vDecline9 := TO_NUMBER(vTotal9) - TO_NUMBER(vSuccess9);
vDecline10 := TO_NUMBER(vTotal10) - TO_NUMBER(vSuccess10);
vDecline11 := TO_NUMBER(vTotal11) - TO_NUMBER(vSuccess11);
vDecline12 := TO_NUMBER(vTotal12) - TO_NUMBER(vSuccess12);
vDecline13 := TO_NUMBER(vTotal13) - TO_NUMBER(vSuccess13);
vDecline14 := TO_NUMBER(vTotal14) - TO_NUMBER(vSuccess14);
vDecline15 := TO_NUMBER(vTotal15) - TO_NUMBER(vSuccess15);
vDecline16 := TO_NUMBER(vTotal16) - TO_NUMBER(vSuccess16);
vDecline17 := TO_NUMBER(vTotal17) - TO_NUMBER(vSuccess17);
vDecline18 := TO_NUMBER(vTotal18) - TO_NUMBER(vSuccess18);
vDecline19 := TO_NUMBER(vTotal19) - TO_NUMBER(vSuccess19);
vDecline20 := TO_NUMBER(vTotal20) - TO_NUMBER(vSuccess20);
vDecline21 := TO_NUMBER(vTotal21) - TO_NUMBER(vSuccess21);
vDecline22 := TO_NUMBER(vTotal22) - TO_NUMBER(vSuccess22);
vDecline23 := TO_NUMBER(vTotal23) - TO_NUMBER(vSuccess23);

–insert into debug values (‘PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM vTotal12’ || vTotal12 || ‘| vSuccess12:’ || vSuccess12);

  OPEN P_DASHBOARD1 FOR
  SELECT 
  DMFLD.FIELDNAME AS "CASE",

– HRS0 AS “0”,case when (vTotal0=‘0’ OR vDecline0=‘0’) then 0 when vSuccess0=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS0 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS0/vTotal0)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS0/vSuccess0)*100),0) ELSE NVL(CEIL((HRS0/vDecline0)*100),0) END AS “P0”,
– HRS1 AS “1”,case when (vTotal1=‘0’ OR vDecline1=‘0’) then 0 when vSuccess1=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS1 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS1/vTotal1)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS1/vSuccess1)*100),0) ELSE NVL(CEIL((HRS1/vDecline1)*100),0) END AS “P1”,
– HRS2 AS “2”,case when (vTotal2=‘0’ OR vDecline2=‘0’) then 0 when vSuccess2=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS2 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS2/vTotal2)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS2/vSuccess2)*100),0) ELSE NVL(CEIL((HRS2/vDecline2)*100),0) END AS “P2”,
– HRS3 AS “3”,case when (vTotal3=‘0’ OR vDecline3=‘0’) then 0 when vSuccess3=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS3 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS3/vTotal3)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS3/vSuccess3)*100),0) ELSE NVL(CEIL((HRS3/vDecline3)*100),0) END AS “P3”,
– HRS4 AS “4”,case when (vTotal4=‘0’ OR vDecline4=‘0’) then 0 when vSuccess4=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS4 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS4/vTotal4)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS4/vSuccess4)*100),0) ELSE NVL(CEIL((HRS4/vDecline4)*100),0) END AS “P4”,
– HRS5 AS “5”,case when (vTotal5=‘0’ OR vDecline5=‘0’) then 0 when vSuccess5=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS5 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS5/vTotal5)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS5/vSuccess5)*100),0) ELSE NVL(CEIL((HRS5/vDecline5)*100),0) END AS “P5”,
– HRS6 AS “6”,case when (vTotal6=‘0’ OR vDecline6=‘0’) then 0 when vSuccess6=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS6 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS6/vTotal6)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS6/vSuccess6)*100),0) ELSE NVL(CEIL((HRS6/vDecline6)*100),0) END AS “P6”,
– HRS7 AS “7”,case when (vTotal7=‘0’ OR vDecline7=‘0’) then 0 when vSuccess7=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS7 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS7/vTotal7)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS7/vSuccess7)*100),0) ELSE NVL(CEIL((HRS7/vDecline7)*100),0) END AS “P7”,
– HRS8 AS “8”,case when (vTotal8=‘0’ OR vDecline8=‘0’) then 0 when vSuccess8=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS8 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS8/vTotal8)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS8/vSuccess8)*100),0) ELSE NVL(CEIL((HRS8/vDecline8)*100),0) END AS “P8”,
– HRS9 AS “9”,case when (vTotal9=‘0’ OR vDecline9=‘0’) then 0 when vSuccess9=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS9 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS9/vTotal9)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS9/vSuccess9)*100),0) ELSE NVL(CEIL((HRS9/vDecline9)*100),0) END AS “P9”,
– HRS10 AS “10”,case when (vTotal10=‘0’ OR vDecline10=‘0’) then 0 when vSuccess10=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS10 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS10/vTotal10)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS10/vSuccess10)*100),0) ELSE NVL(CEIL((HRS10/vDecline10)*100),0) END AS “P10”,
– HRS11 AS “11”,case when (vTotal11=‘0’ OR vDecline11=‘0’) then 0 when vSuccess11=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS11 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS11/vTotal11)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS11/vSuccess11)*100),0) ELSE NVL(CEIL((HRS11/vDecline11)*100),0) END AS “P11”,
– HRS12 AS “12”,case when (vTotal12=‘0’ OR vDecline12=‘0’) then 0 when vSuccess12=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS12 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS12/vTotal12)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS12/vSuccess12)*100),0) ELSE NVL(CEIL((HRS12/vDecline12)*100),0) END AS “P12”,
– HRS13 AS “13”,case when (vTotal13=‘0’ OR vDecline13=‘0’) then 0 when vSuccess13=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS13 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS13/vTotal13)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS13/vSuccess13)*100),0) ELSE NVL(CEIL((HRS13/vDecline13)*100),0) END AS “P13”,
– HRS14 AS “14”,case when (vTotal14=‘0’ OR vDecline14=‘0’) then 0 when vSuccess14=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS14 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS14/vTotal14)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS14/vSuccess14)*100),0) ELSE NVL(CEIL((HRS14/vDecline14)*100),0) END AS “P14”,
– HRS15 AS “15”,case when (vTotal15=‘0’ OR vDecline15=‘0’) then 0 when vSuccess15=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS15 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS15/vTotal15)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS15/vSuccess15)*100),0) ELSE NVL(CEIL((HRS15/vDecline15)*100),0) END AS “P15”,
– HRS16 AS “16”,case when (vTotal16=‘0’ OR vDecline16=‘0’) then 0 when vSuccess16=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS16 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS16/vTotal16)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS16/vSuccess16)*100),0) ELSE NVL(CEIL((HRS16/vDecline16)*100),0) END AS “P16”,
– HRS17 AS “17”,case when (vTotal17=‘0’ OR vDecline17=‘0’) then 0 when vSuccess17=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS17 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS17/vTotal17)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS17/vSuccess17)*100),0) ELSE NVL(CEIL((HRS17/vDecline17)*100),0) END AS “P17”,
– HRS18 AS “18”,case when (vTotal18=‘0’ OR vDecline18=‘0’) then 0 when vSuccess18=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS18 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS18/vTotal18)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS18/vSuccess18)*100),0) ELSE NVL(CEIL((HRS18/vDecline18)*100),0) END AS “P18”,
– HRS19 AS “19”,case when (vTotal19=‘0’ OR vDecline19=‘0’) then 0 when vSuccess19=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS19 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS19/vTotal19)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS19/vSuccess19)*100),0) ELSE NVL(CEIL((HRS19/vDecline19)*100),0) END AS “P19”,
– HRS20 AS “20”,case when (vTotal20=‘0’ OR vDecline20=‘0’) then 0 when vSuccess20=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS20 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS20/vTotal20)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS20/vSuccess20)*100),0) ELSE NVL(CEIL((HRS20/vDecline20)*100),0) END AS “P20”,
– HRS21 AS “21”,case when (vTotal21=‘0’ OR vDecline21=‘0’) then 0 when vSuccess21=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS21 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS21/vTotal21)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS21/vSuccess21)*100),0) ELSE NVL(CEIL((HRS21/vDecline21)*100),0) END AS “P21”,
– HRS22 AS “22”,case when (vTotal22=‘0’ OR vDecline22=‘0’) then 0 when vSuccess22=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS22 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS22/vTotal22)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS22/vSuccess22)*100),0) ELSE NVL(CEIL((HRS22/vDecline22)*100),0) END AS “P22”,
– HRS23 AS “23”,case when (vTotal23=‘0’ OR vDecline23=‘0’) then 0 when vSuccess23=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS23 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS23/vTotal23)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS23/vSuccess23)*100),0) ELSE NVL(CEIL((HRS23/vDecline23)*100),0) END AS “P23”
HRS0 AS “0”,case when (vTotal0=‘0’) then 0 WHEN vDecline0=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess0=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS0 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS0/vTotal0)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS0/vSuccess0)*100),0) ELSE NVL(CEIL((HRS0/vDecline0)*100),0) END AS “P0”,
HRS1 AS “1”,case when (vTotal1=‘0’) then 0 WHEN vDecline1=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess1=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS1 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS1/vTotal1)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS1/vSuccess1)*100),0) ELSE NVL(CEIL((HRS1/vDecline1)*100),0) END AS “P1”,
HRS2 AS “2”,case when (vTotal2=‘0’) then 0 WHEN vDecline2=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess2=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS2 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS2/vTotal2)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS2/vSuccess2)*100),0) ELSE NVL(CEIL((HRS2/vDecline2)*100),0) END AS “P2”,
HRS3 AS “3”,case when (vTotal3=‘0’) then 0 WHEN vDecline3=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess3=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS3 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS3/vTotal3)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS3/vSuccess3)*100),0) ELSE NVL(CEIL((HRS3/vDecline3)*100),0) END AS “P3”,
HRS4 AS “4”,case when (vTotal4=‘0’) then 0 WHEN vDecline4=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess4=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS4 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS4/vTotal4)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS4/vSuccess4)*100),0) ELSE NVL(CEIL((HRS4/vDecline4)*100),0) END AS “P4”,
HRS5 AS “5”,case when (vTotal5=‘0’) then 0 WHEN vDecline5=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess5=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS5 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS5/vTotal5)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS5/vSuccess5)*100),0) ELSE NVL(CEIL((HRS5/vDecline5)*100),0) END AS “P5”,
HRS6 AS “6”,case when (vTotal6=‘0’) then 0 WHEN vDecline6=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess6=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS6 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS6/vTotal6)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS6/vSuccess6)*100),0) ELSE NVL(CEIL((HRS6/vDecline6)*100),0) END AS “P6”,
HRS7 AS “7”,case when (vTotal7=‘0’) then 0 WHEN vDecline7=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess7=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS7 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS7/vTotal7)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS7/vSuccess7)*100),0) ELSE NVL(CEIL((HRS7/vDecline7)*100),0) END AS “P7”,
HRS8 AS “8”,case when (vTotal8=‘0’) then 0 WHEN vDecline8=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess8=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS8 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS8/vTotal8)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS8/vSuccess8)*100),0) ELSE NVL(CEIL((HRS8/vDecline8)*100),0) END AS “P8”,
HRS9 AS “9”,case when (vTotal9=‘0’) then 0 WHEN vDecline9=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess9=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS9 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS9/vTotal9)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS9/vSuccess9)*100),0) ELSE NVL(CEIL((HRS9/vDecline9)*100),0) END AS “P9”,
HRS10 AS “10”,case when (vTotal10=‘0’) then 0 WHEN vDecline10=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess10=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS10 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS10/vTotal10)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS10/vSuccess10)*100),0) ELSE NVL(CEIL((HRS10/vDecline10)*100),0) END AS “P10”,
HRS11 AS “11”,case when (vTotal11=‘0’) then 0 WHEN vDecline11=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess11=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS11 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS11/vTotal11)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS11/vSuccess11)*100),0) ELSE NVL(CEIL((HRS11/vDecline11)*100),0) END AS “P11”,
HRS12 AS “12”,case when (vTotal12=‘0’) then 0 WHEN vDecline12=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess12=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS12 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS12/vTotal12)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS12/vSuccess12)*100),0) ELSE NVL(CEIL((HRS12/vDecline12)*100),0) END AS “P12”,
HRS13 AS “13”,case when (vTotal13=‘0’) then 0 WHEN vDecline13=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess13=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS13 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS13/vTotal13)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS13/vSuccess13)*100),0) ELSE NVL(CEIL((HRS13/vDecline13)*100),0) END AS “P13”,
HRS14 AS “14”,case when (vTotal14=‘0’) then 0 WHEN vDecline14=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess14=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS14 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS14/vTotal14)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS14/vSuccess14)*100),0) ELSE NVL(CEIL((HRS14/vDecline14)*100),0) END AS “P14”,
HRS15 AS “15”,case when (vTotal15=‘0’) then 0 WHEN vDecline15=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess15=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS15 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS15/vTotal15)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS15/vSuccess15)*100),0) ELSE NVL(CEIL((HRS15/vDecline15)*100),0) END AS “P15”,
HRS16 AS “16”,case when (vTotal16=‘0’) then 0 WHEN vDecline16=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess16=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS16 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS16/vTotal16)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS16/vSuccess16)*100),0) ELSE NVL(CEIL((HRS16/vDecline16)*100),0) END AS “P16”,
HRS17 AS “17”,case when (vTotal17=‘0’) then 0 WHEN vDecline17=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess17=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS17 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS17/vTotal17)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS17/vSuccess17)*100),0) ELSE NVL(CEIL((HRS17/vDecline17)*100),0) END AS “P17”,
HRS18 AS “18”,case when (vTotal18=‘0’) then 0 WHEN vDecline18=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess18=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS18 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS18/vTotal18)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS18/vSuccess18)*100),0) ELSE NVL(CEIL((HRS18/vDecline18)*100),0) END AS “P18”,
HRS19 AS “19”,case when (vTotal19=‘0’) then 0 WHEN vDecline19=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess19=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS19 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS19/vTotal19)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS19/vSuccess19)*100),0) ELSE NVL(CEIL((HRS19/vDecline19)*100),0) END AS “P19”,
HRS20 AS “20”,case when (vTotal20=‘0’) then 0 WHEN vDecline20=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess20=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS20 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS20/vTotal20)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS20/vSuccess20)*100),0) ELSE NVL(CEIL((HRS20/vDecline20)*100),0) END AS “P20”,
HRS21 AS “21”,case when (vTotal21=‘0’) then 0 WHEN vDecline21=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess21=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS21 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS21/vTotal21)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS21/vSuccess21)*100),0) ELSE NVL(CEIL((HRS21/vDecline21)*100),0) END AS “P21”,
HRS22 AS “22”,case when (vTotal22=‘0’) then 0 WHEN vDecline22=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess22=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS22 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS22/vTotal22)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS22/vSuccess22)*100),0) ELSE NVL(CEIL((HRS22/vDecline22)*100),0) END AS “P22”,
HRS23 AS “23”,case when (vTotal23=‘0’) then 0 WHEN vDecline23=‘0’ and (ROW_LABEL<>‘4’ AND ROW_LABEL<>‘5’ AND ROW_LABEL<>‘6’ AND ROW_LABEL<>‘7’ AND ROW_LABEL<>‘8’ AND ROW_LABEL<>‘17’ AND ROW_LABEL<>‘18’) then 0 when vSuccess23=‘0’ and (ROW_LABEL=‘5’ or ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then 0 when (ROW_LABEL=‘4’ OR ROW_LABEL=‘17’) THEN HRS23 when (ROW_LABEL=‘5’ OR ROW_LABEL=‘18’) THEN NVL(CEIL((HRS23/vTotal23)*100),0) WHEN (ROW_LABEL=‘6’ or ROW_LABEL=‘7’ or ROW_LABEL=‘8’) then NVL(CEIL((HRS23/vSuccess23)*100),0) ELSE NVL(CEIL((HRS23/vDecline23)*100),0) END AS “P23”
,ROW_LABEL
,NVL(THHLD.THRESHOLDVALUE,‘0’) THRESHOLDVALUE
,THHLD.OPERATORS
–,(NVL(THHLD.THRESHOLDVALUE,‘0’) || ’ ’ || THHLD.OPERATORS) AS THRESHOLDPERCENT
,(NVL(THHLD.OPERATORS,‘0’) || ’ ’ || THHLD.THRESHOLDVALUE) AS THRESHOLDPERCENT
,THHLD.STATUS as STATUS
FROM FINS_DASHBOARD_VIEW DView
INNER JOIN FINS_DASHBOARDTHRESHOLDFIELD DMFLD ON DView.ROW_LABEL = DMFLD.FIELDID
LEFT JOIN FINS_DASHBOARDTHRESHOLD THHLD ON DView.ROW_LABEL = THHLD.THRESHOLDFIELD AND THHLD.ACQUIRINGCHANNEL = DView.ACQ_CHANNEL AND NVL(THHLD.DASHBOARD, ‘0’) = pDashboard
INNER JOIN FINS_DASHBOARDACQUIRINGCHANNEL ACQ ON ACQ.ACQID = DView.ACQ_CHANNEL
WHERE ACQ_CHANNEL = TRIM(v_ACQID)
AND TXNTYPE=TRIM(vTXNTYPE)
AND DASHBOARDTYPE=TRIM(vDASHBOARDTYPE)
----AND NVL(THHLD.DASHBOARD, ‘0’) IN (pDashboard, 0)
ORDER BY TO_NUMBER(ROW_LABEL) ASC
;

END IF;

–insert into debug values (‘PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 8’);
– commit;

OPEN P_THRESHOLDDATA1 FOR
  SELECT DT.THRESHOLDVALUE,DT.OPERATORS,vGRIDVIEW as GRIDVIEW FROM FINS_DASHBOARDTHRESHOLD DT
  LEFT JOIN FINS_DASHBOARDACQUIRINGCHANNEL DA ON DT.ACQUIRINGCHANNEL=DA.ACQID
  WHERE DASHTHRESHOLDID=pDashThresholdID;

– insert into debug values (‘PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 9’);
– commit;

OPEN P_DASHBOARD2 FOR
  SELECT SYSDATE FROM DUAL;

EXCEPTION
WHEN OTHERS THEN
V_MSQLERRM := SUBSTR(SQLERRM, 1, 1000);
– insert into debug values ('PKG_FINS_DASHBOARDTHRESHOLD.GET_DASHBOARDDATAECOM error 10 '||V_MSQLERRM);
–commit;
P_DASHBOARD1:=null;
P_THRESHOLDDATA1:=null;
P_DASHBOARD2:=null;

END GET_DASHBOARDDATAECOM;

procedure GetAcqChannelsByDashThreshold
(
pDashboard VARCHAR:=NULL,
pOutCursor OUT SYS_REFCURSOR
)
IS

END PKG_FINS_DASHBOARDTHRESHOLD_Test;

nvoxland · January 20, 2022, 3:21pm

The formatting the forum software tries to do makes it tough to follow your SQL, but it looks like there are a couple different procedures/package bodies in there?

How are you storing that SQL in your changelog? Is it all in a simple sql file that is referenced with include? Is it in a <sql> block or formatted SQL?

The error makes it sound like it’s an issue with how the splitting of statements is happening. For complex functions, it’s usually best to be able to specify splitStatements=false. How you do that will depend on what is wrapping/containing that SQL.

But that also means that you can’t have both create statements in the same non-split block because the database needs them as separate statements. Since it’s usually not a good idea to have multiple statements in the same changeset anyway, though, moving them into separate changesets is a good answer regardless.

So make sure each CREATE is in it’s own changeset, and if it is still failing add splitStatements=false.

Nathan

sanjaykdalvi · January 25, 2022, 3:39pm

Hi @NathanVoxland

Thanks for revert, I could able to resolve issue by adding “/” at the end of SQL statements.
Issues was with only oracle package create and update statements, where package was getting updated however “;” was missing and not able to compile
-----------------------------------------
Database Change log file: **
-----------------------------------------

** <sqlFile dbms="oracle"
** endDelimiter=";"**
** path="./V28_PKG_TEST.sql"**
** relativeToChangelogFile=“true”**
** splitStatements=“false”**
** stripComments=“false”/>**

----------------------------------------------------------------------------------------------------------------------------
SQL Script : V28_PKG_TEST.sql
----------------------------------------------------------------------------------------------------------------------------

create or replace procedure PKG_TEST
(
pTTHID NUMBER,
pTHRESHOLD NUMBER,
pMINCOUNT NUMBER,
pTTHFalg VARCHAR2,
pOutCursor OUT SYS_REFCURSOR
)
IS
/----------------------------------------------------------------------------------------------------------------------------
Project : PROJECT
Short Description : This sp is used for displaying data on Threshold Page
Developer Name :
Created Date :
----------------------------------------------------------------------------------------------------------------------------/

BEGIN

BEGIN
if (pTTHID = ‘1’ AND pTHRESHOLD = ‘1’) THEN
OPEN pOutCursor FOR
SELECT BUSSINESS_ID, BUSSINESS, THRESHOLD FROM FINS_TransTrendsHourlyThreshold Order by 1 ASC;
ELSE
IF (pTTHFalg = ‘UPDATE’) THEN
UPDATE FINS_TransTrendsHourlyThreshold SET THRESHOLD = pTHRESHOLD WHERE BUSSINESS_ID = pTTHID;

      OPEN pOutCursor FOR
      SELECT BUSSINESS_ID, BUSSINESS, THRESHOLD FROM FINS_TransTrendsHourlyThreshold Order by 1 ASC;
    ELSE
      OPEN pOutCursor FOR 
      SELECT BUSSINESS_ID, BUSSINESS, THRESHOLD FROM FINS_TransTrendsHourlyThreshold WHERE BUSSINESS_ID = pTTHID;
    END IF;
  END IF;

END;
EXCEPTION
WHEN OTHERS THEN
pOutCursor := NULL;

Topic		Replies	Views
Liquibase 4.9.0 and "endDelimiter:/" recognizes "/" in PL/SQL code as changeset end delimiter General Discussion	1	991	March 27, 2022
Create Procedure ends up with ORA-00900 Community Answers	11	6927	August 30, 2021
Oracle - UpdateTagSQL - Issue with delimeters General Discussion	10	839	June 2, 2021
Creating function using liquibase sql changelog is successful but function has compilation warning General Discussion	1	1924	June 22, 2020
An unexpected syntax result with updateSQL for dbms:oracle General Discussion	2	176	June 1, 2023

Liquibase migration with oracle pl/sql function gets PLS-00103

END; /

Related Topics

END;
/