Here I am trying to describe R12 SLA(Sub Ledger Accounting) Procedure.
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.
Hope this will help you.
Oracle Unified Method (OUM) – Basics and Download
Share this:
Retirement of Oracle AIM & AIM for Business Flows
Share this:
R12 SLA Tables join conditions to AR, AP, INV,Payments and Receiving
– Loads SLA tables, GL tables
– Creates detailed data per accounting rules, stores in SLA “distribution links” table
XLA_AE_HEADERS xah
Below are the possible joins between these XLA Tables
xte.entity_code =
XLA_DISTRIBUTION_LINKS table join based on Source Distribution Types
Hope this will help you.
Share this:
Running Fast Formula From Backend – Oracle HRMS
Step1 : Create the Element and Element links
Step2: write the fast formula
Step3: Attach the fast formula in Formula Results
Package used to call the fast formula from the backed : ff_exec
Simple Code Snippet below:
l_formula_id NUMBER;
l_element_inputs ff_exec.inputs_t;
l_element_outputs ff_exec.outputs_t;
l_in_count NUMBER;
l_out_count NUMBER;
l_pay_value NUMBER;
BEGIN
BEGIN
SELECT formula_id
INTO l_formula_id
FROM ff_formulas_f
WHERE formula_name = ‘XX_PAYROLL_FORMULA’
AND p_effective_date BETWEEN effective_start_date
AND effective_end_date;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘NO formula exists’);
END;
IF l_formula_id IS NOT NULL
THEN
— Insert FND_SESSIONS row ( Optional )
INSERT INTO fnd_sessions
( session_id,
effective_date
)
VALUES
( USERENV (‘sessionid’),
p_effective_date
);
—
— Initialize the formula.
—
ff_exec.init_formula (l_formula_id,
p_effective_date,
l_element_inputs,
l_element_outputs
);
—
— Loop through the Input Values
—
FOR l_in_count IN l_element_inputs.FIRST .. l_element_inputs.LAST
LOOP
—
— Pass The each Input value name and its Value : Eg: START_DATE and p_start_date
—
IF (l_element_inputs (l_in_count).NAME = ‘START_DATE’)
THEN
l_element_inputs (l_in_count).VALUE :=
fnd_date.date_to_canonical (p_start_date);
END IF;
END LOOP;
—
–Run The formula
—
ff_exec.run_formula (l_element_inputs, l_element_outputs);
—
— Get the Out Put Values
—
FOR l_out_count IN l_element_outputs.FIRST .. l_element_outputs.LAST
LOOP
—
— Get all the Out Put Values Here L_PAY_VALUE is the out put value
—
IF (l_element_outputs (l_out_count).NAME = ‘L_PAY_VALUE’)
THEN
l_pay_value := l_element_outputs (l_out_count).VALUE;
END IF;
END LOOP;
RETURN (l_pay_value);
END;
Share this:
FND Messages In Oracle HRMS
Here i am Explaining how to create Fnd Messages via E-Business suite and the implementation of message retrieval via the pl/sql API package provided with Oracle Applications.
Creating an Oracle E-Business Suite Message
To create a message in the E-Business suite message library you will need the “Application Developer” responsibility.
Navigate to Application Developer > Application > Messages. This will launch a form
Enter a unique name for your message
Eg: XX_CUSTOMER_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my first message
Click the save icon.
Retrieving a message using PL/SQL:
In order to retrieve the message from the database we need to use a standard API’s in the FND_MESSAGE package.
An E-Business suite message should be retrieved as follows:
1. Clear the current session of any message variables that may already be set
2. Tell E-Business suite which message you wish to retrieve
3. Retrieve the actual message string
4. Clear the session (Optional)
Below is the PL/SQL Block to retrive the message
DECLARE
my_message VARCHAR2(100);
BEGIN
–Initialize Apps Session
Responsibility‘;fnd_global.apps_initialize( user_id => 1234
,resp_id => 1235
,resp_appl_id => 1236
);
/*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd,
fnd_responsibility_tl fresp
where fnd.user_name = ‘OEAG’
and fresp.responsibility_name = ‘Custom HRMS
*/
–Clear the existing session
FND_MESSAGE.CLEAR;
–Tell e business suite which message you want (custom application short name/message name)
FND_MESSAGE.SET_NAME(‘XXERP’,’XX_CUSTOMER_MSG’);
–Retrieve the message
my_message := FND_MESSAGE.GET;
–Output the message
DBMS_OUTPUT.PUT_LINE(my_message);
END;
Output for the Above Block Is : This is my first message
Using Tokens in the message:
The Oracle E-Business suite allows the substitution of tokens within a message string to enable the programmer to add dynamic content to the message at run time.
Open the E-Business Suite message create a New Message
Navigate to Application Developer > Application > Messages. This will launch a form
Enter a unique name for your message
Eg: XX_UNAME_TOKEN_MSG
Select the language that your message is written in and the application that the message belongs
Enter the message text in the “Current Message Text” box.
Eg: This is my second message and the Token User name is &USERNAME
Click the save icon.
Note: In order to insert a token into a message it is necessary to prefix the token with a ampersand e.g. &USERNAME
Retrieving message With Token Substitution
Here USERNAME is called as TOKEN, we will Add the value dynamically
Example Block is below:
DECLARE
my_message VARCHAR2(100);
BEGIN
–Initialize Apps Session
fnd_global.apps_initialize( user_id => 1234
,resp_id => 1235
,resp_appl_id => 1236
);
/*–Note: You will get the uer_id, resp_id and Resp_appl_id using below Query
select fnd.user_id ,
fresp.responsibility_id,
fresp.application_id
from fnd_user fnd,
fnd_responsibility_tl fresp
where fnd.user_name = ‘OEAG’
and fresp.responsibility_name = ‘Custom HRMS Responsibility’;
*/
–Clear the existing session
FND_MESSAGE.CLEAR;
–Tell e business suite which message you want (Application short name/message name)
XXERP‘,’XX_UNAME_TOKEN_MSG’);FND_MESSAGE.SET_NAME(‘
–Set the username message token with the current applications user
FND_MESSAGE.SET_TOKEN(‘USERNAME’,FND_GLOBAL.USER_NAME);
–Retrieve the message
my_message := FND_MESSAGE.GET;
–Output the message
DBMS_OUTPUT.PUT_LINE(my_message);
END;
Out put for above block is :
Downloading and Uploading Messages using the Generic Loader
To download our example message we would use the following command at the Unix prompt on the mid-tier:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
FND_NEW_MESSAGES APPLICATION_SHORT_NAME=’PER’ MESSAGE_NAME=”XX_UNAME_TOKEN_MSG”
To Upload our example message we would use the following command at the Unix prompt on the mid-tier:
FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_UNAME_TOKEN_MSG.ldt
Share this: