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 Cloud Apps and have the following content:

1. inspection orders,
2. samples inspected,
3. tests performed,
4. defects found,
5. equipment used.

You or your 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:

Table 2. Samples inspected

• Table name: BOY_CLOUD_INSP_RESS
• Table description: Inspection Result Sample
• Fields:

Table 3. Tests performed

• Table name: BOY_CLOUD_INSP_REST
• Table description: Inspection Result Test
• Fields:

Table 4. Defects found

• Table name: BOY_CLOUD_INSP_RESD
• Table description: Inspection Result Defect
• Fields:

Table 5. Equipment used

• Table name: BOY_CLOUD_INSP_RESE
• Table description: Inspection Result Equipment
• Fields:

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"