Supplementing TIPQA™ Functionality with External Applications

1. Supplementing TIPQA™ Functionality with External Applications Andy Jobe L-3 Fuzing and Ordnance Systems Business Application Specialist 2011.09.29

2. Agenda • External Application Integration • Custom Calibration Labels • Document Search Widget • External Reporting Integration • Exception Reporting • Forecasting Inspection Needs • RTF Sanitizing Function • Enhanced Doc Notification

3. External Applications • Custom Calibration Labels • Implemented in Visual Basic • Uses API for Brother P-Touch Label Printers • Document Search Widget • Implemented in VB.NET and ASP.NET • Lives on L-3 FOS Intranet Homepage

4. Custom Calibration Labels • Problem Statement: • Standard TIPQA™ Labels did not Meet Size / Configuration Requirements • Solution Summary: • Utilize Custom Application to Print Legacy Labels Using TIPQA™ .csv Output

5. Custom Calibration Labels

6. Custom Calibration Labels • 2-D barcode contains Gage/Tool Number 1.0” x 1.5” .38” x .90”

7. Document Search Widget • TIPQA DocSearch • L-3 FOS Intranet Page • Searches: • DMSN • Identifier • Title • Filename • Owner/Assigned To

8. Document Search Widget • Single Search Box • Automatic Wild Carding by Default • Equivalent to %string% in TIPQA™

9. Document Search Widget • Results Page:

10. Document Search Widget • Results Page: Click opens Document

11. Document Search Widget • Hyperlink Fetching Feature: • http://docsearch/Fetch.aspx?DocDMSN=301638 • Automatically Opens Released Version:

12. Questions?

13. External Reporting • Sometimes you need external reports… • For “unique” internal business processes • To combine TIPQA™ data with ERP system data • Use what you have • Explore connecting existing solution to TIPQA™ • Reporting software often tags along on an ERP project • SSMS and SSRS are free if you own SQL server • Excel can connect to a database using VBA

14. External Reporting • Exception Reporting • Implemented using Event Studio (IBM Cognos) • Forecasting Gage Requirements • Implemented using Report Studio (IBM Cognos) • Sanitizing RTF Fields • Implemented using SQL Server Function • Enhanced Document Approval Notification • Implemented using Microsoft Excel

15. Exception Reporting • Problem statement (from users): • Parts were being received and not inspected • ”Why isn’t TIPQA™ creating inspections for these parts?!?!”

16. Exception Reporting • Problem statement (actual root cause): • Buyers can clear the “Inspection Required” flag when entering POs in our ERP system • TIPQA™ skips inspections when that flag is off • Solution summary: • Scheduled Event to check POs against Part Master in ERP • Notifies Quality Assurance when there is a mismatch

17. Exception Reporting • Example:

18. Forecasting Gage Needs • Problem statement: • Parts were stuck waiting inspection b/c gages weren’t in Receiving Inspection when needed • Solution summary: • Report matches scheduled receipts from ERP with gage usage from last TIPQA™ RI record • Shows where gages are located and which ones need calibration

19. RTF Sanitizing Function • Problem statement: • External Reports unable to render RTF fields • Solution summary: • Wrote SQL function to strip RTF tags • Can be called from inside reporting tool • Displays RTF fields as plain text

20. RTF Sanitizing Function • Raw database contents: • What it should look like: {\rtf1\fbidis\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\froman\fprq2\fcharset0 Times New Roman;}{\f1\fnil\fcharset0 Times New Roman;}} \viewkind4\uc1\pard\ltrpar\ri162\f0\fs20 Characteristic Description: HEIGHT 4.77 +/- 0.13 ( 17 PLS ) \par Zone: D3, SHEET2 \par Gage: IND & STD \par U/M: MM \par Spec: N/A \par \pard\ltrpar\f1 \par \par }

21. RTF Sanitizing Function SOURCE CODE: --FUNCTION DEVELOPED BY TIM VIARS AND ANDY JOBE, L-3 FUZING AND ORDNANCE SYSTEMS --CODE CURRENT AS OF 2011.09.19 --DERIVED FROM CODE ORIGINALLY FOUND HERE: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90034 --FUNCTION IS KNOWN TO PROVIDE UNPREDICTIBLE RESULTS WHEN USED WITH VERY COMPLICATED FORMATTING --CODE PROVIDED AS-IS WITH NO WARRANTY IMPLIED, USE AT YOUR OWN RISK. SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[fnParseRTF] ( @rtf VARCHAR(8000) ) RETURNS VARCHAR(8000) AS BEGIN if @rtf is null or @rtf = '' return '' SELECT @rtf = REPLACE(@rtf, '\{', '['), @rtf = REPLACE(@rtf, '\}', ']'), @rtf = REPLACE(@rtf, '\\', '/'), @rtf = REPLACE(@rtf, '\ldblquote ', '"'), @rtf = REPLACE(@rtf, '\rdblquote ', '"') --@rtf = REPLACE(@rtf, '}[a-z0-9]', '} ') DECLARE @Stage TABLE ( Chr CHAR(1), Pos INT ) INSERT @Stage ( Chr, Pos ) SELECT SUBSTRING(@rtf, Number, 1), Number FROM master..spt_values WHERE Type = 'p' AND SUBSTRING(@rtf, Number, 1) IN ('{', '}') DECLARE @Pos1 INT, @Pos2 INT, @TestRtf INT Set @TestRtf = 0 SELECT @Pos1 = MIN(Pos), @Pos2 = MAX(Pos) FROM @Stage DELETE FROM @Stage WHERE Pos IN (@Pos1, @Pos2) --Loop through all of text to pair up brackets and remove RTF format codes WHILE 1 = 1 BEGIN SELECT TOP 1 @Pos1 = s1.Pos, @Pos2 = s2.Pos FROM @Stage AS s1 INNER JOIN @Stage AS s2 ON s2.Pos > s1.Pos WHERE s1.Chr = '{' AND s2.Chr = '}' ORDER BY s2.Pos - s1.Pos IF @@ROWCOUNT = 0 BEGIN IF @TestRtf = 0 return @rtf BREAK END Set @TestRtf = 1 DELETE FROM @Stage WHERE Pos IN (@Pos1, @Pos2) IF SUBSTRING(@rtf,@Pos2+1,1) NOT IN ('{', '}', '\') BEGIN SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ' ') UPDATE @Stage SET Pos = Pos - @Pos2 + @Pos1 WHERE Pos > @Pos2 END ELSE BEGIN SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '') UPDATE @Stage SET Pos = Pos - @Pos2 + @Pos1 - 1 WHERE Pos > @Pos2 END END --Remove the first piece after brackets which will usually be some sort of RTF code SELECT @rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '') --Strip out invalid newline characters in the RTF code SELECT @rtf = REPLACE(@rtf, CHAR(0), ''), @rtf = REPLACE(@rtf, CHAR(10), ''), @rtf = REPLACE(@rtf, CHAR(13), '') --Remove any ending brackets that were improperly in the RTF code SELECT @rtf = REPLACE(@rtf, '}', '') --Replace \par the RTF endline with newline character SELECT --@rtf = REPLACE(@rtf, '\pard', ''), @rtf = REPLACE(@rtf, '\par ', CHAR(10)), --@rtf = REPLACE(@rtf, '\par[^d]', CHAR(10)), @rtf = REPLACE(@rtf, '\tab', CHAR(11)) --Remove everything with the backslash proceeding it SET @Pos1 = PATINDEX('%\%', @rtf) WHILE @Pos1 > 0 SELECT @Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1), @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''), @Pos1 = PATINDEX('%\%', @rtf) --Remove all Trailing endlines characters SET @Pos1 = LEN(@rtf) WHILE ASCII(SUBSTRING(@rtf,@Pos1,1)) <= 32 SET @Pos1 = @Pos1 - 1 SELECT @rtf = LEFT(@rtf, @Pos1) RETURN @rtf END • Function allows simplified reporting • Example: inspection criteria display • Same RTF field as before: Characteristic Description: HEIGHT 4.77 +/- 0.13 ( 17 PLS ) Zone: D3, SHEET2 Gage: IND & STD U/M: MM Spec: N/A

22. Enhanced Doc Notification • Problem Statement: • Aging of documents waiting approval • No way to find documents if not Assigned To • Solution Summary: • Excel workbook gets Approval information • Sends email notifications to all named approvers even if not yet Assigned To • Emails have attached list of items requiring approval

23. Enhanced Doc Notification • Example message:

24. Enhanced Doc Notification • Example of list attached to an email:

25. Questions?

26. Supplemental Information (Right click and select “Open Hyperlink” on links) • Brother P-Touch label printers: • http://www.brother-usa.com/Ptouch/ • The PT-2430PC (product link) is a very cheap solution, but not very well featured • The PT-9700PC (product link) my preferred solution because it supports half-cutting (having a cut in the label material but not the backing that aids in peeling them) Trust me, if your techs use these as much as we do, IT IS WORTH IT.

27. Supplemental Information (Right click and select “Open Hyperlink” on links) • Brother P-Touch API: • Allows custom applications to print directly onto the label stock with no user interaction • Uses custom pre-made templates that you design and puts the correct information on them on-demand • Link: Brother Developer Center

28. Supplemental Information (Right click and select “Open Hyperlink” on links) • Brother P-Touch Tape: • Our designs use 1” and 3/8” wide TZS tape • I would STRONGLY RECOMMEND using the high-strength adhesive tapes. We have experimented with regular tapes, and they are not durable enough. • Find tapes here (product link)