create or replace 
package      recBO
AS
FUNCTION prelievo
  (
    sbarcode IN VARCHAR2,
    qtp      IN NUMBER,
    suser    IN VARCHAR2 DEFAULT NULL,
    sdate    IN DATE DEFAULT NULL)
  RETURN NUMBER;
PROCEDURE registermol
  (
    ppactive  IN OUT chemdb_moltable.pactive%TYPE,
    mole      IN CLOB,
    plabbbok  IN chemdb_moltable.labbook%TYPE,
    pprogram  IN chemdb_moltable.PROGRAM %TYPE,
    pcode     IN chemdb_moltable.code%TYPE,
    psinonimo IN chemdb_moltable.sinonimo%TYPE,
    pmnote    IN chemdb_moltable.mnote%TYPE,
    pmolname  IN chemdb_moltable.molname%TYPE);
procedure registerprod
  (
    pprodcode in out compound.PRODTYPE%TYPE,
    pcorpid   in compound.CORPID%TYPE,
    ppactive  in compound.PACTIVE%TYPE,
    psaltcode in compound.SALTCODE%TYPE,
    pcmpdname compound.CMPDNAME%TYPE,
    pmolweight     in compound.MOLWEIGHT%TYPE,
    pmolformula    in compound.MOLFORMULA%TYPE,
    pprodtype      in compound.PRODTYPE%TYPE,
    ppatent        in compound.PATENT%TYPE,
    pCCOMMENT      in COMPOUND.CCOMMENT%TYPE,
    ppatexample    in compound.PATEXAMPLE%TYPE,
    pcas           in compound.CAS%TYPE,
    ponlinesearch  in compound.ONLINESEARCH%TYPE,
    pbiblio        in compound.BIBLIO%TYPE,
    pstereocomment in compound.STEREOCOMMENT%TYPE,
    pfontid        in compound.FONTID%TYPE,
    pscode         in compound.SCODE%TYPE,
    pextcorpid     in compound.ext_corpid%type := null);
procedure registerbatch
  (
    pbatchcode   in out batch.BATCHCODE%TYPE,
    pPRODCODE    in batch.PRODCODE%TYPE,
    pBCODE       in batch.BCODE%TYPE,
    pLABNOTEBOOK in batch.LABNOTEBOOK%TYPE,
    pAUTHOR batch.AUTHOR%TYPE,
    pBCOMMENT batch.BCOMMENT%TYPE,
    pSOK          in batch.SOK%TYPE,
    pPERCENTYIELD in batch.PERCENTYIELD%TYPE,
    pPHAR         in batch.PHAR%TYPE,
    pPLATE        in batch.PLATE%TYPE,
    pWELL         in batch.WELL%TYPE,
    pAMOUNT       in batch.AMOUNT%TYPE);
procedure registerinvio
  (
    pBARCODE    in out invio.BARCODE%TYPE,
    pBATCHCODE  in invio.BATCHCODE%TYPE,
    pDATA       in invio.DATA%TYPE,
    pRECEIVER   in invio.RECEIVER%TYPE,
    pNOTES      in invio.NOTES%TYPE,
    pQTINIZIALE in invio.QTINIZIALE%TYPE,
    pQTATTUALE  in invio.QTATTUALE%TYPE,
    pTARA       in invio.TARA%TYPE,
    pISEMPTY    in invio.ISEMPTY%TYPE,
    pplate      in invio.plate%TYPE := null,
    pwell       in invio.well%TYPE := null );
procedure
  delete(pk in varchar, entity in integer);
procedure registerstorage
  (
    pPRODUCT_ID in out storage.PRODUCT_ID%TYPE,
    pPACTIVE    in storage.PACTIVE%TYPE,
    pAMOUNT     in storage.AMOUNT%TYPE,
    pARMADIO    in storage.ARMADIO%TYPE,
    pPOSIZIONE  in storage.POSIZIONE%TYPE,
    pBOTTLE_NO  in storage.BOTTLE_NO%TYPE,
    pBOX        in storage.BOX%TYPE);
PROCEDURE registerprop
  (
    ppactive   IN calc_prop.pactive%TYPE,
    ppropname  IN calc_prop.propname%TYPE,
    ppropvalue IN calc_prop.propvalue%TYPE,
    isnew      in integer);
procedure registerlip
  (
    ppactive                 in LIPINSKY_DRUGABILITY.pactive%TYPE,
    pLIPINSKI_HBOND_ACCEPTOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_ACCEPTOR%TYPE,
    pLIPINSKI_HBOND_DONOR    in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_DONOR%TYPE,
    pALOGP98                 in LIPINSKY_DRUGABILITY.ALOGP98%TYPE,
    pADME_PSA_2D             in LIPINSKY_DRUGABILITY.ADME_PSA_2D%TYPE,
    pMOLWEIGHT               in LIPINSKY_DRUGABILITY.MOLWEIGHT%TYPE,
    pLIPINSKI_VIOLATIONS     in LIPINSKY_DRUGABILITY.LIPINSKI_VIOLATIONS%TYPE,
    pROTLBONDS               in LIPINSKY_DRUGABILITY.ROTLBONDS%TYPE,
    pADME_BBB_2D             in LIPINSKY_DRUGABILITY.ADME_BBB_2D%TYPE,
    pADME_SOLUBILITY         in LIPINSKY_DRUGABILITY.ADME_SOLUBILITY%TYPE,
    isnew                    in integer);
function registersalt(pscode in salts.scode%type, pSNAME in salts.SNAME%type, pmolweight in salts.molweight%type, pMOLFORMULA in salts.MOLFORMULA%type, pATTIVO in salts.ATTIVO%type) return integer;
end recBO ;
/

create or replace 
package body      recBO
AS
function prelievo
  (
    sbarcode in varchar2,
    qtp      in number,
    suser    in varchar2 default null,
    sdate    in date default null)
  return number
is
  muser varchar2(50);
  mdate date;
  res number;
begin
  if suser is null then
    muser  := user;
  else
    muser := suser;
  end if;
  if sdate is null then
    mdate  := sysdate;
  else
    mdate := sdate;
  end if;
  insert
  into prelievi
    (
      barcode,
      qt,
      data,
      userid
    )
    values
    (
      sbarcode,
      qtp,
      mdate,
      muser
    );
  update invio
  set QTATTUALE = QTATTUALE - qtp
  where barcode = sbarcode returning QTATTUALE
  into res;
  return res;
end;
procedure registermol
  (
    ppactive  IN OUT chemdb_moltable.pactive%TYPE,
    mole      IN CLOB,
    plabbbok  IN chemdb_moltable.labbook%TYPE,
    pprogram  IN chemdb_moltable.PROGRAM %TYPE,
    pcode     IN chemdb_moltable.code%TYPE,
    psinonimo in chemdb_moltable.SINONIMO%type,
    pmnote    in chemdb_moltable.MNOTE%type,
    pmolname  in chemdb_moltable.MOLNAME%type)
              is
begin
  if ppactive is null or ppactive < 1 then -- new record
    ppactive  := getPA;
    insert
    into chemdb_mols
      (
        ctab,
        pactive,
        labbook,
        program,
        code,
        sinonimo,
        mnote,
        molname
      )
      values
      (
        mol(mole),
        ppactive,
        plabbbok,
        pprogram,
        pcode,
        psinonimo,
        pmnote,
        pmolname
      );
  else
    update chemdb_mols
    set ctab     =mol(mole),
      labbook    =plabbbok,
      program    =pprogram,
      code       =pcode,
      sinonimo   =psinonimo,
      mnote      =pmnote,
      molname    =pmolname
    where pactive=ppactive ;
  end if;
end;
procedure registerprod
  (
    pprodcode in out compound.PRODTYPE%TYPE,
    pcorpid   in compound.CORPID%TYPE,
    ppactive  in compound.PACTIVE%TYPE,
    psaltcode in compound.SALTCODE%TYPE,
    pcmpdname compound.CMPDNAME%TYPE,
    pmolweight     in compound.MOLWEIGHT%TYPE,
    pmolformula    in compound.MOLFORMULA%TYPE,
    pprodtype      in compound.PRODTYPE%TYPE,
    ppatent        in compound.PATENT%TYPE,
    pCCOMMENT      in COMPOUND.CCOMMENT%TYPE ,
    ppatexample    in compound.PATEXAMPLE%TYPE,
    pcas           in compound.CAS%TYPE,
    ponlinesearch  in compound.ONLINESEARCH%TYPE,
    pbiblio        in compound.BIBLIO%TYPE,
    pstereocomment in compound.STEREOCOMMENT%TYPE,
    pfontid        in compound.FONTID%TYPE,
    pscode         in compound.SCODE%TYPE,
    pextcorpid     in compound.ext_corpid%type := null)
                   is
begin
  if pprodcode is null or pprodcode < 1 then -- new record
    pprodcode  := GetRec;
    insert
    into compound
      (
        prodcode,
        corpid,
        pactive,
        saltcode,
        cmpdname,
        molweight,
        molformula,
        prodtype,
        patent,
        CCOMMENT,
        patexample,
        cas,
        onlinesearch,
        biblio,
        stereocomment,
        fontid,
        scode,
        ext_corpid
      )
      values
      (
        pprodcode,
        pcorpid,
        ppactive,
        psaltcode,
        pcmpdname,
        pmolweight,
        pmolformula,
        pprodtype,
        ppatent,
        pCCOMMENT,
        ppatexample,
        pcas,
        ponlinesearch,
        pbiblio,
        pstereocomment,
        pfontid,
        pscode,
        pextcorpid
      );
  else
    update compound
    set corpid     =pcorpid,
      pactive      =ppactive,
      saltcode     =psaltcode,
      cmpdname     =pcmpdname,
      molweight    =pmolweight,
      molformula   =pmolformula,
      prodtype     =pprodtype,
      patent       =ppatent,
      CCOMMENT     =pCCOMMENT,
      patexample   =ppatexample,
      cas          =pcas,
      onlinesearch =ponlinesearch,
      biblio       =pbiblio,
      stereocomment=pstereocomment,
      fontid       =pfontid,
      scode        =pscode,
      ext_corpid   =pextcorpid
    where prodcode =pprodcode ;
  end if;
end;
procedure registerbatch
  (
    pbatchcode   in out batch.BATCHCODE%TYPE,
    pPRODCODE    in batch.PRODCODE%TYPE,
    pBCODE       in batch.BCODE%TYPE,
    pLABNOTEBOOK in batch.LABNOTEBOOK%TYPE,
    pAUTHOR batch.AUTHOR%TYPE,
    pBCOMMENT batch.BCOMMENT%TYPE,
    pSOK          in batch.SOK%TYPE,
    pPERCENTYIELD in batch.PERCENTYIELD%TYPE,
    pPHAR         in batch.PHAR%TYPE,
    pPLATE        in batch.PLATE%TYPE,
    pWELL         in batch.WELL%TYPE,
    pAMOUNT       in batch.AMOUNT%TYPE)
                  is
begin
  if pbatchcode is null or pbatchcode < 1 then -- new record
    insert
    into batch
      (
        PRODCODE,
        BCODE,
        LABNOTEBOOK,
        AUTHOR,
        BCOMMENT,
        SOK,
        PERCENTYIELD,
        PHAR,
        PLATE,
        WELL,
        AMOUNT
      )
      values
      (
        pPRODCODE,
        pBCODE,
        pLABNOTEBOOK,
        pAUTHOR,
        pBCOMMENT,
        pSOK,
        pPERCENTYIELD,
        pPHAR,
        pPLATE,
        pWELL,
        pAMOUNT
      )
    returning batchcode
    into pbatchcode;
  else
    update batch
    set PRODCODE   =pPRODCODE,
      BCODE        =pBCODE,
      LABNOTEBOOK  =pLABNOTEBOOK,
      AUTHOR       =pAUTHOR,
      BCOMMENT     =pBCOMMENT,
      SOK          =pSOK,
      PERCENTYIELD =pPERCENTYIELD,
      PHAR         =pPHAR,
      PLATE        =pPLATE,
      WELL         =pWELL,
      AMOUNT       =pAMOUNT
    where BATCHCODE=pBATCHCODE ;
  end if;
end;
procedure registerinvio
  (
    pBARCODE    in out invio.BARCODE%TYPE,
    pBATCHCODE  in invio.BATCHCODE%TYPE,
    pDATA       in invio.DATA%TYPE,
    pRECEIVER   in invio.RECEIVER%TYPE,
    pNOTES      in invio.NOTES%TYPE,
    pQTINIZIALE in invio.QTINIZIALE%TYPE,
    pQTATTUALE  in invio.QTATTUALE%TYPE,
    pTARA       in invio.TARA%TYPE,
    pISEMPTY    in invio.ISEMPTY%TYPE,
    pplate      in invio.plate%TYPE := null,
    pwell       in invio.well%TYPE := null )
                is
  tmpData date;
begin
  if pBARCODE is null then -- new record
    if pdata  is null then
      tmpData := sysdate;
    else
      tmpData := pDATA;
    end if;
    insert
    into invio
      (
        BATCHCODE,
        DATA,
        RECEIVER,
        NOTES,
        QTINIZIALE,
        QTATTUALE,
        TARA,
        ISEMPTY,
        plate,
        well
      )
      values
      (
        pBATCHCODE,
        tmpData,
        pRECEIVER,
        pNOTES,
        pQTINIZIALE,
        pQTATTUALE,
        pTARA,
        pISEMPTY,
        pplate,
        pwell
      )
    returning barcode
    into pBARCODE;
  else
    update invio
    set BATCHCODE=pBATCHCODE,
      /*DATA=pDATA, */
      RECEIVER   =pRECEIVER,
      NOTES      =pNOTES,
      QTINIZIALE =pQTINIZIALE,
      QTATTUALE  =pQTATTUALE,
      TARA       =pTARA,
      ISEMPTY    =pISEMPTY,
      plate      =pplate,
      well       =pwell
    where BARCODE=pBARCODE ;
  end if;
end;
procedure
  delete(pk in varchar, entity in integer) is begin if entity = 1 then
  delete from chemdb_mols where pactive = to_number(pk);
elsif entity = 2 then
  delete from compound where prodcode = to_number(pk);
elsif entity = 3 then
  delete from batch where batchcode = to_number(pk);
elsif entity = 4 then
  delete from invio where barcode = pk;
elsif entity = 5 then
  delete from storage where product_id = to_number(pk);
elsif entity = 7 then
  delete from LIPINSKY_DRUGABILITY where pactive = to_number(pk);
elsif entity <0 then
  delete from calc_prop where pactive = -entity and propname=pk;
end if;
end;
procedure registerstorage
  (
    pPRODUCT_ID in out storage.PRODUCT_ID%TYPE,
    pPACTIVE    in storage.PACTIVE%TYPE,
    pAMOUNT     in storage.AMOUNT%TYPE,
    pARMADIO    in storage.ARMADIO%TYPE,
    pPOSIZIONE  in storage.POSIZIONE%TYPE,
    pBOTTLE_NO  in storage.BOTTLE_NO%TYPE,
    pBOX        in storage.BOX%TYPE)
                is
begin
  if pPRODUCT_ID is null or pPRODUCT_ID < 1 then -- new record
    insert
    into storage
      (
        PACTIVE,
        AMOUNT,
        ARMADIO,
        POSIZIONE,
        BOTTLE_NO,
        BOX
      )
      values
      (
        pPACTIVE,
        pAMOUNT,
        pARMADIO,
        pPOSIZIONE,
        pBOTTLE_NO,
        pBOX
      )
    returning PRODUCT_ID
    into pPRODUCT_ID;
  else
    update storage
    set PACTIVE     =pPACTIVE,
      AMOUNT        =pAMOUNT,
      ARMADIO       =pARMADIO,
      POSIZIONE     =pPOSIZIONE,
      BOTTLE_NO     =pBOTTLE_NO,
      BOX           =pBOX
    where PRODUCT_ID=pPRODUCT_ID ;
  end if;
end;
PROCEDURE registerprop
  (
    ppactive   IN calc_prop.pactive%TYPE,
    ppropname  IN calc_prop.propname%TYPE,
    ppropvalue IN calc_prop.propvalue%TYPE,
    isnew      in integer)
               is
begin
  if isnew = 0 then --update
    update calc_prop
    set propvalue = ppropvalue
    where pactive = ppactive
    and propname  = ppropname;
  else --insert
    insert
    into calc_prop
      (
        pactive,
        propname,
        propvalue
      )
      values
      (
        ppactive,
        ppropname,
        ppropvalue
      );
  end if;
end;
procedure registerlip
  (
    ppactive                 in LIPINSKY_DRUGABILITY.pactive%TYPE,
    pLIPINSKI_HBOND_ACCEPTOR in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_ACCEPTOR%TYPE,
    pLIPINSKI_HBOND_DONOR    in LIPINSKY_DRUGABILITY.LIPINSKI_HBOND_DONOR%TYPE,
    pALOGP98                 in LIPINSKY_DRUGABILITY.ALOGP98%TYPE,
    pADME_PSA_2D             in LIPINSKY_DRUGABILITY.ADME_PSA_2D%TYPE,
    pMOLWEIGHT               in LIPINSKY_DRUGABILITY.MOLWEIGHT%TYPE,
    pLIPINSKI_VIOLATIONS     in LIPINSKY_DRUGABILITY.LIPINSKI_VIOLATIONS%TYPE,
    pROTLBONDS               in LIPINSKY_DRUGABILITY.ROTLBONDS%TYPE,
    pADME_BBB_2D             in LIPINSKY_DRUGABILITY.ADME_BBB_2D%TYPE,
    pADME_SOLUBILITY         in LIPINSKY_DRUGABILITY.ADME_SOLUBILITY%TYPE,
    isnew                    in integer
  )
is
begin
  if isnew = 0 then --update
    update LIPINSKY_DRUGABILITY
    set LIPINSKI_HBOND_ACCEPTOR = pLIPINSKI_HBOND_ACCEPTOR,
      LIPINSKI_HBOND_DONOR      = pLIPINSKI_HBOND_DONOR,
      ALOGP98                   = pALOGP98,
      ADME_PSA_2D               = pADME_PSA_2D,
      MOLWEIGHT                 = pMOLWEIGHT,
      LIPINSKI_VIOLATIONS       = pLIPINSKI_VIOLATIONS,
      ROTLBONDS                 = pROTLBONDS,
      ADME_BBB_2D               = pADME_BBB_2D,
      ADME_SOLUBILITY           = pADME_SOLUBILITY
    where pactive               = ppactive;
  else --insert
    insert
    into LIPINSKY_DRUGABILITY
      (
        pactive,
        LIPINSKI_HBOND_ACCEPTOR,
        LIPINSKI_HBOND_DONOR,
        ALOGP98,
        ADME_PSA_2D,
        MOLWEIGHT,
        LIPINSKI_VIOLATIONS,
        ROTLBONDS,
        ADME_BBB_2D,
        ADME_SOLUBILITY
      )
      values
      (
        ppactive,
        pLIPINSKI_HBOND_ACCEPTOR,
        pLIPINSKI_HBOND_DONOR,
        pALOGP98,
        pADME_PSA_2D,
        pMOLWEIGHT,
        pLIPINSKI_VIOLATIONS,
        pROTLBONDS,
        pADME_BBB_2D,
        pADME_SOLUBILITY
      );
  end if;
end;
  function registersalt(pscode in salts.scode%type, pSNAME in salts.SNAME%type, pmolweight in salts.molweight%type, pMOLFORMULA in salts.MOLFORMULA%type, pATTIVO in salts.ATTIVO%type) return integer AS
  cursor appo is select scode from salts where pscode=scode for update;
  zecode salts.scode%type;
  reto integer := 0;
  BEGIN
    open appo;
    fetch appo into zecode;
    if appo%found then
      update salts set
        SNAME = psname,
        MOLWEIGHT = pmolweight,
        MOLFORMULA = pmolformula,
        ATTIVO = pattivo
      where current of appo;
    else
      insert into salts (
        SCODE,
        SNAME,
        MOLWEIGHT,
        MOLFORMULA,
        ATTIVO 
      ) values (
        pscode,
        psname,
        pmolweight,
        pmolformula,
        pattivo
      );
      reto := 1;
    end if;
    close appo;
    return reto;
  END registersalt;
end recBO ;
/