Showing posts with label General Ledger(GL). Show all posts
Showing posts with label General Ledger(GL). Show all posts

Oracle Apps 11i : Creating Journals through Oracle GL

This tutorial will describe the procedure of creating journals through oracle GL - Create a new batch with multiple Journal entries, Enter Journals to a Batch and to Enter Journal Lines. 


Create a new batch with multiple Journal entries

Create a Journal batch

The following are the steps for creation of a journal batch:
1. Navigate to the enter journals window.
2. Choose New Batch.
3. Enter an optional Batch name to identify the batch on general ledger and journal entry reports. You cannot have duplicate batch names in the same accounting period. If you do not enter a batch name, General Ledger will create a default name from the source, combined with a unique batch ID and the system date.
4. Enter the accounting Period for which you want to post the entries in your journal batch. General Ledger defaults to the latest Open period.
a. Note: If you enter a period prior to the current accounting period and the user profile option Journals: Enable Prior Period Notification is set to Yes, General Ledger will display a message indicating that you are entering a prior period journal. You must confirm that this is what you want to do.
b. Additional Information: Balance Type is a display–only field. It displays Actual when you are entering actual journals and Budget when you are entering budget journals.
5. (Optional) Enter a description for the journal batch.
6. Enter a Control Total if you want to verify the total debits for your journal batch against the batch control total. You can also enter a control total at the journal entry level.
7. Choose Journals to add journals to the batch.

Figure -2 The Journal batch creation screen.

Enter Journals to a Batch

One can enter a journal to an existing batch or can create a new journal. The following are the steps:
1. Navigate to the Enter Journals window.
2. Enter or query the batch for which you are entering journals. To enter a journal without entering batch information, choose New Journal and proceed to Step 4.
  • To enter journals for a new batch, choose New Batch and enter the batch information. 
  • To add journals to an existing batch, query the batch and choose 
Review Batch.
3. Choose Journals.
4. Enter a unique Journal name for the entry. If you do not enter a journal name, General Ledger automatically assigns a name using the following format: Source Journal ID Date.
If you did not enter a batch name before entering journals, General Ledger uses the name of the first journal in the batch to create a default batch name.
5. Enter the Period for the journal entry. If you entered a period at the batch level, you must use the same period for each journal entry in the batch. If you did not enter a period at the batch level, choose any Open or Future Enterable period for your journal entry. Note that you can only post journals in Open periods.
6. Accept or change the default Effective Date for the journal entry.
7. Enter a Category to describe the purpose of your journal entry, such as accrual, payments or receipts. All lines in a journal entry share the same journal category.
General Ledger defaults the journal category if you defined the profile option Journals: Default Category.
8. Enter an optional Description for the journal entry. General Ledger uses this as the default description for each journal entry line. You can change the journal entry description as necessary.
9. Enter a Control Total if you want to verify the total debits for the journal lines against the journal control total.
10. Accept the default Currency (the functional currency for your set of books), or change the journal currency to enter a foreign currency or statistical journal.



R12 Query For Invoices Reconciliation and Currencies Data is stored in Oracle AP Tables

Here we find the tables involved in storing the Reconciliation and Currency Data related to the Payable INVOICE (Invoice_id = 1234567).

Reconciliation Tables:
    CE_STATEMENT_RECONCILS_ALL
    CE_STATEMENT_HEADERS
    CE_STATEMENT_LINES

SELECT DISTINCT CSL.*
  FROM CE_STATEMENT_HEADERS CSH,
       CE_STATEMENT_LINES CSL,
       CE_STATEMENT_RECONCILS_ALL CSR,
       AP_CHECKS_ALL ac,
       AP_INVOICE_PAYMENTS_ALL aip
 WHERE     CSL.statement_header_id = CSH.statement_header_id
       AND aip.check_id = ac.check_id
       AND ac.bank_account_id = CSH.bank_account_id
       AND aip.invoice_id = '1234567'
       AND TO_CHAR (ac.check_number) = CSL.bank_trx_number
       AND ac.check_id = aip.check_id
       AND csr.statement_line_id = CSL.statement_line_id;


Currencies Tables:
      FND_CURRENCIES
      AP_SYSTEM_PARAMETERS_ALL
      AP_INVOICES_ALL
    
SELECT *
  FROM FND_CURRENCIES
 WHERE currency_code IN (SELECT a.invoice_currency_code
                           FROM AP_INVOICES_ALL a
                          WHERE a.invoice_id = '1234567'
                         UNION
                         SELECT b.payment_currency_code
                           FROM AP_INVOICES_ALL b
                          WHERE b.invoice_id = '1234567'
                         UNION
                         SELECT c.base_currency_code
                           FROM AP_SYSTEM_PARAMETERS_ALL c

                          WHERE NVL (c.org_id, -99) = '982');

GL INTERFACES



                               GL INTERFACES


Difference between Interface and API


An API (Application Programming Interface) is inbuilt program through which data’s can be transferred to Oracle base tables directly without writing the program for  validating or inserting in Interface tables.


But through User Interface, we have to write codes for validation and insertion of data’s In Interface tables and then in Oracle base tables


Oracle defines an API as "A set of public programmatic interface that consist of a language and message format to communicate with an operating system or other programmatic environment, such as databases, Web servers, JVMs,  and so forth. These messages typically call functions and methods available for application development."I guess that there is no such thing as an interface to Oracle=2 there are only "open" interfaces.


These are a group of published and supported inbound and outbound Interface objects (talk about them more in a second) that Oracle has developed for your use=2 these objects may be, a table or group of tables, a concurrent program that references a PL*SQL or Pro*C/C++ package o library.


Oracle's policy over the years is that if you post data directly to an application table, then you are invalidating your support agreement, and if you are bored enough, you can see the clause in the agreement sure enough.


But these published interfaces are quite often not enough to d what you need to do. Take the case of Payables suppliersfor example. That has been a pain for everyone Right from the beginning.


So in answer to your question. An open interface is a group of objects that Oracle supports and licenses with the software that allow inbound and outbound data transactions in Oracle' Approved format. You put data into the tables and oracle import it or you run a concurrent request and oracle outputs data into that table. Batch processing. You need to put your data into the right pre-validated format before inserting into the interface table or the data will be rejected.


An API is programmatic hooks or coding blocks that you can "call that allow you to perform some function or other to achieve par of your goal. For example, Oracle Projects there is PA_INTERFACE_UTILS_PUB.CREATE_PROJECT API that takes a list o input variables that you assign to the called API (these can b in a table or passed directly) for example;
l_project_in.created_from_project_id := '1001'; -- Project id from template
l_project_in.project_name :='C, AMG TEST PROJECT';
l_project_in.pm_project_reference := 'C, AMG TEST PROJECT';
and if the package passes validation on all these data points your project will be inserted as a result.

So finally, you can insert data into an open interface table then use a concurrent program that CALLS an API to process that information.

In the case of real-time processing or OLTP, once you save record in PeopleSoft HR, you want it to appear in Oracle H immediately, you would write PeopleSoft code that copies those data items to memory, submit the list of required variable (PeopleSoft data)to the Oracle API as inputs. The API o completion posts your employee into theOracle database=2 (ideally you would provide a function then back to PeopleSoft t confirm that oracle received the transaction correctly and i wasn't rejected by the API). All this was achieved without the use of an open "interface".

The nail in t he coffin however and where you may be confused, i that many people think that any exchange of data from one table to another is an "interface". A "link".


And I guess that it i in a way.
Just remember that An interface is the pool,
An open interface table is the lanes and
An API is the swimmer.

The term Open Interfaces actually refers to table driven interfaces, where you put data in
a table that sits between your external application and the Oracle module you are interfacing with.
The term API refers to stored procedure driven interfaces, where you call a stored procedure
to perform an action within an Oracle Module, and the data from your external application is
passed through the stored procedure’s parameters. Historically, there were only table driven
interfaces and they were called Open Interfaces. Later, stored procedures were added and were called APIs.

GL Interface
Interface tables:
GL_INTERFACE

Base tables:
GL_JE_HEADERS
GL_JE_LINES
GL_JE_BACTHES

Concurrent Program: Journal Import
Journal Posting --- populates GL_BALANCES

Validations: check SOB, journal source name, journal category name, actual flag
A – actual amounts
B – budget amounts
E – encumbrance amount
If u enter E in the interface table, then enter appropriate encumbrance ID.
B – budget id.
Check if accounting date or GL date based period name is valid (i.e., not closed).
Check if accounting date falls in open or future open period status.
Check chart of accounts id based on Sob id.
Check if valid code combination.
Check if ccid is enabled.
Check if record already exists in GL interface table.
Check if already journal exists in GL application.
Validations for the staging table:
Check if the input data file is already uploaded into staging table.
Check if the record already exists in the interface table.
Check if the journal already exists in the GL application.

Staging Table:
Create table XX_GL_RY_STG
(status varchar2(50),
set_of_books_id number(15),
User_JE_Source_name varchar2(25),
user_je_category_name varchar2(25),
currency_code varchar2(15),
actual_flag char(1),
ACCOUNTING_DATE date,
DATE_CREATED date,
CREATED_BY number(15),
entered_dr number,
entered_cr number,
accounted_dr number,
accounted_cr number,
segment1 varchar2(25),
segment2 varchar2(25),
segment3 varchar2(25),
segment4 varchar2(25),
segment5 varchar2(25)
);
insert into XX_GL_RY_STG values(
'NEW',1,'Manual' ,'Adjustment','USD','A','20-MAR-2009' ,'20-MAR-2009', 2000,2000,2000,2000,
'01','000','9950','2080','000','0')
Package:
CREATE OR REPLACE PACKAGE XX_GL_INT_RY_PKG
IS
PROCEDURE xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2);
END;
CREATE OR REPLACE Package body XX_GL_INT_RY_PKG
is
Procedure xx_gl_int_prc(errbuf out varchar2,Retcode out varchar2)
is
cursor GL_CUR
IS
SELECT Status,set_of_books_id,User_JE_Source_name,
user_je_category_name,currency_code,actual_flag,
ACCOUNTING_DATE,DATE_CREATED,CREATED_BY,entered_dr,
entered_cr,accounted_dr,accounted_cr,
segment1, segment2, segment3, segment4, segment5
FROM XX_GL_RY_STG;
lv_status varchar2(50);
lv_sob_id Number(15);
lv_user_je_source_name varchar2(25);
lv_user_je_category_name varchar2(25);
lv_cur_code varchar2(15);
lv_actual_flag varchar2(1);
lv_err_flag varchar2(2);
lv_flag varchar2(2);
BEGIN
FOR rec in GL_CUR
LOOP
lv_flag := 'A';
lv_err_flag := 'A';
BEGIN
SELECT distinct Status into lv_status from XX_GL_RY_STG Where status = 'NEW';
EXCEPTION
When no_data_found Then
lv_status := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The status is not correct so change the status');
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_status );
END;
BEGIN
SELECT set_of_books_id into lv_sob_id from gl_sets_of_books
where set_of_books_id=rec.set_of_books_id;
Exception
When no_data_found Then
lv_sob_id:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOB is not correct change SOB ID');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data is inserting'|| lv_sob_id );
BEGIN
SELECT user_je_source_name into lv_user_je_source_name FROM GL_JE_SOURCES
WHERE user_je_source_name=rec.user_je_source_name;
EXCEPTION
WHEN no_data_found THEN
lv_user_je_source_name := NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The SOURCE NAME is not correct change It');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_source_name );
BEGIN
SELECT user_je_category_name INTO lv_user_je_category_name FROM GL_JE_CATEGORIES
where user_je_category_name=rec.user_je_category_name;
EXCEPTION
When no_data_found Then
lv_user_je_category_name:=NULL;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Category name is not correct Change it');
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_user_je_category_name );
END;
BEGIN
SELECT currency_code into lv_cur_code from FND_CURRENCIES
where currency_code=rec.currency_code;
Exception
When no_data_found Then
lv_cur_code:=null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency code is not correct ');
End;
FND_FILE.PUT_line(FND_FILE.LOG,'The data inserting is'|| lv_cur_code);
BEGIN
SELECT ACTUAL_FLAG into lv_actual_flag from XX_GL_RY_STG
where actual_flag in ('A','B','E');
Exception
When no_data_found then
lv_actual_flag := null;
lv_flag := 'E';
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Flag is not correct');
END;
FND_FILE.PUT_line(FND_FILE.LOG,'The dat inserting is... '|| lv_actual_flag);
IF lv_flag = 'A' THEN
INSERT into GL_INTERFACE (
STATUS, SET_OF_BOOKS_ID, USER_JE_SOURCE_NAME ,USER_JE_CATEGORY_NAME,
CURRENCY_CODE,ACTUAL_FLAG,
ACCOUNTING_DATE, DATE_CREATED,CREATED_BY, ENTERED_DR,ENTERED_CR,
ACCOUNTED_DR,ACCOUNTED_CR,segment1, segment2, segment3, segment4, segment5)
VALUES (
lv_Status, lv_sob_id, lv_User_JE_Source_name, lv_user_je_category_name,
lv_cur_code,lv_actual_flag,rec.ACCOUNTING_DATE, rec.DATE_CREATED,
1318,rec.entered_dr, rec.entered_cr, rec.accounted_dr,rec.accounted_cr,
rec.segment1, rec.segment2, rec.segment3, rec.segment4, rec.segment5);
END IF;
lv_flag :=null;
lv_err_flag:=null;
END LOOP;
COMMIT;
End;
END XX_GL_INT_RY_PKG;
/

Base tables for GL Daily Rates are—
Gl_DAILY_RATES

Interface table for GL Daily Rates are—
Gl_DAILY_RATES_INTERFACE

Moving the Data from Flat File to Base Table using SQL * LOADER:
Options (Skip =0)
Load data
infile '/ebs/oracle/apps/apps_st/appl/gl/12.0.0/bin/gl_daily_rates.csv'
Insert into table GL_daily_rates_stg
fields terminated by ','
optionally enclosed by '"'
Trailing nullcols
(From_currency ,To_currency, From_conversion_date, To_conversion_date,
User_conversion_type, conversion_rate, Mode_flag)

Moving the data from Staging tables to Base Tables using
Standard Interface Programs:
Create a Staging table based on the requirement
CREATE TABLE XXGL_DRATES_STG (
FROM_CURRENCY VARCHAR2(15),
TO_CURRENCY VARCHAR2(15),
FROM_CONVERSION_DATE DATE,
TO_CONVERSION_DATE DATE,
USER_CONVERSION_TYPE VARCHAR2(30),
CONVERSION_RATE NUMBER,
MODE_FLAG CHAR(1));

Inserting Data into Staging Table:
Insert into XXGL_DRATES_STG Values (
'USD','INR','29-Jan-2009','31-Jan-2009','Corporate','50','I');

Create a Package with validations to move the data into Interface Tables
CREATE OR REPLACE PACKAGE XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number,errbuff out varchar2);
END;
CREATE OR REPLACE PACKAGE BODY XXGL_DRATES_PKG
is
PROCEDURE DAILY_RATES_PRC(retcode out number, errbuff out varchar2)
Is
Cursor cur_drates is
Select FROM_CURRENCY, TO_CURRENCY, FROM_CONVERSION_DATE , TO_CONVERSION_DATE ,
USER_CONVERSION_TYPE, CONVERSION_RATE , MODE_FLAG FROM XXGL_DRATES_STG;
LV_FROM_CURRENCY VARCHAR2(15);
LV_TO_CURRENCY VARCHAR2(15);
LV_USER_CONVERSION_TYPE VARCHAR2(30);
LV_CONVERSION_RATE NUMBER;
LV_ERR_FLAG VARCHAR2(1):= 'A';
BEGIN
FOR i IN CUR_DRATES
LOOP
BEGIN
Select CURRENCY_CODE into LV_FROM_CURRENCY FROM
FND_CURRENCIES where CURRENCY_CODE=i.FROM_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_FROM_CURRENCY );
BEGIN
Select CURRENCY_CODE into LV_TO_CURRENCY
FROM FND_CURRENCIES where ENABLED_FLAG='Y'
AND CURRENCY_CODE=i.To_CURRENCY;
Exception
When NO_DATA_FOUND Then
lv_from_currency := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code is not defined
/not enabled if not enabled enable it.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The Currency Code inserting IS--'
|| LV_TO_CURRENCY );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
BEGIN
Select USER_CONVERSION_TYPE into LV_USER_CONVERSION_TYPE
FROM GL_DAILY_CONVERSION_TYPES where
USER_CONVERSION_TYPE=i.USER_CONVERSION_TYPE;
Exception
When NO_DATA_FOUND Then
LV_USER_CONVERSION_TYPE := null;
lv_err_flag := 'E';
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE is not defined.');
end;
FND_FILE.PUT_line(FND_FILE.LOG,'The USER_CONVERSION_TYPE inserting IS--'
||LV_USER_CONVERSION_TYPE );
IF LV_ERR_FLAG='A' THEN
INSERT INTO GL_DAILY_RATES_INTERFACE (
FROM_CURRENCY, TO_CURRENCY,
FROM_CONVERSION_DATE, TO_CONVERSION_DATE,
USER_CONVERSION_TYPE, CONVERSION_RATE,
MODE_FLAG)
VALUES (
LV_FROM_CURRENCY,LV_TO_CURRENCY,
I.FROM_CONVERSION_DATE, I.TO_CONVERSION_DATE
, LV_USER_CONVERSION_TYPE, I.CONVERSION_RATE
, I.MODE_FLAG);
END IF;
END LOOP;
COMMIT;
END;
END XXGL_DRATES_PKG;

Create an Executable – XXGL_DRATES_PKG_EXEC
Execution File
Create a Concurrent program – XXGL_DRATES_PKG_EXEC IFace Conc prg
Add the Conc program to the Request group
In custom module, Run the Conc Program thro’ SRS Window.
In GL MODULE Run the Standard Concurrent Program –
Program - Daily Rates Import and Calculation



How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...