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.

•For Professional usage, this report should be published to the database or have the .dll added to the local '.../Earthsoft/EQuIS/' folder.

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

Facility(s)	Select one or more facilities.
Chain of Custody(s)	Select one or more COCs.
Sample Delivery Group(s)	Select one or more SDGs.
Invoice
Date Range
Start	Select a start date.
End	Select an end date.
Invoice Number(s)	Select one or more invoice number(s).

Report Output

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]_MULTIPLIER	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.

Troubleshooting

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.