188 lines
6.4 KiB
MySQL
188 lines
6.4 KiB
MySQL
--------------------------------------------------------
|
|
-- 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
|