Post

1 follower Follow
0
Avatar

Un-cancelling work orders

One of my users recently had a brain freeze and inadvertently cancelled all the work orders in his department, not the one or 2 he thought he was cancelling.  We've all been there!

I was able to script a recovery process using Import and Update to find the munged work orders and restore their ertswhile status (below).  It filters work order status tracking history looking for ones that 1) are currently in cancelled status, 2) were changed to that status in a particular time period.  It then sets the work order status to the 'from' status of the most recent status change (in case your time period is so broad that it includes multiple status changes for a given work order).

To use this yourself, import the Import/Update task below and adjust the BETWEEN clause in the query (on the Data Source tab) to suit your situation.  You'll also have to update the data source to connect to your Lucity database as well.

<?xml version="1.0" encoding="utf-8"?>
<Setup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<UseWindowsAccount>false</UseWindowsAccount>
<LucityLoginName />
<ShowFullError>false</ShowFullError>
<PerformanceOptimized>false</PerformanceOptimized>
<DisplayAdvancedMappingFields>false</DisplayAdvancedMappingFields>
<DisplayedTrackingDays>90</DisplayedTrackingDays>
<TreatWarningsAsErrors>false</TreatWarningsAsErrors>
<ImportSettings>
<ImportSetting>
<PopulatedMappings>
<ImportMapping>
<DestinationModuleID>WorkWorkOrder</DestinationModuleID>
<ModuleID>WorkWorkOrder</ModuleID>
<PropertyName>CategoryCode</PropertyName>
<Formula>=A3</Formula>
<Sequence>999</Sequence>
<Unique>false</Unique>
</ImportMapping>
<ImportMapping>
<DestinationModuleID>WorkWorkOrder</DestinationModuleID>
<ModuleID>WorkWorkOrder</ModuleID>
<PropertyName>StatusType</PropertyName>
<Formula>=A2</Formula>
<Sequence>999</Sequence>
<Unique>false</Unique>
</ImportMapping>
<ImportMapping>
<DestinationModuleID>WorkWorkOrder</DestinationModuleID>
<ModuleID>WorkWorkOrder</ModuleID>
<PropertyName>WorkOrderNumber</PropertyName>
<Formula>=A1</Formula>
<Sequence>999</Sequence>
<Unique>true</Unique>
</ImportMapping>
</PopulatedMappings>
<Correlations />
<ColumnsSchema />
<ID>143</ID>
<Name>Un_cancel_wo</Name>
<Description>In case someone does a WO cancel on an unintended and long list ofwork orders. It happens!</Description>
<SendEmailOnError>false</SendEmailOnError>
<SendEmailAlways>false</SendEmailAlways>
<EncryptedImportDataSource>EAAAAEaTBZOn1DKzTWhsvBotHhKq07IpPE0rCqY61S0mS5YOJBkkPbPGk8bXEMZqTJagXdZmV6RxCAd0TXmoz1sicfvW0ZCzAeVI5oJXGmiFxFg1nWaywrDxQLvoTT93SS4CPFzCMUaVlk8L/WK+Bg6OC+CKhGoPMi2Mpc5JMJ9cbjMA</EncryptedImportDataSource>
<ImportDataSourceType>SQL Server</ImportDataSourceType>
<ImportDataSourceSql>use lucity&#xD;
; with cte as (&#xD;
select &#xD;
--top 3&#xD;
wo_number&#xD;
, substring(wk_desc, 6, charindex(' to ', wk_desc) -6) from_status&#xD;
, row_number() over(partition by wo_number order by (wk_mod_dt+wk_mod_tm) desc) rn&#xD;
, wkwotrak.*&#xD;
from wkwotrak&#xD;
join wkorder on wk_wo_id = wo_id&#xD;
where wk_track = 'status change'&#xD;
and (wk_mod_dt + wk_mod_tm) between '7/23/2018 6:00 am' and '7/23/2018 8:23 am'&#xD;
and wk_desc like '%cancelled'&#xD;
--and wo_stat_cd = 951&#xD;
)&#xD;
select wo_number, from_status, rn, wk_track, wk_desc, wk_by, wk_mod_dt+wk_mod_tm wk_stat_dttm&#xD;
from cte&#xD;
where rn = 1&#xD;
&#xD;
&#xD;
&#xD;
</ImportDataSourceSql>
<ImportDelimiter />
<ImportContainsHeader>false</ImportContainsHeader>
<DownloadSourceType />
<DownloadSourceRemove>false</DownloadSourceRemove>
<DownloadSourceProperties />
<PreProcessingProcesses />
<PostProcessingProcesses />
<Destination>WorkWorkOrder</Destination>
<ExportBadData>false</ExportBadData>
<AddData>false</AddData>
<AlwaysShowReview>true</AlwaysShowReview>
<TrackSource>false</TrackSource>
<CodeTypeImport>false</CodeTypeImport>
<CustomImport />
<Template>false</Template>
<SystemTemplate>false</SystemTemplate>
<UpdateData>true</UpdateData>
<PreventDuplicates>false</PreventDuplicates>
<ClearData>false</ClearData>
<PromptForConnection>false</PromptForConnection>
<ExportDirectory />
<Grouping />
<GroupingOrder xsi:nil="true" />
<EmailFromAddress />
<EmailToAddresses />
<EmailCCAddresses />
<SmtpHost />
<EmailSubjectLine />
<ScheduleID xsi:nil="true" />
</ImportSetting>
</ImportSettings>
</Setup>

 

Bob Hyman

Please sign in to leave a comment.