Israr Oracle: GL Interface Code

How to create the GL Interface in oracle apps

1. Create the staging table

CREATE TABLE XXWIPRO_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)

);

Output :- Table created.

2. Insert the data in XXWIRPO_STG

insert into XXWIPRO_STG values('NEW',2121,'Manual' ,'Adjustment','INR','A','03-OCT-2018' ,'03-OCT-2018' , 787,12,12,12,12,'1001','000','400102','000','000');

OUTPUT:-

3. Create the Package :-

CREATE OR REPLACE PACKAGE XXWIPRO_GL_INT_PKG
IS
PROCEDURE XXWIPRO_GL_INT_PROC(ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2);

----Create the package for specificaton ----
---- techical israr technical.israr@gmail.com

END;

OUTPUT :- Package created.

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

4. Create the Package Body :-

CREATE OR REPLACE PACKAGE BODY XXWIPRO_GL_INT_PKG

PROCEDURE XXWIPRO_GL_INT_PROC (ERRBUF OUT VARCHAR2, RETCODE OUT VARCHAR2)

----Create the package body ----

---- techical israr technical.israr@gmail.com

CURSOR GL_CUR

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 XXWIPRO_STG;

V_STATUS VARCHAR2 (50);

V_SOB_ID NUMBER (15);

V_USER_JE_SOURCE_NAME VARCHAR2 (25);

V_USER_JE_CATEGORY_NAME VARCHAR2 (25);

V_CUR_CODE VARCHAR2 (15);

V_ACTUAL_FLAG VARCHAR2 (1);

V_ERR_FLAG VARCHAR2 (2);

V_FLAG VARCHAR2 (2);

BEGIN

FOR REC IN GL_CUR

LOOP

V_FLAG := 'A';

V_ERR_FLAG := 'A';

BEGIN

SELECT DISTINCT STATUS

INTO V_STATUS

FROM XXWIPRO_STG

WHERE STATUS = 'NEW';

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_STATUS := NULL;

V_FLAG := 'E';

V_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' || V_STATUS);

END;

BEGIN

SELECT SET_OF_BOOKS_ID

INTO V_SOB_ID

FROM XXWIPRO_STG

WHERE SET_OF_BOOKS_ID = REC.SET_OF_BOOKS_ID;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_SOB_ID := NULL;

V_FLAG := 'E';

V_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' || V_SOB_ID);

BEGIN

SELECT USER_JE_SOURCE_NAME

INTO V_USER_JE_SOURCE_NAME

FROM GL_JE_SOURCES

WHERE USER_JE_SOURCE_NAME = REC.USER_JE_SOURCE_NAME;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_USER_JE_SOURCE_NAME := NULL;

V_FLAG := 'E';

V_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' || V_USER_JE_SOURCE_NAME);

BEGIN

SELECT USER_JE_CATEGORY_NAME

INTO V_USER_JE_CATEGORY_NAME

FROM GL_JE_CATEGORIES

WHERE USER_JE_CATEGORY_NAME = REC.USER_JE_CATEGORY_NAME;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_USER_JE_CATEGORY_NAME := NULL;

V_FLAG := 'E';

V_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' || V_USER_JE_CATEGORY_NAME);

END;

BEGIN

SELECT CURRENCY_CODE

INTO V_CUR_CODE

FROM FND_CURRENCIES

WHERE CURRENCY_CODE = REC.CURRENCY_CODE;

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_CUR_CODE := NULL;

V_FLAG := 'E';

V_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' || V_CUR_CODE);

BEGIN

SELECT ACTUAL_FLAG

INTO V_ACTUAL_FLAG

FROM XXWIPRO_STG

WHERE ACTUAL_FLAG IN ('A', 'B', 'E');

EXCEPTION

WHEN NO_DATA_FOUND

THEN

V_ACTUAL_FLAG := NULL;

V_FLAG := 'E';

V_ERR_FLAG := 'E';

FND_FILE.PUT_LINE (FND_FILE.LOG, 'The Flag is not correct');

END;

FND_FILE.

PUT_LINE (FND_FILE.LOG,

'The data inserting is... ' || V_ACTUAL_FLAG);

IF V_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 (V_STATUS,

V_SOB_ID,

V_USER_JE_SOURCE_NAME,

V_USER_JE_CATEGORY_NAME,

V_CUR_CODE,

V_ACTUAL_FLAG,

REC.ACCOUNTING_DATE,

REC.DATE_CREATED,

786,

REC.ENTERED_DR,

REC.ENTERED_CR,

REC.ACCOUNTED_DR,

REC.ACCOUNTED_CR,

REC.SEGMENT1,

REC.SEGMENT2,

REC.SEGMENT3,

REC.SEGMENT4,

REC.SEGMENT5);

END IF;

V_FLAG := NULL;

V_ERR_FLAG := NULL;

END LOOP;

COMMIT;

END;

END XXWIPRO_GL_INT_PKG;

OUTPUT :- Package body created.

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

5. Create the executable program with short_name and executable_file_name

Note:- 1. Short_Name = user define (XXWIP_GL_INT_F)

2. Executable_file_name = package_name.procedure_name
XXWIPRO_GL_INT_PKG.XXWIPRO_GL_INT_PROC