Checked for relevance 16-APR-2010

PURPOSE
-------

To assist in defining problems with Work In Progress(WIP) Move Transactions.

 
SCOPE & APPLICATION
-------------------

This is usefull for all System Administrators and DBAs.


Overview of WIP_PERIOD_BALANCES and some useful queries.
-------------------------------------------------------

The creation and deletion of entries in the WIP_PERIOD_BALANCES is governed by
the following rules:

A record is deleted from WIP_PERIOD_BALANCES when

  1. The job status is changed to unreleased.

  2. The period is closed and the job close date is <= accounting
     period start date.

A record is inserted into WIP_PERIOD_BALANCES when

  1. The job status is changed to released.

  2. The period is opened and the job status is one of the following:

      - Released - charges allowed (3)

      - Complete - charges allowed (4)

      - Complete - no charges allowed (5) job only

      - Hold - no charges allowed (6)

      - canceled - no charge allowed (7) job only

      - Pending close (14) job only

      - Failed close (15) job only

When trying to resolve errors with Move Transactions the following scripts can
be applied:

1)  Run the following using the transaction ID from your errored Move
    Transaction:

SELECT ORGANIZATION_ID, WIP_ENTITY_ID, TRANSACTION_DATE, ACCT_PERIOD_ID
FROM WIP_MOVE_TXN_INTERFACE
WHERE TRANSACTION_ID = &transaction_id
old   3: WHERE TRANSACTION_ID = &transaction_id
new   3: WHERE TRANSACTION_ID = 

ORGANIZATION_ID WIP_ENTITY_ID TRANSACTI ACCT_PERIOD_ID
--------------- ------------- --------- --------------

2)  Using the results from above we can now look to see if there is a 
    corresponding balance for this transaction in the WIP_PERIOD_BALANCES:

SELECT 'Record exists in WIP_PERIOD_BALANCES'
FROM WIP_PERIOD_BALANCES WPB
WHERE WPB.ACCT_PERIOD_ID = &acct_period_id
AND WPB.WIP_ENTITY_ID = &wip_entity_id
AND WPB.ORGANIZATION_ID = &organization_id
Enter value for acct_period_id: 
old   3: WHERE WPB.ACCT_PERIOD_ID = &acct_period_id
new   3: WHERE WPB.ACCT_PERIOD_ID = 
Enter value for wip_entity_id: 
old   4: AND WPB.WIP_ENTITY_ID = &wip_entity_id
new   4: AND WPB.WIP_ENTITY_ID = 
Enter value for organization_id: 
old   5: AND WPB.ORGANIZATION_ID = &organization_id
new   5: AND WPB.ORGANIZATION_ID = 

If no rows are returned there is no entry for the job in the WIP_PERIOD_BALANCES
The entries in the table will need to be regenerated via a script.

RELATED DOCUMENTS
-----------------

Note:95389.1