CSTPACIN.COST_INV_TXN:CSTPAVCP.average_cost_update (40)

Costing error Message : CSTPACIN.COST_INV_TXN:CSTPAVCP.average_cost_update (40): ORA-01400: cannot insert NULL into ("BOM"."CST_LAYER_COST_DETAILS"."ITEM_COST")

Symptoms
 1. Actual Cost Worker Completed warning.
2. Cost manager not initiate the Actual Cost Worker concurrent for related Inventory organization
3. mtl_material_transactions > Costed_flag displayed as 'E'
4. mtl_material_transactions > Error_Code = No error
5. mtl_material_transactions >ERROR_EXPLANATION = CSTPACIN.COST_INV_TXN:CSTPAVCP.average_cost_update (40): ORA-01400: cannot insert NULL into ("BOM"."CST_LAYER_COST_DETAILS"."ITEM_COST")
 
Solution

1. To Find the Problematic record in Mtl_material_transactions Table
select * from mtl_material_transactions where costed_flag = 'E'

2. To find the item code = Segment1
select * from mtl_system_items where inventory_item_id = 787838

3. To find to check the New_average_cost field is null
select * from mtl_cst_txn_cost_details where transaction_id = 37480170

4. Check the Average cost of the item code : Navigation > Cost management - SLA > Item costs > Item costs >Find the Item Code > "Unit cost" field value


To Update the Average cost




update mtl_cst_txn_cost_details
set new_average_cost ='4.31648' --Enter average cost
where transaction_id in
( Select transaction_id from mtl_material_transactions
where costed_flag = 'E'
and transaction_id = 37480170 -- Enter Transaction_id
--and TRANSACTION_ACTION_ID=24 and
--TRANSACTION_SOURCE_TYPE_ID=13)
and new_average_cost is null
and value_change is null
and percentage_change is null)

To update the Costed_flag as 'N'


update mtl_material_transactions
set costed_flag='N',
transaction_group_id=NULL,
error_code=NULL,
error_explanation=NULL
where costed_flag='E'

Oracle Payable - Period Close Exceptional Report Error :The entered amount and accounted amount for line 10 are not on the same side.

Period Close Exceptional Report Error :The entered amount and accounted amount for line 10 are not on the same side.

Payable  

Period Close Exceptional Report - Invoice  - Create Accounting Issues 

Line	Error Number	Error Message
xx	95314	The entered amount and accounted amount for line 10 are not on the same side. Please inform your system administrator or support representative that: The source assigned to the accounting attribute entered amount is not consistent with the source assigned to the accounting attribute accounted amount or the supplied conversion rate is invalid.
xx	953Invoice  - Create Accounting Issues 14	The entered amount and accounted amount for line 11 are not on the same side. Please inform your system administrator or support representative that: The source assigned to the accounting attribute entered amount is not consistent with the source assigned to the accounting attribute accounted amount or the supplied conversion rate is invalid.

Solution :

Update : Query

UPDATE ap_invoice_distributions_all
SET description = description||'_'||invoice_line_number||'_'||
distribution_line_number
WHERE invoice_distribution_id IN (
SELECT invoice_distribution_id
FROM ap_invoice_distributions_all
WHERE invoice_id = 939098)

Commit

And Run the Create accounting against the Invoice or Concurrent.

AR Sub Ledger Period Close Exception Status shows “Unprocessed” and unable to close the AR Period

AR Sub Ledger Period Close Exception Status shows “Unprocessed” and unable to close the AR Period

Symptoms

AR Credit Memo “Status” Completed and Create Accounting completed and posted to GL. But “Sub Ledger Period Close Exception’ Report shows some records in “Unprocessed” status.

Journal Category	Credit Memos		Event Class	Credit Memo
Event Date	Event Type	Transaction Number	Status	Customer Name	Customer Number	Credit Memo Number
07-Oct-16	Credit Memo Updated	1317801453	Unprocessed	xxxxxxxxxxxxxxxxx	1044	1317801453
15-Oct-16	Credit Memo Updated	1317801488	Unprocessed	xxxxxxxxxxxxxxxxx	1042	1317801488
15-Oct-16	Credit Memo Updated	1317801489	Unprocessed	xxxxxxxxxxxxxxxxx	1042	1317801489

Solution

Undo accounting lines and Insert the new accounting lines against the customer_trx_id

Run below script which will undo account the events of the CM:
Script 1: undo_account_transaction_ra_event.sql

Undo Parameters:
org_id (Required)
customer_trx_id (Required) : Pass one transaction_id listed in query output
Bug_Number := 38981
gl_date := Enter GL_DATE of an open period>

The script will undo account the CM and will sweep the CM to the new gl_date which lies in open period.
2. Once successfully executed, issue: commit.
3. Now, run below script to create the missing distributions:
Script 2: insert_missing_cm_ra_dist_120.sql

Insert Parameters:
org_id (Required)
gl_start_date (Required)
gl_end_date (Required)
read_only_mode (Required) : Y to List the corruption and N to fix the corruption.
customer_trx_id (Required) : pass trx_id for which you did undo accounting above
Bug_Number := 38981

It will list the receivable_application_id for which the distributions are missing. Please validate it.
4. Once successfully executed, issue: commit.
5. Run Create Accounting to Post the data to GL.