Overview
This command will list all physical record requests that have been executed.
Related tables
Table
|
Description
|
Primary key (PK)
|
Connection tables
|
DCPHYSFILERECORD
|
Physical record request
|
CDPHYSFILERECORD
|
|
DCPHYSFILERECDOC
|
Physical record request documents
|
CDPHYSFILERECORD
CDDOCUMENT
|
DCPHYSFILERECORD
DCDOCREVISION
|
DCDOCREVISION
|
Document
|
CDDOCUMENT
CDREVISION
|
DCPHYSFILERECDOC
|
DCCATEGORY
|
Category
|
CDCATEGORY
|
DCDOCREVISION
|
ADUSER
|
User
|
CDUSER
|
DCPHYSFILERECORD
|
ADDEPARTMENT
|
Department
|
CDDEPARTMENT
|
DCPHYSFILERECORD
|
GNREASON
|
Reason
|
CDREASON
|
DCPHYSFILERECORD
|
Oracle/Postgres/SQL Server
SELECT REC.NRPHYSFILERECORD,
CASE
WHEN REC.FGSTATUS = 1 THEN 'Issue'
WHEN REC.FGSTATUS = 2 THEN 'Approval'
WHEN REC.FGSTATUS = 3 THEN 'Record'
WHEN REC.FGSTATUS = 4 THEN 'Cancelled'
WHEN REC.FGSTATUS = 5 THEN 'Acknowledgment'
WHEN REC.FGSTATUS = 6 THEN 'Loaned out'
WHEN REC.FGSTATUS = 7 THEN 'Finished'
END AS SITUATION, /*NOTE1*/
CASE
WHEN REC.FGTYPE = 1 THEN 'Content viewing'
WHEN REC.FGTYPE = 2 THEN 'Physical record loan'
WHEN REC.FGTYPE = 3 THEN 'Physical record copy'
END AS REQUEST_TYPE , /*NOTE2*/
CAT.IDCATEGORY, DOC.IDDOCUMENT, DOC.NMTITLE, ADDEP.NMDEPARTMENT,
REC.DTREQUEST,
U.NMUSER ,
GNREAS.NMREASON
FROM DCPHYSFILERECORD REC
INNER JOIN DCPHYSFILERECDOC RDOC ON REC.CDPHYSFILERECORD = RDOC.CDPHYSFILERECORD
INNER JOIN DCDOCREVISION DOC ON DOC.CDDOCUMENT = RDOC.CDDOCUMENT
INNER JOIN DCCATEGORY CAT ON DOC.CDCATEGORY = CAT.CDCATEGORY
INNER JOIN ADUSER U ON U.CDUSER = REC.CDREQUESTBYUSER
LEFT OUTER JOIN ADDEPARTMENT ADDEP ON REC.CDDEPARTMENT = ADDEP.CDDEPARTMENT
LEFT OUTER JOIN GNREASON GNREAS ON GNREAS.CDREASON = REC.CDREASON
WHERE DOC.FGCURRENT=1 /*NOTE3*/
AND REC.FGREQUEST = 1 /*NOTE4*/
ORDER BY REC.NRPHYSFILERECORD, CAT.IDCATEGORY, DOC.IDDOCUMENT
|
Notes
▪1: The REC.FGSTATUS field indicates the request status. Case is used to display each status on text mode. ▪2: The REC.FGTYPE field indicates what the request type is. Case is used to display on text mode. ▪3: The DR.FGCURRENT indicates whether the revision is current. In this case, a value = 1 returns only from the current revision of the document. To return from all revisions, simply remove this condition from the instruction. ▪4: The REC.FGREQUEST = 1 field indicates the requests (1 = Request; 2 = Record).
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
SITUATION (REC.FGSTATUS)
|
NUMERIC(2)
|
Document status, obtained through the case on REC.FGSTATUS.
|
2
|
REQUEST_TYPE (REC.FGTYPE)
|
NUMERIC(2)
|
Request type, obtained through the case on REC.FGTYPE.
|
|
