The most challenging part is reconciling the cost and revenue in PA with other modules.Below is a flow chart showing the data flow between the modules PA, AP, PO and GL.

Data Flow

Here in this Part1 iam giving you the basic queries that will be helpful to reconcile the cost between these modules. In Part2 I will be giving the basic queries for revenue reconciliation. In Part3, i will be giving the UBR and UER reconcilation.

NOTE: These queries are just prototypes, you may have to modify it according to your accounting setup. All the queries are for a particular period since we are concerned about reconciling cost/revenue for that particular period only.

Q1: Cost interfaced from the modules PA,AP,PO,AR to GL in JUN-08.

SELECT SUM(nvl(entered_dr,0) – nvl(entered_cr,0)) amt , glcc.segment1, glcc.segment2, glh.je_source
FROM apps.gl_je_headers glh,
apps.gl_je_lines gll,
apps.gl_code_combinations glcc
WHERE glh.je_header_id = gll.je_header_id
AND gll.code_combination_id = glcc.code_combination_id
AND glcc.segment2 in (‘10903′,’10953′,’10814’) — specific accounts
AND actual_flag = ‘A’
AND summary_flag = ‘N’
AND gll.period_name in (‘JUN-08’) — Period
AND glh.je_source in (‘Project Accounting’, ‘Purchasing’, ‘Payables’, ‘Receivables’) GROUP BY glcc.segment1, glcc.segment2, glh.je_source , gll.period_name

Now we can check the total cost in projects that are interfaced to GL :

Q2: Total Cost in Projects by Transaction source and Segment1 and Segment2 for JUN-08.

You can add more segments here according to your needs.

SELECT transaction_source, glcc.segment1, glcc.segment2, SUM(cdl.burdened_cost) JUN08_Cost,
apps.pa_cost_distribution_lines_all cdl,
apps.pa_expenditure_items_all ei,
apps.pa_projects_all ppa,
apps.hr_all_organization_units hou,
apps.gl_code_combinations glcc
WHERE ppa.carrying_out_organization_id = hou.organization_id
AND ppa.project_id = cdl.project_id
AND cdl.gl_period_name = ‘JUN-08’
AND cdl.expenditure_item_id = ei.expenditure_item_id
AND cdl.dr_code_combination_id = glcc.code_combination_id
GROUP BY ei.transaction_source, glcc.segment1, glcc.segment2, ei.system_linkage_function

Now you have to tie the cost amounts from various sources such as ‘Payables’, ‘PO Receipt’ and other modules to the Cost returned in the Query1 for these sources.

The Costs from AP are directly sent to GL. The Costs from PO/Receiving are directly sent to GL.

Q3: Costs that are interfaced from AP to PA in JUN-08

NVL(sum(nvl(amount,0)),0) amt, glcc.segment1, glcc.segment2 , inv_dist.pa_addition_flag, inv_dist.je_batch_id, inv_dist.accrual_posted_flag,
inv_dist.project_id, inv_dist.expenditure_type
FROM apps.ap_invoice_distributions_all inv_dist
,apps.ap_invoices_all i
,apps.gl_code_combinations glcc
WHERE inv_dist.invoice_id = i.invoice_id
AND inv_dist.dist_code_combination_id = glcc.code_combination_id
AND glcc.segment2 in (‘10903′,’10953′,’10814’) — specific accounts
AND inv_dist.posted_flag = ‘Y’
AND inv_dist.accounting_date between ’01-JUN-08′ and ’30-JUN-08′
AND inv_dist.period_name = ‘JUN-08’
GROUP BY glcc.segment1, glcc.segment2, inv_dist.pa_addition_flag, inv_dist.je_batch_id,
inv_dist.ACCRUAL_POSTED_FLAG,inv_dist.project_id, inv_dist.expenditure_type

The above query will give you the cost that is interfaced from AP to PA for JUN08 period. The Cost in Query Q2 for the transaction source ‘Payables’ should match the cost in Q3.If these two are not matching then it might be that Cost is adjusted in Projects/Payables but posted to the other module.

Case1: Invoice from AP is adjusted in PA.

Case2: Invoice is adjusted in AP after it is interfaced to PA.

For Case1, Interface the un-interfaced Supplier invoice adjustment records in Oracle Projects.

For Case2:Interface the Adjusted AP invoice to Oracle Projects.

Then re-run the queries Q2 and Q3 and check.

Q4: Costs that are interfaced from PO to PA in JUN-08

NVL(sum(nvl(rcv.accounted_dr,0)),0) – NVL(sum(nvl(accounted_cr,0)),0) amt,
glcc.segment1, glcc.segment2, rcv.pa_addition_flag FROM apps.rcv_transactions t
,apps.gl_code_combinations glcc
WHERE rcv.code_combination_id = glcc.code_combination_id
AND rcv.rcv_transaction_id = t.transaction_id
AND glcc.segment2 in (‘10903′,’10953′,’10814’) — specific accounts
AND rcv.actual_flag = ‘A’
AND rcv.accounting_date between ’01-JUN-08′ and ’30-JUN-08′
GROUP BY glcc.segment1, glcc.segment2, rcv.pa_addition_flag

Now we have to look at the output of all the queries to reconcile. If PA and AP does not match then we need to find out the uninterfaced transactions in both the modules and interface them. Similarly for the PO and PA.

In the next part i will be explaining about the Revenue reconciliation.

Leave a Reply