Leveraging Web. FOCUS MAINTAIN to Maximize Data Retrieval and Manipulation John Griffin Pratt & Whitney Rocketdyne, Inc. I. T. Business Systems Information Builders User Conference – Summit 2008 Pratt & Whitney Rocketdyne
Architecture Client (Browser) Web Server (IIS) Java App Server Web. FOCUS Reporting Server (Tomcat) Flat files Pratt & Whitney Rocketdyne Page 2
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 Pratt & Whitney Rocketdyne Page 3
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! Pratt & Whitney Rocketdyne Page 4
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) Pratt & Whitney Rocketdyne Page 5
The Solution • Web. FOCUS MAINTAIN • No COTS software product available out of the box • Pro. Pricer tries but has no detail material cost module • Pro. Pricer used at PWR for pricing exercises AFTER the material costs are established. Pratt & Whitney Rocketdyne Page 6
Launch Page We choose Pratt & Whitney Rocketdyne Page 7
Choose a Function Next choice Pratt & Whitney Rocketdyne Page 8
Upload a New BOM The retrieved file must be created according to a template for the ETL process • 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 Pratt & Whitney Rocketdyne Page 9
Tables Created MEP TOP_ASSY MEP_DESC 1: N CREATE_ID CREATE_DATE CBOM PART_SEG PARTNO PACKAGEID SEQUENCE PACKAGEID TOP_DESC 1: N PACKAGEID PART_NO PARTNO NOUN PACKAGEID NAME PO_NUM MB ITEM_NO DATE_OF_PO RSC IND_LVL N: 1 SRC_DATE QTY_PER EXT_QTY USER_ID EDIT_DATE PACKAGEID Records user info at logon Pratt & Whitney Rocketdyne Page 10 PROP_QTY UOM USER_SEG SRC_QTY LEAD_TIME PART_NUMBER SRC_CODE WBS PO_UN CLIN NR_LOT REC_LOT …
Choose a Function Next Pratt & Whitney Rocketdyne Page 11
Choose an Existing File Pratt & Whitney Rocketdyne Page 12
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. Pratt & Whitney Rocketdyne Page 13
Estimate the Records • First task is to sweep the PO sources to retrieve purchased history. 1 st choice is run when BOM is first loaded or sweep all records and it will overwrite any edits. 2 nd choice takes only Quotes and looks for any PO’s that might have been placed since the first sweep. Editing is not affected. 3 rd choice only looks for new PO’s and leaves all editing as is. Pratt & Whitney Rocketdyne Page 14
View Records • A left-click on the row number opens an edit screen. Pratt & Whitney Rocketdyne Page 15 (Prices and quantities edited due to Export Compliance requirements)
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) Pratt & Whitney Rocketdyne Page 16
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 Pratt & Whitney Rocketdyne Page 17 (Prices and quantities blanked out due to Export Compliance requirements)
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 Pratt & Whitney Rocketdyne Page 18
View Records View IBOM button brings up a table showing the indentured bill of material. Pratt & Whitney Rocketdyne Page 19 (Prices and quantities blanked out due to Export Compliance requirements)
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 Pratt & Whitney Rocketdyne Page 20 (Prices and quantities blanked out due to Export Compliance requirements)
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 Pratt & Whitney Rocketdyne Page 21 (Prices and quantities blanked out due to Export Compliance requirements)
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. Pratt & Whitney Rocketdyne Page 22
Choose a Function Next Pratt & Whitney Rocketdyne Page 23
Reports Menu • BOM file name is dynamically populated from the key of the MEP table • Reports hard coded. • Format choices are HTML, XL 2 K, and PDF or any supported format. • Each choice populates a variable in the various focexecs. Pratt & Whitney Rocketdyne Page 24
Consolidated Report (Prices and Pratt & Whitney Rocketdyne Page 25 quantities blanked out due to Export Compliance requirements)
Report Functionality • All Web. FOCUS 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 Rocketdyne Page 26
BI Dashboard Pratt & Whitney Rocketdyne Page 27
Summary • Web. FOCUS 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 Rocketdyne Page 28
Q&A • Questions? • Stick around for the code…. Pratt & Whitney Rocketdyne Page 29
Addendum Pratt & Whitney Rocketdyne Page 30
Launch 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 Rocketdyne Page 31
Launch MAINTAIN “Load an Existing BOM” calls an external procedure (focexec) that launches MAINTAIN -* File URLRe. Direct. fex -HTMLFORM BEGIN <HTML> <TITLE>Maintain Installation Verification Procedure – BOM 2 </TITLE> <FORM name="startform" action="/ibi_apps/WFServlet" > <INPUT TYPE="HIDDEN" value="on" name="IBIS_connect"> <INPUT TYPE="HIDDEN" value="MNTCON EX BOM 2" name="IBIF_cmd"> <INPUT TYPE="HIDDEN" value="Ames" name="IBIAPP_app"> </FORM> <SCRIPT> startform. submit(); </SCRIPT> </HTML> -HTMLFORM END Pratt & Whitney Rocketdyne Page 32
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; Double-click the file to load Pratt & Whitney Rocketdyne Page 33 End. Case
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 Get. CBOM -* Capture the Package. ID 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 Package. ID into the View. CBOMfrm -*********************************** COMPUTE PACKID = MEPstk(MEPstk. Foc. Index). 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 ibomtitl 1 ="View CBOM for File: " | PACKID ; WINFORM SHOW_INACTIVE View. CBOMfrm; WINFORM SET View. CBOMfrm. Text 1. TEXT TO ibomtitl 1 ; Winform Show View. CBOMfrm; Pratt & Whitney Rocketdyne Page 34 End. Case
Oracle Data 1: N Pratt & Whitney Rocketdyne Page 35 1: N
Estimate the BOM Case ESTIMATE -* Create ESTstk that contains all PO's for a BOM and copy from -* ESTstk 4 only the latest PO data into BOMstk. Compute Where. Cl = 'WHERE (SRC_CODE ' | Where. ID(Where. ID. Foc. Index). WHEq. Ne | ' ' | Where. ID(Where. ID. Foc. Index). WHClause. Out; compute lrow/i 3 = Where. Id. focindex; If Where. Id. Foc. Index Eq 1 Then Begin perform stackclearfields; End. Begin Stack Clear ESTstk; Stack Clear ESTstk 4; Exec ESTIMATE AT RPTSRV DROP FROM PACKID Where. Cl INTO ESTstk 4; COMPUTE cnt 4=2; Stack Sort ESTstk 4 BY PART_NO BY HIGHEST PO_DATE; FOR 1 Copy from ESTstk 4(1) into ESTstk(1); REPEAT ESTstk 4. Foc. Count-1 cnt 1=1; IF ESTstk 4(cnt 1+1). PART_NO NE ESTstk 4(cnt 1). PART_NO THEN Begin For 1 Copy from ESTstk 4(cnt 1+1) into ESTstk(cnt 4); COMPUTE cnt 4=cnt 4+1; End. Begin ENDREPEAT cnt 1=cnt 1+1; Pratt & Whitney Rocketdyne Page 36 REPEAT BOMstk. FOCCOUNT Row=1; REPEAT ESTstk. FOCCOUNT Row 1=1; IF ESTstk(Row 1). PART_NO EQ BOMstk(Row). PART_NO THEN BEGIN COMPUTE BOMstk(Row). PO_NUM = ESTstk(Row 1). PO_NUM; BOMstk(Row). ITEM_NO = ESTstk(Row 1). ITEM_NO; BOMstk(Row). SRC_DATE = ESTstk(Row 1). SRCDTE; BOMstk(Row). DATE_OF_PO = ESTstk(Row 1). PO_DATE; BOMstk(Row). PO_UN = ESTstk(Row 1). NETUP; BOMstk(Row). NR_LOT = ESTstk(Row 1). NRL; BOMstk(Row). REC_LOT = ESTstk(Row 1). RECL; BOMstk(Row). NUM_SUPP = ESTstk(Row 1). SUPPKEY; BOMstk(Row). SUPPLIER = ESTstk(Row 1). SUPP_NAME; BOMstk(Row). FOUND = ESTstk(Row 1). FOUND; BOMstk(Row). SRC_QTY = ESTstk(Row 1). QTY_ORD; BOMstk(Row). SRC_CODE = ESTstk(Row 1). SRC_CD; BOMstk(Row). LDGR_ACCT = ESTstk(Row 1). LEDGER; BOMstk(Row). GO = ESTstk(Row 1). GO; BOMstk(Row). SUB_ACCT = ESTstk(Row 1). SA; BOMstk(Row). ESC_FACTOR = ESTstk(Row 1). ESC_FACTOR; GOTO EXITREPEAT ; ENDBEGIN ENDREPEAT Row 1=Row 1+1; ENDREPEAT ROW=ROW+1; End. Case
Estimate Focexec • -* File ESTIMATE. fex • • • SET ASNAMES = ON SET DEFCENT = 19 SET YRTHRESH = 40 • • FILEDEF ESC DISK I: IBIAPPSAMESESC. DAT -RUN • • JOIN CLEAR * JOIN WF_CBOM. PART_NO IN WF_CBOM TO ALL PH_POITEM. PART_NO IN PH_POITEM AS J 0 END • • • JOIN PH_POITEM. PO_NUM AND PH_POITEM_NO IN WF_CBOM TO ALL PH_POSCH. PO_NUM AND PH_POSCH. ITEM_NO IN PH_POSCH AS J 1 END • • • JOIN LEFT_OUTER PH_POITEM. PO_NUM AND PH_POITEM_NO IN WF_CBOM TO ALL PH_POLOT. PO_NUM AND PH_POLOT. ITEM_NO IN PH_POLOT AS J 2 END • • • JOIN PH_POITEM. PO_NUM IN WF_CBOM TO PH_POBASIC. PO_NUM IN PH_POBASIC AS J 3 END • • JOIN PH_POBASIC. NUM_SUPP IN WF_CBOM TO SUPP_KEY IN PO_SUPPLIER AS J 4 END -RUN Pratt & Whitney Rocketdyne Page 37
Estimate Focexec (cont’d) • • • • • TABLE FILE WF_CBOM PRINT WF_CBOM. PART_NO SRC_CODE PH_POITEM. PO_NUM PH_POITEM_NO PH_POITEM. LEDGER PH_POITEM. GO PH_POITEM. SA PH_POITEM. UM PH_POITEM. PO_UN_PRICE PH_POBASIC. NUM_SUPP_NAME PH_POBASIC. PO_DATE PH_POLOT. LOT_SEQ PH_POLOT. LOT_PRICE PH_POSCH. PO_SCH_QTY PH_POITEM. PO_QTY_ORD • • WHERE WF_CBOM. PACKAGEID EQ '&1'; &2 Compute Where. Cl = 'WHERE (SRC_CODE ' | Where. ID(Where. ID. Foc. Index). WHEq. Ne | ' ' | Where. ID(Where. ID. Foc. Index). WHClause. Out; compute lrow/i 3 = Where. Id. focindex; • • ON TABLE HOLD AS MX 1 FORMAT BINARY END Pratt & Whitney Rocketdyne Page 38 Where. ID(1). WHClause/A 80 = 'Estimate ALL records (Auto. Price)'; Where. ID(2). WHClause = "Sweep Quotes for new PO's"; Where. ID(3). WHClause = "Sweep PH's for new PO's"; Where. ID(1). WHClause. Out/A 50 = "'M')"; Where. ID(2). WHClause. Out = "'SF') OR ('SR' OR 'SC') AND (WF_CBOM. CD_2 NE 1)"; Where. ID(3). WHClause. Out = "'PH') AND (WF_CBOM. CD_2 NE 1)"; Where. ID(1). WHEq. Ne/A 2 = 'NE'; Where. ID(2). WHEq. Ne = 'EQ'; Where. ID(3). WHEq. Ne = 'EQ';
Estimate Focexec (cont’d) • • • • • • • TABLE FILE MX 1 SUM LOT_PRICE AS LTP BY PO_NUM BY ITEM_NO PRINT PART_NO SRC_CODE LEDGER GO SA PO_UN_PRICE NUM_SUPP_NAME PO_DATE LOT_SEQ LOT_PRICE PO_SCH_QTY PO_QTY_ORD BY PO_NUM BY ITEM_NO WHERE PO_UN_PRICE GT 0 • • ON TABLE HOLD AS MX 2 FORMAT BINARY END Pratt & Whitney Rocketdyne Page 39
Estimate Focexec (cont’d) • • • • • DEFINE FILE MX 2 SRC_CD/A 5 = 'PH'; NETUP/P 12. 4 =((PO_UN_PRICE*PO_QTY_ORD)(LTP))/(PO_QTY_ORD); NRL/P 12. 4 = IF LOT_SEQ EQ 'N' THEN LOT_PRICE ELSE 0; RECL/P 12. 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; ACI 6 YMD/I 6 YMD = ACYMD; DIFF 1/I 6 =(DTEYMD - ACYMD)/2. ; MID_POINT/I 6 YMD = AYMD(ACI 6 YMD, DIFF 1, MID_POINT); MID/YMD = MID_POINT; DATEQ/Q = MID; DATEY/Y = MID; DQ/A 1 = EDIT(DATEQ); DY/A 2 = EDIT(DATEY); DT/A 3 = DQ|DY; SRCDTE/I 3 = EDIT(DT); FOUND/A 1 = '*'; ESC_FACTOR/D 5. 3 = DECODE SRCDTE (ESC ELSE 0); QTY_ORD/P 12 = PO_QTY_ORD; END Pratt & Whitney Rocketdyne Page 40 • • • • • • • TABLE FILE MX 2 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)
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 Pratt & Whitney Rocketdyne Page 41
Edit a Record • Left-click on the row number launches another Form: Case On. HTMLTable 1_Click. Link If View. CBOMfrm. HTMLTable 1. Click. Column Eq 1 Then; Begin Perform Drill 1(); End. Begin Case Drill 1 -* Drill down to the Edit. Recordfrm when the user left-clicks a -* Part Number from the View. CBOMfrm screen -*********************************** Compute row 3/i 3=View. CBOMfrm. htmltable 1. clickrow; Infer wf_CBOM. PART_NO into Estk Copy from BOMstk(row 3) into Estk; Repeat Stk. SC. Foc. Count cnt 3=1; If Estk. src_code Eq Stk. SC(cnt 3). Src. Code Then Begin Compute Stk. SC. Foc. Index = cnt 3; Goto Exit. Repeat End. Begin End. Repeat cnt 3=cnt 3+1; Winform Show_Inactive Edit. Recordfrm Winform Set Edit. Recordfrm. Edit. Box 20. Focus To Here; Winform Show Edit. Recordfrm End. Case Pratt & Whitney Rocketdyne Page 42
Save Edits Case Save. BOM -* 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 Pratt & Whitney Rocketdyne Page 43
Thank You • Any Questions? Pratt & Whitney Rocketdyne Page 44
Скачать презентацию Leveraging Web FOCUS MAINTAIN to Maximize Data Retrieval
09c94805afad4721772ef5c80753f81f.ppt