PJ View
Contains job-centric data for reports.
| Table | Field | Data Type | Description |
|---|---|---|---|
| PROFJOB |
LABCODE |
Variable string (10) |
Code of the laboratory associated with the job. |
| PROFJOB |
PRO_JOB |
Variable string (20) |
Code for the job. |
| PROFJOB |
CLI_LABCODE |
Variable string (10) |
Code of the laboratory for the client associated with the job. |
| PROFJOB |
CLI_CODE |
Variable string (40) |
Code of the client associated with the job. |
| PROFJOB |
CONT_CODE |
Variable string (20) |
Code of the contact associated with the job. |
| PROFJOB |
PROJ_CODE |
Variable string (20) |
Code of the project associated with the job. |
| PROFJOB |
COSTCODE |
Variable string (20) |
Code of the cost code associated with the job. |
| PROFJOB |
PRODUCTCODE |
Variable string (20) |
Code of the product associated with the job, to indicate the type of samples. |
| PROFJOB |
RECEIVED |
Date-time |
Date-time the job was received. |
| PROFJOB |
REGISTERED |
Date-time |
Date-time the job was registered. |
| PROFJOB |
REQUIRED |
Date-time |
Date-time the job is required to be reported. |
| PROFJOB |
STARTED |
Date-time |
Date-time the job started analysis. |
| PROFJOB |
COMPLETED |
Date-time |
Date-time the job completed analysis. |
| PROFJOB |
VALIDATED |
Date-time |
Date-time the job was validated. |
| PROFJOB |
FIRSTREPORTED |
Date-time |
Date-time the job was first reported. |
| PROFJOB |
LASTREPORTED |
Date-time |
Date-time the job was last reported. |
| PROFJOB |
INVOICED |
Date-time |
Date-time the job was invoiced. |
| CLIENT1 | CREP_LABCODE | Variable string (10) | Code of the laboratory for the client associated with the report. |
| CLIENT1 | CLI_REP | Variable string (40) |
Code of the client associated with the report. |
| PROFJOB | PJ_CREP_LABCODE | Variable string (10) | Code of the laboratory associated with the job. |
| PROFJOB | PJ_CLI_REP | Variable string (40) | Code of the client associated with the job. |
| PROFJOB | PJ_RECEIVED | Variable string (40) | Date-time the job was received. |
| PROFJOB | PJ.COMPLETED | Variable string (40) | Date-time the job was completed. |
| PROFJOB | PJ.LASTREPORTED | Date-time | Date-time a report was last raised for the job. |
| CLIENT | CL_CLI_NAME |
Variable string (40) Unicode |
Name of the client associated with the job. |
| CLIENT | CL_ADDRESS1..3 |
Variable string Unicode |
Address of the client associated with the job. |
| CLIENT | CL_STATE |
Variable string (40) Unicode |
State in which the client associated with the job exists. |
| CLIENT | CL_POSTCODE |
Variable string (10) Unicode |
Postcode of the client associated with the job. |
| CLIENT1 | CL1_CLI_NAME |
Variable string (40) Unicode |
Name of the client associated with the report. |
| CLIENT1 | CL1_ADDRESS1..2 |
Variable string (40) Unicode |
Address of the client associated with the report, |
| CLIENT1 | CL1_STATE |
Variable string (40) Unicode |
State in which the client associated with the report exists. |
| CLIENT | CL1_POSTCODE |
Variable string (10) Unicode |
Postcode of the client associated with the report, |
| CLIENT_CONTACT | CC_CONT_NAME |
Variable string (40) Unicode |
Name of the contact. |
| CLIENT_CONTACT | CC_CONT_TITLE |
Variable string (40) Unicode |
Title of the contact. |
| CLIENT_CONTACT | CC_DEPARTMENT |
Variable string (40) Unicode |
Department in which the contact exists. |
| CLIENT_PROJECT | CP_DESCRIPTION |
Variable string (80) Unicode |
Description of the project associated with the job. |
| COSTCENTRE | CO_DESCRIPTION |
Variable string (40) Unicode |
Description of the contact associated with the job. |
| PRODUCT | PD_DESCRIPTION |
Variable string (40) Unicode |
Description of the product associated with the job, |
| PROFJOB |
PJ_NOTES1..PJ_NOTES3 |
Variable string (60) |
Notes on the job. |
| PROFJOB |
PJ_PRO_PROJ |
Variable string (40) |
Text containing project details associated with the job. |
| PROFJOB | PJ_ORDERNO | Variable string (40) | Order number associated with the job. |
View the script used to create the view during CCLAS installation
create view PJ
as
select
PROFJOB.LABCODE,
PROFJOB.PRO_JOB,
PROFJOB.CLI_LABCODE,
PROFJOB.CLI_CODE,
PROFJOB.CONT_CODE,
PROFJOB.PROJ_CODE,
PROFJOB.COSTCODE,
PROFJOB.PRODUCTCODE,
PROFJOB.RECEIVED,
PROFJOB.REGISTERED,
PROFJOB.REQUIRED,
PROFJOB.STARTED,
PROFJOB.COMPLETED,
PROFJOB.VALIDATED,
PROFJOB.FIRSTREPORTED,
PROFJOB.LASTREPORTED,
PROFJOB.INVOICED,
CLIENT1.LABCODE as CREP_LABCODE,
CLIENT1.CLI_CODE as CLI_REP,
PROFJOB.LABCODE as PJ_CREP_LABCODE,
PROFJOB.CLI_CODE as PJ_CLI_REP,
PROFJOB.RECEIVED as PJ_RECEIVED,
PROFJOB.COMPLETED as PJ_COMPLETED,
PROFJOB.LASTREPORTED as PJ_LASTREPORTED,
CLIENT.CLI_NAME as CL_CLI_NAME,
CLIENT.ADDRESS1 as CL_ADDRESS1,
CLIENT.ADDRESS2 as CL_ADDRESS2,
CLIENT.ADDRESS3 as CL_ADDRESS3,
CLIENT.STATE as CL_STATE,
CLIENT.POSTCODE as CL_POSTCODE,
CLIENT1.CLI_NAME as CL1_CLI_NAME,
CLIENT1.ADDRESS1 as CL1_ADDRESS1,
CLIENT1.ADDRESS2 as CL1_ADDRESS2,
CLIENT1.ADDRESS3 as CL1_ADDRESS3,
CLIENT1.STATE as CL1_STATE,
CLIENT1.POSTCODE as CL1_POSTCODE,
CLIENT_CONTACT.CONT_NAME as CC_CONT_NAME,
CLIENT_CONTACT.CONT_TITLE as CC_CONT_TITLE,
CLIENT_CONTACT.DEPARTMENT as CC_DEPARTMENT,
CLIENT_PROJECT.DESCRIPTION as CP_DESCRIPTION,
COSTCENTRE.DESCRIPTION as CO_DESCRIPTION,
PRODUCT.DESCRIPTION as PD_DESCRIPTION,
PROFJOB.NOTES1 as PJ_NOTES1,
PROFJOB.NOTES2 as PJ_NOTES2,
PROFJOB.NOTES3 as PJ_NOTES3,
PROFJOB.PRO_PROJ as PJ_PRO_PROJ,
PROFJOB.ORDERNO as PJ_ORDERNO
from
PROFJOB
LEFT OUTER JOIN PROFJOB_CLIENTTO ON
PROFJOB.LABCODE = PROFJOB_CLIENTTO.LABCODE and
PROFJOB.PRO_JOB = PROFJOB_CLIENTTO.PRO_JOB
LEFT OUTER JOIN CLIENT CLIENT1 ON
PROFJOB_CLIENTTO.CLI_LABCODE = CLIENT1.LABCODE and
PROFJOB_CLIENTTO.CLI_CODE = CLIENT1.CLI_CODE
LEFT OUTER JOIN PROFJOBUSER ON
PROFJOB.LABCODE = PROFJOBUSER.LABCODE and
PROFJOB.PRO_JOB = PROFJOBUSER.PRO_JOB
LEFT OUTER JOIN CLIENT ON
PROFJOB.CLI_LABCODE = CLIENT.LABCODE and
PROFJOB.CLI_CODE = CLIENT.CLI_CODE
LEFT OUTER JOIN CLIENT_CONTACT ON
PROFJOB.CLI_LABCODE = CLIENT_CONTACT.LABCODE and
PROFJOB.CLI_CODE = CLIENT_CONTACT.CLI_CODE and
PROFJOB.CONT_CODE = CLIENT_CONTACT.CONT_CODE
LEFT OUTER JOIN CLIENT_PROJECT ON
PROFJOB.CLI_LABCODE = CLIENT_PROJECT.LABCODE and
PROFJOB.CLI_CODE = CLIENT_PROJECT.CLI_CODE and
PROFJOB.PROJ_CODE = CLIENT_PROJECT.PROJ_CODE
LEFT OUTER JOIN COSTCENTRE ON
PROFJOB.LABCODE = COSTCENTRE.LABCODE and
PROFJOB.COSTCODE = COSTCENTRE.COSTCODE
LEFT OUTER JOIN PRODUCT ON
PROFJOB.LABCODE = PRODUCT.LABCODE and
PROFJOB.PRODUCTCODE = PRODUCT.PRODUCTCODE
