Oracle EBS : Costing : Average Costing of Item

Average Costing of item stopes....

Error Code is like:
CST_MATCH_DATE_PERIOD
No Error
Etc...

Solution:
Step 1:
--get details how much lines having costed flag null

Select b.TRANSACTION_TYPE_NAME,a.costed_flag,a.error_code,
a.ERROR_EXPLANATION,a.*
from Mtl_material_transactions a,MTL_TRANSACTION_TYPES b
where ERROR_CODE is not null
and a.TRANSACTION_TYPE_ID=b.TRANSACTION_TYPE_ID

Step 2:
--finding error transaaction line

Select * from Mtl_material_transactions
where costed_flag = 'E'

Step 3:
--checking how much line not costed 

select count(*)
from mtl_material_transactions
where costed_flag = 'N';  --get count as 2013910

Step 4:
--updating error flag

update mtl_material_transactions
set costed_flag = 'N',
error_code = NULL,
error_explanation = NULL,
transaction_group_id = NULL,
transaction_set_id = NULL
--where costed_flag = 'E'
where transaction_id=49186079

Step 5:
runing cost manager

Step 6:
--check cost line count dcreaing

select count(*)
from mtl_material_transactions
where costed_flag = 'N';

If line count decreasing stops then just once again follow the
 steps 1 to step 6.Still problem is not resolve then check
 account_period_id and acc_distribution_id column 

from table Mtl_material_transactions
if these columns are blank then update with respective values.

Account period id should be find as:

select * from ORG_ACCT_PERIODS

after updation again follow step 1 to Step 6.

As count reach to zero means your costing process is completed.

Oracle Purchasing – Troubleshooting : PO to GL Debug

PO to GL reference debug………

– To debug for a PO , where should I start.
–Thats is possible, your PO get stuck somewhere, so what you have to do
–is to analyze which stage it stucked.Get po_header_id first and run each
– query and then analyze the data.For better understanding this is

–List all open–Stage 1: PO Creation :
–PO_HEADERS_ALL
select po_header_id from po_headers_all where segment1 =;
select * from po_headers_all where po_header_id =;
po_lines_all
select * from po_lines_all where po_header_id =;
po_line_locations_all
select * from po_line_locations_all where po_header_id =;
po_distributions_all
select * from po_distributions_all where po_header_id =;
po_releases_all
SELECT * FROM po_releases_all WHERE po_header_id =;

–Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

–RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in(select
shipment_header_id from rcv_shipment_lineswhere po_header_id =);

–RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =;

–RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =;

–RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN(select
transaction_id from rcv_transactionswhere po_header_id =);

–RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in
(select transaction_id from rcv_transactions where po_header_id =);

–RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_detailswhere rcv_transaction_id in (select
transaction_id from rcv_transactions where po_header_id =);

–MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =;

–MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select
transaction_id from mtl_material_transactions where
transaction_source_id = =);

–Stage 3: Invoicing details

–AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in (
select po_distribution_id from po_distributions_all where po_header_id
=);

–AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in(select invoice_id from
ap_invoice_distributions_all where po_distribution_id in( select
po_distribution_id from po_distributions_all where po_header_id =));

–Stage 4 : Many Time there is tie up with Project related PO

–PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where
peia.orig_transaction_reference in( select to_char(transaction_id) from
mtl_material_transactionswhere transaction_source_id = );

–Stage 5 : General Ledger

–Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN (”);

–GL_INTERFACE

SELECT *FROM GL_INTERFACE GLIWHERE user_je_source_name =’Purchasing’AND
gl_sl_link_table =’RSL’AND reference21=’PO’AND EXISTS( SELECT 1FROM
rcv_receiving_sub_ledger RRSLWHERE GLI.reference22 =RRSL.reference2AND
GLI.reference23 =RRSL.reference3AND GLI.reference24 =RRSL.reference4AND
RRSL.rcv_transaction_id in(select transaction_id from
rcv_transactionswhere po_header_id ));

–GL_IMPORT_REFERENCES

SELECT *FROM gl_import_references GLIRWHERE reference_1=’PO’AND
gl_sl_link_table =’RSL’AND EXISTS( SELECT 1FROM rcv_receiving_sub_ledger
RRSLWHERE GLIR.reference_2 =RRSL.reference2AND GLIR.reference_3
=RRSL.reference3AND GLIR.reference_4 =RRSL.reference4AND
RRSL.rcv_transaction_id in(select transaction_id from rcv_transactions
where po_header_id =))

SELECT h.segment1 “PO NUM”, h.authorization_status “STATUS”,
l.line_num “SEQ NUM”, ll.line_location_id, d.po_distribution_id,
h.type_lookup_code “TYPE”
FROM po.po_headers_all h,
po.po_lines_all l,
po.po_line_locations_all ll,
po.po_distributions_all d
WHERE h.po_header_id = l.po_header_id
AND ll.po_line_id = l.po_line_id
AND ll.line_location_id = d.line_location_id
AND h.closed_date IS NULL
AND h.type_lookup_code NOT IN (‘QUOTATION’)