DB View

Variables

A variable must be created for each column of dynamic rows that you want to display in the report.

For example, a date column named issueDate creates a new date variable named IssueDate

Case

There must already be at least 1 active case in TAS that contains the created variables and dynamic rows.

This case ID will be used to create the view

Script

Example of a script for saving a view in the DB, it is a specific script that must be modified according to needs, variable IDs and name

Oracle DB is min. version 19MSSQL - compatibility level must be at least 130 otherwise OPENJSON cannot do it

Oracle

SELECT
 155286 AS IPROC_ID,
 H.HEADER_CODE AS TEMPLATE_HEADER_CODE,
 IPV.IPROC_ID AS IPROC_ID_ORIGIN,
 IPV.VAR_T_38 AS STATUS,
 IPV.VAR_T_110 AS DODAVATEL,
 IPV.VAR_T_74 AS VYBER_ODDELENI,
 IPV.VAR_D_5 AS DUZP,
 IPV.VAR_T_41 AS INTERNI_CISLO_FAKTURY,
 IPV.VAR_D_4 AS DATUM_DORUCENI,
 IPV.VAR_T_44 AS MENA,
 IPV.VAR_T_73 AS VARIABILNI_SYMBOL,
 IPV.VAR_T_26 AS ICO,
 IPV.VAR_T_68 AS TEXT_FAKTURY,
 IPV.VAR_N_6 AS VECNY_SCHVALOVATEL,
 IPV.VAR_T_107 AS RADKY,
 JT.Castka AS CASTKA,
 JT.Poznamka AS POZNAMKA,
 JT.Zakazka AS ZAKAZKA,
 JT.Stredisko AS STREDISKO,
 JT.cenaVcDPH AS CENA_VC_DPH
 FROM INSTANCE_PROCESS_VARIABLES IPV
 INNER JOIN INSTANCE_PROCESSES IP ON IP.IPROC_ID = IPV.IPROC_ID
 INNER JOIN HEADERS H ON H.HEADER_ID = IP.HEADER_ID
 CROSS APPLY (
 SELECT
 castka.Castka,
 poznamka.Poznamka,
 stredisko.Stredisko,
 zakazka.Zakazka,
 cenaVcDPH.CenaVcDPH
 FROM INSTANCE_PROCESS_VARIABLES IPV2
 LEFT JOIN JSON_TABLE(
 IPV2.VAR_T_107,
 '$.Castka[*]' COLUMNS (
 idx FOR ORDINALITY,
 Castka NUMBER PATH '$'
 )
 ) castka ON 1=1
 LEFT JOIN JSON_TABLE(
 IPV.VAR_T_107,
 '$.Poznamka[*]' COLUMNS (
 idx FOR ORDINALITY,
 Poznamka VARCHAR2(100) PATH '$'
 )
 ) poznamka ON poznamka.idx = castka.idx
 LEFT JOIN JSON_TABLE(
 IPV.VAR_T_107,
 '$.Zakazka.title[*]' COLUMNS (
 idx FOR ORDINALITY,
 Zakazka VARCHAR2(100) PATH '$'
 )
 ) zakazka ON zakazka.idx = castka.idx
 LEFT JOIN JSON_TABLE(
 IPV.VAR_T_107,
 '$.CenaVcDPH[*]' COLUMNS (
 idx FOR ORDINALITY,
 CenaVcDPH NUMBER PATH '$'
 )
 ) cenaVcDPH ON cenaVcDPH.idx = castka.idx
 LEFT JOIN JSON_TABLE(
 IPV.VAR_T_107,
 '$.Stredisko.title[*]' COLUMNS (
 idx FOR ORDINALITY,
 Stredisko VARCHAR2(100) PATH '$'
 )
 ) stredisko ON stredisko.idx = castka.idx
 WHERE IPV2.IPROC_ID = IP.IPROC_ID
 ) JT
 WHERE IP.TPROC_ID = 25
 AND IP.HEADER_ID = 941
 AND IP.IPROC_STATUS IN ('A', 'D')

MS SQL

CREATE VIEW [TAS].[IT_REPORT] AS
 SELECT
 1856242 AS IPROC_ID,
 IPV.VAR_T_6 AS CisloObjednavky,
 IPV.VAR_T_30 AS Objednatel,
 IPV.VAR_T_163 AS Dodavatel,
 IPV.VAR_N_2 AS CelkemBezDPH,
 IPV.VAR_N_35 AS CenaCZK,
 IPV.VAR_T_28 AS Mena,
 IPV.VAR_T_5 AS Stat_us,

 JSONData.value AS PONumber,
 JSON_VALUE(IPV.VAR_T_1, '$.Nazev[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + ']') AS NazevPolozky,
 JSON_VALUE(IPV.VAR_T_1, '$.Budget[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + '].title') AS TypNakladu,
 JSON_VALUE(IPV.VAR_T_1, '$.Budget[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + '].value') AS KodNakladu,
 JSON_VALUE(IPV.VAR_T_1, '$.budgetYear[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + '].title') AS Rok,
 JSON_VALUE(IPV.VAR_T_1, '$.costCenter[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + '].title') AS NakladoveStredisko,
 JSON_VALUE(IPV.VAR_T_1, '$.JednotCena[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + ']') AS CenaJednotka,
 JSON_VALUE(IPV.VAR_T_1, '$.Mnozstvi[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + ']') AS Mnozstvi,
 JSON_VALUE(IPV.VAR_T_1, '$.CenaBez[' + COALESCE(CAST(JSONData.[key] AS NVARCHAR(10)), '0') + ']') AS CenaBezDPH,

 IPV.IPROC_ID AS IPROC_ID_ORIGIN
 FROM
 TAS_INT.TAS.INSTANCE_PROCESS_VARIABLES IPV
 INNER JOIN
 TAS_INT.TAS.INSTANCE_PROCESSES IP ON IP.IPROC_ID = IPV.IPROC_ID
 CROSS APPLY OPENJSON(IPV.VAR_T_1, '$.PONumber') WITH (value NVARCHAR(MAX), [key] INT) AS JSONdata
 WHERE
 IP.TPROC_ID = 10
 AND ISJSON(IPV.VAR_T_1) = 1
 AND IP.IPROC_STATUS IN ('A', 'D')
 AND IPV.VAR_T_5 NOT IN ('Zadávání', 'Koncept', 'Stornováno');

Overview

A new overview is created in TAS, which must include:

• Case template - the template from which the view is created
• A new report is created in TAS, where it must be: View used - yes
• Source table - table name from DB
• Available fields - mapping from variables created in step 1 to definition from SELECT in script