1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
|
CREATE PROCEDURE DOSSIER_INSERT (
DS_DT DATE,
DS_TM TIME,
OPER VARCHAR(10),
DS_CRSPD VARCHAR(30),
CLIENT VARCHAR(50),
DS_AR VARCHAR(20),
MARQUE VARCHAR(15),
DS_SN VARCHAR(15),
CATPB VARCHAR(50),
ETAT CHAR(2),
DS_DETAIL BLOB SUB_TYPE 1 SEGMENT SIZE 80,
DS_FACT_NUM VARCHAR(15),
DS_FACT_DT DATE,
DS_WTLBR SMALLINT,
DS_WTLBR_DEB DATE,
DS_WTLBR_FIN DATE,
DS_WTPCE SMALLINT,
DS_WTPCE_DEB DATE,
DS_WTPCE_FIN DATE,
DS_CAR_BLK SMALLINT,
DS_CAR_COL SMALLINT,
DS_TONNER SMALLINT,
DS_ALIM SMALLINT)
RETURNS (
NUM INTEGER)
AS
DECLARE VARIABLE MQ_NO SMALLINT;
DECLARE VARIABLE CPB_NO SMALLINT;
DECLARE VARIABLE ETA_NO SMALLINT;
DECLARE VARIABLE US_NO SMALLINT;
DECLARE VARIABLE US_INT CHAR(2);
DECLARE VARIABLE CL_NO INTEGER;
DECLARE VARIABLE DS_NUM VARCHAR(8);
BEGIN
NUM = GEN_ID(GEN_DOSSIER_NO, 1);
Select MQ_NO from TMARQUE where MQ_ID = :MARQUE into MQ_NO;
Select CPB_NO from tcatprob where CPB_DSC = :catpb into CPB_NO;
Select ETA_NO from TETATDS where ETA_ID = :ETAT into ETA_NO;
Select US_NO,US_INT from TUSER where US_ID = :OPER into US_NO,US_INT;
Select CL_NO from TCLIENT where CL_NOM = :CLIENT into CL_NO;
DS_NUM = US_INT || IntToStr(NUM,'%06u');
INSERT INTO TDOSSIER (
DS_NO,
DS_NUM,
DS_DT,
DS_TM,
DS_PAR,
DS_CRSPD,
DS_CL,
DS_AR,
DS_MRQ,
DS_SN,
DS_CPB,
DS_ETA,
DS_DETAIL,
DS_FACT_NUM,
DS_FACT_DT,
DS_WTLBR,
DS_WTLBR_DEB,
DS_WTLBR_FIN,
DS_WTPCE,
DS_WTPCE_DEB,
DS_WTPCE_FIN,
DS_CAR_BLK,
DS_CAR_COL,
DS_TONNER,
DS_ALIM)
VALUES (
:NUM,
:DS_NUM,
:DS_DT,
:DS_TM,
:US_NO,
:DS_CRSPD,
:CL_NO,
:DS_AR,
:MQ_NO,
:DS_SN,
:CPB_NO,
:ETA_NO,
:DS_DETAIL,
:DS_FACT_NUM,
:DS_FACT_DT,
:DS_WTLBR,
:DS_WTLBR_DEB,
:DS_WTLBR_FIN,
:DS_WTPCE,
:DS_WTPCE_DEB,
:DS_WTPCE_FIN,
:DS_CAR_BLK,
:DS_CAR_COL,
:DS_TONNER,
:DS_ALIM);
END |
Partager