<< Click to Display Table of Contents >> Navigation: EQuIS Library > Reports > EarthSoft Standard Reports by Product > Standard with SPM > SPM - Laboratory Invoice Report |
Report Name: SPM - Laboratory (example)
File Name: EarthSoft.Reports.Library.197043.dll
Dependencies: Licensed SPM and SPM Schema
Software: SPM
Source: SPM install
Language: English
Output Type: Report Grid with options for Excel1 and CrossTab
Description: The SPM - Laboratory Invoice report provides a comparison between expected and actual analytical laboratory costs. In addition, this report compares the quantity of samples sent to the lab versus received by the lab per SDG/COC. This report is intended to generate a grid output to be used in EQuIS Enterprise and Microsoft Power BI Dashboards. This report can be setup as an EQuIS Information Agent (EIA).
Installation Instructions: This report requires a licensed SPM and the SPM Schema applied to the database. For SPM usage, no additional steps are necessary. For Enterprise usage, this report should be published to the database. The report will be available for selection once it has been published.
Overview: The report uses information stored in RT_SPM_COST_CALC_MAG and compares it with the lab costs stored in the DT_SRN_INVOICE table. In estimating the cost, the report takes into consideration the turn-around time and lab reporting level.
Tables: DT_CHAIN_OF_CUSTODY, DT_CHAIN_OF_CUSTODY_MAG, DT_FACILITY, DT_LOCATION, DT_SAMPLE, DT_SDG, DT_SPM_CONTRACT_MAG, DT_SRN, DT_SRN_INVOICE, RT_MATRIX, RT_SPM_COST_CALC_MAG
Report Parameters |
||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
EQuIS Table |
Field |
Column Name in Grid |
---|---|---|
DT_SRN_INVOICE |
LAB_CODE |
lab_code |
DT_SRN_INVOICE |
INVOICE_NUMBER |
invoice_nb |
DT_SRN_INVOICE |
INVOICE_DATE |
invoice_date |
DT_CHAIN_OF_CUSTODY |
CHAIN_OF_CUSTODY |
coc |
DT_SDG |
SDG_NAME |
sdg_name |
DT_SDG |
SDG_STATUS |
sdg_status |
DT_CHAIN_OF_CUSTODY_MAG |
SPM_ANALYSIS |
analysis |
DT_SRN_INVOICE |
ANALYTIC_METHOD |
method |
DT_SPM_CONTRACT_MAG |
REPORT_LEVEL |
level |
DT_SRN_INVOICE |
REPORT_LEVEL |
lab_level |
DT_CHAIN_OF_CUSTODY |
TURN_AROUND_TIME |
coc_tat |
DT_SRN_INVOICE |
SAMPLE_RECEIPT_DATE - REPORT_SENT_DATE |
lab_tat |
-- |
-- |
expected_cost (calculated value) |
DT_SRN_INVOICE |
COST |
lab_cost |
RT_SPM_COST_CALC_MAG |
TAT[xx]_MUTLIPLIER |
tat_multiplier |
RT_SPM_COST_CALC_MAG |
LEVEL[x]_MULTIPLIER |
level_multiplier |
-- |
-- |
coc_quantity (calculated value) |
DT_SRN_INVOICE |
QUANTITY |
lab_quantity |
DT_SRN_INVOICE |
SURCHARGE_YN |
surcharge_yn |
DT_SRN_INVOICE |
SURCHARGE_COMMENT |
surcharge_comment |
DT_SRN_INVOICE |
PO_NUMBER |
po_number |
DT_SRN_INVOICE |
APPROVED_BY |
approved_by |
DT_SRN_INVOICE |
APPROVED_DATE |
approval_date |
DT_FACILITY |
FACILITY_CODE |
facility_code |
DT_FACILITY |
FACILITY_NAME |
facility_name |
DT_SAMPLE |
TASK_CODE |
task |
DT_LOCATION |
SUB_FACILITY_CODE |
sub_factility_code |
DT_SRN_INVOICE |
CLIENT_PRODUCT_CODE |
lab_product_code |
DT_SRN_INVOICE |
REMARK |
remark |
Note: Turn-around time is used to determine tat[xx]_multiplier column, where xx = 1 to 20.
Key Points to check if data are not as expected:
•A record for SPM_CONTRACT_MAG must exist in the RT_SPM_COST_CALC_MAG table for each COST_CALC_CODE.
•SPM_ANALYSIS column in the DT_CHAIN_OF_CUSTODY_MAG table cannot be empty.
•MEDIUM column must have value in the RT_MATRIX table.
•LAB_PRODUCT_CODE is determined in the RT_SPM_COST_CALC_MAG table using the following criteria:
•COST_CALC_CODE must match with COST_CALC_CODE in the DT_SPM_CONTRACT_MAG table,
•SPM_CONTRACT_MAG must match with SPM_ANALYSIS in the DT_CHAIN_OF_CUSTODY_MAG table, and
•MEDIUM must match with MEDIUM in the RT_MATRIX table table.
•LAB_PRODUCT_CODE must be populated in the RT_SPM_COST_CALC_MAG table.
•The CLIENT_PRODUCT_CODE column cannot be empty in the DT_SRN_INVOICE table.
•The value of DT_SRN_INVOICE.CLIENT_PRODUCT_CODE must match with the value of RT_SPM_COST_CALC_MAG.LAB_PRODUCT_CODE.
1Excel report output requires a valid license of Microsoft Excel to view the output.
Copyright © 2023 EarthSoft, Inc • Modified: 25 Nov 2021