GPSOPTrxHist – Dynamics 365 BC Table Reference

Dynamics 365 BC Table Reference Tables Company Tables GGPSOPTrxHist

Easily find this page again using the shortlink: https://bct.azrcrv.co.uk/4078

Table Type: Company

Table Name: GPSOPTrxHist
Database Table Name:

Table Number: 4078

Due to how Dynamics BC tables and columns are named, square brackets need to be wrapped around the table name and column names. Below are example queries showing how GPSOPTrxHist can be queried.

Show/hide an example select of all columns

SELECT

   *

FROM

   []

Show/hide an example select of all columns by name

SELECT



FROM

   []

Show/hide an example select of all columns including those from all joined tables

SELECT
   *
FROM
   [] AS [GPSOPTrxHist]
LEFT JOIN
   [CRONUS UK Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Customer]
      ON
         [GPSOPTrxHist].[CUSTNMBR] = [Customer].[No_]
LEFT JOIN
   [CRONUS UK Ltd_$Post Code$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Post Code]
      ON
         [GPSOPTrxHist].[City] = [Post Code].[City]
LEFT JOIN
   [User] AS [User]
      ON
         [GPSOPTrxHist].[SystemCreatedBy] = [User].[User Security ID]
LEFT JOIN
   [User] AS [User]
      ON
         [GPSOPTrxHist].[SystemModifiedBy] = [User].[User Security ID]

Show/hide columns in GPSOPTrxHist table

Field No.	Field Name	Field Caption	Class	Type	Length
1	SOPTYPE	SOP Type	Normal	Option	4
2	SOPNUMBE	SOP Number	Normal	Text	22
3	ORIGTYPE	Original Type	Normal	Option	4
4	ORIGNUMB	Original Number	Normal	Text	22
5	DOCID	Document ID	Normal	Text	16
6	DOCDATE	Document Date	Normal	Date	4
7	GLPOSTDT	GL Posting Date	Normal	Date	4
8	QUOTEDAT	Quote Date	Normal	Date	4
9	QUOEXPDA	Quote Expiration Date	Normal	Date	4
10	ORDRDATE	Order Date	Normal	Date	4
11	INVODATE	Invoice Date	Normal	Date	4
12	BACKDATE	Back Order Date	Normal	Date	4
13	RETUDATE	Return Date	Normal	Date	4
14	ReqShipDate	Requested Ship Date	Normal	Date	4
15	FUFILDAT	Fulfillment Date	Normal	Date	4
16	ACTLSHIP	Actual Ship Date	Normal	Date	4
17	DISCDATE	Discount Date	Normal	Date	4
18	DUEDATE	Due Date	Normal	Date	4
19	REPTING	Repeating	Normal	Boolean	4
20	TRXFREQU	TRX Frequency	Normal	Option	4
21	TIMEREPD	Times Repeated	Normal	Integer	4
22	TIMETREP	Times To Repeat	Normal	Integer	4
23	DYSTINCR	Days to Increment	Normal	Integer	4
24	DTLSTREP	Date Last Repeated	Normal	Date	4
25	DSTBTCH1	Dest Batch 1	Normal	Text	16
26	DSTBTCH2	Dest Batch 2	Normal	Text	16
27	USDOCID1	Use Document ID 1	Normal	Text	16
28	USDOCID2	Use Document ID 2	Normal	Text	16
29	DISCFRGT	Discount Available Freight	Normal	Decimal	12
30	ORDAVFRT	Originating Discount Available Freight	Normal	Decimal	12
31	DISCMISC	Discount Available Misc	Normal	Decimal	12
32	ORDAVMSC	Originating Discount Available Misc	Normal	Decimal	12
33	DISAVAMT	Discount Available Amount	Normal	Decimal	12
34	ORDAVAMT	Originating Discount Available Amount	Normal	Decimal	12
35	DISCRTND	Discount Returned	Normal	Decimal	12
36	ORDISRTD	Originating Discount Returned	Normal	Decimal	12
37	DISTKNAM	Discount Taken Amount	Normal	Decimal	12
38	ORDISTKN	Originating Discount Taken Amount	Normal	Decimal	12
39	DSCPCTAM	Discount Percent Amount	Normal	Integer	4
40	DSCDLRAM	Discount Dollar Amount	Normal	Decimal	12
41	ORDDLRAT	Originating Discount Dollar Amount	Normal	Decimal	12
42	DISAVTKN	Discount Available Taken	Normal	Decimal	12
43	ORDATKN	Originating Discount Available Taken	Normal	Decimal	12
44	PYMTRMID	Payment Terms ID	Normal	Text	22
45	PRCLEVEL	PriceLevel	Normal	Text	12
46	LOCNCODE	Location Code	Normal	Text	12
47	BCHSOURC	Batch Source	Normal	Text	16
48	BACHNUMB	Batch Number	Normal	Text	16
49	CUSTNMBR	Customer Number	Normal	Text	16
	Key to join to the Customer table. Show/hide example query SELECT [GPSOPTrxHist].[] ,[Customer].[No_] FROM [] AS [GPSOPTrxHist] LEFT JOIN [CRONUS UK Ltd_$Customer$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Customer] ON [GPSOPTrxHist].[] = [Customer].[No_]
50	CUSTNAME	Customer Name	Normal	Text	66
51	CSTPONBR	Customer PO Number	Normal	Text	22
52	PROSPECT	Prospect	Normal	Integer	4
53	MSTRNUMB	Master Number	Normal	Integer	4
54	PCKSLPNO	Packing Slip Number	Normal	Text	22
55	PICTICNU	Picking Ticket Number	Normal	Text	22
56	MRKDNAMT	Markdown Amount	Normal	Decimal	12
57	ORMRKDAM	Originating Markdown Amount	Normal	Decimal	12
58	PRBTADCD	Primary Billto Address Code	Normal	Text	16
59	PRSTADCD	Primary Shipto Address Code	Normal	Text	16
60	CNTCPRSN	Contact Person	Normal	Text	62
61	ShipToName	ShipToName	Normal	Text	66
62	ADDRESS1	Address 1	Normal	Text	62
63	ADDRESS2	Address 2	Normal	Text	62
64	ADDRESS3	Address 3	Normal	Text	62
65	CITY	City	Normal	Text	36
	Key to join to the Post Code table. Show/hide example query SELECT [GPSOPTrxHist].[] ,[Post Code].[City] FROM [] AS [GPSOPTrxHist] LEFT JOIN [CRONUS UK Ltd_$Post Code$437dbf0e-84ff-417a-965d-ed2bb9650972] AS [Post Code] ON [GPSOPTrxHist].[] = [Post Code].[City]
66	STATE	State	Normal	Text	30
67	ZIPCODE	Zip Code	Normal	Text	12
68	CCode	Country Code	Normal	Text	8
69	COUNTRY	Country	Normal	Text	62
70	PHNUMBR1	Phone Number 1	Normal	Text	22
71	PHNUMBR2	Phone Number 2	Normal	Text	22
72	PHONE3	Phone 3	Normal	Text	22
73	FAXNUMBR	Fax Number	Normal	Text	22
74	COMAPPTO	Commission Applied To	Normal	Option	4
75	COMMAMNT	Commission Amount	Normal	Decimal	12
76	OCOMMAMT	Originating Commission Amount	Normal	Decimal	12
77	CMMSLAMT	Commission Sale Amount	Normal	Decimal	12
78	ORCOSAMT	Originating Commission Sales Amount	Normal	Decimal	12
79	NCOMAMNT	Non-Commissioned Amount	Normal	Decimal	12
80	ORNCMAMT	Originating Non-Commissioned Amount	Normal	Decimal	12
81	SHIPMTHD	Shipping Method	Normal	Text	16
82	TRDISAMT	Trade Discount Amount	Normal	Decimal	12
83	ORTDISAM	Originating Trade Discount Amount	Normal	Decimal	12
84	TRDISPCT	Trade Discount Percent	Normal	Integer	4
85	SUBTOTAL	Subtotal	Normal	Decimal	12
86	ORSUBTOT	Originating Subtotal	Normal	Decimal	12
87	REMSUBTO	Remaining Subtotal	Normal	Decimal	12
88	OREMSUBT	Originating Remaining Subtotal	Normal	Decimal	12
89	EXTDCOST	Extended Cost	Normal	Decimal	12
90	OREXTCST	Originating Extended Cost	Normal	Decimal	12
91	FRTAMNT	Freight Amount	Normal	Decimal	12
92	ORFRTAMT	Originating Freight Amount	Normal	Decimal	12
93	MISCAMNT	Misc Amount	Normal	Decimal	12
94	ORMISCAMT	Originating Misc Amount	Normal	Decimal	12
95	TXENGCLD	Tax Engine Called	Normal	Boolean	4
96	TAXEXMT1	Tax Exempt 1	Normal	Text	26
97	TAXEXMT2	Tax Exempt 2	Normal	Text	26
98	TXRGNNUM	Tax Registration Number	Normal	Text	26
99	TAXSCHID	Tax Schedule ID	Normal	Text	16
100	TXSCHSRC	Tax Schedule Source	Normal	Option	4
101	BSIVCTTL	Based On Invoice Total	Normal	Boolean	4
102	FRTSCHID	Freight Schedule ID	Normal	Text	16
103	FRTTXAMT	Freight Tax Amount	Normal	Decimal	12
104	ORFRTTAX	Originating Freight Tax Amount	Normal	Decimal	12
105	FRGTTXBL	Freight Taxable	Normal	Option	4
106	MSCSCHID	Misc Schedule ID	Normal	Text	16
107	MSCTXAMT	Misc Tax Amount	Normal	Decimal	12
108	ORMSCTAX	Originating Misc Tax Amount	Normal	Decimal	12
109	MISCTXBL	Misc Taxable	Normal	Option	4
110	BKTFRTAM	Backout Freight Amount	Normal	Decimal	12
111	ORBKTFRT	Originating Backout Freight Amount	Normal	Decimal	12
112	BKTMSCAM	Backout Misc Amount	Normal	Decimal	12
113	ORBKTMSC	Originating Backout Misc Amount	Normal	Decimal	12
114	BCKTXAMT	Backout Tax Amount	Normal	Decimal	12
115	OBTAXAMT	Originating Backout Tax Amount	Normal	Decimal	12
116	TXBTXAMT	Taxable Tax Amount	Normal	Decimal	12
117	OTAXTAMT	Originating Taxable Tax Amount	Normal	Decimal	12
118	TAXAMNT	Tax Amount	Normal	Decimal	12
119	ORTAXAMT	Originating Tax Amount	Normal	Decimal	12
120	ECTRX	EC Transaction	Normal	Boolean	4
121	DOCAMNT	Document Amount	Normal	Decimal	12
122	ORDOCAMT	Originating Document Amount	Normal	Decimal	12
123	PYMTRCVD	Payment Received	Normal	Decimal	12
124	ORPMTRVD	Originating Payment Received	Normal	Decimal	12
125	DEPRECVD	Deposit Received	Normal	Decimal	12
126	ORDEPRVD	Originating Deposit Received	Normal	Decimal	12
127	CODAMNT	COD Amount	Normal	Decimal	12
128	ORCODAMT	Originating COD Amount	Normal	Decimal	12
129	ACCTAMNT	Account Amount	Normal	Decimal	12
130	ORACTAMT	Originating Account Amount	Normal	Decimal	12
131	SALSTERR	Sales Territory	Normal	Text	16
132	SLPRSNID	Salesperson ID	Normal	Text	16
133	UPSZONE	UPS Zone	Normal	Text	4
134	TIMESPRT	Times Printed	Normal	Integer	4
135	PSTGSTUS	Posting Status	Normal	Integer	4
136	VOIDSTTS	Void Status	Normal	Option	4
137	ALLOCABY	Allocate By	Normal	Option	4
138	NOTEINDX	Note Index	Normal	Decimal	12
139	CURNCYID	Currency ID	Normal	Text	16
140	CURRNIDX	Currency Index	Normal	Integer	4
141	RATETPID	Rate Type ID	Normal	Text	16
142	EXGTBLID	Exchange Table ID	Normal	Text	16
143	XCHGRATE	Exchange Rate	Normal	Decimal	12
144	DENXRATE	Denomination Exchange Rate	Normal	Decimal	12
145	EXCHDATE	Exchange Date	Normal	Date	4
146	TIME1	Time	Normal	DateTime	8
147	RTCLCMTD	Rate Calculation Method	Normal	Option	4
148	MCTRXSTT	MC Transaction State	Normal	Option	4
149	TRXSORCE	TRX Source	Normal	Text	14
150	COMMNTID	Comment ID	Normal	Text	16
151	REFRENCE	Reference	Normal	Text	32
152	POSTEDDT	Posted Date	Normal	Date	4
153	PTDUSRID	Posted User ID	Normal	Text	16
154	USER2ENT	User To Enter	Normal	Text	16
155	CREATDDT	Created Date	Normal	Date	4
156	MODIFDT	Modified Date	Normal	Date	4
157	Tax_Date	Tax Date	Normal	Date	4
158	APLYWITH	Apply Withholding	Normal	Boolean	4
159	WITHHAMT	Withholding Amount	Normal	Decimal	12
160	SHPPGDOC	Shipping Document	Normal	Boolean	4
161	CORRCTN	Correction	Normal	Boolean	4
162	SIMPLIFD	Simplified	Normal	Boolean	4
163	DOCNCORR	Document Number Corrected	Normal	Text	22
164	SEQNCORR	Sequence Number Corrected	Normal	Integer	4
165	SALEDATE	Sale Date	Normal	Date	4
166	EXCEPTIONALDEMAND	Exceptional Demand	Normal	Boolean	4
167	Flags	Flags	Normal	Integer	4
168	SOPSTATUS	SOP Status	Normal	Option	4
169	SHIPCOMPLETE	Ship Complete Document	Normal	Boolean	4
170	DIRECTDEBIT	Direct Debit	Normal	Boolean	4
171	WorkflowApprStatCreditLm	Workflow Approval Status Credit Limit	Normal	Option	4
172	WorkflowPriorityCreditLm	Workflow Priority Credit Limit	Normal	Option	4
173	WorkflowApprStatusQuote	Workflow Approval Status Quote	Normal	Option	4
174	WorkflowPriorityQuote	Workflow Priority Quote	Normal	Option	4
175	Workflow_Status	Workflow Status	Normal	Option	4
176	ContractExchangeRateStat	Contract Exchange Rate Status	Normal	Integer	4
177	Print_Phone_NumberGB	Print Phone Number GB	Normal	Option	4
178	DEX_ROW_TS	DEX_ROW_TS	Normal	DateTime	8
179	DEX_ROW_ID	DEX_ROW_ID	Normal	Integer	4
2000000000	$systemId	System ID	Normal	GUID	16
2000000001	SystemCreatedAt	Created At	Normal	DateTime	8
2000000002	SystemCreatedBy	Created By	Normal	GUID	16
	Key to join to the User table. Show/hide example query SELECT [GPSOPTrxHist].[] ,[User].[User Security ID] FROM [] AS [GPSOPTrxHist] LEFT JOIN [User] AS [User] ON [GPSOPTrxHist].[] = [User].[User Security ID]
2000000003	SystemModifiedAt	Modified At	Normal	DateTime	8
2000000004	SystemModifiedBy	Modified By	Normal	GUID	16