Line item for Expense Report set to 1/1/1900

This question is answered

We currently have a batch that is unallocated due to an expense report with line items using 1/1/1900 as their date. Is there a way to change that date either in the user interfaces of SL or Business Portal or can I only change it in the database?

Verified Answer
  • Hi Casey,

    You can use the Time & Expense Rpt Entry (TM.ENT.00) screen of Time & Expense for projects under project module if the document # is in “In Process” status.

      

    Or you can use the Business portal screen to change the report date of line items of Expense Report.

    Or you can just do the report date of line items (PJEXPDET) update through SQL Query if doument # is in completed status.

    Update PJEXPDET SET exp_date = ‘Date as you wanted’ where docnbr = ‘Document #’

    Hope this will help you to change the Report Date of Line Items.

    Thanks,

    Perumalsamy R

  • Depending on your Auto-Allocation settings, you may also want to check the pjtranwk table as well.   Use basically the same update, just against the pjtranwk table instead of pjtran.  

    Mark
    Technical Support Engineer

    **This posting is provided "AS IS" with no warranties and confers no rights.

All Replies
  • Hi Casey,

    You can use the Time & Expense Rpt Entry (TM.ENT.00) screen of Time & Expense for projects under project module if the document # is in “In Process” status.

      

    Or you can use the Business portal screen to change the report date of line items of Expense Report.

    Or you can just do the report date of line items (PJEXPDET) update through SQL Query if doument # is in completed status.

    Update PJEXPDET SET exp_date = ‘Date as you wanted’ where docnbr = ‘Document #’

    Hope this will help you to change the Report Date of Line Items.

    Thanks,

    Perumalsamy R

  • The expense report was already completed and posted. I was able to use the Update SQL statement to change the date. However we are continuing to get an error when we try to allocate the batch. We tried changing the trans_date in PJTRAN where the acct = 'REIMB EXPENSE' AND batch_id = '[batchnumber]' but we still get the error.

    The error from allocation in the logfile is: "Rate not found for Source Project/Task 4684/999001 in Method/Step: TM/ 60"

    Note: We are testing these changes and SQL statements in an offline test environment that is an older copy of the production database.

  • Hi Casey,

    The Rate is not found for the particular account category “REIMB EXPENSE”. I believe you have already defined the method for TM/60 using the Allocation Method setup (PA.MET.00) screen.

    You have to set the rate for the account category using the Multi-Level Rate Entry (AL.RAT.00) screen.

    For example,

     I have included the REIMD EXPENSE account category rate for your reference

    Thanks,

    Perumalsamy R

  • Depending on your Auto-Allocation settings, you may also want to check the pjtranwk table as well.   Use basically the same update, just against the pjtranwk table instead of pjtran.  

    Mark
    Technical Support Engineer

    **This posting is provided "AS IS" with no warranties and confers no rights.

  • I took the following steps:

    1. Backup Application database
    2. New Query against the Application database
    3. Executed the following statements in the order listed:
    1. Update PJEXPDET SET exp_date = '10/29/2013 00:00:00' where docnbr = '[doc number of the expense report]'
    2. Update PJTRAN SET trans_date = '10/29/2013 00:00:00' where acct = 'REIMB EXPENSE' AND batch_id = '[batchnumber]'
    3. Update PJTRANWK SET trans_date = '10/29/2013 00:00:00' where acct = 'REIMB EXPENSE' AND batch_id = '[batchnumber]'

    Afterward we were able to successfully run the allocator for the company.

    Thank you Perumalsamy and Mark!

  • Additional information:

    We were also having problems with using Analysis services to build our data warehouse with data from the Application database. When the issue with the hung allocation batch first occurred, our accounting manager tried to reverse the expense report by using a date of 1900-01-02. This generated some other records in the PJTRAN table that prevented the cubes from being built to populate our data warehouse. We ended up having to update all of the records in PJTRAN that had a date of 1900-01-02. Once we did that, we were able run the jobs for building the data warehouse.

  • Hello Casey,

    Thanks for your valuable information.

    Thanks,

    Perumalsamy R