SPM - Laboratory Invoice

<< Click to Display Table of Contents >>

Navigation:  EQuIS Library > Reports > EarthSoft Standard Reports by Module > Standard with Sample Planning Module >

SPM - Laboratory Invoice

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

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

 

 

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.