The beauty of sql for configuring vision

by | Dec 30, 2009 | Uncategorized | 0 comments

If you have labor cross charge or global over-head projects implemented in your enterprise where there is a phase for each profit center, you might have run into the pain of keeping all those over-head projects synchronized with the profit centers (organizational units).


While taking a client of mine to multi-company, I noticed that several of their over-head projects created specifically for configuration purposes and time keeping purposes were out of synch and missing profit centers (organizations) that matched to phases on those projects.  Rather than go through a list of phases on the project and compare by hand the list of profit centers then duplicate a phase for each profit center, which can be a laborious and error-ridden task, I wrote a script which does it automatically.  This script is in the form of a stored procedure which compares the organization table to the structure of a given project, and adds the proper phases to it.  

You’ll notice in my version of the script I call some functions for company, dept, and office… this is because the database is in transition from single to multi company, and I needed those scripts to work before the transition as well.

If you would like me to help you adapt this script to your enterprise let me know.

Oh, one more thing… we did a test run of the configuration of the entire database for multicompany from single company yesterday. With the work that I did automating the configuration (including intercompany billing) it took a total of 2 hours to convert a copy of our live database to multicompany.  That’s pretty fast if you’ve ever done a multicompany configuration.  What’s more, it included lots of cleanup of general ledger entries that were missing profit centers (organizations), key conversions for phases that had the wrong profit center (organization) and configuration for IC Billing with detailed subledgers for 40 profit centers (org units).

If you need help going to multicompany, you should get in touch with me.

Here is the script I refer to at the beginning of this entry.  It handles one project at a time:
(use at your own risk of course… it was written for a specific client so won’t work on your database unless you modify it first)




create procedure fmv_pr_admin_overhead_project_segment_check
(@wbs1 varchar(32), @chargetype varchar(1))
as
BEGIN
/* overhead project missing segments creator */

/*  12/30/09  LS

logic

1. get a segment row that is in the existing project
2. for each organization that does not have a matching segment, insert a segment row (use one insert for this, not a cursor)
3. use values from step 1 for default values in new rows (except org and segment number values)

select * from organization
select * from pr where wbs1 like ‘oh-%’
*/

declare @wbs2 varchar(16)

set @wbs2 = (select top 1 wbs2 from pr where wbs1 = @wbs1 and sublevel = ‘N’)

insert pr

SELECT top 100 percent
 @wbs1 as ‘wbs1’
      ,dbo.fmv_fn_office(og.org) + ‘:’ 
  + (case 
    when dbo.fmv_fn_company(og.org)’FMV’ and dbo.fmv_fn_dept(og.org)=’00’ 
     then ’02’ 
    else dbo.fmv_fn_dept(og.org) 
   end) as ‘wbs2’
      ,[WBS3]
      ,og.name as ‘name’
      ,@chargetype as ‘chargetype’
      ,’N’ as ‘sublevel’
      ,[Principal]
      ,[ProjMgr]
      ,[Supervisor]
      ,[ClientID]
      ,[CLAddress]
      ,[Fee]
      ,[ReimbAllow]
      ,[ConsultFee]
      ,[BudOHRate]
      ,[Status]
      ,[RevType]
      ,[MultAmt]
      ,og.org as ‘org’
      ,[UnitTable]
      ,[StartDate]
      ,[EndDate]
      ,[PctComp]
      ,[LabPctComp]
      ,[ExpPctComp]
      ,[BillByDefault]
      ,[BillableWarning]
      ,[Memo]
      ,[BudgetedFlag]
      ,[BudgetedLevels]
      ,[BillWBS1]
      ,[BillWBS2]
      ,[BillWBS3]
      ,[XCharge]
      ,[XChargeMethod]
      ,[XChargeMult]
      ,[Description]
      ,[Closed]
      ,[ReadOnly]
      ,[DefaultEffortDriven]
      ,[DefaultTaskType]
      ,[VersionID]
      ,[ContactID]
      ,[CLBillingAddr]
      ,[LongName]
      ,[Address1]
      ,[Address2]
      ,[Address3]
      ,[City]
      ,[State]
      ,[Zip]
      ,[County]
      ,[Country]
      ,[FederalInd]
      ,[ProjectType]
      ,[Responsibility]
      ,[Referable]
      ,[EstCompletionDate]
      ,[ActCompletionDate]
      ,[ContractDate]
      ,[BidDate]
      ,[ComplDateComment]
      ,[FirmCost]
      ,[FirmCostComment]
      ,[TotalProjectCost]
      ,[TotalCostComment]
      ,[OpportunityID]
      ,[ClientConfidential]
      ,[ClientAlias]
      ,[AvailableForCRM]
      ,[ReadyForApproval]
      ,[ReadyForProcessing]
      ,[BillingClientID]
      ,[BillingContactID]
      ,[Phone]
      ,[Fax]
      ,[EMail]
      ,[ProposalWBS1]
      ,[CostRateMeth]
      ,[CostRateTableNo]
      ,[PayRateMeth]
      ,[PayRateTableNo]
      ,[Locale]
      ,[LineItemApproval]
      ,[LineItemApprovalEK]
      ,[BudgetSource]
      ,[BudgetLevel]
      ,[ProfServicesComplDate]
      ,[ConstComplDate]
      ,[ProjectCurrencyCode]
      ,[ProjectExchangeRate]
      ,[BillingCurrencyCode]
      ,[BillingExchangeRate]
      ,[RestrictChargeCompanies]
      ,[FeeBillingCurrency]
      ,[ReimbAllowBillingCurrency]
      ,[ConsultFeeBillingCurrency]
      ,[RevUpsetLimits]
      ,[RevUpsetWBS2]
      ,[RevUpsetWBS3]
      ,[RevUpsetIncludeComp]
      ,[RevUpsetIncludeCons]
      ,[RevUpsetIncludeReimb]
      ,[PORMBRate]
      ,[POCNSRate]
      ,’ADMIN’ as ‘createuser’
      ,getdate() as ‘createdate’
      ,null as ‘moduser’
      ,null as ‘moddate’
      ,[PlanID]
      ,[TKCheckRPDate]
      ,[ICBillingLab]
      ,[ICBillingLabMethod]
      ,[ICBillingLabMult]
      ,[ICBillingExp]
      ,[ICBillingExpMethod]
      ,[ICBillingExpMult]
      ,[RequireComments]
      ,[TKCheckRPPlannedHrs]
      ,[BillByDefaultConsultants]
      ,[BillByDefaultOtherExp]
      ,[BillByDefaultORTable]
      ,[PhoneFormat]
      ,[FaxFormat]
      ,[RevType2]
      ,[RevType3]
      ,[RevType4]
      ,[RevType5]
      ,[RevUpsetCategoryToAdjust]
  from pr, organization og
 left join (select org, ‘present’ as ‘present’ from pr where wbs1 = @wbs1 and sublevel = ‘N’) existingOrgs
 on og.org = existingOrgs.org
 where existingOrgs.present is null
 and pr.wbs1 = @wbs1
 and pr.wbs2 = @wbs2
 order by dbo.fmv_fn_office(og.org) + ‘:’ + dbo.fmv_fn_dept(og.org) 


END

Have a great day!


Submit a Comment