Oracle ERP Professionals

SELECT
pg_old.name
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id
and paaf_new.effective_start_date =(select
((to_char(max(paaf_new.effective_start_date),'DD-MON-RRRR')))
from apps.per_all_people_f papf,
apps.per_all_assignments_f paaf_old,
apps.per_grades pg_old,
apps.per_all_assignments_f paaf_new,
apps.per_grades pg_new
where 1=1
and papf.person_id = paaf_old.person_id
and papf.person_id = paaf_new.person_id
and paaf_old.person_id = paaf_new.person_id
and paaf_old.assignment_type = 'E'
and paaf_old.primary_flag = 'Y'
and paaf_new.assignment_type = 'E'
and paaf_new.primary_flag = 'Y'
and pg_old.grade_id = paaf_old.grade_id
and pg_new.grade_id = paaf_new.grade_id
and paaf_old.grade_id <> paaf_new.grade_id
and trunc(paaf_old.effective_end_date)+1 =trunc(paaf_new.effective_start_date)
and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and  trunc(paaf_new.effective_start_date) between paaf_new.effective_start_date and paaf_new.effective_end_date
--and papf.person_id = 162
and papf.person_id = paaf.person_id )


SELECT ppps.proposed_salary_n from per_pay_proposals ppps
where paaf.assignment_id = ppps.assignment_id
AND ppps.last_change_date is not null
and ppps.change_date = (SELECT  TO_CHAR(max(ppp.change_date),'DD-MON-RRRR') as change_effective_date
from per_pay_proposals ppp
where 1=1
and paaf.assignment_id = ppp.assignment_id

AND last_change_date is not null )

-----Query to Get OLM Employee Information----------

SELECT
P.EMPLOYEE_NUMBER,
AVT.VERSION_NAME COURSE_NAME,
E.TITLE CLASS_NAME,
E.COURSE_START_DATE,
E.COURSE_END_DATE,
E.EVENT_TYPE,
O.SUCCESSFUL_ATTENDANCE_FLAG,
OB.NAME STATUS,
E.TIMEZONE
--E.EVENT_ID,
--O.EVENT_ID,
--P.EFFECTIVE_START_DATE,
--P.EFFECTIVE_END_DATE,
FROM
APPS.PER_ALL_PEOPLE_F P,
APPS.OTA_DELEGATE_BOOKINGS O,
APPS.OTA_EVENTS E,
APPS.OTA_BOOKING_STATUS_TYPES OB,
APPS.OTA_ACTIVITY_VERSIONS AVT
WHERE 1=1
AND O.DELEGATE_PERSON_ID = P.PERSON_ID
AND E.EVENT_ID = O.EVENT_ID
AND OB.BOOKING_STATUS_TYPE_ID = O.BOOKING_STATUS_TYPE_ID
AND E.ACTIVITY_VERSION_ID = AVT.ACTIVITY_VERSION_ID
AND SYSDATE BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
AND P.EMPLOYEE_NUMBER = 200646
--AND AVT.LANGUAGE = USERENV('LANG')
--AND E.LANGUAGE = USERENV('LANG')

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

AR- Aging Report from RDF to RTF.(Excel Output)

 https://drive.google.com/file/d/0B-v-X3Gxf2sgbUV6RW4yWGlzSzA/edit?usp=sharing

https://drive.google.com/file/d/14-noMV1aNa7emKSnhMPSw8Kk7WGIhIGF/view?usp=sharing

Oracle E-Business Suite R12&11i Form Startup.You can use this Chrome Extension to log in Oracle E-Business R12 Form UI without FRM-92129 error which say the file Registry.dat lost.