Link Between FA and GL table using XLA tables

Query for FA and Gl Link

SELECT *
FROM
GL_JE_HEADERS GJH,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH,
FA_TRANSACTION_HEADERS FTH,
FA_ADJUSTMENTS FAA
WHERE
GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND GJH.JE_HEADER_ID=GIR.JE_HEADER_ID
AND GJL.JE_LINE_ID=GIR.JE_LINE_ID
AND GIR.GL_SL_LINK_ID=XAL.GL_SL_LINK_ID
AND XAL.AE_HEADER_ID=XAH.AE_HEADER_ID
AND XAH.EVENT_ID=FTH.EVENT_ID
AND FTH.TRANSACTION_HEADER_ID=FAA.TRANSACTION_HEADER_ID

please note for an event_id which pertains to Depreciation category, the event_id is not available in fa_transaction_headers, instead it is available in fa_deprn_summary

You have to make appropriate adjustments to the query for book type code, ledger, MRC if any etc.

Link between GL and AR through XLA

The main link to bind information together is the GL_SL_LINK_ID. This field exists in GL_JE_LINES, GL_IMPORT_REFERENCES and XLA_AE_LINES tables.

Also, the XLA_DISTRIBUTION_LINKS table contains the application_id, event_id, ae_header_id, ae_line_num from the XLA Tables and source_distribution_id_num_1 will be the cust_trx_line_gl_dist_id in the case of a transaction.

Query 1: For complete Transaction
SELECT *
FROM
RA_CUSTOMER_TRX_ALL RCTA,
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_TRANSACTION_ENTITIES XTE,
XLA_EVENTS XE
WHERE
RCTA.CUSTOMER_TRX_ID = RCTG.CUSTOMER_TRX_ID
AND RCTA.CUSTOMER_TRX_ID = XTE.SOURCE_ID_INT_1
AND XTE.ENTITY_ID = XE.ENTITY_ID

Query 2: After Create accounting Run
SELECT *
FROM
RA_CUST_TRX_LINE_GL_DIST_ALL RCTG,
XLA_DISTRIBUTION_LINKS XDL,
XLA_AE_LINES XAL,
XLA_AE_HEADERS XAH
WHERE
RCTG.CUST_TRX_LINE_GL_DIST_ID = XDL.SOURCE_DISTRIBUTION_ID_NUM_1
AND XAL.AE_HEADER_ID = XDL.AE_HEADER_ID
AND XAH.AE_HEADER_ID = XDL.AE_HEADER_ID

Query 3 : After Run Transfer to GL
SELECT *
FROM
XLA_AE_LINES XAL,
GL_JE_LINES GJL,
GL_IMPORT_REFERENCES GIR,
GL_JE_HEADERS GJH
WHERE
XAL.GL_SL_LINK_ID = GJL.GL_SL_LINK_ID
AND XAL.GL_SL_LINK_ID = GIR.GL_SL_LINK_ID
AND GIR.JE_HEADER_ID = GJL.JE_HEADER_ID
AND GIR.JE_HEADER_ID = GJH.JE_HEADER_ID

AP to GL Link using XLA Tables

The table is called GL_IMPORT_REFERENCES .

It is linked with GL_JE_BATCHES by JE_BATCH_ID and linked with GL_JE_HEADERS, GL_JE_LINES by JE_HEADER_ID

This table is linked with XLA related table by reference column.
REFERENCE_5 is ENTITY_ID in XLA table.
REFERENCE_6 is ACCOUNTING_EVENT_ID in XLA table.
REFERENCE_7 is AE_HEADER_ID in XLA table.
The most important column in GL_IMPORT_REFERENCES is GL_SL_LINK_ID, you also can find this column in table XLA_AE_LINES

Lets see how we can Drilldown from Account Payables Invoice details to GL Journal Detail.

select * from xla_entity_id_mappings

where entity_code like 'AP_INVOICES';

1. When we create invoices it will hit below given table in account Payables.

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

e.g. :

Invoice Header:

select * from ap_invoices_all

where invoice_num='IN0097158'; -- Copy the value for Invoice_id for future rerfrence 16211228

Invoice Line : 

select * from ap_invoice_lines_all

where invoice_id=16211228;

Invoice Distributions :

select * from ap_invoice_distributions_all

where invoice_id=16211228;

SLA Tables :

select * from XLA.xla_transaction_entities

where source_id_int_1=16211228

and transaction_number='IN0097158'; -- From this table copy value for Entity id 580427218

--  

SELECT * FROM XLA_AE_HEADERS

WHERE ENTITY_ID=580427218;  --- From this table copy value Event id 582978337 and ae_header_id value 126276903

-- 

select * from xla_events

WHERE EVENT_ID=582978337;

-- 

select * from xla_ae_lines

WHERE AE_HEADER_ID=126276903;

--

SELECT *

from gl_import_references gir

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903); -- from this table you will get detail for je_header_id, je_line_num, je_batch_id

--

select * from gl_je_batches

where je_batch_id= 5709025;

-- 

select * from gl_je_headers

where je_header_id=6780748;

--

select * from gl_je_lines

where je_header_id=6780748

and je_line_num in (9619,6734);

--

  

select

gjh.name Journal_name,

gjb.name Batch_name,

gjl.description Line_desc,

gjc.je_category_name,

gjs.je_source_name,

xl.entered_dr,

xl.entered_cr,

xl.accounted_dr,

xl.accounted_cr

from gl_import_references gir,

gl_je_headers gjh,

gl_je_batches gjb,

gl_je_lines gjl,

xla_ae_lines XL,

gl_je_categories gjc,

gl_je_sources gjs

where gir.gl_sl_link_id in

(select gl_sl_link_id from xla_ae_lines

WHERE AE_HEADER_ID=126276903)

and gjh.je_header_id=gir.je_header_id

and gjl.je_line_num=gir.je_line_num

and gjl.je_header_id=gjh.je_header_id

and gjh.je_batch_id=gjb.je_batch_id

and gjh.actual_flag='A'

AND gjh.status='P'

and xl.gl_sl_link_id=gir.gl_sl_link_id

and gjh.je_category=gjc.je_category_name

and gjs.je_source_name=gjh.je_source    ;

How to get FND log messages extract for SR

Please proivde FND logs as per below steps for reproducible case

1) Set the Profiles

* FND: Debug Log Enabled : Yes
* FND: Debug Log Level : Statement
* FND: Log Module : %

2) Get the current log sequence in the FND table.
select max(log_sequence) from fnd_log_messages ; -- start

3) Reproduce the issue

4) Get the log sequence in the FND table.
select max(log_sequence) from fnd_log_messages ; -- end

5) Get the FND log data.
select * from fnd_log_messages where log_sequence between <start> and <end>;

6) Spool this into an xls file.

7) Lastly, remember to disable FND: Debug Log after you are done; otherwise, you could encounter tablespace issues

SQL Query to retrive profile option value from backend

SELECT

fpot.user_profile_option_name Profile,

decode(fpov.level_id,

10001,'Site',

10002,'Application',

10003,'Responsibility',

10004,'User') Profile_option_level,

decode(fpov.level_id,

10001,'Site',

10002,fa.application_short_name,

10003,frt.Responsibility_name,

10004,fu.user_name) profile_Level_Value,

nvl(fpov.profile_option_value,'Is Null') Profile_option_Value

FROM 

fnd_profile_option_values fpov,

fnd_profile_options_tl fpot ,

fnd_profile_options fpo,

fnd_responsibility_tl frt,

fnd_application fa,

fnd_user fu

WHERE

fpo.profile_option_name = fpot.profile_option_name

and  fpo.profile_option_id = fpov.profile_option_id

and  fpov.level_value = frt.responsibility_id (+)

and  fpov.level_value = fa.application_id (+)

and  fpov.level_value = fu.user_id (+)

and  fpot.user_profile_option_name like ('%FND%Debug%')

order by 1,2;

List of API in Oracle Apps for TCA, AP, PO and AOL

Sr. No.	Detail	API Name	Module
1	Create Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
2	Create a Customer Account Relationship API	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCT_RELATE	TCA
3	API to update a Customer Account Relationship TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCT_RELATE	TCA
4	API to Update a Customer Account TCA R12	HZ_CUST_ACCOUNT_V2PUB.UPDATE_CUST_ACCOUNT	TCA
5	Create Customer Profile	HZ_CUSTOMER_PROFILE_V2PUB.CREATE_CUSTOMER_PROFILE	TCA
6	API to Create a Customer Site TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_ACCT_SITE	TCA
7	API to Create a Customer Site Use TCA R12	HZ_CUST_ACCOUNT_SITE_V2PUB.CREATE_CUST_SITE_USE	TCA
8	API to Create a Party Site TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE	TCA
9	API to Create a Party Site Use TCA R12	HZ_PARTY_SITE_V2PUB.CREATE_PARTY_SITE_USE	TCA
10	API to create a Person Type Party and Customer Account	HZ_CUST_ACCOUNT_V2PUB.CREATE_CUST_ACCOUNT	TCA
11	API to Create a Phone Number (Contacts) TCA R12	HZ_CONTACT_POINT_V2PUB.CREATE_CONTACT_POINT	TCA
12	API to create customer Addresses in TCA R12	HZ_LOCATION_V2PUB.CREATE_LOCATION	TCA
13	API to create Group in TCA R12	HZ_PARTY_V2PUB.CREATE_GROUP	TCA
14	API to populate loc_id HZ_LOCATIONS R12	HZ_TAX_ASSIGNMENT_V2PUB. CREATE_LOC_ASSIGNMENT	TCA
15	API to update a Customer Profile TCA R12	HZ_CUSTOMER_PROFILE_V2PUB.UPDATE_CUSTOMER_PROFILE	TCA
16	API to Update Customer Address in Oracle TCA R12	HZ_LOCATION_V2PUB.UPDATE_LOCATION	TCA
17	Assign Receipt method to Customer via API	hz_payment_method_pub.create_payment_method	TCA
18	Create Party of type Organization in Oracle TCA using API	hz_party_v2pub.create_organization	TCA
19	API to apply hold on AP invoice in R12	AP_HOLDS_PKG.INSERT_SINGLE_HOLD	AP
20	API to cancel single AP invoice	AP_CANCEL_PKG.AP_CANCEL_SINGLE_INVOICE	AP
21	API to find AP INVOICE Status	AP_INVOICES_PKG.GET_APPROVAL_STATUS	AP
22	API to release hold on AP invoice in R12	AP_HOLDS_PKG.RELEASE_SINGLE_HOLD	AP
23	Credit Memo Creation via API	ar_credit_memo_api_pub.create_request	AP
24	API to create Supplier in R12	pos_vendor_pub_pkg.Create_Vendor	AP
25	API to create supplier site in R12	pos_vendor_pub_pkg.create_vendor_site	AP
26	API to create or update Supplier Hub UDA attr	pos_vendor_pub_pkg.process_user_attrs_data
27	API for Cancelling the Purchase Order (PO) Document	PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT	PO
28	API to Update Purchase Order Document (PO)	PO_CHANGE_API1_S.UPDATE_PO	PO
29	API to load value in value set	FND_FLEX_VALUES_PKG.LOAD_ROW	AOL
30	API to submit concurrent program	fnd_request.submit_request	AOL

How To Set A System Profile Value Without Logging In To The Applications

To set a profile option without accessing the applications, use the SAVE function from the FND_PROFILE package.

As a test case, below is the detailed procedure of changing the "Guest User Password" profile's value to GUEST/ORACLE.

1. To obtain the internal name of the profile option use the following select statement as APPS:
SQL> select profile_option_name from fnd_profile_options_tl where user_profile_option_name like '<Your Profile Option User Name or Part of it>';
e.g. For the "Guest User Password" profile option the internal name GUEST_USER_PWD

SQL> select profile_option_name from fnd_profile_options_tl where user_profile_option_name like 'Guest%';

PROFILE_OPTION_NAME
--------------------
GUEST_USER_PWD
2. Use the following sample code to set the profile option to the required value:

DECLARE
stat boolean;
BEGIN
dbms_output.disable;
dbms_output.enable(100000);
stat := FND_PROFILE.SAVE('GUEST_USER_PWD', 'GUEST/ORACLE', 'SITE');
IF stat THEN
dbms_output.put_line( 'Stat = TRUE - profile updated' );
ELSE
dbms_output.put_line( 'Stat = FALSE - profile NOT updated' );
END IF;
commit;
END;