Costed Flag in PS_TRANSACTION_INV table

COSTED_FLAG = Y or N

If you are adding a row PS_TRANSACTION_INV, you have to decide if you want the row to be taken into account by the costing process by setting the field COSTED_FLAG either to Y or to N. If you set it to Y, the costing process will skip the row.


HAPPY LEARNING :)

Inventory Issues and Resolution

Completions Pending Putaway =

1) Difference between the quantity (field COMPLETED_QTY) in PS_SF_OUTPUT_LIST for an Item by Production ID where MG_OUTPUT_TYPE = CP (Primary Item) or CS (Secondary Co-Product) and the sum of the quantities (field QTY_BASE) in PS_TRANSACTION_INV for the Item by Production ID for the Transaction Groups 021 (Receipts from production) and 221 (Route to Production Kit).

+

2) Quantity (field COMPLETED_QTY) in PS_SF_OUTPUT_LIST for the Item by Production ID if the Production ID's are not found in PS_TRANSACTION_INV for the Transaction Groups 021 (Receipts from production) and 221 (Route to Production Kit).


Part 1 of the above formula is done by running a query against the View PS_CM_NP_PID_VW and part 2 against the View PS_CM_NP_PID2_VW. So in order to check where the problem is, just run the following two queries:


SELECT COUNT(*) FROM PS_CM_NP_PID_VW WHERE BUSINESS_UNIT = 'Your Business Unit' AND CM_BOOK = 'Your Cost Book';

SELECT COUNT(*) FROM PS_CM_NP_PID2_VW WHERE BUSINESS_UNIT = 'Your Business Unit' AND CM_BOOK = 'Your Cost Book';

Usually the issues lies in the PS_TRANSACTION_INV that has not been updated properly and this is the second query that returns a count <> 0. Note that if a negative completion following by a positive completion was recorded for the PID in an attempt to solve the issue, this is the first query that returns a count <> 0 and not the second one.



Most of the times, once you are sure that there's no staging error and no staging ID waiting to be processed for your PID, the best way to solve the issue is just to update PS_TRANSACTION_INV with the missing information. You might have to add the missing Putaway with a Transaction Groups 021 into PS_TRANSACTION_INV or update an existing Putaway that was created manually into Inventory. Here is the list of fields that must be updated (there might be others depending on how you are using the system):


BUSINESS_UNIT
INV_ITEM_ID
DT_TIMESTAMP
SEQ_NBR = 1
RECEIVER_ID = ALL
RECV_LN_NBR = 999
TRANSACTION_GROUP = 021
QTY_BASE
QTY
UNIT_MEASURE_STD
UNIT_OF_MEASURE
COSTED_FLAG = Y or N
PRODUCTION_ID
MA_PRDN_ID_FLG = Y
PRDN_AREA_CODE
PRODUCTION_TYPE = PR
STAGED_TYPE = 1
TRANSACTION_SOURCE = WL
OP_SEQUENCE

If you are adding a row PS_TRANSACTION_INV, you have to decide if you want the row to be taken into account by the costing process by setting the field COSTED_FLAG either to Y or to N. If you set it to Y, the costing process will skip the row.

Once the missing row has been added into PS_TRANSACTION_INV or the existing manual Putaway modified, check the Pending Transactions page to make sure that Completion Pending Putaway = 0. Run then the costing process and check the results to make sure everything is fine.



=== QUERY VIEW INFORMATION ===

*** Query view CM_NP_PID_VW:


SELECT A.BUSINESS_UNIT
, D.CM_BOOK
, A.PRODUCTION_ID
, A.MG_OUTPUT_ITEM
, (A.COMPLETED_QTY - B.COMPLETED_QTY)
, %Substring(C.MESSAGE_TEXT, 1, 50)
FROM PS_CE_OUTPUT2_VW A
, PS_CE_PID_CMP_VW B
, PSMSGCATDEFN C
, PS_CM_BU_BOOK_TBL D
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND A.PRODUCTION_ID = B.PRODUCTION_ID
AND A.MG_OUTPUT_ITEM = B.INV_ITEM_ID
AND (B.COMPLETED_QTY - A.COMPLETED_QTY) <> 0
AND C.MESSAGE_SET_NBR = 15500
AND C.MESSAGE_NBR = 177
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND D.CM_BOOK_STATUS = 'A'


*** Query view CM_NP_PID2_VW:


SELECT A.BUSINESS_UNIT
, B.CM_BOOK
, A.PRODUCTION_ID
, A.MG_OUTPUT_ITEM
, A.COMPLETED_QTY
, %Substring(C.MESSAGE_TEXT, 1, 50)
FROM PS_CE_OUTPUT2_VW A
, PS_CM_BU_BOOK_TBL B
, PSMSGCATDEFN C
WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT
AND B.CM_BOOK_STATUS = 'A'
AND A.COMPLETED_QTY > 0
AND C.MESSAGE_SET_NBR = 15500
AND C.MESSAGE_NBR = 177
AND NOT EXISTS (
SELECT 'X'
FROM PS_CE_PID_CMP_VW BB
WHERE A.BUSINESS_UNIT = BB.BUSINESS_UNIT
AND A.PRODUCTION_ID = BB.PRODUCTION_ID
AND A.MG_OUTPUT_ITEM = BB.INV_ITEM_ID)


*** Query view CE_OUTPUT2_VW:


SELECT BUSINESS_UNIT
, PRODUCTION_ID
, MG_OUTPUT_ITEM
, COMPLETED_QTY
, MG_OUTPUT_TYPE
FROM PS_SF_OUTPUT_LIST
WHERE MG_OUTPUT_TYPE IN ('CP','CS')


*** Query view CE_PID_CMP_VW:


SELECT BUSINESS_UNIT
, PRODUCTION_ID
, INV_ITEM_ID
, SUM(QTY_BASE)
FROM PS_TRANSACTION_INV
WHERE PRODUCTION_ID <> ' '
AND TRANSACTION_GROUP IN ('021','221')
GROUP BY BUSINESS_UNIT, PRODUCTION_ID , INV_ITEM_ID


=====================

The 'Insufficient Qty Cost Option' is there for you to decide if you want to cost the negative Depletions or to wait until a new Putaway is received.

If you decide to cost the negative Depletions, then checking the 'Resolve Negative Inventory' check box on the Run Control page of the CM_CSTACCTG makes the 'Resolver' run. The 'Resolver' looks for any new Putaway to re-cost the negative Depletions based on the new Cost received on the new Putaway. If you do not select the 'Resolve Negative Inventory' check box, the 'Resolver' will not run.


Example in case you set the 'Insufficient Qty Cost Option' to 'Always cost insufficient qty':

Assume there are 10 units of an Item available in the Inventory Business Unit and a demand line is shipped and depleted for 15 units, then the entire depletion transaction of the 15 units is costed the next time the CM_CSTACCTG process is run, even if this is before sufficient stock is placed in the Business Unit. Of the 15 units, 10 are costed using the existing stock on hand, and 5 are costed using the current Average Cost, current Standard Cost, or last Actual Cost depending on the Cost Method employed.

Assume now you have received the 5 units. Next time you run the CM_CSTACCTG process with the 'Resolve Negative Inventory' check box selected, the 'Resolver' runs and will do the following:

Applies the new Receipt quantities to the previously recorded negative inventory Depletion transactions. The 'Resolver' matches the negative inventory Depletion transactions to the actual Received stock.

Creates Adjustment Accounting entries for the Item using the Deplete Cost Method of Actual Cost. The 'Resolver' creates an entry to reverse the cost previously recorded for the negative inventory Depletion transactions and to record new transactions at the Actual Receipt Cost. The following Accounting Lines are created:

* A reversing entry to remove the Depletion based on the last Actual Cost received before the negative state,
* An entry to record the Depletion based on the Actual Receipt Costs.

For Items using the Deplete Cost Method at Current Standard, Perpetual Weighted Average, or Retroactive Perpetual Weighted Average, the 'Resolver' does not change the previously recorded cost for the negative inventory Depletion transaction. The Standard or Average Cost at the time of the Depletion is used on the original Depletion and is not changed when the 'Resolver' corrects the negative state by matching the negative inventory Depletion transactions to the actual received stock.

Inserts the resolved negative inventory Depletion transactions into a history table called PS_CM_RESOLVE_HIST.

Note:

Running the 'Resolver' is optional. It is highly recommended that you use this process to resolve all negative Depletion transactions so that reports and queries do not reflect negative states that have been resolved. The 'Resolver' does not impact the total on hand quantity. For costs, it only impacts the total inventory value for Actual Cost Items.

Two inquiries are available to monitor the costing of negative inventory Depletion transactions and the recalculations of the quantities and costs performed by the 'Resolver'. These queries are called CM_PENDING_RESOLVER and CM_RESOLVED_NEG_HIST and can be viewed using the Query Viewer.

The CM_PENDING_RESOLVER query returns negative inventory Depletion transactions that have not been resolved.
The CM_RESOLVED_NEG_HIST query returns negative inventory Depletion transactions that have been resolved and recorded in the history table PS_ CM_RESOLVE_HIST.

================

1) Why aren't my rows costing?

Follow the below step when you find out that some inventory transactions are not being costed by the CM_COSTING or the CM_CSTACCTG process:

Check the Message Log of your Process Instance in the Process Monitor.

Query the PS_TRANSACTION_INV.COSTED_FLAG field for the inventory transactions you want to check.

If the value is 'X' it means that the inventory transaction doesn't need to be processed, for example because the Item is a non-costed or a non-owned Item.

If the value is 'N',  it means that the inventory transaction has not been processed yet or the process is not able to process it at this time (see the Top Reasons section below).

If the value is 'Y', it means that the inventory transaction has been processed.


2) Top Reasons why inventory transactions are not being processed

Inventory transaction rows expanded for Books but not costed:

Possible reasons:
Costing hasn't been run for this Inventory Business Unit and Book
SELECT * FROM PS_CM_TRANSACTION WHERE POSTED_FLAG = 'N'


Cost Adjustments not processed:

Possible reasons:
(a) The ''Apply Perpetual Average Adjs' check box has not been selected on the Run Control page.
(b) The Payables Matching process has been run for Voucher(s) before the corresponding Receipt(s) have been putaway.
SELECT * FROM PS_CM_MATCHED_COST WHERE POSTED_FLAG = 'N'
SELECT * FROM PS_CM_COST_ADJ WHERE POSTED_FLAG = 'N'
SELECT * FROM PS_CM_ACTUAL_BOOK WHERE POSTED_FLAG = 'N'


Receipts not costed:

Possible reasons:
(a) There is no cost for the Item.
(b) The process is waiting for the Voucher Cost from Payables or waiting for user to run the process in 'Regular Mode' for period end.
SELECT * FROM PS_CM_RECEIPTS WHERE POSTED_FLAG = 'N'


Depletion rows not depleted:

Possible reasons:
(a) Not enough qty on-hand to satisfy the depletion (see MESSAGE_SET_NBR & MESSAGE_NBR on the CM_DEPLETION rows).
(b) might be explainable if the negative inventory is allowed for the Inventory Business Unit 
SELECT * FROM PS_CM_DEPLETION WHERE POSTED_FLAG = 'N'


Depletion rows not costed:

Possible reasons:
(a) There is no cost for the Item.
(b) The process is waiting for the Voucher Cost from Payables or waiting for user to run the process in 'Regular Mode' for period end.
SELECT * FROM PS_CM_DEPLETE WHERE POSTED_FLAG = 'N'


3) SQL statements you can use to help you resolve missing cost issues



Missing Receipt Costs (Actual and Average Costs):

This query helps identifying rows from the PS_CM_RECEIPTS table which are not getting processed due to a missing Cost or Actual Cost rows without an Actual Cost.
SELECT * FROM PS_CM_RECEIPTS, PS_CM_ITEM_METH_VW
WHERE NOT EXISTS (SELECT 'X' FROM PS_CM_ACTUAL_COST
WHERE PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_ACTUAL_COST.BUSINESS_UNIT
AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_ACTUAL_COST.INV_ITEM_ID
AND PS_CM_RECEIPTS.CM_DT_TIMESTAMP_A = PS_CM_ACTUAL_COST.DT_TIMESTAMP
AND PS_CM_RECEIPTS.CM_SEQ_NBR_A = PS_CM_ACTUAL_COST.SEQ_NBR)
AND PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_ITEM_METH_VW.BUSINESS_UNIT
AND PS_CM_RECEIPTS.CM_BOOK = PS_CM_ITEM_METH_VW.CM_BOOK
AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_ITEM_METH_VW.INV_ITEM_ID
AND PS_CM_RECEIPTS.POSTED_FLAG = 'N'
AND PS_CM_ITEM_METH_VW.CM_TYPE IN ('1')


Missing Receipt Costs (Standard Costs):

This query helps identifying rows from the PS_CM_RECEIPTS table which are not getting processed due to a missing Cost or Standard Cost rows without a Standard Cost (not accurate for Configured Items).
SELECT * FROM PS_CM_RECEIPTS, PS_CM_ITEM_METH_VW
WHERE NOT EXISTS (SELECT 'X' FROM PS_CM_STDCOSTD_VW
WHERE PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_STDCOSTD_VW.BUSINESS_UNIT
AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_STDCOSTD_VW.INV_ITEM_ID)
AND PS_CM_RECEIPTS.BUSINESS_UNIT = PS_CM_ITEM_METH_VW.BUSINESS_UNIT
AND PS_CM_RECEIPTS.CM_BOOK = PS_CM_ITEM_METH_VW.CM_BOOK
AND PS_CM_RECEIPTS.INV_ITEM_ID = PS_CM_ITEM_METH_VW.INV_ITEM_ID
AND PS_CM_RECEIPTS.POSTED_FLAG = 'N'
AND PS_CM_ITEM_METH_VW.CM_TYPE IN ('2')


Missing Deplete Costs (Actual Costs):

This query helps identifying rows from the PS_CM_DEPLETE table which are not getting processed due to a missing Cost or Actual Cost rows without an Actual Cost.
SELECT * FROM PS_CM_DEPLETE, PS_CM_ITEM_METH_VW
WHERE NOT EXISTS (SELECT 'X' FROM PS_CM_ACTUAL_COST
WHERE PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_ACTUAL_COST.BUSINESS_UNIT
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_ACTUAL_COST.INV_ITEM_ID
AND PS_CM_DEPLETE.CM_DT_TIMESTAMP_A = PS_CM_ACTUAL_COST.DT_TIMESTAMP
AND PS_CM_DEPLETE.CM_SEQ_NBR_A = PS_CM_ACTUAL_COST.SEQ_NBR)
AND PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_ITEM_METH_VW.BUSINESS_UNIT
AND PS_CM_DEPLETE.CM_BOOK = PS_CM_ITEM_METH_VW.CM_BOOK
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_ITEM_METH_VW.INV_ITEM_ID
AND PS_CM_DEPLETE.POSTED_FLAG = 'N'
AND PS_CM_ITEM_METH_VW.CM_METHOD IN ('1')


Missing Deplete Costs (Standard Cost):

This query helps identifying rows from the PS_CM_DEPLETE table which are not getting processed due to a missing Cost or Standard Cost rows without a Standard Cost (not accurate for Configured Items).
SELECT * FROM PS_CM_DEPLETE, PS_CM_ITEM_METH_VW
WHERE NOT EXISTS (SELECT 'X' FROM PS_CM_STDCOSTD_VW
WHERE PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_STDCOSTD_VW.BUSINESS_UNIT
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_STDCOSTD_VW.INV_ITEM_ID)
AND PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_ITEM_METH_VW.BUSINESS_UNIT
AND PS_CM_DEPLETE.CM_BOOK = PS_CM_ITEM_METH_VW.CM_BOOK
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_ITEM_METH_VW.INV_ITEM_ID
AND PS_CM_DEPLETE.POSTED_FLAG = 'N'
AND PS_CM_ITEM_METH_VW.CM_METHOD IN ('8')


Missing Deplete Costs (Average Costs):

This query helps identifying rows from the PS_CM_DEPLETE table which are not getting processed due to a missing Cost or Perpetual Average Cost row without a Cost.
SELECT * FROM PS_CM_DEPLETE, PS_CM_ITEM_METH_VW
WHERE NOT EXISTS (SELECT 'X' FROM PS_CM_PERPAVG_COST
WHERE PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_PERPAVG_COST.BUSINESS_UNIT
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_PERPAVG_COST.INV_ITEM_ID
AND PS_CM_DEPLETE.CM_DT_TIMESTAMP_A = PS_CM_PERPAVG_COST.DT_TIMESTAMP
AND PS_CM_DEPLETE.CM_SEQ_NBR_A = PS_CM_PERPAVG_COST.SEQ_NBR)
AND PS_CM_DEPLETE.BUSINESS_UNIT = PS_CM_ITEM_METH_VW.BUSINESS_UNIT
AND PS_CM_DEPLETE.CM_BOOK = PS_CM_ITEM_METH_VW.CM_BOOK
AND PS_CM_DEPLETE.INV_ITEM_ID = PS_CM_ITEM_METH_VW.INV_ITEM_ID
AND PS_CM_DEPLETE.POSTED_FLAG = 'N'
AND PS_CM_ITEM_METH_VW.CM_TYPE IN ('6').


HAPPY LEARNING :)

Insert rows using PeopleCode

When inserting rows using PeopleCode, you can either use the Insert method with a record object or create a SQL Insert statement using the SQL object. If you do a single insert, use the Record Insert method. If you are in a loop and,therefore, calling the insert more than once, use the SQL object. The SQL object uses dedicated cursors and, if the database you are working with supports it, bulk insert.

A dedicated cursor means that the SQL gets compiled only once on the database, so PeopleTools looks for the meta-SQL only once. This can increase performance.

For bulk insert, inserted rows are buffered and sent to the database server only when the buffer is full or a commit occurs. This reduces the number of round-trips to the database. Again, this can increase performance.

⇒ The following is an example of using the Record Insert method:

&REC = CreateRecord(Record.GREG); 
&REC.DESCR.Value = "Y";
&REC.EMPLID.Value = "12180014"; 
&REC.Insert();

⇒ Insert()

The Insert method uses the field names of the record and their values to build and execute an Insert SQL statement which adds the given record (row of data) to the SQL table.Because this method results in a database change, it can only be issued in the following events:



SavePreChange


WorkFlow


SavePostChange



Example:

Local record &REC;
&REC = CreateRecord(RECORD.MYRECORD);
&REC.KEYF1.Value = "A";
&REC.KEYF2.Value = "B";
&REC.MYRF3.Value = "X";
&REC.MYRF4.Value = "Y";
&REC.Insert();

⇒The following is an example using a SQL object to insert rows:

&SQL = CreateSQL("%INSERT(:1)"); 
&REC = CreateRecord(Record.GREG); 
&SQL.BulkMode = True; 
For &I = 1 to 10 
   &REC.DESCR.Value = "Y" | &I; 
   &REC.EMPLID.Value = &I; 
   &SQL.Execute(&REC); 
End-For;

HAPPY LEARNING:)

Creating Left Outer Joins in PSQuery

To create a left outer join on a new query:

1. On the Query Manager search page (Reporting Tools, Query, Query Manager), click the Create New Query link.
   
2. On the Records page, search for the first (left) record for the left outer join.
   
3. Click the Add Record link on the same row as the record that you want to add.
   
   The Query page appears.
   
   
4. Select the appropriate fields to add to the query.
   
5. Navigate to the Records page.
   
6. Find the joining record and then click the Join Record link on the same row as that record.
   
7. Select Join to get additional fields only (Left outer join).
8. Complete the join.
   
   • If the Enable Auto Join preference is selected on the Query Preference page, click the record name to join with.
     
   • If the Enable Auto Join preference is not selected on the Query Preference page, click the OK button.
     
9. Define the join criteria.
   
   • If the Enable Auto Join preference is selected, the Auto Join Criteria page appears.
     You can clear the criteria from the query. You can also click the Add Criteria button to add or edit criteria or conditions. If no common keys are between the two join records, a message appears instead of the Add Criteria page.
     
   • If the Enable Auto Join preference is not selected, the Auto Join Criteria page does not appear.
     If you want, navigate to the Criteria page to add criteria to the ON clause of the outer join. The This Criteria Belongs To drop-down list box specifies where the criteria will appear. Select the alias that corresponds to this join record.
     
10. Define the effective date criteria.
    
    • For 2–3 tier client: If the joined record is an effective-dated record, the Effective Date Criteria page appears.
      You can accept the defaults or change them as desired.
      
    • For 4–tier PeopleSoft Pure Internet Architecture: If the joined record is an effective dated record, a message appears stating that an effective date criteria has been automatically added.
      Optionally, you can navigate to the Criteria page to change the defaults for this criteria.
      
11. To ensure that the left outer join finished successfully, navigate to the Query page.
    Text for the joined records confirms a successful left outer join.
    
12. (Optional) If the query has multiple joins to the same security record, which can be resource intensive and time consuming when it runs, select the Security Join Optimizer option to improve the performance of this query.
    
    
    HAPPY LEARNING :)