Question Status

Verified
Casey McCarthy asked a question on 4 Feb 2014 12:51 PM

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?

Reply
Verified Answer
PerumalSamy R responded on 4 Feb 2014 11:36 PM

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

Thanks,

Perumalsamy R

Reply
Casey McCarthy responded on 5 Feb 2014 1:24 PM

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.

Reply
Suggested Answer
PerumalSamy R responded on 5 Feb 2014 7:53 PM

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

Thanks,

Perumalsamy R

Reply
Verified Answer
Mark Asmus responded on 7 Feb 2014 2:05 PM

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.

Reply
Casey McCarthy responded on 10 Feb 2014 1:23 PM

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!

Reply
Casey McCarthy responded on 11 Feb 2014 9:42 AM

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.

Reply
PerumalSamy R responded on 11 Feb 2014 9:54 AM

Hello Casey,

Thanks for your valuable information.

Thanks,

Perumalsamy R

Thanks,

Perumalsamy R

Reply
Verified Answer
PerumalSamy R responded on 4 Feb 2014 11:36 PM

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

Thanks,

Perumalsamy R

Reply
Verified Answer
Mark Asmus responded on 7 Feb 2014 2:05 PM

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.

Reply
Suggested Answer
PerumalSamy R responded on 5 Feb 2014 7:53 PM

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

Thanks,

Perumalsamy R

Reply