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).
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