%rowtype

Hello,
Would you please tell me what I did wrong here?  I couldn't find the rowtype eliminate with a cursor in multi-tables selected.

CREATE OR REPLACE FUNCTION "CIDR_STAGING"."PR_MIG_STG_I864" ( v_Ret OUT int ) RETURNS integer as $$ declare    c1 cursor is      SELECT stg.Service_Center, stg.Receipt_Number, stg.Date_Entered, stg.Sequence_Number,         stg.SSN, stg.A_Number, stg.Last_Name, stg.First_Name, stg.Middle_Name, stg.DOB,         stg.Street_Mail, stg.City_Mail, stg.State_Mail, stg.Zip_Mail, stg.Province_Mail,         stg.Postal_Code_Mail, stg.Country_Mail, stg.Street_Res, stg.City_Res, stg.State_Res,         stg.Zip_Res, stg.Province_Res, stg.Postal_Code_Res, stg.Country_Res, stg.US_Citizen,         stg.Basis, stg.Accompany, stg.mig_filename,stg.mig_insert_dt,stg.mig_modified_dt,         prod.receipt_number as prod_receipt_number, prod.date_entered as prod_date_entered,         prod.sequence_number as prod_sequence_number      FROM cidr_staging.stg_i864 stg LEFT OUTER JOIN cidrdba.sc_i864 prod         ON coalesce(stg.receipt_number,'NULL') = coalesce(prod.receipt_number,'NULL')        AND coalesce(stg.date_entered,'NULL') = coalesce(prod.date_entered,'NULL')        AND coalesce(stg.sequence_number,'NULL') = coalesce(prod.sequence_number,'NULL')      ORDER by stg.mig_seq;   rec1            c1%rowtype;   v_rows         int = 0;   v_seq          int =0;   v_ErrorCode    int;   v_ErrorMsg     varchar(512);   v_Module       varchar(32) = 'PR_MIG_STG_I864';   v_DDL          varchar(10);   v_Rec_Num      cidr_staging.stg_i864.receipt_number%type;   v_Dat_Ent      cidr_staging.stg_i864.date_entered%type;   v_Seq_Num      cidr_staging.stg_i864.sequence_number%type;
begin
   v_Ret := 0;   for rec1 in c1
loop      --dbms_output.put_line('Processing Receipt_Number ' || rec1.receipt_number);      ----      -- If the PROD_RECEIPT_NUMBER is null, then the record does not exist in the Production table.      ----      if rec1.prod_receipt_number is null then         v_Rec_Num := rec1.receipt_number;         v_Dat_Ent := rec1.date_entered;         v_Seq_Num := rec1.sequence_number;
         insert into cidrdba.sc_i864 values (            rec1.Service_Center, rec1.Receipt_Number, coalesce(rec1.Date_Entered,''),            rec1.Sequence_Number, rec1.ssn, rec1.a_number, rec1.last_name, rec1.first_name,            rec1.middle_name, rec1.dob, rec1.street_mail, rec1.city_mail, rec1.state_mail,            rec1.zip_mail, rec1.Province_Mail, rec1.Postal_Code_Mail, rec1.Country_Mail,            rec1.Street_Res, rec1.City_Res, rec1.State_Res, rec1.Zip_Res, rec1.Province_Res,            rec1.Postal_Code_Res, rec1.Country_Res, rec1.US_Citizen, rec1.Basis, rec1.Accompany,            rec1.mig_filename, rec1.mig_insert_dt, rec1.mig_modified_dt );         v_rows := sql%rowcount;         if v_rows != 1 then            v_Ret := 1;            PERFORM pr_write_error_log( sys_context('userenv','session_user'),                                sys_context('userenv','host'), v_Module,                                0, 'INSERT processed ' || v_rows );         end if;      elsif coalesce(rec1.prod_receipt_number,'NULL')  = coalesce(rec1.receipt_number,'NULL') and            coalesce(rec1.prod_date_entered,'NULL')    = coalesce(rec1.date_entered,'NULL') and            coalesce(rec1.prod_sequence_number,'NULL') = coalesce(rec1.sequence_number,'NULL') then         v_Rec_Num := rec1.receipt_number;         v_Dat_Ent := rec1.date_entered;         v_Seq_Num := rec1.sequence_number;         update cidrdba.sc_i864 set            SSN                    = rec1.SSN,            A_Number               = rec1.A_Number,            Last_Name              = rec1.Last_Name,            First_Name             = rec1.First_Name,            Middle_Name            = rec1.Middle_Name,            DOB                    = rec1.DOB,            Street_Mail            = rec1.Street_Mail,            City_Mail              = rec1.City_Mail,            State_Mail             = rec1.State_Mail,            Zip_Mail               = rec1.Zip_Mail,            Province_Mail          = rec1.Province_Mail,            Postal_Code_Mail       = rec1.Postal_Code_Mail,            Country_Mail           = rec1.Country_Mail,            Street_Res             = rec1.Street_Res,            City_Res               = rec1.City_Res,            State_Res              = rec1.State_Res,            Zip_Res                = rec1.Zip_Res,            Province_Res           = rec1.Province_Res,            Postal_Code_Res        = rec1.Postal_Code_Res,            Country_Res            = rec1.Country_Res,            US_Citizen             = rec1.US_Citizen,            Basis                  = rec1.Basis,            Accompany              = rec1.Accompany,            mig_filename           = rec1.mig_filename,            mig_modified_dt        = current_timestamp         where            coalesce(receipt_number,'NULL')         = coalesce(rec1.receipt_number,'NULL') and            coalesce(date_entered,'NULL')           = coalesce(rec1.date_entered,'NULL') and            coalesce(sequence_number,'NULL')        = coalesce(rec1.sequence_number,'NULL');         v_rows := sql%rowcount;         if v_rows != 1 then            v_Ret := 1;            pr_write_error_log( sys_context('userenv','session_user'),                                sys_context('userenv','host'), v_Module,                                0, 'UPDATE processed ' || v_rows || ', expected 1' );         end if;      else         v_Ret := 1;         PERFORM pr_write_error_log( sys_context('userenv','session_user'),         sys_context('userenv','host'), v_Module,         0, 'FAILED to process: ' ||         rec1.Receipt_Number || ', ' || rec1.Date_Entered || ', ' || rec1.Sequence_Number );      end if;      exit when v_Ret != 0 or SQLCODE != 0;   end loop;
   if v_Ret = 0 then      v_Ret := SQLCODE;   end if;
exception   when others then      v_ErrorCode := SQLCODE;      v_ErrorMsg  := SQLERRM;
      if inserting then         v_ddl := 'INSERT';      elsif updating then         v_ddl := 'UPDATE';      else         v_ddl := 'UNKNOWN';      end if;
      PERFORM pr_write_error_log( sys_context('userenv','session_user'),      sys_context('userenv','host'), v_Module,      0, v_ddl || ' encountered FALTAL ERROR: (' || v_ErrorCode || '): ' || v_ErrorMsg );
      --dbms_output.put_line('FATAL ERROR while ' || v_ddl || ':  Encountered (' || v_ErrorCode || ') : ' || v_ErrorMsg );      --dbms_output.put_line('FATAL ERROR:  Record ' || nvl(v_Rec_Num,'null') || '~' || nvl(v_Dat_Ent,'null') || '~' || nvl(v_Seq_Num,'null') );end;$$ LANGUAGE plpgsql;

ERROR:  relation "c1" does not existCONTEXT:  compilation of PL/pgSQL function "PR_MIG_STG_I864" near line 17SQL state: 42P01  I found the %rowtype could declare of the table%rowtype,myrow tablename%ROWTYPE; but my oracle script had union tables, how can I declare it?  I have tried declare rec1 stg%rowtype or prod%rowtype and/or the cidr_staging.stg_i864%rowtype... the error is still same, relation does not exist.
Any helps and explanation would be appreciated.  
v/r,
Bach-Nga

No one in this world is pure and perfect.  If you avoid people for their mistakes you will be alone. So judge less, love and forgive more.To call him a dog hardly seems to do him justice though in as much as he had four legs, a tail, and barked, I admit he was, to all outward appearances. But to those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely rich *** Faithful talent *** Sharing success