--------------------------------------------------------
-- Proceduri si functii pentru ROAAUTO v 1.4.151      --
-- Created by marius.atanasiu on 2/2/2006, 9:52:31 AM --
--------------------------------------------------------

spool procfunc.log

prompt
prompt Creating function SIR_IN_SIR
prompt ============================
prompt
create or replace function sir_in_sir(sir1 in varchar2, sir2 in varchar2)
  return integer is
  vret    integer := 0;
  v_count integer := 0;
begin
  case
    when instr(sir1, sir2, 1, 1) = 0 then
      vret := 0;
    when instr(sir1, sir2, 1, 1) <> 0 and
         instr(sir1, sir2, 1, 1) = instr(sir1, sir2, -1, 1) then
      vret := 1;
    else
      for i in 1 .. length(sir1) loop
        if substr(sir1, i, length(sir2)) = sir2 then
          v_count := v_count + 1;
        end if;
      end loop;
      vret := v_count;
  end case;
  return vret;
end;
/

prompt
prompt Creating function ELEMENT_DIN_SIR
prompt =================================
prompt
create or replace function ELEMENT_DIN_SIR(SIR1 IN VARCHAR2,
                                           SIR2 IN VARCHAR2,
                                           OCC  IN INTEGER) return VARCHAR2 is

  VRET VARCHAR2(1000) := '';
begin
  if sir_in_sir(sir1, sir2) > 0 then
    vret := NVL(REPLACE(substr(SIR1,(CASE WHEN OCC > 1 THEN instr(sir1, sir2, 1, OCC - 1) ELSE 0 END) + 1, instr(sir1, sir2, 1, OCC) - (CASE WHEN OCC > 1 THEN instr(sir1, sir2, 1, OCC - 1) ELSE 0 END)), SIR2, ''), '');
  end if;
  return vret;
end;
/

prompt
prompt Creating function GETDEV_COMANDA
prompt ================================
prompt
create or replace function getdev_comanda
return number IS
lnReturn NUMBER(20) := 0;
    begin

      select seq_dev_comanda.NEXTVAL into lnReturn from dual;
      return lnReturn;
end getdev_comanda;
/

prompt
prompt Creating function ID_DIN_SIR
prompt ============================
prompt
create or replace function ID_DIN_SIR(SIR1 IN VARCHAR2,
                                      SIR2 IN VARCHAR2,
                                      OCC  IN INTEGER) return NUMBER is

  VRET NUMBER(20) := 0;
begin
  if sir_in_sir(sir1, sir2) > 0 then
    vret := TO_NUMBER(NVL(REPLACE(substr(SIR1,
                                 (CASE WHEN OCC>1 THEN instr(sir1, sir2, 1, OCC-1) ELSE 0 END) + 1,
                                 instr(sir1, sir2, 1, OCC) -
                                 (CASE WHEN OCC>1 THEN instr(sir1, sir2, 1, OCC-1) ELSE 0 END)),SIR2,''),
                          0));
  end if;
  return vret;
end;
/

prompt
prompt Creating procedure DEVINVALCOMPROC
prompt ==================================
prompt
CREATE OR REPLACE PROCEDURE "DEVINVALCOMPROC"(V_SCHEMA   IN VARCHAR2,
                                              V_ID_ORDL  IN NUMBER,
                                              V_ID_UTILS IN NUMBER) AS
BEGIN
  EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.DEV_ORDL ' ||
                    'SET VALIDAT=0,TVAM=0,TVAN=0,TVA=0,' ||
                    'MANOPERA=0,MATERIALE=0,VALOARE=0,' ||
                    'ID_UTIL_VALID=0,DATAORAVALID=NULL,ID_UTILS=:1,' ||
                    'DATAORAS=SYSDATE WHERE ID_ORDL=:2'
    USING V_ID_UTILS, V_ID_ORDL;
END;
/

prompt
prompt Creating procedure DEVPARTINSPROC
prompt =================================
prompt
CREATE OR REPLACE PROCEDURE DEVPARTINSPROC(V_SCHEMA     IN VARCHAR2,
                                           V_NUME       IN VARCHAR2,
                                           V_COD_FISCAL IN VARCHAR2,
                                           V_TELEFON    IN VARCHAR2,
                                           V_ADRESA     IN VARCHAR2,
                                           V_BANCA      IN VARCHAR2,
                                           V_CONT_BANCA IN VARCHAR2,
                                           V_REG_COMERT IN VARCHAR2,
                                           V_ID_LOC     IN NUMBER) IS
  V_ID_PART     NUMBER(20) := 0;
  V_ID_TIP_PART NUMBER(20) := 0;
  V_CONT        VARCHAR2(4) := '4111';
BEGIN
  EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || V_SCHEMA || '.NOM_PARTENERI' ||
                    '(NUME,COD_FISCAL,TELEFON,ADRESA,BANCA,CONT_BANCA,REG_COMERT,ID_LOC) ' ||
                    'VALUES(:1,:2,:3,:4,:5,:6,:7,:8) RETURNING ID_PART INTO :9'
    USING V_NUME, V_COD_FISCAL, V_TELEFON, V_ADRESA, V_BANCA, V_CONT_BANCA, V_REG_COMERT, V_ID_LOC
    RETURNING INTO V_ID_PART;

  EXECUTE IMMEDIATE 'SELECT ID_TIP_PART FROM ' || V_SCHEMA ||
                    '.VCORESP_TIP_CONT ' || 'WHERE CONT=:1'
    INTO V_ID_TIP_PART
    USING V_CONT;

  EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || V_SCHEMA ||
                    '.CORESP_TIP_PART(ID_TIP_PART,ID_PART) ' ||
                    'VALUES(:1,:2)'
    USING V_ID_TIP_PART, V_ID_PART;
END;
/

prompt
prompt Creating procedure DEVVALCOMPROC
prompt ================================
prompt
CREATE OR REPLACE PROCEDURE "DEVVALCOMPROC"(V_SCHEMA     IN VARCHAR2,
                                            V_ID_ORDL    IN NUMBER,
                                            V_ID_LUCRARE IN NUMBER,
                                            V_ID_UTIL    IN NUMBER) as
  V_CTVA      NUMBER(5, 2) := 1.19;
  V_TVAM      NUMBER(17, 4) := 0;
  V_TVAN      NUMBER(17, 4) := 0;
  V_TVA       NUMBER(17, 4) := 0;
  V_MANOPERA  NUMBER(17, 4) := 0;
  V_MATERIALE NUMBER(17, 4) := 0;
  V_VALOARE   NUMBER(17, 4) := 0;
BEGIN
  EXECUTE IMMEDIATE 'SELECT SUM(PRET*TIMPN) AS MANOPERA FROM ' || V_SCHEMA ||
                    '.DEV_OPER WHERE ID_ORDL=:1'
    INTO V_MANOPERA
    USING V_ID_ORDL;

  IF V_MANOPERA IS NULL THEN
     V_MANOPERA:=0;
  END IF;

  EXECUTE IMMEDIATE 'SELECT SUM(PRETV*CANTE) AS MATERIALE FROM ' ||
                    V_SCHEMA || '.RUL WHERE ID_LUCRARE=:1'
    INTO V_MATERIALE
    USING V_ID_LUCRARE;

  IF V_MATERIALE IS NULL THEN
     V_MATERIALE:=0;
  END IF;

  V_TVAM    := V_MATERIALE * (V_CTVA - 1);
  V_TVAN    := V_MANOPERA * (V_CTVA - 1);
  V_TVA     := V_TVAM + V_TVAN;
  V_VALOARE := V_TVA + V_MANOPERA + V_MATERIALE;

  EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.DEV_ORDL SET VALIDAT=1,' ||
                    'TVAM=:1,TVAN=:2,TVA=:3,MANOPERA=:4,MATERIALE=:5,' ||
                    'VALOARE=:6,ID_UTIL_VALID=:7,DATAORAVALID=SYSDATE ' ||
                    'WHERE ID_ORDL=:8'
    USING V_TVAM, V_TVAN, V_TVA, V_MANOPERA, V_MATERIALE, V_VALOARE, V_ID_UTIL, V_ID_ORDL;
END;
/


spool off