1 / 44

Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation

Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation. John Griffin Pratt & Whitney Rocketdyne, Inc. I.T. Business Systems Information Builders User Conference – Summit 2008. Architecture. Web Server (IIS). Client (Browser). Java App Server (Tomcat).

nantai
Download Presentation

Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation John Griffin Pratt & Whitney Rocketdyne, Inc. I.T. Business Systems Information Builders User Conference – Summit 2008

  2. Architecture Web Server (IIS) Client (Browser) Java App Server (Tomcat) WebFOCUS Reporting Server Flat files

  3. Architecture • Windows 2003 Server – Tomcat Application Server • Clients on Windows XP Professional (IE 6) • Data Sources accessed • Oracle (IMS extracts) • MS SQL Server 2000 • MS Access/Excel • Flat Files • Any platform that i-Way can touch

  4. Architecture • SAP Implementation in Progress • Replaces Purchase Order DB & MRP II DB • Big Transition? I don’t think so! • SAP data to be extracted into Oracle (We’re not allowed to report directly off SAP) • Map the new data structure to the old field names and formats (we’re trying to influence that process) • Re-load the synonym with the new source • Check for anomalies with names and format in the code that may have slipped through • You’re good to go!

  5. The Problem • Estimating the costs for new business and follow-on business proposals was always a manual process using spreadsheets and mainframe data screens. • A new automated system was needed to reduce lead-time. • Need to adhere to U.S. Gov’t Federal Acquisition Regulations (FAR) • Need the capability to save data back to Oracle tables for later retrieval and manipulation • Need standard reports and Ad-hoc reporting (Guided Ad-hoc or BI Dashboard)

  6. The Solution • WebFOCUS MAINTAIN • No COTS software product available out of the box • ProPricer tries but has no detail material cost module • ProPricer used at PWR for pricing exercises AFTER the material costs are established.

  7. Launch Page Wechoose

  8. Choose a Function Next choice

  9. Upload a New BOM • Drop a file name into the box and click “Import CSV File”. VB code sends data in CSV format to ETL tool that creates Oracle tables for use by MAINTAIN The retrieved file must be created according to a template for the ETL process

  10. Tables Created MEP PACKAGEID MEP_DESC CREATE_ID CREATE_DATE CBOM PACKAGEID PART_NO NOUN PO_NUM ITEM_NO DATE_OF_PO SRC_DATE SRC_QTY PROP_QTY LEAD_TIME SRC_CODE PO_UN NR_LOT REC_LOT … PART_SEG SEQUENCE PARTNO PACKAGEID NAME MB RSC IND_LVL QTY_PER EXT_QTY UOM PART_NUMBER WBS CLIN TOP_ASSY PARTNO PACKAGEID TOP_DESC 1:N 1:N N:1 USER_SEG USER_ID EDIT_DATE PACKAGEID Records user info at logon

  11. Choose a Function Next

  12. Choose an Existing File

  13. View CBOM Screen Contains a consolidated bill of material (CBOM) Notice: No pricing yet From this screen, you can edit, estimate, escalate, curve, and view the full IBOM.

  14. Estimate the Records • First task is to sweep the PO sources to retrieve purchased history. 1st choice is run when BOM is first loaded or sweep all records and it will overwrite any edits. 2nd choice takes only Quotes and looks for any PO’s that might have been placed since the first sweep. Editing is not affected. 3rd choice only looks for new PO’s and leaves all editing as is.

  15. A left-click on the row number opens an edit screen. View Records (Prices and quantities edited due to Export Compliance requirements)

  16. Edit a Record All Fields except Part Number are editable. This is a consolidated bill of material built from the Indentured BOM. Changing a Part Number here will render the Indentured BOM unusable. (Prices and quantities edited due to Export Compliance requirements)

  17. View Records The system chooses the latest PO data Estimators may not want that. Clicking on a Part Number drills-down to PO data in a stack (Prices and quantities blanked out due to Export Compliance requirements)

  18. P.O. History The Estimator may want one of the other P.O.’s retrieved. Clicking the PO NUM link will replace selected data on the previous screen with the new P.O. data

  19. View Records View IBOM button brings up a table showing the indentured bill of material. (Prices and quantities blanked out due to Export Compliance requirements)

  20. Indentured Bill of Material • The IBOM is the full bill of material that is indentured to show the sequence of manufacturing. • Each lower indentured part goes into the part above it based on its indenture level (Prices and quantities blanked out due to Export Compliance requirements)

  21. Back to View Records PO Data may need to be “massaged” to bring the values up to the current time period or allow for variances. Click on the “Apply Factors” button (Prices and quantities blanked out due to Export Compliance requirements)

  22. Apply Factors • Curve is a factor for quantity variance. E.g.: If you bought 10 of an item and now you only want 1, it will cost more, and vice-versa. • Escalation applies an inflation factor against the purchase price when bought and the need date. It will also de-escalate. • ESC values are from the Producer’s Price Index, updated quarterly.

  23. Choose a Function Next

  24. Reports Menu • BOM file name is dynamically populated from the key of the MEP table • Reports hard coded. • Format choices are HTML, XL2K, and PDF or any supported format. • Each choice populates a variable in the various focexecs.

  25. Consolidated Report (Prices and quantities blanked out due to Export Compliance requirements)

  26. Report Functionality • All WebFOCUS supported formats can be included in the format drop-down on the Reports Menu. • Unlimited number of “canned” reports can be included in the list for report choices. • Ad-Hoc reporting can be accomplished using the Business Intelligence Dashboard (MRE required) or creating a Guided Ad-hoc with drop down boxes to populate the variables.

  27. BI Dashboard

  28. Summary • WebFOCUS MAINTAIN has allowed Estimators to concentrate on estimating and not on data retrieval • Web-based interface has reduced application proliferation of thick software clients to the ultimate thin client, a browser. • Lead-time for completing an estimate has been reduced from several weeks to just a few days.

  29. Q & A • Questions? • Stick around for the code….

  30. Addendum How'd They Do That?

  31. Launch Page All this does is launch a URL that runs another HTML page: http://app-cpc-20:8080/approot/ames/AMES_Launch.htm

  32. Launch MAINTAIN “Load an Existing BOM” calls an external procedure (focexec) that launches MAINTAIN -* File URLReDirect.fex -HTMLFORM BEGIN <HTML> <TITLE>Maintain InstallationVerification Procedure – BOM2 </TITLE> <FORM name="startform" action="/ibi_apps/WFServlet" > <INPUT TYPE="HIDDEN" value="on" name="IBIS_connect"> <INPUT TYPE="HIDDEN" value="MNTCON EX BOM2" name="IBIF_cmd"> <INPUT TYPE="HIDDEN" value="Ames" name="IBIAPP_app"> </FORM> <SCRIPT> startform.submit(); </SCRIPT> </HTML> -HTMLFORM END

  33. Choose a BOM Now MAINTAIN is running File list is dynamically populated from key field in the Oracle data source (Created in Maintain Development Environment) Case Top Reposition wf_MEP.PACKAGEID ; Stack clear MEPstk ; For all next wf_MEP.PACKAGEID into MEPstk; Winform Show PACKfrm; EndCase Double-click the file to load

  34. Load the CBOM Form Consolidated Bill of Material consists of one record for each unique part number in the Indentured Bill of Material. Estimators then only have to edit a part number once. Case GetCBOM -* Capture the PackageID the user clicked on in PACKfrm -* to populate the PACKID variable for use throughout the session. -* Then load BOMstk with all records from wf_CBOM table with the -* same PackageID into the ViewCBOMfrm -********************************************************************* COMPUTE PACKID = MEPstk(MEPstk.FocIndex).PACKAGEID; Reposition wf_CBOM.PART_NO; Stack clear BOMstk; For all next wf_CBOM.PART_NO into BOMstk WHERE wf_CBOM.PACKAGEID EQ PACKID AND wf_CBOM.SRC_CODE OMITS 'M'; COMPUTE ibomtitl1 ="View CBOM for File: " | PACKID ; WINFORM SHOW_INACTIVE ViewCBOMfrm; WINFORM SET ViewCBOMfrm.Text1.TEXT TO ibomtitl1 ; Winform Show ViewCBOMfrm; EndCase

  35. Oracle Data 1:N 1:N 1:N 1:N 1:N 1:N

  36. Estimate the BOM Case ESTIMATE -* Create ESTstk that contains all PO's for a BOM and copy from -* ESTstk4 only the latest PO data into BOMstk. Compute WhereCl = 'WHERE (SRC_CODE ' | WhereID(WhereID.FocIndex).WHEqNe | ' ' | WhereID(WhereID.FocIndex).WHClauseOut; compute lrow/i3 = WhereId.focindex; If WhereId.FocIndex Eq 1 Then Begin perform stackclearfields; EndBegin Stack Clear ESTstk; Stack Clear ESTstk4; Exec ESTIMATE AT RPTSRV DROP FROM PACKID WhereCl INTO ESTstk4; COMPUTE cnt4=2; Stack Sort ESTstk4 BY PART_NO BY HIGHEST PO_DATE; FOR 1 Copy from ESTstk4(1) into ESTstk(1); REPEAT ESTstk4.FocCount-1 cnt1=1; IF ESTstk4(cnt1+1).PART_NO NE ESTstk4(cnt1).PART_NO THEN Begin For 1 Copy from ESTstk4(cnt1+1) into ESTstk(cnt4); COMPUTE cnt4=cnt4+1; EndBegin ENDREPEAT cnt1=cnt1+1; REPEAT BOMstk.FOCCOUNT Row=1; REPEAT ESTstk.FOCCOUNT Row1=1; IF ESTstk(Row1).PART_NO EQ BOMstk(Row).PART_NO THEN BEGIN COMPUTE BOMstk(Row).PO_NUM = ESTstk(Row1).PO_NUM; BOMstk(Row).ITEM_NO = ESTstk(Row1).ITEM_NO; BOMstk(Row).SRC_DATE = ESTstk(Row1).SRCDTE; BOMstk(Row).DATE_OF_PO = ESTstk(Row1).PO_DATE; BOMstk(Row).PO_UN = ESTstk(Row1).NETUP; BOMstk(Row).NR_LOT = ESTstk(Row1).NRL; BOMstk(Row).REC_LOT = ESTstk(Row1).RECL; BOMstk(Row).NUM_SUPP = ESTstk(Row1).SUPPKEY; BOMstk(Row).SUPPLIER = ESTstk(Row1).SUPP_NAME; BOMstk(Row).FOUND = ESTstk(Row1).FOUND; BOMstk(Row).SRC_QTY = ESTstk(Row1).QTY_ORD; BOMstk(Row).SRC_CODE = ESTstk(Row1).SRC_CD; BOMstk(Row).LDGR_ACCT = ESTstk(Row1).LEDGER; BOMstk(Row).GO = ESTstk(Row1).GO; BOMstk(Row).SUB_ACCT = ESTstk(Row1).SA; BOMstk(Row).ESC_FACTOR = ESTstk(Row1).ESC_FACTOR; GOTO EXITREPEAT ; ENDBEGIN ENDREPEAT Row1=Row1+1; ENDREPEAT ROW=ROW+1; EndCase

  37. Estimate Focexec • -* File ESTIMATE.fex • SET ASNAMES = ON • SET DEFCENT = 19 • SET YRTHRESH = 40 • FILEDEF ESC DISK I:\IBI\APPS\AMES\ESC.DAT • -RUN • JOIN CLEAR * • JOIN WF_CBOM.PART_NO IN WF_CBOM TO ALL PH_POITEM.PART_NO IN • PH_POITEM AS J0 • END • JOIN PH_POITEM.PO_NUM AND PH_POITEM.ITEM_NO IN WF_CBOM TO ALL • PH_POSCH.PO_NUM AND PH_POSCH.ITEM_NO IN PH_POSCH AS J1 • END • JOIN LEFT_OUTER PH_POITEM.PO_NUM AND PH_POITEM.ITEM_NO IN WF_CBOM TO ALL • PH_POLOT.PO_NUM AND PH_POLOT.ITEM_NO IN PH_POLOT AS J2 • END • JOIN PH_POITEM.PO_NUM IN WF_CBOM TO • PH_POBASIC.PO_NUM IN PH_POBASIC AS J3 • END • JOIN PH_POBASIC.NUM_SUPP IN WF_CBOM TO • SUPP_KEY IN PO_SUPPLIER AS J4 • END • -RUN

  38. Estimate Focexec (cont’d) • TABLE FILE WF_CBOM • PRINT • WF_CBOM.PART_NO • SRC_CODE • PH_POITEM.PO_NUM • PH_POITEM.ITEM_NO • PH_POITEM.LEDGER • PH_POITEM.GO • PH_POITEM.SA • PH_POITEM.UM • PH_POITEM.PO_UN_PRICE • PH_POBASIC.NUM_SUPP • SUPP_NAME • PH_POBASIC.PO_DATE • PH_POLOT.LOT_SEQ • PH_POLOT.LOT_PRICE • PH_POSCH.PO_SCH • PH_POSCH.PO_SCH_QTY • PH_POITEM.PO_QTY_ORD • WHERE WF_CBOM.PACKAGEID EQ '&1'; • &2 • ON TABLE HOLD AS MX1 FORMAT BINARY • END WhereID(1).WHClause/A80 = 'Estimate ALL records (AutoPrice)'; WhereID(2).WHClause = "Sweep Quotes for new PO's"; WhereID(3).WHClause = "Sweep PH's for new PO's"; WhereID(1).WHClauseOut/A50 = "'M')"; WhereID(2).WHClauseOut = "'SF') OR ('SR' OR 'SC') AND (WF_CBOM.CD_2 NE 1)"; WhereID(3).WHClauseOut = "'PH') AND (WF_CBOM.CD_2 NE 1)"; WhereID(1).WHEqNe/A2 = 'NE'; WhereID(2).WHEqNe = 'EQ'; WhereID(3).WHEqNe = 'EQ'; Compute WhereCl = 'WHERE (SRC_CODE ' | WhereID(WhereID.FocIndex).WHEqNe | ' ' | WhereID(WhereID.FocIndex).WHClauseOut; compute lrow/i3 = WhereId.focindex;

  39. Estimate Focexec (cont’d) • TABLE FILE MX1 • SUM LOT_PRICE AS LTP • BY • PO_NUM • BY • ITEM_NO • PRINT • PART_NO • SRC_CODE • LEDGER • GO • SA • PO_UN_PRICE • NUM_SUPP • SUPP_NAME • PO_DATE • LOT_SEQ • LOT_PRICE • PO_SCH • PO_SCH_QTY • PO_QTY_ORD • BY • PO_NUM • BY • ITEM_NO • WHERE PO_UN_PRICE GT 0 • ON TABLE HOLD AS MX2 FORMAT BINARY • END

  40. DEFINE FILE MX2 SRC_CD/A5 = 'PH'; NETUP/P12.4 =((PO_UN_PRICE*PO_QTY_ORD)-(LTP))/(PO_QTY_ORD); NRL/P12.4 = IF LOT_SEQ EQ 'N' THEN LOT_PRICE ELSE 0; RECL/P12.4 = IF LOT_SEQ EQ 'R' THEN LOT_PRICE ELSE 0; PODATE/YYMD = HDATE(PO_DATE,'YYMD'); DTYMD/YYMD = HDATE(PO_SCH,'YYMD'); DTEYMD/YMD = DTYMD; ACYMD/YMD = PODATE; ACI6YMD/I6YMD = ACYMD; DIFF1/I6 =(DTEYMD - ACYMD)/2.; MID_POINT/I6YMD = AYMD(ACI6YMD, DIFF1, MID_POINT); MID/YMD = MID_POINT; DATEQ/Q = MID; DATEY/Y = MID; DQ/A1 = EDIT(DATEQ); DY/A2 = EDIT(DATEY); DT/A3 = DQ|DY; SRCDTE/I3 = EDIT(DT); FOUND/A1 = '*'; ESC_FACTOR/D5.3 = DECODE SRCDTE (ESC ELSE 0); QTY_ORD/P12 = PO_QTY_ORD; END TABLE FILE MX2 PRINT PART_NO PO_DATE PO_NUM ITEM_NO NETUP QTY_ORD LEDGER GO SA NUM_SUPP AS 'SUPPKEY' SUPP_NAME FOUND SRCDTE SRC_CD NRL RECL ESC_FACTOR BY PART_NO NOPRINT BY HIGHEST PO_DATE NOPRINT BY PO_NUM NOPRINT BY ITEM_NO NOPRINT ON TABLE PCHOLD Estimate Focexec (cont’d) (Data sent back to the MAINTAIN)

  41. PCHOLD Output Now the PO data is written to the HTML table from the PCHOLD file created in the ESTIMATE.fex Event Handlers behind the form allow for navigation to other forms or processes, even other MAINTAINs

  42. Edit a Record • Left-click on the row number launches another Form: Case OnHTMLTable1_ClickLink If ViewCBOMfrm.HTMLTable1.ClickColumn Eq 1 Then;Begin Perform Drill1(); EndBegin Case Drill1 -* Drill down to the EditRecordfrm when the user left-clicks a -* Part Number from the ViewCBOMfrm screen -********************************************************************* Compute row3/i3=ViewCBOMfrm.htmltable1.clickrow; Infer wf_CBOM.PART_NO into Estk Copy from BOMstk(row3) into Estk; Repeat StkSC.FocCount cnt3=1; If Estk.src_code Eq StkSC(cnt3).SrcCode Then Begin Compute StkSC.FocIndex = cnt3; Goto ExitRepeat EndBegin EndRepeat cnt3=cnt3+1; Winform Show_Inactive EditRecordfrm Winform Set EditRecordfrm.EditBox20.Focus To Here; Winform Show EditRecordfrm EndCase

  43. Save Edits Case SaveBOM -* Save all edited records from BOMstk and commit to wf_CBOM table Reposition wf_CBOM.PART_NO; For all update wf_CBOM.NOUN wf_CBOM.PO_NUM wf_CBOM.ITEM_NO wf_CBOM.DATE_OF_PO wf_CBOM.SRC_DATE wf_CBOM.SRC_QTY wf_CBOM.PROP_QTY wf_CBOM.LEAD_TIME wf_CBOM.SRC_CODE wf_CBOM.PO_UN wf_CBOM.NR_LOT wf_CBOM.REC_LOT wf_CBOM.ESC_NR_LOT wf_CBOM.ESC_REC_LOT wf_CBOM.ESC_UN wf_CBOM.ESC_CRV_UN wf_CBOM.NUM_SUPP wf_CBOM.FOUND wf_CBOM.LDGR_ACCT wf_CBOM.GO wf_CBOM.SUB_ACCT wf_CBOM.SUPPLIER wf_CBOM.CD_2 wf_CBOM.ESC_FACTOR wf_CBOM.COMMENTS from BOMStk; Commit

  44. Thank You • Any Questions?

More Related