MarkView Workflow - After the fiscal year is rolled over in SAP workitems may route to the Cancelled queue unexpectedly

Applies To

• ERP System: SAP
• MarkView Version: 6.0 to 7.0.1

  Note that this is resolved in the 7.0.1.1 FixPack

Behavior

Workitems may unexpectedly route to the Cancelled queue when the InvoiceSweep process is run after the fiscal year roll over in SAP.

Known Causes

When a parked invoice in SAP is rolled into a new fiscal year, SAP will copy the invoice from the old year to the new year, usually using the same invoice number. The is because SAP identifies an invoice by combining the three data elements of company code invoice number and fiscal year to form a unique key. For example in 2010 you may have the following document number: 44000012342010. After the fiscal year rolls over a new document number will be created as follows: 4400001234 2011.

MarkView stores the document number, including the fiscal year, in a workitem property in order to link the workitem to a specific SAP Document. When the fiscal year changes in SAP and the InvoiceSweep process runs, MarkView is unable to find the original document number and assumes it has been deleted from SAP, routing the workitem to the Cancelled queue. The original SAP invoice still exists but has been disabled. Technically, the status of the original invoice has gone from “Parked” to “Parked with change of document id”.

Note: This behavior is only relevant to parked invoices. Once an invoice is posted the fiscal year assignment cannot be changed and thus is not relevant to this issue.

Resolution

Please note that this issue has been resolved in the MarkView 7.0.1.1 Fix Pack. If you are running MarkView version 7 please ensure you have applied the latest Fix Pack to your environment.

The following steps can be taken in order to prevent this issue from occurring:

1. Apply SAP Transport hot fix if needed.
   • MarkView Versions prior to 6.1
     • An SAP Transport hot fix is needed to prevent any issues when MarkView attempts to apply and release the payment block in SAP after the fiscal year change. (Note that this can be applied any time prior to the fiscal year changing.)
       Please download the 5.9.2.16-SAPx.x.zip file, where x.x corresponds to your version of SAP. These zip files contain the appropriate SAP transports for your version of SAP. For example customers running SAP 4.7 would download the 5.9.2.16-SAP4.7.zip file.
   • MarkView Versions 6.1 to 6.5.x
     • The SAP Transport hot fix was included in MarkView for SAP Release 6.1. As such no separate hot fix needs to be applied.
2. A new MarkView package is needed for performing the fiscal year rollover in MarkView. Run the 'mvsap_bapi_invoice_getdetail.sql' script through SQL*Plus in the MarkView Schema to install the package. (Note that this can be applied any time prior to the fiscal year changing.)
3. Immediately before running any processes which will update the fiscal year in SAP please take the following steps.
   • Run the following command in the MarkView Schema to make sure any workitems associated with deleted or cancelled invoices are swept to the appropriate queue:

     • exec MVAP_utility.InvoiceSweep;

   • Ensure that all pending events are processed in your MarkView environment by confirming the following query returns zero (0) records.

     • select * from sf_event_alert;

   • Shutdown all MarkView dbms_jobs, and the MarkView Application Server Components, except for the MarkView Gateway. The following OC4J/Deployment is the MarkView Gateway which should be left running:
     mvsap-gw
4. Run any processes in SAP to update the fiscal year.
5. After updating the fiscal year in SAP, run the attached IdentifyWIToRollover.sql script (or IdentifyWIToRolloverPre601.sql for MarkView for SAP versions prior to 6.0.1) to update the link between the MarkView Workitem and SAP. This script has four (4) options:
   • Report the old InvoiceID and new InvoiceID properties for an individual workitem. This option does not convert the work item.
   • Report the old InvoiceID and convert to the new InvoiceID for an individual workitem.
   • Report the old InvoiceID and new InvoiceID for all workitems requiring a fiscal year rollover in MarkView. This option does not convert the workitems.
   • Report the old InvoiceID and new InvoiceID for all workitems requiring a fiscal year rollover in MarkView, and convert the workitems.
     This script will need to be executed from the MarkView Schema via SQL*Plus.
     MarkView Versions prior to 6.0.1 should run the 'IdentifyWIToRolloverPre601.sql' script, while MarkView Versions 6.0.1 and higher should run the 'IdentifyWIToRollover_v2.sql' script.
6. After the script has been executed you can check the log files for any Failures. Any failues in the log file can be logged with Technical Support and handled in a one-off manner.
7. Although there may be failures you should shut down your MarkView Application Server completely, and then bring it back-up again.
8. Leave the InvoiceSweep dbms_job disabled and bring up the rest of your MarkView dbms_jobs.
9. Once any failures are resolved with Technical Support the InvoiceSweep dbms_job can be started.

Known Issues

• Running the IdentifyWIToRollover.sql script
  • No data found:
    ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "MARKVIEW.MVSAP_GET_INVOICE_INFO", line 1640
ORA-06512: at "MARKVIEW.MVSAP_GET_INVOICE_INFO", line 1678
    
    This error can occur when the SAP and MarkView data is not synchronized. Specifically if there is an Invoice associated with a MarkView workitem which does not exist in SAP. Following the steps above should prevent this issue from occurring as running InvoiceSweep at Step 3 above will automatically route the associated MarkView workitem to the Cancelled queue.
    If this error does occur you can run the IdentifyWIToRollover_debug.sql script attached to the answer. This script will catch the exception and log the Workitem, Invoice, and error. In addition it will allow the script to continue. You can then review the log file and confirm the Invoice in question exists in SAP.
  • Numeric or Value Error:
    ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "MARKVIEW.MVSAP_GET_INVOICE_INFO", line 1066
ORA-06512: at "MARKVIEW.MVSAP_GET_INVOICE_INFO", line 1685
    
    This error can indicate that there is a mis-match between the decimal marker in the database, and the data being returned from the MarkView Gateway. In order to resolve the issue run the following command prior to running the IdentifyWIToRollover.sql script.
    ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,' ;
• MarkView Advisor
  • When a fiscal year rollover occurs, any PO Invoice that is rolled over to the new fiscal year will not appear in Advisor until it is either: 1) Updated manually or 2) Posted to GL.

    Note that this is resolved in the 7.0.1.3 FixPack

• Attachments.zip

Keywords: work item, work-item