About Me

Dubai, United Arab Emirates
My name is Muhammad Faizan Karim and my aim is to spread the knowledge to everyone who want to learn oracle HRMS. I will try to write the video blogs for beginners so they can understand e-business suits from HRMS perspective, and if you have any difficulty in oracle applications HRMS , please feel free to contact with me at this email : faizan.kareem.ocp@gmail.com
Powered by Blogger.
Wednesday, June 4, 2014

Package to Load Data into GL_INTERFACE (General Ledger)

3:55 AM | Posted by | Edit Post
CREATE OR REPLACE PACKAGE XX_GL_INT_PKG IS

-- AUTHOR : ADMINISTRATOR
-- CREATED : 5/7/2014 11:20:49 PM
-- PURPOSE : GL INTERFACE

-- PUBLIC TYPE DECLARATIONS


-- PUBLIC FUNCTION AND PROCEDURE DECLARATIONS
PROCEDURE DIS_LOG(P_MSG IN VARCHAR2);

PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2, P_PERIOD_NAME VARCHAR2
);

END XX_GL_INT_PKG;
/
CREATE OR REPLACE PACKAGE BODY XX_GL_INT_PKG
IS
PROCEDURE DIS_LOG(P_MSG IN VARCHAR2)
IS
BEGIN
--FND_FILE.PUT_LINE(FND_FILE.LOG,P_MSG);
DBMS_OUTPUT.PUT_LINE(P_MSG);
END;

PROCEDURE MAIN(ERRBUF OUT VARCHAR2,
RETCODE OUT VARCHAR2,
P_PERIOD_NAME VARCHAR2

)
IS
CURSOR C1 IS SELECT A.ROWID ROW_ID,A.* FROM XXBHG.GL_INTERFACE_TEMP A
WHERE A.PROCESS_FLAG IS NULL; --UPPER(A.PERIOD_NAME) = P_PERIOD_NAME;
V_GL_INT GL_INTERFACE%ROWTYPE;
V_PROCESS_FLAG VARCHAR2(10);
V_ERROR_MSG VARCHAR2(100);
V_TOT_ERR_MSG VARCHAR2(1000);
BEGIN

-- DIS_LOG('BEFORE ENTERING THE LOOP');

FOR I IN C1 LOOP
V_ERROR_MSG :=NULL;
V_PROCESS_FLAG:='S';
V_TOT_ERR_MSG:=NULL;
V_GL_INT:=NULL;
--CURRENCY_CODE VALIDATION
BEGIN
SELECT CURRENCY_CODE INTO V_GL_INT.CURRENCY_CODE
FROM FND_CURRENCIES
WHERE CURRENCY_CODE=I.CURRENCY_CODE;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := 'INVALID CURRENCY CODE =>'||I.CURRENCY_CODE;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
WHEN OTHERS THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := ' EXCEPTION AT CURRENCY CODE =>'||I.CURRENCY_CODE;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
END;


--USER_JE_SOURCE_NAME VALIDATION

BEGIN

SELECT USER_JE_SOURCE_NAME INTO V_GL_INT.USER_JE_SOURCE_NAME
FROM GL_JE_SOURCES
WHERE USER_JE_SOURCE_NAME=I.USER_JE_SOURCE_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := 'INVALID SOUREC NAME =>'||I.USER_JE_SOURCE_NAME;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
WHEN OTHERS THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := ' EXCEPTION AT SOUREC NAME =>'||I.USER_JE_SOURCE_NAME;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
END;

--CATEGORY_NAME VALIDATION
BEGIN
SELECT USER_JE_CATEGORY_NAME INTO V_GL_INT.USER_JE_CATEGORY_NAME
FROM GL_JE_CATEGORIES
WHERE USER_JE_CATEGORY_NAME=I.USER_JE_CATEGORY_NAME;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := 'INVALID CATEGORY_NAME =>'||I.USER_JE_CATEGORY_NAME;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
WHEN OTHERS THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := ' EXCEPTION AT CATEGORY_NAME =>'||I.USER_JE_CATEGORY_NAME;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;

END;

--USER ID VALIDATION

BEGIN
SELECT USER_ID INTO V_GL_INT.CREATED_BY FROM FND_USER
WHERE USER_ID = 0;-- I.CREATED_BY;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := 'INVALID USER ID =>'||I.CREATED_BY;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
WHEN OTHERS THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := ' EXCEPTION AT USER ID =>'||I.CREATED_BY;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;

END;

-- SET OF BOOKS ID VALIDATION

BEGIN

SELECT SET_OF_BOOKS_ID INTO V_GL_INT.SET_OF_BOOKS_ID
FROM GL_SETS_OF_BOOKS WHERE SET_OF_BOOKS_ID=I.SET_OF_BOOKS_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := 'INVALID SET OF BOOKS ID =>'||I.SET_OF_BOOKS_ID;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
WHEN OTHERS THEN
V_PROCESS_FLAG:='E';
V_ERROR_MSG := ' EXCEPTION ATSET OF BOOKS ID =>'||I.SET_OF_BOOKS_ID;
V_TOT_ERR_MSG:= V_TOT_ERR_MSG||' '||V_ERROR_MSG ;
END;


V_GL_INT.LEDGER_ID := I.SET_OF_BOOKS_ID;
V_GL_INT.JE_HEADER_ID :=I.JE_HEADER_ID;
V_GL_INT.JE_BATCH_ID :=I.JE_BATCH_ID;
-- V_GL_INT.JE_LINE_NUM :=I.JE_LINE_NUM;
V_GL_INT.STATUS :=I.STATUS;
V_GL_INT.SET_OF_BOOKS_ID := I.SET_OF_BOOKS_ID;
V_GL_INT.ACCOUNTING_DATE :=I.ACCOUNTING_DATE;
V_GL_INT.CURRENCY_CODE :=I.CURRENCY_CODE;
V_GL_INT.DATE_CREATED :=I.DATE_CREATED;
V_GL_INT.CREATED_BY := 0;
V_GL_INT.ACTUAL_FLAG :=I.ACTUAL_FLAG ;
V_GL_INT.USER_JE_CATEGORY_NAME :=I.USER_JE_CATEGORY_NAME;
V_GL_INT.USER_JE_SOURCE_NAME :=I.USER_JE_SOURCE_NAME;
V_GL_INT.SEGMENT1 :=I.SEGMENT1;
V_GL_INT.SEGMENT2 :=I.SEGMENT2;
V_GL_INT.SEGMENT3 :=I.SEGMENT3;
V_GL_INT.SEGMENT4 :=I.SEGMENT4;
V_GL_INT.SEGMENT5 :=I.SEGMENT5 ;
V_GL_INT.SEGMENT6 :=I.SEGMENT6 ;
V_GL_INT.ENTERED_DR :=I.ENTERED_DR;
V_GL_INT.ENTERED_CR :=I.ENTERED_CR;
V_GL_INT.ACCOUNTED_DR :=I.ACCOUNTED_DR;
V_GL_INT.ACCOUNTED_CR :=I.ACCOUNTED_CR;
V_GL_INT.USER_CURRENCY_CONVERSION_TYPE := I.USER_CURRENCY_CONVERSION_TYPE;
V_GL_INT.CURRENCY_CONVERSION_DATE := I.CURRENCY_CONVERSION_DATE;
V_GL_INT.CURRENCY_CONVERSION_RATE := I.CURRENCY_CONVERSION_RATE;
V_GL_INT.PERIOD_NAME := I.PERIOD_NAME;
V_GL_INT.REFERENCE21 := I.REFERENCE1;
V_GL_INT.REFERENCE22 := I.REFERENCE2;
V_GL_INT.REFERENCE23 := I.REFERENCE3;
V_GL_INT.REFERENCE24 := I.REFERENCE4;
V_GL_INT.REFERENCE25 := I.REFERENCE5;
V_GL_INT.REFERENCE26 := I.REFERENCE6;
V_GL_INT.REFERENCE27 := I.REFERENCE7;
V_GL_INT.REFERENCE28 := I.REFERENCE8;
V_GL_INT.REFERENCE29 := I.REFERENCE9;
V_GL_INT.REFERENCE30 := I.REFERENCE10;
---Extra Information
V_GL_INT.REFERENCE1 := I.BATCH_NAME;
V_GL_INT.REFERENCE2 := I.BATCH_DESC;
V_GL_INT.REFERENCE4 := I.JOURNAL_NAME;
V_GL_INT.REFERENCE5 := I.JOURNAL_DESC;
V_GL_INT.REFERENCE10 := I.JOURNAL_LINE_DESC;


-- DIS_LOG('BEFORE INSERTING THE LOOP');

IF V_PROCESS_FLAG = 'S' THEN

INSERT INTO GL_INTERFACE VALUES V_GL_INT;

END IF;
UPDATE XXBHG.GL_INTERFACE_TEMP SET PROCESS_FLAG=V_PROCESS_FLAG,
ERROR_MESSAGE=V_TOT_ERR_MSG
WHERE ROWID=I.ROW_ID;

-- DIS_LOG('AFTER INSERTING THE LOOP');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DIS_LOG('EXCEPTION OCCURED AT MAIN LOOP');
END MAIN;
END XX_GL_INT_PKG;
/
Subscribe to: Posts (Atom)

Designed by Muhammad Faizan.