Clarity

Expand all | Collapse all

WIP Adjustments & Approvals Using SQL Script v14.3

  • 1.  WIP Adjustments & Approvals Using SQL Script v14.3

    Posted 04-06-2016 09:19 AM
      |   view attached

    All,

     

    I recently had a request to create WIP adjustments for several hundred transactions due to mismatches between the resource's stated and posted rates. In looking at this, I saw that there are two delivered stored procedures that handle the creation of WIP adjustments (REV_PRC_ADJWIPMODXFERXACTION)  and approving the adjustments ([SP_ADJWIPAPPROVELINEITEM). I was able to use these in a script that would perform all the needed adjustments based on a SQL cursor that retrieved the invalid transactions that I needed to update. I wanted to share this with the community because this definitely reduced the pain of doing all the adjustments manually.

     

    Hope this can help!

    Attachment(s)



  • 2.  Re: WIP Adjustments & Approvals Using SQL Script v14.3

    Former Employee
    Posted 04-08-2016 04:32 PM

    Thanks for sharing this tip with the community Michael!

    Michael Lee wrote:

     

    All,

     

    I recently had a request to create WIP adjustments for several hundred transactions due to mismatches between the resource's stated and posted rates. In looking at this, I saw that there are two delivered stored procedures that handle the creation of WIP adjustments (REV_PRC_ADJWIPMODXFERXACTION) and approving the adjustments ([SP_ADJWIPAPPROVELINEITEM). I was able to use these in a script that would perform all the needed adjustments based on a SQL cursor that retrieved the invalid transactions that I needed to update. I wanted to share this with the community because this definitely reduced the pain of doing all the adjustments manually.

     

    Hope this can help!



  • 3.  Re: WIP Adjustments & Approvals Using SQL Script v14.3

    Posted 07-17-2017 12:13 PM

    Thanks Michael for sharing this. I have a similar scenario where a set of projects were aligned to a wrong Rate Matrix. I need to change the Rate matrix and do the WIP adjustment. Since the transactions are large in volume, manual adjustment from UI is not feasible so I am trying the script shared by you. I have updated the script to support the oracle database.

    But I am getting the below error -

    Ora-20240:Ora-20240:Ora-20240:Ora-20240: A Rate Matrix could not be found, or Rate Matrix found was unable to match the transaction data.

    Ora:06512 - at "schema_name.REV_PRC_ADJWIPMODXFERXACTION", at line 456...

    I am calling the procedure REV_PRC_ADJWIPMODXFERXACTION

    REV_PRC_ADJWIPMODXFERXACTION(

    V_TRANSNO,

    NULL,

    NULL,

    NULL,

    NULL,

     

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    NULL,

    'M',

    NULL,

    1,

    dWIPTransDate,

    cWIPClntCode,

    cWIPProjId,

    cWIPCharge_Code,

    cWIPResource_Code,

    cWIPInputType,

    cWIPTransactionClass,

    iTransferStatus,

    iAdjustXactionNo,

    iAdjustNumber,

    cOutErrorMessage,

    'en',

    NULL,

    NULL,

    NULL,

    0,

    NULL,

    NULL,

    NULL,

    NULL

    )

     

    Any thought on this ?