1 / 21

Simple Invoice

Simple Invoice. Project Status. Eric Okin Assistant Deputy Director Policy and Architecture; DFAS - DTB. Mike Lubash XML Team Leader DoD Finance and Accounting Namespace Manager. Agenda. Objective / Scope Partnership with Intuit - marketing

carlynda
Download Presentation

Simple Invoice

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. Simple Invoice Project Status Eric Okin Assistant Deputy Director Policy and Architecture; DFAS - DTB Mike Lubash XML Team Leader DoD Finance and Accounting Namespace Manager

  2. Agenda • Objective / Scope • Partnership with Intuit - marketing • Scenario - For simple invoicing, prompt user for DoD specific information and send to DFAS website • Walkthroughs… • 1. User perspective • 2. Processing Details • 3. Code Review w/ XML • Lessons Learned • Next Steps

  3. Objective – Lower the Bar EDI - 30% XML- 50% ? Web - 20%

  4. Scope Develop prototype to address the needs of those users who have not automated their invoicing through current channels and entice participation by offering easy to use method to submit their invoices via their accounting package. Payback set of users for such a channel, were cited to be those commercial invoices that were deemed straightforward, e.g. without discounts.

  5. Partnership Intuit Electronic Commerce working together to streamline government

  6. WInS Scenario Vendor DFAS Rock Castle Simple Invoice Tool “Web” Server qbXML EDI 3050 X12.810 DEBX Queries QuickBooks for Invoice, prompts vendor for DoD specific information not contained in QuickBooks and ‘posts’ to DFAS ASP or JSP to accept ‘posts’ and writes to WInS DB Payment Systems

  7. QuickBooks Invoice

  8. Complete DoD Specifics & ‘Post’ to Server DAA201-98-C001 Pigs; Live

  9. WInS Query Posting Server Side Processing Details Simple Invoice Tool “Web” Server qbXML

  10. Processing Details - Query 1 User executes program and is prompted for the Invoice Number to be processed 2 qbXML Query Program connects with QuickBooks 3 Program formulates Query and sends parameters to QuickBooks using qbXML Security prompt occurs 4 Response is returned in qbXML qbXML Response 6 Check for errors before attempting to view invoice ? 5 Store qbXML in a parser Parser qbXML

  11. Processing Details - Posting loaded with qbXML Parser qbXML Translate 8 XML Company Parser loaded with Simple Invoice Company Specific Information 9 Parser 10 Persist to file 7 Load stylesheet to convert qbXML to Invoice Simple XSL Convert XML Neutral Invoke web browser to read Simple Invoice XML 11 ? To DFAS web server 12 XSL Present XML declares link to presentation stylesheet for user interface, checking logic, and URL which to post 14 Script checks User’s input 13 User completes DoD specific fields & ‘posts’ to DFAS web server for processing

  12. Acknowledgement • Status • Transaction History • Customer Service WInS Processing Details – Server Side 15 Process as if any other web page submitted to the server, using ASP or JSP to write to database, validating contract, CAGE, etc. “Web” Server DEBX EDI 3050 X12.810 Payment Systems 16 Feedback and assistance is provided by Web interface; future options are for XML acknowledgements to be automatically processed into QuickBooks

  13. Query Posting Code Review Simple Invoice Tool “Web” Server qbXML

  14. Code Review - Query Private Sub Submit_Click() If (qbInvoiceNumber = "") Then MsgBox "Please select an Invoice Number" Else Dim qbXMLRead As Boolean ' Setup error handling & status flag qbXMLRead = False On Error GoTo ErrHandler 'Step 1: Start session with QuickBooks Dim sessionManager As QBFC1Lib.QBSessionManager Set sessionManager = New QBFC1Lib.QBSessionManager sessionManager.OpenConnection cAppID, cAppName sessionManager.BeginSession "", QBFC1Lib.omDontCare 'Step 2: Create Message Set request Dim requestMsgSet As QBFC1Lib.IMsgSetRequest Set requestMsgSet = sessionManager.CreateMsgSetRequest(cQBXMLMajorVersion, cQBXMLMinorVersion) requestMsgSet.Attributes.OnError = roeContinue 'Step 3: Create the query object needed to perform InvoiceQueryRq Dim invQuery As QBFC1Lib.IInvoiceQuery Set invQuery = requestMsgSet.AppendInvoiceQueryRq Dim invFilter As QBFC1Lib.IInvoiceFilter Set invFilter = invQuery.ORInvoiceQuery.InvoiceFilter 'Step 4: Set the invoice number as query parameter as prompted from the user on the form invFilter.ORRefNumberFilter.RefNumberRangeFilter.FromRefNumber.SetValue (qbInvoiceNumber) invFilter.ORRefNumberFilter.RefNumberRangeFilter.ToRefNumber.SetValue (qbInvoiceNumber) invQuery.IncludeLineItems.SetValue (1) ' we want invoice line item to be in the response

  15. Code Review - Query Con’t 'Step 5: Do the request Dim responseMsgSet As QBFC1Lib.IMsgSetResponse Set responseMsgSet = sessionManager.DoRequests(requestMsgSet) 'Step 6: Load up a parser with qbXML response Set oDocFormatA = CreateObject("MSXML2.DOMDocument") ' ODocFormatA is global oDocFormatA.async = False oDocFormatA.loadXML (responseMsgSet.ToXMLString) 'oDocFormatA.save ("D:\qbXMLInvoiceResponse.xml") 'Step 7: Interpret the response Dim rsList As QBFC1Lib.IResponseList Set rsList = responseMsgSet.ResponseList 'Retrieve the one response corresponding to our single request Dim response As QBFC1Lib.IResponse Set response = rsList.GetAt(0) 'Step 8: Check for errors in the response qbXMLRead = True ' we got this far If (response.StatusCode <> 0) Then If (response.StatusCode = 1) Then 'No record found MsgBox "No invoice is found", vbInformation, "Message from QuickBooks" Else Dim msg msg = "Error occured. Status Code = " & CStr(response.StatusCode) & _ ", Status Message = " & response.StatusMessage & _ ", Status Severity = " & response.StatusSeverity MsgBox msg, vbExclamation, "Message from QuickBooks" End If Else 'Step 9: Continue to next step SubmitInvoice.Show Unload Me End If ErrHandler: 'if our query fails, display message box to user … End If End Sub

  16. Code Review - Posting Private Sub Form_Load() Screen.MousePointer = vbHourglass Dim oDocFormatB As New MSXML2.DOMDocument 'Object that holds our - Simple Invoice Dim oToSimpleXSLT As New MSXML2.DOMDocument 'Object that holds the conversion XSLT stylesheet oDocFormatB.async = False oToSimpleXSLT.async = False oToSimpleXSLT.Load "qbXMLInvoiceResponse2SimpleInvoice.xsl" oDocFormatB.loadXML (oDocFormatA.transformNode(oToSimpleXSLT)) 'convert oDocFormatB.save ("D:\SimpleInvoice.xml") 'persist it ' read in browser, xml links to spreadsheet for user display WebBrowser1.Navigate "D:\SimpleInvoice.xml" Screen.MousePointer = vbDefault End Sub

  17. qbXMLInvoiceResponse2SimpleInvoice.XSL <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="xml" version="4.0" omit-xml-declaration="no" indent="yes"/> <xsl:template match="/"> <xsl:processing-instruction name="xml-stylesheet">type="text/xsl" href="SimpleInvoiceView.xsl"</xsl:processing-instruction> <InvoiceSimple> <xsl:variable name="MyInfo" select="document('file:C:\MyInfo.xml')"/> <xsl:for-each select="$MyInfo"> <VendorInfo> <CompanyName><xsl:value-of select="/VendorInfo/CompanyName"/></CompanyName> <CAGE><xsl:value-of select="/VendorInfo/CAGE"/></CAGE> <DUNS><xsl:value-of select="/VendorInfo/DUNS"/></DUNS> <AccountingSystem><xsl:value-of select="/VendorInfo/AccountingSystem"/></AccountingSystem> </VendorInfo> </xsl:for-each> <Customer><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/CustomerRef/FullName"/></Customer> <DODAAC></DODAAC> <ContractId><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/PONumber"/></ContractId> <InvoiceDate><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/TxnDate"/></InvoiceDate> <QBInvoiceId><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/RefNumber"/></QBInvoiceId> <DoDInvoiceId><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/RefNumber"/></DoDInvoiceId> <Total><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/Subtotal"/></Total> <Memo><xsl:value-of select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/Memo"/></Memo> <FastPay></FastPay> <FinalInvoice></FinalInvoice> <LineItems> <xsl:for-each select="/QBXML/QBXMLMsgsRs/InvoiceQueryRs/InvoiceRet/InvoiceLineRet"> <LineItem> <CLIN></CLIN> <Item><xsl:value-of select="ItemRef/FullName"/></Item> <Description><xsl:value-of select="Desc"/></Description> <UOM></UOM> <Quantity><xsl:value-of select="Quantity"/></Quantity> <Rate><xsl:value-of select="Rate"/></Rate> <Amount><xsl:value-of select="Amount"/></Amount> </LineItem> </xsl:for-each> </LineItems> </InvoiceSimple> </xsl:template> </xsl:stylesheet>

  18. SimpleInvoice.XML <?xml version="1.0" encoding="UTF-16"?> <?xml-stylesheet type="text/xsl" href="SimpleInvoiceView.xsl"?> <InvoiceSimple> <VendorInfo> <CompanyName>Rock Castle</CompanyName> <CAGE>1234567</CAGE> <DUNS>7654321</DUNS> <AccountingSystem>QuickBooks</AccountingSystem> </VendorInfo> <BuyOffice>DoD</BuyOffice> <PayOfficeDODAAC>333444555</PayOfficeDODAAC> <ContractId>DAA201-98-C001</ContractId> <InvoiceDate>2003-12-15</InvoiceDate> <QBInvoiceId>87</QBInvoiceId> <DoDInvoiceId>87</DoDInvoiceId> <Total>20000.00</Total> <Memo>Order was shipped 12/30/2003</Memo> <FastPay></FastPay> <FinalInvoice></FinalInvoice> <LineItems> <LineItem> <CLIN>1</CLIN> <Item>Livestock:Pigs</Item> <Description>Pigs; Live</Description> <UOM>EA</UOM> <Quantity>100</Quantity> <Rate>200.00</Rate> <CalculatedAmount>20000.00</CalculatedAmount> </LineItem> </LineItems> </InvoiceSimple>

  19. SimpleInvoiceView.XSL <?xml version="1.0"?> <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"> <xsl:output method="html" version="4.0" omit-xml-declaration="yes" indent="yes"/> <xsl:template match="/"> <html> <head> <title>DoD Simple Invoice Submission</title> </head> <body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> <form name="InvoiceSimple" method="post" action="http://www.dfas.mil/SimpleInvoice/submitInvoice.htm"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="21%" rowspan="2" align="center" bgcolor="#353465"> <h1> <strong> <font color="#FFFFFF" face="Arial, Helvetica, sans-serif">Invoice</font> </strong> </h1> </td> <td width="38%" height="30" align="left" bgcolor="#353465"> <div align="right"> <font color="#FFFFFF"> <em> <b>Please complete fields and submit</b> </em> </font> </div> </td> <td width="41%" align="right" bgcolor="#353465"> <font color="#FFFFFF"> <b> <a href="http://www.dfas.mil/SimpleInvoice/help.htm">Help</a> </b> </font> </td> </tr> …

  20. Lessons Learned • Took an ‘out of the box’ approach; we started down the typical development path as to be expected when presented with a visual basic (VB) library to interface via a complex API such as QuickBooks - template for the future. • Our approach… • Uses XML for storing and manipulating rather than a desktop database such as Access • Limits the use of VB for user interface; doesn’t use array type interface such as flexgrid control – but instead uses HTML • Uses a very simple HTTP interface back to DFAS; traverses any firewalls, etc. and easily backend supported by ISO/TSO • XSL editing tools today don’t cut the mustard – one still needs to create a web page and edit in the XML linking to create a presentation stylesheet (XSL). As far as XSL for mapping, we didn’t find an alternative other than coding. Our approach allows work to take place in any web development environment. • Shocking of all, we learned that there isn’t really a standard web editor within DFAS We did use the 30-day evaluation of Dreamweaver MX and recommended this tool to ISO/TSO as one candidate.

  21. Next Steps • Meet with Electronic Commerce and Server side team • Marketing: Align timeline to coincide with QuickBooks 2003 release • Develop mechanism for User to edit ‘company’s settings’ • Adjust presentation stylesheet to be replica of current web interface, • assuring scripts for checking fields, including contract number • Option: Demonstrate Server Side processing • (ASP processing to database) • passing Web variables • passing XML • If required at server side, prompt user for log in • Server side support; Help page, and other links • Identify Alpha testers for rollout Thank you

More Related