Oracle R12 PO Email to Supplier Status

The query below can help in identifying PO Print email notification status.

 

select
p.segment1 po_number,
p.supplier_notif_method,
p.email_address,
p.document_creation_method,
p.wf_item_type,
p.wf_item_key,
p.closed_code,
p.cancel_flag,
p.printed_date,
p.approved_flag,
p.approved_date,
p.revised_date,
p.vendor_id,
p.vendor_site_id,
p.agent_id,
u.description buyer,
p.org_id,
n.mail_status,
n.status notification_status,
n.sent_date notification_sent_date
from po_headers_all p,
fnd_user u,
ap_suppliers s,
ap_supplier_sites_all ss,
wf_notifications n
where p.creation_date like SYSDATE or p.creation_date > sysdate – 30
and u.employee_id = p.agent_id
and s.vendor_id = p.vendor_id
and ss.vendor_site_id = p.vendor_site_id
and n.item_key = p.wf_item_key
and upper(n.to_user) = upper(s.vendor_name||’-‘||ss.vendor_site_code) ;

 

Accrual Reconciliation SQL for AP and Purchasing

This query lists all the AP invoices linking with or without PO for last update date in the given period. by seeing this, we can identify if  an accrual account from PO is changed at AP.

select
ai.invoice_num,
ai.invoice_date,
aid.amount,
aid.base_amount,
GCC.CONCATENATED_SEGMENTS Invoice_distribution_account,
AID.POSTED_FLAG,
aid.accounting_date,
ph.segment1 PO_Number,
AID.PO_DISTRIBUTION_ID,
pd.amount_billed PO_distribution_amount,
gcc2.CONCATENATED_SEGMENTS PO_Accrual_account
from ap_invoice_distributions_all aid,
ap_invoices_all ai,
gl_code_combinations_kfv gcc,
gl_code_combinations_kfv gcc2,
po_distributions_all pd,
po_headers_all ph
where aid.org_id = &ORG_ID
and aid.invoice_id = ai.invoice_id
and gcc.code_combination_id = aid.DIST_CODE_COMBINATION_ID
and pd.po_distribution_id (+) = AID.PO_DISTRIBUTION_ID
and gcc2.code_combination_id (+) = pd.accrual_account_id
and ph.po_header_id = pd.po_header_id
and ai.last_update_date between ‘&START_DATE’ and ‘&END_DATE’
order by ai.invoice_num

Oracle R12 Consignment Invoices not created – Debug and Solution

How to verify if consignment invoices are missing?

Date range wise identify transactions for which PO was not created.

SELECT *

FROM mtl_consumption_transactions

WHERE transaction_id IN

(SELECT a.transaction_id

FROM mtl_consumption_transactions a ,

mtl_material_transactions b

WHERE consumption_processed_flag = ‘Y’

AND a.transaction_id             = b.transaction_id

AND B.TRANSACTION_date          >= ‘&start_date’

AND B.TRANSACTION_date           < ‘&end_date’

AND NOT EXISTS

(SELECT 1

FROM po_line_locations_all l ,

po_lines_all line

WHERE line.po_line_id = l.po_line_id

AND po_release_id     = a.consumption_release_id

AND line.item_id      = b.inventory_item_id

)

);

Take a backup of above data in to a table and then can ask business using below query if these are the ones they are expecting.

select  t.transaction_id,

i.segment1,

i.description,

t.transaction_date,

t.net_qty,

t.Blanket_Price,

round(t.net_qty * t.Blanket_Price, 2) net_total,

O.ORGANIZATION_name,

ph.segment1 BPA

from MCT_BACKUP_48598204 t,

mtl_system_items_b i,

org_organization_definitions o,

po_lines_all pl,

po_headers_all ph

where o.organization_id = t.organization_id

and i.inventory_item_id = t.inventory_item_id

and i.organization_id = t.organization_id

and pl.po_line_id = T.PO_LINE_ID

and pl.po_header_id = ph.po_header_id;

Then update script from oracle.

CREATE TABLE MCT_BACKUP_48598204 AS

SELECT *

FROM mtl_consumption_transactions

WHERE transaction_id IN

(SELECT a.transaction_id

FROM mtl_consumption_transactions a ,

mtl_material_transactions b

WHERE consumption_processed_flag = ‘Y’

AND a.transaction_id             = b.transaction_id

AND B.TRANSACTION_date          >= ‘&start_date’

AND B.TRANSACTION_date           < ‘&end_date’

AND NOT EXISTS

(SELECT 1

FROM po_line_locations_all l ,

po_lines_all line

WHERE line.po_line_id = l.po_line_id

AND po_release_id     = a.consumption_release_id

AND line.item_id      = b.inventory_item_id

)

);

 

UPDATE mtl_consumption_transactions

SET    CONSUMPTION_PROCESSED_FLAG=’N’,

batch_id = NULL,

consumption_release_id    = NULL

WHERE  transaction_id IN

( select transaction_id from MCT_BACKUP_48598204);

 

COMMIT;

/

Perpetual (Online) Accruals Reconciliation in Oracle R12

The following steps can then be performed to reconcile online (perpetual) accruals with General Ledger:

1.  Receiving Accounting

a) Ensure that all receiving transactions have been processed for the period.
b) Using the ‘Cost Management – SLA’ responsibility, submit the ‘Create Accounting – Cost Management’ program using these parameters:

– Ledger = <ledger name>
– Process Category = <leave blank>
– End Date = <end date of the accounting period>
– Mode = Final
– Errors Only = No
– Report = Summary
– Transfer to General Ledger = Yes
– Post in General Ledger = Yes
– General Ledger Batch Name = <leave blank>
– Include User Transaction Identifiers = <yes or no>

c) Using the ‘Cost Management – SLA’ responsibility, also submit ‘Transfer Journal Entries to GL – Cost Management’ program using these parameters:

– Ledger = <ledger name>
– Category = <leave blank>
– End Date = <end date of the accounting period>
– Post in General Ledger = Yes
– General Ledger Batch Name = <leave blank>

This will process any receiving accounting records which were not previously transferred to GL when Create Accounting was run.

2.  Payables Accounting

a) Ensure that all invoice transactions in Payables have been processed for the period.
b) Using a Payables responsibility, submit the ‘Create Accounting’ program using these parameters:

– Ledger = <ledger name>
– Process Category = <leave blank>
– End Date = <end date of the accounting period>
– Mode = Final
– Errors Only = No
– Report = Summary
– Transfer to General Ledger = Yes
– Post in General Ledger = Yes
– General Ledger Batch Name = <leave blank>
– Include User Transaction Identifiers = <yes or no>

c) Using a Payables responsibility, also submit ‘Transfer Journal Entries to GL’ program using these parameters:

– Ledger = <ledger name>
– Category = <leave blank>
– End Date = <end date of the accounting period>
– Post in General Ledger = Yes
– General Ledger Batch Name = <leave blank>

This will process any Payables accounting records which were not previously transferred to GL when Create Accounting was run.

3.  Using a General Ledger responsibility, ensure that all journals from the Cost Management, Payables, Inventory, and Work In Process subledgers have been imported and posted in GL.

4.  Using either a Purchasing, Payables, or Cost Management – SLA responsibility, submit the ‘Accrual Reconciliation Load Run’ process.

– Date From = <enter a date or leave blank>
– Date To = <last day of the accounting period>

5.  Submit the ‘Summary Accrual Reconciliation Report’.
Review the report output.  Take note of each accrual account and its balance.

6.  Submit the ‘Account Analysis Report’ for each accrual account appearing in the ‘Summary Accrual Reconciliation Report’.  The following parameters can be used:

– Ledger/Ledger Set = <ledger name>
– Period From = <current period>
– Period To = <current period>
– Balance Type = Actual
– Accounting Flexfield From = <accrual account>
– Account Flexfield To = <accrual account>
– Include Zero Amount Lines = Yes
– Include User Transaction Identifiers = Yes
– Include Statistical Amount Lines = Yes
– Include Accounts With No Activity = Yes

Review the report output.  Take note of the account ending balance.  This should reconcile to the account balance shown in the ‘Summary Accrual Reconciliation Report’.

Receipt Accruals Process and Debug

Let us presume you are closing FEB-16

1a)
CLOSE THE PURCHASING PERIOD FOR JAN-16
CLOSE THE AP PERIOD for FEB-16
1b)
MAKE SURE
PO PERIOD FOR FEB-16 and GL PERIOD for FEB-16 are OPEN

2)
Please run the following sql

Select Count(*)
From po_distributions_all
Where Destination_type_code=’EXPENSE’
And Accrue_on_receipt_flag=’N’
And accrued_flag=’N

Sql above should have data

Select Count(*)
From po_distributions_all
Where Destination_type_code=’EXPENSE’
And Accrue_on_receipt_flag=’N’
And accrued_flag=’Y’

sql above should return No rows

If rows are returned from last sql please

Run the following :

Run the ‘Reset Period End Accrual Flags’ concurrent program from Purchasing Responsibility:
Reports/Run. This is done only for the purpose of testing and making sure all Expense PO DISTRIBUTIONS
are set with accrued_flag=N

Re-run the sql above:

Select Count(*)
From po_distributions_all
Where Destination_type_code=’EXPENSE’
And Accrue_on_receipt_flag=’N’
And accrued_flag=’Y’

If rows are still returned DO NOT PROCEED

3) Submit the Uninvoiced Receipts Report with the following parameters:
– Accrued Receipts = No
– Include Online Accruals = No
– Include Closed POs = No
– Period Name =FEB-16
– Dynamic Precision Option = 13 (this is the most precise setting)

Upload the output file and Log File from this report

3)
Run and provide output of the following sql:

select sum(accounted_dr),sum(accounted_cr)
from RCV_RECEIVING_SUB_LEDGER
where Accrual_Method_Flag =’P’
and period_name=’FEB-16′

Make sure no data is retrieved. If data is retrieved do not proceed

4)Submit the ‘Receipt Accruals – Period End process For FEB-16
for the Uninvoiced Receipts Report.

In order to successfully run this program the following should exist:

GL Period for FEB-16 must be OPEN
Purchasing Period for FEB-16 must be OPEN
A/P period forFEB-16 must be CLOSED

Upload the log file for the ‘Receipt Accruals – Period End process when it completes

5) Re-Run the Uninvoiced Receipt Report for same Period

Uninvoiced Receipts Report with the following parameters:
– Accrued Receipts = YES
– Include Online Accruals = No
– Include Closed POs = No
– Period Name = FEB-16
– Dynamic Precision Option = 13 (this is the most precise setting)

6) Re-run the script below
select sum(accounted_dr),sum(accounted_cr)
from RCV_RECEIVING_SUB_LEDGER
where Accrual_Method_Flag =’P’
and period_name=’FEB-16′

7)
Output of the script above should reconcile with the Uninvoiced Receipt Report Total

==========================================================================================STOP HERE DO NOT RUN CREATE ACCOUNTING

8)Make sure both FEB-16 and MAR-16 GL Periods are OPEN

Then Run the Create Accounting Receiving
It is recommended to Run from Cost management /SLA Responsibility: REQUEST
Create Accounting Cost Management
Select Category Receiving
Mode: FINAL
End date FIRST DATE OF NEXT PERIOD— 01-MAR-16
TRANSFER TO GL =Y

9)

verify the period end accruals generated from application front end,
use the SLA Inquiry from the responsibility: Cost Management-> SLA -> View Accounting Journals.
Query with event type code PERIOD_END_ACCRUAL and the concerned period: FEB-16
Do you find the Period Accruals there

10)

Run the following sql when you finish:

select xh. Accounting_date,xh.event_type_code, xl.accounted_dr,xl.accounted_cr, xh.GL_TRANSFER_STATUS_CODE,xh.GL_TRANSFER_DATE,
xh.JE_CATEGORY_NAME,xh.ACCOUNTING_ENTRY_STATUS_CODE,xh.Accrual_Reversal_flag,product_Rule_code
From xla_ae_lines xl, xla_ae_headers xh
where xl.ae_header_id = xh.ae_header_id
And xl.Application_id =xh.Application_id
And xh. period_name in (‘&period_name’)
And xh.event_type_code in (‘PERIOD_END_ACCRUAL’)
And xl.application_id=’707′

Verify that ACCOUNTING_ENTRY_STATUS_CODE=F
GL_TRANSFER_STATUS_CODE=Y
for both accrual_reversal_flag=Y these are the Actuals
accrual_reversal_flag=’N’ These are the Reversal Entries

How to chart of accounts structure in Oracle GL using SQL

select
b.FORM_LEFT_PROMPT user_segment_name,
b.description,
a.segment_name,
c.ID_FLEX_STRUCTURE_NAME,
b.application_column_name
from
FND_ID_FLEX_SEGMENTS a,
FND_ID_FLEX_SEGMENTS_TL b,
FND_ID_FLEX_STRUCTURES_VL c
where
b.language = ‘US’
and c.ID_FLEX_NUM = b.ID_FLEX_NUM
and c.enabled_flag = ‘Y’
and b.application_id = a.application_id
and b.id_flex_num = a.id_flex_num
and b.application_column_name = a.application_column_name
and a.application_id = 101

Thsi query lists all accounting flexfields defined in oracle apps. This query was designed on 11i version.

FSG Transfer Program

FSG Transfer is a programme that you can run in General Ledger which gives you the ability to copy Financial Statement Generator (FSG) components from one environment to another. For example, you might want to transfer your FSG’s from a TEST environment to your PRODUCTION environment.

According to the Oracle General Ledger User Guide, before this report can be run, the following prerequisites should have been implemented: –

  • You or your System Administrator must define database links.
  • The Chart of Accounts in your source database must be identical to the Chart of Accounts in your target database.
  • Any currencies and sets of books referred to by the row sets and column sets being copied must exist in the target database.
  • Report details, such as budgets and encumbrance types, referred to by copied reports must exist in the target database.
  • You must be logged in to General Ledger and connected to the target database.

Programme Parameters

To run the report, from the file menu in General Ledger Responsibility select View > Requests and choose to run a single request entitled Program – FSG Transfer. Examples of how the parameters for this programme can be filled are as follows: –

An example of how you might fill out the FSG programme transfer parameters

Component Type

The following elements of an FSG can be copied between environments: –

  • Column Sets
  • Content Sets
  • Display Groups
  • Display Sets
  • Reports
  • Report Sets
  • Row Orders
  • Row Sets
  • All of the above

Component Name

The name of the component selected above should be entered if you are copying a single component mentioned above. If you are copying all components you do not need to specify a name.

Source DB Chart of Accounts

The exact name of the Chart of Accounts from which you want to copy report objects.

Target DB Chart of Accounts

The exact name of the Chart of Accounts to which you want to copy report objects.

Source

The name of the source database from which you are copying. This field will not have a List of Values (LOV) unless you have defined database links. To define database links within the General Ledger Responsibility go to Setup > System > Database Links and create a New Database Link (if you have any uncertainty about what parameters to enter here contact your DBA, typically you should be able to extract the connect string from the TNSNAMES.ORA file).

Creating Repository – OBIEE

Today I have started creating repository in OBIEE, but it’s not something which can be completed in few minutes.

As Oracle is providing tutorials for 11g, I have downloaded OBIEE 11g at office will be installing some time later tomorrow.

Let’s see how can we connect our XE with OBIEE Admin tool. To achieve this we need to create ODBC connection on our system.

To create ODBC connection go to START -> Control Panel -> Administrative Tools -> Data Sources(ODBC)

Click on the System DSN tab, use the Add… button on the right hand side to add new ODBC connection.

Select suitable Oracle Driver and press Finish to proceed to next step. In my case as I am using Oracle XE Database, I have used Oracle in XE.

Give a suitable Data Source Name, which you need to use during importing tables in RPD.

Description is optional, fill your comments about this ODBC connection.

TNS Service Name use the dropdown and select the Oracle TNS Service name. Eg: ORCL, XE, etc..

Give the User ID of SH schema or the schema you are using. If its SH schema user Id is generally SH.

Else if its APPS schema we need to use user ID which has access to different tables in APPS schema.

Now try testing the connection, if everything is correct connection will be successful, then we can proceed creating the RPD.

To create RPD we need to open Oracle BI Administration Tool.

START -> All Programs -> Oracle Business Intelligence -> Administration

Browse to menu bar through File -> New to create new RPD, give filename of the RPD.

Use File -> Import -> from Database… to import tables of SH schema into Physical layer of RPD.

Select ODBC we have created above and enter the credentials as requested(In my case its ORAXE).

Select required tables under SH schema and complete import.

Note that Tables and Keys must be checked for successful import.

Once import is successful, you will find SH Tables under Physical Layer.

To verify the connection go to Tools -> Update All Row Counts

If connection is successful you will find the row count with mouse over on SH tables under physical layer.

As my SH tables have no data, row count is 0.

Next step would be creating physical joins, will update in next article.

Thanks for reading. Comments are appreciated.

Installing SH Schema on Oracle XE

It was not easy to find ready script for me to install SH schema in Oracle XE database. As its XE edition, I could find HR schema in it which is not pre-configured.

“C:oraclexeapporacleproduct10.2.0serverdemoschema”

You can find sample schemas in the above mentioned path as per standard installation. Before installing our target SH Schema, I tried installing HR schema with few failures.

Then I have found an oracle link which explains how to install all sample schemas like HR, OE, PM, SH etc… below are the urls.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96539/scripts.htm#39789

http://download.oracle.com/docs/cd/B28359_01/server.111/b28328/scripts.htm#Cihijfgh

To install HR schema whose files were already present in my Oracle XE @ “C:oraclexeapporacleproduct10.2.0serverdemoschemahuman_resources” I used following command.

Line 1: CONNECT system/pass

Line2: @?/demo/schema/human_resources/hr_main.sql HR example temp pass ?/demo/schema/log/

Line1 connects to database as System Administrator. Kindly note “pass” is my database password, you have to replace it with yours, which you have given while installing Oralce XE on your system.

Line2 has many things passed as parameters.

First Parameter: “HR” – is the first parameter which is password for the HR schema. So you may use your own password for the HR schema by passing it as the first parameter to the script file.

Second Parameter: “example” – I guess it’s the schema type passed by Oracle. I cant confirm on this. If you know about this please post a comment below.

Third Parameter: “temp” – This is the schema name used for creating TEMPORARY TABLES which are available only during the session they are created.

Fourth Parameter: “pass” – It’s the system administrator password of Oracle XE Database. In my case its “pass”, you have to use the password which you have given during installation of the Oracle XE.

Fifth Parameter: “?/demo/schema/log/” – this the location where you want oracle to keep the log files created for the HR schema. Make sure the directory is created, once the setup is completed.

For SH schema code is as below

@?/demo/schema/sales_history/sh_main SH example temp pass @?/demo/schema/sales_history/ ?/demo/schema/log/

In the above code one extra parameter listed is “@?/demo/schema/sales_history/”. This specifies the path where SH script files are placed.

Before running the above script make sure you have created “sales_history” folder under “C:oraclexeapporacleproduct10.2.0serverdemoschema” and place the following list of files under it.

Script Name Description
sh_analz.sql Gathers statistics on the schema objects
sh_comnt.sql Creates comments for the objects in the schema
sh_cons.sql0 Modifies constraints on objects in the schema
sh_cre.sql Creates the objects in the schema
sh_cremv.sql Creates materialized views and bitmapped indexes
sh_drop.sql Drops the SH schema and all its objects
sh_idx.sql Creates indexes on tables in the schema
sh_main.sql Main script for the SH schema; calls other scripts
olp_v3.sql Creates dimensions and hierarchies used by the OLAP server
sh_olp_d.sql Drops the objects used by the OLAP server

Link for the scripts: http://www.mediafire.com/?12icizt51gwh919

Thanks for reading🙂