Representation of inspection order results in SAP Business One
When an inspection order is finalized, the related inspection results are exported to SAP Business One where they are stored in five tables. These tables are created during the installation of Netronic Manufacturing and have the following content:
- inspection orders,
- samples inspected,
- tests performed,
- defects found,
- equipment used.
You or you company's IT department can write database queries to create reports from the data collected.
Below, you will find the detailed specification of the data tables and also two example queries that could be run on them.
Table 1. Inspection orders
This table contains one record per closed inspection order.
- Table name: BOY_CLOUD_INSP_RESO
- Table description: Inspection Result Order
- Fields:
Field name | Field description | Data type | Comment |
Code | Code | Alphanumeric (50) | Unique, auto-generated id; only used to join tables together |
Name | Name | Alphanumeric (100) | Unique, auto-generated id; only used to join tables together |
U_DocNum | Inspection Order | Numeric (11) | The document number of the inspection order |
U_ItemCode | Item Code | Alphanumeric (50) | The item code in the inspection order |
U_ItemName | Item Name | Alphanumeric (100) | The item name in the inspection order |
U_Batch | Batch number | Alphanumeric (36) | The batch number linked to the inspection order |
U_Serial | Serial number | Alphanumeric (36) | The serial number linked to the inspection order |
U_Quantity | Quantity | Quantity | The quantity in the inspection order |
U_CardCode | Business Partner Code | Alphanumeric (15) | The customer or supplier code of the source document of the inspection order |
U_CardName | Business Partner Name | Alphanumeric (100) | The customer or supplier name of the source document of the inspection order |
U_CrtDate | Creation Date | Date | The creation date of the inspection order |
U_ClsDate | Closing Date | Date | Not used currently |
U_CrtType | Creation Type | Numeric (11) | The creation type of the inspection order. Possible values are:
|
U_ErpDocType | ERP Document Type | Numeric (11) | The SAP document type of the source document. Possible values are:
|
U_ErpDocEntry | ERP Document Key | Numeric (11) | The SAP document entry of the source document |
U_ErpDocNum | ERP Document Number | Numeric (11) | The SAP document number of the source document |
U_ErpDocLine | ERP Document Line | Numeric (11) | The SAP document line of the source document |
U_PassRate | Pass Rate | Rate | The pass rate as a percentage (100 = 100%) |
U_Result | Inspection Result | Numeric (11) | The inspection order result. Possible values are:
|
Table 2. Samples inspected
- Table name: BOY_CLOUD_INSP_RESS
- Table description: Inspection Result Sample
- Fields:
Field name | Field description | Data type | Comment |
Code | Code | Alphanumeric (50) | Unique, auto-generated id; only used to join tables together |
Name | Name | Alphanumeric (100) | Unique, auto-generated id; only used to join tables together |
U_ResOrdCode | Result Order Code | Alphanumeric (50) | Reference to BOY_CLOUD_INSP_RESO.Code |
U_Number | Sample Number | Numeric (11) | The sample number |
U_Result | Sample Result | Numeric (11) | The sample result. Possible values are:
|
Table 3. Tests performed
- Table name: BOY_CLOUD_INSP_REST
- Table description: Inspection Result Test
- Fields:
Field name | Field description | Data type | Comment |
Code | Code | Alphanumeric (50) | Unique, auto-generated id; only used to join tables together |
Name | Name | Alphanumeric (100) | Unique, auto-generated id; only used to join tables together |
U_ResSamCode | Result Sample Code | Alphanumeric (50) | Reference to BOY_CLOUD_INSP_RESS.Code |
U_Id | Test Id | Alphanumeric (50) | The test's unique ID |
U_Name | Test Name | Alphanumeric (254) | The test name |
U_ValAttr | Value Attribute | Alphanumeric (254) | For attribute tests, the name of the attribute chosen as the test result |
U_ValVar | Value Variable | Measure | For variable tests, the value of the test result |
U_MeasUnit | Measurement Unit | Alphanumeric (50) | For variable tests, the measurement unit |
U_Result | Test Result | Numeric (11) | The test result. Possible values are:
|
Table 4. Defects found
- Table name: BOY_CLOUD_INSP_RESD
- Table description: Inspection Result Defect
- Fields:
Field name | Field description | Data type | Comment |
Code | Code | Alphanumeric (50) | Unique, auto-generated id; only used to join tables together |
Name | Name | Alphanumeric (100) | Unique, auto-generated id; only used to join tables together |
U_ResTstCode | Result Test Code | Alphanumeric (50) | Reference to BOY_CLOUD_INSP_REST.Code |
U_Id | Defect Id | Alphanumeric (50) | The defect's unique ID |
U_Name | Defect Name | Alphanumeric (254) | The defect name |
Table 5. Equipment used
- Table name: BOY_CLOUD_INSP_RESE
- Table description: Inspection Result Equipment
- Fields:
Field name | Field description | Data type | Comment |
Code | Code | Alphanumeric (50) | Unique, auto-generated id; only used to join tables together |
Name | Name | Alphanumeric (100) | Unique, auto-generated id; only used to join tables together |
U_ResTstCode | Result Test Code | Alphanumeric (50) | Reference to BOY_CLOUD_INSP_REST.Code |
U_Name | Equipment Name | Alphanumeric (254) | Equipment name |
U_Code | Equipment Code | Alphanumeric (254) | The equipment's unique ID |
Example queries
The two HANA queries listed below combine these tables and filter for a given inspection order number.
Example 1 - Select all values from all tables
SELECT * FROM "@BOY_CLOUD_INSP_RESO" T0 LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESS" T1 ON T1."U_ResOrdCode" = T0."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_REST" T2 ON T2."U_ResSamCode" = T1."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESD" T3 ON T3."U_ResTstCode" = T2."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESE" T4 ON T4."U_ResTstCode" = T2."Code" WHERE T0."U_DocNum" = [%0] ORDER BY T0."U_DocNum", T1."U_Number", T2."U_Name", T3."U_Name", T4."U_Name"
Example 2 - Select some values, parse enumerations, and link to OITM and OCRD
SELECT T0."U_DocNum" AS "Order No", T4."ItemCode" AS "Item code", T0."U_ItemName" AS "Item name", T0."U_Batch" AS "Batch", T0."U_Serial" AS "Serial", T0."U_Quantity" AS "Order quantity", T5."CardCode" AS "BP Code", T0."U_CardName" AS "BP Name", T0."U_CrtDate" AS "Created", CASE T0."U_Result" WHEN 10 THEN 'Inconclusive' WHEN 30 THEN 'Passed' WHEN 40 THEN 'Failed' END AS "Order result", T1."U_Number" AS "Sample", CASE T1."U_Result" WHEN 10 THEN 'Inconclusive' WHEN 30 THEN 'Passed' WHEN 40 THEN 'Failed' END AS "Sample result", T2."U_Name" AS "Test name", CASE WHEN T2."U_ValAttr" IS NULL THEN '' || T2."U_ValVar" || ' ' || T2."U_MeasUnit" ELSE T2."U_ValAttr" END AS "Value", CASE T2."U_Result" WHEN 10 THEN 'Inconclusive' WHEN 30 THEN 'Passed' WHEN 40 THEN 'Failed' END AS "Test result", T3."U_Name" AS "Defect", T6."U_Name" AS "Equipment" FROM "@BOY_CLOUD_INSP_RESO" T0 LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESS" T1 ON T1."U_ResOrdCode" = T0."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_REST" T2 ON T2."U_ResSamCode" = T1."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESD" T3 ON T3."U_ResTstCode" = T2."Code" LEFT OUTER JOIN "@BOY_CLOUD_INSP_RESE" T6 ON T6."U_ResTstCode" = T2."Code" LEFT OUTER JOIN OITM T4 ON T4."ItemCode" = T0."U_ItemCode" LEFT OUTER JOIN OCRD T5 ON T5."CardCode" = T0."U_CardCode" WHERE T0."U_DocNum" = [%0] ORDER BY T0."U_DocNum", T1."U_Number", T2."U_Name", T3."U_Name", T6."U_Name"