PJCSA View

Contains job sample scheme analyte-centric data for reports.

Table	Field	Data Type	Description
PROFJOB_CUID	LABCODE	Variable string (10)	Code of the laboratory for the job associated with the job sample scheme analyte.
PROFJOB_CUID	PRO_JOB	Variable string (20)	Code of the job associated with the job sample scheme analyte.
PROFJOB_CUID	REPSEQUENCE	Variable string (4)	Report sequence of the sample, from 0001 to 9999, with respect to other samples within the job.
PROFJOB_CUID	CUID	Variable string (20)	Common unique identifier of the sample associated with the job sample scheme analyte.
PROFJOB_CUID	SAMPLETYPE	Variable string (3)	Sample type: UNK (Unknown), DUP (Duplicate), REP (Replicate), BLK (Blank), STD (Standard), SPK (Spike), 001..999 (Reread), of the sample associated with the job sample scheme analyte.
PROFJOB_CUID	SAMPLEIDENT	Variable string (40)	Sample identifier of the sample associated with the job sample scheme analyte.
PROFJOB_CUID	EXTERNALIDENT	Variable string (40) Unicode	External identifier of the sample associated with the job sample scheme analyte.
PROFJOB_CUID	DESCRIPTION	Variable string (40) Unicode	Description of the sample associated with the job sample scheme analyte.
PROFJOB_CUID	PRODUCTCODE	Variable string (20)	Code of the product on the sample associated with the job sample scheme analyte.
PROFJOB_CUID	REGISTERED	Date-time	Date-time the sample was registered.
PROFJOB_CUID	REGISTEREDBY	Variable string (20)	Code of the staff member who registered the sample.
PROFJOB_CUID	ANALYSED	Date-time	Date-time the sample was analysed.
PROFJOB_CUID	LASTREPORTED	Date-time	Date-time the sample was last reported.
PROFJOB_CUID	FIRSTREPORTED	Date-time	Date-time the sample was first reported.
PROFJOB_CUID	REQUIRED	Date-time	Date-time the report is required for the results of the sample.
PROFJOB_CUID	SAMPLED	Date-time	Date-time the sample was taken.
PROFJOB_CUID	VALIDATED	Date-time	Date-time the sample was validated.
PROFJOB_CUID	VALIDATEDBY	Variable string (20)	Code of the staff member who validated the sample.
PROFJOB_CUID	RECEIVED	Date-time	Date-time the sample was received.
PROFJOB_CUID	STARTED	Date-time	Date-time the sample started analysis.
PROFJOB_CUID	INVOICED	Date-time	Date-time the sample was invoiced.
PROFJOB_CUID	COMPLETED	Date-time	Date-time the sample completed analysis.
PROFJOB_CUID	FINALISED	Date-time	Date-time the sample was finalised.
PROFJOB_CUID	REPORTACTIVE	Boolean	Indicates whether the analyte is reportable.
PRODUCT	PD_DESCRIPTION	Variable string (40) Unicode	Description of the product on the sample associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	SCH_CODE	Variable string (20)	Code of the scheme associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	SCHVERSION	Integer	Version number of the scheme associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	ANALYTECODE	Variable string (20)	Code of the analyte associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	FINALVALUE	Variable string (80) Unicode	The result in its final state (corrected for weight, volume, instrument factors and dilution factors) for the analyte associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	ROUNDEDVALUE	Variable string (80) Unicode	The result in its final state (corrected for weight, volume, instrument factors and dilution factors) for the analyte associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	FORMATTEDVALUE	Variable string (80) Unicode	The result in its formatted state (rounded and masked) for the analyte associated with the job sample scheme analyte.
PROFJOB_CUID_SCHEME_ANALYTE	ANALYTESTATUS	Variable string (5)	Status of the result for the analyte associated with the job sample scheme analyte: (Left empty) = Not started NVL = Not validated CHK = Waiting on check CPL = Completed LNR = Listed not received NA = Not analysed IS = Insufficient sample LAB = Lab batched QFH = Quality control failure – too high QFL = Quality control failure – too low QWH = Quality control warning – high QWL = Quality control warning – low QTF = Quality control trend failure IGN = Ignore quality control results and any other user-defined status code from the STATUSCODES table. If the sample has a PRODUCTCODE defined for it, this field holds the status of the results versus the product limits. If the sample does not have a PRODUCTCODE defined for it, this field holds the status of the results versus the replicate or duplicate limits.
PROFJOB_CUID_SCHEME_ANALYTE	USERSTATUS	Variable string (5)	Custom status for the analyte associated with the job sample scheme analyte. (This should not be confused with the custom status codes in the ANALYTESTATUS status.) Reserved for future use.
PROFJOB_CUID_SCHEME_ANALYTE	USERNOTES	Variable string (80) Unicode	Miscellaneous notes for the analyte associated with the job sample scheme analyte. Notes that are entered via script and are longer than this specified length are truncated.

View the script used to create the view during CCLAS installation

create view PJCSA as

select

PROFJOB_CUID.LABCODE,

PROFJOB_CUID.PRO_JOB,

PROFJOB_CUID.REPSEQUENCE,

PROFJOB_CUID.CUID,

PROFJOB_CUID.SAMPLETYPE,

PROFJOB_CUID.SAMPLEIDENT,

PROFJOB_CUID.EXTERNALIDENT,

PROFJOB_CUID.DESCRIPTION,

PROFJOB_CUID.PRODUCTCODE,

PROFJOB_CUID.REGISTERED,

PROFJOB_CUID.REGISTEREDBY,

PROFJOB_CUID.ANALYSED,

PROFJOB_CUID.LASTREPORTED,

PROFJOB_CUID.FIRSTREPORTED,

PROFJOB_CUID.REQUIRED,

PROFJOB_CUID.SAMPLED,

PROFJOB_CUID.VALIDATED,

PROFJOB_CUID.VALIDATEDBY,

PROFJOB_CUID.RECEIVED,

PROFJOB_CUID.STARTED,

PROFJOB_CUID.INVOICED,

PROFJOB_CUID.COMPLETED,

PROFJOB_CUID.FINALISED,

PROFJOB_CUID.REPORTACTIVE,

PRODUCT.DESCRIPTION as PD_DESCRIPTION,

PROFJOB_CUID_SCHEME_ANALYTE.SCH_CODE,

PROFJOB_CUID_SCHEME_ANALYTE.SCHVERSION,

PROFJOB_CUID_SCHEME_ANALYTE.ANALYTECODE,

PROFJOB_CUID_SCHEME_ANALYTE.FINALVALUE,

PROFJOB_CUID_SCHEME_ANALYTE.FINALVALUE as ROUNDEDVALUE,

PROFJOB_CUID_SCHEME_ANALYTE.FORMATTEDVALUE,

PROFJOB_CUID_SCHEME_ANALYTE.ANALYTESTATUS,

PROFJOB_CUID_SCHEME_ANALYTE.USERSTATUS,

PROFJOB_CUID_SCHEME_ANALYTE.USERNOTES

from

PROFJOB_CUID_SCHEME_ANALYTE

LEFT OUTER JOIN PROFJOB_CUID ON

PROFJOB_CUID_SCHEME_ANALYTE.LABCODE = PROFJOB_CUID.LABCODE and

PROFJOB_CUID_SCHEME_ANALYTE.PRO_JOB = PROFJOB_CUID.PRO_JOB and

PROFJOB_CUID_SCHEME_ANALYTE.CUID = PROFJOB_CUID.CUID

LEFT OUTER JOIN PROFJOB_CUIDUSER ON

PROFJOB_CUID_SCHEME_ANALYTE.LABCODE = PROFJOB_CUIDUSER.LABCODE and

PROFJOB_CUID_SCHEME_ANALYTE.PRO_JOB = PROFJOB_CUIDUSER.PRO_JOB and

PROFJOB_CUID_SCHEME_ANALYTE.CUID = PROFJOB_CUIDUSER.CUID

LEFT OUTER JOIN PRODUCT ON

PROFJOB_CUID.LABCODE = PRODUCT.LABCODE and

PROFJOB_CUID.PRODUCTCODE = PRODUCT.PRODUCTCODE

union