CMS Snapshot Analysis - Update Sources - Fatal Error - Error while executing Procedure

While running the snapshot, it fails with the error "Error while executing Procedure".

Please find the screen shot below:

1. Package and deliver the source code.
2. Accept the delivery and set as current version.
3. Run snapshot , while generating snapshot , it throws an Error while executing Procedure when performing Update Sources in Local service step.

1. Find which procedure is failing, for this run the snapshot in SQL mode,
   1. Check the CAST- MS log in normal mode and in SQL mode.
      CAST- MS log in normal mode:
      
      CAST - MS log in SQL mode:
      
      From the CAST - MS log in SQL mode above we could see that procedure which is failing is dssapp_source_pos_update.
2. Run the snapshot by un checking the checkbox  "Always upload source code for viewing in CAST Dashboard" as shown in the screen shot below, this will prevent the source code to be uploaded in the Local Database and will make the source code not visible in Dashboard.
   
3. Run the below query in the Local base which will replace the present DSSAPP_SOURCE_POS_UPDATE () procedure with the optimized procedure.

   1. create or replace function DSSAPP_SOURCE_POS_UPDATE () returns integer as
      $body$
      declare
        L_ERRORCODE     int default 0;
      begin
        
        /* finalyzing DSS table for position */
        delete from DSS_SOURCE_POSITIONS 
              where SOURCE_ID < 0;
        
        insert into DSS_SOURCE_POSITIONS (OBJECT_ID, SOURCE_ID, LINE_START, LINE_END, COL_START, COL_END, PANEL) 
        select ops.IdObj, dcs.SOURCE_ID, ops.Info1, ops.Info3, ops.Info2, ops.Info4, ops.BlkNo
          from DSS_CODE_SOURCES dcs join RefPath rp on (dcs.SOURCE_PATH = rp.Path)
                                    join ObjFilRef ofr on (ofr.IdFilRef = rp.IdFilRef)
                                    join ObjPos ops on (ops.IdObjRef = ofr.IdObj)
         where dcs.SOURCE_ID < 0;
               
        /* Objects with Code but without positions  : bookmark set to -1,-1,-1,-1 */
        insert into DSS_SOURCE_POSITIONS (OBJECT_ID, SOURCE_ID, LINE_START, LINE_END, COL_START, COL_END, PANEL) 
        select ofr.IdObj, dcs.SOURCE_ID, -1, -1, -1, -1, -1
        from  ObjFilRef ofr join RefPath rp on (ofr.IdFilRef = rp.IdFilRef)
                            join DSS_CODE_SOURCES dcs on (dcs.SOURCE_PATH = rp.Path)   
        where
          not exists (select 1 from DSS_SOURCE_POSITIONS where OBJECT_ID = ofr.IdObj)
         and dcs.SOURCE_ID < 0;
               
        /* Generate translation table for bookmark */
        delete from DSS_SourceIdTranslation 
              where DSS_SourceId < 0;
        
        insert into DSS_SourceIdTranslation (KB_SourceId, DSS_SourceId)
        select distinct p.SourceId, dcs.SOURCE_ID
           from (select distinct p.SourceId from DSS_Positions p) p 
               join (select distinct IdFil, IdFilRef from ObjFilRef ofr) ofr on (ofr.IdFil = p.SourceId) 
           join RefPath rp on (rp.IdFilRef = ofr.IdFilRef)
           join DSS_CODE_SOURCES dcs on (rp.Path = dcs.SOURCE_PATH)
         where dcs.SOURCE_ID < 0;
        
        return 0;
      end;
      $body$
      LANGUAGE plpgsql
      

4. Go to the Local service and click on "Upload source code for viewing in CAST Dashboard" option, as shown in the screen shot below:
   
   
5. Or you can run the snapshot by skipping the analysis.

Impact of issue: Snapshot crashes with error message in the CAST - MS log.
Impact after applying solution: Snapshot runs fine.

Ticket # 5325