DB QUERIES

Created by Dawn Stevens, Modified on Fri, 8 Mar, 2024 at 3:36 PM by Dawn Stevens

ABOUT: This article has a list of all the database queries that can be used to help analyze failed operations including fixing data from the backend. They can be copied and pasted. These queries are for SQL Lite Manager software only on Wholesome Sweeteners EDI system. This software is able to make changes directly to the table via the "select" query result without an "update" command. When using the queries, one must be very careful. If you accidentally changed a field and didn't hit enter yet, you can hit your escape button, and it puts back the data "as was".

The queries will be organized by groups: SO Queries, Invoice Queries, Customer Queries, Inventory Queries, Warehouse Queries, Trigger Queries, Turn Around Table (TAT) Queries, Shipment (204) Queries, Amazon Invoice Fix Queries.

TRIGGER QUERIES:

ABOUT: These queries are used to reprocess outbound documents. The main field to change is EOD_Proc to "null".

FILTER EXAMPLE IN MAP: where EOD_DocType = '810' and EOD_Customer in ('WAL0100') and EOD_Proc is null

1. The "trigger" process begins with EOD_Proc set to null where the Filter in map is looking for this condition.

2. During the transformation process, the DB Source Read script will mark this field to 'N' in beginning then when it creates the envelope during the Post Process phase, it will mark it to 'Y'. Any document left with a 'N' in this field means the post enveloping process was never done, and there was a transformation error that needs to be viewed and fixed.

3. The Post Process phase will not be initiated if any transformation errors (as above) leaving the failed with a 'N' in the EOD_Proc field. Post Process to 'Y' indicates this record was sent to Biz successfully via transformation to send out via the communications. Any "communication errors" will be in Bizmanager Failed Ops and separate of this process.

This is the main query below to view all those documents that have failed in the outbound operation regardless of document type. This can be used and the main script to right-click and change any document back to null or 'X' (just resolved - no need to send and to be ignored).

Select * from WS_EdiOutboundDoc where EOD_Proc = 'N' order by EOD_DocKey DESC

In SQL Lite, you can run the above script, right-click on EOD_Proc field and "Set Value"-"Set to NULL" (screen shot below).

You can do this for many fields at once too by clicking on top line then holding the shift key and clicking on bottom line, then right-click on one of the EOD_Proc field and set to null (screen shot 2 below).

Setting many to null using Shift key.

You can also click on field and click again and manually change the value to Y or X (ignore) with keyboard. For setting to null, you must right-click and "set to null". Remember, if you hit the escape key before hitting enter, it will do an "undo".

SALES ORDER QUERIES:

ABOUT: These queries are used to either look up data on orders in Syspro or to fix fields like addresses or missing customer names for outbound 940, 943, etc. See Invoice Queries for 810s.

ERROR EXAMPLE: For each ST[1].N1_ST[1] at least one of the following must exist: N102, N103

Remember to retrigger any outbounds after fixing them using the Trigger Table Query instructions at beginning of this knowledgebase article.

To Find a SO# for a given PO#: Note: Once you look this up, you can grab the SO# to get the detail line.

Select * from SorMaster where CustomerPoNumber = '38B1NBDC'

To Look and Fix a SO using SO# - Header:

Select * from SorMaster where SalesOrder = '000000000264207'

To Look at SO Detail using SO#:
Select * from SorDetail where SalesOrder = '000000000264207'

INVOICE QUERIES:

ABOUT: After being invoiced (Order Status = 9), the invoices move to the SorMasterRep & SorDetailRep tables. The maps (810/880) use these tables for data. Anything that needs to be fixed in the invoice can be fixed on these tables.

ERROR EXAMPLE: There must be a minimum of 1 instance(s) of ST.N1_ST.N3.N301 - 0 instance(s) found

Remember to retrigger any outbounds after fixing them using the Trigger Table Query instructions at beginning of this knowledgebase article.

To Find a Invoice# for a given PO#: Note: Once you look this up, you can grab the SO# or Invoice # to get the detail line.

Select * from SorMasterRep where CustomerPoNumber = '38B1NBDC'

To Look and Fix an Invoice using Inv# - Header:

Select * from SorMasterRep where InvoiceNumber= '000000000230907'

To Look at Inv Detail using Inv#:
Select * from SorDetailRep where Invoice = '000000000230907'

To Look and Fix an Invoice using SO# - Header:

Select * from SorMasterRep where SalesOrder= '000000000228961'

To Look at Inv Detail using SO#:
Select * from SorDetailRep where SalesOrder = '000000000228961'

ABOUT: Below are queries to view Invoice Total Amounts. The map looks for InvoiceBal1 and if not there, it grabs InvoiceBal2. Everything moves to the different fields based off of when this invoice was closed. Eventually, it will not be in any of the InvoiceBal fields. DO NOT MODIFY THESE IN THE TABLES. IF NEED TO GET TOTAL ON OLD INVOICE, CHANGE MAP TO DO FORMULA total(IT102*IT104) AND THEN PUT IT BACK. You can just comment out the other formula by putting in a // in front of it.

To Look at Invoice Totals using Inv#:

Select * from ArInvoice where Invoice= '000000000229953'

To Look at Invoice Totals using SO#:

Select * from ArInvoice where SalesOrder= '000000000131861'

ABOUT: Below are queries to view Invoice Discount Amounts for Summary SACs. Walmart is an example where special SACs are taking out. This table is ArTrnDetail. Otherwise, follow procedures on SAC in other maps

To Look at Invoice Discounts using Inv#:

SELECT SUM(DiscValue) FROM ArTrnDetail where Invoice ='000000000149095'

ABOUT: Below are queries to fix Amazon Invoices. Amazon invoicing is unique due to line numbers being added to the SO tables via the Inbound PO XML on the UserDef field, and it compares it to the "turn around tables" to match all information for the 810 to send back. Sometimes these are missing and required to send back invoice correct. See knowledgebase article on Amazon Invoice Troubleshooting.

ERROR EXAMPLE: UPC for Amazon Invoice Number 000000000230775 is Missing for Stock Code 44652

To Look at Detail line of the Stock Code and the Line Numbers by using SO# on SO tables:

Select MStockCode, MUserDef from SorDetail where SalesOrder = '000000000229633'

To Look at Detail line of the Stock Code and the Line Numbers by using Inv# on Invoice tables:

select StockCode, UserDef from SorDetailRep where Invoice = '000000000231016'

You can use script below to fix the Amazon invoice.

-----Script to write the line numbers to Invoice UserDef-----

update a

Set a.UserDef = c.LINE_NO

from SorDetailRep AS a

inner join SorMasterRep AS b

on a.Invoice = b.InvoiceNumber

inner join WS_EdiCustomerPO_Details c

on b.CustomerPoNumber = c.PurchaseOrder

where a.StockCode = c.WSI_StockCode

and a.Invoice = '000000000230775'

CUSTOMER QUERIES:

ABOUT: These queries are used to look up Customer Account information like Ship To Addresses, Location IDs, etc. Inbound POs use the ShipToAddr2 field to look up N104 and insert back what the customer number is in inbound XML to Syspro. Sometimes the N104 is missing on the outbound 810, 855, etc which would be in the SorMaster ShipAddress2 field. If you need to look it up and fix the SorMaster or SorMasterRep table, you can grab it and copy it from here.

ERROR EXAMPLE: For each ST[1].N1_ST either all or none of the following must exist: N103, N104

Remember to retrigger any outbounds after fixing them using the Trigger Table Query instructions at beginning of this knowledgebase article. If an inbound error, one may just reprocess the operation in Failed Ops.

To view the customer, get the customer number from the SorMaster or SorMasterRep:

select * from ArCustomer where Customer = 'TRA0100'

To Look up via ship location code:

select * from ArCustomer where ShipToAddr2= '0098263148601'

ABOUT: The ArCustomer+ table are used for all customer notes that go out on the 940 and 943s. This table is also used in programs to execute EDI triggering. Those fields are to have a 'Y' if ASN, 855 and 810 are used amongst autoinvoicing, autoreleasing, and more.

To Look up via customer:

select * from [ArCustomer+] where Customer = 'KEH0101'

INVENTORY QUERIES:

ABOUT: These queries are used to either look up Customer Item Numbers or Wholesome's item number and/or UPC codes. There are three different tables. The ArCustStkXref is the customrer cross reference tables, the [InvMaster+] are the UPC codes, and the InvMaster is the normal inventory table.

ERROR EXAMPLE: Customer Part Number 266531 is Missing in Customer Part Number Cross Reference Table for Kroger (KRO0100) PO# 12345

To look up Customer Cross Reference Table, you grab the Customer number in the error message (KRO0100) and plug it in below script:

select * from ArCustStkXref where Customer = 'KRO0100'

Note: For Inbound, the CustStockCode is looked up and returns the StockCode in the XML. In the Outbound, the StockCode is looked up and it returns the CustStockCode for the EDI.

ERROR EXAMPLE: UPC12551 10099482446120 is Missing in Inventory Master for LIDL PO# 12345

UPC Error Messages will give the actual UPC field the map is looking for. For example, UPC1551, UPC255, UPC155, UPC12551. The above example uses UPC12551. The one exception is Amazon because it looks up several different fields in that table if one is not there (Amazon only says "UPC").
To look up UPC Codes, you can search with (*) for all the fields to view.
Note: This error will be for any inbound and outbound that uses the UPCs. Look at the Source of where the file came from, for example, DBR - Invoices, etc.

select * from [InvMaster+] where StockCode = '55167'

Below are the UPC codes, and they are in all the fields. This error example is fixed already but the UpcCase12551 would have been missing. However, for Amazon errors , see the other Amazon Invoice Error Fix under the Invoice Tables.

ERROR EXAMPLE: Stock Code 12345 is On Hold for UNFI PO# 456977

Stock Code 67890 is On Partial Hold for UNFI PO# 124785

ABOUT: Inbound orders will be rejected to determine full and partial holds on stock codes. Syspro will not allow them, so they are rejected before they get to Syspro. Customer Service will have to get the holds removed or have the EDI support remove the entire item lines from the order before reprocessing. The above two errors could be seen.

These queries below are the Inventory Master tables where you can not only look up holds, but any other information related to the item. For example, UOM conversions, descriptions, packs, etc.

To look up Stock On Hold. A full hold is marked by "F" in the below field. Partial is "P". If either value is present, it will determine the error message to state "full" vs "partial".

select StockOnHold from InvMaster where StockCode = '20220'

To look up the entire Inventory Master Table, change the Select to (*).

select * from InvMaster where StockCode = '30010'

WAREHOUSE QUERIES:

ABOUT: These queries are used to look up Warehouses. Warehouses are looked up and determined by most maps in the below process:

1. The SalesWarehouse in the ArCustomer (Customer Master) Table is first looked up. If there is a warehouse code, it will then review the quantity that is on hand. If there is quantity, the map will choose this warehouse code first.

2. If the above SalesWarehouse is blank or the quantity on hand is below amount needed, the DistWarehouseToUse in the InvMaster (Inventory Master) table is used.

3. If the DistWarehouseToUse is blank, then the WatehouseToUse is taken from the InvMaster table.

4. If all the above are blank, the following error will be generated.

ERROR EXAMPLE: Amazon PO 6VGBE8MN Error: Warehouse is missing for Stock Code 32131

To look up SalesWarehouse field in ArCustomer Table, you grab the Customer number. You may need to look the order up to get this field.

select Customer, SalesWarehouse from ArCustomer where Customer = 'KRO0103'

To look up QtyOnHand field in InvWarehouse Table (Inventory Warehouse), you grab the Customer number from previous script and the item warehouse the above script returned with.

select StockCode, QtyOnHand, Warehouse from InvWarehouse where StockCode = '89167' and Warehouse = 'WDAVL'

To look up Distribution or Warehouse To Use, use below script.

select ltrim(WarehouseToUse), ltrim(DistWarehouseToUse) from InvMaster where StockCode = '89167'

TURN AROUND TABLE (TAT) QUERIES:

ABOUT: Turn Around Tables are used for outbound maps when we need to store information from inbound maps to send back out in outbound. If any of the above tables are not as beneficial, these will be used. Not all maps use these tables.

To look up Header table using the PO#. Note: The PO# is key to both turn around tables.

Select * from WS_EdiCustomerPO_Header where PurchaseOrder = '6NT1984V'

To look up Detail table using PO#.

Select * from WS_EdiCustomerPO_Details where PurchaseOrder = '6NT1984V'

SHIPMENT TABLE (204) QUERIES:

ABOUT: Shipment tables are used for the 204s. There are Header table, Shipment Stop table, and Shipment Note tables. Other common tables are used in the 204 like the SorMaster and SorDetail Tables in map as well. Stops need to be distinguished between CL (Complete Load) and CU (Complete Unload) which also includes destination stops.

To look up WS_Shipment table (Header) using the Ship ID. Note: The Ship ID is the same as the Sales Order # and may need to be looked up. Note: This table is a used via the S5 loops and will show the CL vs CU including those address lines.

select * from WS_Shipment where ShipId = '000000000265205'

To look up Shipment Stops (OID Loop) table using Ship ID. Note: This is the PO#s in the shipment and that information pertaining to that PO.

select * from WS_ShipmentStop where ShipId = '000000000264571'

To look up Shipment Notes for Header using Ship ID.

select * from WS_ShipmentNote where ShipId = '000000000264571'