66 lines
2.0 KiB
SQL
66 lines
2.0 KiB
SQL
-- SecurityDossier --
|
|
|
|
SELECT
|
|
SD.RECID 'RECID'
|
|
,SD.SAFETYDOCID 'SAFETYDOCID'
|
|
,SDT.TYPEID 'TYPE'
|
|
,SD.DESCRIPTION 'DESCRIPTION'
|
|
,ICO.FILEPATH 'ICONPATH'
|
|
,SD.PALDC_SAFETYDOCUDOSSIERQTY 'QTY'
|
|
|
|
FROM PALDC_SAFETYDOCUMENTS SD
|
|
LEFT JOIN PALDC_SAFETYDOCUTYPE SDT
|
|
ON SD.SAFETYDOCUTYPERECID = SDT.RECID
|
|
AND SD.DATAAREAID = SDT.DATAAREAID
|
|
AND SD.PARTITION = SDT.PARTITION
|
|
LEFT JOIN PALDC_ICONS ICO
|
|
ON SDT.ICONRECID = ICO.RECID
|
|
AND SDT.DATAAREAID = ICO.DATAAREAID
|
|
AND SDT.PARTITION = ICO.PARTITION
|
|
|
|
ORDER BY SD.SAFETYDOCID ASC
|
|
|
|
-- ROW
|
|
SELECT
|
|
SDR.SAFETYDOCID 'SAFETYDOCID'
|
|
,SDR.RECID 'RECID'
|
|
,SDR.SAFETYDOCROWID 'SAFETYDOCROWID'
|
|
,SDR.DOSSIERNUM 'DOSSIERNUM'
|
|
,SDR.ESTABLISHMENTS 'ESTABLISHMENTS'
|
|
,ISNULL(SDR.NOTES, '') 'NOTES'
|
|
,SDR.SCHEDDATETIMETYPEID 'SCHEDDATETIMETYPEID'
|
|
,SDR.DESCRIPTION 'DESCRIPTION'
|
|
,SDR.DURATIONESTIMATED 'DURATIONESTIMATED'
|
|
FROM PALDC_SAFETYDOCUMENTSROW SDR
|
|
WHERE SDR.SAFETYDOCID = 'DDS-000001'
|
|
|
|
-- ALLEGATI
|
|
SELECT
|
|
SDRA.DUEDATE
|
|
,SDRA.DURATIONEFFECTIVE
|
|
,SDRA.DURATIONEFFECTIVETYPE
|
|
,HCMWDV.NAME 'HCMWORKER'
|
|
,OS.DESCRIPTION 'ORGANIZATIONESTABLISHMENTS'
|
|
,SDRA.NOTES
|
|
,SDRA.SAFETYDOCUSTATUS
|
|
,SDRA.REFERENCE
|
|
,SDRA.RECID
|
|
,DR.NOTES 'FILEURI'
|
|
|
|
FROM PALDC_SAFETYDOCUMENTSROWATTACHS SDRA
|
|
LEFT JOIN HCMWORKERDETAILSVIEW HCMWDV
|
|
ON SDRA.HCMWORKERRECID = HCMWDV.RecId
|
|
AND SDRA.PARTITION = HCMWDV.PARTITION
|
|
AND SDRA.PARTITION = HCMWDV.PARTITION#2
|
|
LEFT JOIN PALDC_ORGANIZATIONESTABLISHMENTS OS
|
|
ON SDRA.PALDC_ORGESTABLISHMENTSRECID = OS.RECID
|
|
AND SDRA.DATAAREAID = OS.DATAAREAID
|
|
AND SDRA.PARTITION = OS.PARTITION
|
|
LEFT JOIN DOCUREF DR
|
|
ON SDRA.RECID = DR.REFRECID
|
|
AND SDRA.DATAAREAID = DR.ACTUALCOMPANYID
|
|
AND SDRA.PARTITION = DR.PARTITION
|
|
WHERE SAFETYDOCROWID = 'DRS-000001'
|
|
AND SDRA.SAFETYDOCUSTATUS = 2
|
|
AND DR.TYPEID = 'SIC_ATT'
|
|
ORDER BY SDRA.REFERENCE, SDRA.DUEDATE DESC |