<$W>GetDocumentWorkspace 105057 08-Oct-2025 11:15:36:4170 AM
<$W>SELECT DOC_BRAND1,DOC_STATUS_STR,IS_TRANS,STAGE_NAME,DISPLAY_STAGE_NAME,APPROVAL_OF_PARALLEL_STAGES,ACCEPTED,REJECTED,OPERATION_TYPE,MOREINFO,TIME_ALLOT,IS_COND,WF_ID,SENT_TO_WS,HAS_FILES_ATTACHED,REFERRED_DOC_COUNT,XREF_COUNT,CORRESPONDANCE_TYPE,REPLY_REQUIRED,EXPECTED_REPLY_DATE,RCV_BACK_DATE,REPLAY_EXPECTED_FROM,TO_BE_REPLIED_BY,LETTER_DATE,CORR_ISSUE_DATE,RECEIPT_DATE,DESCRIPTION,STATUS_DESC,INTR_STAT_DESC,REPLAY_RECEIPT_DATE,CLIENT_CODE,PUBLISH_TYPE_NEW,ITEM_AVAILABLE_WITH,ROUTE_SUB_TYPE,ROUTING_ID,ROUTING_SUB_ID,RUN_ID,GUIDED_UNGUIDED,IS_READ,READ_UNREAD,SENT_BY,MERGE_MESSAGE,USER_NAME,SENT_DATE,SENT_DATE_STR,FROM_STAGE_ID,IS_EXTERNAL,SENT_TO_STAGE_ID,SENT_TO,ROUTING_COMMENTS,USR_ACCESS,REQUEST,PROGRESS_MODE,APPROVAL_OF_STAGE,STAGE_TYPE,STAGE_ID,PROGRESS_AND_CLOSURE,OBJECT_ID,OBJECT_TYPE,WF_TEAM_ID,WF_TEAM_NAME,OBJECT_GUID_UNGUIDED,DUE_DATE,SEQUENCE,MIN_VALUE,PLAN_DATE_WFRH,IDOC_ID,INT_REV_NO,DOC_REV_SERIES_ID,REV_NO_INDEX,REV_NO_VALUE,DOC_NO,VERSION_NO,REV_REQ,DOC_DESCRIPTION,ORIGINAL_F_NAME,FILE_ID,SHEET_NO,NO_OF_SHEETS,COUT_BY,COUT_STATUS,FILE_ICONS,COUT_ON,COUT_MCID,COUT_PATH,COUT_COMMENT,CIN_COMMENT,DOC_STATUS,FILE_TYPE,DOC_TYPE,ORDER_ID,SRV_ID,CREATED_BY,CREATED_ON,APPROVED_BY,APPROVED_ON,RELEASED_BY,RELEASED_ON,LAST_EDITED_BY,LAST_EDITED_ON,GENO_KEY,CODE,DOC_ORG_DESC,SHT_SIZE,SHT_DESC,ASSOCIATION_STATUS,ORDER_NO,ORDER_NAME,LCS_USER_REV_NO,GENEALOGY_STRING,CLIENT_DOC_NO,VENDOR_DOC_NO,INT_APPR_STATUS_CODE,EXT_AGENCY_TRN_NO,EXT_AGENCY_TRN_RCPT_DATE,EXPECTED_RCPT_DATE,APP_STAT_CODE,IS_PUBLISHED,PERCENTAGE_COMPLETED_DOC,LCS_CODE,SHEET_SIZE,RECIVE_BACK_ON,RECIVE_BACK_ON_STR,FILE_COMPOSITION,ORDER_DESCRIPTION,ORDER_SHORT_DESCRIPTION,PROJECT_ENQUIRY FROM (SELECT TOP 10000 FILE_INDEX.DOC_TYPE as DOC_BRAND1,DSM.DOC_STATUS_DESC AS DOC_STATUS_STR,
NULL AS IS_TRANS, WTSD.STAGE_NAME AS STAGE_NAME,
CASE WR.ROUTE_SUB_TYPE WHEN 1 THEN 'CC' WHEN 0 THEN 'SEND A COPY' ELSE WTSD.STAGE_NAME END as DISPLAY_STAGE_NAME,
WTSD. APPROVAL_OF_PARALLEL_STAGES, WTSD.ACCEPTED, WTSD.REJECTED, CASE ROUTE_SUB_TYPE WHEN 0 THEN 'Forward' WHEN 1
THEN 'For Information'WHEN 2 THEN 'For Action' END AS OPERATION_TYPE, WTSD.MOREINFO, WTSD.TIME_ALLOT, WTSD.IS_COND,
WTM.WF_ID,WTM.WF_TEAM_NAME,WR.ROUTE_SUB_TYPE AS SENT_TO_WS, (CASE WHEN doc_info.ASSOCIATION_STATUS = 0 AND DRD.DOC_COUNT IS NULL AND
DXREF.XREF_COUNT IS NULL THEN 0 WHEN doc_info.ASSOCIATION_STATUS > 0 OR DRD.DOC_COUNT IS NULL OR DXREF.XREF_COUNT IS NULL THEN 1
ELSE 0 END) HAS_FILES_ATTACHED, DRD.DOC_COUNT REFERRED_DOC_COUNT, DXREF.XREF_COUNT, (CASE WHEN DC.MAIL_DIRECTION=0 AND
FILE_INDEX.DOC_TYPE=2 THEN 'Incoming' WHEN DC.MAIL_DIRECTION=1 AND FILE_INDEX.DOC_TYPE=2 THEN 'Outgoing' ELSE '' END) CORRESPONDANCE_TYPE,
(CASE WHEN DC.REPLY_REQUIRED=0 AND FILE_INDEX.DOC_TYPE=2 THEN 'NO' WHEN DC. REPLY_REQUIRED<>0 AND FILE_INDEX.DOC_TYPE=2
THEN 'YES' ELSE '' END) REPLY_REQUIRED,DC.EXPECTED_REPLY_DATE, (CASE WHEN DC.MAIL_DIRECTION=0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.EXPECTED_REPLY_DATE
WHEN DC.MAIL_DIRECTION<>0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.EXPECTED_REPLY_RECEIPT_DATE ELSE NULL END) AS RCV_BACK_DATE,DC.REPLAY_EXPECTED_FROM,
DC.TO_BE_REPLIED_BY,DC.LETTER_DATE,DC.CORR_ISSUE_DATE,(CASE WHEN DC.MAIL_DIRECTION=0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.CORR_RECEIPT_DATE
WHEN DC.MAIL_DIRECTION<>0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.CORR_ISSUE_DATE ELSE NULL END) AS RECEIPT_DATE,
CASE WHEN ISNULL(ORDER_APP_STATUS_CODES.DESCRIPTION,'')= ''
THEN APPROV_STAT_MSTR.DESCRIPTION
ELSE ORDER_APP_STATUS_CODES.DESCRIPTION
END DESCRIPTION ,
ISNULL(ORDER_LCS.STATUS_DESC, LIFECYCLE_STATUS_MASTER.STATUS_DESC ) STATUS_DESC,ISNULL(ORDER_INT_STATUS_CODES.INT_STATUS_DESC,INT_STAT_MSTR.INTR_STAT_DESC) AS INTR_STAT_DESC,
DC.REPLAY_RECEIPT_DATE, DC.AGEING_DAYS_PRIORITY, (CASE WHEN DOC_INFO.ORDER_ID=-2 THEN (SELECT DM.CLIENT_CODE FROM DP_LEVEL_DOC_APPR_MAP DM WHERE DOC_INFO.GENO_KEY = DM.GENO_KEY
AND DM.APP_STAT_CODE = doc_info.APP_STAT_CODE) ELSE (SELECT CLIENT_CODE FROM ORDER_APP_STATUS_CODES OASC WHERE doc_info.ORDER_ID = OASC.ORDER_ID
AND doc_info.APP_STAT_CODE = OASC.APP_STAT_CODE ) END) CLIENT_CODE, (CASE WHEN doc_info.IS_PUBLISHED =1 THEN 'Yes' ELSE 'No' END) PUBLISH_TYPE_NEW,
CASE WHEN WR.ITEM_AVAILABLE_WITH =1 THEN 'Me Only' ELSE 'Me and Others' END ITEM_AVAILABLE_WITH, WR.ROUTE_SUB_TYPE,
WR.ROUTING_ID, WR.ROUTING_SUB_ID, WR.RUN_ID, WR.GUIDED_UNGUIDED,WR.IS_READ,(CASE WHEN WR.IS_READ = 1 THEN 'Read'
ELSE 'Unread' END) READ_UNREAD,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_BY_ORIGIN THEN WRSS.SENT_BY_ORIGIN ELSE WR.SENT_BY END SENT_BY,
WR.REQUEST+ CHAR(10) + WR.SENT_BY as MERGE_MESSAGE,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_BY_ORIGIN THEN WRSS.SENT_BY_ORIGIN ELSE UM.USER_NAME END USER_NAME,
WR.SENT_DATE,WR.SENT_DATE AS SENT_DATE_STR,
WR.FROM_STAGE_ID, WRSS.IS_EXTERNAL, WR.SENT_TO_STAGE_ID,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_TO_ORIGIN THEN WRSS.SENT_TO_ORIGIN ELSE WR.SENT_TO END SENT_TO,
ISNULL(MCM.MESSAGE_CONTENT,WR.ROUTING_COMMENTS) AS ROUTING_COMMENTS, WR.USR_ACCESS, WR.REQUEST, WR.PROGRESS_MODE,
WTSD.APPROVAL_OF_STAGE,WTSD.STAGE_TYPE, WR.SENT_TO_STAGE_ID STAGE_ID, WR.PROGRESS_AND_CLOSURE,WR1.OBJECT_ID,WR1.OBJECT_TYPE, WR1.WF_TEAM_ID, WR.GUIDED_UNGUIDED AS OBJECT_GUID_UNGUIDED, WR.DUE_DATE,SEQUENCE,
WR.MIN_VALUE, WRSS.IS_HELD,WR.PLAN_DATE as PLAN_DATE_WFRH ,
DOC_INFO.IDOC_ID, DOC_INFO.IDOC_ID AS ITEM_ID, DOC_INFO.INT_REV_NO, DOC_INFO.REV_SERIES_ID AS DOC_REV_SERIES_ID, DOC_INFO.REV_NO_INDEX,
DOC_INFO.REV_NO_VALUE, DOC_INFO.DOC_NO, DOC_INFO.VERSION_NO, DOC_INFO.REV_REQ, ISNULL(DOC_INFO.DOC_DESCRIPTION, '')
AS DOC_DESCRIPTION, FILE_INDEX.ORIGINAL_F_NAME, DOC_INFO.FILE_ID, DOC_INFO.SHEET_NO, DOC_INFO.NO_OF_SHEETS,
FILE_INDEX.COUT_BY, CASE ISNULL(COUT_BY, '') WHEN '' THEN 'IN' ELSE 'OUT' END AS COUT_STATUS, CASE ISNULL(COUT_BY, '')
WHEN '' THEN (CASE ISNULL(FILE_TYPE, '') WHEN '' THEN '.UNKNOWN' ELSE CASE FILE_COMPOSITION WHEN 1 THEN '.FOLDER' ELSE FILE_INDEX.FILE_TYPE END END) ELSE '.CHECKOUTICON' END
AS FILE_ICONS, FILE_INDEX.COUT_ON, FILE_INDEX.COUT_MCID, FILE_INDEX.COUT_PATH, FILE_INDEX.COUT_COMMENT,
FILE_INDEX.CIN_COMMENT, DOC_INFO.DOC_STATUS, FILE_INDEX.FILE_TYPE, FILE_INDEX.DOC_TYPE, DOC_INFO.ORDER_ID,
FILE_INDEX.GPS_LOCATION,FILE_INDEX.GPS_ALTITUDE,FILE_INDEX.GPS_LONGITUDE,FILE_INDEX.GPS_LATITUDE,
DOC_INFO.SRV_ID, DOC_INFO.CREATED_BY, DOC_INFO.CREATED_ON, DOC_INFO.APPROVED_BY, DOC_INFO.APPROVED_ON,
DOC_INFO.RELEASED_BY, DOC_INFO.RELEASED_ON, DOC_INFO.LAST_EDITED_BY, DOC_INFO.LAST_EDITED_ON, DOC_INFO.GENO_KEY,
DOC_ORG_MSTR.CODE, DOC_ORG_MSTR.DESCR AS DOC_ORG_DESC, DOC_SIZE_MSTR.SHT_SIZE, DOC_SIZE_MSTR.SHT_DESC,
DOC_INFO.ASSOCIATION_STATUS,ORDER_MASTER.ORDER_NO,
CASE DOC_INFO.ORDER_ID WHEN - 2 THEN 'Project Independent' ELSE ORDER_MASTER.ORDER_DESCRIPTION END AS ORDER_NAME,
CASE WHEN ISNULL(DOC_INFO.USER_REV_NO_VALUE, '') = ''
THEN DOC_INFO.REV_NO_VALUE ELSE DOC_INFO.USER_REV_NO_VALUE END AS LCS_USER_REV_NO, GENEALOGY.GENEALOGY_STRING,
DOC_INFO.CLIENT_DOC_NO, DOC_INFO.VENDOR_DOC_NO, DOC_INFO.INT_APPR_STATUS_CODE, DOC_INFO.EXT_AGENCY_TRN_NO,
DOC_INFO.EXT_AGENCY_TRN_RCPT_DATE, DOC_INFO.EXPECTED_RCPT_DATE, DOC_INFO.APP_STAT_CODE, DOC_INFO.IS_PUBLISHED,
DOC_INFO.PERCENTAGE_COMPLETED_DOC, DOC_INFO.LCS_CODE, DOC_INFO.SHEET_SIZE, DOC_INFO.RECIVE_BACK_ON,
DOC_INFO.RECIVE_BACK_ON AS RECIVE_BACK_ON_STR, DOC_INFO.FILE_COMPOSITION,
ORDER_MASTER.ORDER_DESCRIPTION, ORDER_MASTER.ORDER_SHORT_DESCRIPTION, PROJECT_ENQUIRY ,CHAR_10_000,CHAR_105_000,CHAR_106_000,CHAR_107_000,CHAR_108_000,CHAR_109_000,CHAR_110_000,CHAR_111_000,CHAR_112_000,CHAR_113_000,CHAR_114_000,CHAR_115_000,CHAR_116_000,CHAR_117_000,CHAR_118_000,CHAR_119_000,CHAR_120_000,CHAR_121_000,CHAR_122_000,CHAR_123_000,CHAR_124_000,CHAR_125_000,CHAR_126_000,CHAR_127_000,CHAR_128_000,CHAR_129_000,CHAR_130_000,CHAR_131_000,CHAR_132_000,CHAR_133_000,CHAR_134_000,CHAR_135_000,CHAR_136_000,CHAR_137_000,CHAR_138_000,CHAR_139_000,CHAR_141_000,CHAR_142_000,CHAR_143_000,CHAR_144_000,CHAR_145_000,CHAR_146_000,CHAR_147_000,CHAR_148_000,CHAR_149_000,CHAR_150_000,CHAR_151_000,CHAR_152_000,CHAR_153_000,CHAR_154_000,CHAR_155_000,CHAR_156_000,CHAR_157_000,CHAR_158_000,CHAR_159_000,CHAR_160_000,CHAR_161_000,CHAR_162_000,CHAR_163_000,CHAR_164_000,CHAR_165_000,CHAR_166_000,CHAR_167_000,CHAR_168_000,CHAR_169_000,CHAR_17_000,CHAR_170_000,CHAR_171_000,CHAR_172_000,CHAR_173_000,CHAR_174_000,CHAR_175_000,CHAR_176_000,CHAR_177_000,CHAR_178_000,CHAR_179_000,CHAR_18_000,CHAR_180_000,CHAR_181_000,CHAR_184_000,CHAR_185_000,CHAR_186_000,CHAR_188_000,CHAR_189_000,CHAR_19_000,CHAR_190_000,CHAR_191_000,CHAR_192_000,CHAR_193_000,CHAR_194_000,CHAR_195_000,CHAR_196_000,CHAR_197_000,CHAR_198_000,CHAR_199_000,CHAR_20_000,CHAR_200_000,CHAR_201_000,CHAR_202_000,CHAR_203_000,CHAR_204_000,CHAR_205_000,CHAR_206_000,CHAR_207_000,CHAR_215_000,CHAR_216_000,CHAR_22_000,CHAR_226_000,CHAR_231_000,CHAR_235_000,CHAR_236_000,CHAR_237_000,CHAR_239_000,CHAR_240_000,CHAR_242_000,CHAR_243_000,CHAR_244_000,CHAR_245_000,CHAR_246_000,CHAR_247_000,CHAR_248_000,CHAR_249_000,CHAR_250_000,CHAR_251_000,CHAR_252_000,CHAR_253_000,CHAR_254_000,CHAR_255_000,CHAR_256_000,CHAR_257_000,CHAR_258_000,CHAR_259_000,CHAR_260_000,CHAR_261_000,CHAR_262_000,CHAR_263_000,CHAR_264_000,CHAR_265_000,CHAR_266_000,CHAR_267_000,CHAR_268_000,CHAR_269_000,CHAR_270_000,CHAR_271_000,CHAR_272_000,CHAR_274_000,CHAR_275_000,CHAR_276_000,CHAR_277_000,CHAR_278_000,CHAR_279_000,CHAR_280_000,CHAR_281_000,CHAR_282_000,CHAR_283_000,CHAR_284_000,CHAR_285_000,CHAR_286_000,CHAR_287_000,CHAR_288_000,CHAR_289_000,CHAR_290_000,CHAR_291_000,CHAR_292_000,CHAR_293_000,CHAR_294_000,CHAR_295_000,CHAR_296_000,CHAR_297_000,CHAR_298_000,CHAR_299_000,CHAR_300_000,CHAR_301_000,CHAR_302_000,CHAR_303_000,CHAR_304_000,CHAR_305_000,CHAR_306_000,CHAR_307_000,CHAR_308_000,CHAR_309_000,CHAR_310_000,CHAR_312_000,CHAR_313_000,CHAR_314_000,CHAR_315_000,CHAR_316_000,CHAR_317_000,CHAR_319_000,CHAR_320_000,CHAR_322_000,CHAR_323_000,CHAR_324_000,CHAR_325_000,CHAR_326_000,CHAR_329_000,CHAR_33_000,CHAR_330_000,CHAR_331_000,CHAR_332_000,CHAR_333_000,CHAR_334_000,CHAR_335_000,CHAR_336_000,CHAR_337_000,CHAR_338_000,CHAR_339_000,CHAR_34_000,CHAR_341_000,CHAR_342_000,CHAR_343_000,CHAR_344_000,CHAR_345_000,CHAR_346_000,CHAR_347_000,CHAR_348_000,CHAR_349_000,CHAR_35_000,CHAR_350_000,CHAR_351_000,CHAR_352_000,CHAR_353_000,CHAR_354_000,CHAR_355_000,CHAR_356_000,CHAR_357_000,CHAR_358_000,CHAR_359_000,CHAR_36_000,CHAR_360_000,CHAR_361_000,CHAR_362_000,CHAR_363_000,CHAR_364_000,CHAR_365_000,CHAR_366_000,CHAR_367_000,CHAR_368_000,CHAR_369_000,CHAR_37_000,CHAR_372_000,CHAR_373_000,CHAR_374_000,CHAR_375_000,CHAR_376_000,CHAR_378_000,CHAR_379_000,CHAR_38_000,CHAR_380_000,CHAR_381_000,CHAR_382_000,CHAR_385_000,CHAR_386_000,CHAR_387_000,CHAR_388_000,CHAR_389_000,CHAR_39_000,CHAR_390_000,CHAR_391_000,CHAR_392_000,CHAR_393_000,CHAR_395_000,CHAR_396_000,CHAR_397_000,CHAR_398_000,CHAR_399_000,CHAR_40_000,CHAR_400_000,CHAR_401_000,CHAR_402_000,CHAR_403_000,CHAR_404_000,CHAR_405_000,CHAR_406_000,CHAR_407_000,CHAR_408_000,CHAR_409_000,CHAR_41_000,CHAR_410_000,CHAR_411_000,CHAR_412_000,CHAR_413_000,CHAR_414_000,CHAR_415_000,CHAR_416_000,CHAR_417_000,CHAR_419_000,CHAR_420_000,CHAR_421_000,CHAR_422_000,CHAR_423_000,CHAR_426_000,CHAR_427_000,CHAR_432_000,CHAR_433_000,CHAR_434_000,CHAR_435_000,CHAR_436_000,CHAR_437_000,CHAR_438_000,CHAR_439_000,CHAR_440_000,CHAR_441_000,CHAR_442_000,CHAR_443_000,CHAR_444_000,CHAR_445_000,CHAR_446_000,CHAR_447_000,CHAR_448_000,CHAR_449_000,CHAR_450_000,CHAR_452_000,CHAR_453_000,CHAR_454_000,CHAR_455_000,CHAR_456_000,CHAR_457_000,CHAR_458_000,CHAR_459_000,CHAR_460_000,CHAR_461_000,CHAR_462_000,CHAR_463_000,CHAR_464_000,CHAR_466_000,CHAR_467_000,CHAR_468_000,CHAR_469_000,CHAR_47_000,CHAR_470_000,CHAR_473_000,CHAR_477_000,CHAR_478_000,CHAR_479_000,CHAR_48_000,CHAR_489_000,CHAR_49_000,CHAR_490_000,CHAR_493_000,CHAR_495_000,CHAR_496_000,CHAR_499_000,CHAR_50_000,CHAR_500_000,CHAR_501_000,CHAR_504_000,CHAR_505_000,CHAR_506_000,CHAR_507_000,CHAR_508_000,CHAR_509_000,CHAR_510_000,CHAR_511_000,CHAR_512_000,CHAR_513_000,CHAR_514_000,CHAR_515_000,CHAR_516_000,CHAR_517_000,CHAR_518_000,CHAR_519_000,CHAR_52_000,CHAR_520_000,CHAR_521_000,CHAR_522_000,CHAR_524_000,CHAR_525_000,CHAR_526_000,CHAR_527_000,CHAR_528_000,CHAR_529_000,CHAR_53_000,CHAR_530_000,CHAR_531_000,CHAR_532_000,CHAR_533_000,CHAR_534_000,CHAR_539_000,CHAR_54_000,CHAR_548_000,CHAR_549_000,CHAR_55_000,CHAR_550_000,CHAR_551_000,CHAR_552_000,CHAR_553_000,CHAR_554_000,CHAR_555_000,CHAR_556_000,CHAR_557_000,CHAR_558_000,CHAR_559_000,CHAR_56_000,CHAR_561_000,CHAR_562_000,CHAR_563_000,CHAR_564_000,CHAR_565_000,CHAR_566_000,CHAR_567_000,CHAR_568_000,CHAR_569_000,CHAR_57_000,CHAR_570_000,CHAR_575_000,CHAR_576_000,CHAR_577_000,CHAR_579_000,CHAR_58_000,CHAR_580_000,CHAR_581_000,CHAR_582_000,CHAR_583_000,CHAR_588_000,CHAR_589_000,CHAR_590_000,CHAR_591_000,CHAR_592_000,CHAR_593_000,CHAR_594_000,CHAR_595_000,CHAR_596_000,CHAR_597_000,CHAR_598_000,CHAR_599_000,CHAR_60_000,CHAR_600_000,CHAR_601_000,CHAR_602_000,CHAR_603_000,CHAR_604_000,CHAR_605_000,CHAR_606_000,CHAR_607_000,CHAR_608_000,CHAR_609_000,CHAR_61_000,CHAR_610_000,CHAR_611_000,CHAR_62_000,CHAR_63_000,CHAR_64_000,CHAR_65_000,CHAR_66_000,CHAR_67_000,CHAR_68_000,CHAR_69_000,CHAR_70_000,CHAR_71_000,CHAR_72_000,CHAR_73_000,CHAR_74_000,CHAR_75_000,CHAR_76_000,CHAR_78_000,CHAR_80_000,CHAR_81_000,CHAR_83_000,CHAR_85_000,CHAR_86_000,CHAR_87_000,CHAR_88_000,CHAR_89_000,CHAR_96_000,CHAR_97_000,CHAR_98_000,CHAR_99_000,
--TMP.C_TRANS_REF_NO, TMP.C_TRANS_TYPE_CODE,TMP.C_RELEASED_ON, ISNULL(TMP_PREV.T_ACKNOWLEDGE_ON,TMP.T_ACKNOWLEDGE_ON) AS T_ACKNOWLEDGE_ON,
--TMP_PREV.P_TRANS_REF_NO,TMP_PREV.P_TRANS_TYPE_CODE,TMP_PREV.P_RELEASED_ON,
TM_CUR.TRANS_REF_NO C_TRANS_REF_NO, TM_CUR.TRANS_TYPE_CODE C_TRANS_TYPE_CODE,TM_CUR.RELEASED_ON C_RELEASED_ON, TM_CUR.CREATED_ON C_CREATED_ON,ISNULL(TM_PREV.ACKNOWLEDGE_ON,TM_CUR.ACKNOWLEDGE_ON) AS T_ACKNOWLEDGE_ON,
TM_PREV.TRANS_REF_NO P_TRANS_REF_NO,TM_PREV.TRANS_TYPE_CODE P_TRANS_TYPE_CODE,TM_PREV.RELEASED_ON P_RELEASED_ON,TM_PREV.CREATED_ON P_CREATED_ON,
TM_CUR_SAVED.TRANS_REF_NO CUR_SAVED_TRANS_REF_NO,TM_CUR_SAVED.TRANS_TYPE_CODE CUR_SAVED_TRANS_TYPE_CODE,TM_CUR_SAVED.CREATED_ON CUR_SAVED_CREATED_ON,
CLIENT_REF,DOC_INFO.EXPECTED_RECEIVE_BACK_DATE ,WR.IS_FORWARDED
FROM WF_RUN_ROUTING_HISTORY WR
INNER JOIN WF_RUN WR1 ON WR.RUN_ID = WR1.RUN_ID AND WR.STATUS = 0
INNER JOIN WF_RUN_STAGE_STATUS WRSS ON WRSS.RUN_ID = WR.RUN_ID AND WRSS.ROUTING_ID = WR.ROUTING_ID
INNER JOIN DOC_INFO ON DOC_INFO.IDOC_ID = WR1.OBJECT_ID
INNER JOIN GENEALOGY ON GENEALOGY.GENO_KEY = DOC_INFO.GENO_KEY
INNER JOIN FILE_INDEX ON FILE_INDEX.FILE_ID = DOC_INFO.FILE_ID
INNER JOIN USER_MSTR UM ON UM.LOGIN_NAME = WR.SENT_BY
LEFT JOIN MESSAGE_CONTENT_MASTER MCM ON MCM.MESSAGE_CONTENT_ID=WR.ROUTING_COMMENTS_ID
LEFT JOIN WF_TEAM_MST WTM ON WTM.WF_TEAM_ID = WR1.WF_TEAM_ID
LEFT JOIN WF_TEAM_STAGE_DET WTSD ON WTSD.WF_TEAM_ID = WR1.WF_TEAM_ID AND WTSD.STAGE_ID = WR.SENT_TO_STAGE_ID
LEFT JOIN ORDER_MASTER ON ORDER_MASTER.ORDER_ID = DOC_INFO.ORDER_ID
LEFT JOIN DP_LEVEL_DOC ON DP_LEVEL_DOC.GENO_KEY = DOC_INFO.GENO_KEY
LEFT JOIN DOC_ORG_MSTR ON DOC_ORG_MSTR.ID = DP_LEVEL_DOC.DOC_ORIGIN_CODE
LEFT JOIN DOC_SIZE_MSTR ON DOC_SIZE_MSTR.SHT_SIZE = DOC_INFO.SHEET_SIZE
LEFT JOIN DOC_CORRESPONDENCE DC ON DC.IDOC_ID = DOC_INFO.IDOC_ID
LEFT JOIN APPROV_STAT_MSTR ON APPROV_STAT_MSTR.APP_STAT_CODE =DOC_INFO.APP_STAT_CODE
LEFT JOIN LIFECYCLE_STATUS_MASTER ON LIFECYCLE_STATUS_MASTER.STATUS_CODE = DOC_INFO.LCS_CODE
LEFT JOIN INT_STAT_MSTR ON INT_STAT_MSTR.INTR_STAT_CODE = DOC_INFO.INT_APPR_STATUS_CODE
LEFT JOIN ORDER_INT_STATUS_CODES ON ORDER_INT_STATUS_CODES.ORDER_ID=ORDER_MASTER.ORDER_ID AND ORDER_INT_STATUS_CODES.INT_STATUS_CODE = INT_STAT_MSTR.INTR_STAT_CODE
LEFT JOIN ORDER_LCS ON ORDER_LCS.ORDER_ID=ORDER_MASTER.ORDER_ID AND ORDER_LCS.STATUS_CODE = DOC_INFO.LCS_CODE
LEFT JOIN (SELECT COUNT(IDOC_ID) AS DOC_COUNT,IDOC_ID FROM DOC_REF_DOC GROUP BY IDOC_ID) AS DRD ON DRD.IDOC_ID=DOC_INFO.IDOC_ID
LEFT JOIN (SELECT COUNT(IDOC_ID) AS XREF_COUNT ,IDOC_ID,INT_REV_NO FROM DOC_INFO_LINKS GROUP BY IDOC_ID,INT_REV_NO) AS DXREF
ON DXREF.IDOC_ID=DOC_INFO.IDOC_ID AND DXREF.INT_REV_NO =DOC_INFO.INT_REV_NO
LEFT JOIN CUSTOM_DOC_CHAR_DETAILS CUSTOM_OBJECT_CHAR_DETAILS ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID
LEFT JOIN EXTENDED_DOC_CHAR_DETAILS EXTENDED_DOC_CHAR_DETAILS ON EXTENDED_DOC_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID
--OUTER APPLY (SELECT TOP 1 TM.TRANS_ID,TDD.IDOC_ID,TM.TRANS_REF_NO AS C_TRANS_REF_NO,TM.TRANS_TYPE_CODE AS C_TRANS_TYPE_CODE,TM.RELEASED_ON AS C_RELEASED_ON,TM.ACKNOWLEDGE_ON AS T_ACKNOWLEDGE_ON FROM TRANS_DOC_DET TDD
--INNER JOIN TRANSMITTAL_MST TM ON TM.TRANS_ID=TDD.TRANS_ID AND (TM.TRANS_STATUS=1 OR TM.TRANS_STATUS=2) WHERE TDD.IDOC_ID=WR.OBJECT_ID ORDER BY RELEASED_ON DESC) AS TMP
--OUTER APPLY (SELECT TOP 1 TDD.IDOC_ID,TM.TRANS_REF_NO AS P_TRANS_REF_NO,TM.TRANS_TYPE_CODE AS P_TRANS_TYPE_CODE,TM.RELEASED_ON AS P_RELEASED_ON,TM.ACKNOWLEDGE_ON AS T_ACKNOWLEDGE_ON FROM TRANS_DOC_DET TDD
--INNER JOIN TRANSMITTAL_MST TM ON TM.TRANS_ID=TDD.TRANS_ID AND (TM.TRANS_STATUS=1 OR TM.TRANS_STATUS=2) AND TM.TRANS_ID <> TMP.TRANS_ID AND TDD.IDOC_ID=WR.OBJECT_ID ORDER BY RELEASED_ON DESC) AS TMP_PREV
LEFT JOIN TRANSMITTAL_MST TM_CUR ON TM_CUR.TRANS_ID = DOC_INFO.CUR_TRANS_ID
LEFT JOIN TRANSMITTAL_MST TM_CUR_SAVED ON TM_CUR_SAVED.TRANS_ID = DOC_INFO.CUR_SAVED_TRANS_ID
LEFT JOIN TRANSMITTAL_MST TM_PREV ON TM_PREV.TRANS_ID = DOC_INFO.PREV_TRANS_ID
INNER JOIN DOC_STATUS_MSTR DSM ON DSM.DOC_STATUS_ID = DOC_INFO.DOC_STATUS
LEFT JOIN ORDER_APP_STATUS_CODES ON DOC_INFO.APP_STAT_CODE = ORDER_APP_STATUS_CODES.APP_STAT_CODE
AND DOC_INFO.ORDER_ID = ORDER_APP_STATUS_CODES.ORDER_ID
WHERE WR.STATUS = 0
--AND WR.ROUTE_STATUS = 0
AND WR.SENT_TO = N'105057'
AND WR1.OBJECT_TYPE = 'DOC' AND WR.IS_ACTIVE_SEQ = 1 ORDER BY WR.SENT_DATE DESC ) A
<$W>Execution Time: Milliseconds. (11:15:35.999 AM)
----------------------------------------------------------------------------------------------------------
<$W>GetDocumentWorkspace 105057 08-Oct-2025 11:53:54:5874 AM
<$W>SELECT DOC_BRAND1,DOC_STATUS_STR,IS_TRANS,STAGE_NAME,DISPLAY_STAGE_NAME,APPROVAL_OF_PARALLEL_STAGES,ACCEPTED,REJECTED,OPERATION_TYPE,MOREINFO,TIME_ALLOT,IS_COND,WF_ID,SENT_TO_WS,HAS_FILES_ATTACHED,REFERRED_DOC_COUNT,XREF_COUNT,CORRESPONDANCE_TYPE,REPLY_REQUIRED,EXPECTED_REPLY_DATE,RCV_BACK_DATE,REPLAY_EXPECTED_FROM,TO_BE_REPLIED_BY,LETTER_DATE,CORR_ISSUE_DATE,RECEIPT_DATE,DESCRIPTION,STATUS_DESC,INTR_STAT_DESC,REPLAY_RECEIPT_DATE,CLIENT_CODE,PUBLISH_TYPE_NEW,ITEM_AVAILABLE_WITH,ROUTE_SUB_TYPE,ROUTING_ID,ROUTING_SUB_ID,RUN_ID,GUIDED_UNGUIDED,IS_READ,READ_UNREAD,SENT_BY,MERGE_MESSAGE,USER_NAME,SENT_DATE,SENT_DATE_STR,FROM_STAGE_ID,IS_EXTERNAL,SENT_TO_STAGE_ID,SENT_TO,ROUTING_COMMENTS,USR_ACCESS,REQUEST,PROGRESS_MODE,APPROVAL_OF_STAGE,STAGE_TYPE,STAGE_ID,PROGRESS_AND_CLOSURE,OBJECT_ID,OBJECT_TYPE,WF_TEAM_ID,WF_TEAM_NAME,OBJECT_GUID_UNGUIDED,DUE_DATE,SEQUENCE,MIN_VALUE,PLAN_DATE_WFRH,IDOC_ID,INT_REV_NO,DOC_REV_SERIES_ID,REV_NO_INDEX,REV_NO_VALUE,DOC_NO,VERSION_NO,REV_REQ,DOC_DESCRIPTION,ORIGINAL_F_NAME,FILE_ID,SHEET_NO,NO_OF_SHEETS,COUT_BY,COUT_STATUS,FILE_ICONS,COUT_ON,COUT_MCID,COUT_PATH,COUT_COMMENT,CIN_COMMENT,DOC_STATUS,FILE_TYPE,DOC_TYPE,ORDER_ID,SRV_ID,CREATED_BY,CREATED_ON,APPROVED_BY,APPROVED_ON,RELEASED_BY,RELEASED_ON,LAST_EDITED_BY,LAST_EDITED_ON,GENO_KEY,CODE,DOC_ORG_DESC,SHT_SIZE,SHT_DESC,ASSOCIATION_STATUS,ORDER_NO,ORDER_NAME,LCS_USER_REV_NO,GENEALOGY_STRING,CLIENT_DOC_NO,VENDOR_DOC_NO,INT_APPR_STATUS_CODE,EXT_AGENCY_TRN_NO,EXT_AGENCY_TRN_RCPT_DATE,EXPECTED_RCPT_DATE,APP_STAT_CODE,IS_PUBLISHED,PERCENTAGE_COMPLETED_DOC,LCS_CODE,SHEET_SIZE,RECIVE_BACK_ON,RECIVE_BACK_ON_STR,FILE_COMPOSITION,ORDER_DESCRIPTION,ORDER_SHORT_DESCRIPTION,PROJECT_ENQUIRY FROM (SELECT TOP 10000 FILE_INDEX.DOC_TYPE as DOC_BRAND1,DSM.DOC_STATUS_DESC AS DOC_STATUS_STR,
NULL AS IS_TRANS, WTSD.STAGE_NAME AS STAGE_NAME,
CASE WR.ROUTE_SUB_TYPE WHEN 1 THEN 'CC' WHEN 0 THEN 'SEND A COPY' ELSE WTSD.STAGE_NAME END as DISPLAY_STAGE_NAME,
WTSD. APPROVAL_OF_PARALLEL_STAGES, WTSD.ACCEPTED, WTSD.REJECTED, CASE ROUTE_SUB_TYPE WHEN 0 THEN 'Forward' WHEN 1
THEN 'For Information'WHEN 2 THEN 'For Action' END AS OPERATION_TYPE, WTSD.MOREINFO, WTSD.TIME_ALLOT, WTSD.IS_COND,
WTM.WF_ID,WTM.WF_TEAM_NAME,WR.ROUTE_SUB_TYPE AS SENT_TO_WS, (CASE WHEN doc_info.ASSOCIATION_STATUS = 0 AND DRD.DOC_COUNT IS NULL AND
DXREF.XREF_COUNT IS NULL THEN 0 WHEN doc_info.ASSOCIATION_STATUS > 0 OR DRD.DOC_COUNT IS NULL OR DXREF.XREF_COUNT IS NULL THEN 1
ELSE 0 END) HAS_FILES_ATTACHED, DRD.DOC_COUNT REFERRED_DOC_COUNT, DXREF.XREF_COUNT, (CASE WHEN DC.MAIL_DIRECTION=0 AND
FILE_INDEX.DOC_TYPE=2 THEN 'Incoming' WHEN DC.MAIL_DIRECTION=1 AND FILE_INDEX.DOC_TYPE=2 THEN 'Outgoing' ELSE '' END) CORRESPONDANCE_TYPE,
(CASE WHEN DC.REPLY_REQUIRED=0 AND FILE_INDEX.DOC_TYPE=2 THEN 'NO' WHEN DC. REPLY_REQUIRED<>0 AND FILE_INDEX.DOC_TYPE=2
THEN 'YES' ELSE '' END) REPLY_REQUIRED,DC.EXPECTED_REPLY_DATE, (CASE WHEN DC.MAIL_DIRECTION=0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.EXPECTED_REPLY_DATE
WHEN DC.MAIL_DIRECTION<>0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.EXPECTED_REPLY_RECEIPT_DATE ELSE NULL END) AS RCV_BACK_DATE,DC.REPLAY_EXPECTED_FROM,
DC.TO_BE_REPLIED_BY,DC.LETTER_DATE,DC.CORR_ISSUE_DATE,(CASE WHEN DC.MAIL_DIRECTION=0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.CORR_RECEIPT_DATE
WHEN DC.MAIL_DIRECTION<>0 AND FILE_INDEX.DOC_TYPE=2 THEN DC.CORR_ISSUE_DATE ELSE NULL END) AS RECEIPT_DATE,
CASE WHEN ISNULL(ORDER_APP_STATUS_CODES.DESCRIPTION,'')= ''
THEN APPROV_STAT_MSTR.DESCRIPTION
ELSE ORDER_APP_STATUS_CODES.DESCRIPTION
END DESCRIPTION ,
ISNULL(ORDER_LCS.STATUS_DESC, LIFECYCLE_STATUS_MASTER.STATUS_DESC ) STATUS_DESC,ISNULL(ORDER_INT_STATUS_CODES.INT_STATUS_DESC,INT_STAT_MSTR.INTR_STAT_DESC) AS INTR_STAT_DESC,
DC.REPLAY_RECEIPT_DATE, DC.AGEING_DAYS_PRIORITY, (CASE WHEN DOC_INFO.ORDER_ID=-2 THEN (SELECT DM.CLIENT_CODE FROM DP_LEVEL_DOC_APPR_MAP DM WHERE DOC_INFO.GENO_KEY = DM.GENO_KEY
AND DM.APP_STAT_CODE = doc_info.APP_STAT_CODE) ELSE (SELECT CLIENT_CODE FROM ORDER_APP_STATUS_CODES OASC WHERE doc_info.ORDER_ID = OASC.ORDER_ID
AND doc_info.APP_STAT_CODE = OASC.APP_STAT_CODE ) END) CLIENT_CODE, (CASE WHEN doc_info.IS_PUBLISHED =1 THEN 'Yes' ELSE 'No' END) PUBLISH_TYPE_NEW,
CASE WHEN WR.ITEM_AVAILABLE_WITH =1 THEN 'Me Only' ELSE 'Me and Others' END ITEM_AVAILABLE_WITH, WR.ROUTE_SUB_TYPE,
WR.ROUTING_ID, WR.ROUTING_SUB_ID, WR.RUN_ID, WR.GUIDED_UNGUIDED,WR.IS_READ,(CASE WHEN WR.IS_READ = 1 THEN 'Read'
ELSE 'Unread' END) READ_UNREAD,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_BY_ORIGIN THEN WRSS.SENT_BY_ORIGIN ELSE WR.SENT_BY END SENT_BY,
WR.REQUEST+ CHAR(10) + WR.SENT_BY as MERGE_MESSAGE,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_BY_ORIGIN THEN WRSS.SENT_BY_ORIGIN ELSE UM.USER_NAME END USER_NAME,
WR.SENT_DATE,WR.SENT_DATE AS SENT_DATE_STR,
WR.FROM_STAGE_ID, WRSS.IS_EXTERNAL, WR.SENT_TO_STAGE_ID,
CASE WHEN N'' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'' <> WRSS.SENT_TO_ORIGIN THEN WRSS.SENT_TO_ORIGIN ELSE WR.SENT_TO END SENT_TO,
ISNULL(MCM.MESSAGE_CONTENT,WR.ROUTING_COMMENTS) AS ROUTING_COMMENTS, WR.USR_ACCESS, WR.REQUEST, WR.PROGRESS_MODE,
WTSD.APPROVAL_OF_STAGE,WTSD.STAGE_TYPE, WR.SENT_TO_STAGE_ID STAGE_ID, WR.PROGRESS_AND_CLOSURE,WR1.OBJECT_ID,WR1.OBJECT_TYPE, WR1.WF_TEAM_ID, WR.GUIDED_UNGUIDED AS OBJECT_GUID_UNGUIDED, WR.DUE_DATE,SEQUENCE,
WR.MIN_VALUE, WRSS.IS_HELD,WR.PLAN_DATE as PLAN_DATE_WFRH ,
DOC_INFO.IDOC_ID, DOC_INFO.IDOC_ID AS ITEM_ID, DOC_INFO.INT_REV_NO, DOC_INFO.REV_SERIES_ID AS DOC_REV_SERIES_ID, DOC_INFO.REV_NO_INDEX,
DOC_INFO.REV_NO_VALUE, DOC_INFO.DOC_NO, DOC_INFO.VERSION_NO, DOC_INFO.REV_REQ, ISNULL(DOC_INFO.DOC_DESCRIPTION, '')
AS DOC_DESCRIPTION, FILE_INDEX.ORIGINAL_F_NAME, DOC_INFO.FILE_ID, DOC_INFO.SHEET_NO, DOC_INFO.NO_OF_SHEETS,
FILE_INDEX.COUT_BY, CASE ISNULL(COUT_BY, '') WHEN '' THEN 'IN' ELSE 'OUT' END AS COUT_STATUS, CASE ISNULL(COUT_BY, '')
WHEN '' THEN (CASE ISNULL(FILE_TYPE, '') WHEN '' THEN '.UNKNOWN' ELSE CASE FILE_COMPOSITION WHEN 1 THEN '.FOLDER' ELSE FILE_INDEX.FILE_TYPE END END) ELSE '.CHECKOUTICON' END
AS FILE_ICONS, FILE_INDEX.COUT_ON, FILE_INDEX.COUT_MCID, FILE_INDEX.COUT_PATH, FILE_INDEX.COUT_COMMENT,
FILE_INDEX.CIN_COMMENT, DOC_INFO.DOC_STATUS, FILE_INDEX.FILE_TYPE, FILE_INDEX.DOC_TYPE, DOC_INFO.ORDER_ID,
FILE_INDEX.GPS_LOCATION,FILE_INDEX.GPS_ALTITUDE,FILE_INDEX.GPS_LONGITUDE,FILE_INDEX.GPS_LATITUDE,
DOC_INFO.SRV_ID, DOC_INFO.CREATED_BY, DOC_INFO.CREATED_ON, DOC_INFO.APPROVED_BY, DOC_INFO.APPROVED_ON,
DOC_INFO.RELEASED_BY, DOC_INFO.RELEASED_ON, DOC_INFO.LAST_EDITED_BY, DOC_INFO.LAST_EDITED_ON, DOC_INFO.GENO_KEY,
DOC_ORG_MSTR.CODE, DOC_ORG_MSTR.DESCR AS DOC_ORG_DESC, DOC_SIZE_MSTR.SHT_SIZE, DOC_SIZE_MSTR.SHT_DESC,
DOC_INFO.ASSOCIATION_STATUS,ORDER_MASTER.ORDER_NO,
CASE DOC_INFO.ORDER_ID WHEN - 2 THEN 'Project Independent' ELSE ORDER_MASTER.ORDER_DESCRIPTION END AS ORDER_NAME,
CASE WHEN ISNULL(DOC_INFO.USER_REV_NO_VALUE, '') = ''
THEN DOC_INFO.REV_NO_VALUE ELSE DOC_INFO.USER_REV_NO_VALUE END AS LCS_USER_REV_NO, GENEALOGY.GENEALOGY_STRING,
DOC_INFO.CLIENT_DOC_NO, DOC_INFO.VENDOR_DOC_NO, DOC_INFO.INT_APPR_STATUS_CODE, DOC_INFO.EXT_AGENCY_TRN_NO,
DOC_INFO.EXT_AGENCY_TRN_RCPT_DATE, DOC_INFO.EXPECTED_RCPT_DATE, DOC_INFO.APP_STAT_CODE, DOC_INFO.IS_PUBLISHED,
DOC_INFO.PERCENTAGE_COMPLETED_DOC, DOC_INFO.LCS_CODE, DOC_INFO.SHEET_SIZE, DOC_INFO.RECIVE_BACK_ON,
DOC_INFO.RECIVE_BACK_ON AS RECIVE_BACK_ON_STR, DOC_INFO.FILE_COMPOSITION,
ORDER_MASTER.ORDER_DESCRIPTION, ORDER_MASTER.ORDER_SHORT_DESCRIPTION, PROJECT_ENQUIRY ,CHAR_10_000,CHAR_105_000,CHAR_106_000,CHAR_107_000,CHAR_108_000,CHAR_109_000,CHAR_110_000,CHAR_111_000,CHAR_112_000,CHAR_113_000,CHAR_114_000,CHAR_115_000,CHAR_116_000,CHAR_117_000,CHAR_118_000,CHAR_119_000,CHAR_120_000,CHAR_121_000,CHAR_122_000,CHAR_123_000,CHAR_124_000,CHAR_125_000,CHAR_126_000,CHAR_127_000,CHAR_128_000,CHAR_129_000,CHAR_130_000,CHAR_131_000,CHAR_132_000,CHAR_133_000,CHAR_134_000,CHAR_135_000,CHAR_136_000,CHAR_137_000,CHAR_138_000,CHAR_139_000,CHAR_141_000,CHAR_142_000,CHAR_143_000,CHAR_144_000,CHAR_145_000,CHAR_146_000,CHAR_147_000,CHAR_148_000,CHAR_149_000,CHAR_150_000,CHAR_151_000,CHAR_152_000,CHAR_153_000,CHAR_154_000,CHAR_155_000,CHAR_156_000,CHAR_157_000,CHAR_158_000,CHAR_159_000,CHAR_160_000,CHAR_161_000,CHAR_162_000,CHAR_163_000,CHAR_164_000,CHAR_165_000,CHAR_166_000,CHAR_167_000,CHAR_168_000,CHAR_169_000,CHAR_17_000,CHAR_170_000,CHAR_171_000,CHAR_172_000,CHAR_173_000,CHAR_174_000,CHAR_175_000,CHAR_176_000,CHAR_177_000,CHAR_178_000,CHAR_179_000,CHAR_18_000,CHAR_180_000,CHAR_181_000,CHAR_184_000,CHAR_185_000,CHAR_186_000,CHAR_188_000,CHAR_189_000,CHAR_19_000,CHAR_190_000,CHAR_191_000,CHAR_192_000,CHAR_193_000,CHAR_194_000,CHAR_195_000,CHAR_196_000,CHAR_197_000,CHAR_198_000,CHAR_199_000,CHAR_20_000,CHAR_200_000,CHAR_201_000,CHAR_202_000,CHAR_203_000,CHAR_204_000,CHAR_205_000,CHAR_206_000,CHAR_207_000,CHAR_215_000,CHAR_216_000,CHAR_22_000,CHAR_226_000,CHAR_231_000,CHAR_235_000,CHAR_236_000,CHAR_237_000,CHAR_239_000,CHAR_240_000,CHAR_242_000,CHAR_243_000,CHAR_244_000,CHAR_245_000,CHAR_246_000,CHAR_247_000,CHAR_248_000,CHAR_249_000,CHAR_250_000,CHAR_251_000,CHAR_252_000,CHAR_253_000,CHAR_254_000,CHAR_255_000,CHAR_256_000,CHAR_257_000,CHAR_258_000,CHAR_259_000,CHAR_260_000,CHAR_261_000,CHAR_262_000,CHAR_263_000,CHAR_264_000,CHAR_265_000,CHAR_266_000,CHAR_267_000,CHAR_268_000,CHAR_269_000,CHAR_270_000,CHAR_271_000,CHAR_272_000,CHAR_274_000,CHAR_275_000,CHAR_276_000,CHAR_277_000,CHAR_278_000,CHAR_279_000,CHAR_280_000,CHAR_281_000,CHAR_282_000,CHAR_283_000,CHAR_284_000,CHAR_285_000,CHAR_286_000,CHAR_287_000,CHAR_288_000,CHAR_289_000,CHAR_290_000,CHAR_291_000,CHAR_292_000,CHAR_293_000,CHAR_294_000,CHAR_295_000,CHAR_296_000,CHAR_297_000,CHAR_298_000,CHAR_299_000,CHAR_300_000,CHAR_301_000,CHAR_302_000,CHAR_303_000,CHAR_304_000,CHAR_305_000,CHAR_306_000,CHAR_307_000,CHAR_308_000,CHAR_309_000,CHAR_310_000,CHAR_312_000,CHAR_313_000,CHAR_314_000,CHAR_315_000,CHAR_316_000,CHAR_317_000,CHAR_319_000,CHAR_320_000,CHAR_322_000,CHAR_323_000,CHAR_324_000,CHAR_325_000,CHAR_326_000,CHAR_329_000,CHAR_33_000,CHAR_330_000,CHAR_331_000,CHAR_332_000,CHAR_333_000,CHAR_334_000,CHAR_335_000,CHAR_336_000,CHAR_337_000,CHAR_338_000,CHAR_339_000,CHAR_34_000,CHAR_341_000,CHAR_342_000,CHAR_343_000,CHAR_344_000,CHAR_345_000,CHAR_346_000,CHAR_347_000,CHAR_348_000,CHAR_349_000,CHAR_35_000,CHAR_350_000,CHAR_351_000,CHAR_352_000,CHAR_353_000,CHAR_354_000,CHAR_355_000,CHAR_356_000,CHAR_357_000,CHAR_358_000,CHAR_359_000,CHAR_36_000,CHAR_360_000,CHAR_361_000,CHAR_362_000,CHAR_363_000,CHAR_364_000,CHAR_365_000,CHAR_366_000,CHAR_367_000,CHAR_368_000,CHAR_369_000,CHAR_37_000,CHAR_372_000,CHAR_373_000,CHAR_374_000,CHAR_375_000,CHAR_376_000,CHAR_378_000,CHAR_379_000,CHAR_38_000,CHAR_380_000,CHAR_381_000,CHAR_382_000,CHAR_385_000,CHAR_386_000,CHAR_387_000,CHAR_388_000,CHAR_389_000,CHAR_39_000,CHAR_390_000,CHAR_391_000,CHAR_392_000,CHAR_393_000,CHAR_395_000,CHAR_396_000,CHAR_397_000,CHAR_398_000,CHAR_399_000,CHAR_40_000,CHAR_400_000,CHAR_401_000,CHAR_402_000,CHAR_403_000,CHAR_404_000,CHAR_405_000,CHAR_406_000,CHAR_407_000,CHAR_408_000,CHAR_409_000,CHAR_41_000,CHAR_410_000,CHAR_411_000,CHAR_412_000,CHAR_413_000,CHAR_414_000,CHAR_415_000,CHAR_416_000,CHAR_417_000,CHAR_419_000,CHAR_420_000,CHAR_421_000,CHAR_422_000,CHAR_423_000,CHAR_426_000,CHAR_427_000,CHAR_432_000,CHAR_433_000,CHAR_434_000,CHAR_435_000,CHAR_436_000,CHAR_437_000,CHAR_438_000,CHAR_439_000,CHAR_440_000,CHAR_441_000,CHAR_442_000,CHAR_443_000,CHAR_444_000,CHAR_445_000,CHAR_446_000,CHAR_447_000,CHAR_448_000,CHAR_449_000,CHAR_450_000,CHAR_452_000,CHAR_453_000,CHAR_454_000,CHAR_455_000,CHAR_456_000,CHAR_457_000,CHAR_458_000,CHAR_459_000,CHAR_460_000,CHAR_461_000,CHAR_462_000,CHAR_463_000,CHAR_464_000,CHAR_466_000,CHAR_467_000,CHAR_468_000,CHAR_469_000,CHAR_47_000,CHAR_470_000,CHAR_473_000,CHAR_477_000,CHAR_478_000,CHAR_479_000,CHAR_48_000,CHAR_489_000,CHAR_49_000,CHAR_490_000,CHAR_493_000,CHAR_495_000,CHAR_496_000,CHAR_499_000,CHAR_50_000,CHAR_500_000,CHAR_501_000,CHAR_504_000,CHAR_505_000,CHAR_506_000,CHAR_507_000,CHAR_508_000,CHAR_509_000,CHAR_510_000,CHAR_511_000,CHAR_512_000,CHAR_513_000,CHAR_514_000,CHAR_515_000,CHAR_516_000,CHAR_517_000,CHAR_518_000,CHAR_519_000,CHAR_52_000,CHAR_520_000,CHAR_521_000,CHAR_522_000,CHAR_524_000,CHAR_525_000,CHAR_526_000,CHAR_527_000,CHAR_528_000,CHAR_529_000,CHAR_53_000,CHAR_530_000,CHAR_531_000,CHAR_532_000,CHAR_533_000,CHAR_534_000,CHAR_539_000,CHAR_54_000,CHAR_548_000,CHAR_549_000,CHAR_55_000,CHAR_550_000,CHAR_551_000,CHAR_552_000,CHAR_553_000,CHAR_554_000,CHAR_555_000,CHAR_556_000,CHAR_557_000,CHAR_558_000,CHAR_559_000,CHAR_56_000,CHAR_561_000,CHAR_562_000,CHAR_563_000,CHAR_564_000,CHAR_565_000,CHAR_566_000,CHAR_567_000,CHAR_568_000,CHAR_569_000,CHAR_57_000,CHAR_570_000,CHAR_575_000,CHAR_576_000,CHAR_577_000,CHAR_579_000,CHAR_58_000,CHAR_580_000,CHAR_581_000,CHAR_582_000,CHAR_583_000,CHAR_588_000,CHAR_589_000,CHAR_590_000,CHAR_591_000,CHAR_592_000,CHAR_593_000,CHAR_594_000,CHAR_595_000,CHAR_596_000,CHAR_597_000,CHAR_598_000,CHAR_599_000,CHAR_60_000,CHAR_600_000,CHAR_601_000,CHAR_602_000,CHAR_603_000,CHAR_604_000,CHAR_605_000,CHAR_606_000,CHAR_607_000,CHAR_608_000,CHAR_609_000,CHAR_61_000,CHAR_610_000,CHAR_611_000,CHAR_62_000,CHAR_63_000,CHAR_64_000,CHAR_65_000,CHAR_66_000,CHAR_67_000,CHAR_68_000,CHAR_69_000,CHAR_70_000,CHAR_71_000,CHAR_72_000,CHAR_73_000,CHAR_74_000,CHAR_75_000,CHAR_76_000,CHAR_78_000,CHAR_80_000,CHAR_81_000,CHAR_83_000,CHAR_85_000,CHAR_86_000,CHAR_87_000,CHAR_88_000,CHAR_89_000,CHAR_96_000,CHAR_97_000,CHAR_98_000,CHAR_99_000,
--TMP.C_TRANS_REF_NO, TMP.C_TRANS_TYPE_CODE,TMP.C_RELEASED_ON, ISNULL(TMP_PREV.T_ACKNOWLEDGE_ON,TMP.T_ACKNOWLEDGE_ON) AS T_ACKNOWLEDGE_ON,
--TMP_PREV.P_TRANS_REF_NO,TMP_PREV.P_TRANS_TYPE_CODE,TMP_PREV.P_RELEASED_ON,
TM_CUR.TRANS_REF_NO C_TRANS_REF_NO, TM_CUR.TRANS_TYPE_CODE C_TRANS_TYPE_CODE,TM_CUR.RELEASED_ON C_RELEASED_ON, TM_CUR.CREATED_ON C_CREATED_ON,ISNULL(TM_PREV.ACKNOWLEDGE_ON,TM_CUR.ACKNOWLEDGE_ON) AS T_ACKNOWLEDGE_ON,
TM_PREV.TRANS_REF_NO P_TRANS_REF_NO,TM_PREV.TRANS_TYPE_CODE P_TRANS_TYPE_CODE,TM_PREV.RELEASED_ON P_RELEASED_ON,TM_PREV.CREATED_ON P_CREATED_ON,
TM_CUR_SAVED.TRANS_REF_NO CUR_SAVED_TRANS_REF_NO,TM_CUR_SAVED.TRANS_TYPE_CODE CUR_SAVED_TRANS_TYPE_CODE,TM_CUR_SAVED.CREATED_ON CUR_SAVED_CREATED_ON,
CLIENT_REF,DOC_INFO.EXPECTED_RECEIVE_BACK_DATE ,WR.IS_FORWARDED
FROM WF_RUN_ROUTING_HISTORY WR
INNER JOIN WF_RUN WR1 ON WR.RUN_ID = WR1.RUN_ID AND WR.STATUS = 0
INNER JOIN WF_RUN_STAGE_STATUS WRSS ON WRSS.RUN_ID = WR.RUN_ID AND WRSS.ROUTING_ID = WR.ROUTING_ID
INNER JOIN DOC_INFO ON DOC_INFO.IDOC_ID = WR1.OBJECT_ID
INNER JOIN GENEALOGY ON GENEALOGY.GENO_KEY = DOC_INFO.GENO_KEY
INNER JOIN FILE_INDEX ON FILE_INDEX.FILE_ID = DOC_INFO.FILE_ID
INNER JOIN USER_MSTR UM ON UM.LOGIN_NAME = WR.SENT_BY
LEFT JOIN MESSAGE_CONTENT_MASTER MCM ON MCM.MESSAGE_CONTENT_ID=WR.ROUTING_COMMENTS_ID
LEFT JOIN WF_TEAM_MST WTM ON WTM.WF_TEAM_ID = WR1.WF_TEAM_ID
LEFT JOIN WF_TEAM_STAGE_DET WTSD ON WTSD.WF_TEAM_ID = WR1.WF_TEAM_ID AND WTSD.STAGE_ID = WR.SENT_TO_STAGE_ID
LEFT JOIN ORDER_MASTER ON ORDER_MASTER.ORDER_ID = DOC_INFO.ORDER_ID
LEFT JOIN DP_LEVEL_DOC ON DP_LEVEL_DOC.GENO_KEY = DOC_INFO.GENO_KEY
LEFT JOIN DOC_ORG_MSTR ON DOC_ORG_MSTR.ID = DP_LEVEL_DOC.DOC_ORIGIN_CODE
LEFT JOIN DOC_SIZE_MSTR ON DOC_SIZE_MSTR.SHT_SIZE = DOC_INFO.SHEET_SIZE
LEFT JOIN DOC_CORRESPONDENCE DC ON DC.IDOC_ID = DOC_INFO.IDOC_ID
LEFT JOIN APPROV_STAT_MSTR ON APPROV_STAT_MSTR.APP_STAT_CODE =DOC_INFO.APP_STAT_CODE
LEFT JOIN LIFECYCLE_STATUS_MASTER ON LIFECYCLE_STATUS_MASTER.STATUS_CODE = DOC_INFO.LCS_CODE
LEFT JOIN INT_STAT_MSTR ON INT_STAT_MSTR.INTR_STAT_CODE = DOC_INFO.INT_APPR_STATUS_CODE
LEFT JOIN ORDER_INT_STATUS_CODES ON ORDER_INT_STATUS_CODES.ORDER_ID=ORDER_MASTER.ORDER_ID AND ORDER_INT_STATUS_CODES.INT_STATUS_CODE = INT_STAT_MSTR.INTR_STAT_CODE
LEFT JOIN ORDER_LCS ON ORDER_LCS.ORDER_ID=ORDER_MASTER.ORDER_ID AND ORDER_LCS.STATUS_CODE = DOC_INFO.LCS_CODE
LEFT JOIN (SELECT COUNT(IDOC_ID) AS DOC_COUNT,IDOC_ID FROM DOC_REF_DOC GROUP BY IDOC_ID) AS DRD ON DRD.IDOC_ID=DOC_INFO.IDOC_ID
LEFT JOIN (SELECT COUNT(IDOC_ID) AS XREF_COUNT ,IDOC_ID,INT_REV_NO FROM DOC_INFO_LINKS GROUP BY IDOC_ID,INT_REV_NO) AS DXREF
ON DXREF.IDOC_ID=DOC_INFO.IDOC_ID AND DXREF.INT_REV_NO =DOC_INFO.INT_REV_NO
LEFT JOIN CUSTOM_DOC_CHAR_DETAILS CUSTOM_OBJECT_CHAR_DETAILS ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID
LEFT JOIN EXTENDED_DOC_CHAR_DETAILS EXTENDED_DOC_CHAR_DETAILS ON EXTENDED_DOC_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID
--OUTER APPLY (SELECT TOP 1 TM.TRANS_ID,TDD.IDOC_ID,TM.TRANS_REF_NO AS C_TRANS_REF_NO,TM.TRANS_TYPE_CODE AS C_TRANS_TYPE_CODE,TM.RELEASED_ON AS C_RELEASED_ON,TM.ACKNOWLEDGE_ON AS T_ACKNOWLEDGE_ON FROM TRANS_DOC_DET TDD
--INNER JOIN TRANSMITTAL_MST TM ON TM.TRANS_ID=TDD.TRANS_ID AND (TM.TRANS_STATUS=1 OR TM.TRANS_STATUS=2) WHERE TDD.IDOC_ID=WR.OBJECT_ID ORDER BY RELEASED_ON DESC) AS TMP
--OUTER APPLY (SELECT TOP 1 TDD.IDOC_ID,TM.TRANS_REF_NO AS P_TRANS_REF_NO,TM.TRANS_TYPE_CODE AS P_TRANS_TYPE_CODE,TM.RELEASED_ON AS P_RELEASED_ON,TM.ACKNOWLEDGE_ON AS T_ACKNOWLEDGE_ON FROM TRANS_DOC_DET TDD
--INNER JOIN TRANSMITTAL_MST TM ON TM.TRANS_ID=TDD.TRANS_ID AND (TM.TRANS_STATUS=1 OR TM.TRANS_STATUS=2) AND TM.TRANS_ID <> TMP.TRANS_ID AND TDD.IDOC_ID=WR.OBJECT_ID ORDER BY RELEASED_ON DESC) AS TMP_PREV
LEFT JOIN TRANSMITTAL_MST TM_CUR ON TM_CUR.TRANS_ID = DOC_INFO.CUR_TRANS_ID
LEFT JOIN TRANSMITTAL_MST TM_CUR_SAVED ON TM_CUR_SAVED.TRANS_ID = DOC_INFO.CUR_SAVED_TRANS_ID
LEFT JOIN TRANSMITTAL_MST TM_PREV ON TM_PREV.TRANS_ID = DOC_INFO.PREV_TRANS_ID
INNER JOIN DOC_STATUS_MSTR DSM ON DSM.DOC_STATUS_ID = DOC_INFO.DOC_STATUS
LEFT JOIN ORDER_APP_STATUS_CODES ON DOC_INFO.APP_STAT_CODE = ORDER_APP_STATUS_CODES.APP_STAT_CODE
AND DOC_INFO.ORDER_ID = ORDER_APP_STATUS_CODES.ORDER_ID
WHERE WR.STATUS = 0
--AND WR.ROUTE_STATUS = 0
AND WR.SENT_TO = N'105057'
AND WR1.OBJECT_TYPE = 'DOC' AND WR.IS_ACTIVE_SEQ = 1 ORDER BY WR.SENT_DATE DESC ) A
<$W>Execution Time: Milliseconds. (11:53:53.966 AM)
----------------------------------------------------------------------------------------------------------