Failure : Method Name : GetData Query Failure Exception Message Database operation failed.[DBER250903111848] Invalid column name 'FILE_ICONS'. Invalid column name 'USER_REV_NO_VALUE'. Invalid column name 'VERSION_NO'. Invalid column name 'CLIENT_DOC_NO'. Invalid column name 'VENDOR_DOC_NO'. Invalid column name 'ORIGINAL_F_NAME'. Invalid column name 'FILE_ID'. Invalid column name 'FILE_COMPOSITION'. Invalid column name 'COUT_STATUS'. Invalid column name 'ASSOCIATION_STATUS'. Invalid column name 'DOC_TYPE'. Invalid column name 'REFERRED_DOC_COUNT'. Invalid column name 'XREF_COUNT'. Invalid column name 'REV_NO_VALUE'. Login : ADMIN 03-Sep-2025 11:18:48:5907 AM Connection : wrench Command(s) : SELECT DISTINCT * FROM (SELECT ADDED_BY_USER,ATTACHMENTS,FILE_ICONS,DOC_STATUS,ITEM_NO,ITEM_DESCR,USER_REV_NO_VALUE,ORDER_NO,ORDER_NAME,VERSION_NO,CLIENT_DOC_NO,VENDOR_DOC_NO,ITEM_ID,SHORTCUT,LEVEL_NUMBER,OBJECT,OBJECT_KEY,LAST_EDITED_ON,INT_REV_NO,ORIGINAL_F_NAME,FILE_ID,FILE_COMPOSITION,CREATED_ON,CREATED_BY,ORDER_ID,GENO_KEY,COUT_STATUS,ASSOCIATION_STATUS,DOC_TYPE,REFERRED_DOC_COUNT,XREF_COUNT,GENEALOGY_STRING,REV_NO_VALUE FROM (SELECT DOC_INFO.IDOC_ID AS ITEM_ID ,(CASE WHEN ISNULL(DOC_INFO.ASSOCIATION_STATUS,0) = 0 AND DRD.DOC_COUNT IS NULL AND DXREF.XREF_COUNT IS NULL THEN 0 WHEN ISNULL(DOC_INFO.ASSOCIATION_STATUS,0) > 0 OR DRD.DOC_COUNT > 0 OR DXREF.XREF_COUNT > 0 THEN 1 ELSE '0' END)AS ATTACHMENTS , DOC_INFO.DOC_NO ITEM_NO, DOC_INFO.INT_REV_NO , CASE WHEN ISNULL(DOC_CORRESPONDENCE.CORR_SOURCE, 0)=1 THEN ISNULL(UM6.USER_NAME, DOC_CORRESPONDENCE.MAIL_FROM) ELSE DOC_CORRESPONDENCE.MAIL_FROM END AS MAIL_FROM , DOC_CORRESPONDENCE.MAIL_SUBJECT, DOC_CORRESPONDENCE.CORR_ISSUE_DATE , DOC_CORRESPONDENCE.RECIPIENTS_TO AS MAIL_TO,DOC_CORRESPONDENCE.RECIPIENTS_CC MAIL_CC,DOC_CORRESPONDENCE.RECIPIENTS_BCC MAIL_BCC , DOC_CORRESPONDENCE.LETTER_DATE , DOC_CORRESPONDENCE.TO_BE_REPLIED_BY , DOC_CORRESPONDENCE.REPLAY_RECEIPT_DATE [ACTUAL_REPLY_DATE] , DOC_CORRESPONDENCE.EXPECTED_REPLY_DATE , DOC_CORRESPONDENCE.SHORT_MESSAGE , OM.ORDER_ID , OM.ORDER_NO , OM.ORDER_DESCRIPTION AS ORDER_NAME , DOC_INFO.LAST_EDITED_BY, DOC_INFO.LAST_EDITED_ON , DOC_INFO.DOC_NO CORRESPONCE_REF_NO , CASE WHEN ISNULL(DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY,0) = 0 THEN '' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =1 THEN 'High' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY=2 THEN 'Medium' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =3 THEN 'Low' END AS AGEING_DAYS_PRIORITY , DSM.DOC_STATUS_DESC AS DOC_STATUS , DOC_INFO.CREATED_BY, CONVERT(DATETIME,CONVERT(NVARCHAR,DOC_INFO.CREATED_ON,113)) AS CREATED_ON , WFRSS.STAGE_ID, WFRSS.USERS_TO AS CURRENT_STAGE_RESOURCES , WFTD.STAGE_NAME CURRENT_STAGE, WF_RUN_TD.WF_OBJ ,WRRH.SENT_BY,WTM.WF_TEAM_NAME , DOC_CORRESPONDENCE.CORR_RECEIPT_DATE , CONVERT(DATETIME,CONVERT(NVARCHAR,DOC_CORRESPONDENCE.REPLAY_RECEIPT_DATE,106))as REPLAY_RECEIPT_DATE , DOC_CORRESPONDENCE.MAIL_RECEIPT_DATE, DOC_CORRESPONDENCE.MAIL_RECEIPT_DATE AS MAIL_SENT_DATE , ISNULL(DOC_CORRESPONDENCE.TRACK_MAIL,0) AS TRACK_MAIL, CORRESPONDENCE_RECIPIENTS.READ_STATUS AS READ_STATUS , CASE WHEN CORRESPONDENCE_RECIPIENTS.READ_STATUS = 1 THEN 'Read' ELSE 'UnRead' END AS READ_UNREAD , OM.ORDER_DESCRIPTION , CASE WHEN ISNULL(DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY,0) = 0 THEN '' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =1 THEN 'High' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY=2 THEN 'Medium' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =3 THEN 'Low' END AS PRIORITY --, (SELECT TOP 1 DI1.DOC_NO -- FROM DOC_CORRESPONDENCE_DET DCD1 -- LEFT JOIN DOC_CORRESPONDENCE DC1 ON DC1.CORR_ID=DCD.PARENT_CORR_ID -- LEFT JOIN DOC_INFO DI1 ON DC1.IDOC_ID=DI1.IDOC_ID -- WHERE DCD1.PARENT_CORR_ID=DCD.PARENT_CORR_ID -- ) AS CORR_REF_NO , DOC_CORRESPONDENCE.PARENT_CORRESPONDENCE_REF_NUMBERS CORR_REF_NO , UM2.USER_NAME AS CREATED_BY_USER,UM_DI.USER_NAME AS LAST_EDITED_BY_USER , DRV.SENT_BY_USER SENT_BY_USER , Z.REPLIED_BY AS FIRST_REPLIED_BY, Z.REPLIED_ON AS FIRST_REPLIED_ON , UM5.USER_NAME REPLAY_EXPECTED_FROM , CASE WHEN ISNULL(DOC_INFO.DOC_DESCRIPTION,'')='' THEN '' ELSE DOC_INFO.DOC_DESCRIPTION END AS ITEM_DESCR, 19 AS OBJECT,'-999' AS LEVEL_NUMBER,0 AS SHORTCUT , -999 AS OBJECT_KEY ,(CASE WHEN PFO.FOLDER_ID IS NULL THEN -1 ELSE 1 END) AS ADDED_BY_USER , CASE WHEN FILE_INDEX.DOC_TYPE = 2 THEN DOC_CORRESPONDENCE.EXPECTED_REPLY_RECEIPT_DATE ELSE DOC_INFO.EXPECTED_RECEIVE_BACK_DATE END AS EXPECTED_REPLY_RECEIPT_DATE ,G.GENO_KEY,G.GENEALOGY_STRING ,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 FROM DOC_INFO INNER JOIN FILE_INDEX ON DOC_INFO.FILE_ID = FILE_INDEX.FILE_ID AND FILE_INDEX.DOC_TYPE =2 AND DOC_INFO.IS_DEPENDENT=0 INNER JOIN DOC_CORRESPONDENCE ON DOC_INFO.IDOC_ID = DOC_CORRESPONDENCE.IDOC_ID AND ISNULL(DOC_CORRESPONDENCE.CORR_SOURCE,0)=1 LEFT JOIN CORRESPONDENCE_RECIPIENTS ON CORRESPONDENCE_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN DOC_CORRESPONDENCE_DET DCD ON DCD.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN USER_MSTR UM_CORR ON UM_CORR.LOGIN_NAME = CORRESPONDENCE_RECIPIENTS.RECIPIENTS LEFT JOIN CORRESPONDENCE_EXT_RECIPIENTS ON CORRESPONDENCE_EXT_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID OUTER APPLY (SELECT R_1.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Document' as WF_OBJ FROM WF_RUN R_1 WHERE R_1.OBJECT_TYPE='DOC' AND R_1.OBJECT_ID = DOC_INFO.IDOC_ID UNION ALL SELECT WO.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Task' as WF_OBJ FROM WF_RUN R_1 INNER JOIN WBS_OBJECTS WO ON WO.TASK_ID=R_1.OBJECT_ID AND WO.LINK_TYPE=0 AND WO.OBJECT_TYPE=0 WHERE R_1.OBJECT_TYPE='TSK' AND WO.OBJECT_ID = DOC_INFO.IDOC_ID ) WF_RUN_TD LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH1 ON WF_RUN_TD.RUN_ID=WRRH1.RUN_ID LEFT JOIN WF_RUN_STAGE_STATUS WFRSS ON WF_RUN_TD.RUN_ID=WFRSS.RUN_ID AND WFRSS.STATUS=0 LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH ON WF_RUN_TD.RUN_ID=WRRH.RUN_ID AND WRRH.STATUS=0 AND WFRSS.ROUTING_ID=WRRH.ROUTING_ID LEFT JOIN ORDER_MASTER OM ON DOC_INFO.ORDER_ID=OM.ORDER_ID LEFT JOIN ORDER_AREA ON ORDER_AREA.ORD_AREA_ID = DOC_INFO.AREACODE_ID AND ORDER_AREA.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORDER_SYSTEM ON ORDER_SYSTEM.ORD_SYS_ID = DOC_INFO.SYSTEM_ID AND ORDER_SYSTEM.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORIGIN ON ORIGIN.ORIGIN_CODE=DOC_INFO.DOC_ORIGIN AND ORIGIN.ORIGIN=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN DOC_ORG_MSTR DOM ON DOM.ID=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN GENEALOGY G ON DOC_INFO.GENO_KEY=G.GENO_KEY LEFT JOIN GENEALOGY G1 ON OM.GENO_KEY=G1.GENO_KEY LEFT JOIN DOC_APR_REQ_CODES_MSTR ON DOC_APR_REQ_CODES_MSTR.APR_REQ_CODE=DOC_INFO.APR_REQ_CODE LEFT JOIN INT_STAT_MSTR on DOC_INFO.INT_APPR_STATUS_CODE=INT_STAT_MSTR.INTR_STAT_CODE LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME = DOC_CORRESPONDENCE.REPLY_RECEIVED_BY LEFT JOIN USER_MSTR UM1 ON UM1.LOGIN_NAME = FILE_INDEX.COUT_BY LEFT JOIN USER_MSTR UM2 ON UM2.LOGIN_NAME = DOC_INFO.CREATED_BY LEFT JOIN ORDER_LCS ON ORDER_LCS.ORDER_ID=OM.ORDER_ID AND DOC_INFO.LCS_CODE= ORDER_LCS.STATUS_CODE LEFT JOIN DOC_SIZE_MSTR ON SHT_SIZE=DOC_INFO.SHEET_SIZE LEFT JOIN LIFECYCLE_STATUS_MASTER ON DOC_INFO.LCS_CODE = LIFECYCLE_STATUS_MASTER.STATUS_CODE OUTER APPLY (SELECT COUNT(1) AS DOC_COUNT FROM DOC_REF_DOC REF where REF.IDOC_ID=DOC_INFO.IDOC_ID AND REF.DOC_INT_REV_NO = DOC_INFO.INT_REV_NO ) AS DRD OUTER APPLY (SELECT COUNT(1) AS XREF_COUNT from DOC_INFO_LINKS LINKS where LINKS.IDOC_ID=DOC_INFO.IDOC_ID AND LINKS.INT_REV_NO =DOC_INFO.INT_REV_NO ) AS DXREF --LEFT JOIN CUSTOM_OBJECT_CHAR_DETAILS WITH (FORCESCAN) ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_TYPE =0 --AND CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_DOC_CHAR_DETAILS CUSTOM_OBJECT_CHAR_DETAILS ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_OBJECT_CHAR_LOV_DETAILS COCLD ON COCLD.OBJECT_TYPE = 0 AND COCLD.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN PERSONAL_FOLDER PF ON PF.FOLDER_ID = 10230294 AND PF.OBJECT_TYPE = 19 LEFT JOIN PERSONAL_FOLDER_OBJECTS PFO ON PF.FOLDER_ID = PFO.FOLDER_ID AND PFO.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN WF_TEAM_STAGE_DET WFTD ON WFTD.STAGE_ID=WFRSS.STAGE_ID AND WFTD.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH2 ON WF_RUN_TD.RUN_ID=WRRH2.RUN_ID LEFT JOIN WF_TEAM_MST WTM ON WTM.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_TEAM_STAGE_DET WFTS ON WRRH.SENT_TO_STAGE_ID=WFTS.STAGE_ID AND WF_RUN_TD.WF_TEAM_ID=WFTS.WF_TEAM_ID LEFT JOIN USER_MSTR UM4 ON UM4.LOGIN_NAME = WRRH.SENT_BY LEFT JOIN USER_MSTR UM_DI ON UM_DI.LOGIN_NAME = DOC_INFO.LAST_EDITED_BY INNER JOIN DOC_STATUS_MSTR DSM ON DSM.DOC_STATUS_ID = DOC_INFO.DOC_STATUS LEFT JOIN USER_MSTR UM_APPROVE ON UM_APPROVE.LOGIN_NAME = DOC_INFO.APPROVED_BY OUTER APPLY (SELECT TOP 1 USER_MSTR.USER_NAME AS REPLIED_BY, DOC_CORRESPONDENCE_DET.REPLIED_ON FROM DOC_CORRESPONDENCE_DET LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME=DOC_CORRESPONDENCE_DET.REPLIED_BY WHERE DOC_CORRESPONDENCE_DET.PARENT_CORR_ID=DCD.CORR_ID AND DOC_CORRESPONDENCE_DET.REPLIED_ON IS NOT NULL ORDER BY DOC_CORRESPONDENCE_DET.REPLIED_ON ASC )Z LEFT JOIN USER_MSTR UM5 ON UM5.LOGIN_NAME = DOC_CORRESPONDENCE.REPLAY_EXPECTED_FROM LEFT OUTER JOIN USER_MSTR UM6 ON UM6.LOGIN_NAME = DOC_CORRESPONDENCE.MAIL_FROM CROSS APPLY ( SELECT CASE WHEN N'10230294' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'10230294' <> WFRSS.SENT_BY_ORIGIN THEN WFRSS.SENT_BY_ORIGIN ELSE UM4.USER_NAME END AS SENT_BY_USER )DRV LEFT JOIN EXTENDED_DOC_CHAR_DETAILS EXTENDED_DOC_CHAR_DETAILS ON EXTENDED_DOC_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID WHERE DOC_INFO.IS_CONFIDENTIAL <> 1 AND ( (DOC_INFO.ORDER_ID = N'1013244') ) OR (EXISTS ( SELECT 1 FROM PERSONAL_FOLDER_OBJECTS PFO INNER JOIN PERSONAL_FOLDER PF ON PFO.FOLDER_ID = PF.FOLDER_ID AND PF.OBJECT_TYPE = 19 WHERE PF.FOLDER_ID =10230294 AND PF.OBJECT_TYPE = 19 AND PFO.OBJECT_ID = doc_info.idoc_id) ) ) A ) DIST_RECORDS ORDER BY ITEM_ID DESC Connection Timeout : 0 Queries : SELECT DISTINCT * FROM (SELECT ADDED_BY_USER,ATTACHMENTS,FILE_ICONS,DOC_STATUS,ITEM_NO,ITEM_DESCR,USER_REV_NO_VALUE,ORDER_NO,ORDER_NAME,VERSION_NO,CLIENT_DOC_NO,VENDOR_DOC_NO,ITEM_ID,SHORTCUT,LEVEL_NUMBER,OBJECT,OBJECT_KEY,LAST_EDITED_ON,INT_REV_NO,ORIGINAL_F_NAME,FILE_ID,FILE_COMPOSITION,CREATED_ON,CREATED_BY,ORDER_ID,GENO_KEY,COUT_STATUS,ASSOCIATION_STATUS,DOC_TYPE,REFERRED_DOC_COUNT,XREF_COUNT,GENEALOGY_STRING,REV_NO_VALUE FROM (SELECT DOC_INFO.IDOC_ID AS ITEM_ID ,(CASE WHEN ISNULL(DOC_INFO.ASSOCIATION_STATUS,0) = 0 AND DRD.DOC_COUNT IS NULL AND DXREF.XREF_COUNT IS NULL THEN 0 WHEN ISNULL(DOC_INFO.ASSOCIATION_STATUS,0) > 0 OR DRD.DOC_COUNT > 0 OR DXREF.XREF_COUNT > 0 THEN 1 ELSE '0' END)AS ATTACHMENTS , DOC_INFO.DOC_NO ITEM_NO, DOC_INFO.INT_REV_NO , CASE WHEN ISNULL(DOC_CORRESPONDENCE.CORR_SOURCE, 0)=1 THEN ISNULL(UM6.USER_NAME, DOC_CORRESPONDENCE.MAIL_FROM) ELSE DOC_CORRESPONDENCE.MAIL_FROM END AS MAIL_FROM , DOC_CORRESPONDENCE.MAIL_SUBJECT, DOC_CORRESPONDENCE.CORR_ISSUE_DATE , DOC_CORRESPONDENCE.RECIPIENTS_TO AS MAIL_TO,DOC_CORRESPONDENCE.RECIPIENTS_CC MAIL_CC,DOC_CORRESPONDENCE.RECIPIENTS_BCC MAIL_BCC , DOC_CORRESPONDENCE.LETTER_DATE , DOC_CORRESPONDENCE.TO_BE_REPLIED_BY , DOC_CORRESPONDENCE.REPLAY_RECEIPT_DATE [ACTUAL_REPLY_DATE] , DOC_CORRESPONDENCE.EXPECTED_REPLY_DATE , DOC_CORRESPONDENCE.SHORT_MESSAGE , OM.ORDER_ID , OM.ORDER_NO , OM.ORDER_DESCRIPTION AS ORDER_NAME , DOC_INFO.LAST_EDITED_BY, DOC_INFO.LAST_EDITED_ON , DOC_INFO.DOC_NO CORRESPONCE_REF_NO , CASE WHEN ISNULL(DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY,0) = 0 THEN '' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =1 THEN 'High' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY=2 THEN 'Medium' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =3 THEN 'Low' END AS AGEING_DAYS_PRIORITY , DSM.DOC_STATUS_DESC AS DOC_STATUS , DOC_INFO.CREATED_BY, CONVERT(DATETIME,CONVERT(NVARCHAR,DOC_INFO.CREATED_ON,113)) AS CREATED_ON , WFRSS.STAGE_ID, WFRSS.USERS_TO AS CURRENT_STAGE_RESOURCES , WFTD.STAGE_NAME CURRENT_STAGE, WF_RUN_TD.WF_OBJ ,WRRH.SENT_BY,WTM.WF_TEAM_NAME , DOC_CORRESPONDENCE.CORR_RECEIPT_DATE , CONVERT(DATETIME,CONVERT(NVARCHAR,DOC_CORRESPONDENCE.REPLAY_RECEIPT_DATE,106))as REPLAY_RECEIPT_DATE , DOC_CORRESPONDENCE.MAIL_RECEIPT_DATE, DOC_CORRESPONDENCE.MAIL_RECEIPT_DATE AS MAIL_SENT_DATE , ISNULL(DOC_CORRESPONDENCE.TRACK_MAIL,0) AS TRACK_MAIL, CORRESPONDENCE_RECIPIENTS.READ_STATUS AS READ_STATUS , CASE WHEN CORRESPONDENCE_RECIPIENTS.READ_STATUS = 1 THEN 'Read' ELSE 'UnRead' END AS READ_UNREAD , OM.ORDER_DESCRIPTION , CASE WHEN ISNULL(DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY,0) = 0 THEN '' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =1 THEN 'High' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY=2 THEN 'Medium' WHEN DOC_CORRESPONDENCE.AGEING_DAYS_PRIORITY =3 THEN 'Low' END AS PRIORITY --, (SELECT TOP 1 DI1.DOC_NO -- FROM DOC_CORRESPONDENCE_DET DCD1 -- LEFT JOIN DOC_CORRESPONDENCE DC1 ON DC1.CORR_ID=DCD.PARENT_CORR_ID -- LEFT JOIN DOC_INFO DI1 ON DC1.IDOC_ID=DI1.IDOC_ID -- WHERE DCD1.PARENT_CORR_ID=DCD.PARENT_CORR_ID -- ) AS CORR_REF_NO , DOC_CORRESPONDENCE.PARENT_CORRESPONDENCE_REF_NUMBERS CORR_REF_NO , UM2.USER_NAME AS CREATED_BY_USER,UM_DI.USER_NAME AS LAST_EDITED_BY_USER , DRV.SENT_BY_USER SENT_BY_USER , Z.REPLIED_BY AS FIRST_REPLIED_BY, Z.REPLIED_ON AS FIRST_REPLIED_ON , UM5.USER_NAME REPLAY_EXPECTED_FROM , CASE WHEN ISNULL(DOC_INFO.DOC_DESCRIPTION,'')='' THEN '' ELSE DOC_INFO.DOC_DESCRIPTION END AS ITEM_DESCR, 19 AS OBJECT,'-999' AS LEVEL_NUMBER,0 AS SHORTCUT , -999 AS OBJECT_KEY ,(CASE WHEN PFO.FOLDER_ID IS NULL THEN -1 ELSE 1 END) AS ADDED_BY_USER , CASE WHEN FILE_INDEX.DOC_TYPE = 2 THEN DOC_CORRESPONDENCE.EXPECTED_REPLY_RECEIPT_DATE ELSE DOC_INFO.EXPECTED_RECEIVE_BACK_DATE END AS EXPECTED_REPLY_RECEIPT_DATE ,G.GENO_KEY,G.GENEALOGY_STRING ,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 FROM DOC_INFO INNER JOIN FILE_INDEX ON DOC_INFO.FILE_ID = FILE_INDEX.FILE_ID AND FILE_INDEX.DOC_TYPE =2 AND DOC_INFO.IS_DEPENDENT=0 INNER JOIN DOC_CORRESPONDENCE ON DOC_INFO.IDOC_ID = DOC_CORRESPONDENCE.IDOC_ID AND ISNULL(DOC_CORRESPONDENCE.CORR_SOURCE,0)=1 LEFT JOIN CORRESPONDENCE_RECIPIENTS ON CORRESPONDENCE_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN DOC_CORRESPONDENCE_DET DCD ON DCD.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN USER_MSTR UM_CORR ON UM_CORR.LOGIN_NAME = CORRESPONDENCE_RECIPIENTS.RECIPIENTS LEFT JOIN CORRESPONDENCE_EXT_RECIPIENTS ON CORRESPONDENCE_EXT_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID OUTER APPLY (SELECT R_1.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Document' as WF_OBJ FROM WF_RUN R_1 WHERE R_1.OBJECT_TYPE='DOC' AND R_1.OBJECT_ID = DOC_INFO.IDOC_ID UNION ALL SELECT WO.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Task' as WF_OBJ FROM WF_RUN R_1 INNER JOIN WBS_OBJECTS WO ON WO.TASK_ID=R_1.OBJECT_ID AND WO.LINK_TYPE=0 AND WO.OBJECT_TYPE=0 WHERE R_1.OBJECT_TYPE='TSK' AND WO.OBJECT_ID = DOC_INFO.IDOC_ID ) WF_RUN_TD LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH1 ON WF_RUN_TD.RUN_ID=WRRH1.RUN_ID LEFT JOIN WF_RUN_STAGE_STATUS WFRSS ON WF_RUN_TD.RUN_ID=WFRSS.RUN_ID AND WFRSS.STATUS=0 LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH ON WF_RUN_TD.RUN_ID=WRRH.RUN_ID AND WRRH.STATUS=0 AND WFRSS.ROUTING_ID=WRRH.ROUTING_ID LEFT JOIN ORDER_MASTER OM ON DOC_INFO.ORDER_ID=OM.ORDER_ID LEFT JOIN ORDER_AREA ON ORDER_AREA.ORD_AREA_ID = DOC_INFO.AREACODE_ID AND ORDER_AREA.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORDER_SYSTEM ON ORDER_SYSTEM.ORD_SYS_ID = DOC_INFO.SYSTEM_ID AND ORDER_SYSTEM.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORIGIN ON ORIGIN.ORIGIN_CODE=DOC_INFO.DOC_ORIGIN AND ORIGIN.ORIGIN=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN DOC_ORG_MSTR DOM ON DOM.ID=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN GENEALOGY G ON DOC_INFO.GENO_KEY=G.GENO_KEY LEFT JOIN GENEALOGY G1 ON OM.GENO_KEY=G1.GENO_KEY LEFT JOIN DOC_APR_REQ_CODES_MSTR ON DOC_APR_REQ_CODES_MSTR.APR_REQ_CODE=DOC_INFO.APR_REQ_CODE LEFT JOIN INT_STAT_MSTR on DOC_INFO.INT_APPR_STATUS_CODE=INT_STAT_MSTR.INTR_STAT_CODE LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME = DOC_CORRESPONDENCE.REPLY_RECEIVED_BY LEFT JOIN USER_MSTR UM1 ON UM1.LOGIN_NAME = FILE_INDEX.COUT_BY LEFT JOIN USER_MSTR UM2 ON UM2.LOGIN_NAME = DOC_INFO.CREATED_BY LEFT JOIN ORDER_LCS ON ORDER_LCS.ORDER_ID=OM.ORDER_ID AND DOC_INFO.LCS_CODE= ORDER_LCS.STATUS_CODE LEFT JOIN DOC_SIZE_MSTR ON SHT_SIZE=DOC_INFO.SHEET_SIZE LEFT JOIN LIFECYCLE_STATUS_MASTER ON DOC_INFO.LCS_CODE = LIFECYCLE_STATUS_MASTER.STATUS_CODE OUTER APPLY (SELECT COUNT(1) AS DOC_COUNT FROM DOC_REF_DOC REF where REF.IDOC_ID=DOC_INFO.IDOC_ID AND REF.DOC_INT_REV_NO = DOC_INFO.INT_REV_NO ) AS DRD OUTER APPLY (SELECT COUNT(1) AS XREF_COUNT from DOC_INFO_LINKS LINKS where LINKS.IDOC_ID=DOC_INFO.IDOC_ID AND LINKS.INT_REV_NO =DOC_INFO.INT_REV_NO ) AS DXREF --LEFT JOIN CUSTOM_OBJECT_CHAR_DETAILS WITH (FORCESCAN) ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_TYPE =0 --AND CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_DOC_CHAR_DETAILS CUSTOM_OBJECT_CHAR_DETAILS ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_OBJECT_CHAR_LOV_DETAILS COCLD ON COCLD.OBJECT_TYPE = 0 AND COCLD.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN PERSONAL_FOLDER PF ON PF.FOLDER_ID = 10230294 AND PF.OBJECT_TYPE = 19 LEFT JOIN PERSONAL_FOLDER_OBJECTS PFO ON PF.FOLDER_ID = PFO.FOLDER_ID AND PFO.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN WF_TEAM_STAGE_DET WFTD ON WFTD.STAGE_ID=WFRSS.STAGE_ID AND WFTD.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH2 ON WF_RUN_TD.RUN_ID=WRRH2.RUN_ID LEFT JOIN WF_TEAM_MST WTM ON WTM.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_TEAM_STAGE_DET WFTS ON WRRH.SENT_TO_STAGE_ID=WFTS.STAGE_ID AND WF_RUN_TD.WF_TEAM_ID=WFTS.WF_TEAM_ID LEFT JOIN USER_MSTR UM4 ON UM4.LOGIN_NAME = WRRH.SENT_BY LEFT JOIN USER_MSTR UM_DI ON UM_DI.LOGIN_NAME = DOC_INFO.LAST_EDITED_BY INNER JOIN DOC_STATUS_MSTR DSM ON DSM.DOC_STATUS_ID = DOC_INFO.DOC_STATUS LEFT JOIN USER_MSTR UM_APPROVE ON UM_APPROVE.LOGIN_NAME = DOC_INFO.APPROVED_BY OUTER APPLY (SELECT TOP 1 USER_MSTR.USER_NAME AS REPLIED_BY, DOC_CORRESPONDENCE_DET.REPLIED_ON FROM DOC_CORRESPONDENCE_DET LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME=DOC_CORRESPONDENCE_DET.REPLIED_BY WHERE DOC_CORRESPONDENCE_DET.PARENT_CORR_ID=DCD.CORR_ID AND DOC_CORRESPONDENCE_DET.REPLIED_ON IS NOT NULL ORDER BY DOC_CORRESPONDENCE_DET.REPLIED_ON ASC )Z LEFT JOIN USER_MSTR UM5 ON UM5.LOGIN_NAME = DOC_CORRESPONDENCE.REPLAY_EXPECTED_FROM LEFT OUTER JOIN USER_MSTR UM6 ON UM6.LOGIN_NAME = DOC_CORRESPONDENCE.MAIL_FROM CROSS APPLY ( SELECT CASE WHEN N'10230294' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'10230294' <> WFRSS.SENT_BY_ORIGIN THEN WFRSS.SENT_BY_ORIGIN ELSE UM4.USER_NAME END AS SENT_BY_USER )DRV LEFT JOIN EXTENDED_DOC_CHAR_DETAILS EXTENDED_DOC_CHAR_DETAILS ON EXTENDED_DOC_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID WHERE DOC_INFO.IS_CONFIDENTIAL <> 1 AND ( (DOC_INFO.ORDER_ID = N'1013244') ) OR (EXISTS ( SELECT 1 FROM PERSONAL_FOLDER_OBJECTS PFO INNER JOIN PERSONAL_FOLDER PF ON PFO.FOLDER_ID = PF.FOLDER_ID AND PF.OBJECT_TYPE = 19 WHERE PF.FOLDER_ID =10230294 AND PF.OBJECT_TYPE = 19 AND PFO.OBJECT_ID = doc_info.idoc_id) ) ) A ) DIST_RECORDS ORDER BY ITEM_ID DESC SELECT COUNT(1) TOTAL_COUNT FROM (SELECT DISTINCT * FROM (SELECT DOC_INFO.IDOC_ID AS ITEM_ID FROM DOC_INFO INNER JOIN FILE_INDEX ON DOC_INFO.FILE_ID = FILE_INDEX.FILE_ID AND FILE_INDEX.DOC_TYPE =2 AND DOC_INFO.IS_DEPENDENT=0 INNER JOIN DOC_CORRESPONDENCE ON DOC_INFO.IDOC_ID = DOC_CORRESPONDENCE.IDOC_ID AND ISNULL(DOC_CORRESPONDENCE.CORR_SOURCE,0)=1 LEFT JOIN CORRESPONDENCE_RECIPIENTS ON CORRESPONDENCE_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN DOC_CORRESPONDENCE_DET DCD ON DCD.CORR_ID = DOC_CORRESPONDENCE.CORR_ID LEFT JOIN USER_MSTR UM_CORR ON UM_CORR.LOGIN_NAME = CORRESPONDENCE_RECIPIENTS.RECIPIENTS LEFT JOIN CORRESPONDENCE_EXT_RECIPIENTS ON CORRESPONDENCE_EXT_RECIPIENTS.CORR_ID = DOC_CORRESPONDENCE.CORR_ID OUTER APPLY (SELECT R_1.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Document' as WF_OBJ FROM WF_RUN R_1 WHERE R_1.OBJECT_TYPE='DOC' AND R_1.OBJECT_ID = DOC_INFO.IDOC_ID UNION ALL SELECT WO.OBJECT_ID IDOC_ID, R_1.OBJECT_ID,R_1.OBJECT_TYPE,R_1.WF_TEAM_ID,R_1.RUN_ID,R_1.STATUS,'Task' as WF_OBJ FROM WF_RUN R_1 INNER JOIN WBS_OBJECTS WO ON WO.TASK_ID=R_1.OBJECT_ID AND WO.LINK_TYPE=0 AND WO.OBJECT_TYPE=0 WHERE R_1.OBJECT_TYPE='TSK' AND WO.OBJECT_ID = DOC_INFO.IDOC_ID ) WF_RUN_TD LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH1 ON WF_RUN_TD.RUN_ID=WRRH1.RUN_ID LEFT JOIN WF_RUN_STAGE_STATUS WFRSS ON WF_RUN_TD.RUN_ID=WFRSS.RUN_ID AND WFRSS.STATUS=0 LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH ON WF_RUN_TD.RUN_ID=WRRH.RUN_ID AND WRRH.STATUS=0 AND WFRSS.ROUTING_ID=WRRH.ROUTING_ID LEFT JOIN ORDER_MASTER OM ON DOC_INFO.ORDER_ID=OM.ORDER_ID LEFT JOIN ORDER_AREA ON ORDER_AREA.ORD_AREA_ID = DOC_INFO.AREACODE_ID AND ORDER_AREA.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORDER_SYSTEM ON ORDER_SYSTEM.ORD_SYS_ID = DOC_INFO.SYSTEM_ID AND ORDER_SYSTEM.ORDER_ID = DOC_INFO.ORDER_ID LEFT JOIN ORIGIN ON ORIGIN.ORIGIN_CODE=DOC_INFO.DOC_ORIGIN AND ORIGIN.ORIGIN=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN DOC_ORG_MSTR DOM ON DOM.ID=DOC_INFO.DOC_ORIGIN_TYPE LEFT JOIN GENEALOGY G ON DOC_INFO.GENO_KEY=G.GENO_KEY LEFT JOIN GENEALOGY G1 ON OM.GENO_KEY=G1.GENO_KEY LEFT JOIN DOC_APR_REQ_CODES_MSTR ON DOC_APR_REQ_CODES_MSTR.APR_REQ_CODE=DOC_INFO.APR_REQ_CODE LEFT JOIN INT_STAT_MSTR on DOC_INFO.INT_APPR_STATUS_CODE=INT_STAT_MSTR.INTR_STAT_CODE LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME = DOC_CORRESPONDENCE.REPLY_RECEIVED_BY LEFT JOIN USER_MSTR UM1 ON UM1.LOGIN_NAME = FILE_INDEX.COUT_BY LEFT JOIN USER_MSTR UM2 ON UM2.LOGIN_NAME = DOC_INFO.CREATED_BY LEFT JOIN ORDER_LCS ON ORDER_LCS.ORDER_ID=OM.ORDER_ID AND DOC_INFO.LCS_CODE= ORDER_LCS.STATUS_CODE LEFT JOIN DOC_SIZE_MSTR ON SHT_SIZE=DOC_INFO.SHEET_SIZE LEFT JOIN LIFECYCLE_STATUS_MASTER ON DOC_INFO.LCS_CODE = LIFECYCLE_STATUS_MASTER.STATUS_CODE OUTER APPLY (SELECT COUNT(1) AS DOC_COUNT FROM DOC_REF_DOC REF where REF.IDOC_ID=DOC_INFO.IDOC_ID AND REF.DOC_INT_REV_NO = DOC_INFO.INT_REV_NO ) AS DRD OUTER APPLY (SELECT COUNT(1) AS XREF_COUNT from DOC_INFO_LINKS LINKS where LINKS.IDOC_ID=DOC_INFO.IDOC_ID AND LINKS.INT_REV_NO =DOC_INFO.INT_REV_NO ) AS DXREF --LEFT JOIN CUSTOM_OBJECT_CHAR_DETAILS WITH (FORCESCAN) ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_TYPE =0 --AND CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_DOC_CHAR_DETAILS CUSTOM_OBJECT_CHAR_DETAILS ON CUSTOM_OBJECT_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN CUSTOM_OBJECT_CHAR_LOV_DETAILS COCLD ON COCLD.OBJECT_TYPE = 0 AND COCLD.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN PERSONAL_FOLDER PF ON PF.FOLDER_ID = 10230294 AND PF.OBJECT_TYPE = 19 LEFT JOIN PERSONAL_FOLDER_OBJECTS PFO ON PF.FOLDER_ID = PFO.FOLDER_ID AND PFO.OBJECT_ID = DOC_INFO.IDOC_ID LEFT JOIN WF_TEAM_STAGE_DET WFTD ON WFTD.STAGE_ID=WFRSS.STAGE_ID AND WFTD.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_RUN_ROUTING_HISTORY WRRH2 ON WF_RUN_TD.RUN_ID=WRRH2.RUN_ID LEFT JOIN WF_TEAM_MST WTM ON WTM.WF_TEAM_ID=WF_RUN_TD.WF_TEAM_ID LEFT JOIN WF_TEAM_STAGE_DET WFTS ON WRRH.SENT_TO_STAGE_ID=WFTS.STAGE_ID AND WF_RUN_TD.WF_TEAM_ID=WFTS.WF_TEAM_ID LEFT JOIN USER_MSTR UM4 ON UM4.LOGIN_NAME = WRRH.SENT_BY LEFT JOIN USER_MSTR UM_DI ON UM_DI.LOGIN_NAME = DOC_INFO.LAST_EDITED_BY INNER JOIN DOC_STATUS_MSTR DSM ON DSM.DOC_STATUS_ID = DOC_INFO.DOC_STATUS LEFT JOIN USER_MSTR UM_APPROVE ON UM_APPROVE.LOGIN_NAME = DOC_INFO.APPROVED_BY OUTER APPLY (SELECT TOP 1 USER_MSTR.USER_NAME AS REPLIED_BY, DOC_CORRESPONDENCE_DET.REPLIED_ON FROM DOC_CORRESPONDENCE_DET LEFT JOIN USER_MSTR ON USER_MSTR.LOGIN_NAME=DOC_CORRESPONDENCE_DET.REPLIED_BY WHERE DOC_CORRESPONDENCE_DET.PARENT_CORR_ID=DCD.CORR_ID AND DOC_CORRESPONDENCE_DET.REPLIED_ON IS NOT NULL ORDER BY DOC_CORRESPONDENCE_DET.REPLIED_ON ASC )Z LEFT JOIN USER_MSTR UM5 ON UM5.LOGIN_NAME = DOC_CORRESPONDENCE.REPLAY_EXPECTED_FROM LEFT OUTER JOIN USER_MSTR UM6 ON UM6.LOGIN_NAME = DOC_CORRESPONDENCE.MAIL_FROM CROSS APPLY ( SELECT CASE WHEN N'10230294' <> '' AND WTM.SHOW_ONLY_ASSIGNED_ORIGINS = 1 AND N'10230294' <> WFRSS.SENT_BY_ORIGIN THEN WFRSS.SENT_BY_ORIGIN ELSE UM4.USER_NAME END AS SENT_BY_USER )DRV LEFT JOIN EXTENDED_DOC_CHAR_DETAILS EXTENDED_DOC_CHAR_DETAILS ON EXTENDED_DOC_CHAR_DETAILS.OBJECT_ID = DOC_INFO.IDOC_ID WHERE DOC_INFO.IS_CONFIDENTIAL <> 1 AND ( (DOC_INFO.ORDER_ID = N'1013244') ) OR (EXISTS ( SELECT 1 FROM PERSONAL_FOLDER_OBJECTS PFO INNER JOIN PERSONAL_FOLDER PF ON PFO.FOLDER_ID = PF.FOLDER_ID AND PF.OBJECT_TYPE = 19 WHERE PF.FOLDER_ID =10230294 AND PF.OBJECT_TYPE = 19 AND PFO.OBJECT_ID = doc_info.idoc_id) ) ) DIST_RECORDS ) A ------------------------------------------------------------------------------------------------------------------------------------------------------