SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[sp_getSummary_S] AS BEGIN ( Select usr_nm, usr_cd, (select count(*) from action_taken where status = 'Open' and sent_to_usr_code=userdet.usr_cd ) as [Letters To Receive], ( ((SELECT COUNT(*) FROM action_taken AS at INNER JOIN userdet AS u ON u.usr_cd = at.sender_usr_code INNER JOIN register AS r ON at.receiver_reg_code = r.reg_code INNER JOIN let_entry AS e ON at.let_no = e.let_no WHERE (at.sent_to_usr_code = userdet.usr_cd) AND (at.status = 'Received') and (e.status = 'Received') and (e.cur_usr_code = userdet.usr_cd) and let_file_status=18 and isnull(at.multiple_marked,'')=0 and e.let_no not in (Select let_no from file_let_det ) ) + (SELECT COUNT(*) FROM let_entry AS e INNER JOIN userdet AS u ON u.usr_cd = e.org_usr_code INNER JOIN register AS r ON e.reg_code = r.reg_code WHERE (e.org_usr_code = userdet.usr_cd) AND (e.status = 'Created') and e.let_no not in (Select let_no from file_let_det)) + (SELECT COUNT(*) FROM action_taken AS at INNER JOIN userdet AS u ON u.usr_cd = at.sender_usr_code INNER JOIN register AS r ON at.receiver_reg_code = r.reg_code INNER JOIN let_entry AS e ON at.let_no = e.let_no INNER JOIN let_mult_marked M on at.let_no=M.let_no WHERE (at.sent_to_usr_code = userdet.usr_cd) AND (at.status = 'Received') and (e.status = 'Received') and ( M.cur_usr_code=userdet.usr_cd) and let_file_status=18 and at.multiple_marked=1))) as [Letters To Mark], ( (SELECT count(*) from file_entry WHERE (section_code =userdet.usr_sec_code) and file_movement='Yes' AND (file_status = 'Open' or file_status ='Gaurd File/Close File') and file_creation_usr =userdet.usr_cd and (select count(*) from file_action_taken where file_code=file_entry.file_code and file_part=file_entry.file_part) = 0 ) + (SELECT count(*) from file_action_taken fat inner join file_entry fe on fat.file_code = fe.file_code WHERE fat.sent_to_usr_code = userdet.usr_cd AND (fat.status = 'Received') and isnull(fe.file_movement,'') = 'Yes') ) as [Files To Mark], ( (SELECT COUNT(*) AS Expr1 FROM file_action_taken WHERE (sent_to_usr_code = userdet.usr_cd) AND (status = 'Open')) ) as [Files To Receive] From userdet WHERE usr_type='SU' and usr_cd<>29 group by usr_nm,usr_cd,usr_sec_code ) END
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)