create or replace 
PACKAGE REAGENTIIO AS 

  /* TODO enter package declarations (types, exceptions, methods etc) here 
  CDBREGNO
  CTAB
  MOLWEIGHT
  REG_DATE
  MOLID
  PRODUCTNAME
  STORAGE
  GIACENZA
  EXTREG_MODDATE
  MOLFORMULA
  CHIME7
  MDLNUMBER
  IUPACNAME
  CASNAME
  CASNUMBER
  */ 
  function RegisterMol (
    pCDBREGNO in reagenti_moltable.CDBREGNO%type,
    pCTAB in clob,
    pMOLID in reagenti_moltable.MOLID%type,
    pPRODUCTNAME in reagenti_moltable.PRODUCTNAME%type,
    pSTORAGE in reagenti_moltable.STORAGE%type,
    pGIACENZA in reagenti_moltable.GIACENZA%type,
    pEXTREG_MODDATE in reagenti_moltable.EXTREG_MODDATE%type,
    pMDLNUMBER in reagenti_moltable.MDLNUMBER%type,
    pIUPACNAME in reagenti_moltable.IUPACNAME%type,
    pCASNAME in reagenti_moltable.CASNAME%type,
    pCASNUMBER in reagenti_moltable.CASNUMBER%type
  ) return reagenti_moltable.CDBREGNO%type;

/*
BID	NUMBER(9,0)
MOLID	NUMBER(9,0)
LOTCODE	VARCHAR2(50 BYTE)
AMOUNT	NUMBER
ARMADIO	VARCHAR2(20 BYTE)
POSIZIONE	VARCHAR2(50 BYTE)
BOTTLE_NUMBER	VARCHAR2(20 BYTE)
RIPIANO	NUMBER
ARMADIO_NEW	VARCHAR2(20 BYTE)
LABEL	VARCHAR2(50 BYTE)
REG_DATE	DATE
EXPIRY_DATE	DATE
SITE_LOCATION	VARCHAR2(20 BYTE)
UNITA	VARCHAR2(10 BYTE)
FORNITORE	VARCHAR2(50 BYTE)
NUMERO_FLACONI	NUMBER(5,0)
*/
  function RegisterBottle (
    pBID in bottles.BID%type,
    pMOLID in bottles.MOLID%type,
    pLOTCODE in bottles.LOTCODE%type,
    pAMOUNT in bottles.AMOUNT%type,
    pARMADIO in bottles.ARMADIO%type,
    pPOSIZIONE in bottles.POSIZIONE%type,
    pBOTTLE_NUMBER in bottles.BOTTLE_NUMBER%type,
    pRIPIANO in bottles.RIPIANO%type,
    pARMADIO_NEW in bottles.ARMADIO_NEW%type,
    pLABEL in bottles.LABEL%type,
    pREG_DATE in bottles.REG_DATE%type,
    pEXPIRY_DATE in bottles.EXPIRY_DATE%type,
    pSITE_LOCATION in bottles.SITE_LOCATION%type,
    pUNITA in bottles.UNITA%type,
    pFORNITORE in bottles.FORNITORE%type,
    pNUMERO_FLACONI in bottles.NUMERO_FLACONI%type
  ) return bottles.BID%type;
  
  procedure DeleteEntity (
    pKey in number,
    pEntity in varchar2
  );
  
  function GetNid (
    pproject in notebooks.PROJECTNAME%type
  ) return notebooks.NID%type;

END REAGENTIIO;
/

create or replace 
PACKAGE BODY REAGENTIIO AS

  function RegisterMol (
    pCDBREGNO in reagenti_moltable.CDBREGNO%type,
    pCTAB in clob,
    pMOLID in reagenti_moltable.MOLID%type,
    pPRODUCTNAME in reagenti_moltable.PRODUCTNAME%type,
    pSTORAGE in reagenti_moltable.STORAGE%type,
    pGIACENZA in reagenti_moltable.GIACENZA%type,
    pEXTREG_MODDATE in reagenti_moltable.EXTREG_MODDATE%type,
    pMDLNUMBER in reagenti_moltable.MDLNUMBER%type,
    pIUPACNAME in reagenti_moltable.IUPACNAME%type,
    pCASNAME in reagenti_moltable.CASNAME%type,
    pCASNUMBER in reagenti_moltable.CASNUMBER%type
  ) return reagenti_moltable.CDBREGNO%type AS
    retval reagenti_moltable.CDBREGNO%type := -1;
  BEGIN
    if pCDBREGNO is null or pCDBREGNO < 1 then
      insert into reagenti_moltable (
        CTAB,
        PRODUCTNAME,
        STORAGE,
        GIACENZA,
        EXTREG_MODDATE,
        MDLNUMBER,
        IUPACNAME,
        CASNAME,
        CASNUMBER
      ) values (
        mol(pCTAB),
        pPRODUCTNAME,
        pSTORAGE,
        pGIACENZA,
        pEXTREG_MODDATE,
        pMDLNUMBER,
        pIUPACNAME,
        pCASNAME,
        pCASNUMBER
      ) returning cdbregno into retval;
    else
      retval := pCDBREGNO;
      update reagenti_moltable set
        CTAB = mol(pctab),
        PRODUCTNAME = pPRODUCTNAME,
        STORAGE = pstorage,
        GIACENZA = pGIACENZA,
        EXTREG_MODDATE = pEXTREG_MODDATE,
        MDLNUMBER = pMDLNUMBER,
        IUPACNAME = pIUPACNAME,
        CASNAME = pCASNAME,
        CASNUMBER = pCASNUMBER
      where CDBREGNO = pCDBREGNO;
    end if;
    RETURN retval;
  END RegisterMol;

  function RegisterBottle (
    pBID in bottles.BID%type,
    pMOLID in bottles.MOLID%type,
    pLOTCODE in bottles.LOTCODE%type,
    pAMOUNT in bottles.AMOUNT%type,
    pARMADIO in bottles.ARMADIO%type,
    pPOSIZIONE in bottles.POSIZIONE%type,
    pBOTTLE_NUMBER in bottles.BOTTLE_NUMBER%type,
    pRIPIANO in bottles.RIPIANO%type,
    pARMADIO_NEW in bottles.ARMADIO_NEW%type,
    pLABEL in bottles.LABEL%type,
    pREG_DATE in bottles.REG_DATE%type,
    pEXPIRY_DATE in bottles.EXPIRY_DATE%type,
    pSITE_LOCATION in bottles.SITE_LOCATION%type,
    pUNITA in bottles.UNITA%type,
    pFORNITORE in bottles.FORNITORE%type,
    pNUMERO_FLACONI in bottles.NUMERO_FLACONI%type
  ) return bottles.BID%type as
    retval bottles.BID%type := -1;
  begin
    if pBID is null or pBID < 1 then
      insert into bottles (
        MOLID,
        LOTCODE,
        AMOUNT,
        ARMADIO,
        POSIZIONE,
        BOTTLE_NUMBER,
        RIPIANO,
        ARMADIO_NEW,
        LABEL,
        REG_DATE,
        EXPIRY_DATE,
        SITE_LOCATION,
        UNITA,
        FORNITORE,
        NUMERO_FLACONI
      ) values (
        pMOLID,
        pLOTCODE,
        pAMOUNT,
        pARMADIO,
        pPOSIZIONE,
        pBOTTLE_NUMBER,
        pRIPIANO,
        pARMADIO_NEW,
        pLABEL,
        pREG_DATE,
        pEXPIRY_DATE,
        pSITE_LOCATION,
        pUNITA,
        pFORNITORE,
        pNUMERO_FLACONI
      ) returning bid into retval;
    else
      retval := pbid;
      update bottles set
        MOLID = pMOLID,
        LOTCODE = pLOTCODE,
        AMOUNT = pAMOUNT,
        ARMADIO = pARMADIO,
        POSIZIONE = pPOSIZIONE,
        BOTTLE_NUMBER = pBOTTLE_NUMBER,
        RIPIANO = pRIPIANO,
        ARMADIO_NEW = pARMADIO_NEW,
        LABEL = pLABEL,
        REG_DATE = pREG_DATE,
        EXPIRY_DATE = pEXPIRY_DATE,
        SITE_LOCATION = pSITE_LOCATION,
        UNITA = pUNITA,
        FORNITORE = pFORNITORE,
        NUMERO_FLACONI = pNUMERO_FLACONI
      where bid = pbid;
    end if;
    return retval;
  end RegisterBottle;

  procedure DeleteEntity (
    pKey in number,
    pEntity in varchar2
  ) as 
  begin
    if pEntity = 'MOLTABLE' then
      delete from reagenti_moltable where cdbregno=pKey;
    elsif pEntity = 'BOTTLES' then
      delete from BOTTLES where bid=pKey;
    end if;
  end;
  
  function GetNid (
    pproject in notebooks.PROJECTNAME%type
  ) return notebooks.NID%type as
  cursor noto is select max(nid) from notebooks;
  reto notebooks.NID%type;
  begin
    open noto;
    fetch noto into reto;
    close noto;
    if reto is null then
      reto := 1;
    else
      reto := reto + 1;
    end if;
    insert into notebooks (nid, projectname) values (reto, pproject);
    return reto;
  end;
  
END REAGENTIIO;
/