Flow Diagram for Sales Order in Financial Domain - DOC

Document Sample
Flow Diagram for Sales Order in Financial Domain - DOC Powered By Docstoc
					                                           UCDBuy Reconciliation Process

The following document outlines the existing Reconciliation Process on UCDBuy for Hosted and Punch-out Catalogs
utilizing the cXML Invoice process.

      The process assumes that the PO cXML has been submitted and the Supplier shipped the product to the end-
       user (department).
      Once the product is shipped the Supplier submits a cXML Invoice to a designated web site (Review cXML Invoice
       Sample). In addition, the Supplier charges the ghost card for the product shipped. Each Supplier has been
       assigned a unique ghost card; for the current process to work this ghost card must be unique for each
       Supplier/Contract. Furthermore, the Supplier would need to provide our Purchase Order Number on the Point of
       Sale Text Field on the Credit Card Transaction received (Review Credit Card Transaction sample) from our credit
       card provider (USBank).
      When the cXML Invoice is received and accepted by the system, then a sales transaction is created.
      Once a day (manual process), the system will retrieve all Sales Transactions and generates the GL Recharges.
       The GL recharges are submitted into the GL scrubber engine, and get posted to ledger overnight.
      If the Invoice Received matches the Credit Card Payment Detail, then no action is required (by AP staff) nor do
       they look at those transactions. However, if there is a discrepancy between Invoice Received and Credit Card
       Transaction received by our credit card provider; then the system generates the following reports
       (UCDbuy/Pcard Invoice Discrepancies and Daily Invoice Transactions Discrepancies). Sample of these reports are
       located (in sections) below.
      In addition to the system generated reports; end-users have the ability to execute the following reports (on-
       demand).
            o UCDBuy Invoices with No PCard Charge – Displays Orders and Invoices received without a
                corresponding PCard Charge; end-user has the ability to select time frame. It normally defaults to
                display all discrepancies that fall between 2 and 10 weeks old.
            o UCDBuy PCard with Added Tax – Displays UCDBuy Purchasing Card transactions with UCD added tax;
                please note these transactions normally have been generated because the vendor didn’t include tax on
                the Purchasing Card transaction record. UCD is obligated to collect sales tax for the California Franchise
                Tax Board (if the Vendor is out-of-state). If the vendor has charge tax; however, they have not indicated
                on the credit card transaction, Materiel Management then requests an exception (from AP) and the
                income account is credited. This particular transaction is performed in our financial system (DaFIS).
            o UCDBuy Credit Invoice Entry; this screen enables our end-user (AP staff) to generate Credit Invoices;
                this particular option was specifically created for Dell. Dell doesn’t provide credits via cXML Invoices;
                these credits are done manually via a DaFIS Credit Memo. Please note that we may have vendors that
                may not be able to handle credits electronically. Also, this is the only option that the end-user can
                perform that has financial implications (within the reconciliation realm). This particular action will credit
                the department’s account and debit the income account. All other, if any, financial transactions are
                performed in DaFIS.
            o UCDBuy PCard/Invoice Match – Lists all Credit Card activity with a corresponding Invoice; this particular
                report provides all matches for a specific Catalog and a specific time frame.
            o UCDBuy Invoices no Order Match – this is the same as the section of the report that’s generated
                automatically; however, the end-user (AP staff) can indicated the appropriate Order Number or mark
                this Invoice as a Vendor error.
            o UCDBuy/PCard Invoice Discrepancies– this is the same as the section of the report that’s generated
                automatically; however, the end-user (AP staff) has the ability to generate this report on demand. In
    addition, after the necessary research is done, the end-user can then remove the discrepancy from the
    report; otherwise it will remain indefinitely.
o   UCDBuy Credit Card Discrepancy Review – Displays Credit Card discrepancies that have been removed
    by the end-user. Please note: removing a discrepancy record from this report, adds the original
    discrepancy back to the previous report (UCDBuy/PCard Invoice Discrepancies).
Flow Diagram

                                    EP – UCDBuy Invoice/Recharge Process
                                                                $                                             $




Invoices Received Electronically                          Suppliers Bank
                                                                                                          US Bank
          By Suppliers




                          cXML Invoice
                                                    YES
                             (Y/N)


                                                           System Process e-Invoices
                                                                                                   DaFIS Pcard Transactions
                                                                 Automatically


                               NO




                                                                    Invoices are
                                                                                                       Invoices/Pcard
                                                                      created
                                                                                                     Matching Processes
                                                                                                         are created
                 Technical Staff Process Invoices
                           (Flat Files)
                              Fisher
                                                                             Invoice Reconciliation Process



                                                                   EP Sales
                                                                Transactions are
                                                                    created




                                                                    Scrubber
                                                                    Process is
                                                                     Created

                Technical Staff Process Recharges
                                                                                                     Ledger Receives Recharges
                    (eProcurement/UCDBuy)




UCDBuy (Reconciliation tables):

EP_INVOICEXML_T – Collects all submitted cXML Invoices

EP_INVOICE_T – Collects all submitted Invoices (parsed cXML Invoices and Flat file Invoices)

EP_SLS_ITM_T – Collects all Sales Transactions; these records are a by-product of the EP_INVOICE_T table.

EP_CC_DISCREPANCY_T – Collects all Discrepancies that have been removed by the end-user.

ST_SCRB_SLS_ITM_T – Collects all Sales Transactions and generates the GL re-charges.

CHARGE_CARD_FEED_TRANSACTION – Contains all Credit Card activity at UCDavis.
cXML Invoice Sample
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE cXML SYSTEM "http://xml.cxml.org/schemas/cXML/1.2.009/InvoiceDetail.dtd">
<cXML version="1.2.009" payloadID="1249018945190.319906613@prod-wm-app9" timestamp="2009-07-31T12:00:00-00:00"
xml:lang="en-US">
  <Header>
    <From>
      <Credential domain="DUNS" type="marketplace">
         <Identity>606788404</Identity>
         <SharedSecret>ucdpasswrd06</SharedSecret>
      </Credential>
    </From>
    <To>
      <Credential domain="NetworkId" type="marketplace">
         <Identity>eprocurement@ucdavis.edu</Identity>
      </Credential>
    </To>
    <Sender>
      <Credential domain="DUNS" type="marketplace">
         <Identity>606788404</Identity>
         <SharedSecret>ucdpasswrd06</SharedSecret>
      </Credential>
      <UserAgent>Supplier</UserAgent>
    </Sender>
  </Header>
  <Request deploymentMode="production">
    <InvoiceDetailRequest>
      <InvoiceDetailRequestHeader invoiceID="736873JUL3009" purpose="standard" operation="new"
invoiceDate="2009-07-30T12:00:00-00:00">
         <InvoiceDetailHeaderIndicator/>
         <InvoiceDetailLineIndicator/>
         <InvoicePartner>
           <Contact role="from">
             <Name xml:lang="en-US">Laura Sebesta</Name>
             <PostalAddress>
               <Street>800 W. Bryn Mawr Ave.</Street>
               <Street>AMNE / AMSE cXML Profile</Street>
               <City>Itasca</City>
               <State>IL</State>
               <PostalCode>60143</PostalCode>
               <Country isoCountryCode="US">United States</Country>
             </PostalAddress>
           </Contact>
         </InvoicePartner>
         <InvoicePartner>
           <Contact role="soldTo">
             <Name xml:lang="en-US">UC DAVIS                  </Name>
             <PostalAddress>
               <Street>MATERIEL MGMT FINANCE UNIT</Street>
               <Street>202 COUSTEAU PL., STE 205 </Street>
               <City>DAVIS              </City>
               <State>CA</State>
               <PostalCode>956167715</PostalCode>
               <Country isoCountryCode="US">United States</Country>
             </PostalAddress>
           </Contact>
         </InvoicePartner>
         <InvoicePartner>
           <Contact role="remitTo">
             <Name xml:lang="en-US">OMX Office Solutions</Name>
             <PostalAddress>
               <Street>800 W. Bryn Mawr Ave</Street>
               <Street>AMNE/ AMSE cXML profile</Street>
               <City>Itasca</City>
               <State>IL</State>
               <PostalCode>60143</PostalCode>
               <Country isoCountryCode="US">United States</Country>
             </PostalAddress>
           </Contact>
           <IdReference identifier="069984151" domain="accountID"/>
         </InvoicePartner>
         <InvoicePartner>
           <Contact role="billTo" addressID="95616 ">
             <Name xml:lang="en-US">UC DAVIS                   </Name>
             <PostalAddress>
               <Street>O&amp;M PLANT OPERATIONS      </Street>
               <Street>STEAM PLANT AT DIARY RD   </Street>
        <City>DAVIS              </City>
        <State>CA</State>
        <PostalCode>95616    </PostalCode>
        <Country isoCountryCode="US">United States</Country>
      </PostalAddress>
    </Contact>
  </InvoicePartner>
  <InvoiceDetailShipping>
    <Contact role="shipFrom">
      <Name xml:lang="en-US">OfficeMax Contract</Name>
      <PostalAddress>
        <Street>1315 O&#39;Brien Dr.</Street>
        <City>Menlo Park</City>
        <State>CA</State>
        <PostalCode>94025</PostalCode>
        <Country isoCountryCode="US">United States</Country>
      </PostalAddress>
    </Contact>
    <Contact role="shipTo">
      <Name xml:lang="en-US">UC DAVIS                  </Name>
      <PostalAddress>
        <Street>O&amp;M PLANT OPERATIONS      </Street>
        <Street>STEAM PLANT AT DIARY RD   </Street>
        <City>DAVIS              </City>
        <State>CA</State>
        <PostalCode>95616    </PostalCode>
        <Country isoCountryCode="US">United States</Country>
      </PostalAddress>
    </Contact>
  </InvoiceDetailShipping>
  <InvoiceDetailPaymentTerm payInNumberOfDays="30" percentageRate="0"/>
</InvoiceDetailRequestHeader>
<InvoiceDetailOrder>
  <InvoiceDetailOrderInfo>
    <OrderReference orderID="0000406203-1">
      <DocumentReference payloadID="2009-07-29T14:14:35-0700@0000406203-1"/>
    </OrderReference>
  </InvoiceDetailOrderInfo>
  <InvoiceDetailItem invoiceLineNumber="1" quantity="1">
    <UnitOfMeasure>EA</UnitOfMeasure>
    <UnitPrice>
      <Money currency="USD">27.87</Money>
    </UnitPrice>
    <InvoiceDetailItemReference lineNumber="1">
      <ItemID>
        <SupplierPartID>S51382A003AA </SupplierPartID>
      </ItemID>
      <Description xml:lang="en-US">COPIER TONER CTG,NPG11 2                </Description>
      <ManufacturerPartID>1382A003AA </ManufacturerPartID>
      <ManufacturerName xml:lang="en-US">CANUSA</ManufacturerName>
    </InvoiceDetailItemReference>
    <SubtotalAmount>
      <Money currency="USD">00027.87</Money>
    </SubtotalAmount>
    <GrossAmount>
      <Money currency="USD">27.87</Money>
    </GrossAmount>
    <NetAmount>
      <Money currency="USD">00027.87</Money>
    </NetAmount>
    <Distribution>
      <Accounting name="AccountID">
        <AccountingSegment id="0000406203-1">
          <Name xml:lang="en-US">OLI</Name>
          <Description xml:lang="en-US">3-6098505-UNALL--</Description>
        </AccountingSegment>
      </Accounting>
      <Charge>
        <Money currency="USD">30.31</Money>
      </Charge>
    </Distribution>
  </InvoiceDetailItem>
</InvoiceDetailOrder>
<InvoiceDetailSummary>
  <SubtotalAmount>
    <Money currency="USD">27.87</Money>
  </SubtotalAmount>
  <Tax>
    <Money currency="USD">2.30</Money>
          <Description xml:lang="en-US"/>
          <TaxDetail purpose="tax" category="sales" percentageRate="8.252601363473268">
             <TaxableAmount>
               <Money currency="USD">27.87</Money>
             </TaxableAmount>
             <TaxAmount>
               <Money currency="USD">2.30</Money>
             </TaxAmount>
             <TaxLocation xml:lang="en-US">usa</TaxLocation>
             <Description xml:lang="en-US"/>
          </TaxDetail>
        </Tax>
        <ShippingAmount>
          <Money currency="USD">.00</Money>
        </ShippingAmount>
        <GrossAmount>
          <Money currency="USD">30.17</Money>
        </GrossAmount>
        <NetAmount>
          <Money currency="USD">30.17</Money>
        </NetAmount>
        <DueAmount>
          <Money currency="USD">30.17</Money>
        </DueAmount>
      </InvoiceDetailSummary>
    </InvoiceDetailRequest>
  </Request>
</cXML>



Please Note: the Payload ID must be a unique identifier across the system, the SharedSecret Tag identifies the Supplier
within our system, and the Distribution Tag identifies the original Purchase Order Information (<AccountingSegment
id="0000406203-1">).
Credit Card Transaction (Sample Report)




Please Note: This information is provided by our Credit Card provider (USBank) the key to the reconciliation process is
that the Point of Sale Text field (Listed above) contains the UCDBuy Order Number. The total amount of this transaction
should match the total amount received on the Invoice cXML. If it doesn’t, our Accounts Payable staff will be involved.
Normally that means that they contact the vendor and determine what the issue might be.
                                                    UCDBuy/PCard Invoice Discrepancies

This report allows for a 7 day delay between the most recent vendor invoice and receipt of purchasing card charge.

                                 Order    Invoice       Transaction      Total P-Card      Invoice                  Transaction
    Merchant Name                                                                                    Difference                   Posted Date
                                Number     No.            Amount          Amount          Amount                      Date


 VWR INTERNATIONAL
                          0000420136                  $17.90          $17.90            $0.00        ($17.90)     12/10/2009      12/11/2009
INC


 VWR INTERNATIONAL
                          0000422603     40057993     $928.39         $2,507.84         $1,777.50    ($730.34)    10/29/2009      10/30/2009
INC


 VWR INTERNATIONAL
                          0000422652     40030408     $20.03          $58.56            $40.32       ($18.24)     10/31/2009      11/02/2009
INC


DMI* DELL HIGHER
                          0000421543                  $7,798.90       $7,798.90         $0.00        ($7,798.90) 12/07/2009       12/07/2009
EDUC


DMI* DELL HIGHER
                          0000421848                  $1,318.40       $1,318.40         $0.00        ($1,318.40) 12/03/2009       12/03/2009
EDUC


DMI* DELL HIGHER
                          0000424940                  $594.12         $1,230.88         $0.00        ($1,230.88) 11/11/2009       11/11/2009
EDUC


DMI* DELL HIGHER
                          0000427695                  $81.24          $81.24            $0.00        ($81.24)     12/02/2009      12/03/2009
EDUC


DMI* DELL HIGHER
                          0000427759                  $39.14          $1,366.30         $0.00        ($1,366.30) 12/02/2009       12/03/2009
EDUC


DMI* DELL HIGHER
                          0000427759                  $1,327.16       $1,366.30         $0.00        ($1,366.30) 12/05/2009       12/07/2009
EDUC


OFFICEMAX
                          0000421252                  ($38.71)        $12.47            $12.46       ($0.01)      12/02/2009      12/03/2009
CT*IN#348765


OFFICEMAX
                          0000421252                  $51.18          $12.47            $12.46       ($0.01)      10/21/2009      10/22/2009
CT*IN#859116




Total Seconds to run: 546.953

Please Note: This report is generated on a daily basis; it provides discrepancies between Invoices received and PCard
Transactions received by our provider.
                                                    Daily Invoice Transactions Discrepancies



Substitutions

                                                                                Inv. Inv.
                                                 Commodity                                      Inv. Tax Inv. Total Date      Posted
Vendor           Order - Item No. PO Commodity                U/I Description   Qty Unit                                                   Invoice No.
                                                 Code                                           Amount Amount Shipped         Date
                                                                                Ship Price


Laboratory - 0000430269 - 22 03-409-11C.CS MISC               EA VOLUME         -1    ($0.79) $0.00       ($0.79)   12/14/2009 12/15/2009 7074968
Fisher                                                           DISCOUNT
Scientific


Laboratory - 0000430269 - 22 03-409-11C.CS MISC               CS VOLUME         -1    ($1.25) $0.00       ($1.25)   12/14/2009 12/15/2009 7074968
Fisher                                                           DISCOUNT
Scientific


Stationery -     0000430445 - 1 B1CB435WBLK- B1CB435WBLK10 EA PLNR,10RY         1     $6.28     $0.52     $6.80     12/15/2009 12/15/2009 600717DEC1509
Office Max                      09                            MO
                                                              ECOLOGX




Price Discrepancies

                                                             Inv.           Inv.              Inv.
               Order - Item    Commodity                            PO Unit          Inv. Tax        PO     Percent    Date            Posted      Invoice
Vendor                                   U/I Description     Qty            Unit              Total
               No.             Code                                 Price            Amount          Amount Difference Shipped         Date        No.
                                                             Ship           Price             Amount


Laboratory 0000413473 - 2 MISC             EA NON-CONTRACT 1        $30.00 $28.37 $0.00        $28.37   $32.63 -13.0555% 12/14/2009 12/15/2009 7073464
- Fisher                                      TRANSPORTATION
Scientific


Department Name:               Plant Sciences                F.A.U.: 3-VCR6U66-OSIF1-8030---


Laboratory 0000424790 - 1 MISC             EA DIRECT SHIP    1      $366.00 $12.00 $0.00       $12.00   $398.03 -96.9852% 12/14/2009 12/15/2009 7073446
- Fisher                                      SUPPLIER
Scientific                                    CHARGES


Department Name:               SM: Medical Microbiology &    F.A.U.: S-CBNOVEL--8030---
                               Immunology


Laboratory 0000424790 - 1 MISC             EA NON-CONTRACT 1        $366.00 $34.00 $0.00       $34.00   $398.03 -91.4579% 12/14/2009 12/15/2009 7073446
- Fisher                                      TRANSPORTATION
Scientific


Department Name:               SM: Medical Microbiology &    F.A.U.: S-CBNOVEL--8030---
                               Immunology



Over-Shipments

                Order - Item    Commodity                           Qty              Inv. Unit     Inv. Tax Inv. Total Date       Posted
Vendor                                    U/I Description                 Total Inv.                                                            Invoice No.
                No.             Code                                Order            Price         Amount Amount Shipped          Date
                                                                          Qty Qty
                                                                            Ship    Ship


Dell          0000426742 - 1 223-9153     EA 01:00 Latitude         1       2       1      $2,106.74 $129.50 $2,236.24 11/20/2009 12/15/2009 XDJ9KTF22R
Computers                                    E6500;IntelREG CoreTM
                                             2 Duo T9600 with VT
                                             (2.80GHz, 6M L2 Cache,


Laboratory - 0000413473 - 2 NC9798735 PK 3 X 3 WEIGH PAPER          1       2       1      $30.00     $2.48    $32.48       12/14/2009 12/15/2009 7073464
Fisher                                   1000/PK
Scientific


Laboratory - 0000413473 - 2 MISC          EA NON-CONTRACT           1       2       1      $28.37     $0.00    $28.37       12/14/2009 12/15/2009 7073464
Fisher                                       TRANSPORTATION
Scientific




UCDBuy Invoices No Order Match

                    Order - Item   Commodity                                    Qty     Inv. Unit   Inv. Tax   Inv. Total   Date        Posted     Invoice
Vendor                                          U/I Description
                    No.            Code                                         Ship    Price       Amount     Amount       Shipped     Date       No.


Laboratory -        -              T326F1GAL    CS TOLUENE HISTO F-STYL         1       $120.95     $9.98      $130.93      12/10/2009 12/11/2009 6668417
Fisher Scientific                                  POLY 1GAL


Laboratory -        -              1535050      CS FRZCNTD                      1       $49.64      $4.10      $53.74       12/14/2009 12/15/2009 7073448
Fisher Scientific                                  RT1.0/1.2/1.5/2.0ML1CS


Laboratory - VWR 0000424654 - 7 EMD-10081-      EA QUANT PEROXIDE STRIP         1       $33.98      $2.97      $36.95       12/11/2009 12/12/2009 40424905
                                1-EA               EA=PK100



This report took 41.109 seconds to complete.

Please Note: This report is generated on a daily basis; it’s grouped into four categories (Substitutions, Price
Discrepancies, Over-Shipments, Invoices received without a corresponding UCDBuy Order).

Substitutions – The system matches the Purchase Order’s particular Commodity Code with the Invoice’s (received)
ManufacturerPartID tag value; if those two don’t match then the particular line is displayed on this report (under the
Substitutions grouping).

Price Discrepancies - The system matches the Purchase Order’s Total Line Amount with the Invoice’s (received) Total
Line Amount; if those two don’t match (within the tolerance) then the particular line is displayed on this report (under
the Price Discrepancies grouping).

Over-Shipments – The system compares the (all Invoice Quantity received) for a particular Purchase Order and if the
quantity received is greater than the original Purchase Order quantity, then the particular Order/Line number is
displayed on this report (under the Over-Shipment grouping).

UCDBuy Invoices No Order Match – Occasionally we receive invoices without the corresponding Purchase Order number,
then this particular Invoice line is displayed on this report (under the UCDBuy Invoices No Order Match grouping).

Our Accounts Payable staff pays close attention to the Price Discrepancies and the UCDBuy Invoices No Order Match sections of the
report; Price Discrepancies normally trigger a new catalog load process (with updated prices); the UCDBuy Invoices No Order Match
normally triggers a call to the vendor to determine with actual Order Number that were attempting to Charge and/or Credit.
                                                 Decision Support Reports

The following reports require end-user action.


UCDBuy Invoices with No PCard Charge
UCDBuy Invoices No Order Match




UCDBuy PCard with Added Tax
UCDBuy Credit Invoice Entry




UCDBuy PCard/Invoice Match
UCDBuy/PCard Invoice Discrepancies




UCDBuy Credit Card Discrepancy Review

				
DOCUMENT INFO
Description: Flow Diagram for Sales Order in Financial Domain document sample