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
|
create or replace
FUNCTION FS_RECHERCHE_NEW ( PFam_c IN CHAR,
PFour_c IN CHAR,
PRefFour_c IN CHAR,
PMag_c IN CHAR,
PDateHistomou_c IN CHAR,
PDateJour_c IN CHAR) RETURN NUMBER AS
TYPE Curseur_c IS REF CURSOR;
ReqStEnv_c VARCHAR(1000);
ReqStEnv_c := ' SELECT HI.NMAGASIN , HI.NUMETIQ , JT.NUMBORD , SUM(HI.QTEVENDUE)
FROM HISTOMOU HI , JOURTR JT
WHERE HI.CFAMILLE = ''' || PFam_c || ''' AND
HI.CFOURNISS = ''' || PFour_c || ''' AND
HI.REFFOUR = ''' || PReffour_c || ''' AND
HI.MAGDES = ''' || PMag_c || ''' AND
HI.TYPEENREG LIKE ''E%'' AND
HI.DATEVENT BETWEEN
TO_DATE('''||PDateHistomou_c||''',''YYYYMMDD'') AND
TO_DATE('''||PDateJour_c||''' ,''YYYYMMDD'') AND
( HI.NMAGASIN,HI.NUMETIQ) NOT IN
( SELECT ST.NMAGASIN, ST.NUMETIQ
FROM STOCKC ST
WHERE ST.CONTRMARQ = HI.MAGDES AND
ST.NMAGASIN = HI.NMAGASIN AND
ST.NUMETIQ = HI.NUMETIQ ) AND
HI.NUMFACCLI = JT.NUMENREG
GROUP BY HI.NMAGASIN , HI.NUMETIQ , JT.NUMBORD ';
OPEN Cur_StEnvoye FOR ReqStEnv_c; |
Partager