Hi everyone - please save the date for what is likely our last gathering of 2022 before the holidays!
~~~
Seattle Rep is inviting you to a scheduled Zoom meeting.
Topic: PNWTC November GatheringTime: Nov 29, 2022 11:00 AM Pacific Time (US and Canada)
Join Zoom Meetinghttps://us02web.zoom.us/j/81850179790?pwd=Y1JWMWgvTTRRYm9PZHhNTnZVNkF2dz09
Meeting ID: 818 5017 9790Passcode: 780650One tap mobile+12532158782,,81850179790# US (Tacoma)+13462487799,,81850179790# US (Houston)
Dial by your location +1 253 215 8782 US (Tacoma) +1 346 248 7799 US (Houston) +1 408 638 0968 US (San Jose) +1 669 444 9171 US +1 669 900 6833 US (San Jose) +1 719 359 4580 US +1 312 626 6799 US (Chicago) +1 360 209 5623 US +1 386 347 5053 US +1 564 217 2000 US +1 646 876 9923 US (New York) +1 646 931 3860 US +1 301 715 8592 US (Washington DC) +1 309 205 3325 USMeeting ID: 818 5017 9790Find your local number: https://us02web.zoom.us/u/kerv99EqA6
On second pass, based on the descriptions I think these are all super old reports that aren't in use anymore. So, nevermind. I think I'm good!
Do you have any tips for how to find which objects in infomaker correspond to the objects listed in the first section of this?
Thank you! This is so helpful.
Lastly, here is a script to search for Memb or the membership tables in list criteria, attributes, output elements, and report procedures. (The section at the bottom searching on SQL Jobs doesn't work for me I think because we are on RAMP).
USE [Impresario] -- search stored procedures for TX_CUST_MEMBERSHIP table SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%TX_CUST_MEMBERSHIP%' and (OBJECT_NAME(object_id) like 'L%' OR OBJECT_NAME(object_id) like 'SO%') -- order by OBJECT_NAME(object_id) -- search stored procedures for TX_CONT_MEMB table SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id) FROM sys.procedures WHERE OBJECT_DEFINITION(object_id) LIKE '%TX_CONT_MEMB%' and (OBJECT_NAME(object_id) like 'L%' OR OBJECT_NAME(object_id) like 'SO%') -- order by OBJECT_NAME(object_id) -- search report tables for Memb in Report Parameters select category_desc = c.description, rp.name, rp.psr_file, rp.report_type, rp.inactive -- parameter_name = grp.description, from gooesoft_request r left outer join gooesoft_request_parameter p on r.report_id = p.report_id and r.id = p.request_id join gooesoft_report rp on r.report_id = rp.id left outer join gooesoft_report_parameter grp on rp.id = grp.report_id and p.parameter_id = grp.id join gooesoft_report_category c on rp.category = c.id where grp.description like '%Memb%' and rp.name like 'SO%' group by c.description, rp.name, rp.psr_file, rp.report_type, rp.inactive -- grp.description order by c.description, rp.name -- seach for Memb for criteria elements, attributes, custom table fields select * from T_KEYWORD where description like '%Memb%' -- search for Memb in output elements select q.description, q.data_select, Group_desc = g.description, g.data_from, g.data_where from TR_QUERY_ELEMENT q join TR_QUERY_ELEMENT_GROUP g on q.group_id = g.id where (q.description like '%Memb%' or g.description like '%Memb%') -- 27 and q.inactive = 'N' -- 24 -- search for Memb in SQL Jobs USE [msdb] SELECT s.step_id as 'Step ID', j.[name] as 'SQL Agent Job Name', s.database_name as 'DB Name', s.command as 'Command' FROM msdb.dbo.sysjobsteps AS s INNER JOIN msdb.dbo.sysjobs AS j ON s.job_id = j.job_id WHERE j.[name] not like 'S%' and s.database_name = 'Impresario' and s.command LIKE '%Memb%' SELECT * FROM msdb.dbo.sysjobs j WHERE j.[name] not like 'S%' -- The SELECT permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'.
USE [Impresario] GO /****** Object: StoredProcedure [dbo].[so_job_update_all_prog_list_donation_level] Script Date: 11/29/2022 12:53:19 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create PROCEDURE [dbo].[so_job_update_all_prog_list_donation_level] AS /* $Author: adminjo $ $Date: 2003/08/19 19:12:17 $ $Id: so_update_all_prog_list_donation_level.sql,v 1.4 2003/08/19 19:12:17 adminjo Exp $ description: this procedure looks at all records in tx_cust_program, and assigns donation level with matching membership level for for customers having Annual Individual, Annual Corporate/Foundation/Government, Annual In-Kind, Project Restricted, or Ring memberships. It presupposes that somewhere in the program donation level description there is a 3 character string enclosed in brackets that corresponds to the 3 character memb_level field in the tx_cust_membership record. 11/13/2001 created by jeff stetson 12/9/2003 Chris Roe - to rely solely on membership levels 11/2/2011 Chris Roe - to add some new program types, some based on contribution totals and some based on mbership as above 4/22/13 Stuart - only update Annual Ind and Ring memb orgs/program name types / it was overwriting manually entered #3 Encore Society programs 7/2/13 Stuart - July 1, 2013 changing memb org from Annual Ind (1) to Annual Fund (18) so change program Annual Ind (1) to Annual Fund (21) - commented out program updates at bottom of non-membership based donation levels at Devo's request 12/7/15 Stuart - added secton taken from SO_JOB_UPDATE_MEMBERSHIPS to update Program Name by what the Memb Level would be if Non-Benefit $ wasn't subtracted 1/6/16 stuart changed procedure from so_update_all_prog_list_donation_level to so_job_update_all_prog_list_donation_level 1/6/16 Stuart changed Monthly Giving section from > to >= 14/14/22 Stuart removed parameters; added clause at end to only update if there is a change to Donation Level 11/29/22 Stuart removed Ring program no and added notes for v16 upgrade grant execute on so_job_update_all_prog_list_donation_level to ImpUsers grant execute on so_job_update_all_prog_list_donation_level to Tessitura_app */ BEGIN set nocount on DECLARE @cust_m TABLE (customer_no int, esal1_desc varchar(55), cont_total money, memb_org_no int, memb_level varchar(3), current_status int, program_no int, program_desc varchar(20), level_id int, level_desc varchar(30)) insert into @cust_m (customer_no, memb_org_no, memb_level, current_status, program_no) select customer_no, memb_org_no, memb_level, current_status, program_no = case memb_org_no when 18 then 21 end -- Annual Fund memb org sets Annual Fund program from tx_cust_membership m -- substitute for table T_Membership where memb_org_no in (18) -- Annual Fund and current_status in (2,4,7) -- Active, Awaiting Payment, Lapsed -- substitute for view VS_Membership_Current lookup group by customer_no, memb_org_no, memb_level, current_status, case memb_org_no when 18 then 21 when 3 then 4 end delete from @cust_m where current_status = 3 and customer_no in (select distinct customer_no from @cust_m where current_status in (2,4,7)) ----------------- calculate memb level without subtracting the Non-Benefit $ attributes - new 12/7/15 taken from SO_JOB_UPDATE_MEMBERSHIPS ------------- DECLARE @memb_level TABLE (customer_no int, memb_level varchar(3)) -- memb_level changes to ID or short_desc from T_MEMBERSHIP_LEVEL with v16 -- 1) insert all current memb_levels insert @memb_level (customer_no, memb_level) select cm.customer_no, cm.memb_level from TX_CUST_MEMBERSHIP cm with (Nolock) -- substitute for table T_Membership join T_CAMPAIGN cp with (Nolock) on cm.campaign_no = cp.campaign_no where cm.memb_org_no = 18 -- Annual Fund and cm.cur_record = 'Y' and cm.current_status = 2 -- Active -- substitute for view VS_Membership_Current and GETDATE() between cp.start_dt and cp.end_dt and cp.description like '%Annual%' group by cm.customer_no, cm.memb_level declare @current_year varchar(4), @start_dt datetime, @end_dt datetime IF DATEPART(mm,getdate()) in ('01','02','03','04','05','06') Begin Set @start_dt = cast(datepart(yyyy,DATEADD(yyyy,-1,getdate())) as varchar) + '-07-01' Set @end_dt = cast(DATEPART(yyyy,getdate()) as varchar) + '-06-30 23:59' --select @start_dt, @end_dt -- testing End Else Begin Set @start_dt = cast(DATEPART(yyyy,getdate()) as varchar) + '-07-01' Set @end_dt = cast(DATEPART(yyyy,DATEADD(yyyy,1,getdate())) as varchar) + '-06-30 23:59' --select @start_dt, @end_dt -- testing End -- 2) update memb_levels by Giving FY attributes Update @memb_level set memb_level = case when ac.key_value is not null and (cast(ISNULL(ac.key_value,0) as money)) >= (cast(ISNULL(a1.key_value,0) as money)) -- current >= last year and ac.key_value is not null and (cast(ISNULL(ac.key_value,0) as money)) >= (cast(ISNULL(an.key_value,0) as money)) then ml1.memb_level -- and current >= next year when a1.key_value is not null and (cast(ISNULL(a1.key_value,0) as money)) > (cast(ISNULL(ac.key_value,0) as money)) -- last year > current and a1.key_value is not null and (cast(ISNULL(a1.key_value,0) as money)) >= (cast(ISNULL(an.key_value,0) as money)) then ml2.memb_level -- and last year >= next year when an.key_value is not null and (cast(ISNULL(an.key_value,0) as money)) > (cast(ISNULL(ac.key_value,0) as money)) -- next year > current and an.key_value is not null and (cast(ISNULL(an.key_value,0) as money)) > (cast(ISNULL(a1.key_value,0) as money)) then mln.memb_level -- and next year > last year when ac.key_value is null and a1.key_value is null and an.key_value is null then 'UND' end -- if no Giving attributes Stuart added 9/4/15 from @memb_level mlv join TX_CUST_MEMBERSHIP cm with (Nolock) on mlv.customer_no = cm.customer_no -- substitute for table T_Membership join T_MEMB_LEVEL ml with (Nolock) on cm.memb_level = ml.memb_level and ml.inactive <> 'Y' -- substitute for T_MEMBERSHIP_LEVEL join T_CUSTOMER c with (Nolock) on cm.customer_no = c.customer_no left outer join TX_CUST_KEYWORD ac with (Nolock) on c.customer_no = ac.customer_no and ac.keyword_no in (587) -- Giving FY (Current) left outer join TX_CUST_KEYWORD a1 with (Nolock) on c.customer_no = a1.customer_no and a1.keyword_no in (586) -- Giving FY-1yr left outer join TX_CUST_KEYWORD an with (Nolock) on c.customer_no = an.customer_no and an.keyword_no in (709) -- Giving FY (+1) left outer join T_MEMB_LEVEL ml1 with (Nolock) on ml1.memb_org_no = 18 and ml1.inactive <> 'Y' and (cast(ISNULL(ac.key_value,0) as money)) between ml1.start_amt and ml1.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL ml2 with (Nolock) on ml2.memb_org_no = 18 and ml2.inactive <> 'Y' and (cast(ISNULL(a1.key_value,0) as money)) between ml2.start_amt and ml2.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL mln with (Nolock) on mln.memb_org_no = 18 and mln.inactive <> 'Y' and (cast(ISNULL(an.key_value,0) as money)) between mln.start_amt and mln.end_amt -- 18 = Annual Fund left outer join (select customer_no, campaign_no from T_CONTRIBUTION with (Nolock) where billing_type = 9 -- Monthly Giving GROUP by customer_no, campaign_no) emg on emg.campaign_no = cm.campaign_no and emg.customer_no = cm.customer_no where cm.memb_org_no = 18 -- Annual Fund and cm.cur_record = 'Y' and cm.current_status = 2 -- Active -- substitute for view VS_Membership_Current and emg.customer_no is null and c.customer_no not in (select a.customer_no from T_CUST_ACTIVITY a join TR_CUST_ACTIVITY_TYPE at on a.activity_type = at.id left outer join (select max(IsAc_no) as isAc_no, customer_no, activity_no from T_ISSUE_ACTION ac Group by customer_no, activity_no) ia1 on a.activity_no = ia1.activity_no and a.customer_no = ia1.customer_no left outer join T_ISSUE_ACTION ia on a.activity_no = ia.activity_no and a.customer_no = ia.customer_no and ia1.isAc_no = ia.IsAc_no where a.activity_type = 204 -- Auto Membership Disable and at.category = 25 -- Development Issue and (ia.res_ind is null or ia.res_ind = 'N')) -- only open CSI's added 8/13/15 -- 3) overwrite those if they have Monthly Giving Pledge custom cont field ------------------------------- Update @memb_level set memb_level = case when cast(isnull(mga.key_value,0) as money) * 12 >= (cast(ISNULL(ac.key_value,0) as money)) -- Monthly Giving Amt > current giving and cast(isnull(mga.key_value,0) as money) * 12 >= (cast(ISNULL(a1.key_value,0) as money)) -- Monthly Giving Amt > last year giving and cast(isnull(mga.key_value,0) as money) * 12 >= (cast(ISNULL(an.key_value,0) as money)) -- Monthly Giving Amt > next year giving and cast(isnull(mga.key_value,0) as money) * 12 >= -- Monthly Giving Amt > (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money))-- (Monthly Giving Amt X # months left in FY) + current giving then mlY.memb_level when (cast(ISNULL(ac.key_value,0) as money)) >= cast(isnull(mga.key_value,0) as money) * 12 -- current giving > Monthly Giving Amt and (cast(ISNULL(ac.key_value,0) as money)) >= (cast(ISNULL(a1.key_value,0) as money)) -- current giving > last year giving and (cast(ISNULL(ac.key_value,0) as money)) >= (cast(ISNULL(an.key_value,0) as money)) -- current giving > next year giving and (cast(ISNULL(ac.key_value,0) as money)) >= -- current giving > (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) -- (Monthly Giving Amt X # months left in FY) + current giving then ml1.memb_level when (cast(ISNULL(an.key_value,0) as money)) >= cast(isnull(mga.key_value,0) as money) * 12 -- next year giving > Monthly Giving Amt and (cast(ISNULL(an.key_value,0) as money)) >= (cast(ISNULL(ac.key_value,0) as money)) -- next year giving > current giving and (cast(ISNULL(an.key_value,0) as money)) >= (cast(ISNULL(a1.key_value,0) as money)) -- next year giving > last year giving and (cast(ISNULL(an.key_value,0) as money)) >= -- next year giving > (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) -- (Monthly Giving Amt X # months left in FY) + current giving then mln.memb_level when (cast(ISNULL(a1.key_value,0) as money)) >= cast(isnull(mga.key_value,0) as money) * 12 -- last year giving > Monthly Giving Amt and (cast(ISNULL(a1.key_value,0) as money)) >= (cast(ISNULL(ac.key_value,0) as money)) -- last year giving > current giving and (cast(ISNULL(a1.key_value,0) as money)) >= (cast(ISNULL(an.key_value,0) as money)) -- last year giving > next year giving and (cast(ISNULL(a1.key_value,0) as money)) >= -- last year giving > (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money))-- (Monthly Giving Amt X # months left in FY) + current giving then ml2.memb_level when (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) -- (Monthly Giving Amt X # months left in FY) + current giving >= (cast(ISNULL(ac.key_value,0) as money)) -- > current giving and (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) -- (Monthly Giving Amt X # months left in FY) + current giving >= (cast(ISNULL(a1.key_value,0) as money)) -- > last year giving and (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) -- (Monthly Giving Amt X # months left in FY) + current giving >= (cast(ISNULL(an.key_value,0) as money)) -- > next year giving then mlFY.memb_level end from @memb_level mlv join TX_CUST_MEMBERSHIP cm with (Nolock) on mlv.customer_no = cm.customer_no -- substitute for table T_Membership join T_MEMB_LEVEL ml with (Nolock) on cm.memb_level = ml.memb_level and ml.inactive <> 'Y' -- substitute for T_MEMBERSHIP_LEVEL join T_CAMPAIGN cp with (Nolock) on cm.memb_org_no = cp.memb_org_no and cm.campaign_no = cp.campaign_no join T_CONTRIBUTION c with (Nolock) on cp.campaign_no = c.campaign_no and cm.customer_no = c.customer_no join TX_CUST_KEYWORD mga with (Nolock) on cm.customer_no = mga.customer_no and mga.keyword_no = 748 -- Monthly Giving Amount left outer join TX_CUST_KEYWORD ac with (Nolock) on c.customer_no = ac.customer_no and ac.keyword_no in (587) -- Giving FY (Current) left outer join TX_CUST_KEYWORD a1 with (Nolock) on c.customer_no = a1.customer_no and a1.keyword_no in (586) -- Giving FY-1yr left outer join TX_CUST_KEYWORD an with (Nolock) on c.customer_no = an.customer_no and an.keyword_no in (709) -- Giving FY (+1) left outer join T_MEMB_LEVEL ml1 with (Nolock) on ml1.memb_org_no = 18 and ml1.inactive <> 'Y' and (cast(ISNULL(ac.key_value,0) as money)) between ml1.start_amt and ml1.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL ml2 with (Nolock) on ml2.memb_org_no = 18 and ml2.inactive <> 'Y' and (cast(ISNULL(a1.key_value,0) as money)) between ml2.start_amt and ml2.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL mln with (Nolock) on mln.memb_org_no = 18 and mln.inactive <> 'Y' and (cast(ISNULL(an.key_value,0) as money)) between mln.start_amt and mln.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL mlY with (Nolock) on mlY.memb_org_no = 18 and mlY.inactive <> 'Y' and cast(isnull(mga.key_value,0) as money) * 12 between mlY.start_amt and mlY.end_amt -- 18 = Annual Fund left outer join T_MEMB_LEVEL mlFY with (Nolock) on mlFY.memb_org_no = 18 and mlFY.inactive <> 'Y' and (cast(isnull(mga.key_value,0) as money) * cast(DATEDIFF(MM, GETDATE(),cp.end_dt) as int)) + (cast(ISNULL(ac.key_value,0) as money)) between mlFY.start_amt and mlFY.end_amt -- 18 = Annual Fund -- new memb join to get level for Monthly Give Amt X # of months left in FY + Current Giving where cm.memb_org_no = 18 -- Annual Fund and GETDATE() between cp.start_dt and cp.end_dt and cp.description like '%Annual Fund' and cp.category = 3 -- Annual Fund and c.billing_type = 9 -- Monthly Giving -- Stuart added changed this to OR 11/6/14 - new way and cm.cur_record = 'Y' and cm.current_status = 2 -- Active -- 24 and c.customer_no not in (select a.customer_no from T_CUST_ACTIVITY a join TR_CUST_ACTIVITY_TYPE at on a.activity_type = at.id left outer join (select max(IsAc_no) as isAc_no, customer_no, activity_no from T_ISSUE_ACTION ac Group by customer_no, activity_no) ia1 on a.activity_no = ia1.activity_no and a.customer_no = ia1.customer_no left outer join T_ISSUE_ACTION ia on a.activity_no = ia.activity_no and a.customer_no = ia.customer_no and ia1.isAc_no = ia.IsAc_no where a.activity_type = 204 -- Auto Membership Disable and at.category = 25 -- Development Issue and (ia.res_ind is null or ia.res_ind = 'N')) -- only open CSI's added 8/13/15 -------------------- end of new section ------------------------------------------------------------------------------------------------------------------- update @cust_m set level_id = l.id from @cust_m cm join @memb_level ml on cm.customer_no = ml.customer_no join tr_donation_level l on l.description like '%[[]' + ml.memb_level + ']' and l.inactive = 'N' -- Update Program Name Donation Level update tx_cust_program set donation_level = isnull(cm.level_id, 23), last_update_dt = GETDATE(), last_updated_by = 'SQLJob' from tx_cust_program xp left join @cust_m cm on cm.customer_no = xp.customer_no and cm.program_no = xp.program_no where xp.program_no in (21) -- Annual Fund and xp.donation_level not like isnull(cm.level_id, 23) -- only update if there is a change -- 10/14/22 END