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:

  1. inspection orders,
  2. samples inspected,
  3. tests performed,
  4. defects found,
  5. 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
CodeCodeAlphanumeric (50)Unique, auto-generated id; only used to join tables together
NameNameAlphanumeric (100)Unique, auto-generated id; only used to join tables together
U_DocNumInspection OrderNumeric (11)The document number of the inspection order
U_ItemCodeItem CodeAlphanumeric (50)The item code in the inspection order
U_ItemNameItem NameAlphanumeric (100)The item name in the inspection order
U_BatchBatch numberAlphanumeric (36)The batch number linked to the inspection order
U_SerialSerial numberAlphanumeric (36)The serial number linked to the inspection order
U_QuantityQuantityQuantityThe quantity in the inspection order
U_CardCodeBusiness Partner CodeAlphanumeric (15)The customer or supplier code of the source document of the inspection order
U_CardNameBusiness Partner NameAlphanumeric (100)The customer or supplier name of the source document of the inspection order
U_CrtDateCreation DateDateThe creation date of the inspection order
U_ClsDateClosing DateDateNot used currently
U_CrtTypeCreation TypeNumeric (11)

The creation type of the inspection order. Possible values are:

  • 10 = Manual
  • 20 = Goods receipt automation
  • 30 = Production receipt automation
U_ErpDocTypeERP Document TypeNumeric (11)The SAP document type of the source document. Possible values are:
  • 18 = A/P invoice
  • 20 = Goods receipt PO
  • 59 = Production receipt
U_ErpDocEntryERP Document KeyNumeric (11)The SAP document entry of the source document
U_ErpDocNumERP Document NumberNumeric (11)The SAP document number of the source document
U_ErpDocLineERP Document LineNumeric (11)The SAP document line of the source document
U_PassRatePass RateRateThe pass rate as a percentage (100 = 100%)
U_ResultInspection ResultNumeric (11)The inspection order result. Possible values are:
  • 10 = Inconclusive
  • 30 = Passed
  • 40 = Failed


Table 2. Samples inspected

  • Table name: BOY_CLOUD_INSP_RESS
  • Table description: Inspection Result Sample
  • Fields:
Field name
Field description
Data type
Comment
CodeCodeAlphanumeric (50)Unique, auto-generated id; only used to join tables together
NameNameAlphanumeric (100)Unique, auto-generated id; only used to join tables together
U_ResOrdCodeResult Order CodeAlphanumeric (50)Reference to BOY_CLOUD_INSP_RESO.Code
U_NumberSample NumberNumeric (11)The sample number
U_ResultSample ResultNumeric (11)The sample result. Possible values are:
  • 10 = Inconclusive
  • 30 = Passed
  • 40 = Failed


Table 3. Tests performed

  • Table name: BOY_CLOUD_INSP_REST
  • Table description: Inspection Result Test
  • Fields:
Field name
Field description
Data type
Comment
CodeCodeAlphanumeric (50)Unique, auto-generated id; only used to join tables together
NameNameAlphanumeric (100)Unique, auto-generated id; only used to join tables together
U_ResSamCodeResult Sample CodeAlphanumeric (50)Reference to BOY_CLOUD_INSP_RESS.Code
U_IdTest IdAlphanumeric (50)The test's unique ID
U_NameTest NameAlphanumeric (254)The test name
U_ValAttrValue AttributeAlphanumeric (254)For attribute tests, the name of the attribute chosen as the test result
U_ValVarValue VariableMeasureFor variable tests, the value of the test result
U_MeasUnitMeasurement UnitAlphanumeric (50)For variable tests, the measurement unit
U_ResultTest ResultNumeric (11)The test result. Possible values are:
  • 10 = Inconclusive
  • 30 = Passed
  • 40 = Failed


Table 4. Defects found

  • Table name: BOY_CLOUD_INSP_RESD
  • Table description: Inspection Result Defect
  • Fields:
Field name
Field description
Data type
Comment
CodeCodeAlphanumeric (50)Unique, auto-generated id; only used to join tables together
NameNameAlphanumeric (100)Unique, auto-generated id; only used to join tables together
U_ResTstCodeResult Test CodeAlphanumeric (50)Reference to BOY_CLOUD_INSP_REST.Code
U_IdDefect IdAlphanumeric (50)The defect's unique ID
U_NameDefect NameAlphanumeric (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
CodeCodeAlphanumeric (50)Unique, auto-generated id; only used to join tables together
NameNameAlphanumeric (100)Unique, auto-generated id; only used to join tables together
U_ResTstCodeResult Test CodeAlphanumeric (50)Reference to BOY_CLOUD_INSP_REST.Code
U_NameEquipment NameAlphanumeric (254)Equipment name
U_CodeEquipment CodeAlphanumeric (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"

Did this article help solve your problem?