Project

General

Profile

Download (4.19 KB)

Bug #3796 » SPERPRPTSALESDOCDETAIL_20251020.sql

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

 
1 
??USE [MinovaES_DEMO_GolfCourse]
2
3 
GO
4
5 
/****** Object:  StoredProcedure [dbo].[SPERPRPTSALESDOCDETAIL]    Script Date: 20/10/2025 14.34.27 ******/
6
7 
SET ANSI_NULLS ON
8
9 
GO
10
11 
SET QUOTED_IDENTIFIER ON
12
13 
GO
14
15 
ALTER procedure [dbo].[SPERPRPTSALESDOCDETAIL]
16
17
18
19 
 @DocNo varchar(8),
20
21 
 @DocDate varchar (8),
22
23 
 @DocType varchar (8)
24
25
26
27 
 as
28
29
30
31 
 begin
32
33
34
35 
--declare @DocNo varchar(8) =''
36
37 
--declare @DocDate varchar (8) =''
38
39 
--declare @DocType varchar (8) =''
40
41
42
43 
select distinct salesitems.DocNo
44
45 
,header.DocDate
46
47 
,header.DocType
48
49 
,doctype.DocTypeDesc as DocTypeDesc
50
51 
,salesitems.ItemType as ServiceType
52
53 
,_itemtype.[Description] as ServiceTypeDesc
54
55 
,case
56
57 
	when _itemtype.ItemType =0 then mat01.ExternalID
58
59 
	when _itemtype.ItemType =1 then v01.ExternalID
60
61 
	when _itemtype.ItemType =2 then ast01.AssetType end as ServiceID
62
63 
--,mat01.Description as ServiceMaterialAssetDesc
64
65 
,case
66
67 
	when salesitems.ItemType =0 then mat01.[Description]
68
69 
	--when salesitems.ItemType =1 then srv01.[Description]
70
71 
	when salesitems.ItemType =2 then ast01.AssetName
72
73 
	end as ServiceDesc
74
75 
,salesitems.PriceAmount
76
77 
,salesitems.QuantityMaterial
78
79 
,case
80
81 
	when salesitems.QuantityMaterial ='' then '1'
82
83 
	when salesitems.QuantityMaterial is null then '1'
84
85 
	else salesitems.QuantityMaterial
86
87 
	end as Quantity
88
89 
,salesitems.QuantityTransaction
90
91 
,salesitems.Discount
92
93 
,salesitems.NetAmount
94
95 
,salesitems.NetAmount*0.11 as TaxAmount
96
97 
,salesitems.TotAmount
98
99 
,salesitems.Currency
100
101
102
103 
from PTRSALESITEMS salesitems
104
105 
full outer join PTRSALESHEADER header
106
107 
	on salesitems.DocNo = header.DocNo
108
109 
left join PCMBSDOCTYPE doctype
110
111 
	on header.DocType = doctype.DocType
112
113 
left join PCMLOGITEMTYPE _itemtype
114
115 
	on salesitems.ItemType = _itemtype.ItemType
116
117 
left join PMDMAT0001 mat01
118
119 
	on salesitems.Material = mat01.MaterialID
120
121 
left join PMDSRV0001 srv01
122
123 
	on salesitems.Material = srv01.ServiceID
124
125 
left join PMDAST0001 ast01
126
127 
	on salesitems.Material = ast01.AssetCode
128
129 
left join PMDSRV0001 v01
130
131 
	on salesitems.Material = v01.ServiceID
132
133
134
135
136
137 
where
138
139 
	(salesitems.DocNo = @DocNo or @DocNo='')
140
141 
	and
142
143 
	(header.DocDate =@DocDate or @DocDate='')
144
145 
	 and
146
147 
	 (header.DocType = @DocType or @DocType ='')
148
149 
end
150
151
(7-7/9)