This topic gives some examples of simple SQL select statements which can be adapted by users of the Integra Tools.
DSET 01 = Actuals
DSET 02 = Budget
1415 is the current year - the year being reported on.
DEPARTMENT or its ancestor has been set up in the first group on NML150 Summary Views Definition with ONCOLOGY, RADIOLOGY and CARDIOLOGY for example among the high-level elements defined, representing hospital departments.
The NML691 Summary View Update has been run recently.
SELECT SUM(PERIOD_0 + PERIOD_1 + PERIOD_2 + PERIOD_3 + PERIOD_4 + PERIOD_5 + PERIOD_6 + PERIOD_7 + PERIOD_8 + PERIOD_9 + PERIOD_10 + PERIOD_11 + PERIOD_12) AS ACTUAL_VALUE
FROM NMLORGSUMMV_D
WHERE POSTING_YEAR = '1415'
AND DSET = '01'
AND GROUP_1 = 'DEPARTMENT' AND ELEMENT_1 = 'ONCOLOGY'
SELECT SUM(PERIOD_0 + PERIOD_1 + PERIOD_2 + PERIOD_3 + PERIOD_4 + PERIOD_5 + PERIOD_6 + PERIOD_7 + PERIOD_8 + PERIOD_9 + PERIOD_10 + PERIOD_11 + PERIOD_12) AS BUDGET_VALUE
FROM NMLORGSUMMV_D
WHERE POSTING_YEAR = '1415'
AND DSET = '02'
AND GROUP_1 = 'DEPARTMENT' AND ELEMENT_1 = 'ONCOLOGY'
Other values can be retrieved by substituting the items in blue depending on what Summary View element (department) is required and on whether budget or actuals are to be retrieved.
Variations of this formula can be used to get direct debit values for the other companies. The = operator is changed to <> to get values for payments not made by direct debit.
select sum(local_amt)
from SLSCUMTRX
where receipt_type ='DD'
and COMPANY_CODE = '000001'
SELECT sum(TOT_VAL_ORD)
FROM RSSPOHEAD
WHERE STATUS = 'A'
select ord_no, supplier from rsspohead where status = 'T'
select comp as Company ,ref as Reference, supplier as Supplier
from prlreghed
WHERE (status='1' and doc_type='INV' and tot_value<=(select aut_value
from rssautlev l
join rssreqner r on l.autlev_code=r.autlev_code_inv and r.reqner_code='000001'))