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
|
CREATE OR REPLACE FORCE VIEW WV_QIFM30_bis (CODSOC, LIB1, LIB2, LIB3, LIB4, LIB5, LIB6, LIB7, LIB8, LIB9, LIB10, LIB11, LIB12, LIB13, LIB14, LIB15, LIB16, LIB17, LIB18, LIB19, LIB20, DAT1, DAT2, DAT3, DAT4, DAT5, NUM01, NUM02, NUM03, NUM04, NUM05, NUM06, NUM07, NUM08, NUM09, NUM10, VAL01, VAL02, VAL03, VAL04, VAL05, VAL06, VAL07, VAL08, VAL09, VAL10, VAL11, VAL12, VAL13, VAL14, VAL15, VAL16, VAL17, VAL18, VAL19, VAL20)
AS
SELECT cde.codsoc,
cde.achvte,
cde.typeve,
DECODE( UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16), '000'
||SUBSTR(tie.sigtie,2,5)),
cde.codeta,
tie.codett,
SUBSTR(DECODE(NVL(trim(prc.codzn2), ' '), ' ', pro.codzn15, prc.codzn2),2,8),
max(liv.datliv),
cdp.codpro,
' ',
pro.codzn10,
cde.codctg,
'XXX',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
max(liv.datliv),
' ',
' ',
' ',
' ',
cde.numeve,
NVL(to_number(trim(evt.codzn5)), cdp.qtecde),
0,0,0,
0,0,0,0,0,
pru.coefuv,
0,0,0,0,
0,0,0,0,0,
0,0,0,0,0,
0,0,0,0,0
FROM eve cde
inner join eve liv on liv.codsoc = cde.codsoc and liv.achvto = cde.achvte and liv.typevo = cde.typeve and liv.numevo = cde.numeve and liv.achvte = 'V' and liv.typeve = 'LIV'
INNER JOIN tie
ON tie.codsoc = cde.codsoc
AND tie.typtie = 'CLI'
AND tie.sigtie = cde.sigtie
INNER JOIN evp cdp
ON cdp.codsoc = cde.codsoc
AND cdp.achvte = cde.achvte
AND cdp.typeve = cde.typeve
AND cdp.numeve = cde.numeve
INNER JOIN pro
ON pro.codsoc = cdp.codsoc
AND pro.codpro = cdp.codpro
INNER JOIN prl
ON prl.codsoc = pro.codsoc
AND prl.codpro = pro.codpro
AND prl.typtie = ' '
AND prl.sigtie = ' '
AND prl.codva1 = '00'
AND prl.codva2 = SUBSTR(pro.codpro,8,2)
AND prl.cntcod = ' '
INNER JOIN pru
ON pru.codsoc = prl.codsoc
AND pru.codpro = prl.codpro
AND pru.coduni = prl.coduni1
INNER JOIN evt
ON evt.codsoc = cdp.codsoc
AND evt.achvte = cdp.achvte
AND evt.typeve = cdp.typeve
AND evt.numeve = cdp.numeve
AND evt.numpos = cdp.numpos
AND evt.numlig = 0
AND evt.numspo = 0
AND evt.numblo = 0
LEFT OUTER JOIN prc
ON prc.codsoc = cde.codsoc
AND prc.typtie = 'CLI'
AND prc.sigfou = tie.sigtie
AND prc.codpro = pro.codpro
WHERE cde.codctg NOT IN ('DE','GR','EC')
group by cde.codsoc, cde.achvte, cde.typeve, DECODE( UPPER(SUBSTR(tie.codzn16,1,1)),'P',upper(tie.codzn16), '000' ||SUBSTR(tie.sigtie,2,5)), cde.codeta, tie.codett, SUBSTR(DECODE(NVL(trim(prc.codzn2), ' '), ' ', pro.codzn15, prc.codzn2),2,8), cdp.codpro, pro.codzn10, cde.codctg, cde.numeve, NVL(to_number(trim(evt.codzn5)), cdp.qtecde), pru.coefuv; |
Partager