MagicBuzz | Genre :
Ca, c'est ce que j'ai écrit. C'est relou, faut scroller un peu sur la droite, mais c'est lisible.
Code :
SELECT tbl.lib1 enseigne, cli.nomtie, SUBSTR(fac.datliv, 5, 2) mois, decode(facp.numevo, 0, facp.numeve, facp.numevo) cde, ROUND(SUM(f_qtedip(facp.codsoc, facp.codpro, facp.coduni, 'PAL', facp.qtecde)), 2) cpal, SUM(DECODE(pro.codzn3, 'OS', facp.qtecde * facp.prxvdu, 0)) ctotal, ROUND(SUM(DECODE(pro.codzn3, 'OS', f_qtedip(facp.codsoc, facp.codpro, facp.coduni, 'PAL', facp.qtecde), 0)), 2) ospal, facp.prxuf ostotal, DECODE(pro.codzn3, 'OS', DECODE (fct_valzod (75, 'PRO', pro.codpro, 128 ), 'PROMO', 1, 0), 0) promo, facp.taurem1, facp.prxbrtfac FROM eve fac INNER JOIN lad l ON l.codsoc = fac.codsoc AND l.typtie = fac.typtie AND l.sigtie = fac.sigtie AND l.typadr = 'FAC' AND l.numadr = fac.numfil AND fac.sigtie <> '245205' AND l.sigadr <> '245205' INNER JOIN evp facp ON facp.codsoc = fac.codsoc AND facp.achvte = fac.achvte AND facp.typeve = fac.typeve AND facp.numeve = fac.numeve INNER JOIN pro ON pro.codsoc = fct_mev(fac.codsoc, 'PRO', ' ') AND pro.codpro = facp.codpro INNER JOIN tie cli ON cli.codsoc = fct_mev(fac.codsoc, 'TIE', 'CLI') AND cli.typtie = fac.typtie AND cli.sigtie = fac.sigtie INNER JOIN tbl ON tbl.codsoc = fct_mev(cli.codsoc, 'TBL', '210') AND tbl.codtbl = '210' AND tbl.cletbl = cli.codzn5 WHERE fac.codsoc IN (fct_mev(?, 'EVE', ' '), fct_mev(?, 'EVE', ' ')) AND fac.achvte = 'V' AND fac.typeve = 'FAC' AND SUBSTR(fac.datliv, 0, 4) = ? AND EXISTS ( SELECT NULL FROM evp osp INNER JOIN pro os ON os.codsoc = fct_mev(osp.codsoc, 'PRO', ' ') AND os.codpro = osp.codpro AND os.codzn3 = 'OS' WHERE osp.codsoc = fct_mev(fac.codsoc, 'EVP', ' ') AND osp.achvte = fac.achvte AND osp.typeve = fac.typeve AND osp.numeve = fac.numeve ) GROUP BY tbl.lib1, cli.nomtie, SUBSTR(fac.datliv, 5, 2), decode(facp.numevo, 0, facp.numeve, facp.numevo), DECODE (pro.codzn3, 'OS', DECODE(fct_valzod(75, 'PRO', pro.codpro, 128 ), 'PROMO', 1, 0), 0), facp.prxuf, facp.prxbrtfac, facp.taurem1 ORDER BY enseigne, nomtie, mois, cde, promo, ostotal
|
Ca, c'est ce que donne Formater Plus dans TOAD :
Code :
/* Formatted on 2008/04/18 12:41 (Formatter Plus v4.8.5) */ SELECT tbl.lib1 enseigne, cli.nomtie, SUBSTR (fac.datliv, 5, 2) mois, DECODE (facp.numevo, 0, facp.numeve, facp.numevo) cde, ROUND (SUM (f_qtedip (facp.codsoc, facp.codpro, facp.coduni, 'PAL', facp.qtecde ) ), 2 ) cpal, SUM (DECODE (pro.codzn3, 'OS', facp.qtecde * facp.prxvdu, 0)) ctotal, ROUND (SUM (DECODE (pro.codzn3, 'OS', f_qtedip (facp.codsoc, facp.codpro, facp.coduni, 'PAL', facp.qtecde ), 0 ) ), 2 ) ospal, facp.prxuf ostotal, DECODE (pro.codzn3, 'OS', DECODE (fct_valzod (75, 'PRO', pro.codpro, 128), 'PROMO', 1, 0 ), 0 ) promo, facp.taurem1, facp.prxbrtfac FROM eve fac INNER JOIN lad l ON l.codsoc = fac.codsoc AND l.typtie = fac.typtie AND l.sigtie = fac.sigtie AND l.typadr = 'FAC' AND l.numadr = fac.numfil AND fac.sigtie <> '245205' AND l.sigadr <> '245205' INNER JOIN evp facp ON facp.codsoc = fac.codsoc AND facp.achvte = fac.achvte AND facp.typeve = fac.typeve AND facp.numeve = fac.numeve INNER JOIN pro ON pro.codsoc = fct_mev (fac.codsoc, 'PRO', ' ') AND pro.codpro = facp.codpro INNER JOIN tie cli ON cli.codsoc = fct_mev (fac.codsoc, 'TIE', 'CLI') AND cli.typtie = fac.typtie AND cli.sigtie = fac.sigtie INNER JOIN tbl ON tbl.codsoc = fct_mev (cli.codsoc, 'TBL', '210') AND tbl.codtbl = '210' AND tbl.cletbl = cli.codzn5 WHERE fac.codsoc IN (fct_mev (?, 'EVE', ' '), fct_mev (?, 'EVE', ' ')) AND fac.achvte = 'V' AND fac.typeve = 'FAC' AND SUBSTR (fac.datliv, 0, 4) = ? AND EXISTS ( SELECT NULL FROM evp osp INNER JOIN pro os ON os.codsoc = fct_mev (osp.codsoc, 'PRO', ' ' ) AND os.codpro = osp.codpro AND os.codzn3 = 'OS' WHERE osp.codsoc = fct_mev (fac.codsoc, 'EVP', ' ') AND osp.achvte = fac.achvte AND osp.typeve = fac.typeve AND osp.numeve = fac.numeve) GROUP BY tbl.lib1, cli.nomtie, SUBSTR (fac.datliv, 5, 2), DECODE (facp.numevo, 0, facp.numeve, facp.numevo), DECODE (pro.codzn3, 'OS', DECODE (fct_valzod (75, 'PRO', pro.codpro, 128), 'PROMO', 1, 0 ), 0 ), facp.prxuf, facp.prxbrtfac, facp.taurem1 ORDER BY enseigne, nomtie, mois, cde, promo, ostotal
|
Mmm miam miam, comment c'est imbittable maintenant ![:love: :love:](https://forum-images.hardware.fr/icones/smilies/love.gif) |