leveraging webfocus maintain to maximize data retrieval and manipulation
DESCRIPTION
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). - PowerPoint PPT PresentationTRANSCRIPT
Pratt & Whitney Rocketdyne
Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation
Leveraging WebFOCUS MAINTAIN to Maximize Data Retrieval and Manipulation
John GriffinPratt & Whitney Rocketdyne, Inc.I.T. Business Systems
Information Builders User Conference – Summit 2008
Pratt & Whitney RocketdynePage 2
ArchitectureArchitecture
Web Server (IIS) Java App Server
(Tomcat)
Client (Browser)WebFOCUS Reporting Server
Flat files
Pratt & Whitney RocketdynePage 3
ArchitectureArchitecture
• 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
Pratt & Whitney RocketdynePage 4
ArchitectureArchitecture
• 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!
Pratt & Whitney RocketdynePage 5
The ProblemThe 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)
Pratt & Whitney RocketdynePage 6
The SolutionThe 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.
Pratt & Whitney RocketdynePage 7
Launch PageLaunch Page
We choose
Pratt & Whitney RocketdynePage 8
Choose a FunctionChoose a Function
Next choice
Pratt & Whitney RocketdynePage 9
Upload a New BOMUpload 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
Pratt & Whitney RocketdynePage 10
Tables CreatedTables Created
MEP
PACKAGEID
MEP_DESC
CREATE_ID
CREATE_DATE
TOP_ASSY
PARTNO
PACKAGEID
TOP_DESC
PART_SEG
SEQUENCE
PARTNO
PACKAGEID
NAME
MB
RSC
IND_LVL
QTY_PER
EXT_QTY
UOM
PART_NUMBER
WBS
CLIN
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
…
USER_SEG
USER_ID
EDIT_DATE
PACKAGEID
N:1
1:N1:N
Records user info at logon
Pratt & Whitney RocketdynePage 11
Choose a FunctionChoose a Function
Next
Pratt & Whitney RocketdynePage 12
Choose an Existing FileChoose an Existing File
Pratt & Whitney RocketdynePage 13
View CBOM ScreenView 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.
Pratt & Whitney RocketdynePage 14
Estimate the RecordsEstimate 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.
Pratt & Whitney RocketdynePage 15
View RecordsView Records
• A left-click on the row number opens an edit screen.
(Prices and quantities edited due to Export Compliance requirements)
Pratt & Whitney RocketdynePage 16
Edit a RecordEdit 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)
Pratt & Whitney RocketdynePage 17
View RecordsView 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)
Pratt & Whitney RocketdynePage 18
P.O. HistoryP.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
Pratt & Whitney RocketdynePage 19
View RecordsView Records
View IBOM button brings up a table showing the indentured bill of material.
(Prices and quantities blanked out due to Export Compliance requirements)
Pratt & Whitney RocketdynePage 20
Indentured Bill of MaterialIndentured 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)
Pratt & Whitney RocketdynePage 21
Back to View RecordsBack to View Records
Click on the “Apply Factors” button
PO Data may need to be “massaged” to bring the values up to the current time period or allow for variances.
(Prices and quantities blanked out due to Export Compliance requirements)
Pratt & Whitney RocketdynePage 22
Apply FactorsApply 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.
Pratt & Whitney RocketdynePage 23
Choose a FunctionChoose a Function
Next
Pratt & Whitney RocketdynePage 24
Reports MenuReports 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.
Pratt & Whitney RocketdynePage 25
Consolidated ReportConsolidated Report
(Prices and quantities blanked out due to Export Compliance requirements)
Pratt & Whitney RocketdynePage 26
Report FunctionalityReport 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.
Pratt & Whitney RocketdynePage 27
BI DashboardBI Dashboard
Pratt & Whitney RocketdynePage 28
SummarySummary
• 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.
Pratt & Whitney RocketdynePage 29
Q & AQ & A
•Questions?
•Stick around for the code….
Pratt & Whitney RocketdynePage 30
AddendumAddendum
Pratt & Whitney RocketdynePage 31
Launch PageLaunch Page
All this does is launch a URL that runs another HTML page:
http://app-cpc-20:8080/approot/ames/AMES_Launch.htm
Pratt & Whitney RocketdynePage 32
Launch MAINTAINLaunch MAINTAIN
-* File URLReDirect.fex-HTMLFORM BEGIN<HTML>
<TITLE>Maintain Installation Verification 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
“Load an Existing BOM” calls an external procedure (focexec) that launches MAINTAIN
Pratt & Whitney RocketdynePage 33
Choose a BOMChoose a BOM
Now MAINTAIN is runningFile list is dynamically populated from key field in the Oracle data source
Case TopReposition wf_MEP.PACKAGEID ;Stack clear MEPstk ;
For all next wf_MEP.PACKAGEID into MEPstk;
Winform Show PACKfrm;
EndCase
(Created in Maintain Development Environment)
Double-click the file to load
Pratt & Whitney RocketdynePage 34
Load the CBOM FormLoad 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-*********************************************************************COMPUTEPACKID = 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
Pratt & Whitney RocketdynePage 35
Oracle DataOracle Data
POBASIC Parent Table with PO Number Data
POITEMDetail at Item Level
POSCHScheduled Delivery
Dates and Quantities
PORPTActual Deliveries on
the Dock
POLOTLot Charges
PURCHASE ORDER DATABASE
POINVInvoice Data at the
PO Level
POINVITMInvoice Data at the
Item Level
1:N
1:N1:N
1:N
1:N
1:N
Pratt & Whitney RocketdynePage 36
Estimate the BOMEstimate 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 Beginperform 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; EndBeginENDREPEAT 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
Pratt & Whitney RocketdynePage 37
Estimate FocexecEstimate 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
Pratt & Whitney RocketdynePage 38
Estimate Focexec (cont’d)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
Compute WhereCl = 'WHERE (SRC_CODE ' | WhereID(WhereID.FocIndex).WHEqNe | ' ' | WhereID(WhereID.FocIndex).WHClauseOut;compute lrow/i3 = WhereId.focindex;
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';
Pratt & Whitney RocketdynePage 39
Estimate Focexec (cont’d)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
Pratt & Whitney RocketdynePage 40
Estimate Focexec (cont’d)Estimate Focexec (cont’d)
• 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
(Data sent back to the MAINTAIN)
Pratt & Whitney RocketdynePage 41
PCHOLD OutputPCHOLD 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
Pratt & Whitney RocketdynePage 42
Edit a RecordEdit a Record
• Left-click on the row number launches another Form:Case OnHTMLTable1_ClickLinkIf ViewCBOMfrm.HTMLTable1.ClickColumn Eq 1 Then;BeginPerform 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 EstkCopy 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 EndBeginEndRepeat cnt3=cnt3+1;
Winform Show_Inactive EditRecordfrmWinform Set EditRecordfrm.EditBox20.Focus To Here;Winform Show EditRecordfrm
EndCase
Pratt & Whitney RocketdynePage 43
Case SaveBOM-* Save all edited records from BOMstk and commit to wf_CBOM tableReposition 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
Save EditsSave Edits
Pratt & Whitney RocketdynePage 44
Thank YouThank You
• Any Questions?