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
|
CREATE OR REPLACE FUNCTION dinsertpalletdesc (PLSqlszURN in text, PLSqlszSSCC in text, PLSqlszTagID in text, PLSqlszNumPalette in text, PLSqlszPlateforme in text, PLSqlszEntrepot in text,
PLSqltDateHeureIlotage in text, PLSqleTypePalette in text, PLSqlePFEPFS in text, PLSqlCodePDLClient in text,
PLSqlszCodeArticle in text, PLSqlszLibelleArticle in text, PLSqliNbColis in bigint, PLSqleAlcool in text, PLSqlszNumCommande in text,
PLSqlszNumPaletteFille in text, NewPallet in bigint)
RETURNS VOID AS $body$
DECLARE
BEGIN
if NewPallet=1 then
BEGIN
insert into dtag (szURN, szSSCC, szTagID, szNumPalette, szPlateforme, szEntrepot, tDateHeureIlotage, eTypePalette, ePFEPFS, CodePDLClient, itypetag)
values (PLSqlszURN, PLSqlszSSCC, PLSqlszTagID, PLSqlszNumPalette, PLSqlszPlateforme, PLSqlszEntrepot,
CASE
WHEN
substr(PLSqltDateHeureIlotage, 1, 19) LIKE '0002-11-30T00:00:00' THEN null
ELSE
to_timestamp(substr(replace(PLSqltDateHeureIlotage, 'T', ' '), 1, 19), 'RRRR-MM-DD HH24:MI:SS')
END,
PLSqleTypePalette,
CASE
WHEN PLSqlePFEPFS = 'E' THEN 'PFE'
WHEN PLSqlePFEPFS = 'S' THEN 'PFS'
WHEN PLSqlePFEPFS = 'B' THEN 'PFB'
ELSE
null
END,
PLSqlCodePDLClient, 0);
EXCEPTION
WHEN
unique_violation
THEN
update dtag set szNumPalette=PLSqlszNumPalette, szPlateforme=PLSqlszPlateforme, szEntrepot=PLSqlszEntrepot,
tDateHeureIlotage=
CASE
WHEN substr(PLSqltDateHeureIlotage, 1, 19) LIKE '0002-11-30T00:00:00' THEN null
ELSE
to_timestamp(substr(replace(PLSqltDateHeureIlotage, 'T', ' '), 1, 19), 'RRRR-MM-DD HH24:MI:SS')
END,
eTypePalette=PLSqleTypePalette, ePFEPFS=
CASE
WHEN PLSqlePFEPFS = 'E' THEN 'PFE'
WHEN PLSqlePFEPFS = 'S' THEN 'PFS'
WHEN PLSqlePFEPFS = 'B' THEN 'PFB'
ELSE
null
END,
CodePDLClient=PLSqlCodePDLClient where szURN=PLSqlszURN;
END;
delete from dpalette where szURN=PLSqlszURN;
END IF;
insert into DPALETTE (szURN, SZCODEARTICLE, SZLIBELLEARTICLE, INBCOLIS, EALCOOL, SZNUMCOMMANDE, SZNUMPALETTEFILLE)
values (PLSqlszURN, PLSqlszCodeArticle, PLSqlszLibelleArticle, PLSqliNbColis, (CASE WHEN PLSqleAlcool='O' THEN 'TRUE' WHEN PLSqleAlcool='N' THEN 'FALSE' ELSE Null END), PLSqlszNumCommande, PLSqlszNumPaletteFille);
COMMIT;
END;
$body$
LANGUAGE PLPGSQL; |
Partager