Solved

Trying to execute stored procedure but receiving errors ORA-06550 and PLS-00201

Posted on 2008-06-24
7
2,983 Views
Last Modified: 2013-12-19
I am trying to apply a stored procedure in SQL*PLUS but receiving the following message:

SQL> exec LDPLDJ.ora;
BEGIN LDPLDJ.ora; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'LDPLDJ.ORA' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


I am brand new to stored procedures any suggestions would be greatly appreciated! I've attached the file for review.
CREATE OR REPLACE PROCEDURE DELTEK.CP_LDPLDJ_LABHS

  (sDBErrorDesc       OUT    VARCHAR2,

   sProcAction        OUT    VARCHAR2,

   sExpectedVersion   IN     VARCHAR2,

   bValidityCheckOnly IN     INTEGER, 

   sVersionFound      IN OUT VARCHAR2,  

   nPostSeqNo         IN     INTEGER,

   nSemaphorePdNo     IN     INTEGER,

   sUserId            IN     VARCHAR2,

   sSemaphoreFyCd     IN     VARCHAR2,

   sSemaphoreSJnlCd   IN     VARCHAR2,

   dtStepStartDtt     IN     DATE,

   nStepSeqNo         IN OUT INTEGER

) is

   sProcName       VARCHAR2(30) := 'CP_LDPLDJ_LABHS';

 

   nLastKey        INTEGER := 0;

   nNewRows        INTEGER := 0;

   nRows           INTEGER := 0;

   TRUE            INTEGER := 1;

   nLabHsKey       INTEGER := 0;

 

   CURSOR SUBPD IS

     SELECT SUB_PD_NO FROM DELTEK.Z_LDPLDJ_LAB_HS

     GROUP BY SUB_PD_NO;

 

   SUBPD_REC SUBPD%ROWTYPE;

 

   CURSOR WRK IS  

     SELECT LAB_HS_KEY, PROJ_ID, ORG_ID, ACCT_ID, ACT_HRS, ACT_AMT,

            ALLOW_REV_HRS, GENL_LAB_CAT_CD, BILL_LAB_CAT_CD,

            FY_CD, PD_NO, SUB_PD_NO, EMPL_ID, EFFECT_BILL_DT

     FROM DELTEK.Z_LDPLDJ_LAB_HS

     WHERE ROWVERSION = 1;

  WRK_REC WRK%ROWTYPE;

 

BEGIN

   sDBErrorDesc := '';

   sVersionFound := '3.1A';

   if sExpectedVersion != sVersionFound then

      sProcAction := sProcName || ' - Version check';

      return;

   end if;

   if bValidityCheckOnly = TRUE then

      return;

   end if;

   BEGIN

      sProcAction := sProcName || ' - Count rows to post to LAB_HS.';

      SELECT COUNT(*) 

      INTO nRows

      FROM DELTEK.Z_LDPLDJ_GL_DETL Z

      WHERE Z.ID IS NOT NULL;

      EXCEPTION

         WHEN TOO_MANY_ROWS THEN

            nRows := nRows;

         WHEN NO_DATA_FOUND THEN

            nRows := 0;

   END;

   BEGIN

      if nRows > 0 then

       BEGIN

            sProcAction := sProcName || ' - INSERT EMPL DATA TO Z_LDPLDJ_LAB_HS';

            INSERT INTO DELTEK.Z_LDPLDJ_LAB_HS(

               LAB_HS_KEY,PROJ_ID,ORG_ID,ACCT_ID,

               FY_CD,PD_NO,SUB_PD_NO,

               BILL_LAB_CAT_CD,GENL_LAB_CAT_CD,

               ACT_HRS,ALLOW_REV_HRS,

               REV_RT_AMT,ACT_AMT,

               EMPL_ID,VEND_ID,EFFECT_BILL_DT,ROWVERSION)

            SELECT 0,Z.PROJ_ID,Z.ORG_ID,Z.ACCT_ID,

               Z.FY_CD,Z.PD_NO,Z.SUB_PD_NO,

               Z.BILL_LAB_CAT_CD,NVL(Z.GENL_LAB_CAT_CD,' '),

               SUM(Z.HRS),1,

               0,SUM(Z.AMT),

               Z.ID,NULL,Z.EFFECT_BILL_DT,0

            FROM DELTEK.Z_LDPLDJ_GL_DETL Z

            WHERE Z.ID IS NOT NULL

            GROUP BY 

               Z.PROJ_ID,Z.ORG_ID,Z.ACCT_ID,

               Z.FY_CD,Z.PD_NO,Z.SUB_PD_NO,

               Z.BILL_LAB_CAT_CD,Z.GENL_LAB_CAT_CD,

               Z.ID,Z.EFFECT_BILL_DT;

         END;

         OPEN SUBPD;

         FETCH SUBPD INTO SUBPD_REC;

         CLOSE SUBPD;

         sProcAction := sProcName || ' - Get current LAB_HS data ';

         INSERT INTO DELTEK.Z_LDPLDJ_LAB_HS(

               LAB_HS_KEY,PROJ_ID,ORG_ID,ACCT_ID,

               FY_CD,PD_NO,SUB_PD_NO,

               BILL_LAB_CAT_CD,GENL_LAB_CAT_CD,

               ACT_HRS,ALLOW_REV_HRS,

               REV_RT_AMT,ACT_AMT,

               EMPL_ID,VEND_ID,EFFECT_BILL_DT,ROWVERSION)

            SELECT LAB_HS_KEY,PROJ_ID,ORG_ID,ACCT_ID,

               FY_CD,PD_NO,SUB_PD_NO,

               BILL_LAB_CAT_CD,GENL_LAB_CAT_CD,

               ACT_HRS,2,

               0,ACT_AMT,

               EMPL_ID,NULL,EFFECT_BILL_DT,0

            FROM DELTEK.LAB_HS 

            WHERE FY_CD = sSemaphoreFyCd AND

                  PD_NO = nSemaphorePdNo AND

                  SUB_PD_NO = SUBPD_REC.SUB_PD_NO AND

                  EMPL_ID IS NOT NULL AND EMPL_ID != ' ';

         sProcAction := sProcName || ' - Roll up labor data ';

         UPDATE DELTEK.Z_LDPLDJ_LAB_HS SET EFFECT_BILL_DT = to_date('01-JAN-1900','DD-MON-YYYY')

         WHERE EFFECT_BILL_DT IS NULL;

         INSERT INTO DELTEK.Z_LDPLDJ_LAB_HS(

               LAB_HS_KEY,PROJ_ID,ORG_ID,ACCT_ID,

               FY_CD,PD_NO,SUB_PD_NO,

               BILL_LAB_CAT_CD,GENL_LAB_CAT_CD,

               ACT_HRS,ALLOW_REV_HRS,

               REV_RT_AMT,ACT_AMT,

               EMPL_ID,VEND_ID,EFFECT_BILL_DT,ROWVERSION)

            SELECT SUM(LAB_HS_KEY),PROJ_ID,ORG_ID,ACCT_ID,

               FY_CD,PD_NO,SUB_PD_NO,

               BILL_LAB_CAT_CD,GENL_LAB_CAT_CD,

               SUM(ACT_HRS),SUM(ALLOW_REV_HRS),

               0,SUM(ACT_AMT),

               EMPL_ID,NULL,EFFECT_BILL_DT,1

            FROM DELTEK.Z_LDPLDJ_LAB_HS

            GROUP BY PROJ_ID, ORG_ID, ACCT_ID, FY_CD, PD_NO, SUB_PD_NO, BILL_LAB_CAT_CD,

                     GENL_LAB_CAT_CD, EMPL_ID,EFFECT_BILL_DT;

         COMMIT WORK;

         SELECT COUNT(*) INTO nNewRows

           FROM DELTEK.Z_LDPLDJ_LAB_HS 

           WHERE LAB_HS_KEY = 0 AND ROWVERSION = 1;

         if nNewRows > 0 then

           BEGIN

             sProcAction := sProcName || ' - Dummy Update Seq Gen';

             UPDATE DELTEK.SEQ_GENERATOR

               SET TIME_STAMP = TIME_STAMP

               WHERE S_TABLE_ID = 'DELTEK.LAB_HS' AND

                     S_COL_ID = 'DELTEK.LAB_HS.LAB_HS_KEY';

             sProcAction := sProcName || ' - Select Last Key';

             SELECT LAST_KEY 

               INTO nLastKey

               FROM DELTEK.SEQ_GENERATOR

               WHERE S_TABLE_ID = 'DELTEK.LAB_HS' AND

                     S_COL_ID = 'DELTEK.LAB_HS.LAB_HS_KEY';

             nLastKey := nLastKey + 1;

             nRows := nNewRows + nLastKey;

             sProcAction := sProcName || ' - Update Last Key in Seq Gen';

             UPDATE DELTEK.SEQ_GENERATOR 

               SET LAST_KEY = nRows-1,TIME_STAMP = SYSDATE

               WHERE S_TABLE_ID = 'DELTEK.LAB_HS' AND

                     S_COL_ID = 'DELTEK.LAB_HS.LAB_HS_KEY';

             COMMIT WORK;

           END;

         END IF;

         BEGIN

            sProcAction := sProcName || ' - Open WRK CURSOR';

            OPEN WRK;

            LOOP

               BEGIN

                  sProcAction := sProcName || ' - Fetch WRK data.';

                  FETCH WRK INTO WRK_REC;

                  EXIT WHEN WRK%NOTFOUND;

                   IF WRK_REC.EFFECT_BILL_DT IS NULL THEN

                      WRK_REC.EFFECT_BILL_DT := to_date('01-JAN-1900','DD-MON-YYYY');

                   END IF;

                   IF WRK_REC.LAB_HS_KEY != 0 AND WRK_REC.ALLOW_REV_HRS = 3 THEN

                    UPDATE DELTEK.LAB_HS 

                      SET ACT_AMT = WRK_REC.ACT_AMT,            

                          ACT_HRS = WRK_REC.ACT_HRS,

                          EFFECT_BILL_DT = WRK_REC.EFFECT_BILL_DT,

                          MODIFIED_BY = sUserId,

                          TIME_STAMP = SYSDATE

                      WHERE LAB_HS_KEY = WRK_REC.LAB_HS_KEY;

                  ELSIF WRK_REC.LAB_HS_KEY = 0 THEN

                    INSERT INTO DELTEK.LAB_HS (LAB_HS_KEY, PROJ_ID, ORG_ID, ACCT_ID,

                      FY_CD, PD_NO, SUB_PD_NO, GENL_LAB_CAT_CD, ACT_HRS, REV_RT_AMT,

                      ALLOW_REV_HRS,

                      ACT_AMT, BILL_LAB_CAT_CD, EMPL_ID, VEND_ID, VEND_EMPL_ID, EFFECT_BILL_DT,

                      MODIFIED_BY, TIME_STAMP, ROWVERSION)

                    VALUES (nLastKey, WRK_REC.PROJ_ID, WRK_REC.ORG_ID, WRK_REC.ACCT_ID,

                      sSemaphoreFyCd, nSemaphorePdNo, SUBPD_REC.SUB_PD_NO,

                      WRK_REC.GENL_LAB_CAT_CD, WRK_REC.ACT_HRS, 0, 0, WRK_REC.ACT_AMT,

                      WRK_REC.BILL_LAB_CAT_CD, WRK_REC.EMPL_ID, NULL, NULL, WRK_REC.EFFECT_BILL_DT,

                      sUserId, SYSDATE, 0);

                    nLastKey := nLastKey + 1;

                  END IF;

               END;

            END LOOP;

            CLOSE WRK;

         END;

      END IF;

   END;

         

   BEGIN

      sProcAction := sProcName || ' - Insert into post semaphore.';

      nStepSeqNo := nStepSeqNo + 1;

      INSERT INTO DELTEK.POST_SEMAPHORE(

         S_APPL_FUNC_ID,STEP_SEQ_NO,FY_CD,DELTEK.POST_SEMAPHORE.PD_NO,

         POST_SEQ_NO,S_JNL_CD,S_STEP_TYPE,STEP_DESC,START_DTT,END_DTT,

         MODIFIED_BY,TIME_STAMP,ROWVERSION)

         VALUES('LDPLDJ',nStepSeqNo,sSemaphoreFyCd,nSemaphorePdNo,

         nPostSeqNo,sSemaphoreSJnlCd,'STEP','BOTH - LAB_HS UPDATE',dtStepStartDtt,

         SYSDATE,sUserId,SYSDATE,0);

      COMMIT WORK;

   END;

   EXCEPTION

      when others then

         sDBErrorDesc := SQLERRM(SQLCODE);

END CP_LDPLDJ_LABHS; 

/

show errors procedure CP_LDPLDJ_LABHS

 

CREATE OR REPLACE PROCEDURE DELTEK.CP_LDPLDJ_FSSUM

  (sDBErrorDesc       OUT    VARCHAR2,

   sProcAction        OUT    VARCHAR2,

   sExpectedVersion   IN     VARCHAR2,

   bValidityCheckOnly IN     INTEGER, 

   sVersionFound      IN OUT VARCHAR2,  

   nPostSeqNo         IN     INTEGER,

   nSemaphorePdNo     IN     INTEGER,

   sUserId            IN     VARCHAR2,

   sSemaphoreFyCd     IN     VARCHAR2,

   sSemaphoreSJnlCd   IN     VARCHAR2,

   dtStepStartDtt     IN     DATE,

   nStepSeqNo         IN OUT INTEGER

) is

   sProcName       VARCHAR2(30) := 'CP_LDPLDJ_FSSUM';

   TRUE            INTEGER :=1;

 

   CURSOR c1 IS  SELECT PD_1_AMT,PD_2_AMT,PD_3_AMT,PD_4_AMT,PD_5_AMT,PD_6_AMT,

                    PD_7_AMT,PD_8_AMT,PD_9_AMT,PD_10_AMT,PD_11_AMT,PD_12_AMT,

                    PD_13_AMT,PD_14_AMT,PD_15_AMT,PD_16_AMT,PD_17_AMT,PD_18_AMT,

                    PD_1_HRS,PD_2_HRS,PD_3_HRS,PD_4_HRS,PD_5_HRS,PD_6_HRS,

                    PD_7_HRS,PD_8_HRS,PD_9_HRS,PD_10_HRS,PD_11_HRS,PD_12_HRS,

                    PD_13_HRS,PD_14_HRS,PD_15_HRS,PD_16_HRS,PD_17_HRS,PD_18_HRS,

                    ACCT_ID,ORG_ID

                 FROM DELTEK.Z_LDPLDJ_SUM

                 WHERE MODIFIED_BY IS NOT NULL;

   C1_REC c1%ROWTYPE;

BEGIN

   sDBErrorDesc := '';

   sVersionFound := '3.1A';

   if sExpectedVersion != sVersionFound then

      sProcAction := sProcName || ' - Version check';

      return;

   end if;

   if bValidityCheckOnly = TRUE then

      return;

   end if;

   BEGIN      

      OPEN c1;

      LOOP

         BEGIN

            sProcAction := sProcName || ' - Fetch records from temp table.';

            FETCH c1 INTO C1_REC;

               EXIT WHEN c1%NOTFOUND;

            BEGIN

               sProcAction := sProcName || ' - Update FS_SUM';

               UPDATE DELTEK.FS_SUM SET PD_1_AMT=PD_1_AMT+C1_REC.PD_1_AMT,

                  PD_2_AMT=PD_2_AMT+C1_REC.PD_2_AMT,PD_3_AMT=PD_3_AMT+C1_REC.PD_3_AMT,

                  PD_4_AMT=PD_4_AMT+C1_REC.PD_4_AMT,PD_5_AMT=PD_5_AMT+C1_REC.PD_5_AMT,

                  PD_6_AMT=PD_6_AMT+C1_REC.PD_6_AMT,PD_7_AMT=PD_7_AMT+C1_REC.PD_7_AMT,

                  PD_8_AMT=PD_8_AMT+C1_REC.PD_8_AMT,PD_9_AMT=PD_9_AMT+C1_REC.PD_9_AMT,

                  PD_10_AMT=PD_10_AMT+C1_REC.PD_10_AMT,PD_11_AMT=PD_11_AMT+C1_REC.PD_11_AMT,

                  PD_12_AMT=PD_12_AMT+C1_REC.PD_12_AMT,PD_13_AMT=PD_13_AMT+C1_REC.PD_13_AMT,

                  PD_14_AMT=PD_14_AMT+C1_REC.PD_14_AMT,PD_15_AMT=PD_15_AMT+C1_REC.PD_15_AMT,

                  PD_16_AMT=PD_16_AMT+C1_REC.PD_16_AMT,PD_17_AMT=PD_17_AMT+C1_REC.PD_17_AMT,

                  PD_18_AMT=PD_18_AMT+C1_REC.PD_18_AMT,PD_1_HRS=PD_1_HRS+C1_REC.PD_1_HRS,

                  PD_2_HRS=PD_2_HRS+C1_REC.PD_2_HRS,PD_3_HRS=PD_3_HRS+C1_REC.PD_3_HRS,

                  PD_4_HRS=PD_4_HRS+C1_REC.PD_4_HRS,PD_5_HRS=PD_5_HRS+C1_REC.PD_5_HRS,

                  PD_6_HRS=PD_6_HRS+C1_REC.PD_6_HRS,PD_7_HRS=PD_7_HRS+C1_REC.PD_7_HRS,

                  PD_8_HRS=PD_8_HRS+C1_REC.PD_8_HRS,PD_9_HRS=PD_9_HRS+C1_REC.PD_9_HRS,

                  PD_10_HRS=PD_10_HRS+C1_REC.PD_10_HRS,PD_11_HRS=PD_11_HRS+C1_REC.PD_11_HRS,

                  PD_12_HRS=PD_12_HRS+C1_REC.PD_12_HRS,PD_13_HRS=PD_13_HRS+C1_REC.PD_13_HRS,

                  PD_14_HRS=PD_14_HRS+C1_REC.PD_14_HRS,PD_15_HRS=PD_15_HRS+C1_REC.PD_15_HRS,

                  PD_16_HRS=PD_16_HRS+C1_REC.PD_16_HRS,PD_17_HRS=PD_17_HRS+C1_REC.PD_17_HRS,

                  PD_18_HRS=PD_18_HRS+C1_REC.PD_18_HRS,

                  MODIFIED_BY=sUserId,TIME_STAMP=SYSDATE,ROWVERSION=ROWVERSION+1

               WHERE ACCT_ID = C1_REC.ACCT_ID

                 AND ORG_ID = C1_REC.ORG_ID

                 AND FY_CD = sSemaphoreFyCd;

            END;

         END;

      END LOOP;

      sProcAction := sProcName || ' - Close c1';

      CLOSE c1;

   END;

   BEGIN

      sProcAction := sProcName || ' - Insert into post semaphore.';

      nStepSeqNo := nStepSeqNo + 1;

      INSERT INTO DELTEK.POST_SEMAPHORE(

         S_APPL_FUNC_ID,STEP_SEQ_NO,FY_CD,DELTEK.POST_SEMAPHORE.PD_NO,

         POST_SEQ_NO,S_JNL_CD,S_STEP_TYPE,STEP_DESC,START_DTT,END_DTT,

         MODIFIED_BY,TIME_STAMP,ROWVERSION)

         VALUES('LDPLDJ',nStepSeqNo,sSemaphoreFyCd,nSemaphorePdNo,

         nPostSeqNo,sSemaphoreSJnlCd,'STEP','BOTH - FS_SUM UPDATE',dtStepStartDtt,

         SYSDATE,sUserId,SYSDATE,0);

      COMMIT WORK;

   END;

   EXCEPTION

      when others then

         sDBErrorDesc := SQLERRM(SQLCODE);

END CP_LDPLDJ_FSSUM; 

/

show errors procedure CP_LDPLDJ_FSSUM

 

CREATE OR REPLACE PROCEDURE DELTEK.CP_LDPLDJ_REF

  (sDBErrorDesc       OUT    VARCHAR2,

   sProcAction        OUT    VARCHAR2,

   sExpectedVersion   IN     VARCHAR2,

   bValidityCheckOnly IN     INTEGER, 

   sVersionFound      IN OUT VARCHAR2,  

   nPostSeqNo         IN     INTEGER,

   nSemaphorePdNo     IN     INTEGER,

   sUserId            IN     VARCHAR2,

   sSemaphoreFyCd     IN     VARCHAR2,

   sSemaphoreSJnlCd   IN     VARCHAR2,

   dtStepStartDtt     IN     DATE,

   nStepSeqNo         IN OUT INTEGER

) is

   sProcName       VARCHAR2(30) := 'CP_LDPLDJ_REF';

   TRUE            INTEGER :=1;

 

   CURSOR c1 IS  SELECT PD_1_AMT,PD_2_AMT,PD_3_AMT,PD_4_AMT,PD_5_AMT,PD_6_AMT,

                    PD_7_AMT,PD_8_AMT,PD_9_AMT,PD_10_AMT,PD_11_AMT,PD_12_AMT,

                    PD_13_AMT,PD_14_AMT,PD_15_AMT,PD_16_AMT,PD_17_AMT,PD_18_AMT,

                    PD_1_HRS,PD_2_HRS,PD_3_HRS,PD_4_HRS,PD_5_HRS,PD_6_HRS,

                    PD_7_HRS,PD_8_HRS,PD_9_HRS,PD_10_HRS,PD_11_HRS,PD_12_HRS,

                    PD_13_HRS,PD_14_HRS,PD_15_HRS,PD_16_HRS,PD_17_HRS,PD_18_HRS,

                    ACCT_ID,ORG_ID

                 FROM DELTEK.Z_LDPLDJ_SUM

                 WHERE MODIFIED_BY IS NOT NULL;

   C1_REC c1%ROWTYPE;

BEGIN

   sDBErrorDesc := '';

   sVersionFound := '3.1A';

   if sExpectedVersion != sVersionFound then

      sProcAction := sProcName || ' - Version check';

      return;

   end if;

   if bValidityCheckOnly = TRUE then

      return;

   end if;

   BEGIN      

      OPEN c1;

      LOOP

         BEGIN

            sProcAction := sProcName || ' - Fetch records from temp table.';

            FETCH c1 INTO C1_REC;

               EXIT WHEN c1%NOTFOUND;

            BEGIN

               sProcAction := sProcName || ' - Update REF_SUM';

               UPDATE DELTEK.REF_SUM SET PD_1_AMT=PD_1_AMT+C1_REC.PD_1_AMT,

                  PD_2_AMT=PD_2_AMT+C1_REC.PD_2_AMT,PD_3_AMT=PD_3_AMT+C1_REC.PD_3_AMT,

                  PD_4_AMT=PD_4_AMT+C1_REC.PD_4_AMT,PD_5_AMT=PD_5_AMT+C1_REC.PD_5_AMT,

                  PD_6_AMT=PD_6_AMT+C1_REC.PD_6_AMT,PD_7_AMT=PD_7_AMT+C1_REC.PD_7_AMT,

                  PD_8_AMT=PD_8_AMT+C1_REC.PD_8_AMT,PD_9_AMT=PD_9_AMT+C1_REC.PD_9_AMT,

                  PD_10_AMT=PD_10_AMT+C1_REC.PD_10_AMT,PD_11_AMT=PD_11_AMT+C1_REC.PD_11_AMT,

                  PD_12_AMT=PD_12_AMT+C1_REC.PD_12_AMT,PD_13_AMT=PD_13_AMT+C1_REC.PD_13_AMT,

                  PD_14_AMT=PD_14_AMT+C1_REC.PD_14_AMT,PD_15_AMT=PD_15_AMT+C1_REC.PD_15_AMT,

                  PD_16_AMT=PD_16_AMT+C1_REC.PD_16_AMT,PD_17_AMT=PD_17_AMT+C1_REC.PD_17_AMT,

                  PD_18_AMT=PD_18_AMT+C1_REC.PD_18_AMT,PD_1_HRS=PD_1_HRS+C1_REC.PD_1_HRS,

                  PD_2_HRS=PD_2_HRS+C1_REC.PD_2_HRS,PD_3_HRS=PD_3_HRS+C1_REC.PD_3_HRS,

                  PD_4_HRS=PD_4_HRS+C1_REC.PD_4_HRS,PD_5_HRS=PD_5_HRS+C1_REC.PD_5_HRS,

                  PD_6_HRS=PD_6_HRS+C1_REC.PD_6_HRS,PD_7_HRS=PD_7_HRS+C1_REC.PD_7_HRS,

                  PD_8_HRS=PD_8_HRS+C1_REC.PD_8_HRS,PD_9_HRS=PD_9_HRS+C1_REC.PD_9_HRS,

                  PD_10_HRS=PD_10_HRS+C1_REC.PD_10_HRS,PD_11_HRS=PD_11_HRS+C1_REC.PD_11_HRS,

                  PD_12_HRS=PD_12_HRS+C1_REC.PD_12_HRS,PD_13_HRS=PD_13_HRS+C1_REC.PD_13_HRS,

                  PD_14_HRS=PD_14_HRS+C1_REC.PD_14_HRS,PD_15_HRS=PD_15_HRS+C1_REC.PD_15_HRS,

                  PD_16_HRS=PD_16_HRS+C1_REC.PD_16_HRS,PD_17_HRS=PD_17_HRS+C1_REC.PD_17_HRS,

                  PD_18_HRS=PD_18_HRS+C1_REC.PD_18_HRS,

                  MODIFIED_BY=sUserId,TIME_STAMP=SYSDATE,ROWVERSION=ROWVERSION+1

               WHERE ACCT_ID = C1_REC.ACCT_ID

                 AND REF_STRUC_ID = C1_REC.ORG_ID

                 AND FY_CD = sSemaphoreFyCd;

            END;

         END;

      END LOOP;

      sProcAction := sProcName || ' - Close c1';

      CLOSE c1;

   END;

   BEGIN

      sProcAction := sProcName || ' - Insert into post semaphore.';

      nStepSeqNo := nStepSeqNo + 1;

      INSERT INTO DELTEK.POST_SEMAPHORE(

         S_APPL_FUNC_ID,STEP_SEQ_NO,FY_CD,DELTEK.POST_SEMAPHORE.PD_NO,

         POST_SEQ_NO,S_JNL_CD,S_STEP_TYPE,STEP_DESC,START_DTT,END_DTT,

         MODIFIED_BY,TIME_STAMP,ROWVERSION)

         VALUES('LDPLDJ',nStepSeqNo,sSemaphoreFyCd,nSemaphorePdNo,

         nPostSeqNo,sSemaphoreSJnlCd,'STEP','BOTH - REF_SUM UPDATE',dtStepStartDtt,

         SYSDATE,sUserId,SYSDATE,0);

      COMMIT WORK;

   END;

   EXCEPTION

      when others then

         sDBErrorDesc := SQLERRM(SQLCODE);

END CP_LDPLDJ_REF; 

/

show errors procedure CP_LDPLDJ_REF

 

 

CREATE OR REPLACE PROCEDURE DELTEK.CP_LDPLDJ_LV_HS

  (sDBErrorDesc       OUT    VARCHAR2,

   sProcAction        OUT    VARCHAR2,

   sExpectedVersion   IN     VARCHAR2,

   bValidityCheckOnly IN     INTEGER, 

   sVersionFound      IN OUT VARCHAR2,  

   sUserId            IN     VARCHAR2,

   nTempSeqNo         IN     INTEGER) 

is

   sProcName       VARCHAR2(30) := 'CP_LDPLDJ_LV_HS';

   nLastKey        INTEGER := 0;

   TRUE            INTEGER := 1;

 

 

 

   sEmplId       DELTEK.Z_LDPLDJ_LV_HIST.EMPL_ID%TYPE;

   sLvTypeCd     DELTEK.Z_LDPLDJ_LV_HIST.LV_TYPE_CD%TYPE;

   nLvYr         DELTEK.Z_LDPLDJ_LV_HIST.LV_YR_NO%TYPE;

   sLvPdCd       DELTEK.Z_LDPLDJ_LV_HIST.LV_PD_CD%TYPE;

   dtPdEndDt     DELTEK.Z_LDPLDJ_LV_HIST.LV_PD_END_DT%TYPE;

   nTsLnKey      DELTEK.Z_LDPLDJ_LV_HIST.TS_LN_KEY%TYPE;

 

 

   

 

CURSOR Z_LV_HIST IS

 SELECT 

   EMPL_ID, LV_TYPE_CD, LV_YR_NO, LV_PD_CD,LV_PD_END_DT,TS_LN_KEY 

 FROM 

   DELTEK.Z_LDPLDJ_LV_HIST 

 ORDER BY EMPL_ID;

 

BEGIN

   sDBErrorDesc := '';

   sVersionFound := '3.1A';

   IF sExpectedVersion != sVersionFound THEN

      sProcAction := sProcName || ' - Version check';

      RETURN;

   END IF;

   IF bValidityCheckOnly = TRUE THEN

      RETURN;

   END IF;

 

   sProcAction := sProcName || ' - Z_LDPLDJ_LV_HIST fetch loop.';

   nLastKey := nTempSeqNo;

   OPEN Z_LV_HIST;

   LOOP

      BEGIN

         FETCH Z_LV_HIST INTO sEmplId,sLvTypeCd,nLvYr,sLvPdCd,dtPdEndDt,nTsLnKey ;  

         EXIT WHEN Z_LV_HIST%NOTFOUND;

 

         INSERT INTO DELTEK.Z_LDPLDJ_KEYS

           (SEQ_NO, KEY_CHAR_1, KEY_CHAR_2, KEY_INT_1, KEY_CHAR_3, KEY_DATE_1,KEY_INT_2,

            MODIFIED_BY, TIME_STAMP, ROWVERSION) 

         VALUES

           (nLastKey,sEmplId,sLvTypeCd,nLvYr,sLvPdCd,dtPdEndDt,nTsLnKey,

            sUserId,SYSDATE,0);

 

         nLastKey := nLastKey + 1;

      END;

   END LOOP;

   CLOSE Z_LV_HIST;

   COMMIT WORK;

END CP_LDPLDJ_LV_HS;

/

SHOW ERRORS PROCEDURE CP_LDPLDJ_LV_HS

Open in new window

0
Comment
Question by:la_colibri
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the error means that the connected user does have accces to a procedure named "ora" under the schema ldpldj, or to a procedure named "ora" in package ldpldj in it's current schema, or that the procedure indeed does not exist.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
just a side question: LDPLDJ.ora is not a file, isn't it?
0
 

Author Comment

by:la_colibri
Comment Utility
"LDPLDJ.ora" is a file that was sent to me requesting to be applied to the database... problem is I wanted to run it in development and test prior to production just because I did not write stored procedure but I am unable to do so and getting errors instead.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
if it's a file:

SQL> @LDPLDJ.ora;
 

you might need to specify the entire path.

Open in new window

0
 

Author Comment

by:la_colibri
Comment Utility
Is this what you meant? Still got an error messsage.

SQL> EXEC c:/LDPLDJ.ora;
BEGIN c:/LDPLDJ.ora; END;

       *
ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00103: Encountered the symbol ":" when expecting one of the following:
:= . ( @ % ;
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
no, I meant to use @ isntead of EXEC or EXECUTE
0
 

Author Closing Comment

by:la_colibri
Comment Utility
Thanks it worked...  procedure created without errors
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now