Overview
This command will list documents with physical record control and their locations.
Related tables
Table
|
Description
|
Primary key (PK)
|
Connection tables
|
DCDOCREVISION
|
Document
|
CDDOCUMENT
CDREVISION
|
|
DCCATEGORY
|
Category
|
CDCATEGORY
|
DCDOCREVISION
|
GNREVISION
|
|
CDREVISION
|
DCDOCREVISION
|
DCDOCUMENTARCHIVAL
|
Physical record data
|
CDDOCUMENT
|
DCDOCREVISION
|
DCPHYSLOCATION
|
Location
|
CDPHYSLOCATION
|
DCDOCUMENTARCHIVAL
|
DCPHYSICALFILE
|
Repository
|
CDPHYSICALFILE
|
DCPHYSLOCATION
|
Oracle
SELECT CT.IDCATEGORY , DR.IDDOCUMENT , DR.NMTITLE , CASE WHEN CT.FGENABLEREVISION = 1 THEN GR.IDREVISION ELSE NULL END AS IDREVISION ,
(SELECT CASE WHEN PL.DSLOCATION IS NOT NULL THEN CAST(PL.DSLOCATION AS VARCHAR(4000)) || '.' ELSE '' END || CASE WHEN PL.NMPHYSLOCATION IS NOT NULL THEN NMPHYSLOCATION || '.' ELSE '' END || PF.NMBOX
FROM DCPHYSLOCATION PL, DCPHYSICALFILE PF
WHERE PL.CDPHYSLOCATION = PF.CDPHYSLOCATION
AND PF.CDPHYSICALFILE = ( CASE DA.FGSTATUSPHYSFILE WHEN 1 THEN DA.CDPHYSFILECURRENT WHEN 2 THEN DA.CDPHYSFILEINTERMED WHEN 3 THEN DA.CDPHYSFILEFINLDEST ELSE NULL END )
) AS NMPHYSFILE /*NOTE1*/
FROM DCDOCREVISION DR
INNER JOIN DCCATEGORY CT ON DR.CDCATEGORY = CT.CDCATEGORY
INNER JOIN GNREVISION GR ON GR.CDREVISION = DR.CDREVISION
INNER JOIN DCDOCUMENTARCHIVAL DA ON DA.CDDOCUMENT = DR.CDDOCUMENT
WHERE DR.FGCURRENT = 1 /*NOTE2*/
AND CT.IDCATEGORY = 'Category ID'/*NOTE3*/
ORDER BY IDCATEGORY, IDDOCUMENT
|
Postgres
SELECT CT.IDCATEGORY , DR.IDDOCUMENT , DR.NMTITLE , CASE WHEN CT.FGENABLEREVISION = 1 THEN GR.IDREVISION ELSE NULL END AS IDREVISION ,
(SELECT CASE WHEN PL.DSLOCATION IS NOT NULL THEN CAST(PL.DSLOCATION AS VARCHAR(4000)) || '.' ELSE '' END || CASE WHEN PL.NMPHYSLOCATION IS NOT NULL THEN NMPHYSLOCATION || '.' ELSE '' END || PF.NMBOX
FROM DCPHYSLOCATION PL, DCPHYSICALFILE PF
WHERE PL.CDPHYSLOCATION = PF.CDPHYSLOCATION
AND PF.CDPHYSICALFILE = ( CASE DA.FGSTATUSPHYSFILE WHEN 1 THEN DA.CDPHYSFILECURRENT WHEN 2 THEN DA.CDPHYSFILEINTERMED WHEN 3 THEN DA.CDPHYSFILEFINLDEST ELSE NULL END )
) AS NMPHYSFILE /*NOTE1*/
FROM DCDOCREVISION DR
INNER JOIN DCCATEGORY CT ON DR.CDCATEGORY = CT.CDCATEGORY
INNER JOIN GNREVISION GR ON GR.CDREVISION = DR.CDREVISION
INNER JOIN DCDOCUMENTARCHIVAL DA ON DA.CDDOCUMENT = DR.CDDOCUMENT
WHERE DR.FGCURRENT = 1 /*NOTE2*/
AND CT.IDCATEGORY = 'Category ID'/*NOTE3*/
ORDER BY IDCATEGORY, IDDOCUMENT
|
SQL Server
SELECT CT.IDCATEGORY , DR.IDDOCUMENT , DR.NMTITLE , CASE WHEN CT.FGENABLEREVISION = 1 THEN GR.IDREVISION ELSE NULL END AS IDREVISION ,
(SELECT CASE WHEN PL.DSLOCATION IS NOT NULL THEN CAST(PL.DSLOCATION AS VARCHAR(4000)) + '.' ELSE '' END + CASE WHEN PL.NMPHYSLOCATION IS NOT NULL THEN NMPHYSLOCATION + '.' ELSE '' END + PF.NMBOX
FROM DCPHYSLOCATION PL, DCPHYSICALFILE PF
WHERE PL.CDPHYSLOCATION = PF.CDPHYSLOCATION
AND PF.CDPHYSICALFILE = ( CASE DA.FGSTATUSPHYSFILE WHEN 1 THEN DA.CDPHYSFILECURRENT WHEN 2 THEN DA.CDPHYSFILEINTERMED WHEN 3 THEN DA.CDPHYSFILEFINLDEST ELSE NULL END )
) AS NMPHYSFILE /*NOTE1*/
FROM DCDOCREVISION DR
INNER JOIN DCCATEGORY CT ON DR.CDCATEGORY = CT.CDCATEGORY
INNER JOIN GNREVISION GR ON GR.CDREVISION = DR.CDREVISION
INNER JOIN DCDOCUMENTARCHIVAL DA ON DA.CDDOCUMENT = DR.CDDOCUMENT
WHERE DR.FGCURRENT = 1 /*NOTE2*/
AND CT.IDCATEGORY = 'Category ID' /*NOTE3*/
ORDER BY IDCATEGORY, IDDOCUMENT
|
Notes
▪1: The data referring to the file location is composed of more than one piece of information, so a subselect must be created for the information to be complete. ▪2: 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. ▪3: Filter by category. The ID # of the category whose documents will have their location obtained must be wrapped by single quotes. To return from all categories, this command line must be removed.
Fields used on the view screen
#
|
Field ID
|
Type
|
Description
|
1
|
CT.IDCATEGORY
|
VARCHAR(50)
|
ID # that must be entered in the SQL command.
|
2
|
DR.IDDOCUMENT
|
VARCHAR(50)
|
Document ID #
|
3
|
DR.NMTITLE
|
VARCHAR(255)
|
Document title
|
4
|
PL.DSLOCATION
|
TEXT
|
Physical path.
|
5
|
PL.NMPHYSLOCATION
|
VARCHAR(255)
|
Location name.
|
6
|
PF.NMBOX
|
VARCHAR(255)
|
Name of the physical file repository.
|
7
|
NMPHYSFILE
|
|
Information that is composed by joining fields 4, 5 and 6.
|
|
