SELECT
GNTRA.FGSTATUS,
GNTRA.FGTYPE,
CASE
WHEN GNSTOREORI.CDSTOREROOM IS NOT NULL THEN GNSTOREFROM.CDPRODOBJECT
WHEN GNSTOREDEST.CDSTOREROOM IS NOT NULL THEN GNSTORETO.CDPRODOBJECT
END AS CDPRODOBJECT,
GNTRA.IDTRANSARCHIVAL,
GNTRA.DTTRANSARCHIVAL,
GNTRA.FGTYPEMOVEFROM,
CASE
WHEN GNTRA.FGTYPEMOVEFROM = 1 THEN
CASE
WHEN GNSTOREFROM.IDSTOREROOM IS NOT NULL THEN
CAST((GNSTOREFROM.IDSTOREROOM + ' - ' + GNSTOREFROM.NMSTOREROOM) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 2 THEN
CASE
WHEN ADDEPFROM.IDDEPARTMENT IS NOT NULL THEN
CAST((ADDEPFROM.IDDEPARTMENT + ' - ' + ADDEPFROM.NMDEPARTMENT) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 3 THEN
CASE
WHEN AUSERFROM.IDUSER IS NOT NULL THEN
CAST((AUSERFROM.IDUSER + ' - ' + AUSERFROM.NMUSER) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 4 THEN
CASE
WHEN ADCOMPFROM.IDCOMMERCIAL IS NOT NULL THEN
CAST((ADCOMPFROM.IDCOMMERCIAL + ' - ' + ADCOMPFROM.NMCOMPANY) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 5 THEN
CASE
WHEN PROCESS.IDACTIVITY IS NOT NULL THEN
CASE
WHEN ACTI.IDACTIVITY IS NOT NULL THEN
CAST((PROCESS.IDACTIVITY +'/'+ ACTI.IDACTIVITY +' - '+PROCESS.NMACTIVITY +'/'+ACTI.NMACTIVITY) AS VARCHAR(4000))
ELSE CAST((PROCESS.IDACTIVITY + ' - ' + PROCESS.NMACTIVITY) AS VARCHAR(4000))
END
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 7 THEN
CASE WHEN GEN.IDGENERATOR IS NOT NULL THEN
CAST((GEN.IDGENERATOR + ' - ' + GEN.NMGENERATOR) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVEFROM = 9 THEN
CASE WHEN GNR.IDREQUEST IS NOT NULL THEN
CAST((GNR.IDREQUEST + ' - ' + GNR.NMREQUEST) AS VARCHAR(4000))
ELSE ''
END
END AS NMTYPEMOVEFROM,
GNSTOREORI.IDSTOREROOM AS IDSTOREROOMORIGIN,
GNTRA.DTTRANSARCFROM AS DTTRANSARCFROM,
GNTRA.FGTYPEMOVETO,
CASE
WHEN GNTRA.FGTYPEMOVETO = 1 THEN
CASE
WHEN GNSTORETO.IDSTOREROOM IS NOT NULL THEN
CAST((GNSTORETO.IDSTOREROOM + ' - ' + GNSTORETO.NMSTOREROOM) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 2 THEN
CASE WHEN ADDEPTO.IDDEPARTMENT IS NOT NULL THEN
CAST((ADDEPTO.IDDEPARTMENT + ' - ' + ADDEPTO.NMDEPARTMENT) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 3 THEN
CASE
WHEN AUSERTO.IDUSER IS NOT NULL THEN
CAST((AUSERTO.IDUSER + ' - ' + AUSERTO.NMUSER) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 4 THEN
CASE
WHEN ADCOMPTO.IDCOMMERCIAL IS NOT NULL THEN
CAST((ADCOMPTO.IDCOMMERCIAL + ' - ' + ADCOMPTO.NMCOMPANY) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 6 THEN
CASE
WHEN WMDISP.IDFINALDISP IS NOT NULL THEN
CAST((WMDISP.IDFINALDISP + ' - ' + WMDISP.NMFINALDISP) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 8 THEN
CASE WHEN GNACT.IDACTIVITY IS NOT NULL THEN
CAST((GNACT.IDACTIVITY) AS VARCHAR(4000))
ELSE ''
END
WHEN GNTRA.FGTYPEMOVETO = 9 THEN
CASE WHEN GNR.IDREQUEST IS NOT NULL THEN
CAST((GNR.IDREQUEST + ' - ' + GNR.NMREQUEST) AS VARCHAR(4000))
ELSE ''
END
END AS NMTYPEMOVETO,
GNSTOREDEST.IDSTOREROOM AS IDSTOREROOMDEST,
GNTRA.DTTRANSARCTO AS DTTRANSARCTO,
GNARC.VLARCHIVAL,
OBJCTYPE.IDOBJECTTYPE,
OBJ.IDOBJECT,
OBJ.NMOBJECT,
GNARC.NMARCHIVALLOT,
GNARC.NMSERIAL,
GNARC.DTVALIDITY
FROM GNTRANSARCHIVAL GNTRA
LEFT OUTER JOIN GNARCHIVAL GNARC ON (GNARC.CDTRANSARCHIVAL = GNTRA.CDTRANSARCHIVAL)
LEFT OUTER JOIN GNSTOREROOM GNSTOREFROM ON (GNSTOREFROM.CDSTOREROOM = GNTRA.CDSTOREROOMFROM)
LEFT OUTER JOIN GNSTOREROOM GNSTORETO ON (GNSTORETO.CDSTOREROOM = GNTRA.CDSTOREROOMTO)
LEFT OUTER JOIN ADALLUSERS AUSERFROM ON (AUSERFROM.CDUSER = GNTRA.CDUSERFROM AND GNTRA.FGTYPEMOVEFROM = 3)
LEFT OUTER JOIN ADALLUSERS AUSERTO ON (AUSERTO.CDUSER = GNTRA.CDUSERTO AND GNTRA.FGTYPEMOVETO = 3)
LEFT OUTER JOIN ADDEPARTMENT ADDEPFROM ON (ADDEPFROM.CDDEPARTMENT = GNTRA.CDDEPARTMENTFROM AND GNTRA.FGTYPEMOVEFROM = 2)
LEFT OUTER JOIN ADDEPARTMENT ADDEPTO ON (ADDEPTO.CDDEPARTMENT = GNTRA.CDDEPARTMENTTO AND GNTRA.FGTYPEMOVETO = 2)
LEFT OUTER JOIN ADCOMPANY ADCOMPFROM ON (ADCOMPFROM.CDCOMPANY = GNTRA.CDSUPPLIERFROM AND GNTRA.FGTYPEMOVEFROM = 4)
LEFT OUTER JOIN ADCOMPANY ADCOMPTO ON (ADCOMPTO.CDCOMPANY = GNTRA.CDSUPPLIERTO AND GNTRA.FGTYPEMOVETO = 4)
LEFT OUTER JOIN GNSTOREROOM GNSTOREORI ON (GNSTOREORI.CDSTOREROOM = GNARC.CDSTOREROOMORIGIN)
LEFT OUTER JOIN GNSTOREROOM GNSTOREDEST ON (GNSTOREDEST.CDSTOREROOM = GNARC.CDSTOREROOMDEST)
LEFT OUTER JOIN PMACTIVITY PROCESS ON (PROCESS.CDACTIVITY = GNTRA.CDPROCESSFROM)
LEFT OUTER JOIN PMSTRUCT PMSTR ON (PMSTR.CDSTRUCT = GNTRA.CDSTRUCT)
LEFT OUTER JOIN PMACTIVITY ACTI ON (ACTI.CDACTIVITY = PMSTR.CDACTIVITY)
LEFT OUTER JOIN WMGENERATOR GEN ON (GEN.CDGENERATOR = GNTRA.CDGENERATORFROM)
LEFT OUTER JOIN WMFINALDISP WMDISP ON (WMDISP.CDFINALDISP = GNTRA.CDFINALDISP)
LEFT OUTER JOIN GNCOSTCONFIG GNCT ON (GNCT.CDCOSTCONFIG = GNTRA.CDCOSTCONFIG)
LEFT OUTER JOIN GNACTIVITY GNACT ON (GNCT.CDCOSTCONFIG = GNACT.CDCOSTCONFIG AND CDISOSYSTEM IN (109,115,126))
LEFT OUTER JOIN GNREQUEST GNR ON (GNR.CDREQUEST = GNTRA.CDREQUEST)
LEFT OUTER JOIN GNSHIPARCHIVAL GNSPARCH ON (GNSPARCH.CDTRANSARCHIVAL = GNTRA.CDTRANSARCHIVAL)
LEFT OUTER JOIN OBARCHIVAL TBS ON GNARC.CDARCHIVAL = TBS.CDARCHIVAL
INNER JOIN OBOBJECT OBJ ON (OBJ.CDOBJECT = TBS.CDOBJECT AND OBJ.CDREVISION = TBS.CDREVISION)
LEFT OUTER JOIN WMFINALDISP FINDISP ON (FINDISP.CDFINALDISP = GNTRA.CDFINALDISP OR FINDISP.CDFINALDISP = GNSPARCH.CDFINALDISP)
INNER JOIN OBOBJECTGROUP OBGROUP ON (OBGROUP.CDOBJECTGROUP = OBJ.CDOBJECT)
INNER JOIN OBOBJECTTYPE OBJCTYPE ON (OBJCTYPE.CDOBJECTTYPE = OBGROUP.CDOBJECTTYPE)
WHERE ( GNTRA.FGTYPE NOT IN (6) OR GNSPARCH.CDSHIPARCHIVAL IS NOT NULL )
AND (GNSTOREFROM.IDSTOREROOM = 'STOREROOM_ID' OR GNSTORETO.IDSTOREROOM = 'STOREROOM_ID') /*NOTE 1*/
AND EXISTS(
SELECT 1
FROM
ITITEM CHKIT
WHERE
CHKIT.CDITEM = OBJ.CDOBJECT
UNION
SELECT 1
FROM
WMNEWWASTE CHKWST
WHERE
CHKWST.CDWASTE = OBJ.CDOBJECT
UNION
SELECT 1
FROM
OBMATERIAL OBMAT
WHERE
OBMAT.CDMATERIAL = OBJ.CDOBJECT
)
|
