Thread: Function error

 -----------------------

ERROR:  syntax error at or near "*"
LINE 35: SELECT * from logError(msg text) is
                ^
CONTEXT:  invalid type name "* from logError(msg text) is

BEGIN
    insert into SMERROR_LOG
    (error_message,  error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
       values(msg,  nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"
********** Error **********

ERROR: syntax error at or near "*"
SQL state: 42601
Character: 1276
Context: invalid type name "* from logError(msg text) is

BEGIN
    insert into SMERROR_LOG
    (error_message,  error_log_id, method_name, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
       values(msg,  nextval('smerror_log_sequence'), 'Gen_Budget_Periods_ALL_ForYear',subscriberID, companyID, LOCALTIMESTAMP, 'Admin', LOCALTIMESTAMP, 'Admin')"

--------------

Function Code as below:

----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Function: cp_property_room_count_trans(bigint, bigint, text)

-- DROP FUNCTION cp_property_room_count_trans(bigint, bigint, text);

CREATE OR REPLACE FUNCTION cp_property_room_count_trans(
    subscriberid bigint,
    incompanyid bigint,
    loginname text)
  RETURNS void AS
$BODY$
DECLARE

 
  CRS_1 CURSOR FOR SELECT distinct company_id from CP_ROOM_COUNT_STAGING where subscriber_id=subscriberID and (process_flag is null or process_flag=0)  order by company_id;

  --Version:
  -- 05/16/07 (Bea) transformed data from CP_ROOM_COUNT_STAGING
  --   into CP_ROOM_TYPE_COUNT and CP_PROPERTY_PHASE tables
  -- 06/08/07 (Bea) insert value for CP_ROOM_TYPE_COUNT.room_budget_home_amt
  --          If phaseID is new, validate that these as required fields  :CORP_PHASE_ID, PHASE_DESCRIPTION or PHASE_START_DATE
  --           Will set process_flag=2 if fail the validation.
  --          CP_PROPERTY_PHASE.PHASE_1_CODE field must be populated per business rule 15327    
  --
 
  RW_1 RECORD; --CRS_1 %ROWTYPE;
  err_msg_1 varchar(100);
  v_errorMessage_1 varchar(4000);
  sucessfulRecCount bigint :=0;
  failedRecCount bigint :=0;
 
  --************************************************************
  SELECT logError(v_errorMesg text, procedureName text, subscriberID bigint, companyID bigint, supplierSku text, loginName text) is
 
BEGIN
     insert into SMERROR_LOG
     (error_message, method_name, system_message, error_log_id, subscriber_id, company_id, creation_date, creation_user, update_date, update_user)
     values(v_errorMesg, procedureName, supplierSku, nextval('smerror_log_sequence'), subscriberID, companyID, LOCALTIMESTAMP, loginName, LOCALTIMESTAMP, 'V7Admin'); 
  end;

  --************************************************************
 
  FUNCTION Invoke_InsertPropertyRCProcess(subID in number, compID in number, loginName in varchar2) is
    
      CRS CURSOR FOR
             SELECT st.*, st.rowid, ph.project_code from CP_ROOM_COUNT_STAGING st, CP_PROJECT_PHASE ph 
             where st.SUBSCRIBER_ID =subID and st.company_id=compID and (st.process_flag is null or st.process_flag=0)
             and ph.SUBSCRIBER_ID(+)= st.SUBSCRIBER_ID AND ph.PHASE_ID(+)= st.CORP_PHASE_ID order by st.subscriber_id, st.company_id, st.phase_id;
      
     RW CRS%ROWTYPE;
     err_msg varchar2(100);
     v_errorMessage varchar2(4000);
     v_errorCountExp number :=0;
     MAX_ERR_WRITTEN constant number :=10;
     recCount number := 0;
  
     checkCount number;
     startRowCounter number :=0;
     isValidated boolean :=true;
    

     begin
       logError('Begin Invoke_InsertPropertyRCProcess subID:'|| subID || ' and compID:'||compID,'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);    
      
       -- the row_number counter starting number
       select max(Row_number) into startRowCounter from CP_ROOM_TYPE_COUNT 
       where subscriber_id=subID and company_id=compID;
      
       if(startRowCounter is null) then
           startRowCounter :=0;
       end if;
            
       open CRS;
       loop
          begin -- the begin inside the loop
             fetch CRS into RW;
             IF NOT FOUND THEN EXIT; END IF; -- apply on CRS
               recCount := recCount+1;
               startRowCounter := startRowCounter+1;
              
               --check to if phase_id already existed in CP_PROPERTY_PHASE  if not insert
               select  count(1)  into checkCount from CP_PROPERTY_PHASE
               where subscriber_id=subID and company_id=compID and PHASE_ID=RW.PHASE_ID;
              
               isValidated := true;
                --check to see if pass validation.
               if(checkCount =0 and (RW.phase_start_date is null or RW.phase_description is null or RW.corp_phase_id is null)) then
                    isValidated:=false;
                    --update processing flag to 2 as fail.
                   update CP_ROOM_COUNT_STAGING set process_flag=2 where rowid=RW.rowid;
                   failedRecCount := failedRecCount+1;
               end if;
                
               if(isValidated = true) then    
                 if(checkCount =0 ) then -- if phase_id not yet existed in the cp_property_phase table
                   insert into cp_property_phase
                   (subscriber_id, company_id, phase_id, phase_seq, phase_description,
                   corp_phase_id, phase_start_date, duration_type, phase_status, po_created_flag,
                   project_code, phase_level_path, is_deleted, phase_1_code,
                   creation_date, creation_user)
                   values
                   (subID, compID, RW.phase_id, RW.phase_id, RW.phase_description,
                   RW.corp_phase_id, RW.phase_start_date, 'W', 0, 0,
                   RW.project_code,  RW.phase_id, 0,
                   lpad(to_char(RW.phase_id),5,'0')||' '||upper(substring(RW.phase_description from 1 for 40)),
                   LOCALTIMESTAMP, loginName);
                  end if;--if(checkCount =0 )
            
                  --insert into CP_ROOM_TYPE_COUNT  table
                  insert into cp_room_type_count
                  (subscriber_id, company_id, phase_id,  room_type_code,
                  room_count, room_measure_unit, room_length, room_width,
                  room_height, bath_length, bath_width, row_number, room_budget_home_amt,
                  creation_date, creation_user)
                  values
                  (subID, compID, RW.phase_id, RW.room_type_code,
                   RW.room_count, RW.room_measure_unit, RW.room_length, RW.room_width,
                   RW.room_height, RW.bath_length, RW.bath_width, startRowCounter, RW.room_budget_home_amt,
                   LOCALTIMESTAMP, loginName);
              
                   --update processing flag to 1 as successful.
                   update CP_ROOM_COUNT_STAGING set process_flag=1 where rowid=RW.rowid;
                   sucessfulRecCount := sucessfulRecCount +1;
                  
                   if(mod(recCount, 500) = 0 ) then
                        commit;
                   end if;
               end if; -- if(isValidated = true)
             exception
             when others then
                if(v_errorCountExp < MAX_ERR_WRITTEN) then  
                    err_msg := substring(SQLERRM from 1 for 100);
                    v_errorMessage:=  'Run into Exception in Invoke_InsertPropertyRCProcess: ' || err_msg;
                    logError(v_errorMessage, 'Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);  
                 end if;
                 v_errorCountExp := v_errorCountExp+1;
                 commit;       
            end;   --for inside begin
          end loop;                                                              
      close CRS;
      
      if( failedRecCount > 0) then
          logError('Error: set cp_room_count_staging.process_flag=2 ( Failed to insert new records dued to one of these required fields are empty: Corp Phase ID, Phase Desc or Phase Start Date)','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);
      end if;
     
      logError('Ending Invoke_InsertPropertyRCProcess Total records: '||recCount|| '.  There are ' || sucessfulRecCount || ' sucessfull and ' || failedRecCount ||' failed records.','Invoke_InsertPropertyRCProcess', subID, compID,null, loginName);  
      commit;
  end Invoke_InsertPropertyRCProcess;
 
  --************************************************************

begin

   logError('Begin CP_Property_Room_Count_Trans ','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);    
 
   open CRS_1;
       loop
          begin -- the begin inside the loop
             fetch CRS_1 into RW_1;
             IF NOT FOUND THEN EXIT; END IF; -- apply on CRS_1
            
             sucessfulRecCount := 0;
             failedRecCount := 0;
             Invoke_InsertPropertyRCProcess(subscriberID, RW_1.company_ID, loginName);
             
             exception
             when others then
                    err_msg_1 := substring(SQLERRM from 1 for 100);
                    v_errorMessage_1:=  'Run into Exception in CP_Property_Room_Count_Trans: ' || err_msg_1;
                    logError(v_errorMessage_1, 'CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);  
                
                 commit;       
          end;   --for inside begin
       end loop;                                                              
   close CRS_1;
     
   logError('Ending CP_Property_Room_Count_Trans.','CP_Property_Room_Count_Trans', subscriberID, inCompanyID,null, loginName);    
   commit;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE SECURITY DEFINER
  COST 100;
ALTER FUNCTION cp_property_room_count_trans(bigint, bigint, text)
  OWNER TO postgres;