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
|
spool /usr1/lw/edt/FACSB.txt
SET TERM OFF
COL today NOPRINT NEW_VALUE today
SELECT sysdate today FROM dual;
SET TERM ON
set pages 3000
set lines 132
set verify off
set feedback off
TTITLE CENTER'*** EDITION DES FACTURES ENREGISTREES DEPUIS LE << &&PARAM2 >> ET NON MANDATEES.***' -
SKIP 3 -
'Edité le &today' -
skip 3
Select FAC."Ty",
FAC."DateRecep",
FAC."NumFact",
FAC."MtTTC",
FAC."NumFo/Tiers",
CASE WHEN FAC.Fattier = 'F'
THEN (Select T.fnfo from kfourni T where FAC."NumFo/Tiers"=T.fcfo AND FAC.SCSO=T.SCSO)
ELSE (Select Ti.tinom from ktiers Ti where FAC."NumFo/Tiers"=Ti.ticod AND FAC.SCSO=Ti.SCSO)
END as "Four/Tiers",
FAC."NumEng",
FAC."ExeEnga",
FAC."TE",
SUI.dest,
KENG.Pecodop "Nom"
FROM (SELECT decode(F.fatype, 'F','Fa','A','Av','R','R') "Ty",
lpad(F.fadrec,9,' ') "DateRecep",
F.FAEXE "ExeFac",
F.fanfac "NumFact",
nvl(F.famtht,0)-nvl(F.faremi,0)+nvl(F.fatva,0) "MtTTC",
F.factier "NumFo/Tiers",
F.fanoeng "NumEng",
F.enexe "ExeEnga" ,
decode(F.faceo,'B','BT','O','OS','C','Cde') "TE",
F.FATTIER,
F.SCSO
FROM KSAIFAC F
WHERE (F.VALID IS NULL)
AND ((F.fadrec >= DECODE('&&PARAM2',null,'01-JAN-00','&&PARAM2')) or F.fadrec is null )
) FAC,
(SELECT
FACSUI.dest,
FACSUI.FAEXE,
FACSUI.FANFAC
FROM KSBSUIFA FACSUI
WHERE FACSUI.ROWID = (SELECT MAX(FACSUI2.ROWID)
FROM KSBSUIFA FACSUI2
WHERE (FACSUI.FANFAC=FACSUI2.FANFAC AND FACSUI.FAEXE=FACSUI2.FAEXE))
) SUI,
(SELECT
Pecodop,
enno,
enexe
FROM KPERSONL K,
KVENG ENG
WHERE ENG.PEC=K.PEC) KENG
WHERE
(FAC."ExeFac"=SUI.FAEXE(+) AND FAC."NumFact"=SUI.FANFAC(+))
AND (FAC."NumEng"=KENG.ENNO(+) AND FAC."ExeEnga"=KENG.ENEXE(+))
AND (SUI.dest like UPPER('%&&PARAM1%'))
ORDER BY 3
/
spool off
ho /usr1/lw/bin/mt_facstandby.sh &&PARAM1 |
Partager