SET QUOTED_IDENTIFIER OFF
GO
CREATE view [dbo].[EDIInvoiceException]
(
    invoiceno,
    invoicedate,
    cusno,
    billtoname,
    totinvoiceamount,
    orderno,
    purchaseorderno
)
as

select distinct
    invoiceno = case
        when InvoicedHeader.InvoiceNumber is null then null
        when charindex( '/', InvoicedHeader.InvoiceNumber ) = 0 then dbo.EdiTextCleaner( InvoicedHeader.InvoiceNumber )
        else dbo.EdiTextCleaner( InvoicedHeader.OrderNumber + '-' + reverse( substring( reverse( InvoicedHeader.InvoiceNumber ), 1, charindex('/', reverse( InvoicedHeader.InvoiceNumber ) ) - 1 ) ) )
    end,
    invoicedate = convert( nvarchar(8), InvoicedHeader.InvoiceDate, 112 ),
    cusno = dbo.EdiTextCleaner( InvoicedHeader.CustomerNumber ),
    billtoname = dbo.EdiTextCleaner( InvoicedHeader.CustomerName ),
    totinvoiceamount = InvoicedHeader.InvoiceTotal,
    orderno = dbo.EdiTextCleaner( InvoicedHeader.OrderNumber ),
    purchaseorderno = dbo.EdiTextCleaner( MilitaryOrder.GBLNumber )
from InvoicedHeader
inner join CustomerInvoiceReport on CustomerInvoiceReport.CustomerNumber = InvoicedHeader.CustomerNumber
inner join InvoiceStatus on InvoiceStatus.InvoiceStatusID = InvoicedHeader.InvoiceStatusFID
inner join InvoicedGroup on InvoicedGroup.IHPriKey = InvoicedHeader.IHPriKey
inner join Orders on Orders.PriKey = InvoicedHeader.OrdPriKey
left outer join MilitaryOrder on MilitaryOrder.OrdersFID = Orders.PriKey
left outer join CustomerInvoiceReportEDIItem as GroupCustomerInvoiceReportEDIItem on GroupCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID =
    InvoicedGroup.CustomerInvoiceReportEDIItemFID
left outer join InvoicedDetail on InvoicedDetail.IGPriKey = InvoicedGroup.IGPriKey
left outer join CustomerInvoiceReportEDIItem as DetailCustomerInvoiceReportEDIItem on DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID =
    InvoicedDetail.CustomerInvoiceReportEDIItemFID

where InvoicedHeader.InvoiceDate >= dateadd( D, -30, datediff( D, 0, getdate() ) ) and
    InvoiceStatus.Status = 'Invoice' and
    ( ( InvoicedDetail.IDPriKey is not null and DetailCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null ) or
    ( InvoicedDetail.IDPriKey is null and GroupCustomerInvoiceReportEDIItem.CustomerInvoiceReportEDIItemID is not null ) )
GO
GRANT SELECT ON  [dbo].[EDIInvoiceException] TO [MssExec]
GRANT INSERT ON  [dbo].[EDIInvoiceException] TO [MssExec]
GRANT DELETE ON  [dbo].[EDIInvoiceException] TO [MssExec]
GRANT UPDATE ON  [dbo].[EDIInvoiceException] TO [MssExec]
GO