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
|
USE [INVOICE_TRACKING]
GO
/****** Object: StoredProcedure [dbo].[InvoicesBySupplierAndDate] Script Date: 03/30/2011 15:00:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[InvoicesBySupplierAndDate]
-- Add the parameters for the stored procedure here
@DateInf as date,
@DateSup as date,
@Supplier as char(15)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT TOP (100) PERCENT dbo.TINVOICE_HEAD.Invoice_Ref, dbo.TINVOICE_HEAD.Invoice_ID, dbo.TINVOICE_HEAD.Invoice_date,
dbo.TINVOICE_HEAD.Received_dte, dbo.TINVOICE_HEAD.Curr, dbo.TINVOICE_HEAD.Invoice_Value, dbo.TINVOICE_HEAD.Code_supp,
dbo.SSRFADD.ADDRESS_1, dbo.SALFLDGSAG.ANAL_T7,dbo.SALFLDGSAG.JRNAL_NO, dbo.SALFLDGSAG.POSTING_DATE
FROM dbo.TINVOICE_HEAD INNER JOIN
dbo.SSRFADD ON dbo.TINVOICE_HEAD.Code_supp = dbo.SSRFADD.ADD_CODE LEFT OUTER JOIN
dbo.SALFLDGSAG ON dbo.TINVOICE_HEAD.Invoice_ID = dbo.SALFLDGSAG.ANAL_T7
WHERE (dbo.TINVOICE_HEAD.Received_dte >= CONVERT(date,@DateInf,101)) AND
(dbo.TINVOICE_HEAD.Received_dte <= CONVERT(date,@DateSup,101))
AND (dbo.TINVOICE_HEAD.Code_supp = @Supplier)
GROUP BY dbo.TINVOICE_HEAD.Invoice_Ref, dbo.TINVOICE_HEAD.Invoice_ID, dbo.TINVOICE_HEAD.Invoice_date, dbo.TINVOICE_HEAD.Received_dte,
dbo.TINVOICE_HEAD.Curr, dbo.TINVOICE_HEAD.Invoice_Value, dbo.TINVOICE_HEAD.Code_supp, dbo.SSRFADD.ADDRESS_1,
dbo.SALFLDGSAG.ANAL_T7, dbo.SALFLDGSAG.JRNAL_NO, dbo.SALFLDGSAG.POSTING_DATE
ORDER BY dbo.TINVOICE_HEAD.Received_dte DESC
END |
