· 9 years ago · Jan 19, 2017, 10:30 AM
1-- object name : FocusInvoiceExportSage (Stored Procedure)
2-- created on : 07/05/2015 15:05:52
3-- scripted on : 18/01/2017 14:54:37
4-- using server: FRSNETDEV
5-- and database: Dev
6
7SET QUOTED_IDENTIFIER ON
8GO
9SET ANSI_NULLS ON
10GO
11
12if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FocusInvoiceExportSage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
13 return
14exec('create procedure [FocusInvoiceExportSage] as')
15GO
16
17ALTER PROC FocusInvoiceExportSage
18AS
19
20CREATE TABLE #tmp (
21Ref NVARCHAR(2),
22TaxExemptReference NVARCHAR(50),
23Sales NVARCHAR(30),
24DateCreated DATETIME,
25InvoiceID INT,
26NetSellingPrice MONEY,
27TType NVARCHAR(2),
28LineVATAmount MONEY
29)
30
31INSERT INTO #tmp
32SELECT
33 CASE WHEN SUM(inv.[Net Selling Price]) < 0 THEN 'SC' ELSE 'SI' END,
34 MAX(ISNULL(inv.TaxExemptReference, 'Not Set')) AS TaxExemptReference,
35 inv.Sales,
36 MAX(inv.[Invoice Date]),
37 inv.[Invoice ID],
38 SUM(inv.[Net Selling Price]),
39 'T1',
40 SUM(inv.[Line VAT Amount])
41FROM RPT_Focus_Invoice inv
42LEFT JOIN ExportedInvoices ex ON inv.[Invoice ID] = ex.InvoiceID AND ex.InvoiceType = 1
43WHERE ex.ExportedInvoiceID IS NULL
44GROUP BY inv.[Invoice ID], inv.Sales
45
46INSERT INTO ExportedInvoices (InvoiceID, DateSent, InvoiceType)
47SELECT DISTINCT InvoiceID, GETDATE(), 1 FROM #tmp
48
49SELECT * FROM #tmp
50
51
52GO
53SET QUOTED_IDENTIFIER OFF
54GO
55SET ANSI_NULLS ON
56GO
57
58-- object name : FocusInvoiceExportFacflow (Stored Procedure)
59-- created on : 07/05/2015 16:05:08
60-- scripted on : 18/01/2017 14:48:39
61-- using server: FRSNETDEV
62-- and database: Dev
63
64SET QUOTED_IDENTIFIER ON
65GO
66SET ANSI_NULLS ON
67GO
68
69if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FocusInvoiceExportFacflow]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
70 return
71exec('create procedure [FocusInvoiceExportFacflow] as')
72GO
73
74
75ALTER PROC FocusInvoiceExportFacflow
76AS
77
78CREATE TABLE #tmp (
79InvoiceID INT,
80Ref NVARCHAR(15),
81DateCreated DATETIME,
82TaxExemptReference NVARCHAR(50),
83Customer NVARCHAR(90),
84LineSellingPrice MONEY
85)
86
87INSERT INTO #tmp
88SELECT
89 inv.[Invoice ID],
90 'Product Invoice',
91 MAX(inv.[Invoice Date]),
92 MAX(inv.TaxExemptReference),
93 MAX(LTRIM(RTRIM(ISNULL(inv.Title, '')) + ' ' + RTRIM(ISNULL(inv.Initials, '')) + ' ' + RTRIM(ISNULL(inv.CustName, '')))),
94 SUM(inv.[Line Selling Price]),
95 SUM(inv.[Line VAT Amount]),
96 SUM(inv.[Net Selling Price])
97FROM RPT_Focus_Invoice inv
98LEFT JOIN ExportedInvoices ex ON inv.[Invoice ID] = ex.InvoiceID AND ex.InvoiceType = 2
99WHERE ex.ExportedInvoiceID IS NULL
100GROUP BY inv.[Invoice ID]
101
102INSERT INTO ExportedInvoices (InvoiceID, DateSent, InvoiceType)
103SELECT DISTINCT InvoiceID, GETDATE(), 2 FROM #tmp
104
105SELECT * FROM #tmp
106
107
108
109GO
110SET QUOTED_IDENTIFIER OFF
111GO
112SET ANSI_NULLS ON
113GO