Overview
This command will list the documents that have been loaned.
Related tables
Table
|
Description
|
Primary key (PK)
|
Connection tables
|
DCDOCREVISION
|
Document
|
CDDOCUMENT
CDREVISION
|
|
DCDOCUMENT
|
Document
|
CDDOCUMENT
|
DCDOCREVISION
|
DCCATEGORY
|
Category
|
CDCATEGORY
|
DCDOCREVISION
|
DCPHYSFILERECDOC
|
Physical record request documents
|
CDPHYSFILERECORD
CDDOCUMENT
|
DCPHYSFILERECORD
DCDOCREVISION
|
DCPHYSFILERECORD
|
Physical record request
|
CDPHYSFILERECORD
|
DCPHYSFILERECDOC
|
DCDOCUMENTARCHIVAL
|
Physical record data
|
CDDOCUMENT
|
DCDOCREVISION
|
DCCOMPLIENCERET
|
Record retention schedule
|
CDCOMPLIENCERET
CDREVISION
|
DCDOCUMENTARCHIVAL
|
DCPHYSLOCATION
|
Location
|
CDPHYSLOCATION
|
|
DCPHYSICALFILE
|
Repository
|
CDPHYSICALFILE
|
DCPHYSLOCATION
|
ADUSER
|
User
|
CDUSER
|
DCPHYSFILERECORD
|
Oracle/Postgres/SQL Server
SELECT
REC.NRPHYSFILERECORD,
CT.IDCATEGORY,
DR.IDDOCUMENT,
DR.NMTITLE,
RECDOC.DTDEVOLDUEDATE,
DLOC.DSLOCATION,
DLOC.NMPHYSLOCATION,
DF.NMBOX,
USSOL.NMUSER AS REQUESTERUSER
FROM DCDOCREVISION DR
INNER JOIN DCDOCUMENT DC ON DC.CDDOCUMENT = DR.CDDOCUMENT
INNER JOIN DCCATEGORY CT ON DR.CDCATEGORY = CT.CDCATEGORY
INNER JOIN DCPHYSFILERECDOC RECDOC ON RECDOC.CDDOCUMENT = DR.CDDOCUMENT
INNER JOIN DCPHYSFILERECORD REC ON REC.CDPHYSFILERECORD = RECDOC.CDPHYSFILERECORD
INNER JOIN DCDOCUMENTARCHIVAL DA ON DC.CDDOCUMENT = DA.CDDOCUMENT
INNER JOIN DCCOMPLIENCERET CP ON CP.CDCOMPLIENCERET = DA.CDCOMPLIENCERET AND CP.CDREVISION = DA.CDREVISION
INNER JOIN DCPHYSLOCATION DLOC ON 1=1
INNER JOIN DCPHYSICALFILE DF ON DLOC.CDPHYSLOCATION = DF.CDPHYSLOCATION
INNER JOIN ADUSER USSOL ON USSOL.CDUSER = REC.CDREQUESTBYUSER
WHERE DR.FGCURRENT = 1 /*NOTE1*/
AND REC.FGSTATUS = 6 /*NOTE2*/
AND DC.FGSTATUS IN (2,3) /*NOTE3*/
AND RECDOC.DTDEVOLUTION IS NULL
AND
(
(
(DA.FGSTATUSPHYSFILE = 1) AND (DF.CDPHYSICALFILE = DA.CDPHYSFILECURRENT)
)
OR
(
(DA.FGSTATUSPHYSFILE = 2) AND (DF.CDPHYSICALFILE = DA.CDPHYSFILEINTERMED)
)
OR
(
(DA.FGSTATUSPHYSFILE = 3) AND CP.FGPERMARCHIVETYPE = 2 AND (DF.CDPHYSICALFILE = DA.CDPHYSFILEFINLDEST)
) /*NOTE4*/
)
|
Notes
▪1: The DR.FGCURRENT field 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. ▪2: The REC.FGSTATUS = 6 field ensures that loaned records are returned. ▪3: The DC.FGSTATUS IN (2,3) condition ensure that cancelled documents or documents in the approval step are not considered in the search. ▪4: This condition is necessary to ensure that the correct location of the file is shown.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
REC.NRPHYSFILERECORD
|
NUMERIC(10)
|
Loan record ID #
|
2
|
CAT.IDCATEGORY
|
VARCHAR(50)
|
Category ID #
|
3
|
DOC.IDDOCUMENT
|
VARCHAR(50)
|
Document ID #
|
4
|
DOC.NMTITLE
|
VARCHAR(255)
|
Document title
|
5
|
RECDOC.DTDEVOLDUEDATE
|
DATETIME
|
Return deadline
|
6
|
DLOC.DSLOCATION
|
TEXT
|
Composes the information referring to the physical path up to the first point.
|
6
|
DLOC.NMPHYSLOCATION
|
VARCHAR(255)
|
Composes the information referring to the physical path. Central data.
|
6
|
DF.NMBOX
|
VARCHAR(255)
|
Composes the information referring to the physical path. Final data.
|
7
|
USSOL.NMUSER (REQUESTERUSER)
|
VARCHAR(255)
|
Requester's name
|
|