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 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
|
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 /*+ index(tie TIE_IDX1) */ 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(p2.coduni, ' '), ' ', pro.codzn15, 'PI', pro.codzn15, DECODE(SUBSTR(p2.coduni, 1, 1), 'P', prc.codzn2, 'D', prc.codzn2, 'B', prc.codzn2, pro.codzn15)),2,8),
/*liv.datliv*/
(SELECT MAX(liv.datliv)
FROM eve liv
WHERE liv.codsoc = cde.codsoc
AND liv.achvto = cde.achvte
AND liv.typevo = cde.typeve
AND liv.numevo = cde.numeve
),
cdp.codpro,
' ',
pro.codzn10,
cde.codctg,
'XXX',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
' ',
/*liv.datliv*/
(
SELECT MAX(liv.datliv)
FROM eve liv
WHERE liv.codsoc = cde.codsoc
AND liv.achvto = cde.achvte
AND liv.typevo = cde.typeve
AND liv.numevo = cde.numeve
) ,
' ',
' ',
' ',
' ',
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 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
LEFT OUTER JOIN pro p2
ON p2.codsoc = prc.codsoc
AND p2.codpro = prc.codzn2
WHERE cde.codctg NOT IN ('DE','GR','EC'); |
Partager