R12 SLA Tables join conditions to AP, AR, INV,Payments and Receiving

R12 SLA Tables connection to AP, AR, INV,Payments, Receiving

R12 SLA (Sub ledger Accounting)

1) All accounting performed before transfer to the GL. Accounting data generated and stored in "Accounting Events" tables prior to transfer to GL

2) Run "Create Accounting" to populate accounting events (SLA) tables. User can "View Accounting" only after "Create Accounting" is run. Create Accounting process

– Applies accounting rules

– Loads SLA tables, GL tables

– Creates detailed data per accounting rules, stores in SLA "distribution links" table

3) Below are the key tables for SLA in R12

XLA_AE_HEADERS xah

XLA_AE_LINES xal

XLA_TRANSACTION_ENTITIES xte

XLA_DISTRIBUTION_LINKS xdl

GL_IMPORT_REFERENCES gir

Below are the possible joins between these XLA Tables

xah.ae_header_id = xal.ae_header_id

xah.application_id = xal.application_id

xal.application_id = xte.application_id

xte.application_id = xdl.application_id

xah.entity_id = xte.entity_id

xah.ae_header_id = xdl.ae_header_id

xah.event_id = xdl.event_id

xal.gl_sl_link_id = gir.gl_sl_link_id

xal.gl_sl_link_table = gir.gl_sl_link_table

xah.application_id = (Different value based on Module)

xte.entity_code =

'TRANSACTIONS' or

'RECEIPTS' or

'ADJUSTMENTS' or

'PURCHASE_ORDER' or

'AP_INVOICES' or

'AP_PAYMENTS' or

'MTL_ACCOUNTING_EVENTS' or

'WIP_ACCOUNTING_EVENTS'

xte.source_id_int_1 =

'INVOICE_ID' or

'CHECK_ID' or

'TRX_NUMBER'

XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types

xdl.source_distribution_type = 'AP_PMT_DIST'

and xdl.source_distribution_id_num_1 = AP_PAYMENT_HIST_DISTS.payment_hist_dist_id

---------------

xdl.source_distribution_type = 'AP_INV_DIST'

and xdl.source_distribution_id_num_1 = AP_INVOICE_DISTRIBUTIONS_ALL.invoice_distribution_id

---------------

xdl.source_distribution_type = 'AR_DISTRIBUTIONS_ALL'

and xdl.source_distribution_id_num_1 = AR_DISTRIBUTIONS_ALL.line_id

and AR_DISTRIBUTIONS_ALL.source_id = AR_RECEIVABLE_APPLICATIONS_ALL.receivable_application_id

---------------

xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'

and xdl.source_distribution_id_num_1 = RA_CUST_TRX_LINE_GL_DIST_ALL.cust_trx_line_gl_dist_id

---------------

xdl.source_distribution_type = 'MTL_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = MTL_TRANSACTION_ACCOUNTS.inv_sub_ledger_id

---------------

xdl.source_distribution_type = 'WIP_TRANSACTION_ACCOUNTS'

and xdl.source_distribution_id_num_1 = WIP_TRANSACTION_ACCOUNTS.wip_sub_ledger_id

---------------

xdl.source_distribution_type = 'RCV_RECEIVING_SUB_LEDGER'

and xdl.source_distribution_id_num_1 = RCV_RECEIVING_SUB_LEDGER.rcv_sub_ledger_id

--

Query to get alerts information

SELECT
alv . *
FROM ALR_ALERTS al ,
ALR_ACTION_HISTORY aah ,
ALR_OUTPUT_HISTORY aoh ,
ALR_ACTIONS_V alv ,
alr_alert_historY_view aahv
WHERE al . alert_name = <name of alert > ---------'XX_ALERTS_SAMPLE'
AND al . alert_id = aah . alert_id
AND aah . check_id = aoh . check_id
AND alv . ALERT_ID = aah . alert_id
AND aahv . alert_name = al . alert_name;

--

QUERY FOR FINDING REQUEST GROUP

QUERY FOR FINDING REQUEST GROUP

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name,
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE     frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id
       AND fe.executable_id = fcp.executable_id
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = 'XX_PC_PURCHASE';
--

Create directory in Oracle

Create directory in Oracle
       

create or replace directory foo_dir as '/tmp';

Directories must be created if external tables are used.
Created directories are shown in either dba_directories or all_directories. There is no user_directories.

Privileges

When a «directory» has been created, the read and write object privileges can be granted on it:

create directory some_dir;
grant read, write on directory some_dir to micky_mouse;

An example

The following example shows how create directory and utl_file can be used to write text into a file:

create or replace directory dir_temp as 'c:\temp';

declare
  f utl_file.file_type;
begin
  f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
  utl_file.put_line(f, 'line one: some text');
  utl_file.put_line(f, 'line two: more text');
  utl_file.fclose(f);
end;
/

How to deleted from the interface tables after it has been loaded in import standard purchase order

How is data deleted from the interface tables after it has been loaded ?

After loading the data from the interface tables into the system successfully,  the data is not being removed (cleaned) from the interfaces.
They will have process_code = 'ACCEPT' in the interface tables.

To remove the processed data from the interface, a concurrent program is available.  The program name is : purge purchasing open interface processed data(POXPOIPR)

Run this program with paramenter purge accepted data = Yes
Then the process_code = 'ACCEPTED' records will be removed from the interface tables.

--

Purchasing Interface Errors Report(choose parameter : PO_DOCS_OPEN_INTERFACE)

What document can be viewed after running purchasing document open interface with records rejected?

Check the process_code in the po_headers_interface and po_lines_interface, if it is 'REJECTED',

select process_code from po_headers_interface;
select process_code from po_lines_interface;

please do the following:
Run the program - Purchasing Interface Errors Report
choose parameter : PO_DOCS_OPEN_INTERFACE

The report will list all the errors you have during importing. You can fix the data, then reset process_code = Null in both interface tables, rerun the Purchasing Document Open Interface.

update po_headers_interface set process_code = null
where process_code = 'REJECTED';
update po_lines_interface set process_code = null
where process_code = 'REJECTED';

--

What Actions are supported in import standard purchase order ?

What Actions are supported?

ORIGINAL - create a new document
ADD - add new lines onto an existing document
UPDATE - update existing line information for a document
REPLACE - Replace the existing document
Which interface tables are involved?

PO_HEADERS_INTERFACE
PO_LINES_INTERFACE
PO_DISTRIBUTIONS_INTERFACE(Used for Standard PO only)

--