Solved

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

Posted on 2008-06-24
7
2,992 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]
ID: 21856253
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]
ID: 21856260
just a side question: LDPLDJ.ora is not a file, isn't it?
0
 

Author Comment

by:la_colibri
ID: 21856388
"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]
ID: 21856700
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
ID: 21856981
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
ID: 21857054
no, I meant to use @ isntead of EXEC or EXECUTE
0
 

Author Closing Comment

by:la_colibri
ID: 31470170
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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

776 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