[QUOTE][i]Originally posted by chrisrlong [/i]
[B]Huh?

And I thought I strung thoughts together :)

Post the proc please, and explain the situation again - a little slower please :)

- Chris [/B][/QUOTE]

-------starting of the procedure
PACKAGE BODY DELL_CA_ALLOC_VARIANCES
IS
PROCEDURE DELL_CA_ALLOC_VARIANCE (
AIS_CCN IN C_CTSSK.CCN%TYPE,
AID_PROC_DATE IN DATE
)
IS
LS_CCN CCN.CCN%TYPE;
LD_FISCAL_PROC_DATE DATE;
LS_MESSAGE VARCHAR2(255);
BEGIN
SET TRANSACTION USE ROLLBACK SEGMENT R01; --FORCE THE LARGEST ROLLBACK SEGMENT.
LS_CCN := NVL (AIS_CCN, 'BCC');
LD_FISCAL_PROC_DATE := DELL_GLOVIA_FISCAL_YEAR.CURR_SATURDAY (NVL (AID_PROC_DATE, SYSDATE));

SELECT 'STARTING OF THE PROCEDURE dell_ca_alloc_variances '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
DBMS_OUTPUT.PUT_LINE(LS_MESSAGE);
--STEP 1-----
-- STEP 1

UPDATE C_CTSCM
SET IPV = (SELECT C_CTVWK.IPV * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
FROM C_CTVWK,
(SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
FROM C_CTSCM A, C_CTSSK
WHERE A.CCN = LS_CCN
AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND A.CCN = C_CTSSK.CCN
AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
) COMP_TOTALS
WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
AND C_CTVWK.CCN = C_CTSCM.CCN
AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
)
WHERE CCN = LS_CCN
AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND EXISTS(SELECT DISTRIBUTE_VARIANCES
FROM C_CTSSK
WHERE C_CTSCM.CCN = C_CTSSK.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
)
AND C_CTSCM.SHIPPED_QTY != 0;
dbms_output.put_line('END OF STEP 1..'||LS_MESSAGE);
UPDATE C_CTSCM
SET PPV = ( SELECT C_CTVWK.PPV * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
FROM C_CTVWK,
(SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
FROM C_CTSCM A, C_CTSSK
WHERE A.CCN = LS_CCN
AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND A.CCN = C_CTSSK.CCN
AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
) COMP_TOTALS
WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
AND C_CTVWK.CCN = C_CTSCM.CCN
AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
)
WHERE CCN = LS_CCN
AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND EXISTS(SELECT DISTRIBUTE_VARIANCES
FROM C_CTSSK
WHERE C_CTSCM.CCN = C_CTSSK.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
)
AND C_CTSCM.SHIPPED_QTY != 0;
UPDATE C_CTSCM
SET STD_REVAL = (SELECT C_CTVWK.STD_REVAL * C_CTSCM.SHIPPED_QTY / COMP_TOTALS.TOTAL_SHIPPED
FROM C_CTVWK,
(SELECT A.CCN, A.COMP_ITEM, A.COMP_REV,
SUM( A.SHIPPED_QTY ) AS TOTAL_SHIPPED
FROM C_CTSCM A, C_CTSSK
WHERE A.CCN = LS_CCN
AND A.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND A.CCN = C_CTSSK.CCN
AND A.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND A.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND A.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND A.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
GROUP BY A.CCN, A.COMP_ITEM, A.COMP_REV
) COMP_TOTALS
WHERE C_CTVWK.CCN = COMP_TOTALS.CCN
AND C_CTVWK.COMP_ITEM = COMP_TOTALS.COMP_ITEM
AND C_CTVWK.COMP_REV = COMP_TOTALS.COMP_REV
AND C_CTVWK.CCN = C_CTSCM.CCN
AND C_CTVWK.COMP_ITEM = C_CTSCM.COMP_ITEM
AND C_CTVWK.COMP_REV = C_CTSCM.COMP_REV
)
WHERE CCN = LS_CCN
AND C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
AND EXISTS( SELECT DISTRIBUTE_VARIANCES --ONLY UPDATE THE COMPONENTS TO DISTRIBUTE VARIANCES TO.
FROM C_CTSSK
WHERE C_CTSCM.CCN = C_CTSSK.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
)
AND C_CTSCM.SHIPPED_QTY != 0;
SELECT 'STEP 3 OF 1 COMPLETED' ||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
-- STEP -4
UPDATE C_CTVWK SET
IPV_FLAG = 'I'
WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
WHERE C_CTSSK.CCN = C_CTSCM.CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
AND C_CTSCM.CCN = C_CTVWK.CCN
AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
AND IPV_FLAG = 'T'
AND CCN = LS_CCN
AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
UPDATE C_CTVWK SET
PPV_FLAG = 'P'
WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
WHERE C_CTSSK.CCN = C_CTSCM.CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
AND C_CTSCM.CCN = C_CTVWK.CCN
AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
AND IPV_FLAG = 'T'
AND CCN = LS_CCN
AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
UPDATE C_CTVWK SET
STD_REVAL_FLAG = 'P'
WHERE EXISTS(SELECT C_CTSSK.C_SKU_NUM FROM C_CTSSK, C_CTSCM
WHERE C_CTSSK.CCN = C_CTSCM.CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = C_CTSCM.C_FISCAL_PROC_DATE
AND C_CTSSK.C_SALES_ORDER_NUM = C_CTSCM.C_SALES_ORDER_NUM
AND C_CTSSK.C_TIE_NUM = C_CTSCM.C_TIE_NUM
AND C_CTSSK.C_SKU_NUM = C_CTSCM.C_SKU_NUM
AND C_CTSSK.DISTRIBUTE_VARIANCES = 'Y'
AND C_CTSCM.CCN = C_CTVWK.CCN
AND C_CTSCM.COMP_ITEM = C_CTVWK.COMP_ITEM
AND C_CTSCM.COMP_REV = C_CTVWK.COMP_REV
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE)
AND IPV_FLAG = 'T'
AND CCN = LS_CCN
AND FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE ;

SELECT 'STEP 4 OF 1 COMPLETED '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
COMMIT;
-- STEP 2-------
/* TOTAL ALL SHIPPED MOD(C_CTSMD) */
UPDATE C_CTSMD SET
IPV = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
WHERE C_CTSCM.CCN = C_CTSMD.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
WHERE C_CTSMD.CCN = LS_CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
UPDATE C_CTSMD SET
PPV = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
WHERE C_CTSCM.CCN = C_CTSMD.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
WHERE C_CTSMD.CCN = LS_CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
UPDATE C_CTSMD SET
STD_REVAL = (SELECT SUM(C_CTSCM.IPV) FROM C_CTSCM
WHERE C_CTSCM.CCN = C_CTSMD.CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = C_CTSMD.C_FISCAL_PROC_DATE
AND C_CTSCM.C_SALES_ORDER_NUM = C_CTSMD.C_SALES_ORDER_NUM
AND C_CTSCM.C_TIE_NUM = C_CTSMD.C_TIE_NUM
AND C_CTSCM.C_SKU_NUM = C_CTSMD.C_SKU_NUM
AND C_CTSCM.C_MOD = C_CTSMD.C_MOD)
WHERE C_CTSMD.CCN = LS_CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE;
SELECT 'STEP TWO COMPLETED '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
-- STEP 3----------------
/* TOTAL ALL SHIPPED SKU VARIANCE FIELDS(IPV, PPV, STD_REVAL)
BY SUMMING THE RESPECTIVE VARIANCE FIELDS IN THE MOD TABLES */
SELECT 'BEGINING OF THE STEP THREE '|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
UPDATE C_CTSSK SET
IPV = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
WHERE C_CTSMD.CCN = C_CTSSK.CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
UPDATE C_CTSSK SET
PPV = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
WHERE C_CTSMD.CCN = C_CTSSK.CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
UPDATE C_CTSSK SET
STD_REVAL = (SELECT SUM(C_CTSMD.IPV) FROM C_CTSMD
WHERE C_CTSMD.CCN = C_CTSSK.CCN
AND C_CTSMD.C_FISCAL_PROC_DATE = C_CTSSK.C_FISCAL_PROC_DATE
AND C_CTSMD.C_SALES_ORDER_NUM = C_CTSSK.C_SALES_ORDER_NUM
AND C_CTSMD.C_TIE_NUM = C_CTSSK.C_TIE_NUM
AND C_CTSMD.C_SKU_NUM = C_CTSSK.C_SKU_NUM
AND C_CTSSK.CCN = LS_CCN
AND C_CTSSK.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE);
SELECT 'END OF STEP THREE '||TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
/* END OF STEP --3 ----------*/
/* STEP --4 ---------
UPDATE VARIANCE AUDIT TABLE BY ADDING THE RECORDS */
SELECT 'STARTING OF STEPFOUR'|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
INSERT INTO C_CTVAU(CCN,
C_VARIANCE_TYPE,
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
VARIANCE_ALLOCATED,
FISCAL_PROC_DATE,
CHANGE_OPER,
LAST_CHANGE_DATE,
LAST_CHANGE_TIME
)
SELECT CCN,
'I',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
SUM(C_CTSCM.IPV),
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
WHERE C_CTSCM.IPV NOT IN(NULL, 0)
AND C_CTSCM.CCN = LS_CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
GROUP BY CCN,
'I',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS');

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line('INSERT INTO C_CTVAU FOR PPV'||LS_MESSAGE);
-- FOR PPV
INSERT INTO C_CTVAU(CCN,
C_VARIANCE_TYPE,
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
VARIANCE_ALLOCATED,
FISCAL_PROC_DATE,
CHANGE_OPER,
LAST_CHANGE_DATE,
LAST_CHANGE_TIME
)
SELECT CCN,
'P',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
SUM(C_CTSCM.PPV),
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
WHERE C_CTSCM.PPV NOT IN(NULL, 0)
AND C_CTSCM.CCN = LS_CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
GROUP BY CCN,
'P',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS');
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line('INSERT INTO C_CTVAU FOR STD_REVAL '||LS_MESSAGE);
-- FOR STD_REVAL
INSERT INTO C_CTVAU(CCN,
C_VARIANCE_TYPE,
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
VARIANCE_ALLOCATED,
FISCAL_PROC_DATE,
CHANGE_OPER,
LAST_CHANGE_DATE,
LAST_CHANGE_TIME
)
SELECT CCN,
'S',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
SUM(C_CTSCM.STD_REVAL),
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS') FROM C_CTSCM
WHERE C_CTSCM.STD_REVAL NOT IN(NULL, 0)
AND C_CTSCM.CCN = LS_CCN
AND C_CTSCM.C_FISCAL_PROC_DATE = LD_FISCAL_PROC_DATE
GROUP BY CCN,
'S',
COMP_ITEM,
COMP_REV,
C_SKU_NUM,
C_MOD,
C_FISCAL_PROC_DATE,
'SYS',
SYSDATE,
TO_CHAR (SYSDATE, 'HH24MISS');
COMMIT;
SELECT 'END POINT OF PROCEDURE'|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') INTO LS_MESSAGE FROM DUAL;
dbms_output.put_line(LS_MESSAGE);
EXCEPTION
WHEN OTHERS THEN
LS_MESSAGE :=SUBSTR(SQLERRM,1,9);
DBMS_OUTPUT.PUT_LINE(LS_MESSAGE);
END; -- PROCEDURE STEPFOUR
END; -- PACKAGE BODY DELL_CA_ALLOC_VARIANCES
----------------end of the procedure

i am able to execute the sql queries and it is doing well in the sql prompt, when i hardcode the parameters in the procedure it is doing well, while i am passing the parameters to the procedure and it is not executing, simply displaying all the messages coded in the procedure.