Project

General

Profile

Feature #3869 » PRPTPUDOCRECAP_20251020.sql

Tri Rizqiaty, 10/20/2025 03:19 PM

 
1
??USE [MinovaES_Tulisan_Dev]
2

3
GO
4

5
/****** Object:  StoredProcedure [dbo].[PRPTPUDOCRECAP]    Script Date: 20/10/2025 15.13.28 ******/
6

7
SET ANSI_NULLS ON
8

9
GO
10

11
SET QUOTED_IDENTIFIER ON
12

13
GO
14

15
ALTER PROCEDURE [dbo].[PRPTPUDOCRECAP]
16

17
(
18

19
	@CompanyID VARCHAR(20)
20

21
	,@BussinessUnit VARCHAR(20)
22

23
	,@DocDateStart VARCHAR(20)
24

25
	,@DocDateEnd VARCHAR(20)
26

27
	,@DocStatus VARCHAR(20)
28

29
	,@DocType VARCHAR (20)
30

31
)
32

33
AS
34

35

36

37
--DECLARE @CompanyID VARCHAR(20) = '1000'
38

39
--DECLARE @BussinessUnit VARCHAR(20) = ''
40

41
--DECLARE @DocDateStart VARCHAR(20) = '20240301'
42

43
--DECLARE @DocDateEnd VARCHAR(20) = '20240331'
44

45
--DECLARE @DocStatus VARCHAR(20) = ''
46

47
--DECLARE @DocType VARCHAR(20) = 'PO'
48

49

50

51
IF (@DocDateStart <> '') BEGIN SET @DocDateStart = @DocDateStart END ELSE BEGIN SET @DocDateStart = '19000101' END
52

53
IF (@DocDateEnd <> '') BEGIN SET @DocDateEnd = @DocDateEnd END ELSE BEGIN SET @DocDateEnd = '99991231' END
54

55

56

57

58

59
DECLARE @nowyear VARCHAR (4) = dbo.fn_formatdatetime(GETDATE(), 'yyyy')
60

61

62

63
SELECT distinct head.DocNo, SUM(ISNULL(item.NetAmount,0)) as NetAmount 
64

65
into #NetAmount
66

67
FROM PTRPURCHHEAD  head
68

69
left join PTRPURCHITEM item on head.DocNo=item.DocNo
70

71
where head.DocType=@DocType 
72

73
	and (head.DocStatus=@DocStatus OR @DocStatus ='')
74

75
	--and left(head.DocDate,4)=@nowyear 
76

77
	and head.DocDate BETWEEN @DocDateStart and @DocDateEnd
78

79
group by head.DocNo
80

81

82

83
SELECT DISTINCT comp.CompanyDescription
84

85
				,'PURCHASE RECAP' as Title
86

87
				,sh.* 
88

89
				,dt.DocTypeDesc
90

91
				,bus.Description AS budesc
92

93
				,wh.Description AS whdesc
94

95
				,dbo.fn_formatdatetime(sh.DocDate, 'dd/mm/yyyy') AS docdatedesc
96

97
				,ds.Description AS docstatdesc
98

99
				,ven.Description AS vendordesc
100

101
				,dbo.fn_formatdatetime(sh.InvoiceDate, 'dd/mm/yyyy') AS invoicedatedesc
102

103
				,COALESCE(NetAmount.NetAmount,0)  as NetAmount
104

105
FROM dbo.PTRPURCHHEAD AS sh
106

107
LEFT JOIN #NetAmount NetAmount on sh.DocNo=NetAmount.DocNo
108

109
LEFT JOIN dbo.PCMBSDOCTYPE AS dt
110

111
	ON sh.DocType = dt.DocType
112

113
LEFT JOIN dbo.PCMEPCOMPID AS comp
114

115
	ON comp.CompanyID = sh.CompanyID
116

117
LEFT JOIN dbo.PCMFILOBUSUNIT AS bus	
118

119
	ON bus.BuCode = sh.BusinessUnit
120

121
LEFT JOIN dbo.PCMFILOWAREHOUSE AS wh
122

123
	ON sh.Warehouse = wh.Warehouse
124

125
LEFT JOIN dbo.PCMBSPURCHASEDOCSTATUS AS ds
126

127
	ON sh.DocStatus = ds.DocStatus
128

129
LEFT JOIN dbo.PMDVEN0001 AS ven
130

131
	ON ven.VendorID = sh.VendorID
132

133
WHERE (sh.CompanyID = @CompanyID OR @CompanyID = '')
134

135
	AND (sh.BusinessUnit = @BussinessUnit OR @BussinessUnit = '')
136

137
	AND (sh.DocStatus = @DocStatus OR @DocStatus = '')
138

139
	AND (sh.DocDate BETWEEN @DocDateStart AND @DocDateEnd)
140

141
	AND (sh.DocType= @DocType or @DocType='')
142

143
	----select * from PCMBSPURCHASEDOCSTATUS
144

145

146

147
	drop table #NetAmount
(3-3/5)