428 lines
18 KiB
Plaintext
Executable File
428 lines
18 KiB
Plaintext
Executable File
-- Fatture con SMI
|
|
CREATE VIEW &DT/VBFTE55V AS (
|
|
select distinct
|
|
case when "T12".NUMFAT is null then '0' else '1' end ALLEGATO,
|
|
"T1".TFDTF TFDTF,
|
|
"T1".TFTPN TFTPN,
|
|
"T1".TFNRF TFNRF,
|
|
"T1".TFTPD TFTPD,
|
|
case when "T1".TFFL2 = '' then '0' else '1' end TFFL2,
|
|
ltrim("T1".TFCLS) CLIENTE,
|
|
"T3".DSCOCP DSCOCP,
|
|
case when ("T6".STATOTRAS = 'T' and "T13".PROGID is not null) then 'E' when ("T6".STATOTRAS = 'V' and "T13".PROGID is not null) then 'C' when "T6".STATOTRAS is null then 'B' else "T6".STATOTRAS end STATO,
|
|
case when ("T8".MAILPEC is not null and "T8".MAILPEC <> '') then "T8".MAILPEC else coalesce("T6".CODIDEST, case when "T8".PAOPRIV is null then '0000000' else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end) end IPA_PEC,
|
|
case when ("T8".PAOPRIV is null or "T8".PAOPRIV = 'R') then '1' else '0' end PRIVATO,
|
|
"T9".TDREG TDREG,
|
|
coalesce(substr("T4".XDATTB, 9, 20), '') DESC_GIVA,
|
|
"T6".PROGID PROGID,
|
|
"T1".TFAZI TFAZI,
|
|
case when "T13".PROGID is null then '0' else '1' end ERRORI,
|
|
"T6".EXTID EXTID,
|
|
"T6".NOMEXML NOMEXML,
|
|
case when "T8".PAOPRIV = 'R' then "T8".CODIPA else case when "T8".PAOPRIV is null then '0000000' else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end end CODEIPA,
|
|
"T6".SERVTRA SERVTRA,
|
|
"T5".NUMEFATT NUMEFATT
|
|
from GSDVDATW.VBFTE00F "T1" join GSDVDATW.LFTPD00F "T9" on (T1.TFAZI = T9.TDAZI and T1.TFTPD = T9.TDCDO)
|
|
join C1180DAT.CGPCO00F "T3" on substr(T1.TFCLS, 3) = T3.CONTCP
|
|
join C1180DAT.SMTAB00F "T4" on T9.TDREG = T4.XCODTB
|
|
left outer join C1180DAT.FTPAE55F "T2" on (T1.TFAZI = T2.CDDITTA and T2.CDCANT = T1.TFCAN and T3.CONTCP = T2.CODCLI and T2.CDCOMME = '' and T1.TFDTF >= T2.DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T7" on (T1.TFAZI = T7.CDDITTA and T1.TFCOM = T7.CDCOMME and T1.TFCAN = T7.CDCANT and T7.CODCLI = '' and T1.TFDTF >= T7.DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T8" on (T3.CONTCP = T8.CODCLI and T8.CDCOMME = '' and T8.CDCANT = '')
|
|
left outer join C1180DAT.FTPAF55F "T5" on (T1.TFAZI = T5.CDDTFM and T1.TFDTF = T5.DTFTFM and T1.TFNRF = T5.NRFTFM and T1.TFTPN = T5.NFATFM)
|
|
left outer join C1180DAT.FTPAI55F "T6" on T5.PROGID = T6.PROGID left outer join C1180DAT.FTPAU55F "T12" on (T1.TFAZI = T12.CDDTFAT and T1.TFDTF = T12.DATAFAT and T1.TFNRF = T12.NUMFAT and T1.TFTPN = T12.NFATFAT)
|
|
left outer join C1180DAT.FTPAX55F "T13" on (T6.PROGID = T13.PROGID and "T13".PROGRIGA = 0)
|
|
join C1180DAT.CGANA00F "T18" on "T18".CONTCA = "T3".CONTCP
|
|
join C1180DAT.FTPSET55F "T17" on 1 = 1
|
|
where (XTIPTB = '01GI' and "T1".TFNRF > 0 and "T1".TFNRF <> 9999999 and "T1".TFDTF >= 20140601 and "T1".TFFL1 <> ' ' and "T1".TFAZI = '001' and ("T5".STORFM is null or "T5".STORFM = '') and ("T5".NRFTFM is not null or (coalesce("T8".ESCLUSOFE, '') = '' and (("T8".DTIPA is null and "T18".CISOCA = 'IT' and "T1".TFDTF >= "T17".DATAINB2B) or ("T8".DTIPA is not null and (("T8".DTIPA > 0 and "T1".TFDTF >= "T8".DTIPA) or ("T8".DTIPA = 0 and ("T8".PAOPRIV = ' ' or "T1".TFDTF >= "T17".DATAINB2B)))))))
|
|
);
|
|
|
|
-- Fatture con SAP
|
|
CREATE VIEW &DT/VBFTE55V AS (
|
|
select distinct
|
|
case when "T12".NUMFAT is null then '0' else '1' end ALLEGATO,
|
|
"T1".TFDTF TFDTF,
|
|
"T1".TFTPN TFTPN,
|
|
"T1".TFNRF TFNRF,
|
|
"T1".TFTPD TFTPD,
|
|
case when "T1".TFFL2 = '' then '0' else '1' end TFFL2,
|
|
ltrim("T1".TFCLS) CLIENTE,
|
|
"T16".OFRAG DSCOCP,
|
|
case when ("T6".STATOTRAS = 'T' and "T13".PROGID is not null) then 'E' when ("T6".STATOTRAS = 'V' and "T13".PROGID is not null) then 'C' when "T6".STATOTRAS is null then 'B' else "T6".STATOTRAS end STATO,
|
|
case when ("T8".MAILPEC is not null and "T8".MAILPEC <> '') then "T8".MAILPEC else coalesce("T6".CODIDEST, case when "T8".PAOPRIV is null then '0000000' else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end) end IPA_PEC,
|
|
case when ("T8".PAOPRIV is null or "T8".PAOPRIV = 'R') then '1' else '0' end PRIVATO,
|
|
substr("T14".TBDAT, 63, 1) TDREG,
|
|
coalesce(substr("T14".TBDAT, 9, 20), '') DESC_GIVA,
|
|
"T6".PROGID PROGID,
|
|
"T1".TFAZI TFAZI,
|
|
case when "T13".PROGID is null then '0' else '1' end ERRORI,
|
|
"T6".EXTID EXTID,
|
|
"T6".NOMEXML NOMEXML,
|
|
case when "T8".PAOPRIV = 'R' then "T8".CODIPA else case when "T8".PAOPRIV is null then '0000000' else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end end CODEIPA,
|
|
"T6".SERVTRA SERVTRA,
|
|
"T5".NUMEFATT NUMEFATT
|
|
from GSDVDATW.VBFTE00F "T1" join GSDVDATW.LFTPD00F "T9" on (T1.TFAZI = T9.TDAZI and T1.TFTPD = T9.TDCDO)
|
|
join GSDVDATW.GOFFE00F "T16" on (T1.TFAZI = T16.OFAZI and T1.TFCLS = T16.OFCOD and 'C' = "T16".OFTIP)
|
|
join GSDVDATW.BTABE00F "T14" on ("T14".TBCDA = '000' and "T14".TBTIP = 'TGCA' and T9.TDCAC = T14.TBELE)
|
|
join GSDVDATW.BTABE00F "T15" on (T9.TDAZI = T15.TBCDA and "T15".TBTIP = 'TGGI' and "T15".TBELE = substr("T14".TBDAT, 63, 1))
|
|
left outer join C1180DAT.FTPAE55F "T2" on (T1.TFAZI = T2.CDDITTA and "T2".CDCANT = "T1".TFCAN and "T2".CODCLI = substr("T16".OFCOD, 3) and "T2".CDCOMME = '' and "T1".TFDTF >= "T2".DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T7" on (T1.TFAZI = T7.CDDITTA and T1.TFCOM = T7.CDCOMME and T1.TFCAN = T7.CDCANT and "T7".CODCLI = '' and "T1".TFDTF >= "T7".DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T8" on (T1.TFAZI = T8.CDDITTA and "T8".CODCLI = substr("T16".OFCOD, 3) and "T8".CDCOMME = '' and "T8".CDCANT = '')
|
|
left outer join C1180DAT.FTPAF55F "T5" on (T1.TFAZI = T5.CDDTFM and T1.TFDTF = T5.DTFTFM and T1.TFNRF = T5.NRFTFM and T1.TFTPN = T5.NFATFM and "T5".GIVAFM = substr("T14".TBDAT, 63, 1))
|
|
left outer join C1180DAT.FTPAI55F "T6" on T5.PROGID = T6.PROGID
|
|
left outer join C1180DAT.FTPAU55F "T12" on (T1.TFAZI = T12.CDDTFAT and T1.TFDTF = T12.DATAFAT and T1.TFNRF = T12.NUMFAT and T1.TFTPN = T12.NFATFAT and "T12".GIVAFAT = substr("T14".TBDAT, 63, 1))
|
|
left outer join C1180DAT.FTPAX55F "T13" on (T6.PROGID = T13.PROGID and "T13".PROGRIGA = 0)
|
|
join GSGVOBJPW.FTPSET55F "T17" on 1 = 1
|
|
where ("T1".TFNRF > 0 and "T1".TFNRF <> 9999999 and "T1".TFDTF >= 20140601 and "T1".TFFL1 <> ' ' and "T1".TFAZI = '001' and ("T5".STORFM is null or "T5".STORFM = '') and ("T5".NRFTFM is not null or (coalesce("T8".ESCLUSOFE, '') = '' and (("T8".DTIPA is null and "T16".OFISO = 'IT' and "T1".TFDTF >= "T17".DATAINB2B) or ("T8".DTIPA is not null and (("T8".DTIPA > 0 and "T1".TFDTF >= "T8".DTIPA) or ("T8".DTIPA = 0 and ("T8".PAOPRIV = ' ' or "T1".TFDTF >= "T17".DATAINB2B)))))))
|
|
)
|
|
|
|
-- Esensione clienti SMI
|
|
create view &DT/FTPAE55V AS (
|
|
select "CGPCO00F".CONTCP, "CGPCO00F".DSCOCP, "CGANA00F".CDFICA, "CGANA00F".PIVACA, "CGANA00F".CISOCA,
|
|
coalesce("FTPAE55F".CODIPA, '') CODIPA, coalesce("FTPAE55F".DTIPA,
|
|
0) DTIPA , coalesce(CDDITTA, '001') CDDITTA, coalesce("FTPAE55F".NOSPPA,
|
|
' ') NOSPPA ,
|
|
case when "FTPAE55F".CODIPA
|
|
is null then 0 else 1 end FOUND,
|
|
COMTAG,
|
|
COMTPDATO,
|
|
coalesce("FTPAE55F".MAILPEC, '') MAILPEC,
|
|
PAOPRIV,
|
|
INDISTAO,
|
|
IDFISRAF
|
|
from CGPCO00F "CGPCO00F" join CGANA00F "CGANA00F" on
|
|
CGPCO00F.CONTCP = CGANA00F.CONTCA left outer join FTPAE55F
|
|
"FTPAE55F" on (CGPCO00F.CONTCP = FTPAE55F.CODCLI and
|
|
FTPAE55F.CDCOMME = '' and FTPAE55F.CDCANT = '')
|
|
where (CLFOCP = 'C')
|
|
)
|
|
|
|
-- Esensione clienti SAP
|
|
CREATE VIEW &DT/FTPAE55V AS (
|
|
Select OFAZI CDDITTA , SubStr(OFCOD , 3) CONTCP,
|
|
OFRAG DSCOCP, OFCFI CDFICA, OFPIV PIVACA, OFISO CISOCA,
|
|
Coalesce("FTPAE55F".CODIPA, '') CODIPA,
|
|
Coalesce("FTPAE55F".DTIPA, 0) DTIPA ,
|
|
coalesce("FTPAE55F".NOSPPA,
|
|
' ') NOSPPA ,
|
|
case when "FTPAE55F".CODIPA
|
|
is null then 0 else 1 end FOUND,
|
|
Coalesce("FTPAE55F".COMTAG, '') COMTAG,
|
|
Coalesce("FTPAE55F".COMTPDATO, '') COMTPDATO ,
|
|
coalesce("FTPAE55F".MAILPEC, '') MAILPEC,
|
|
PAOPRIV,
|
|
INDISTAO,
|
|
IDFISRAF
|
|
From GOFFE00F
|
|
Left Outer Join FTPAE55F
|
|
On (OFAZI = CDDITTA And
|
|
SUBSTR(OFCOD , 3)= CODCLI And
|
|
FTPAE55F.CDCOMME = '' And
|
|
FTPAE55F.CDCANT = '')
|
|
Where OFTIP = 'C')
|
|
|
|
|
|
-- Esensione Mod. Pag. SMI
|
|
CREATE VIEW &DT/FTPAP55V AS (
|
|
select SMTAB00F.XCODTB, substr(SMTAB00F.XDATTB, 9, 40) DESC_GAL,
|
|
coalesce(FTPAP55F.TABPACP, '') TABPACP
|
|
from SMTAB00F left outer join FTPAP55F on SMTAB00F.XCODTB = FTPAP55F.TABGACP
|
|
where SMTAB00F.XTIPTB = '01CP')
|
|
|
|
-- Esensione Mod. Pag. SAP
|
|
CREATE VIEW &DT/FTPAP55V AS (
|
|
Select btabe00F.tbele XCODTB,
|
|
SubStr(btabe00F.tbdat, 9, 40) DESC_GAL,
|
|
Coalesce(FTPAP55F.TABPACP, '') TABPACp
|
|
From btabe00F left outer join
|
|
FTPAP55F on btabe00F.tbele = FTPAP55F.TABGACP
|
|
Where btabe00f.tbcda='000' AND btabe00F.tBtip = 'TGCP'
|
|
And SubStr(btabe00F.tbdat, 132, 1) in ('C',' ') )
|
|
|
|
-- Esensione IVA SMI
|
|
CREATE VIEW &DT/FTPAN55V AS (
|
|
SELECT SMTAB00F.XCODTB, SUBSTR(SMTAB00F.XD
|
|
ATTB, 9, 20) DESC_GAL, COALESCE(FTPAN55F.TABPACI, '') TABPACI,
|
|
case when FTPAN55F.TABPACI
|
|
is null then 0 else 1 end FOUND,
|
|
RIFNORM
|
|
FROM C1180DAT.SMTAB00F SMTAB00F LEFT OUTER JOIN FTPAN55F
|
|
ON SMTAB00F.XCODTB = FTPAN55F.TABGACI WHERE SMTAB00F.XTIPTB = '01CI')
|
|
|
|
-- Esensione IVA SAP
|
|
CREATE VIEW &DT/FTPAN55V AS (
|
|
Select btabe00F.tbele XCODTB,
|
|
Max(substr(btabe00F.tbdat, 9, 20)) DESC_GAL,
|
|
Coalesce(max(FTPAN55F.TABPACI), '') TABPACI ,
|
|
case when max(FTPAN55F.TABPACI)
|
|
is null then 0 else 1 end FOUND,
|
|
Coalesce(max(FTPAN55F.RIFNORM), '') RIFNORM
|
|
From btabe00F Left Outer Join
|
|
FTPAN55F on btabe00F.tbele = FTPAN55F.TABGACI
|
|
Where btabe00F.tBtip = 'TGCI' AND Substr(btabe00F.tbdat, 157, 1) ='C'
|
|
Group By btabe00F.tbele)
|
|
|
|
-- Estensione cantieri clienti SMI
|
|
select "VCANC00F".CCAZI, "CGANA00F".CONTCA, "VCANC00F".CCCAN,
|
|
"VCANC00F".CCDES, "CGANA00F".CDFICA, coalesce("FTPAE55F".CODIPA, '')
|
|
CODIPA, coalesce("FTPAE55F".DTIPA, 0) DTIPA from VCANC00F "VCANC00F"
|
|
join CGANA00F "CGANA00F" on substr(VCANC00F.CCCLI, 3) =
|
|
CGANA00F.CONTCA left outer join FTPAE55F "FTPAE55F" on
|
|
(CGANA00F.CONTCA = FTPAE55F.CODCLI and VCANC00F.CCAZI =
|
|
FTPAE55F.CDDITTA and FTPAE55F.CDCANT = VCANC00F.CCCAN and
|
|
FTPAE55F.CDCOMME = '')
|
|
|
|
-- SQL FATTURE SENZA VIEW
|
|
select distinct
|
|
case when "T12".NUMFAT is null then '0' else '1' end "F1",
|
|
"T1".TFDTF "F2",
|
|
"T1".TFTPN "F3",
|
|
"T1".TFNRF "F4",
|
|
"T1".TFTPD "F5",
|
|
case when "T1".TFFL2 = '' then '0' else '1' end "F6",
|
|
ltrim("T1".TFCLS) "F7", "T3".DSCOCP "F8",
|
|
case when ("T6".STATOTRAS = 'V' and "T13".PROGID is not null) then 'C' when "T6".STATOTRAS is null then 'B' else "T6".STATOTRAS end "F9",
|
|
case when "T8".PAOPRIV = 'R' then case when "T8".MAILPEC = '' then "T8".CODIPA else "T8".MAILPEC end else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end "F10",
|
|
case when "T8".PAOPRIV = 'R' then '1' else '0' end "F11",
|
|
"T9".TDREG "F12",
|
|
coalesce(substr("T4".XDATTB, 9, 20), '') "F13",
|
|
"T6".PROGID "F14",
|
|
"T1".TFAZI "F15",
|
|
case when "T13".PROGID is null then '0' else '1' end "F16",
|
|
"T6".EXTID "F17",
|
|
"T6".NOMEXML "F18",
|
|
case when "T8".PAOPRIV = 'R' then "T8".CODIPA else coalesce(coalesce("T2".CODIPA, "T7".CODIPA), "T8".CODIPA) end "F19"
|
|
from GSDVDATW.VBFTE00F "T1"
|
|
join GSDVDATW.LFTPD00F "T9" on (T1.TFAZI = T9.TDAZI and T1.TFTPD = T9.TDCDO)
|
|
join C1180DAT.CGPCO00F "T3" on substr(T1.TFCLS, 3) = T3.CONTCP
|
|
join C1180DAT.SMTAB00F "T4" on T9.TDREG = T4.XCODTB
|
|
left outer join C1180DAT.FTPAE55F "T2" on (T1.TFAZI = T2.CDDITTA and T2.CDCANT = T1.TFCAN and T3.CONTCP = T2.CODCLI and T2.CDCOMME = '' and T1.TFDTF >= T2.DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T7" on (T1.TFAZI = T7.CDDITTA and T1.TFCOM = T7.CDCOMME and T1.TFCAN = T7.CDCANT and T7.CODCLI = '' and T1.TFDTF >= T7.DTIPA)
|
|
left outer join C1180DAT.FTPAE55F "T8" on (T3.CONTCP = T8.CODCLI and T8.CDCOMME = '' and T8.CDCANT = '' and T1.TFDTF >= T8.DTIPA)
|
|
left outer join C1180DAT.FTPAF55F "T5" on (T1.TFAZI = T5.CDDTFM and T1.TFDTF = T5.DTFTFM and T1.TFNRF = T5.NRFTFM and T1.TFTPN = T5.NFATFM)
|
|
left outer join C1180DAT.FTPAI55F "T6" on T5.PROGID = T6.PROGID
|
|
left outer join C1180DAT.FTPAU55F "T12" on (T1.TFAZI = T12.CDDTFAT and T1.TFDTF = T12.DATAFAT and T1.TFNRF = T12.NUMFAT and T1.TFTPN = T12.NFATFAT)
|
|
left outer join C1180DAT.FTPAX55F "T13" on (T6.PROGID = T13.PROGID and "T13".PROGRIGA = 0)
|
|
where (XTIPTB = '01GI' and T1.TFNRF > 0 and T1.TFNRF <> 9999999 and T1.TFDTF >= 20140601 and T1.TFFL1 <> ' ' and "T8".CODIPA is not null and T1.TFAZI = '001' and (T5.STORFM is null or T5.STORFM = ''))
|
|
|
|
-- sql VIEW fatture non estraibili SMI
|
|
CREATE VIEW GSDVDATW/VBFTENE55V AS (
|
|
SELECT
|
|
"VBFTE".TFDTF,
|
|
"VBFTE".TFTPN,
|
|
"VBFTE".TFNRF,
|
|
"VBFTE".TFTPD,
|
|
substr("VBFTE".TFCLS, 3) TFCLS,
|
|
"CGPCO".DSCOCP,
|
|
"CGANA".CISOCA,
|
|
"CGANA".PIVACA,
|
|
"LFTPD".TDREG,
|
|
COALESCE(substr("TABGI".XDATTB, 9, 20), '') DESC_GIVA,
|
|
"VBFTE".TFCOM,
|
|
"VBFTE".TFCAN,
|
|
COALESCE("BCANT".CNNOM, "VCANC".CCDES,'') DESC_CANTIERE,
|
|
"VBFTE".TFFL2,
|
|
"FTPAI".STATOTRAS,
|
|
"VBFTE".TFAZI
|
|
FROM
|
|
GSDVDATW.VBFTE00F "VBFTE"
|
|
JOIN GSDVDATW.LFTPD00F "LFTPD" ON
|
|
(
|
|
VBFTE.TFAZI = LFTPD.TDAZI
|
|
AND VBFTE.TFTPD = LFTPD.TDCDO
|
|
)
|
|
JOIN C1180DAT.CGPCO00F "CGPCO" ON
|
|
substr(
|
|
"VBFTE".TFCLS,
|
|
3
|
|
)= "CGPCO".CONTCP
|
|
JOIN C1180DAT.CGANA00F "CGANA" ON
|
|
CGANA.CONTCA = CGPCO.CONTCP
|
|
JOIN C1180DAT.SMTAB00F "TABGI" ON
|
|
(
|
|
LFTPD.TDREG = TABGI.XCODTB
|
|
AND "TABGI".XTIPTB = '01GI'
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAE55F "FTPAE" ON
|
|
(
|
|
CGPCO.CONTCP = FTPAE.CODCLI
|
|
AND "FTPAE".CDCOMME = ''
|
|
AND "FTPAE".CDCANT = ''
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAF55F "FTPAF" ON
|
|
(
|
|
VBFTE.TFAZI = FTPAF.CDDTFM
|
|
AND VBFTE.TFDTF = FTPAF.DTFTFM
|
|
AND VBFTE.TFNRF = FTPAF.NRFTFM
|
|
AND VBFTE.TFTPN = FTPAF.NFATFM
|
|
AND "FTPAF".STORFM = ''
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAI55F "FTPAI" ON
|
|
FTPAF.PROGID = FTPAI.PROGID
|
|
LEFT OUTER JOIN GSDVDATW.BCANT00F "BCANT" ON
|
|
(
|
|
VBFTE.TFAZI = BCANT.CNAZI
|
|
AND VBFTE.TFCOM = BCANT.CNCOM
|
|
AND VBFTE.TFCAN = BCANT.CNCOD
|
|
)
|
|
LEFT OUTER JOIN GSDVDATW.VCANC00F "VCANC" ON
|
|
(
|
|
VBFTE.TFAZI = VCANC.CCAZI
|
|
AND VBFTE.TFCLS = VCANC.CCCLI
|
|
AND VBFTE.TFCAN = VCANC.CCCAN
|
|
)
|
|
WHERE
|
|
(
|
|
"VBFTE".TFNRF > 0
|
|
AND "VBFTE".TFNRF <> 9999999
|
|
AND "VBFTE".TFDTF >= 20140601
|
|
AND "VBFTE".TFFL1 <> ' '
|
|
AND "FTPAE".CODIPA IS NULL
|
|
));
|
|
|
|
-- sql VIEW fatture non estraibili SAP
|
|
CREATE VIEW &DT/VBFTENE55V AS (
|
|
select VBFTE.TFDTF ,
|
|
VBFTE.TFTPN ,
|
|
VBFTE.TFNRF ,
|
|
VBFTE.TFTPD ,
|
|
substring(VBFTE.TFCLS, 3) TFCLS,
|
|
GOFFE.OFRAG DSCOCP ,
|
|
GOFFE.OFISO CISOCA,
|
|
GOFFE.OFPIV PIVACA,
|
|
substr(TGCA.tbdat, 63 , 1) TDREG ,
|
|
coalesce(substr(TGCA.TBDAT, 9, 20), '') DESC_GIVA,
|
|
VBFTE.TFFL2 ,
|
|
VBFTE.TFCOM,
|
|
VBFTE.TFCAN,
|
|
COALESCE(BCANT.CNNOM, VCANC.CCDES,'') DESC_CANTIERE,
|
|
FTPAI.STATOTRAS,
|
|
VBFTE.TFAZI
|
|
FROM
|
|
VBFTE00F VBFTE
|
|
JOIN LFTPD00F LFTPD ON
|
|
VBFTE.TFAZI = LFTPD.TDAZI
|
|
AND VBFTE.TFTPD = LFTPD.TDCDO
|
|
JOIN GOFFE00F GOFFE ON
|
|
VBFTE.TFAZI=GOFFE.OFAZI AND 'C' = GOFFE.OFTIP AND VBFTE.TFCLS=GOFFE.OFCOD
|
|
JOIN btabe00f TGCA on TGCA.TBCDA = '000' AND TGCA.TBTIP = 'TGCA' AND TGCA.TBELE = LFTPD.TDCAC
|
|
LEFT OUTER JOIN FTPAE55F FTPAE ON
|
|
SUBSTR(VBFTE.TFCLS , 3) = FTPAE.CODCLI
|
|
AND FTPAE.CDCOMME = ''
|
|
AND FTPAE.CDCANT = ''
|
|
LEFT OUTER JOIN FTPAF55F FTPAF ON
|
|
VBFTE.TFAZI = FTPAF.CDDTFM
|
|
AND VBFTE.TFDTF = FTPAF.DTFTFM
|
|
AND VBFTE.TFNRF = FTPAF.NRFTFM
|
|
AND VBFTE.TFTPN = FTPAF.NFATFM
|
|
AND FTPAF.STORFM = ''
|
|
LEFT OUTER JOIN FTPAI55F FTPAI ON
|
|
FTPAF.PROGID = FTPAI.PROGID
|
|
LEFT OUTER JOIN BCANT00F BCANT ON
|
|
VBFTE.TFAZI = BCANT.CNAZI
|
|
AND VBFTE.TFCOM = BCANT.CNCOM
|
|
AND VBFTE.TFCAN = BCANT.CNCOD
|
|
LEFT OUTER JOIN VCANC00F VCANC ON
|
|
VBFTE.TFAZI = VCANC.CCAZI
|
|
AND VBFTE.TFCLS = VCANC.CCCLI
|
|
AND VBFTE.TFCAN = VCANC.CCCAN
|
|
WHERE
|
|
VBFTE.TFNRF > 0
|
|
AND VBFTE.TFNRF <> 9999999
|
|
AND VBFTE.TFDTF >= 20140601
|
|
AND VBFTE.TFFL1 <> ' '
|
|
AND FTPAE.CODIPA IS NULL);
|
|
|
|
-- sql fatture non estraibili senza VIEW
|
|
SELECT
|
|
"VBFTE".TFDTF "F0",
|
|
"VBFTE".TFTPN "F1",
|
|
"VBFTE".TFNRF "F2",
|
|
"VBFTE".TFTPD "F3",
|
|
substr(
|
|
"VBFTE".TFCLS,
|
|
3
|
|
) "F4",
|
|
"CGPCO".DSCOCP "F5",
|
|
"CGANA".CISOCA "F6",
|
|
"CGANA".PIVACA "F7",
|
|
"LFTPD".TDREG "F8",
|
|
COALESCE(
|
|
substr(
|
|
"TABGI".XDATTB,
|
|
9,
|
|
20
|
|
),
|
|
''
|
|
) "F9",
|
|
"VBFTE".TFCOM "F10",
|
|
"VBFTE".TFCAN "F11",
|
|
COALESCE(
|
|
"BCANT".CNNOM,
|
|
"VCANC".CCDES,
|
|
''
|
|
) "F12",
|
|
"VBFTE".TFFL2 "F13",
|
|
"FTPAI".STATOTRAS "F14",
|
|
"VBFTE".TFAZI "F15"
|
|
FROM
|
|
GSDVDATW.VBFTE00F "VBFTE"
|
|
JOIN GSDVDATW.LFTPD00F "LFTPD" ON
|
|
(
|
|
VBFTE.TFAZI = LFTPD.TDAZI
|
|
AND VBFTE.TFTPD = LFTPD.TDCDO
|
|
)
|
|
JOIN C1180DAT.CGPCO00F "CGPCO" ON
|
|
substr(
|
|
"VBFTE".TFCLS,
|
|
3
|
|
)= "CGPCO".CONTCP
|
|
JOIN C1180DAT.CGANA00F "CGANA" ON
|
|
CGANA.CONTCA = CGPCO.CONTCP
|
|
JOIN C1180DAT.SMTAB00F "TABGI" ON
|
|
(
|
|
LFTPD.TDREG = TABGI.XCODTB
|
|
AND "TABGI".XTIPTB = '01GI'
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAE55F "FTPAE" ON
|
|
(
|
|
CGPCO.CONTCP = FTPAE.CODCLI
|
|
AND "FTPAE".CDCOMME = ''
|
|
AND "FTPAE".CDCANT = ''
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAF55F "FTPAF" ON
|
|
(
|
|
VBFTE.TFAZI = FTPAF.CDDTFM
|
|
AND VBFTE.TFDTF = FTPAF.DTFTFM
|
|
AND VBFTE.TFNRF = FTPAF.NRFTFM
|
|
AND VBFTE.TFTPN = FTPAF.NFATFM
|
|
AND "FTPAF".STORFM = ''
|
|
)
|
|
LEFT OUTER JOIN C1180DAT.FTPAI55F "FTPAI" ON
|
|
FTPAF.PROGID = FTPAI.PROGID
|
|
LEFT OUTER JOIN GSDVDATW.BCANT00F "BCANT" ON
|
|
(
|
|
VBFTE.TFAZI = BCANT.CNAZI
|
|
AND VBFTE.TFCOM = BCANT.CNCOM
|
|
AND VBFTE.TFCAN = BCANT.CNCOD
|
|
)
|
|
LEFT OUTER JOIN GSDVDATW.VCANC00F "VCANC" ON
|
|
(
|
|
VBFTE.TFAZI = VCANC.CCAZI
|
|
AND VBFTE.TFCLS = VCANC.CCCLI
|
|
AND VBFTE.TFCAN = VCANC.CCCAN
|
|
)
|
|
WHERE
|
|
(
|
|
"VBFTE".TFNRF > 0
|
|
AND "VBFTE".TFNRF <> 9999999
|
|
AND "VBFTE".TFDTF >= 20140601
|
|
AND "VBFTE".TFFL1 <> ' '
|
|
AND "FTPAE".CODIPA IS NULL
|
|
AND "VBFTE".TFAZI = '001'
|
|
AND "VBFTE".TFDTF >= 20140606
|
|
)
|
|
ORDER BY
|
|
F0 DESC,
|
|
F2 DESC,
|
|
F8 ASC,
|
|
F4 ASC |