create or replace PACKAGE BODY XXTEST_ERS_REPORT_PKG AS
/*==============================================================================================================
* TITLE : ERS REPORT
* PROGRAM NAME : XXTEST_ERS_REPORT_PKG
* CREATED BY : SAHA G
* CREATION DATE :
* CALLED BY :
* DEPENDENCY : NONE
* FREQUENCY : NONE
* RELATED DOCUMENTS :
* TABLES/VIEWS ACCESSED:
*
*
* CHANGE HISTORY LOG
*==============================================================================================================
* DATE |NAME |TICKET / BUG |VERSION |REMARKS
* 14-SEP-2010 SAHA G 12.1.1.0 DEVELOPED FOR ....
*==============================================================================================================
***************************************************************************************************************
*/
----------
--- ||PROCEDURE : XXTEST_ERS_REPORT_PRC ||
-----------
PROCEDURE XXTEST_ERS_REPORT_PRC(
ERRBUFF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY NUMBER ,
P_FIN_YEAR IN VARCHAR2 ,
P_ORG_ID IN VARCHAR2
)AS
L_FIRST_DATE DATE ;
L_LAST_DATE DATE ;
L_DATE DATE ;
L_END_YEAR VARCHAR2(10) ;
L_MONTH VARCHAR2(10) ;
L_EMP_CODE VARCHAR2(40) ;
L_Employee_Name VARCHAR2(40) ;
L_REF_CND_EMP_NO VARCHAR2(40) ;
L_JOIN_DATE VARCHAR2(40) ;
L_REF_CND_NO VARCHAR2(40) ;
L_COUNT NUMBER :=0 ;
----------------
-- CURSOR CUR_ASG_DETAIL USED TO FETCH ASSIGNMENT ACTION_ID AND ASSIGNMENT_ID AND RUN RESULT VALUE
-- FOR THOSE EMPLOYEE WHO HAVE EMPLOYEE REFERAL BONUS
----------------
CURSOR CUR_ASG_DETAIL(L_FIRST_DATE DATE,L_LAST_DATE DATE) IS
SELECT PAA.ASSIGNMENT_ID ASSIGNMENT_ID ,
NVL(PRRV.RESULT_VALUE,0) ELE_VALUE ,
NVL(PRRV1.RESULT_VALUE,'') REF_CND_NO ,
PPA.EFFECTIVE_DATE EFFECTIVE_DATE
FROM PER_TIME_PERIODS PTP ,
PAY_ASSIGNMENT_ACTIONS PAA ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PAY_PAYROLL_ACTIONS PPA ,
PAY_RUN_RESULTS PRR ,
PAY_RUN_RESULT_VALUES PRRV ,
PAY_ELEMENT_TYPES_F PETF ,
PAY_INPUT_VALUES_F PIVF ,
PAY_INPUT_VALUES_F PIVF1 ,
PAY_RUN_RESULT_VALUES PRRV1
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAAF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAAF.ORGANIZATION_ID = NVL(P_ORG_ID, PAAF.ORGANIZATION_ID)
AND PAAF.PAYROLL_ID = PTP.PAYROLL_ID
AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRR.RUN_RESULT_ID = PRRV1.RUN_RESULT_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_NAME ='Employee Referral Information'
AND PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
AND PIVF.NAME = 'Amount'
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF1.NAME = 'Referred Emp Num'
AND PIVF1.INPUT_VALUE_ID = PRRV1.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF1.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND L_FIRST_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND L_FIRST_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND L_FIRST_DATE BETWEEN PIVF1.EFFECTIVE_START_DATE AND PIVF1.EFFECTIVE_END_DATE
AND ( L_FIRST_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
OR PAAF.EFFECTIVE_START_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE)
ORDER BY PAA.assignment_id, REF_CND_NO;
----------
--FETCHING THE EMPLOYEE DETAILS
-------
CURSOR REF_EMPLOYEE_DETAILS(L_VAR_ASG_ID NUMBER,L_FIRST_DATE DATE,L_LAST_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER Emp_Cd ,
INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) Employee_Name
FROM PER_ALL_PEOPLE_F PAPF ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAAF.ASSIGNMENT_ID = L_VAR_ASG_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PERIOD_OF_SERVICE_ID =PPOS.PERIOD_OF_SERVICE_ID
AND (( L_LAST_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND L_LAST_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
OR ( PPOS.ACTUAL_TERMINATION_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
OR ( PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)) ;
---------------
--------------
---CURSOR USED TO FETCH REFERED CANDIDATE DETAILS
-------------
CURSOR REF_CANDIDATE_DETAILS(V_REF_CND_NO VARCHAR2,V_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER EMP_NO ,
PAPF.ORIGINAL_DATE_OF_HIRE JOIN_DATE
FROM PER_ALL_PEOPLE_F PAPF ,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAPF.EMPLOYEE_NUMBER = V_REF_CND_NO
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)
OR ( PPOS.ACTUAL_TERMINATION_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)
OR ( PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)) ;
-------
--CREATING ARRAY OF TABLE TYPE.
------
type r_data IS record(t_month VARCHAR2(9)
,t_amount NUMBER);
type a_multi IS TABLE OF r_data INDEX BY binary_integer;
v_data a_multi;
--
i number:= 0;
l_assignment_id NUMBER := NULL;
L_BG_NAME VARCHAR2(100);
-- l_new_assignment NUMBER := NULL;
------------
BEGIN
L_FIRST_DATE :=FND_CONC_DATE.STRING_TO_DATE('01' || '-' || 'APR' || '-' || SUBSTR(P_FIN_YEAR,1, instr(P_FIN_YEAR, '-') - 1)) ;
L_LAST_DATE :=FND_CONC_DATE.STRING_TO_DATE('31' || '-' || 'MAR' || '-' || SUBSTR(P_FIN_YEAR, instr(P_FIN_YEAR, '-') + 1)) ;
BEGIN
SELECT NAME INTO L_BG_NAME FROM PER_BUSINESS_GROUPS WHERE BUSINESS_GROUP_ID=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
EXCEPTION WHEN OTHERS THEN
L_BG_NAME :='';
FND_FILE.PUT_LINE(FND_FILE.log,'Error Occure During Fetching BG Name');
END;
------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>') ;
--------
--LOG FILE
--------
FND_FILE.PUT_LINE(FND_FILE.log,'01 :'||TO_CHAR(L_FIRST_DATE,'DD-Mon-YYYY')) ;
FND_FILE.PUT_LINE(FND_FILE.log,'02 :'||TO_CHAR(L_LAST_DATE,'DD-Mon-YYYY')) ;
---------------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ERS_DETAILS>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FIN_YEAR>' ||P_FIN_YEAR || '</FIN_YEAR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_BG_NAME>' ||L_BG_NAME || '</L_BG_NAME>');
FOR REC_CUR_ASG_DETAIL IN CUR_ASG_DETAIL(L_FIRST_DATE,L_LAST_DATE)
LOOP
--
FND_FILE.PUT_LINE(FND_FILE.log,'03 :REC_CUR_ASG_DETAIL.ASSIGNMENT_ID:'||REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) ;
FND_FILE.PUT_LINE(FND_FILE.log,'04 :BEFORE IF PREVIOUS :'||l_assignment_id) ;
--
IF l_assignment_id <> REC_CUR_ASG_DETAIL.ASSIGNMENT_ID OR L_REF_CND_NO <> REC_CUR_ASG_DETAIL.REF_CND_NO THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Record>') ;
---------
FND_FILE.PUT_LINE(FND_FILE.log,'05 :l_assignment_id :'||l_assignment_id) ;
FND_FILE.PUT_LINE(FND_FILE.log,'06 :L_REF_CND_NO :'||L_REF_CND_NO) ;
FND_FILE.PUT_LINE(FND_FILE.log,'07 :DATE :'||L_DATE) ;
L_COUNT :=L_COUNT+1;
--------
--------FETCH EMPLOYEE DETAILS
OPEN REF_EMPLOYEE_DETAILS(l_assignment_id, L_FIRST_DATE, L_LAST_DATE) ;
FETCH REF_EMPLOYEE_DETAILS
INTO L_EMP_CODE ,
L_Employee_Name ;
CLOSE REF_EMPLOYEE_DETAILS ;
-----
FND_FILE.PUT_LINE(FND_FILE.log,'08 :fetched REF_EMPLOYEE_DETAILS') ;
----
------- FETCH CANDIDATE DETAILS
OPEN REF_CANDIDATE_DETAILS(L_REF_CND_NO,L_DATE) ;
FETCH REF_CANDIDATE_DETAILS
INTO L_REF_CND_EMP_NO ,
L_JOIN_DATE ;
CLOSE REF_CANDIDATE_DETAILS ;
--------
FND_FILE.PUT_LINE(FND_FILE.log,'09 :fetched REF_CANDIDATE_DETAILS') ;
--------
--XML Tags
--------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT> ' ||L_COUNT || '</COUNT>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||L_EMP_CODE || ']]></EMPCODE>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMP_NAME><![CDATA[' ||L_Employee_Name || ']]></EMP_NAME>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REF_EMP_CD>' ||L_REF_CND_EMP_NO ||'</REF_EMP_CD>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_DATE>' ||L_JOIN_DATE ||'</JOINING_DATE>') ;
END IF;
IF nvl(l_assignment_id, REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) <> REC_CUR_ASG_DETAIL.ASSIGNMENT_ID OR L_REF_CND_NO <> REC_CUR_ASG_DETAIL.REF_CND_NO THEN
FND_FILE.PUT_LINE(FND_FILE.log,'10 :REC_CUR_ASG_DETAIL.ASSIGNMENT_ID'||REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) ;
FOR j IN 0..(i-1) LOOP
--
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<'|| v_data(j).t_month ||'>'
|| TO_CHAR(ROUND( v_data(j).t_amount ,2),'FM99,99,99,99,999.00') || '</'|| v_data(j).t_month ||'>');--||''''
END LOOP;
--------
i := 0;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Record>') ;
END IF ;
l_assignment_id := REC_CUR_ASG_DETAIL.ASSIGNMENT_ID ;
L_REF_CND_NO := REC_CUR_ASG_DETAIL.REF_CND_NO ;
L_DATE := TO_DATE(REC_CUR_ASG_DETAIL.EFFECTIVE_DATE) ;
v_data(i).t_amount := REC_CUR_ASG_DETAIL.ELE_VALUE ;
v_data(i).t_month := 'L'||to_char(REC_CUR_ASG_DETAIL.EFFECTIVE_DATE,'MM') ;
i := i+1 ;
FND_FILE.PUT_LINE(FND_FILE.log,'i =06:'||i) ;
---------
---FETCH EMPLOYEE DETAILS
---------
OPEN REF_EMPLOYEE_DETAILS(l_assignment_id, L_FIRST_DATE, L_LAST_DATE) ;
FETCH REF_EMPLOYEE_DETAILS
INTO L_EMP_CODE ,
L_Employee_Name ;
CLOSE REF_EMPLOYEE_DETAILS ;
FND_FILE.PUT_LINE(FND_FILE.log,'fetched REF_EMPLOYEE_DETAILS LAST') ;
---------
--- FETCH CANDIDATE DETAILS
----------
OPEN REF_CANDIDATE_DETAILS(L_REF_CND_NO,L_DATE) ;
FETCH REF_CANDIDATE_DETAILS
INTO L_REF_CND_EMP_NO ,
L_JOIN_DATE ;
CLOSE REF_CANDIDATE_DETAILS ;
FND_FILE.PUT_LINE(FND_FILE.log,'fetched REF_EMPLOYEE_DETAILS LAST') ;
FND_FILE.PUT_LINE(FND_FILE.log,'***************************') ;
END LOOP;
-------
---GENERATING TAG FOR THE LAST ASSIGNMENT ID OR LAST ROW.
------
L_COUNT :=L_COUNT+1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Record_LAST>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT> ' ||L_COUNT || '</COUNT>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||L_EMP_CODE || ']]></EMPCODE>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMP_NAME><![CDATA[' ||L_Employee_Name || ']]></EMP_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REF_EMP_CD>' ||L_REF_CND_EMP_NO ||'</REF_EMP_CD>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_DATE>' ||L_JOIN_DATE ||'</JOINING_DATE>');
FOR j IN 0..(i-1) LOOP
--
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<'|| v_data(j).t_month ||'>'
||TO_CHAR(ROUND( v_data(j).t_amount ,2),'FM99,99,99,99,999.00')|| '</'|| v_data(j).t_month ||'>');
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Record_LAST>');
---------
----CLOSE HEAD TAG
---------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</ERS_DETAILS>');
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.log,'EXCEPTION *************************** EXCEPTION');
DBMS_OUTPUT.PUT_LINE('UNEXPECTED EXCEPTION at :'||SQLERRM);
END XXTEST_ERS_REPORT_PRC;
----------------
END XXTEST_ERS_REPORT_PKG;
/*==============================================================================================================
* TITLE : ERS REPORT
* PROGRAM NAME : XXTEST_ERS_REPORT_PKG
* CREATED BY : SAHA G
* CREATION DATE :
* CALLED BY :
* DEPENDENCY : NONE
* FREQUENCY : NONE
* RELATED DOCUMENTS :
* TABLES/VIEWS ACCESSED:
*
*
* CHANGE HISTORY LOG
*==============================================================================================================
* DATE |NAME |TICKET / BUG |VERSION |REMARKS
* 14-SEP-2010 SAHA G 12.1.1.0 DEVELOPED FOR ....
*==============================================================================================================
***************************************************************************************************************
*/
----------
--- ||PROCEDURE : XXTEST_ERS_REPORT_PRC ||
-----------
PROCEDURE XXTEST_ERS_REPORT_PRC(
ERRBUFF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY NUMBER ,
P_FIN_YEAR IN VARCHAR2 ,
P_ORG_ID IN VARCHAR2
)AS
L_FIRST_DATE DATE ;
L_LAST_DATE DATE ;
L_DATE DATE ;
L_END_YEAR VARCHAR2(10) ;
L_MONTH VARCHAR2(10) ;
L_EMP_CODE VARCHAR2(40) ;
L_Employee_Name VARCHAR2(40) ;
L_REF_CND_EMP_NO VARCHAR2(40) ;
L_JOIN_DATE VARCHAR2(40) ;
L_REF_CND_NO VARCHAR2(40) ;
L_COUNT NUMBER :=0 ;
----------------
-- CURSOR CUR_ASG_DETAIL USED TO FETCH ASSIGNMENT ACTION_ID AND ASSIGNMENT_ID AND RUN RESULT VALUE
-- FOR THOSE EMPLOYEE WHO HAVE EMPLOYEE REFERAL BONUS
----------------
CURSOR CUR_ASG_DETAIL(L_FIRST_DATE DATE,L_LAST_DATE DATE) IS
SELECT PAA.ASSIGNMENT_ID ASSIGNMENT_ID ,
NVL(PRRV.RESULT_VALUE,0) ELE_VALUE ,
NVL(PRRV1.RESULT_VALUE,'') REF_CND_NO ,
PPA.EFFECTIVE_DATE EFFECTIVE_DATE
FROM PER_TIME_PERIODS PTP ,
PAY_ASSIGNMENT_ACTIONS PAA ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PAY_PAYROLL_ACTIONS PPA ,
PAY_RUN_RESULTS PRR ,
PAY_RUN_RESULT_VALUES PRRV ,
PAY_ELEMENT_TYPES_F PETF ,
PAY_INPUT_VALUES_F PIVF ,
PAY_INPUT_VALUES_F PIVF1 ,
PAY_RUN_RESULT_VALUES PRRV1
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ACTION_STATUS = 'C'
AND PAAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PAAF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAAF.ORGANIZATION_ID = NVL(P_ORG_ID, PAAF.ORGANIZATION_ID)
AND PAAF.PAYROLL_ID = PTP.PAYROLL_ID
AND PTP.TIME_PERIOD_ID = PPA.TIME_PERIOD_ID
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRR.RUN_RESULT_ID = PRRV1.RUN_RESULT_ID
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETF.ELEMENT_NAME ='Employee Referral Information'
AND PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID')
AND PIVF.NAME = 'Amount'
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PIVF1.NAME = 'Referred Emp Num'
AND PIVF1.INPUT_VALUE_ID = PRRV1.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF1.ELEMENT_TYPE_ID
AND PPA.EFFECTIVE_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND L_FIRST_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND L_FIRST_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
AND L_FIRST_DATE BETWEEN PIVF1.EFFECTIVE_START_DATE AND PIVF1.EFFECTIVE_END_DATE
AND ( L_FIRST_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
OR PAAF.EFFECTIVE_START_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE)
ORDER BY PAA.assignment_id, REF_CND_NO;
----------
--FETCHING THE EMPLOYEE DETAILS
-------
CURSOR REF_EMPLOYEE_DETAILS(L_VAR_ASG_ID NUMBER,L_FIRST_DATE DATE,L_LAST_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER Emp_Cd ,
INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) Employee_Name
FROM PER_ALL_PEOPLE_F PAPF ,
PER_ALL_ASSIGNMENTS_F PAAF ,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAAF.ASSIGNMENT_ID = L_VAR_ASG_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PERIOD_OF_SERVICE_ID =PPOS.PERIOD_OF_SERVICE_ID
AND (( L_LAST_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND L_LAST_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
OR ( PPOS.ACTUAL_TERMINATION_DATE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)
OR ( PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN L_FIRST_DATE AND L_LAST_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE)) ;
---------------
--------------
---CURSOR USED TO FETCH REFERED CANDIDATE DETAILS
-------------
CURSOR REF_CANDIDATE_DETAILS(V_REF_CND_NO VARCHAR2,V_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER EMP_NO ,
PAPF.ORIGINAL_DATE_OF_HIRE JOIN_DATE
FROM PER_ALL_PEOPLE_F PAPF ,
PER_PERIODS_OF_SERVICE PPOS
WHERE PAPF.EMPLOYEE_NUMBER = V_REF_CND_NO
AND PAPF.PERSON_ID = PPOS.PERSON_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)
OR ( PPOS.ACTUAL_TERMINATION_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)
OR ( PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE)) ;
-------
--CREATING ARRAY OF TABLE TYPE.
------
type r_data IS record(t_month VARCHAR2(9)
,t_amount NUMBER);
type a_multi IS TABLE OF r_data INDEX BY binary_integer;
v_data a_multi;
--
i number:= 0;
l_assignment_id NUMBER := NULL;
L_BG_NAME VARCHAR2(100);
-- l_new_assignment NUMBER := NULL;
------------
BEGIN
L_FIRST_DATE :=FND_CONC_DATE.STRING_TO_DATE('01' || '-' || 'APR' || '-' || SUBSTR(P_FIN_YEAR,1, instr(P_FIN_YEAR, '-') - 1)) ;
L_LAST_DATE :=FND_CONC_DATE.STRING_TO_DATE('31' || '-' || 'MAR' || '-' || SUBSTR(P_FIN_YEAR, instr(P_FIN_YEAR, '-') + 1)) ;
BEGIN
SELECT NAME INTO L_BG_NAME FROM PER_BUSINESS_GROUPS WHERE BUSINESS_GROUP_ID=FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID');
EXCEPTION WHEN OTHERS THEN
L_BG_NAME :='';
FND_FILE.PUT_LINE(FND_FILE.log,'Error Occure During Fetching BG Name');
END;
------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>') ;
--------
--LOG FILE
--------
FND_FILE.PUT_LINE(FND_FILE.log,'01 :'||TO_CHAR(L_FIRST_DATE,'DD-Mon-YYYY')) ;
FND_FILE.PUT_LINE(FND_FILE.log,'02 :'||TO_CHAR(L_LAST_DATE,'DD-Mon-YYYY')) ;
---------------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ERS_DETAILS>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FIN_YEAR>' ||P_FIN_YEAR || '</FIN_YEAR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_BG_NAME>' ||L_BG_NAME || '</L_BG_NAME>');
FOR REC_CUR_ASG_DETAIL IN CUR_ASG_DETAIL(L_FIRST_DATE,L_LAST_DATE)
LOOP
--
FND_FILE.PUT_LINE(FND_FILE.log,'03 :REC_CUR_ASG_DETAIL.ASSIGNMENT_ID:'||REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) ;
FND_FILE.PUT_LINE(FND_FILE.log,'04 :BEFORE IF PREVIOUS :'||l_assignment_id) ;
--
IF l_assignment_id <> REC_CUR_ASG_DETAIL.ASSIGNMENT_ID OR L_REF_CND_NO <> REC_CUR_ASG_DETAIL.REF_CND_NO THEN
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Record>') ;
---------
FND_FILE.PUT_LINE(FND_FILE.log,'05 :l_assignment_id :'||l_assignment_id) ;
FND_FILE.PUT_LINE(FND_FILE.log,'06 :L_REF_CND_NO :'||L_REF_CND_NO) ;
FND_FILE.PUT_LINE(FND_FILE.log,'07 :DATE :'||L_DATE) ;
L_COUNT :=L_COUNT+1;
--------
--------FETCH EMPLOYEE DETAILS
OPEN REF_EMPLOYEE_DETAILS(l_assignment_id, L_FIRST_DATE, L_LAST_DATE) ;
FETCH REF_EMPLOYEE_DETAILS
INTO L_EMP_CODE ,
L_Employee_Name ;
CLOSE REF_EMPLOYEE_DETAILS ;
-----
FND_FILE.PUT_LINE(FND_FILE.log,'08 :fetched REF_EMPLOYEE_DETAILS') ;
----
------- FETCH CANDIDATE DETAILS
OPEN REF_CANDIDATE_DETAILS(L_REF_CND_NO,L_DATE) ;
FETCH REF_CANDIDATE_DETAILS
INTO L_REF_CND_EMP_NO ,
L_JOIN_DATE ;
CLOSE REF_CANDIDATE_DETAILS ;
--------
FND_FILE.PUT_LINE(FND_FILE.log,'09 :fetched REF_CANDIDATE_DETAILS') ;
--------
--XML Tags
--------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT> ' ||L_COUNT || '</COUNT>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||L_EMP_CODE || ']]></EMPCODE>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMP_NAME><![CDATA[' ||L_Employee_Name || ']]></EMP_NAME>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REF_EMP_CD>' ||L_REF_CND_EMP_NO ||'</REF_EMP_CD>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_DATE>' ||L_JOIN_DATE ||'</JOINING_DATE>') ;
END IF;
IF nvl(l_assignment_id, REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) <> REC_CUR_ASG_DETAIL.ASSIGNMENT_ID OR L_REF_CND_NO <> REC_CUR_ASG_DETAIL.REF_CND_NO THEN
FND_FILE.PUT_LINE(FND_FILE.log,'10 :REC_CUR_ASG_DETAIL.ASSIGNMENT_ID'||REC_CUR_ASG_DETAIL.ASSIGNMENT_ID) ;
FOR j IN 0..(i-1) LOOP
--
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<'|| v_data(j).t_month ||'>'
|| TO_CHAR(ROUND( v_data(j).t_amount ,2),'FM99,99,99,99,999.00') || '</'|| v_data(j).t_month ||'>');--||''''
END LOOP;
--------
i := 0;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Record>') ;
END IF ;
l_assignment_id := REC_CUR_ASG_DETAIL.ASSIGNMENT_ID ;
L_REF_CND_NO := REC_CUR_ASG_DETAIL.REF_CND_NO ;
L_DATE := TO_DATE(REC_CUR_ASG_DETAIL.EFFECTIVE_DATE) ;
v_data(i).t_amount := REC_CUR_ASG_DETAIL.ELE_VALUE ;
v_data(i).t_month := 'L'||to_char(REC_CUR_ASG_DETAIL.EFFECTIVE_DATE,'MM') ;
i := i+1 ;
FND_FILE.PUT_LINE(FND_FILE.log,'i =06:'||i) ;
---------
---FETCH EMPLOYEE DETAILS
---------
OPEN REF_EMPLOYEE_DETAILS(l_assignment_id, L_FIRST_DATE, L_LAST_DATE) ;
FETCH REF_EMPLOYEE_DETAILS
INTO L_EMP_CODE ,
L_Employee_Name ;
CLOSE REF_EMPLOYEE_DETAILS ;
FND_FILE.PUT_LINE(FND_FILE.log,'fetched REF_EMPLOYEE_DETAILS LAST') ;
---------
--- FETCH CANDIDATE DETAILS
----------
OPEN REF_CANDIDATE_DETAILS(L_REF_CND_NO,L_DATE) ;
FETCH REF_CANDIDATE_DETAILS
INTO L_REF_CND_EMP_NO ,
L_JOIN_DATE ;
CLOSE REF_CANDIDATE_DETAILS ;
FND_FILE.PUT_LINE(FND_FILE.log,'fetched REF_EMPLOYEE_DETAILS LAST') ;
FND_FILE.PUT_LINE(FND_FILE.log,'***************************') ;
END LOOP;
-------
---GENERATING TAG FOR THE LAST ASSIGNMENT ID OR LAST ROW.
------
L_COUNT :=L_COUNT+1;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Record_LAST>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<COUNT> ' ||L_COUNT || '</COUNT>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||L_EMP_CODE || ']]></EMPCODE>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMP_NAME><![CDATA[' ||L_Employee_Name || ']]></EMP_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<REF_EMP_CD>' ||L_REF_CND_EMP_NO ||'</REF_EMP_CD>') ;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_DATE>' ||L_JOIN_DATE ||'</JOINING_DATE>');
FOR j IN 0..(i-1) LOOP
--
fnd_file.PUT_LINE(fnd_file.OUTPUT, '<'|| v_data(j).t_month ||'>'
||TO_CHAR(ROUND( v_data(j).t_amount ,2),'FM99,99,99,99,999.00')|| '</'|| v_data(j).t_month ||'>');
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</Record_LAST>');
---------
----CLOSE HEAD TAG
---------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</ERS_DETAILS>');
EXCEPTION WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.log,'EXCEPTION *************************** EXCEPTION');
DBMS_OUTPUT.PUT_LINE('UNEXPECTED EXCEPTION at :'||SQLERRM);
END XXTEST_ERS_REPORT_PRC;
----------------
END XXTEST_ERS_REPORT_PKG;
No comments:
Post a Comment