|
1
|
ALTER PROCEDURE [dbo].[PRPTINVDOCDETAILN]
|
|
2
|
(
|
|
3
|
@DocDateFrom VARCHAR(20)
|
|
4
|
,@DocDateTo VARCHAR(20)
|
|
5
|
,@DocStatus VARCHAR(20)
|
|
6
|
,@DocNo VARCHAR(20)
|
|
7
|
,@DocType VARCHAR(20)
|
|
8
|
)
|
|
9
|
AS
|
|
10
|
|
|
11
|
--DECLARE @DocDateFrom VARCHAR(20) = ''
|
|
12
|
--DECLARE @DocDateTo VARCHAR(20) = ''
|
|
13
|
--DECLARE @DocStatus VARCHAR(20) = ''
|
|
14
|
--DECLARE @DocNo VARCHAR(20) = '00000800'
|
|
15
|
--DECLARE @DocType VARCHAR(20) = 'GICU'
|
|
16
|
|
|
17
|
IF (@DocDateFrom <> '') BEGIN SET @DocDateFrom = @DocDateFrom END ELSE BEGIN SET @DocDateFrom = '19000101' END
|
|
18
|
IF (@DocDateTo <> '') BEGIN SET @DocDateTo = @DocDateTo END ELSE BEGIN SET @DocDateTo = '99991231' END
|
|
19
|
|
|
20
|
DECLARE @DefCompIDDesc VARCHAR(250) = (SELECT TOP(1) CompanyDescription FROM dbo.PCMEPCOMPID)
|
|
21
|
|
|
22
|
SELECT DISTINCT head.DocNo
|
|
23
|
,dbo.fn_formatdatetime(head.DocDate, 'dd/mm/yyyy') AS DocDate
|
|
24
|
,head.DocType
|
|
25
|
,dt.DocTypeDesc
|
|
26
|
,head.NoResi
|
|
27
|
,head.Description
|
|
28
|
,head.CompanyID
|
|
29
|
,CASE WHEN comp.CompanyDescription <> '' THEN comp.CompanyDescription ELSE @DefCompIDDesc END CompanyDescription
|
|
30
|
,item.MaterialID
|
|
31
|
,mat.Description AS MaterialDesc
|
|
32
|
,mat.ExternalID
|
|
33
|
,item.Quantity
|
|
34
|
,item.Warehouse
|
|
35
|
,wh.Description AS WarehouseDesc
|
|
36
|
FROM dbo.PTRMATERIALHEAD AS head
|
|
37
|
INNER JOIN dbo.PTRMATERIALITEM AS item
|
|
38
|
ON head.DocNo = item.DocNo
|
|
39
|
LEFT JOIN dbo.PMDMAT0001 AS mat
|
|
40
|
ON item.MaterialID = mat.MaterialID
|
|
41
|
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
|
|
42
|
ON head.DocType = dt.DocType
|
|
43
|
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
|
|
44
|
ON item.Warehouse = wh.Warehouse
|
|
45
|
LEFT JOIN dbo.PCMEPCOMPID AS comp
|
|
46
|
ON head.CompanyID = comp.CompanyID
|
|
47
|
WHERE (head.DocType = @DocType OR @DocType = '')
|
|
48
|
AND (head.DocDate >= @DocDateFrom AND head.DocDate <= @DocDateTO)
|
|
49
|
AND (head.DocStatus = @DocStatus OR @DocStatus = '')
|
|
50
|
AND (head.DocNo = @DocNo OR @DocNo = '')
|
