Understanding WIP

WIP stands for Work In Progress, it is a table that holds the financial transactions before they go through to Billing and then onto Invoicing.
The three main tables that hold the data are  PPA_WIP,PPA_WIP_VALUES and PPA_WIPAPINFO .
PPA_WIP -->This table holds the header information for the WIP transaction, it doesn’t contain any money values, but it does contain the transaction type.
Some Important Columns are Highlighted below.

Transaction Type

    *  L. This is for Labour transactions.
    *  M. This is for Material transactions.
    *  Q. This is for Equipment transaction.
    *  X. This is for Expense transaction.

PROJECTTYPE --> This column indicates the project type.Transactions are not considered for billing for the project created of type INTERNAL.  

    *   Internal
    *   Standard
    *   Retainer
    *   Contract

SOURCEMODULE -->
 This column refers to the source module where the transaction originated from. Values are:

    * 50 = Transaction originated from CA Clarity XOG (unless otherwise specified)
    * 51 = Transaction originated from a timesheet in CA Clarity PPM.
    * 52 = Transaction originated from voucher entry of type VOUCHER_EXPENSE
    * 53 = Transaction originated from voucher entry of type (VOUCHER_OTHER)

 STATUS -->
  Indicates the current status of this transaction. Values are:

    *  0 = No adjustment, no pending approval for billing
    *  1 = Adjusted
    *  2 = Reversed
    *  4 = Updated
    *  8 = Processed by billing, approval pending 

 PPA_WIP_VALUES -->This table holds 5 records for each WIP transaction, each with a different currency type, these are

o HOME. This is the Accounts system setting for holding transactions, so if the system was in the USA, the HOME currency would be US$
o EURO. This holds the euro value for the transaction.
o BILLING. This is the currency in which billing is Done.
o NATURAL. This is the currency in which the transaction occurred.
o REPORTING. This is the reporting currency.

QUANTITY = Financial Transaction Entry quantity.This could represent hours or units.  This is the quantity entered on Timesheets or other Financial Transaction Entries.

NATURAL_STDCOST = Rate Matrix Standard Cost Value as it appears in UI
This is the Standard Cost value from the Rate Matrix Row no computations are done for this field.

STDCOST = NATURAL_STDCOST * COST_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference STDCOST_CURRENCY_CODE

NATURAL_ACTUALCOST = Rate Matrix Actual Cost Value as it appears in UI
This is the Actual Cost value from the Rate Matrix Row no computations are done for this field.

ACTUALCOST = NATURAL_ACTUALCOST * COST_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference COST_CURRENCY_CODE.

PRICE = Rate Matrix Actual Cost Value as it appears in UI
This is the Actual Cost value from the Rate Matrix Row no computations are done for this field. (Same as NATURAL_ACTUALCOST)

TOTALCOST = ACTUAL_COST * QUANTITY

NATURAL_BILLRATE = Rate Matrix Rate Value as it appears in the UI **IF no Cost Plus Code is defined on the Rate Matrix Row**

If a Cost Plus Code is defined on the Rate Matrix Row for the Project’s ‘Rate’ Matrix, then the NATURAL_BILLRATE does not use the Rate Matrix Rate Value, as described below:
NATURAL_BILLRATE = NATURAL_ACTUALCOST when a Cost Code applied to Actual Costs is defined on the Rate Matrix Row
NATURAL_BILLRATE = NATURAL_STDCOST when a Cost Code applied to Standard Costs is defined on the Rate Matrix Row

BILLRATE = NATURAL_BILLRATE * RATE_EXCHANGE_RATE
If a single currency environment is configured, the Exchange Rate = 1.00
Otherwise, the Exchange Rate is calculated from the configuration for selected Currency Code, and Exchange Rate Type.  Reference RATE_CURRENCY_CODE.

AMOUNT = BILLRATE * QUANTITY

When a Cost Plus Code is defined on the Rate Matrix Row that is used for calculating costs, the following fields are calculated, otherwise the values in these fields is zero.

FACTORAMOUNT = AMOUNT * Cost Plus Multiplier
BURDEN = AMOUNT * Cost Plus Burden
OVERHEAD = AMOUNT * Cost Plus Overhead
TOTALAMOUNT = AMOUNT + FACTORAMOUNT + BURDEN + OVERHEAD

Query for Retrieving Records from Wip.

The query below extracts a list of the transaction and adds together any which have been adjusted through timesheet adjustments etc. The query is filtered to only show the Test001 project.

select srm.unique_name,srm.name,wip.transdate,wip.transtype,sum(wip.quantity) hrs,sum(value.totalcost) cost,value.billrate
from srm_projects srm,PAC_MNT_PROJECTS pac,ppa_wip wip,ppa_wip_values value
where
wip.project_code = pac.project_code and wip.transno = value.transno
and value.currency_type= 'HOME' and PAC.project_code = 'TEST001'
and srm.id = pac.id
group by srm.unique_name,wip.transtype,srm.name,wip.transdate,value.billrate

PPA_WIPAPINFO
 
This table contains supplemental accounts payable (AP) information for transactions imported from an AP system.For Non Manday Resource Type Expense the transactions are stored in PPA_WIPAPINFO Table.GL Upload on Expense will fall in this Type.

SELECT
                        WP.PROJECT_CODE, WP.RESOURCE_CODE, UPPER(WP.EMPLYHOMELOCATION) EMPLYHOMELOCATION
                       , UPPER(WP.PROJECT_LOCATION) PROJECT_LOCATION, WP.PROJECT_DEPARTMENT, WP.WIPCLASS
                       , WP.TRANSDATE, UPPER(WP.TRANSTYPE) TRANSTYPE, UPPER(WP.TRANSCLASS) TRANSCLASS
                       , WP.CHARGE_CODE, WAI.VOUCHERNO, WAI.PONO, WAI.VENDOR_CODE, WPV.TOTALCOST
                FROM
                       PPA_WIP WP , PPA_WIP_VALUES WPV, PPA_WIPAPINFO WAI
                WHERE
                       WP.TRANSNO = WPV.TRANSNO AND WPV.CURRENCY_TYPE = 'HOME' AND WP.PPA_WIPAPINFO_ID = WAI.ID (+)
                       AND WP.TRANSDATE BETWEEN 'Start Date' AND  'End Date' AND WP.STATUS = 0
                       and WP.PROJECT_CODE = 'Project Code' and TRANSTYPE ='X'