Friday, May 19, 2017

XML BI/PUBLISHER

Steps to Create XML/BI Publisher


*********************************************************************************
1.Connect to Oracle Reports build a Query Manually and Connect to Apps
2.Next save the file in the .rdf format
3. FTP  the file /apps/aptest/VIS/apps/apps_st/appl/po/12.0.0/reports/US
4.register in Oracle as Oracle reports under purchasing application depending upon the folder setup.
5.then assign it to concurrent program request group Responsibility and run through SRS window once it is complted normal XML tags are created in Output file.
6. Then save the page as .XML format and Save it.
7. Then Load the XML data toMSWord through Add ins and load XML data.
8. Then next insert all fields and validate them and you recieve message no error found
9. Then next save the file as .rtf format and register them in Oracle XML System administrator got to data definition and create a new data definition and with code as Concurrent Program Short name and Once created.
10. Now we need to create the Template for it Create template and fill all the details and add the data definition created intially to the template.
11.Next Browse and attach the .rtf file that has been created through the MS word.
12.And apply now you get AS template Successfully Created.
13.Now Again the run Concurrent Job through SRS window.
14. If some times if it doesnot provide the output, Then attach the XML Publisher report request set in Request Group and run the Concurrent Program through the XMLPublisher report.
15. Then you find the Output in the Outputfile..

*********************************************************************************

Make Sure always the Parameter Passed to the report Should have the Same Name as Token name in the Concurrent Program


Select a.empno,a.ename,a.job,a.sal,b.deptno from Emp a,Dept b where
a.deptno=b.deptno

and  b.deptno=:DNO
Rest of the Process as Usaual.....

MULTI LAYOUT REPORT USING XML PUBLISHER


1.)First and Foremost Connect to report builder, Then Create 2 select statements and Create Q1 and Q2,
2.) Inorder to pass the parameter add a User parameter with a datatype and LOV.
3.) Then next select a formula Column in order to return a Parameter that has been Passed, we generally create a formula Column So that, a tag is created for the parameter , SO that that Tag Can be used in the rtf file in future for IF Condition.
4.) Then once (.rdf) file is created then follow the USual Process and and Run through the SRS window Once the Output is generated then  save the Output file as .XML and then in MS Word ==>Load XML data===>follow the below diagrams Once then rtf fiole Successfully generated then we can register in Oracle Apps through XML publisher Administrator and define the Data definition and Create the template Once the template is Created, Run the Program from SRS window by Passing the Parameters and then next run the Supporting Proge=am Called XML Report Publisher then we are good to go :)










*********************************************************************************
















Posted by at No comments:

Tuesday, May 16, 2017

TRIGGERS IN PL/SQL



Triggers

  Trigger is also same as stored procedure & also it will automatically invoked whenever DML

 Operation performed against table or view.

  There are two types of triggers supported by PL/SQL.

 1) Statement Level Trigger

 2) Row Level Trigger

  In Statement Level Trigger, Trigger body is executed only once for DML Statements.

  In Row Level Trigger, Trigger body is executed for each and every DML Statements.

 Syntax : create { or replace } trigger trigger_name

 before / after trigger event

 insert / update / delete on table_name

 { for each row }

 { where condition }

 { declare }

 variable declarations, cursors

 begin

 -----

 end;

 Execution order in Triggers

 1 ) Before Statement Level

 2 ) Before Row Level

 3 ) After Row Level

 4 ) After Statement Level

 1) Statement Level Trigger

  In Statement Level Trigger, Trigger body is executed only once for each DML

 Statement. Thats why generally statement level triggers used to define type based

 H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com

 Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333

 ================================================================================================

 condition and also used to implement auditing reports. These triggers does not contain

 new, old qualifiers.

 Q) Write a pl/sql statement level trigger on emp table not to perform DML Operations in

 saturday and sunday?

 Program) Create or replace trigger tr1 before insert or update or delete on tt

 begin

 if to_char(sysdate,'DY') in ('SAT','SUN')

 then

 raise_application_error(-20123,'we can not perform DMLs on sat and sunday');

 end if;

 end;

 Q) Write a pl/sql statement level trigger on emp table not to perform DML Operation on last

 day of the month?

 Program ) create or replace trigger tt2 before insert or update or delete on tt

 begin

 if sysdate=last_day(sysdate) then

 raise_application_error (-20111,'we can not perform dml operations on lastday ');

 end if;

 end;

 Trigger Event ( or ) Trigger Predicate Clauses

  If you want to define multiple conditions on multiple tables then all database systems

 uses trigger events.

  These are inserting, updating, deleting clauses

  These clauses are used in either row level or statement level triggers.

 Syntax : if inserting then

 statements;

 elsif updating then

 statements;

 elsif deleting then

 statements;

 end if;

 Q ) Write a pl/sql statement level trigger on emp table not to perform any dml operation in any

 days using triggering event?

 Program ) create or replace trigger tr3 before insert or update or delete on tt

 begin

 if inserting then

 raise_application_error (-20121,'we can not perform inserting operation');

 elsif updating then

 raise_application_error (-20122,'we can not perfrom update operation');

 elsif deleting then

 H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com

 Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333

 ================================================================================================

 raise_application_error (-20123,'we can not perform deleting operation');

 end if;

 end;

 Ex : Create table test ( msg varchar2(100));

 create or replace trigger tr4 after insert or update or delete on tt

 declare

 a varchar2(50);

 begin

 if inserting then

 a := 'rows inserted';

 elsif updating then

 a := 'rows updated';

 elsif deleting then

 a := 'rows deleted';

 end if;

 insert into testt values (a);

 end;

 2) Row Level Trigger

  In Row Level Trigger, Trigger body is executed for each row for DML Statement, Thats

 why we are using for each row clause in trigger specification and also data internally

 stored in 2 rollback segment qualifiers are OLD & NEW

  These qualifiers are used in either trigger specification or in trigger body. when we are

 using these modifiers in trigger body we must use colon prefix in the qualifiers.

 Syntax - :old.column_name ( or ) :new.column_name.

  When we are using these qualifiers in when clause we are not allow to use colon infront

 of the qualifiers.

 Qualifier Insert Update Delete

 :new YES YES NO

 :old NO YES YES

  In Before Triggers, Trigger body is executed before DML Statements are effected into

 database.

  In After Triggers, Trigger body is executed after DML Statements are effected into

 database.

  Generally if we want to restrict invalid data entry always we are using before triggers,

 where as if we are performing operation on the one table those operations are effected in

 another table then we are using after trigger.

  Whenever we are inserting values into new qualifiers we must use before trigger

 otherwise oracle server returns an error.

 Q ) Write a PL/SQL Row Level Trigger on emp table whenever user inserting data into a emp

 table sal should be more than 5000?

 H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com

 Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333

 ================================================================================================

 Program ) Create or replace trigger t90 before insert on tb

 for each row

 begin

 if :new.sal<5000 then

 raise_application_error (-20123,'salary should be more than 5000');

 end if;

 end;

 Q ) Write a PL/SQL Row Level Trigger on emp, dept tables while implement on delete cascade

 concept without using on delete cascade clause?

 Program ) Create or replace trigger t1

 after delete on dept

 for each row

 begin

 delete from emp where deptno=:old.deptno;

 end;

 Q ) Write a PL/SQL Row Level Trigger on dept table whenever updating deptno's in dept table

 automatically those deptno's modified into emp table?

 Program ) Create or replace trigger t19

 after update on dept

 for each row

 begin

 update emp set deptno=:new.deptno where deptno=:old.deptno;

 end;

 Q ) Write a PL/SQL Row Level Trigger whenever user inserting data into ename column after

 inserting data must be converted into uppercase ?

 Program ) create or replace trigger t21

 before insert on emp

 for each row

 begin

 :new.ename:=upper(:new.ename);

 end;

 Q ) Write a PL/SQL Row Level Trigger on emp table by using below conditions?

 1 ) whenever user inserting data those values stored in another table

 2 ) whenever user updating data those values stored in another table

 3 ) whenever user deleting data those values stored in another table

 Program ) First we create 3 tables which are having the same structure of emp table.

 Create or replace trigger te1

 after insert or update or delete on t01

 for each row

 begin

 H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com

 Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333

 ================================================================================================

 if inserting then

 insert into e1(empno,ename) values (:new.empno,:new.ename);

 elsif updating then

 insert into e2(empno,ename) values (:old.empno,:old.ename);

 elsif deleting then

 insert into e3(empno,ename) values (:old.empno,:old.ename);

 end if;

 end;

 Q ) Write a PL/SQL Trigger on emp table whenever user deleting records from emp table

 automatically display remaining number of existing record number in bottom of the delete

 statment?

 Program ) Create or replace trigger tp1 after delete on emp

 declare

 a number(10);

 begin

 select count(*) into a from emp;

 dbms_output.put_line('remaining records are: '||a);

 end;

 Mutating Trigger

 Ex : Create or replace trigger tp1 after delete on emp

 for each row

 declare

 a number(10);

 begin

 select count(*) into a from emp;

 dbms_output.put_line('remaining records are: '||a);

 end;

  Into a Row Level Trigger based on a table trigger body can not read data from same

 table and also we can not perform DML Operations on same table.

  If we are trying to this oracle server returns an error is table is mutating.

  This Error is called Mutating Error

  This Trigger is called Mutating Trigger

  This Table is called Mutating Table

  Mutating Errors are not accured in Statement Level Trigger Because through these

 Statement Level Trigger when we are performing DML Operations automatically data

 Committed into database.

  Where as in Row Level Trigger when we are performing transaction data is not

 committed and also again we are reading this data from the same table then only

 mutating error is accured.

  To avoid this mutating error we are using autonomous transaction in triggers.

 H- No: 100/B, Ground Floor, Near S.R.Nagar Community Hall, S.R.Nagar, Hyderabad – 500 038, Web : www.k-onlines.com

 Contact Ph No => Land Line : 040 – 42221320 / 65530333, Mobile : 91-8143900333

 ================================================================================================

 Ex Create or replace trigger tp1 after delete on t01

 for each row

 declare

 pragma autonomous_transaction;

 a number(10);

 begin

 select count(*) into a from t01;

 dbms_output.put_line('remaining records are: '||a);

 commit;

 end;

 DDL Triggers

  We can also create triggers on schema level, database level. These types of triggers are

 called DDL Triggers or System Triggers.

  These types of triggers are created by database administrator.

 Syntax : Create or replace trigger trigger_name

 Before / After

 Create / Alter / Drop / Truncate / Rename

 On Username.Schema

 Q ) Write a PL/SQL Trigger on scott schema not to drop emp table?

 Program ) Create or replace trigger td

 before drop on apps.schema

 begin

 if ora_dict_obj_name = 'T100' and

 ora_dict_obj_type = 'TABLE' then

 raise_application_error(-20121,'we can not drop this table');

 end if;
end;






Create or Replace trigger Trigger_test 
before insert or update or delete  on Siddharth 
begin
if inserting then
Raise_Application_Error (-2001, 'Then you cannot make insertion on this table');-- User defined error with own Error number and message
elsif updating then
Raise_Application_Error (-2002, 'Then you cannot make updates on this table');
elsif deleting then  
Raise_Application_Error (-2002, 'Then you cannot make updates on this table');
end if;
end;
/














CREATE OR REPLACE TRIGGER LOD_PUBPRTPMO_LOD_ID_TRIG BEFORE INSERT OR UPDATE ON LOD_PUBPRTPMO
FOR EACH ROW
DECLARE 
v_newVal NUMBER(12) := 0;
v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.LOD_ID IS NULL THEN
    SELECT  LOD_PUBPRTPMO_LOD_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    -- If this is the first time this table have been inserted into (sequence == 1)
    IF v_newVal = 1 THEN 
      --get the max indentity value from the table
      SELECT NVL(max(LOD_ID),0) INTO v_newVal FROM LOD_PUBPRTPMO;
      v_newVal := v_newVal + 1;
      --set the sequence to that value
      LOOP
           EXIT WHEN v_incval>=v_newVal;
           SELECT LOD_PUBPRTPMO_LOD_ID_SEQ.nextval INTO v_incval FROM dual;
      END LOOP;
    END IF;
   -- assign the value from the sequence to emulate the identity column
   :new.LOD_ID := v_newVal;
  END IF;
END;
/



CREATE OR REPLACE TRIGGER LOD_PUBLISHER_ID_TRIG BEFORE INSERT OR UPDATE ON LOD_PUBLISHER
 FOR EACH ROW
DECLARE
 v_newVal NUMBER(12) := 0;
 v_incval NUMBER(12) := 0;
BEGIN
  IF INSERTING AND :new.LOD_ID  IS NULL THEN    
    SELECT  LOD_PUBLISHER_ID_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
    :new.LOD_ID := v_newVal;  
  END IF;  
 END;
/


Posted by at No comments:

Friday, May 5, 2017

GL Journal Enter









Once the balnces are posted then data get effected into GL_balances Table................................



Select * from GL_JE_BATCHES
Select * from GL_JE_HEADERS
Select * from GL_JE_LINES
Select * from GL_BALANCES






















Posted by at No comments:

Wednesday, April 26, 2017

Lookups , Value sets, Profile Options,Request Sets





















SELECT * FROM FND_LOOKUP_TYPES_VL WHERE LOOKUP_TYPE LIKE 'SIDDHARTH_TEST%'


SELECT * FROM FND_LOOKUP_VALUES WHERE LOOKUP_TYPE LIKE 'SIDDHARTH_TEST%'-- Go With this query this help in finding the lookupcode and Meaning.....


Profile Option
===========



SELECT FND_PROFILE.VALUE('XXX_profile') FROM DUAL


How to do it...

To create a request set using the wizard complete the following tasks:
  1. 1. Log in to Oracle with the System Administrator responsibility.
  2. 2. Navigate to Requests | Set and the Request Set window will open, as shown in the following screenshot:
  3. 3. Click the Request Set Wizard button.
  4. 4. Select the radio button called Sequentially (One After Another) and then click Next, as shown in the following screenshot:
  5. 5. Click on radio button called Continue Processing. This is what we want the request set to do if any of the programs end with a status of Error:
  6. 6. We now need to enter the details of our request set in the wizard as shown in the following table and click Next:
    Item name
    Item value
    Set
    XXHR20001
    Application
    XXHR Custom Application
    Description
    XXHR Employee By Organization
  7. 7. We now want to print the output files as each request finishes, so select As Each Request in the Set Completes and click Next as follows:
  8. 8. Now add the concurrent programs that we want to run in the request set so add the two programs we have created, XXHR First Concurrent Program and XXHR Second Concurrent Program, and click Finish as shown in the following screenshot:
  9. 9. The following message will appear; click OK:
  10. 10. The request set is then automatically created and the completed set will appear something similar to the following screenshot. We are going to first look at the Define Stages screen and the Link Stages screen to check the configuration:
  11. 11. Click on the Define Stages button to check that the concurrent programs are configured as required:
  12. 12. Check that the screen is configured as we expected and then close the Stages window to navigate back to the Request Set window.
  13. 13. Click on the Link Stages button and the Link Stages window will open as shown in the following screenshot:

How it works...

We have now created a request set using the request set wizard. We can now run the request set, and the concurrent programs will run as we have defined them in the request set.

Add a request set to a request group

We will now add our request set to the request group we have associated with the XXEBS Extending e-Business Suite responsibility.

How to do it...

To add the request set perform the following steps:
  1. 1. Log in to Oracle with the System Administrator responsibility.
  2. 2. Navigate to Security | Responsibility | Request and the Request Groups window will open.
  3. 3. Query back the XXHR Request Group request group.
  4. 4. Now we are going to add the request set we created in the Requests region. Enter data as in the following table in the Requests block:
    Type
    Name
    Application
    Set
    XXHR20001
    XXHR Custom Application
  5. 5. Click the Save button in the toolbar (or Ctrl + S) to save the record.
  6. 6. Exit the form.

How it works...

Okay so now we have added the request set to our request group. Next we are going to run the request set.

Run the request set

Now we want to run the concurrent request set.

How to do it...

To run the request set take the following steps:
  1. 1. Log in to Oracle with the XXEBS Extending e-Business Suite responsibility.
  2. 2. Navigate to Submit Requests and click the OK button as shown in the following screenshot:
  3. 3. Navigate to the Request Set field and select the XXHR20001 request set from the list of values and click OK.
  4. 4. Click on the Submit button and when prompted to submit a new request select No and the form will close down.
  5. 5. Navigate to View Requests and click on the Find button (to find all requests) and you will see the request set as shown in the following screenshot:
  6. 6. You should see that the request set we just submitted is running.
If you click the refresh button you will see the stages of the request set complete as they are executed. Once the request set has completed you will see three records in the Requests block. One for the set and one each for the concurrent programs in the request set as shown in the following screenshot:

Note

Note: Remember to click the refresh button as the page does not refresh automatically, so if you see a program still has a phase of Running you will need to click the refresh button until the phase is Completed.

How it works...

We have now run the request set and can see that we can

 VALUE SETS
============





Profile Option===========


















Posted by at No comments:

Outbound Interfacing with Exceptions

CREATE OR REPLACE PROCEDURE OUTBOUND_INTERFACE(ERRBUF VARCHAR2, RETCODE VARCHAR2) IS
CURSOR C1 IS SELECT VENDOR_NAME,VENDOR_ID,VENDOR_TYPE_LOOKUP_CODE FROM PO_VENDORS;
V_OUTFILE                  UTL_FILE.FILE_TYPE;
V_OUTPATH                  VARCHAR2(100);
V_OUTFILENAME              VARCHAR2(200);
V_HEADER                   VARCHAR2(400);
V_LINE                     VARCHAR2(4000);
BEGIN
FND_PROFILE.GET ('CMG_ABC_INCOMING_PATH', V_OUTPATH);
V_OUTFILENAME :='ENS_EXCEL_FEED' || '.csv';
V_OUTFILE := UTL_FILE.FOPEN (V_OUTPATH, V_OUTFILENAME, 'w');
V_HEADER :='VENDOR_NAME'||','||'VENDOR_ID'||','||'VENDOR_TYPE_LOOKUP_CODE';
UTL_FILE.PUT_LINE (V_OUTFILE, V_HEADER);
FOR UPC_REC IN C1 LOOP
V_LINE:=UPC_REC.VENDOR_NAME||','||UPC_REC.VENDOR_ID||','||UPC_REC.VENDOR_TYPE_LOOKUP_CODE;
UTL_FILE.PUT_LINE (V_OUTFILE, V_LINE);
END LOOP;
UTL_FILE.FCLOSE (V_OUTFILE);
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID OPERATION');
WHEN UTL_FILE.INVALID_PATH THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INVALID FILE');
WHEN UTL_FILE.READ_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'READ ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'INTERNAL ERROR');
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'OTHER ERROR');
END;
/



/HDGSTG01/APPLR11I/COMMON/ABC/INCOMING
SELECT FND_PROFILE.VALUE('CMG_ABC_INCOMING_PATH') FROM DUAL
Posted by at No comments:

Tuesday, April 18, 2017

Merge Statement

MERGE INTO CMG_PPA_SUBSIDIARY_LEDGER_TBL  hra
USING (
     select AMOUNT,CMG_PPA_SUB_LEDGER_ID SUB from
       CMG_PPA_SUBSIDIARY_LEDGER_TBL
         where vendor_id=143) main1
ON (hra.CMG_PPA_SUB_LEDGER_ID =main1.SUB)
WHEN MATCHED THEN
UPDATE SET
hra.VENDOR_func_amount = main1.AMOUNT
WHEN NOT MATCHED THEN
INSERT ( hra.VENDOR_func_amount) VALUES (main1.AMOUNT);
Commit;


MERGE INTO CMG_PPA_REMITTANCES_DETAIL_TBL  hra
USING (
     select AMOUNT,REMITTANCE_DETAIL_ID RDI from
            CMG_PPA_REMITTANCES_DETAIL_TBL
         where issue_dim_id in (select issue_dim_id from CMGPPA.CMG_PPA_ISSUE_DIM_TBL where ISS_PUB_ID=143))main1
ON (hra.REMITTANCE_DETAIL_ID=main1.RDI)
WHEN MATCHED THEN
UPDATE SET
hra.CMG_FUNC_AMOUNT = main1.AMOUNT
WHEN NOT MATCHED THEN
INSERT ( hra.CMG_
Posted by at No comments:

Thursday, March 30, 2017

GL INTERFACING (JOURNAL IMPORT PROGRAM)

CREATE OR REPLACE PROCEDURE PROCEDURE_VALIDATION(ERRBUF VARCHAR2,RETCODE VARCHAR2) AS
ERROR_FLAG VARCHAR2(10):='N';
V_ERROR_MSG VARCHAR2(200);
V_ERROR_CODE VARCHAR2(200);
V_CURRENCY_CODE VARCHAR2(20);
V_COMBINATION  VARCHAR2(200);
V_SOURCE_NAME   VARCHAR2(200);
V_BOOKS_ID    NUMBER(20);
CATEGORY_NAME VARCHAR2(200);
V_CREATED_BY  NUMBER(20);
L_COUNT NUMBER(25):=0;
CURSOR C1 IS SELECT * FROM GL_STAGE;
BEGIN
FOR I IN C1 LOOP
L_COUNT:=L_COUNT+1;
BEGIN
IF (I.STATUS='Y') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Status is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Status is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;


BEGIN
IF (I.ACCOUNTING_DATE)<SYSDATE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is  is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
IF(I.ACTUAL_FLAG='A') THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'actual flag is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'actual flag is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
SELECT  (CURRENCY_CODE) INTO V_CURRENCY_CODE FROM FND_CURRENCIES_TL WHERE CURRENCY_CODE=I.CURRENCY_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'currency code column FOR THE RECORD IS MISSING PLEASE UPDATE THE currency code COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many Currency codes');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'currency code is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT (USER_JE_CATEGORY_NAME) INTO CATEGORY_NAME FROM GL_JE_CATEGORIES_TL WHERE USER_JE_CATEGORY_NAME=I.USER_JE_CATEGORY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CATEGORY COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE CATEGORY COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOO MANY CATEGORY_NAMES');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CATEGORY IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT USER_JE_SOURCE_NAME INTO V_SOURCE_NAME FROM GL_JE_SOURCES_TL WHERE USER_JE_SOURCE_NAME=I.USER_JE_SOURCE_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SOURCE NAME COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE CREATED BY COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOO MANY SOURCES_NAMES');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SOURCE NAME IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT distinct (CREATED_BY) INTO V_CREATED_BY FROM FND_USER WHERE CREATED_BY=I.CREATED_BY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'created by Column FOR THE RECORD IS MISSING PLEASE UPDATE THE created by COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many created_by');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'created by is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
IF I.DATE_CREATED<=SYSDATE THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'date created is valid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
ELSE
ERROR_FLAG :='Y';
FND_FILE.PUT_LINE(FND_FILE.LOG,'accounting date is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END IF;
END;

BEGIN
SELECT  (SET_OF_BOOKS_ID) INTO V_BOOKS_ID FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=I.SET_OF_BOOKS_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'set of books id column FOR THE RECORD IS MISSING PLEASE UPDATE THE set of books id COLUMN');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many category_names');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'set of books id is invalid');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'The error code and error msg:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

BEGIN
SELECT  DISTINCT (SEGMENT1||SEGMENT2||SEGMENT3||SEGMENT4||SEGMENT5) INTO V_COMBINATION FROM GL_CODE_COMBINATIONS WHERE SEGMENT1=I.SEGMENT1 AND SEGMENT2=I.SEGMENT2 AND  SEGMENT3=I.SEGMENT3 AND SEGMENT4=I.SEGMENT4 AND SEGMENT5=I.SEGMENT5;
EXCEPTION
WHEN NO_DATA_FOUND THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'SEGMENTS COLUMN FOR THE RECORD IS MISSING PLEASE UPDATE THE SEGMENTS');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN TOO_MANY_ROWS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'too many category_names');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'CODE COMBINATION IS INVALID');
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
V_ERROR_CODE:=SQLCODE;
V_ERROR_MSG:=SQLERRM;
FND_FILE.PUT_LINE(FND_FILE.LOG,'THE ERROR CODE AND ERROR MSG:'||V_ERROR_CODE||','||V_ERROR_MSG);
FND_FILE.PUT_LINE(FND_FILE.LOG,L_COUNT);
END;

IF (ERROR_FLAG='N') THEN
INSERT INTO GL_INTERFACE
(STATUS,
SET_OF_BOOKS_ID,
ACCOUNTING_DATE,
CURRENCY_CODE,
DATE_CREATED,
CREATED_BY,
ACTUAL_FLAG,
USER_JE_CATEGORY_NAME,
USER_JE_SOURCE_NAME,
SEGMENT1,
SEGMENT2,
SEGMENT3,
SEGMENT4,
SEGMENT5,
ENTERED_DR,
ENTERED_CR,
ACCOUNTED_DR,
ACCOUNTED_CR
) VALUES
(I.STATUS,
V_BOOKS_ID,
I.ACCOUNTING_DATE,
V_CURRENCY_CODE,
I.DATE_CREATED,
V_CREATED_BY,
I.ACTUAL_FLAG,
CATEGORY_NAME,
V_SOURCE_NAME,
I.SEGMENT1,
I.SEGMENT2,
I.SEGMENT3,
I.SEGMENT4,
I.SEGMENT5,
I.ENTERED_DR,
I.ENTERED_CR,
I.ACCOUNTED_DR,
I.ACCOUNTED_CR);
END IF;
END LOOP;
END PROCEDURE_VALIDATION;
/


Then Once the Data is in GL_Interface then Run the Journal Import Program with Source name and the Group Id then data comes onto GL_JE_HEADERS,GL_JE_LINES and GL_JE_batches
and once you post the balances then we can have data on GL_Balances.







https://docs.oracle.com/cloud/latest/financialscs_gs/OEDMF/GL_INTERFACE_tbl.htm







GL_INTERFACE

GL_INTERFACE contains journal entry batches through Journal Import. You insert rows in this table and then use the Import Journals window to create journal batches. You must supply values for all NOT NULL columns. For a complete description of how to load this table, see the Oracle General Ledger User Guide.

Details

  • Schema: FUSION
  • Object owner: GL
  • Object type: TABLE
  • Tablespace: APPS_TS_TX_DATA

Columns

NameDatabaseLengthPrecisionNot NullCommentsFlexfield Mapping
STATUSVARCHAR250YesJournal Import status. Use: NEW.
GL_INTERFACE_IDNUMBER18Interface Identifier. Oracle internal use only. Populated by the journal import program.
CREATION_DATETIMESTAMPWho column: date and time of the creation of the row.
LAST_UPDATE_DATETIMESTAMPWho column: date and time of the last update of the row.
LAST_UPDATE_LOGINVARCHAR232Who column: session login associated to the user who last updated the row.
LAST_UPDATED_BYVARCHAR264Who column: user who last updated the row.
OBJECT_VERSION_NUMBERNUMBER9Used to implement optimistic locking. Incremented every time the row is updated. Compared at the start and end of a transaction to detect whether another session has updated the row since it was queried.
LEDGER_IDNUMBER18Ledger identifier. Use the Manage Primary Ledgers task to find valid values.
JE_SOURCE_NAMEVARCHAR225Oracle internal use only. Use column USER_JE_SOURCE_NAME to populate journal source.
JE_CATEGORY_NAMEVARCHAR225Oracle internal use only. Use column USER_JE_CATEGORY_NAME to populate journal category.
ACCOUNTING_DATEDATEYesEffective date of the journal entry. Used to assign the accounting period.
CURRENCY_CODEVARCHAR215YesEntered currency of the transaction. Use the Manage Currencies task to find valid values. Use the three character ISO currency code. Example: US Dollars is USD.
DATE_CREATEDDATEYesWho column: date the row was created.
CREATED_BYVARCHAR264Who column: user who created the row.
ACTUAL_FLAGVARCHAR21YesBalance type of the journal. Use: A. Meaning: Actual.
REQUEST_IDNUMBER18Enterprise Service Scheduler: request ID of the job that created or last updated the row.
ENCUMBRANCE_TYPE_IDNUMBEROracle internal use only.
BUDGET_VERSION_IDNUMBEROracle internal use only.
CURRENCY_CONVERSION_DATEDATEDate of exchange rate. Date format: YYYY/MM/DD. Required if CURRENCY_CONVERSION_TYPE is not User.
CURRENCY_CONVERSION_TYPEVARCHAR230Currency conversion type. Use Manage Currency Conversion Types task to identify valid values. For Fusion ERP in the Cloud, use USER_CURRENCY_CONVERSION_TYPE instead.
CURRENCY_CONVERSION_RATENUMBERForeign currency exchange rate. Mandatory if CURRENCY_CONVERSION_TYPE is User.
SEGMENT1VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT2VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT3VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT4VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT5VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT6VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT7VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT8VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT9VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT10VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT11VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT12VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT13VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT14VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT15VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT16VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT17VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT18VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT19VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT20VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT21VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT22VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT23VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT24VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT25VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT26VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT27VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT28VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT29VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
SEGMENT30VARCHAR225Segment of the chart of accounts. Only use if assigned to the chart of accounts of the ledger. Validation: must be a valid value for the chart of accounts.
ENTERED_DRNUMBERTransaction debit amount in the entered currency.
ENTERED_CRNUMBERTransaction credit amount in the entered currency.
ACCOUNTED_DRNUMBERJournal debit amount in the ledger currency.
ACCOUNTED_CRNUMBERJournal credit amount in the ledger currency.
TRANSACTION_DATEDATEOracle internal use only. Date of transaction.
REFERENCE1VARCHAR2100Reference column: batch name. Free text field. Not validated.
REFERENCE2VARCHAR2240Reference column: batch description. Free text field. Not validated.
REFERENCE3VARCHAR2100Oracle internal use only.
REFERENCE4VARCHAR2100Reference column: journal entry name. Free text field. Not validated.
REFERENCE5VARCHAR2240Reference column: journal entry description. Free text field. Not validated.
REFERENCE6VARCHAR2100Reference column: journal entry reference. Free text field. Not validated.
REFERENCE7VARCHAR2100Reference column: journal entry reversal flag. Valid values: Y, N.
REFERENCE8VARCHAR2100Reference column: journal entry reversal period. Validation: mandatory if REFERENCE7, journal entry reversal flag, is Y. If average balance processing is enabled, enter effective date for reversal. This will be used to determine the GL period.
REFERENCE9VARCHAR2100Reference column: journal reversal method. Valid values: Y, N. Meanings: Y changes sign, N switches debits/credits.
REFERENCE10VARCHAR2240Reference column: journal entry line description. Free text field. Not validated.
REFERENCE11VARCHAR2240Oracle internal use only.
REFERENCE12VARCHAR2100Oracle internal use only.
REFERENCE13VARCHAR2100Oracle internal use only.
REFERENCE14VARCHAR2100Oracle internal use only.
REFERENCE15VARCHAR2100Oracle internal use only.
REFERENCE16VARCHAR2100Oracle internal use only.
REFERENCE17VARCHAR2100Oracle internal use only.
REFERENCE18VARCHAR2100Oracle internal use only.
REFERENCE19VARCHAR2100Oracle internal use only.
REFERENCE20VARCHAR2100Oracle internal use only.
REFERENCE21VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE22VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE23VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE24VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE25VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE26VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE27VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE28VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE29VARCHAR2240Reference column: journal line. Free text field. Not validated.
REFERENCE30VARCHAR2240Reference column: journal line. Free text field. Not validated.
INTERFACE_RUN_IDNUMBER18Oracle internal use only.
JE_BATCH_IDNUMBER18Oracle internal use only.
PERIOD_NAMEVARCHAR215Period name. Use the Manage Accounting Calendars task to identify valid values.
JE_HEADER_IDNUMBER18Oracle internal use only.
JE_LINE_NUMNUMBER18Oracle internal use only.
CHART_OF_ACCOUNTS_IDNUMBER18Oracle internal use only. Chart of accounts identifier.
FUNCTIONAL_CURRENCY_CODEVARCHAR215Oracle internal use only. Ledger base currency.
CODE_COMBINATION_IDNUMBER18Use the Manage Account Combinations task, column Account ID, to find valid values. Can be used instead of populating the SEGMENT columns individually. If CODE_COMBINATION_ID and the columns beginning with SEGMENT are populated, the SEGMENT column values take precedence.
DATE_CREATED_IN_GLDATEOracle internal use only. Date journal import created batch. Populated by the journal import program.
WARNING_CODEVARCHAR24Oracle internal use only.
STATUS_DESCRIPTIONVARCHAR2240Oracle internal use only. Journal import status description. Populated by the journal import program.
STAT_AMOUNTNUMBERStatistical amount.
USER_JE_CATEGORY_NAMEVARCHAR225YesJournal entry category. Use the Manage Journal Categories task to find valid values.
USER_JE_SOURCE_NAMEVARCHAR225YesJournal entry source user defined name. Use the Manage Journal Sources page to find valid values.
USER_CURRENCY_CONVERSION_TYPEVARCHAR230Type of exchange rate. Use the Manage Conversion Rate Types task to find valid values. Translated value for CONVERSION_TYPE. Use either CURRENCY_CONVERSION_TYPE or USER_CURRENCY_CONVERSION_TYPE, but not both.
GROUP_IDNUMBER18Groups lines for journals. Use positive integers. Lines with the same GROUP_ID are grouped into the same journal.
SUBLEDGER_DOC_SEQUENCE_IDNUMBEROracle internal use only. Sequential numbering sequence defining column. Populated by journal import program when journal is sequenced.
SUBLEDGER_DOC_SEQUENCE_VALUENUMBEROracle internal use only. Sequential numbering sequence value. Populated by journal import program when journal is sequenced.
ATTRIBUTE1VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE2VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE3VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE4VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE5VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE6VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE7VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE8VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE9VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE10VARCHAR2150Segment value for Journals Lines descriptive flexfield.
ATTRIBUTE11VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE12VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE13VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE14VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE15VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE16VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE17VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE18VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE19VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE20VARCHAR2150Segment value for Journals Captured Information descriptive flexfield.
ATTRIBUTE_CATEGORYVARCHAR2150Context code for Journals Lines descriptive flexfield. Use the Manage General Ledger Descriptive Flexfields task to identify valid values. Use ATTRIBUTE1 to ATTRIBUTE10 for the segment values.
ATTRIBUTE_CATEGORY2VARCHAR2150Context code for Journals Captured Information descriptive flexfield. Use the Manage General Ledger Descriptive Flexfields task to identify valid values. Use ATTRIBUTE11 to ATTRIBUTE20 for the segment values.
INVOICE_DATEDATEOracle internal use only.
TAX_CODEVARCHAR215Oracle internal use only.
INVOICE_IDENTIFIERVARCHAR220Oracle internal use only.
INVOICE_AMOUNTNUMBEROracle internal use only.
ATTRIBUTE_CATEGORY3VARCHAR2150Oracle internal use only.
USSGL_TRANSACTION_CODEVARCHAR230Government transaction code. Oracle internal use only. Only applicable if Oracle Federal Financials is used.
DESCR_FLEX_ERROR_MESSAGEVARCHAR2240Oracle internal use only.
JGZZ_RECON_REFVARCHAR2240Oracle internal use only.
AVERAGE_JOURNAL_FLAGVARCHAR21Oracle internal use only.
GL_SL_LINK_IDNUMBERLink to associated subledger data. Oracle internal use only.
GL_SL_LINK_TABLEVARCHAR230Table containing associated subledger data. Oracle internal use only.
ORIGINATING_BAL_SEG_VALUEVARCHAR225Originating balancing segment value for intercompany transaction. Overrides default balancing segment value. Should be a valid value for value set used for intercompany.
REFERENCE_DATEDATEReference Date for sequencing to meet statutory requirements in Italy. Date format: YYYY/MM/DD.
SET_OF_BOOKS_IDNUMBER18Oracle internal use only.
BALANCING_SEGMENT_VALUEVARCHAR225Oracle internal use only.
MANAGEMENT_SEGMENT_VALUEVARCHAR225Oracle internal use only.
FUNDS_RESERVED_FLAGVARCHAR21Oracle internal use only.
CODE_COMBINATION_ID_INTERIMNUMBER18Oracle internal use only.
CURRENCY_CONV_DATE_INTERDATEOracle internal use only.
CURRENCY_CONV_TYPE_INTERVARCHAR230Oracle internal use only.
CURRENCY_CONV_RATE_INTERNUMBER18Oracle internal use only.
LOAD_REQUEST_IDNUMBER18Enterprise Service Scheduler: request ID of the interface load job that created the row.
LEGAL_ENTITY_IDNUMBER18Legal Entity Identifier. Foreign key to XLE_ENTITY_PROFILES
LEGAL_ENTITY_IDENTIFIERVARCHAR230Unique number used to identify a legal entity. Foreign key to XLE_ENTITY_PROFILES.LEGAL_ENTITY_IDENTIFIER.
LEDGER_NAMEVARCHAR230Ledger name for the journal to be imported. Used in file based data import.

Foreign Keys

TableForeign TableForeign Key Column
GL_INTERFACEGL_LEDGERSLEDGER_ID
GL_INTERFACEGL_JE_BATCHESJE_BATCH_ID
GL_INTERFACEGL_JE_HEADERSJE_HEADER_ID
GL_INTERFACEGL_JE_LINESJE_HEADER_ID, JE_LINE_NUM
GL_INTERFACEGL_CODE_COMBINATIONSCODE_COMBINATION_ID

Indexes


IndexUniquenessTablespaceColumn
GL_INTERFACE_N1Non UniqueDefaultUSER_JE_SOURCE_NAME, LEDGER_ID, SET_OF_BOOKS_ID, GROUP_ID
GL_INTERFACE_N2Non UniqueDefaultREQUEST_ID, JE_HEADER_ID, STATUS, CODE_COMBINATION_ID
GL_INTERFACE_N3Non UniqueDefaultSUBLEDGER_DOC_SEQUENCE_VALUE, SUBLEDGER_DOC_SEQUENCE_ID
GL_INTERFACE_N4Non UniqueDefaultREFERENCE26, REFERENCE22, REFERENCE23

https://bhaskarreddyapps.blogspot.com/2011/11/gl-interfaces.html






Posted by at No comments:
Subscribe to: Posts (Atom)