Код:
CREATE OR REPLACE FORCE VIEW "SUPERMAG"."SVSPECNACLREP" ("DOCTYPE", "DOCID", "CREATEDAT", "DOCSTATE", "CLIENTINDEX", "LOCATIONTO", "LOCATIONFROM", "OPCODE", "USEROP", "SPECITEM", "DISPLAYITEM", "ARTICLE", "QUANTITY", "ITEMPRICE", "ITEMPRICENOTAX", "TOTALPRICENOTAX", "TOTALPRICE", "ITEMPRICECUR", "TOTALPRICECUR", "VATSUM", "VATRATE", "NSPSUM", "QUANTBYDOC", "REASON", "CAUSETYPE", "CAUSEID", "NAME", "IDMEASUREMENT", "MESABBREV", "PREC", "MEASCODE", "SUPPLIERARTICLE", "GROSSWEIGHT", "COUNTRY") AS
select d.DocType, d.ID DocID, d.Createdat, d.DocState,
d.ClientIndex, d.LocationTo, d.LocationFrom, d.OpCode, d.UserOp,
s.specitem, s.displayitem, s.article,
s.Quantity, s.ItemPrice, s.ItemPriceNoTax,
nvl(s.TotalPriceNoTax,0) TotalPriceNoTax, s.TotalPrice,
s.ItemPriceCur, s.TotalPriceCur,
nvl((select t.taxsum from smspectax t
where t.doctype=s.doctype
and t.docid=s.docid
and t.specitem=s.specitem
and exists (select 1 from SMTaxIdentity i
where t.TaxID = i.TaxID
and i.IdentId = 0)),0) VATSum,
nvl((select t.taxrate from smspectax t
where t.doctype=s.doctype
and t.docid=s.docid
and t.specitem=s.specitem
and exists (select 1 from SMTaxIdentity i
where t.TaxID = i.TaxID
and i.IdentId = 0)),0) VATRate,
nvl((select t.taxsum from smspectax t
where t.doctype=s.doctype
and t.docid=s.docid
and t.specitem=s.specitem
and exists (select 1 from SMTaxIdentity i
where t.TaxID = i.TaxID
and i.IdentId = 1)),0) NSPSum,
nvl((select l.QuantByDoc from SLSpecQMismatch l
where l.doctype = s.doctype
and l.docid = s.docid
and l.specitem = s.specitem), s.Quantity) QuantByDoc,
(select l.Reason from SLSpecQMismatch l
where l.doctype = s.doctype
and l.docid = s.docid
and l.specitem = s.specitem) as Reason,
s.CauseType, s.CauseID,
replace(crd.name, chr(9), ' ') name,
crd.IDMeasurement,
nvl(crd.MesAbbrev,m.Abbrev) MesAbbrev, m.Prec, m.Code MeasCode,
(select sc.SupplierArticle
from SMSupplyCase sc
where sc.Article = s.Article
and sc.IDSupplier = d.ClientIndex
) SupplierArticle,
(select sum(l.GrossWeight*decode(l.PackNum,-1,s.Quantity,l.NPacks))
from SLSpecPacks l
where l.DocType = s.DocType
and l.DocID = s.DocID
and l.SpecItem = s.SpecItem
) GrossWeight,
(select l.Country from SMSpecNacl l
where l.DocType = s.DocType
and l.DocID = s.DocID
and l.SpecItem = s.SpecItem
) Country
from SMDocuments d, SMSpec s, SMCard crd, SAMeasurement m
where crd.article = s.article
and crd.IDMeasurement = m.ID
and s.DocType = d.DocType
and s.DocID = d.ID
and d.DocType in ('WI','WO','CI')
union
select d.DocType, d.ID DocID, d.Createdat, d.DocState,
d.ClientIndex, d.LocationTo, d.LocationFrom, d.OpCode, d.UserOp,
t.SpecItem, t.DisplayItem, t.Article,
0 Quantity, t.ItemPrice, t.ItemPriceNoTax,
0 TotalPriceNoTax, 0 TotalPrice,
t.ItemPriceCur, 0 TotalPriceCur,
0 VATSum, t.VATRate, 0 NSPSum, t.QuantByDoc, t.Reason,
'' CauseType, '' CauseID,
replace(crd.name, chr(9), ' ') name,
crd.IDMeasurement,
nvl(crd.MesAbbrev,m.Abbrev) MesAbbrev, m.Prec, m.Code MeasCode,
(select sc.SupplierArticle
from SMSupplyCase sc
where sc.Article = t.Article
and sc.IDSupplier = d.ClientIndex
) SupplierArticle,
0 GrossWeight,
(select l.Country from SMSpecNacl l
where l.DocType = t.DocType
and l.DocID = t.DocID
and l.SpecItem = t.SpecItem
) Country
from SMDocuments d, TTPrintSp