create or replace 
procedure     AttachPdf2(exp_id in results.expid%type, prot_id in results.protid%type, batch_code in results.batchcode%type, file in blob, pdocext in RESULTS.DOCEXT%type) is
	tmplob results.pdf%type;
begin
		update results set pdf = file, docext = pdocext, docflag = 'Y' where expid=exp_id and protid=prot_id and batchcode=batch_code;
end;
/

create or replace 
procedure DeleteLinkedProtocol (
    SumProtocol in rblinkedprotocols.SUMMARIZED_RECBIO_PROTID%type
  ) iS
    cursor cucco is select recbio_protid from RBLINKEDPROTOCOLS where SUMMARIZED_RECBIO_PROTID=SumProtocol for update;
    cuccato rblinkedprotocols.SUMMARIZED_RECBIO_PROTID%type;
  BEGIN
  open cucco;
  fetch cucco into cuccato;
  if cucco%found then
    delete from RBLINKEDVARIABLES where RECBIO_PROTID=cuccato;
    delete from RBLINKEDPROTOCOLS where current of cucco;
  end if;
  close cucco;
  END DeleteLinkedProtocol;
/

create or replace 
PROCEDURE DELIST(prefix in varchar2 default 'ICS_TMP%') AS
  retval integer;
begin
  retval := DELISTEMP(prefix);
end DELIST;
/

create or replace 
PROCEDURE SETSUMMARYLOG (
	PPROTID IN SUMMARY_LOG.PROTID%TYPE,
	PBATCHCODE IN SUMMARY_LOG.BATCHCODE%TYPE,
	PSTATUS IN SUMMARY_LOG.STATUS%TYPE,
	PMESSAGE IN SUMMARY_LOG.MESSAGE%TYPE := NULL
) AS
  CURSOR SELO IS SELECT PROTID, BATCHCODE FROM SUMMARY_LOG WHERE PROTID=PPROTID AND BATCHCODE=PBATCHCODE FOR UPDATE;
  SELECTOR SELO%ROWTYPE;
BEGIN
	OPEN SELO;
  FETCH SELO INTO SELECTOR;
  IF SELO%FOUND THEN
    UPDATE SUMMARY_LOG SET
      STATUS = PSTATUS,
      MESSAGE = PMESSAGE,
      TSDATE = SYSDATE
    WHERE CURRENT OF SELO;
  ELSE
    INSERT INTO SUMMARY_LOG (
      PROTID,
      BATCHCODE,
      STATUS,
      MESSAGE,
      TSDATE
    ) VALUES (
      PPROTID,
      PBATCHCODE,
      PSTATUS,
      PMESSAGE,
      SYSDATE
    );     
  END IF;
  CLOSE SELO;
END;
/

create or replace 
PROCEDURE SUMMARIZELOG AS
  CURSOR SELO IS SELECT PROTID, BATCHCODE FROM SUMMARY_LOG WHERE STATUS='R';
BEGIN
  FOR SELECTOR IN SELO LOOP
    BEGIN
      SUMMARIZE(SELECTOR.PROTID, SELECTOR.BATCHCODE, 1);
      SETSUMMARYLOG(SELECTOR.PROTID, SELECTOR.BATCHCODE, 'P');
   EXCEPTION
      WHEN OTHERS THEN
        SETSUMMARYLOG(SELECTOR.PROTID, SELECTOR.BATCHCODE, 'E', SQLERRM);
    END;
  END LOOP;
END SUMMARIZELOG;
/

create or replace 
PROCEDURE UPDATEEXPERIMENTFIELD (
  pExpId in out RESULTS.EXPID%type,
  pProtid in results.protid%type,
  pBatchCode in BATCH.BATCHCODE%type,
  pValue in varchar2,
  pColumn in varchar2
) AS
  pSeparator varchar2(1);
  pAntiSeparator varchar2(1);
  pTmp results.text1%type;
BEGIN
  if pExpId is null or pExpId < 1 then --new record
    pExpId := getrec;
    insert into RESULTS (expid, protid, batchcode, expcode, status)
      values (pExpId, pProtid, pBatchCode, pExpId, 'Y');
  end if;
  if pColumn is not null then
    --CHECK IF NUMERIC FIELD
    IF UPPER(SUBSTR(pColumn,1,1))='V' THEN --numeric
      select substr(value,1,1) into pSeparator from v$nls_parameters
        where parameter = 'NLS_NUMERIC_CHARACTERS';
      if pSeparator = '.' then
        pAntiSeparator := ',';
      else
        pAntiSeparator := '.';
      end if;
      pTmp := replace(pValue, pAntiSeparator, pSeparator); --replace bad with goog in case
    ELSE
      pTmp := pvalue;
    END IF;
    -- update record
    execute immediate 'update results set ' || pColumn || '=:1 where expid=:2 and protid=:3 and batchcode=:4'
      using pTmp, pExpId, pProtid, pBatchCode;
  end if;
END UPDATEEXPERIMENTFIELD;
/

create or replace 
PROCEDURE UpdateLinkedProtocol (
    SumProtocol in rblinkedprotocols.SUMMARIZED_RECBIO_PROTID%type,
    RawProtocol in RBLINKEDPROTOCOLS.RECBIO_PROTID%type,
    CountField in RBLINKEDPROTOCOLS.REPLICATE_NUMBER_FIELDCOL%type,
    Active in RBLINKEDPROTOCOLS.TO_SUMMARIZE%type,
    ForceVarDelete in number
  ) iS
    cursor pesca is select * from RBLINKEDPROTOCOLS where SUMMARIZED_RECBIO_PROTID=SumProtocol for update;
    pescatore pesca%rowtype;
  BEGIN
    open pesca;
    fetch pesca into pescatore;
    if pesca%found then --update
      if rawprotocol != pescatore.RECBIO_PROTID or ForceVarDelete != 0 then --must delete depending variables
        delete from rblinkedvariables where RECBIO_PROTID=pescatore.RECBIO_PROTID;
      end if;
      update RBLINKEDPROTOCOLS set
        RECBIO_PROTID = RawProtocol,
        REPLICATE_NUMBER_FIELDCOL = CountField,
        TO_SUMMARIZE = Active
      where current of pesca;
      close pesca;
    else
      close pesca;
      insert into RBLINKEDPROTOCOLS (
        SUMMARIZED_RECBIO_PROTID,
        RECBIO_PROTID,
        REPLICATE_NUMBER_FIELDCOL,
        TO_SUMMARIZE
      ) values (
        SumProtocol,
        RawProtocol,
        CountField,
        Active
      );
    end if;
  END UpdateLinkedProtocol;
/

create or replace 
PROCEDURE UpdateLinkedVariable (
    RawProtocol in RBLINKEDvariables.RECBIO_PROTID%type,
    RawField in RBLINKEDVARIABLES.FIELDCOL%type,
    SummaryField in RBLINKEDVARIABLES.SUMMARY_FIELDCOL%type,
    Active in RBLINKEDVARIABLES.TO_SUMMARIZE%type
  ) iS
    cursor becca is select * from RBLINKEDVARIABLES where RECBIO_PROTID=RawProtocol and 
      fieldcol=rawfield for UPDATE;
    beccato becca%rowtype;
  BEGIN
    open becca;
    fetch becca into beccato;
    if becca%found then
      update RBLINKEDVARIABLES set
        SUMMARY_FIELDCOL = SummaryField,
        TO_SUMMARIZE = Active
      where current of becca;
      close becca;
    else
      close becca;
      insert into RBLINKEDVARIABLES (
        RECBIO_PROTID,
        FIELDCOL,
        SUMMARY_FIELDCOL,
        TO_SUMMARIZE
      ) values (
        RawProtocol,
        RawField,
        SummaryField,
        Active
      );
    end if;
  END UpdateLinkedVariable;
/

create or replace 
PROCEDURE UPDATENAMES AS 
cursor curo is select 
  PACTIVE,
  IUPAC_NAME,
  CAS_NAME,
  OLD_NAME
  from name_update for update;
cursor propi(patti chemical_properties.pactive%type) is select 
  pactive from chemical_properties where pactive=patti for update;
pattini chemical_properties.pactive%type;
BEGIN
  FOR nuovi IN curo
  LOOP
    open propi(nuovi.pactive);
    fetch propi into pattini;
    if propi%found then --update
      if trim(nuovi.IUPAC_NAME) is not null then
        update chemical_properties set IUPAC_NAME=nuovi.IUPAC_NAME where current of propi;
      end if;
      if trim(nuovi.CAS_NAME) is not null then
        update chemical_properties set CAS_NAME=nuovi.CAS_NAME where current of propi;
      end if;
      if trim(nuovi.OLD_NAME) is not null then
        update chemical_properties set OLD_NAME=nuovi.OLD_NAME where current of propi;
      end if;
    else --insert
      insert into chemical_properties (
        PACTIVE,
        IUPAC_NAME,
        CAS_NAME,
        OLD_NAME
      ) values (
        nuovi.PACTIVE,
        nuovi.IUPAC_NAME,
        nuovi.CAS_NAME,
        nuovi.OLD_NAME
      );
    end if;
    close propi;
    if trim(nuovi.IUPAC_NAME) is not null then
      update CHEMDB_MOLTABLE SET MOLNAME = nuovi.IUPAC_NAME where pactive=nuovi.PACTIVE;
    end if;
    delete from name_update where current of curo;
  END LOOP ;
END UPDATENAMES;
/

create or replace 
procedure UpsertProtocol (
    pprotid in out protocol.PROTID%type,
    pBIOAREAID in protocol.BIOAREAID%type,
    pGROUPID in protocol.GROUPID%type,
    pNAME in protocol.NAME%type,
    pSTATUS in protocol.STATUS%type,
    pPCOMMENT in protocol.PCOMMENT%type,
    pSOP in protocol.SOP%type
  ) as
  BEGIN
    if pprotid is null or pprotid < 1 then --insert
      pprotid := getrec;
      insert into protocol (
        PROTID,
        BIOAREAID,
        GROUPID,
        NAME,
        STATUS,
        PCOMMENT,
        SOP
      ) values (
        pPROTID,
        pBIOAREAID,
        pGROUPID,
        pNAME,
        pSTATUS,
        pPCOMMENT,
        pSOP
      );
    else
      update protocol set
        BIOAREAID = BIOAREAID,
        GROUPID = pGROUPID,
        NAME = pNAME,
        STATUS = pSTATUS,
        PCOMMENT = pPCOMMENT,
        SOP = pSOP
      where protid = pprotid;
    end if;
  END UpsertProtocol;
/

create or replace 
procedure UpsertTerms (
    pprotid in out protocol_terms.PROTID%type,
    ptermid in protocol_terms.termid%type
  ) as
  BEGIN
    if ptermid is null then
      delete from protocol_terms where protid=pprotid;
    else
      insert into protocol_terms (
        PROTID,
        TERMID
      ) values (
        pPROTID,
        pTERMID
      );
    end if;
  END UpsertTerms;
/

create or replace 
procedure UpsertVariables (
    pPROTID in variables.PROTID%type,
    pVARLISTID in variables.VARLISTID%type,
    pVORDER in variables.VORDER%type,
    pFIELDCOL in variables.PROTID%type,
    pFLAG in variables.FLAG%type
  ) as
  cursor curo is select * from variables where protid=pprotid and pVARLISTID=VARLISTID for UPDATE;
  curtipo curo%rowtype;
  BEGIN
    if pVARLISTID is null then
      delete from variables where protid=pprotid;
    else
      open curo;
      fetch curo into curtipo;
      if curo%found then
        update variables set
          VORDER = pVORDER,
          FIELDCOL = pFIELDCOL,
          FLAG = pFLAG
        where current of curo;
      else
      insert into variables (
        PROTID,
        VARLISTID,
        VORDER,
        FIELDCOL,
        FLAG
      ) values (
        pPROTID,
        pVARLISTID,
        pVORDER,
        pFIELDCOL,
        pFLAG
      );
       end if;
   end if;
  END UpsertVariables;
/