354 lines
15 KiB
SQL
354 lines
15 KiB
SQL
----------------------------------------------------
|
|
-- Export file for user MARIUSA_CRM --
|
|
-- Created by paula.rota on 5/12/2006, 9:54:14 AM --
|
|
----------------------------------------------------
|
|
|
|
spool pack_preturi.log
|
|
|
|
prompt
|
|
prompt Creating package PACK_PRETURI
|
|
prompt =============================
|
|
prompt
|
|
create or replace package PACK_PRETURI is
|
|
|
|
-- Author : PAULA.MUTU
|
|
-- Created : 5/12/2006 9:30:00 AM
|
|
-- Purpose :
|
|
|
|
procedure adauga_articol(V_SCHEMA IN VARCHAR2,
|
|
V_CODMAT IN VARCHAR2,
|
|
V_DENUMIRE IN VARCHAR2,
|
|
V_UM IN VARCHAR2,
|
|
V_ID_CATEG IN NUMBER,
|
|
V_INACTIV IN NUMBER,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure modifica_articol(V_SCHEMA IN VARCHAR2,
|
|
V_ID_ARTICOL IN NUMBER,
|
|
V_CODMAT IN VARCHAR2,
|
|
V_DENUMIRE IN VARCHAR2,
|
|
V_UM IN VARCHAR2,
|
|
V_ID_CATEG IN NUMBER,
|
|
V_INACTIV IN NUMBER,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure sterge_articol(V_SCHEMA IN VARCHAR2,
|
|
V_ID_ARTICOL IN NUMBER,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure adauga_politica(V_SCHEMA IN VARCHAR2,
|
|
V_NUME IN VARCHAR2,
|
|
V_DATAI IN DATE,
|
|
V_DATAS IN DATE,
|
|
V_ID_VALUTA IN NUMBER,
|
|
V_SIR IN VARCHAR2,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure modifica_politica(V_SCHEMA IN VARCHAR2,
|
|
V_ID_POL IN NUMBER,
|
|
V_NUME IN VARCHAR2,
|
|
V_DATAI IN DATE,
|
|
V_DATAS IN DATE,
|
|
V_ID_VALUTA IN NUMBER,
|
|
V_SIR IN VARCHAR2,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure sterge_politica(V_SCHEMA IN VARCHAR2,
|
|
V_ID_POL IN NUMBER,
|
|
V_ID_UTIL IN NUMBER);
|
|
|
|
procedure adauga_nota_vanzari(tcSchema varchar2,
|
|
tcDenumire varchar2,
|
|
tcScd varchar2,
|
|
tcAscd varchar2,
|
|
tcScc varchar2,
|
|
tcAscc varchar2,
|
|
tnId_util number);
|
|
|
|
procedure modifica_nota_vanzari(tcSchema varchar2,
|
|
tnId_nota number,
|
|
tcDenumire varchar2,
|
|
tcScd varchar2,
|
|
tcAscd varchar2,
|
|
tcScc varchar2,
|
|
tcAscc varchar2,
|
|
tnId_util number);
|
|
|
|
procedure sterge_nota_vanzari(tcSchema varchar2,
|
|
tnId_nota number,
|
|
tnId_utils number);
|
|
|
|
procedure adauga_categ_art(tcSchema varchar2,
|
|
tcDenumire varchar2,
|
|
tnId_nota number,
|
|
tnId_tata number,
|
|
tnId_util number);
|
|
|
|
procedure modifica_categ_art(tcSchema varchar2,
|
|
tnId_categ number,
|
|
tcDenumire varchar2,
|
|
tnId_nota number,
|
|
tnId_tata number,
|
|
tnId_util number);
|
|
|
|
procedure sterge_categ_art(tcSchema varchar2,
|
|
tnId_categ number,
|
|
tnId_utils number);
|
|
|
|
end PACK_PRETURI;
|
|
/
|
|
|
|
prompt
|
|
prompt Creating package body PACK_PRETURI
|
|
prompt ==================================
|
|
prompt
|
|
create or replace package body PACK_PRETURI is
|
|
|
|
procedure adauga_articol(V_SCHEMA IN VARCHAR2,
|
|
V_CODMAT IN VARCHAR2,
|
|
V_DENUMIRE IN VARCHAR2,
|
|
V_UM IN VARCHAR2,
|
|
V_ID_CATEG IN NUMBER,
|
|
V_INACTIV IN NUMBER,
|
|
V_ID_UTIL IN NUMBER) is
|
|
V_INREGISTRARI NUMBER(10);
|
|
begin
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || V_SCHEMA ||
|
|
'.NOM_ARTICOLE ' ||
|
|
'WHERE STERS=:1 AND CODMAT=:2 AND IN_CRM=:3 AND IN_STOC=:4'
|
|
INTO V_INREGISTRARI
|
|
USING 0, V_CODMAT, 1, 0;
|
|
|
|
IF V_INREGISTRARI > 0 THEN
|
|
RAISE_APPLICATION_ERROR(-20000,
|
|
'Mai exista un articol cu acest cod de material!');
|
|
ELSE
|
|
EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
|
|
'.NOM_ARTICOLE (CODMAT,DENUMIRE,UM,ID_CATEG,INACTIV,IN_CRM,IN_STOC) ' ||
|
|
'VALUES (:1,:2,:3,:4,:5,:6,:7)'
|
|
USING V_CODMAT, V_DENUMIRE, V_UM, V_ID_CATEG, V_INACTIV, 1, 0;
|
|
END IF;
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure modifica_articol(V_SCHEMA IN VARCHAR2,
|
|
V_ID_ARTICOL IN NUMBER,
|
|
V_CODMAT IN VARCHAR2,
|
|
V_DENUMIRE IN VARCHAR2,
|
|
V_UM IN VARCHAR2,
|
|
V_ID_CATEG IN NUMBER,
|
|
V_INACTIV IN NUMBER,
|
|
V_ID_UTIL IN NUMBER) is
|
|
V_INREGISTRARI NUMBER(10);
|
|
begin
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || V_SCHEMA ||
|
|
'.NOM_ARTICOLE ' ||
|
|
'WHERE STERS=:1 AND CODMAT=:2 AND IN_CRM=:3 AND IN_STOC=:4 AND ID_ARTICOL<>:5'
|
|
INTO V_INREGISTRARI
|
|
USING 0, V_CODMAT, 1, 0, V_ID_ARTICOL;
|
|
|
|
IF V_INREGISTRARI > 0 THEN
|
|
RAISE_APPLICATION_ERROR(-20000,
|
|
'Mai exista un articol cu acest cod de material!');
|
|
ELSE
|
|
EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA ||
|
|
'.NOM_ARTICOLE SET CODMAT=:1,' ||
|
|
'DENUMIRE=:2,UM=:3,
|
|
ID_CATEG=:4,INACTIV=:5 WHERE ID_ARTICOL=:6'
|
|
USING V_CODMAT, V_DENUMIRE, V_UM, V_ID_CATEG, V_INACTIV, V_ID_ARTICOL;
|
|
END IF;
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure sterge_articol(V_SCHEMA IN VARCHAR2,
|
|
V_ID_ARTICOL IN NUMBER,
|
|
V_ID_UTIL IN NUMBER) is
|
|
begin
|
|
EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA ||
|
|
'.NOM_ARTICOLE SET STERS=:1 WHERE ID_ARTICOL=:2'
|
|
USING 1, V_ID_ARTICOL;
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure adauga_politica(V_SCHEMA IN VARCHAR2,
|
|
V_NUME IN VARCHAR2,
|
|
V_DATAI IN DATE,
|
|
V_DATAS IN DATE,
|
|
V_ID_VALUTA IN NUMBER,
|
|
V_SIR IN VARCHAR2,
|
|
V_ID_UTIL IN NUMBER) is
|
|
V_ID_POL NUMBER(10);
|
|
V_LUNGIME_SIR NUMBER(10);
|
|
V_ELEMENT VARCHAR2(100);
|
|
V_ID_ARTICOL NUMBER(10);
|
|
V_UM VARCHAR2(10);
|
|
V_PRET NUMBER(10);
|
|
begin
|
|
EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
|
|
'.CRM_POLITICI_PRETURI ' ||
|
|
'(NUME,DATAI,DATAS,ID_VALUTA,ID_UTIL) VALUES(:1,:2,:3,:4,:5) ' ||
|
|
'RETURNING ID_POL INTO :6'
|
|
USING V_NUME, V_DATAI, V_DATAS, V_ID_VALUTA, V_ID_UTIL
|
|
RETURNING INTO V_ID_POL;
|
|
|
|
IF V_SIR IS NOT NULL THEN
|
|
V_LUNGIME_SIR := SIR_IN_SIR(V_SIR, ';');
|
|
|
|
FOR i IN 1 .. V_LUNGIME_SIR LOOP
|
|
V_ELEMENT := ELEMENT_DIN_SIR(V_SIR, ';', i) || ',';
|
|
V_ID_ARTICOL := TO_NUMBER(ELEMENT_DIN_SIR(V_ELEMENT, ',', 1));
|
|
V_UM := ELEMENT_DIN_SIR(V_ELEMENT, ',', 2);
|
|
V_PRET := TO_NUMBER(TRANSLATE(ELEMENT_DIN_SIR(V_ELEMENT,
|
|
',',
|
|
3),
|
|
',',
|
|
'.'));
|
|
|
|
EXECUTE IMMEDIATE 'INSERT INTO ' || V_SCHEMA ||
|
|
'.CRM_POLITICI_PRET_ART (ID_POL,ID_ART,PRET,UM,ID_VALUTA,ID_UTIL) ' ||
|
|
'VALUES(:1,:2,:3,:4,:5,:6)'
|
|
USING V_ID_POL, V_ID_ARTICOL, V_PRET, V_UM, V_ID_VALUTA, V_ID_UTIL;
|
|
END LOOP;
|
|
|
|
END IF;
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure modifica_politica(V_SCHEMA IN VARCHAR2,
|
|
V_ID_POL IN NUMBER,
|
|
V_NUME IN VARCHAR2,
|
|
V_DATAI IN DATE,
|
|
V_DATAS IN DATE,
|
|
V_ID_VALUTA IN NUMBER,
|
|
V_SIR IN VARCHAR2,
|
|
V_ID_UTIL IN NUMBER) is
|
|
begin
|
|
pack_oferta.sterge_politica(V_SCHEMA, V_ID_POL, V_ID_UTIL);
|
|
pack_oferta.adauga_politica(V_SCHEMA,
|
|
V_NUME,
|
|
V_DATAI,
|
|
V_DATAS,
|
|
V_ID_VALUTA,
|
|
V_SIR,
|
|
V_ID_UTIL);
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure sterge_politica(V_SCHEMA IN VARCHAR2,
|
|
V_ID_POL IN NUMBER,
|
|
V_ID_UTIL IN NUMBER) is
|
|
begin
|
|
EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_POLITICI_PRETURI ' ||
|
|
'SET STERS=:1,DATAORAS=:2,ID_UTILS=:3 WHERE ID_POL=:4'
|
|
USING 1, SYSDATE, V_ID_UTIL, V_ID_POL;
|
|
|
|
EXECUTE IMMEDIATE 'UPDATE ' || V_SCHEMA || '.CRM_POLITICI_PRET_ART ' ||
|
|
'SET STERS=:1,DATAORAS=:2,ID_UTILS=:3 WHERE ID_POL=:4'
|
|
USING 1, SYSDATE, V_ID_UTIL, V_ID_POL;
|
|
end;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure adauga_nota_vanzari(tcSchema varchar2,
|
|
tcDenumire varchar2,
|
|
tcScd varchar2,
|
|
tcAscd varchar2,
|
|
tcScc varchar2,
|
|
tcAscc varchar2,
|
|
tnId_util number) is
|
|
|
|
begin
|
|
execute immediate 'insert into ' || tcSchema ||
|
|
'.crm_note_vanzari
|
|
(denumire, scd, ascd, scc, ascc, id_util, dataora)
|
|
values (:1, :2, :3, :4, :5, :6, :7)'
|
|
using tcDenumire, tcScd, tcAscd, tcScc, tcAscc, tnId_util, SYSDATE;
|
|
end adauga_nota_vanzari;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure modifica_nota_vanzari(tcSchema varchar2,
|
|
tnId_nota number,
|
|
tcDenumire varchar2,
|
|
tcScd varchar2,
|
|
tcAscd varchar2,
|
|
tcScc varchar2,
|
|
tcAscc varchar2,
|
|
tnId_util number) is
|
|
begin
|
|
execute immediate 'update ' || tcSchema || '.crm_note_vanzari
|
|
set denumire=:1, scd=:2, ascd=:3, scc=:4, ascc=:5, id_util=:6, dataora=:7
|
|
where id_nota=:8'
|
|
using tcDenumire, tcScd, tcAscd, tcScc, tcAscc, tnId_util, SYSDATE, tnId_nota;
|
|
|
|
end modifica_nota_vanzari;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure sterge_nota_vanzari(tcSchema varchar2,
|
|
tnId_nota number,
|
|
tnId_utils number) is
|
|
begin
|
|
execute immediate 'update ' || tcSchema || '.crm_note_vanzari
|
|
set sters=:1, id_utils=:2, dataoras=:3
|
|
where id_nota=:4'
|
|
using 1, tnId_utils, SYSDATE, tnId_nota;
|
|
end sterge_nota_vanzari;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure adauga_categ_art(tcSchema varchar2,
|
|
tcDenumire varchar2,
|
|
tnId_nota number,
|
|
tnId_tata number,
|
|
tnId_util number) is
|
|
V_INREGISTRARI NUMBER(10);
|
|
begin
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || TCSCHEMA ||
|
|
'.CRM_CATEG_ARTICOLE ' ||
|
|
'WHERE STERS=:1 AND DENUMIRE=:2'
|
|
INTO V_INREGISTRARI
|
|
USING 0, TCDENUMIRE;
|
|
|
|
IF V_INREGISTRARI > 0 THEN
|
|
RAISE_APPLICATION_ERROR(-20000,
|
|
'Mai exista o categorie cu aceeasi denumire!');
|
|
ELSE
|
|
execute immediate 'insert into ' || tcSchema ||
|
|
'.crm_categ_articole
|
|
(denumire, id_nota, id_tata, id_util, dataora)
|
|
values (:1, :2, :3, :4, :5)'
|
|
using tcDenumire, tnId_nota, tnId_tata, tnId_util, SYSDATE;
|
|
END IF;
|
|
end adauga_categ_art;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure modifica_categ_art(tcSchema varchar2,
|
|
tnId_categ number,
|
|
tcDenumire varchar2,
|
|
tnId_nota number,
|
|
tnId_tata number,
|
|
tnId_util number) is
|
|
V_INREGISTRARI NUMBER(10);
|
|
begin
|
|
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || TCSCHEMA ||
|
|
'.CRM_CATEG_ARTICOLE ' ||
|
|
'WHERE STERS=:1 AND DENUMIRE=:2 AND ID_CATEG<>:3'
|
|
INTO V_INREGISTRARI
|
|
USING 0, TCDENUMIRE, tnId_categ;
|
|
|
|
IF V_INREGISTRARI > 0 THEN
|
|
RAISE_APPLICATION_ERROR(-20000,
|
|
'Mai exista o categorie cu aceeasi denumire!');
|
|
ELSE
|
|
execute immediate 'update ' || tcSchema || '.crm_categ_articole ' ||
|
|
'set denumire=:1, id_nota=:2, id_tata=:3,
|
|
id_util=:4, dataora=:5 ' ||
|
|
'where id_categ=:6'
|
|
using tcDenumire, tnId_nota, tnId_tata, tnId_util, SYSDATE, tnId_categ;
|
|
END IF;
|
|
end modifica_categ_art;
|
|
-------------------------------------------------------------------------------------------
|
|
procedure sterge_categ_art(tcSchema varchar2,
|
|
tnId_categ number,
|
|
tnId_utils number) is
|
|
begin
|
|
execute immediate 'update ' || tcSchema || '.crm_categ_articole
|
|
set sters=:1, id_utils=:2, dataoras=:3
|
|
where id_categ=:4'
|
|
using 1, tnId_utils, SYSDATE, tnId_categ;
|
|
end sterge_categ_art;
|
|
-------------------------------------------------------------------------------------------
|
|
|
|
end PACK_PRETURI;
|
|
/
|
|
|
|
|
|
spool off
|