Clarity

Greetings,  We are wondering if anyone has come up with a way to automate the WIP Adjustment/Approvals process?   We do not use Invoicing in Clarity, so in order to close out a project we must adjust all WIP transactions for a project and approve those adjustments before the project can be marked as closed.   This takes a LONG time (right now there are 200 pages of WIP Adjustments) and is very manual.   Has anyone come up with a better way to do this?  We have already submitted a support request for this in both ver 7.5.3 as well as 12.0.2, but it is not handled any differently in R12 so unless we come up with something this will continue to be a manual process.  Clarity 7.5.3 FP06Oracle 10g  Thanks in advance!  Daniel

Which version do you have?In r8.1 SP3 there isPurge Financial Tables Job
This job permanently deletes all financial transactions for the specified project,
including WIP entries.  Further I recall somebody posting having made Post to WIP to a job. That would require CA approval. There are a number of SP related to WIP (PAV_PRC_POSTTOWIP... and PAC_PRC_WIPADJ etc and even PAC_PURGE_FINANCIAL) in r8.1 SP3. You could have a look at them.  Martti K.

Daniel,  I am curious as to why you think you need to adjust all the WIP transactions.     We have been on financials for almost 3 years now, started out in 7.5.2, on 8.1 now.     We don't close out all the WIP transactions before we close a project.   Technically, you do not even have to approve the WIP Adjustments, they do apply to your financials as soon as they are submitted.     They'll reverse if you  reject but they only disappear from the approval screen when you approve.       If you close them out, aren't you losing all the historical financial history?       Are you doing this because you think you have to?  Steven

Which financial type are your projects Steven?  Martti K.

I am sorry, but I am not sure I understand your question.   When you say type, what are you trying to get at?  We have all our projects setup with Cost Plan's and Budget's.  We break down spending by Transaction Class (Labor, Contracting, Travel, Hardware, etc.) and then by Charge Code (Capital or Expense).  We post labor transactions via Timesheets and non labor transactions come in from the General Ledger via XOG.

When you go to the financial properties page there is financial type field (in r8.x it is Investment financail type) and the choices are Contract, Internal, Retainer and Standard.  Martti K. Message Edited by another_martink on 07-30-2009 04:31 PM [left]

Ours are all set to Standard.      Steven

just stumbled on this and thought I would finish it..you need to set the default to internal if you don't use billing. That way you won't have to close out any finanicals.. you should be able to just close out the project and inactivate it   (note it is NOT retroactive)

Thank you all for your replies.   A few observations & details:  On the Project Properties default tab, the check box for 'Active' is greyed out for projects until all WIP transactions are first cleared.  All of our projects are set to 'Standard' on the Financial Properties type selection.   I'm not sure that I know enough of the differences between these type selections and if it would be appropriate to designate some (or all) as 'Internal'.   Does anyone have any more information on this type designation?   I'm assuming this depends on how we use Clarity from a project perspective.  Thank you in advance for your patience.   I'm relatively new to the Clarity world.  Daniel

Dan.  If you are set to standard, then the issue you are describing is really a result of the 'Chargeable' flag being set to Yes on the WIP transaction.     Chargeable ties into the billing functions, if you are not doing billing out of Clarity then there is no need to set the Chargeable flag to Yes.      If all of your tasks are set to Chargeable = No, you can then make the project inactive.      You can run WIP Adjustments to first filter for an then remove the chargeable flag on the transactions to clean up past projects.     Going forward, on new projects if you do not set the Chargeable flag to yes, you will not experience this problem  again.  Hope this helps.Steve

Hi Steve,  Thanks for the feedback.   I do see the filter option on the 'Create WIP Adjustment' screen to filter by chargeable.   My questions are:   How do we designate a task (or a transaction) as chargeable? Is this something that can be both controlled when the project is setup as well as managed after the fact?   In other words, can I handle the 200+ pages of existing transactions in this manner as well as change the way our projects are created to avoid this in the future?   Thanks again,  Daniel  

I noticed a few points:   There's a 1:1 relationship between what I see in the "Approve WIP Adjustment" screen in Clarity vs. what's in the table PPA_TRANSWIPADJUST When I approve a WIP Adjustment using the application, the PPA_TRANSWIPADJUST table record count is decremented by the same number of WIP transactions that were adjusted/approved.   Does anyone know what the effects would be of simply dropping the records in the PPA_WIPADJUST table?   I'm sure the app is doing more than simply removing the records from the table but we've got to come up with a better method of clearing these WIP transactions so that we can close our projects.   Any feedback would be greatly appreciated.  Daniel

Hi,  There is a link between what you see in the portlet and in the PPA_TRANSWIPADJUST table.   This table is also linked to the PPA_TRANSWIPADJUST_VALUES table.   You will notice that when you have created an adjustment the original transaction stored in the PPA_WIP table changes to Status=1, so would appear to disappear from the financials for the project.   When you approve the adjustment, the transaction from the PPA_TRANSWIPADJUST is moved into the PPA_WIP table.   The original transaction is changed to Status=2 and its APPYTO is set to the TRANSNO of the new transaction.  There are a number of stored procedures that handle creating WIP Adjustments and also approving/rejecting the adjustment created.  REV_PRC_ADJWIPMODXFERACTION allows you to adjust a transaction from a stored procedure - you just need to locate the original TRANSNO.   This returns an Adjustment Group number which you can then feed into a stored procedure called PAC_PRC_ADJWIPAPPROVEITEM_SP which will automatically approve the adjustment.  Hope this helps.  Steve

Hi,     Pls Refer this  Kb article:-  TEC466436
 
  Title:   Clarity: Unable to close financially-enabled projects (REVMGR-20058, REVMGR-20062)
 
  Description:
 
  When trying to change the Project's Financial status to 'Closed', this error message is displayed:
 
  REVMGR-20058 - Unable to close project because some transactions in wip have amount remaining greater than 0
  -- OR --
  REVMGR-20062 - Unable to close project because some transactions are waiting to be adjusted
 
  Solution:
 
  A project cannot be financially closed when there are:
 
  Transactions for that project are posted to financial.  
 
  WIP transactions for that project that are not billed (ie) if there are WIP transactions with an amount remaining not equal to zero.  
   
  If there are any adjustment transactions waiting to be approved.
  First bill and approve those transactions so that the Amount Remaining for the WIP will become zero. But if you don't use the Billing functionality in the application but you keep track of the expenses by creating transactions in the application, then below are the different options to solve this:
 
  For Any (future) financially enabled projects:  
  The project should be created of type INTERNAL so that the transactions are not considered for billing . (OR)      
  For projects of types other than INTERNAL, create the transactions as non-chargeable. (OR)  
   
  For future transactions submitted for any existing projects, create transcations as non-chargeable . While creating Voucher Expense transactions - from Work >Financial Processing > Transaction Entry >Entry > Transaction Details page under the Transactions Data section uncheck the box 'Chargeable'.
  For those existing projects in the system for which you have transactions created already, we have two options to make them non-billable in order to be able to financially close those projects.
 
  Do a Write off for the transactions from the Demand Billing:  
 
  Navigate to Work > Billing > Demand Billing. Browse for the Project Code and Search.        
  From the search result , select the project and hit button.
  (OR)
       
  Create WIP Adjustment for the transactions for the project and mark them non-chargeable:  
 
  Navigate to Work >Financial Processing > Create WIP Adjustment.        
  Enter the From date & To Date, browse for the Project Id.        
  Click 'Search' button.        
  From the results, click on the Transaction Date link to open the transaction.
 
  In the Work > Financial Processing > Create WIP Adjustment > Modify transaction page , under the Transactions Data section uncheck the box 'Chargeable'.    
    Click 'Submit' button.    
  Then approve the WIP Adjustment from the Work > Financial Processing > Approve WIP Adjustment page.  
 
  Repeat the above steps for all outstanding transactions for the projects that you want to close.
  If you still cannot close the project and you wish to contact Clarity Support for more assistance, send the following information:
 
  Screenshot of Admin Tool > invalid Transactions > filter for specific project.    
  Screenshot of Main App > Post to WIP > filter for specific project.    
  Screenshot of Main App > Create WIP Adjustment > filter for project.    
  Screenshot of Main App > Approve WIP Adjustment > filter for project.    
  Screenshot of Main App > Demand Billing > filter for project.    
  Screenshot of Main App > Approve Billing > filter for project.    
  Screenshot of Main App > Print Invoice > filter for project.    
  Execute this query and send results in excel spreadsheet:
 
  select * from ppa_wip
  where
  project_code = AND
  transno in (select transno from ppa_wip_values where amountremaining != 0)
  More Information:
  If the user does not see the project in the 'Demand Billing' page, the user may not have access to generate invoices for that project.
  Go to Project > Properties > Financials > Access and grant access to generate Billing and Approve Billing for the authorized user.  Regards,sundar
 

DanielSGHE wrote: in order to close out a project we must adjust all WIP transactions for a project and approve those adjustments before the project can be marked as closed.
Clarity 7.5.3 FP06Oracle 10g   Why adjust any WIP transactions? Clarity does not require you to do this. Is there a business practice you follow that forces you do this? (e.g. late changes to rate matrix)  WIP adjustments should be infrequent.  

Thanks Sundar, that article was a huge help!  As the article states, projects that are financially enabled can't be closed if there are still amounts remaining in WIP transactions >0.   We don't use the invoicing features in Clarity, so all of our projects fall into this category.   Naturally, we want to first find a way to prevent this from happening and second, find a way to processes these WIP transactions faster than having to Adjust/Approve to close a project.   I think the article Sundar referenced helps us on our way to that goal.  As I see it, the challenges now are to test the alternate method described in the article (write off via Demand Billing for existing projects) and then to incorporate it into our policies and procedures so that we  correctly setup new projects  so that they don't require WIP adjustments (Internal &/or non-chargeable).  Any further observations, comments, etc are very much appreciated!  Daniel

To produce non chargeable transactions:  Input Type Codes can be set to be non chargeable and assigned to resources, when users enter timesheets they can also change to a non-chargeable Input Type Code; If you use Transaction Entry there is a checkbox for Chargeable; If you use XOG you can set the flag Chargeable=0.

Hi,  If you are not using Billing then for "New" Projects you can select type = 'Internal' and so you don't have do any wip adjustments.  But i believe there is some issue(i think it may be wrong)  for existing projects in which their projects are selected other than type =Internal and already have Transactions.  So for existing wip transactions we can handle it via manual adjustments as advised in the KB Article.  Then how to avoid it for future months Transactions?  For transactions(Resource Type=Expense) it is ok to uncheck the chargeable.  But for resources(Resource Type=labour) who input via timesheets,modifying the typecodes to Non-Chg,will there be any cost for the transactions enterd for the Hours Entered?.Hope then we need to provide zero in the rate matrix table for Rate&Actual & standard Cost for the Non-chargeable ones.But then totally cost will not be shown?.  My understanding may be wrong for the above mentioned one,if so pls correct me...  My Question isHow to stop the transactions posted via Timesheets are not considered for billing in future for the projects type 'Internal'.There should be cost in wip.  If a project is selecetd as projects type='Standard'   it becomes read only,is there any way to change it to 'Internal'.  If not then  Before posting to wip If we update the column chargeable=0 in ppa_transcontrol then all the records in ppa_wip will have cost but chargeable=0 so will not be processed to Billing.Is it a correct way to do that as it requires updation of a column.Pls advice...  Regards,sundar      

sundar wrote:
 For transactions(Resource Type=Expense) it is ok to uncheck the chargeable.  How to stop the transactions posted via Timesheets are not considered for billing in future for the projects type 'Internal'.There should be cost in wip.   These  are a good questions Sundar - and I don't know for certain (without testing) but this is what I  expect will happen:  a. you should be able to make this change - and it is probably a good thing for you to do as you are not using the billing functionalityb. once that change is made, all new transactions referring to that now unchargeable charge code will carry the chargeable=0 in ppa_transcontrol   and this will flow directly into ppa_wip  I would not expect  a change of charge code,  from chargeable to non-charegeable,  to affect the rating processes in any way. So, I would expect rates to apply to each hour in the usual way.  Please note  however that I have not had to  undertake this type of change and I would always want to verify by testing before commiting    to it.  Regarding making changes to ppa_wip - this is not the sort of change I would recommend. Assuming my points above are true, then I would suggest you handle the records where    chargeable=1 through OOTB (out-of-the-box) features. This should be transitional. That is, hopefully once you have set everything to non-charegable you should only be left with legacy records to deal with and "is it worth the risk of using unsupported sql on financial tables?".  So, I would advise:  (If you can)  get a copy of prod. into a test environment. Test that changing Charge Codes to non-chargeable does create new transactions that  flow as uncharegebale through ppa_transcontrol and into ppa_wip and  that such changes don't affect rating. If this is verified you should be good to go in production.  You may also create a support case of course.

 My understanding is that you can change project financial type and this field does not become read-only unless configured so in Studio. When you change a project financial type the effect is going to be on future transactions. The records in WIP still keep the type when the transactions were generated.  The same project can have records with types (can't remember the actual column name) 'S', and 'I', and others as well.  A non-chargeable transaction would still be in WIP, you can bring up a transaction in Create WIP Adjustment and make it chargeable/non-chargeable. The data on this page is pulled from ppa_wip and ppa_wip_values.  I don't have the system up in front of me but I don't recall a charge code being chargeable/non-chargeable, will have to check..  I totally agree with the suggestion of testing before doing anything (from the UI) in production.

CF,  Iam not able to so that (modifying project type) in studio(perhaps above in 8.x) if not pls advice me.For testing to me it will take some time to me as i need to test it throughly.Financial Management is some thing Little difficult to understand in clarity(For me??).  regards,sundar

Hi Sundar,Are you trying to change project financial type in 7.5.3? In 7.5.3 this field does become read-only once actuals have been processed finanically. My bad.  In r12 you will be able to change the type even after financial actuals are posted.  

sundar wrote:  If you are not using Billing then for "New" Projects you can select type = 'Internal' and so you don't have do any wip adjustments.   Are there any other affects of creating projects as type = 'Internal'?   Most of ours are created as 'Standard' now.   Does anyone know if the same fields & functionality will be present with this type there just won't be the need to adjust WIP transactions?   sundar wrote:
But for resources(Resource Type=labour) who input via timesheets,modifying the typecodes to Non-Chg,will there be any cost for the transactions enterd for the Hours Entered?.Hope then we need to provide zero in the rate matrix table for Rate&Actual & standard Cost for the Non-chargeable ones.But then totally cost will not be shown?. The vast majority of our resource transactions are labor transactions.   Can typecode charge settings be made mid-stream or should this only be set at the beginning of a project?  I tried the alternate method in the KB article Sundar referenced that had the user go into Demand Billing to somehow remove the WIP transactions.   In my TEST environment this worked but in my PROD environment the Demand Billing screen is empty.   Does anyone know what this process actually does?  Thanks,  Daniel

Danny,     Have you tried to enter the start and end date or choosed the project in the demand billing and then you are unable to view the results or you just clicked the link and  find no results.     If you have just clicked the demand billing and found no results then     Run the query or check the maximum of transaction date in the wip adjustment screen and enter the start date and end date in the Demand Billing screen     select max(transdate)trans_date from PPA_TRANSWIPADJUST     then in the Demand Billing-->In the start and end date pls put that month start and end date and filter     Regards,  sundar    

sundar wrote:
Have you tried to enter the start and end date or choosed the project in the demand billing and then you are unable to view the results or you just clicked the link and  find no results.     Hi Sundar,  Yes, I have searched the Demand Billing page by project and by date.   sundar wrote:
Run the query or check the maximum of transaction date in the wip adjustment screen and enter the start date and end date in the Demand Billing screen     select max(transdate)trans_date from PPA_TRANSWIPADJUST     This query returns 'null'.   However, when I go into "create wip adjustment" screen, I can locate the wip transactions for the project I'm trying to close.   I also still receive the message, "REVMGR-20058:Unable to close project, because some transactions in WIP have amount remaining greater than 0." when I try to change the financial status of the project from 'Hold' to 'Close'.   This project was created of type 'Standard' and, as you mentioned this can not be changed.  Daniel

In fact, the demand biling page is completely empty.   When I click 'Show All' nothing appears and I receive the message, "No results found. Modify your search as needed."  Daniel

Hi danny,    In the KB Article it is mentioned that there 2 ways to handle it.Have you tried the 2 nd way.The first on eis the Billing ,pls follow the 2nd one.  Try for 1 project which you are unable to close financially  select * from ppa_wip
where
project_code = AND
transno in (select transno from ppa_wip_values where amountremaining != 0)  Then  Follow this way:-Create WIP Adjustment for the transactions for the project and mark them non-chargeable:Navigate to Work >Financial Processing > Create WIP Adjustment.Enter the From date & To Date, browse for the Project Id.Click 'Search' button.From the results, click on the Transaction Date link to open the transaction.In the Work > Financial Processing > Create WIP Adjustment > Modify transaction page , under the Transactions Data section uncheck the box 'Chargeable'.Click 'Submit' button.Then approve the WIP Adjustment from the Work > Financial Processing > Approve WIP Adjustment page.Repeat the above steps for all outstanding transactions for the projects that you want to close.    Regards,sundar  

sundar wrote:
Hi danny,    In the KB Article it is mentioned that there 2 ways to handle it.Have you tried the 2 nd way.The first on eis the Billing ,pls follow the 2nd one.  Try for 1 project which you are unable to close financially  select * from ppa_wip
where
project_code = AND
transno in (select transno from ppa_wip_values where amountremaining != 0)  Then  Follow this way:-Create WIP Adjustment for the transactions for the project and mark them non-chargeable:Navigate to Work >Financial Processing > Create WIP Adjustment.Enter the From date & To Date, browse for the Project Id.Click 'Search' button.From the results, click on the Transaction Date link to open the transaction.In the Work > Financial Processing > Create WIP Adjustment > Modify transaction page , under the Transactions Data section uncheck the box 'Chargeable'.Click 'Submit' button.Then approve the WIP Adjustment from the Work > Financial Processing > Approve WIP Adjustment page.Repeat the above steps for all outstanding transactions for the projects that you want to close.    Regards,sundar     Hi Sundar,  Thank you again.   This is the method we were trying to replace.   Creating & approving WIP adjustments like this is a very manual and time-consuming process.   Some projects have hundreds of pages of transactions to adjust and then approve.   This takes hours to complete.   We were hoping for a better way.  Daniel

Danny,     You can also cross check this one for the specific project and follow the steps mentioned in the KB article.     select *   from ppa_billings where project_code =''      regards,  sundar

sundar wrote:
select *   from ppa_billings where project_code =''  This query yields no results when I plug in the project code.   It appears WIP adjust/approval may be the only way to clear these transactions so that the projects can be financially closed.  Daniel

Danny,  There is an  un authorized way (DB Customization)  to do that but i don't know whether it  will cause  any Impact.Pls validate from CA.Since you are not using Billing you can ask CA support."Don't try ever in Pdn.".    An DB update statement for current projects  UPDATE PPA_WIP_VALUES   WV SET AMOUNTREMAINING=0 WHERE WV.AMOUNTREMAINING 0 AND WV.TRANSNO IN (SELECT TRANSNO FROM PPA_WIP WHERE STATUS=0
AND PROJECTTYPE='S' AND PROJECT_CODE = :NEW.PROJECT_CODE  And For future projects which are other than project type  Internal create an trigger...  CREATE OR REPLACE TRIGGER WIP_AMT_ZERO
BEFORE UPDATE
ON PAC_MNT_PROJECTS
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
WHEN (
NEW. STATUS='C'
          )
BEGINUPDATE PPA_WIP_VALUES   WV SET AMOUNTREMAINING=0 WHERE WV.AMOUNTREMAINING 0 AND WV.TRANSNO IN (SELECT TRANSNO FROM PPA_WIP WHERE STATUS=0
AND PROJECTTYPE 'I' AND PROJECT_CODE = :NEW.PROJECT_CODE);EXCEPTION
    WHEN NO_DATA_FOUND THEN NULL;END WIP_AMT_ZERO;
/REGARDS,SUNDAR  

Danny,  OOPS missed the closing braces..  For single project:-(You said that your project type is standard)  UPDATE PPA_WIP_VALUES   WV SET AMOUNTREMAINING=0 WHERE WV.AMOUNTREMAINING 0 AND WV.TRANSNO IN (SELECT TRANSNO FROM PPA_WIP WHERE STATUS=0
AND PROJECTTYPE='S' AND PROJECT_CODE = :PROJECT_CODE);    For All projects  UPDATE PPA_WIP_VALUES   WV SET AMOUNTREMAINING=0 WHERE WV.AMOUNTREMAINING 0 AND WV.TRANSNO IN (SELECT TRANSNO FROM PPA_WIP WHERE STATUS=0
AND PROJECTTYPE 'I'   );  Regards,sundar