create or replace
PACKAGE BODY XXARJ_PAY_REG_REP_PKG_TEST
AS
---
--- XXARJ_PR_CALC_SITE_FNC used to calculate RATE of an element
---
FUNCTION XXARJ_PR_CALC_SITE_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF
WHERE PRR.ASSIGNMENT_ACTION_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION WHEN OTHERS THEN
L_ELEMENT_VALUE :=-1;
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_CALC_SITE_FNC;
----------
--- ||XXARJ_PR_RATE_CALCULATION_FNC used to calculate RATE of an element||
-----------
FUNCTION "XXARJ_PR_RATE_CALCULATION_FNC"
(
P_ASSIGNMENT_ID NUMBER,
P_ELEMENT_NAME VARCHAR2,
P_INPUT_VALUE_NAME VARCHAR2,
P_DATE DATE
) RETURN NUMBER
AS
L_BASIC_RATE NUMBER:=0;
BEGIN
SELECT fnd_number.canonical_to_number(PEEVF.SCREEN_ENTRY_VALUE)
INTO L_BASIC_RATE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_INPUT_VALUES_F PIVF
WHERE PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_ENTRY_ID = PEEVF.ELEMENT_ENTRY_ID
AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_BASIC_RATE;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXARJ_PR_RATE_CALCULATION_FNC;
-------------
-- ||XXARJ_PR_ELEMENT_VALUE_ASG_FNC used to calculate sum of Arrears From RUN RESULT VALUE of an element ||
-------------
FUNCTION XXARJ_PR_ELEMENT_VALUE_ASG_FNC
(
P_ASSIGNMENT_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
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 PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
And paa.source_action_id is not null
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_ELEMENT_VALUE_ASG_FNC;
------------
----||FUNCTION:XXARJ_PR_ELEMENT_VALUE_FNC used to Fetch RUN RESULT VALUE of an element ||
-----------
FUNCTION XXARJ_PR_ELEMENT_VALUE_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF
WHERE PRR.ASSIGNMENT_ACTION_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_ELEMENT_VALUE_FNC;
---------------
---- ||FUNCTION:XXARJ_PR_RUN_RESULT_NAME_FNC used to Fetch RUN RESULT VALUE of an element which return Charecter value like Bank name ||
----------------
FUNCTION XXARJ_PR_RUN_RESULT_NAME_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN VARCHAR2
AS
L_BANK_NAME VARCHAR2(150):='';
BEGIN
SELECT PRRV.RESULT_VALUE
INTO L_BANK_NAME
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
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 PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
and rownum<=1;
RETURN L_BANK_NAME;
EXCEPTION
WHEN OTHERS THEN
RETURN L_BANK_NAME;
END XXARJ_PR_RUN_RESULT_NAME_FNC;
----------
---||FUNCTION:XXARJ_PR_NET_SAL_FNC used to CALCULATE NET SALARY||
---------
FUNCTION XXARJ_PR_NET_SAL_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER,
P_BAL_NAME IN VARCHAR2 ,
P_DIM_NAME IN VARCHAR2 )
RETURN VARCHAR2
AS
L_NET_SALARY NUMBER:=0;
V_NET_PAY_ID NUMBER;
BEGIN
/*V_NET_PAY_ID := XXARJ_pay_payroll_reports_pkg.get_defined_bal_id(P_BAL_NAME, P_DIM_NAME);*/
BEGIN
SELECT pdb.defined_balance_id
INTO V_NET_PAY_ID
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = P_BAL_NAME
AND (pbt.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbt.legislation_code = 'IN')
AND (pbd.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbd.legislation_code = 'IN')
AND (pdb.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pdb.legislation_code = 'IN')
AND pbd.database_item_suffix = P_DIM_NAME;
EXCEPTION WHEN OTHERS THEN
V_NET_PAY_ID :=1;
END;
L_NET_SALARY := pay_balance_pkg.get_value(V_NET_PAY_ID, P_ASSIGNMENT_ACTION_ID);
--FND_FILE.PUT_LINE(FND_FILE.log,'~~L_NET_SALARY :'|| ' ' ||L_NET_SALARY);
RETURN L_NET_SALARY;
EXCEPTION
WHEN OTHERS THEN
RETURN L_NET_SALARY;
END XXARJ_PR_NET_SAL_FNC;
--------
--||FUNCTION:XXARJ_PR_FHNAME_FNC used to FIND FATHERS NAME||
--------
FUNCTION XXARJ_PR_FHNAME_FNC
(
P_PERSON_ID IN NUMBER
,P_DATE IN DATE
)
RETURN VARCHAR2 AS
L_FHNAME VARCHAR2(150);
BEGIN
SELECT INITCAP(PAPF1.FIRST_NAME
||' '
|| PAPF1.MIDDLE_NAMES
||' '
|| PAPF1.LAST_NAME) INTO L_FHNAME
FROM PER_ALL_PEOPLE_F PAPF1,
PER_CONTACT_RELATIONSHIPS PCR
WHERE PCR.PERSON_ID = P_PERSON_ID
AND PAPF1.PERSON_ID = PCR.CONTACT_PERSON_ID
AND PCR.CONTACT_TYPE = 'JP_FT'
AND P_DATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE;
RETURN L_FHNAME;
EXCEPTION WHEN OTHERS THEN
RETURN L_FHNAME;
END XXARJ_PR_FHNAME_FNC;
---------------
-- ||FUNCTION:XXARJ_REMARKS_NON_REC_FNC used to FIND Remarks i.e. List of all non recurring elemnts||
---------------
FUNCTION XXARJ_REMARKS_NON_REC_FNC(
P_ASSIGNMENT_ID IN NUMBER
,P_DATE IN DATE
)
RETURN VARCHAR2 AS
L_REMARKS VARCHAR2(10000):='';
CURSOR CUR_NON_REC_VALUE IS
SELECT NVL(PETF.ELEMENT_NAME,'') ELEMENT_NAME,
(NVL(PRRV.RESULT_VALUE,0)) RESULT_VALUE
FROM PAY_RUN_RESULT_VALUES PRRV,
PAY_RUN_RESULTS PRR,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ELEMENT_TYPES_F PETF,
PAY_INPUT_VALUES_F PIVF,
PAY_PAYROLL_ACTIONS PPA
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PETF.PROCESSING_TYPE = 'N'
AND PIVF.NAME = 'Pay Value'
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
/*SELECT NVL(PETF.ELEMENT_NAME,'') ELEMENT_NAME,
(NVL(PRRV.RESULT_VALUE,0)) RESULT_VALUE
FROM PAY_RUN_RESULT_VALUES PRRV,
PAY_RUN_RESULTS PRR,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ELEMENT_TYPES_F PETF,
PAY_INPUT_VALUES_F PIVF
WHERE PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = 21786
AND PETF.PROCESSING_TYPE = 'N'
AND PIVF.NAME = 'Pay Value'
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;*/
BEGIN
L_REMARKS :='';
FOR REC_CUR_NON_REC_VALUE IN CUR_NON_REC_VALUE
LOOP
--L_REMARKS:=L_REMARKS||' '||REC_CUR_NON_REC_VALUE.ELEMENT_NAME||'='||REC_CUR_NON_REC_VALUE.RESULT_VALUE ||' ';
L_REMARKS:=L_REMARKS||' '||REC_CUR_NON_REC_VALUE.ELEMENT_NAME||' , ';
END LOOP;
RETURN L_REMARKS;
EXCEPTION WHEN OTHERS THEN
RETURN L_REMARKS;
END XXARJ_REMARKS_NON_REC_FNC;
-----------
-- ||PROCEDURE:XXARJ_PAY_REGISTER_REPORT_PROC ||
-- ||MODIFIED ON:18-AUG-2010 ||
-- ||MODIFIED BY:SAHA GOPAL ||
-- ||THIS PROCEDURE IS USED FOR PAY REGISTER REPORT PLL INDIA ||
-----------
PROCEDURE XXARJ_PAY_REGISTER_REPORT_PRC(
ERRBUFF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY NUMBER ,
P_FIN_YEAR IN VARCHAR2,
P_MONTH IN VARCHAR2,
P_PAYROLL_ID IN VARCHAR2,
P_ORG_ID IN VARCHAR2
) IS
V_DATE DATE;
L_ASSIGNMENT_ID NUMBER;
L_YEAR NUMBER;
L_BASIC_SALARY_RATE NUMBER;
L_HRA_RATE NUMBER;
L_CON_ALL_R NUMBER;
L_CON_REIM_R NUMBER;
L_SPECIAL_ALL_R NUMBER;
L_PETROL_R NUMBER;
L_MED_ALL_R NUMBER;
L_DRIVER_R NUMBER;
L_EDUCATION_ALL_R NUMBER;
L_SUBSIDY_ALL_R NUMBER;
L_TELE_R NUMBER;
L_SITE_ALL_R NUMBER;
L_HARDSHIP_ALL_R NUMBER;
L_HARDSHIP_OFF_SHORE_R NUMBER;
L_STD_DAYS NUMBER;
L_SUPER_ANNUATION_R NUMBER;
L_ONSITE_DAYS NUMBER;
L_OVERTIME_HOURS NUMBER;
L_BASIC NUMBER;
L_BASIC_ARR NUMBER;
L_HRA NUMBER;
L_HRA_ARR NUMBER;
L_CON_ALL NUMBER;
L_CON_ALLW_ARR NUMBER;
L_CON_REIM_PAYABLE NUMBER;
L_CON_REIM_PAYABLE_ARR NUMBER;
L_SPCIAL_ALL NUMBER;
L_SPCIAL_ALL_ARR NUMBER;
L_ADVANCE_AGAINST_PETROL NUMBER;
L_ADVANCE_AGAINST_PETROL_ARR NUMBER;
L_MEDICAL_ALLOW NUMBER;
L_MEDICAL_ALLOW_ARR NUMBER;
L_ADVANCE_AGAINST_DRIVER NUMBER;
L_ADVANCE_AGAINST_DRIVER_ARR NUMBER;
L_EDUCATION_ALL NUMBER;
L_EDUCATION_ALL_ARR NUMBER;
L_GROSS_UP_ALL NUMBER;
L_SUBSIDY_ALLW NUMBER;
L_SUBSIDY_ALLW_ARR NUMBER;
L_SUPER_ANNUATION NUMBER;
L_SUPER_ANNUATION_ARR NUMBER;
L_TELE NUMBER;
L_TELE_ARR NUMBER;
L_SITE_ALL_AMOUNT NUMBER;
L_SITE_ALL_AMOUNT_ARR NUMBER;
L_HARDSHIP_AMOUNT_PAYABLE NUMBER;
L_HARDSHIP_AMOUNT_PAYABLE_ARR NUMBER;
L_HARDSHIP_OFF_SHORE_PAYA NUMBER;
L_OT_AMOUNT NUMBER;
L_OFFSHORE_ALL NUMBER;
L_LTA NUMBER;
L_RETANTION_BONUS NUMBER;
L_NEG_SAL NUMBER;
L_OTHER_EARNINGS_NON_TAXABLE NUMBER;
L_TELEPHONE_ADVANCE NUMBER;
L_LEAVE_ENCASHMENT NUMBER;
L_BONUS NUMBER;
L_GROSS_EARN NUMBER;
L_PF NUMBER;
L_ESI NUMBER;
L_IT NUMBER;
L_LWF NUMBER;
L_BUS_BEDN NUMBER;
L_CANTEEN_DEDN NUMBER;
L_BANK_NAME VARCHAR2(50);
L_CITI_BANK_LOAN_DEDUCTION NUMBER:=0;
L_HDFC_DEDUCTION NUMBER:=0;
L_CANARA_BANK_LOAN_DEDUCTION NUMBER:=0;
L_INDUSIND_BANK_DEDUCTION NUMBER:=0;
L_PF_LOAN NUMBER;
L_LOAN_AMOUNT NUMBER;
L_PERSONAL_LOAN NUMBER;
L_IMPREST_AMOUNT NUMBER;
L_INSURANCE NUMBER;
L_OTHER_DEDUCTION NUMBER;
L_NEG_SAL_REC NUMBER;
L_GROSS_DEDUCTION NUMBER;
L_COUNT NUMBER;
L_NET_SALARY NUMBER;
L_GROSS_UP_ALL_R NUMBER;
L_TDS_LOCATION NUMBER;
L_PT_LOCATION NUMBER;
L_ASG_LOCATION NUMBER;
V_FH_NAME VARCHAR2(150);
L_LOAN_TYPE VARCHAR2(150);
L_HOUSING_LOAN NUMBER;
L_NOTICE_PAY_DEDUCTION NUMBER;
L_C_REMARKS VARCHAR2(10000):='';
L_LOP_DAYS NUMBER:=0;
L_WRK_DAYS NUMBER:=0;
L_B_OS_ASSIGN NUMBER:=0;
V_process_date DATE;
L_PT NUMBER;
L_HARDSHIP_OFF_SHORE_PAYA_ARR NUMBER;
L_RELOCATION_AMOUNT NUMBER;
L_JOINING_BONUS NUMBER;
L_NOTICE_PAY_PAYMENT NUMBER;
L_Allowance1 NUMBER;
L_Ph_Subsidy NUMBER;
L_Ph_Subsidy_ARR NUMBER;
L_Notice_pay NUMBER;
L_Notice_Deduc NUMBER;
L_LOAN_PERQUISITE NUMBER;
L_R_Thai_Allw NUMBER;
L_Thai_Allw NUMBER;
L_LOAN_AMT_RECO NUMBER;
L_PHONE_SUBSIDY_R NUMBER;
----------
----||CURSER USED TO FETCH ASSIGNMENT ACTION ID AND ASSIGNMENT ID||
---------
CURSOR CUR_ASSIGNMENT_ACTION_ID IS
SELECT MAX(PAA.ASSIGNMENT_ACTION_ID) ASG_ACT_ID
,PAA.ASSIGNMENT_ID ASSIGNMENT_ID
,PAAF.PERSON_ID PERSON_ID
FROM PER_TIME_PERIODS PTP
,PAY_ASSIGNMENT_ACTIONS PAA
,PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PAY_PAYROLL_ACTIONS PPA
,PER_PERIODS_OF_SERVICE PPOS
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 paa.source_action_id is not null
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAAF.ORGANIZATION_ID = NVL(P_ORG_ID, PAAF.ORGANIZATION_ID)
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_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 PAAF.PAYROLL_ID = P_PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND ( V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
OR PAAF.EFFECTIVE_START_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE))
GROUP BY PAA.ASSIGNMENT_ID ,PAAF.PERSON_ID;
--------
---||CURSER USED TO FETCH EMPLOYEE'S PERSONAL DETAILS||
-------
CURSOR CUR_EMP_PERSONAL_DETAILS(L_ASSIGNMENT_ID NUMBER,L_PERSON_ID NUMBER,V_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER EMPCODE
,INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) EMPNAME
,HAOU.NAME ORGANIZATION_NAME
,PAPF.DATE_OF_BIRTH DOB
,PAPF.ORIGINAL_DATE_OF_HIRE DOJ
,PPOS.ACTUAL_TERMINATION_DATE DOL1
,HR_GENERAL.DECODE_LOOKUP('SEX',PAPF.SEX) GENDER
,PJ.NAME DESIGNATION
,DECODE(haou.type, 'XX_DEPARTMENT', haou.name) DEPARTMENT
,PG.NAME GRADE
,DECODE (HAOU.TYPE,'XX_DIVISION',HAOU.NAME) DIVISION
,PPT.PAYMENT_TYPE_NAME PAYMODE
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',hr_general.decode_lookup('IN_BANK',pea.segment3),'NA') BANK_NAME
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',PEA.SEGMENT1,'NA') ACCOUNTNO
,PAST.USER_STATUS PROCESS_STATUS
,PAPF.EMAIL_ADDRESS EMAIL_ID
,PAPF.PER_INFORMATION4 PAN
,PAPF.PER_INFORMATION8 PF_NO
,PAPF.PER_INFORMATION9 ESINO
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_PERIODS_OF_SERVICE PPOS
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ /*TABLE USED TO FETCH DESIGNATION*/
,PER_GRADES PG /*TABLE USED TO FETCH GRADE*/
,PAY_PAYMENT_TYPES PPT /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_PERSONAL_PAYMENT_METHODS_F PPPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_ORG_PAYMENT_METHODS_F POPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_EXTERNAL_ACCOUNTS PEA /*TABLE USED TO FETCH BANK DETAILS*/
,PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.ASSIGNMENT_ID = L_ASSIGNMENT_ID
AND PAPF.PERSON_ID = L_PERSON_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAAF.ASSIGNMENT_ID = PPPMF.ASSIGNMENT_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND PPPMF.ORG_PAYMENT_METHOD_ID = POPMF.ORG_PAYMENT_METHOD_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND POPMF.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID(+) /*JOINING USED TO FETCH PAYMENT TYPE*/
AND PPPMF.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID(+) /*JOINING USED TO FETCH BANK NAME AND ACCOUNT NO*/
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID /*JOINING USED TO FETCH ASSIGNMENT STATUS*/
AND PAAF.PRIMARY_FLAG = 'Y'
--AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
--AND PAPF.EMPLOYEE_NUMBER ='P1000012'
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND V_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')) )
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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')))
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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')))
)
union
(SELECT PAPF.EMPLOYEE_NUMBER EMPCODE
--,PAPF.PERSON_ID
,INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) EMPNAME
,HAOU.NAME ORGANIZATION_NAME
,PAPF.DATE_OF_BIRTH DOB
,PAPF.ORIGINAL_DATE_OF_HIRE DOJ
,PPOS.ACTUAL_TERMINATION_DATE DOL1
,HR_GENERAL.DECODE_LOOKUP('SEX',PAPF.SEX) GENDER
,PJ.NAME DESIGNATION
,DECODE(haou.type, 'XX_DEPARTMENT', haou.name) DEPARTMENT
,PG.NAME GRADE
,DECODE (HAOU.TYPE,'XX_DIVISION',HAOU.NAME) DIVISION
,PPT.PAYMENT_TYPE_NAME PAYMODE
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',hr_general.decode_lookup('IN_BANK',pea.segment3),'NA') BANK_NAME
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',PEA.SEGMENT1,'NA') ACCOUNTNO
,PAST.USER_STATUS PROCESS_STATUS
,PAPF.EMAIL_ADDRESS EMAIL_ID
,PAPF.PER_INFORMATION4 PAN
,PAPF.PER_INFORMATION8 PF_NO
,PAPF.PER_INFORMATION9 ESINO
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_PERIODS_OF_SERVICE PPOS
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ /*TABLE USED TO FETCH DESIGNATION*/
,PER_GRADES PG /*TABLE USED TO FETCH GRADE*/
,PAY_PAYMENT_TYPES PPT /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_PERSONAL_PAYMENT_METHODS_F PPPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_ORG_PAYMENT_METHODS_F POPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_EXTERNAL_ACCOUNTS PEA /*TABLE USED TO FETCH BANK DETAILS*/
,PER_ASSIGNMENT_STATUS_TYPES PAST
,hr_person_deployments hpd
WHERE
PAPF.PERSON_ID = PAAF.PERSON_ID
--AND PAPF.PERSON_ID IN (9757)
--AND PAAF.ASSIGNMENT_ID IN(9736)
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAPF.PERSON_ID = hpd.TO_PERSON_ID
--AND HPD.TO_PERSON_ID IN (9757)
--and papf.employee_number ='C1002633'
AND HPD.TO_PERSON_ID = L_PERSON_ID
AND PAAF.ASSIGNMENT_ID = L_ASSIGNMENT_ID
AND PAAF.ASSIGNMENT_ID = PPPMF.ASSIGNMENT_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND PPPMF.ORG_PAYMENT_METHOD_ID = POPMF.ORG_PAYMENT_METHOD_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND POPMF.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID(+) /*JOINING USED TO FETCH PAYMENT TYPE*/
AND PPPMF.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID(+) /*JOINING USED TO FETCH BANK NAME AND ACCOUNT NO*/
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID /*JOINING USED TO FETCH ASSIGNMENT STATUS*/
AND PAAF.PRIMARY_FLAG = 'Y'
--AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND V_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')) )
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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))))
OR (
last_day(V_DATE) between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
And last_day(V_DATE) between PAAF.EFFECTIVE_START_DATE and PAAF.EFFECTIVE_END_DATE
and hpd.start_date between V_DATE and last_day(V_DATE)
--AND nvl(PPOS.ACTUAL_TERMINATION_DATE,to_date('31-dec-4712')) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
--AND nvl(PPOS.ACTUAL_TERMINATION_DATE,to_date('31-dec-4712')) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
--AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN to_date('01-may-2011') AND LAST_DAY(to_date('01-may-2011'))
) ));
---------
---||CURSER USED TO FETCH LOCATION DETAILS||
----------
CURSOR CUR_LOCATION_DET(L_PERSON_ID NUMBER) IS
SELECT HRL.LOCATION_CODE LOCATION_NAME
,HRL1.LOCATION_CODE PT_LOCATION
,HRL2.LOCATION_CODE TDS_LOCATION
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,HR_LOCATIONS HRL
,HR_ALL_ORGANIZATION_UNITS HAOU
,HR_ALL_ORGANIZATION_UNITS HAOU1
,PER_PERIODS_OF_SERVICE PPOS
,HR_LOCATIONS HRL1
,HR_LOCATIONS HRL2
,HR_SOFT_CODING_KEYFLEX HSCK
WHERE PAPF.PERSON_ID =L_PERSON_ID
AND PAPF.PERSON_ID =PAAF.PERSON_ID
AND PAPF.PERSON_ID =PPOS.PERSON_ID
AND PAAF.LOCATION_ID =HRL.LOCATION_ID
AND PAAF.SOFT_CODING_KEYFLEX_ID =HSCK.SOFT_CODING_KEYFLEX_ID
AND HSCK.SEGMENT3 =HAOU.ORGANIZATION_ID(+)
AND HAOU.LOCATION_ID =HRL1.LOCATION_ID(+)
AND HSCK.SEGMENT1 =HAOU1.ORGANIZATION_ID
AND HAOU1.LOCATION_ID =HRL2.LOCATION_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE));
--------------
---BEGIN START
--------------
BEGIN
-- FND_FILE.PUT_LINE(FND_FILE.log,'01');
--------
--Converting INPUT MONTH AND YEAR INTO A DATE FORMAT
--------
IF UPPER(P_MONTH) IN('JAN', 'FEB', 'MAR') THEN
V_DATE := FND_CONC_DATE.STRING_TO_DATE('01' || UPPER(P_MONTH) || SUBSTR(P_FIN_YEAR,5));
L_YEAR := SUBSTR(P_FIN_YEAR,6);
ELSE
V_DATE := FND_CONC_DATE.STRING_TO_DATE('01' || UPPER(P_MONTH) || SUBSTR(P_FIN_YEAR,1,4));
L_YEAR := SUBSTR(P_FIN_YEAR,1,4);
END IF;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PR_EMPLOYEE_DETAILS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MONTH>'||P_MONTH||'</MONTH>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FIN_YEAR>'||L_YEAR||'</FIN_YEAR>');
--------
---- || FETCHING STANDARD DAYS IN A PARTICULAR MONTH ||
--------
SELECT DISTINCT (END_DATE-START_DATE)+1 INTO L_STD_DAYS FROM PER_TIME_PERIODS WHERE START_DATE=V_DATE;
FOR REC_ASSIGNMENT_ACTION_ID IN CUR_ASSIGNMENT_ACTION_ID
LOOP
-- FND_FILE.PUT_LINE(FND_FILE.log,'02:'||REC_ASSIGNMENT_ACTION_ID.PERSON_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:Going to call CUR_EMP_PERSONAL_DETAILS~~~~~~~~~~~~~~~~~');
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID : '||REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID : '||REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.PERSON_ID : '||REC_ASSIGNMENT_ACTION_ID.PERSON_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:V_DATE : '||to_char(V_DATE,'DD-Mon-YYYY'));
-------------------
BEGIN
FOR REC_EMP_PERSONAL_DETAILS IN CUR_EMP_PERSONAL_DETAILS(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID
,REC_ASSIGNMENT_ACTION_ID.PERSON_ID
,V_DATE)
LOOP
-- DBMS_OUTPUT.PUT_LINE('REC_EMP_PERSONAL_DETAILS.EMPNAME : '||REC_EMP_PERSONAL_DETAILS.EMPNAME);
-- FND_FILE.PUT_LINE(FND_FILE.log,'~~~~~~~~~~~~~:BEFORE FATHERS NAME');
--------
-- FETCHING FATHER'S NAME BY XXARJ_PR_FHNAME_FNC()
--------
BEGIN
V_FH_NAME :=XXARJ_PR_FHNAME_FNC(REC_ASSIGNMENT_ACTION_ID.PERSON_ID,V_DATE);
-- FND_FILE.PUT_LINE(FND_FILE.log,'03:'||V_FH_NAME);
EXCEPTION WHEN OTHERS THEN
V_FH_NAME:='';
FND_FILE.PUT_LINE(FND_FILE.LOG,'MORE THAN ONE FATHER NAME :'||SQLERRM);
END;
--------
---INTIALIZING VARIABLE
-------
L_BASIC_SALARY_RATE :=0;
L_HRA_RATE :=0;
L_CON_ALL_R :=0;
L_CON_REIM_R :=0;
L_SPECIAL_ALL_R :=0;
L_PETROL_R :=0;
L_MED_ALL_R :=0;
L_DRIVER_R :=0;
L_EDUCATION_ALL_R :=0;
L_SUBSIDY_ALL_R :=0;
L_TELE_R :=0;
L_SITE_ALL_R :=0;
L_HARDSHIP_ALL_R :=0;
L_HARDSHIP_OFF_SHORE_R :=0;
L_SUPER_ANNUATION_R :=0;
L_ONSITE_DAYS :=0;
L_OVERTIME_HOURS :=0;
L_BASIC :=0;
L_BASIC_ARR :=0;
L_HRA :=0;
L_HRA_ARR :=0;
L_CON_ALL :=0;
L_CON_ALLW_ARR :=0;
L_CON_REIM_PAYABLE :=0;
L_CON_REIM_PAYABLE_ARR :=0;
L_SPCIAL_ALL :=0;
L_SPCIAL_ALL_ARR :=0;
L_ADVANCE_AGAINST_PETROL :=0;
L_ADVANCE_AGAINST_PETROL_ARR :=0;
L_MEDICAL_ALLOW :=0;
L_MEDICAL_ALLOW_ARR :=0;
L_ADVANCE_AGAINST_DRIVER :=0;
L_ADVANCE_AGAINST_DRIVER_ARR :=0;
L_EDUCATION_ALL :=0;
L_EDUCATION_ALL_ARR :=0;
L_GROSS_UP_ALL :=0;
L_SUBSIDY_ALLW :=0;
L_SUBSIDY_ALLW_ARR :=0;
L_SUPER_ANNUATION :=0;
L_SUPER_ANNUATION_ARR :=0;
L_TELE :=0;
L_TELE_ARR :=0;
L_SITE_ALL_AMOUNT :=0;
L_SITE_ALL_AMOUNT_ARR :=0;
L_HARDSHIP_AMOUNT_PAYABLE :=0;
L_HARDSHIP_AMOUNT_PAYABLE_ARR :=0;
L_HARDSHIP_OFF_SHORE_PAYA :=0;
L_OT_AMOUNT :=0;
L_OFFSHORE_ALL :=0;
L_LTA :=0;
L_RETANTION_BONUS :=0;
L_OTHER_EARNINGS_NON_TAXABLE :=0;
L_TELEPHONE_ADVANCE :=0;
L_LEAVE_ENCASHMENT :=0;
L_BONUS :=0;
L_GROSS_EARN :=0;
L_PF :=0;
L_ESI :=0;
L_IT :=0;
L_LWF :=0;
L_BUS_BEDN :=0;
L_CANTEEN_DEDN :=0;
L_CITI_BANK_LOAN_DEDUCTION :=0;
L_HDFC_DEDUCTION :=0;
L_CANARA_BANK_LOAN_DEDUCTION :=0;
L_INDUSIND_BANK_DEDUCTION :=0;
L_PF_LOAN :=0;
L_LOAN_AMOUNT :=0;
L_PERSONAL_LOAN :=0;
L_IMPREST_AMOUNT :=0;
L_INSURANCE :=0;
L_OTHER_DEDUCTION :=0;
L_NEG_SAL :=0;
L_NEG_SAL_REC :=0;
L_GROSS_DEDUCTION :=0;
L_NET_SALARY :=0;
L_GROSS_UP_ALL_R :=0;
L_HOUSING_LOAN :=0;
L_NOTICE_PAY_DEDUCTION :=0;
L_PT :=0;
L_HARDSHIP_OFF_SHORE_PAYA_ARR :=0;
L_RELOCATION_AMOUNT :=0;
L_JOINING_BONUS :=0;
L_NOTICE_PAY_PAYMENT :=0;
L_Allowance1 :=0;
L_Ph_Subsidy :=0;
L_Ph_Subsidy_ARR :=0;
L_Notice_pay :=0;
L_Notice_Deduc :=0;
L_LOAN_PERQUISITE :=0;
L_Thai_Allw :=0;
L_R_Thai_Allw :=0;
L_LOAN_AMT_RECO :=0;
L_PHONE_SUBSIDY_R :=0;
----------
--- ||CHECKING IF THE PERSON JOIN IN BETWEEN ANY MONTH|| /
----------
IF REC_EMP_PERSONAL_DETAILS.DOJ BETWEEN V_DATE AND last_day(V_DATE) THEN
V_process_date := REC_EMP_PERSONAL_DETAILS.DOJ;
ELSE
V_process_date := V_DATE;
END IF;
----------
---- || FETCHING RATES OF ELEMENTS BY XXARJ_PR_RATE_CALCULATION_FNC()||
----------
L_BASIC_SALARY_RATE := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Basic Salary','Standard Value', V_process_date);
L_HRA_RATE := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'House Rent Allowance','Standard Value', V_process_date);
L_CON_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Conveyance Allowance','Allowance Amount', V_process_date);
L_CON_REIM_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Conveyance Reimbursement','Allowance Amount', V_process_date);
L_SPECIAL_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Special Allowance','Standard Value', V_process_date);
L_PETROL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Petrol Reimbursement','Allowance Amount', V_process_date);
L_MED_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Medical Reimbursement','Allowance Amount', V_process_date);
L_DRIVER_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Driver Reimbursement','Allowance Amount', V_process_date);
L_EDUCATION_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Education Allowance','Allowance Amount', V_process_date);
L_GROSS_UP_ALL_R := XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Pay Value','Gross UP Allowance', V_process_date);
L_SUBSIDY_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Subsidy Allowance','Allowance Amount', V_process_date);
L_SUPER_ANNUATION_R :=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Superannuation','Cost to Employer', V_process_date);
L_TELE_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Telephone Reimbursement','Allowance Amount', V_process_date);
L_HARDSHIP_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Hardship Allowance','Allowance Amount', V_process_date);
L_HARDSHIP_OFF_SHORE_R:=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Hardship Offshore Allowance','Allowance Amount', V_process_date);
L_PHONE_SUBSIDY_R :=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Phone Subsidy','Allowance Amount', V_process_date);
---------
--- || CALCULATING WORKING DAYS,LOP DAYS AND ONSITE DAYS ||
--------
L_WRK_DAYS := XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Days','Payable Days', V_process_date);
L_LOP_DAYS := L_STD_DAYS - NVL(L_WRK_DAYS,0);
-- L_ONSITE_DAYS := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Site Allowance','Site Days', V_process_date);
L_ONSITE_DAYS := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Site Days','Site Allowance', V_process_date);
----------------
----- || ACTUAL VALUES AND ARREARS OF ELEMENTS FROM RUN RESULT BY XXARJ_PR_ELEMENT_VALUE_FNC()||
----------------
-- FND_FILE.PUT_LINE(FND_FILE.log,'06');
L_OVERTIME_HOURS :=XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Hours','Overtime', V_process_date);
L_BASIC :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Basic Salary', V_process_date);
L_BASIC_ARR :=nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Basic Salary', V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrear Basic Salary', V_process_date),0);
L_HRA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','House Rent Allowance', V_process_date);
L_HRA_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro House Rent Allowance', V_process_date);
L_CON_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Conveyance Allowance', V_process_date);
L_CON_ALLW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Conveyance Allowance', V_process_date);
L_CON_REIM_PAYABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Conveyance Reimbursement', V_process_date);
L_CON_REIM_PAYABLE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Conveyance Reimbursement', V_process_date);
L_SPCIAL_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Special Allowance', V_process_date);
L_SPCIAL_ALL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Special Allowance', V_process_date);
L_ADVANCE_AGAINST_PETROL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Petrol Reimbursement', V_process_date);
L_ADVANCE_AGAINST_PETROL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Petrol Reimbursement', V_process_date);
L_MEDICAL_ALLOW :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Medical Reimbursement', V_process_date);
L_MEDICAL_ALLOW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Medical Reimbursement', V_process_date);
L_ADVANCE_AGAINST_DRIVER :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Driver Reimbursement', V_process_date);
L_ADVANCE_AGAINST_DRIVER_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Driver Reimbursement', V_process_date);
L_EDUCATION_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Education Allowance', V_process_date);
L_EDUCATION_ALL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Education Allowance', V_process_date);
L_GROSS_UP_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Gross UP Allowance', V_process_date);
L_SUBSIDY_ALLW :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Subsidy Allowance', V_process_date);
L_SUBSIDY_ALLW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Subsidy Allowance', V_process_date);
L_SUPER_ANNUATION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Superannuation', V_process_date);
L_SUPER_ANNUATION_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Superannuation', V_process_date);
L_TELE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Telephone Reimbursement', V_process_date);
L_TELE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Telephone Reimbursement', V_process_date);
L_SITE_ALL_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Site Allowance', V_process_date);
-----
IF L_SITE_ALL_AMOUNT >= 0 THEN
L_SITE_ALL_R :=L_BASIC_SALARY_RATE/2;
Else
L_SITE_ALL_R := 0;
L_SITE_ALL_AMOUNT :=0;
END IF;
-------
L_SITE_ALL_AMOUNT_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Site Allowance', V_process_date);
L_HARDSHIP_AMOUNT_PAYABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Hardship Allowance', V_process_date);
L_HARDSHIP_AMOUNT_PAYABLE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro HardShip Allowance', V_process_date);
L_HARDSHIP_OFF_SHORE_PAYA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Hardship Offshore Allowance', V_process_date);
L_HARDSHIP_OFF_SHORE_PAYA_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro HardShip Offshore Allowance', V_process_date);
--
--NEW COLUMNS(18-OCT-2010)
--
L_RELOCATION_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Relocation Allowance', V_process_date);
L_JOINING_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Joining Bonus', V_process_date);
--L_NOTICE_PAY_PAYMENT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay', V_process_date);
L_NOTICE_PAY_PAYMENT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Payment', V_process_date),0)+ NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay', V_process_date),0);
L_OT_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Overtime', V_process_date);
L_OFFSHORE_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Offshore Allowance', V_process_date);
L_LTA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','LTA Payment', V_process_date);
L_RETANTION_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retention Bonus', V_process_date);
L_OTHER_EARNINGS_NON_TAXABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Non Taxable Allowance', V_process_date);
L_TELEPHONE_ADVANCE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Telephone NR Reimbursement', V_process_date);
L_LEAVE_ENCASHMENT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Leave Encashment', V_process_date);
L_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Bonus Amount','Bonus', V_process_date);
L_Allowance1 := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Allowance1', V_process_date);
L_NEG_SAL := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Negative Salary Payment',V_process_date);
L_Ph_Subsidy := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Phone Subsidy', V_process_date); ---Updated By Arjjon
L_Ph_Subsidy_ARR := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Phone Subsidy',V_process_date);
L_Thai_Allw := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Thailand Allowance', V_process_date); ---Updated By Arjjon
L_R_Thai_Allw := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Thailand Allowance', V_process_date); ---Updated By Arjjon
--FND_FILE.PUT_LINE(FND_FILE.log,'*****L_Thai_Allw ++++++++++++++++++++++++++++++:'|| ' ' ||L_Thai_Allw);
L_GROSS_EARN := NVL(L_BASIC,0)
+NVL(L_BASIC_ARR,0)
+NVL(L_HRA,0)
+NVL(L_HRA_ARR,0)
+NVL(L_CON_ALL,0)
+NVL(L_CON_ALLW_ARR,0)
+NVL(L_CON_REIM_PAYABLE,0)
+NVL(L_CON_REIM_PAYABLE_ARR,0)
+NVL(L_SPCIAL_ALL,0)
+NVL(L_SPCIAL_ALL_ARR,0)
+NVL(L_ADVANCE_AGAINST_PETROL,0)
+NVL(L_ADVANCE_AGAINST_PETROL_ARR,0)
+NVL(L_MEDICAL_ALLOW,0)
+NVL(L_MEDICAL_ALLOW_ARR,0)
+NVL(L_ADVANCE_AGAINST_DRIVER,0)
+NVL(L_ADVANCE_AGAINST_DRIVER_ARR,0)
+NVL( L_EDUCATION_ALL,0)
+NVL(L_EDUCATION_ALL_ARR,0)
+NVL(L_GROSS_UP_ALL,0)
+NVL(L_SUBSIDY_ALLW,0)
+NVL(L_SUBSIDY_ALLW_ARR,0)
+NVL(L_SUPER_ANNUATION,0)
+NVL(L_SUPER_ANNUATION_ARR,0)
+NVL(L_TELE,0)
+NVL(L_TELE_ARR,0)
+NVL(L_SITE_ALL_AMOUNT,0)
+NVL(L_SITE_ALL_AMOUNT_ARR,0)
+NVL(L_HARDSHIP_AMOUNT_PAYABLE,0)
+NVL(L_HARDSHIP_AMOUNT_PAYABLE_ARR,0)
+NVL(L_HARDSHIP_OFF_SHORE_PAYA,0)
+NVL(L_HARDSHIP_OFF_SHORE_PAYA_ARR,0)
+NVL(L_RELOCATION_AMOUNT,0)
+NVL(L_JOINING_BONUS,0)
+NVL(L_NOTICE_PAY_PAYMENT,0)
+NVL(L_OT_AMOUNT,0)
+NVL(L_OFFSHORE_ALL,0)
+NVL(L_LTA,0)
+NVL(L_RETANTION_BONUS,0)
+NVL(L_OTHER_EARNINGS_NON_TAXABLE,0)
+NVL(L_TELEPHONE_ADVANCE,0)
+NVL(L_LEAVE_ENCASHMENT,0)
+NVL(L_BONUS,0)
+NVL(L_Allowance1,0)
+NVL(L_Ph_Subsidy,0)
+NVL(L_Ph_Subsidy_ARR,0)
+NVL(L_NEG_SAL,0)
+NVL(L_Thai_Allw,0)
+NVL(L_R_Thai_Allw,0); ---Updated for Phone Subsidy;
DBMS_OUTPUT.PUT_LINE( 'GROSS EARNINGS:'|| ' ' ||L_GROSS_EARN);
-----
-- || CALCULATING DIFFERENT DEDUCTION COLUMNS FROM RUN RESULT BY XXARJ_PR_ELEMENT_VALUE_ASG_FNC()||
-----
L_PF :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee Statutory PF Contribution',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Employee Statutory PF',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Employee Statutory PF',V_process_date),0);
L_ESI :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee ESI Contribution',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Employee ESI',V_process_date),0);
L_IT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Income Tax',V_process_date),0)+
NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','User Declared Income Tax',V_process_date),0)+
NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Tax on Bonus',V_process_date),0);
L_PT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Professional Tax Deduction',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Professional Tax',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Professional Tax',V_process_date),0);
L_LWF :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee LWF Contributions',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Employee Contribution','LWF Adjustment',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrear Employee LWF',V_process_date),0);
L_BUS_BEDN :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bus Deductions',V_process_date),0)+ NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bus Deduction',V_process_date),0);
L_CANTEEN_DEDN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Canteen deduction',V_process_date);
--------
-- ||FETCHING BANK NAME FROM RUN RESULT VALUES BY XXARJ_PR_RUN_RESULT_NAME_FNC() ||
-------
L_BANK_NAME :=XXARJ_PR_RUN_RESULT_NAME_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Bank Name','Bank Loan Recovery',V_process_date);
L_LOAN_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bank Loan Recovery',V_process_date);
-----
-- ||PLACING LOAN AMOUNT INTO PROPER BANK LOAN DEDUCTION COLUMN ||
-----
IF L_BANK_NAME='CITI' THEN
L_CITI_BANK_LOAN_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='HDFC' THEN
L_HDFC_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='CANARA' THEN
L_CANARA_BANK_LOAN_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='INDUSIND' THEN
L_INDUSIND_BANK_DEDUCTION :=L_LOAN_AMOUNT;
END IF;
L_PF_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','PF Loan Deduction',V_process_date);
L_LOAN_TYPE :=XXARJ_PR_RUN_RESULT_NAME_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Loan Type','Loan Amount Recovery',V_process_date);
IF L_LOAN_TYPE='Housing Loan' THEN
L_HOUSING_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Amount Recovery',V_process_date);
ELSIF L_LOAN_TYPE='Personal Loan' THEN
L_PERSONAL_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Amount Recovery',V_process_date);
END IF;
L_IMPREST_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Imprest Deduction',V_process_date);
L_INSURANCE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Insurance Deduction',V_process_date);
L_OTHER_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Other Deductions',V_process_date) ;
L_NEG_SAL_REC := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Negative Salary Recovery',V_process_date);
-- L_NOTICE_PAY_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay',V_process_date);
L_NOTICE_PAY_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Deduction',V_process_date);
L_LOAN_PERQUISITE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Perquisites',V_process_date);
L_LOAN_AMT_RECO :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan',V_process_date);
L_GROSS_DEDUCTION := NVL(L_PF,0)
+NVL(L_ESI,0)
+NVL(L_IT,0)
+NVL(L_PT,0)
+NVL(L_LWF,0)
+NVL(L_BUS_BEDN,0)
+NVL(L_CANTEEN_DEDN,0)
+NVL(L_LOAN_AMOUNT,0)
+NVL(L_PF_LOAN,0)
+NVL(L_PERSONAL_LOAN,0)
+NVL(L_HOUSING_LOAN,0)
+NVL(L_IMPREST_AMOUNT,0)
+NVL(L_INSURANCE,0)
+NVL(L_OTHER_DEDUCTION,0)
+NVL(L_NOTICE_PAY_DEDUCTION,0)
+NVL(L_LOAN_PERQUISITE,0)
+NVL(L_LOAN_AMT_RECO,0)
+NVL(L_NEG_SAL_REC,0);
-- FND_FILE.PUT_LINE(FND_FILE.log,'07');
L_NET_SALARY :=XXARJ_PR_NET_SAL_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Net Pay','_ASG_MTD');
DBMS_OUTPUT.PUT_LINE( 'L_NET_SALARY'|| ' ' ||L_NET_SALARY);
L_C_REMARKS :=XXARJ_REMARKS_NON_REC_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,V_process_date);
-- FND_FILE.PUT_LINE(FND_FILE.log,'*****L_REMARKS :'|| ' ' ||L_C_REMARKS);
-- FND_FILE.PUT_LINE(FND_FILE.log,'08');
-------
-- || GENERATING XML TAGS FOR PERSONAL INFORMATION COLUMNS||
-------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RECORD_SET>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMPCODE || ']]></EMPCODE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPNAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMPNAME || ']]></EMPNAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORGANIZATION_NAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.ORGANIZATION_NAME || ']]></ORGANIZATION_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOB><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOB,'DD-MON-YYYY') || ']]></DOB>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOJ><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOJ,'DD-MON-YYYY') || ']]></DOJ>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOL1><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOL1 ,'DD-MON-YYYY') || ']]></DOL1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GENDER>' ||REC_EMP_PERSONAL_DETAILS.GENDER || '</GENDER>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DESIGNATION><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.DESIGNATION || ']]></DESIGNATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DEPARTMENT><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.DEPARTMENT || ']]></DEPARTMENT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GRADE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.GRADE || ']]></GRADE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DIVISION><![CDATA[' || REC_EMP_PERSONAL_DETAILS.DIVISION || ']]></DIVISION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PAYMODE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PAYMODE || ']]></PAYMODE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BANK_NAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.BANK_NAME || ']]></BANK_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ACCOUNTNO>' ||REC_EMP_PERSONAL_DETAILS.ACCOUNTNO || '</ACCOUNTNO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PROCESS_STATUS>' ||REC_EMP_PERSONAL_DETAILS.PROCESS_STATUS || '</PROCESS_STATUS>');
--------
-- ||CURSOR CUR_LOCATION_DET USED TO FETCH LOCATION DETAILS||
--------
FOR REC_CUR_LOCATION_DET IN CUR_LOCATION_DET(REC_ASSIGNMENT_ACTION_ID.PERSON_ID)
LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.LOCATION_NAME || ']]></LOCATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PT_LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.PT_LOCATION || ']]></PT_LOCATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TDS_LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.TDS_LOCATION || ']]></TDS_LOCATION>');
END LOOP;
----
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMAIL_ID><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMAIL_ID || ']]></EMAIL_ID>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PAN><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PAN || ']]></PAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FHNAME><![CDATA[' ||V_FH_NAME || ']]></FHNAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF_NO><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PF_NO || ']]></PF_NO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ESINO><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.ESINO || ']]></ESINO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC_SALARY_RATE>' || TO_CHAR(ROUND(NVL(L_BASIC_SALARY_RATE,0),2),'FM99999999999.00') || '</BASIC_SALARY_RATE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA_RATE>' || TO_CHAR(ROUND(NVL(L_HRA_RATE,0),2),'FM99999999999.00') || '</HRA_RATE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALL_R>' || TO_CHAR(ROUND(NVL(L_CON_ALL_R,0),2),'FM99999999999.00') || '</CON_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_R>' || TO_CHAR(ROUND(NVL(L_CON_REIM_R,0),2),'FM99999999999.00') || '</CON_REIM_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPECIAL_ALL_R>' || TO_CHAR(ROUND(NVL(L_SPECIAL_ALL_R,0),2),'FM99999999999.00') || '</SPECIAL_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PETROL_R>' || TO_CHAR(ROUND(NVL(L_PETROL_R,0),2),'FM99999999999.00') || '</PETROL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MED_ALL_R>' || TO_CHAR(ROUND(NVL(L_MED_ALL_R,0),2),'FM99999999999.00') || '</MED_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DRIVER_R>' || TO_CHAR(ROUND(NVL(L_DRIVER_R,0),2),'FM99999999999.00') || '</DRIVER_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL_R>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL_R,0),2),'FM99999999999.00') || '</EDUCATION_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_UP_ALL_R>' || TO_CHAR(ROUND(NVL(L_GROSS_UP_ALL_R,0),2),'FM99999999999.00') || '</GROSS_UP_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALL_R>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALL_R,0),2),'FM99999999999.00') || '</SUBSIDY_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION_R>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION_R,0),2),'FM99999999999.00') || '</SUPER_ANNUATION_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE_R>' || TO_CHAR(ROUND(NVL(L_TELE_R,0),2),'FM99999999999.00') || '</TELE_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_R>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_R,0),2),'FM99999999999.00') || '</SITE_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_ALL_R>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_ALL_R,0),2),'FM99999999999.00') || '</HARDSHIP_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_R>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_R,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOP_DAYS>' || NVL(L_LOP_DAYS,0) || '</LOP_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<STD_DAYS>' || NVL(L_STD_DAYS,0) || '</STD_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WRK_DAYS>' || NVL(L_WRK_DAYS,0) || '</WRK_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ONSITE_DAYS>' || NVL(L_ONSITE_DAYS,0) || '</ONSITE_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<B_OS_ASSIGN>' || NVL(L_B_OS_ASSIGN,0) || '</B_OS_ASSIGN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OVERTIME_HOURS>' || NVL(L_OVERTIME_HOURS,0) || '</OVERTIME_HOURS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC>' || TO_CHAR(ROUND(NVL(L_BASIC,0),2),'FM99999999999.00') || '</BASIC>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC_ARR>' || TO_CHAR(ROUND(NVL(L_BASIC_ARR,0),2),'FM99999999999.00') || '</BASIC_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA>' || TO_CHAR(ROUND(NVL(L_HRA,0),2),'FM99999999999.00') || '</HRA>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA_ARR>' || TO_CHAR(ROUND(NVL(L_HRA_ARR,0),2),'FM99999999999.00') || '</HRA_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALL>' || TO_CHAR(ROUND(NVL(L_CON_ALL,0),2),'FM99999999999.00') || '</CON_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALLW_ARR>' || TO_CHAR(ROUND(NVL(L_CON_ALLW_ARR,0),2),'FM99999999999.00') || '</CON_ALLW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_PAYABLE>' || TO_CHAR(ROUND(NVL(L_CON_REIM_PAYABLE,0),2),'FM99999999999.00') || '</CON_REIM_PAYABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_PAYABLE_ARR>' || TO_CHAR(ROUND(NVL(L_CON_REIM_PAYABLE_ARR,0),2),'FM99999999999.00') || '</CON_REIM_PAYABLE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPCIAL_ALL>' || TO_CHAR(ROUND(NVL(L_SPCIAL_ALL,0),2),'FM99999999999.00') || '</SPCIAL_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPCIAL_ALL_ARR>' || TO_CHAR(ROUND(NVL(L_SPCIAL_ALL_ARR,0),2),'FM99999999999.00') || '</SPCIAL_ALL_ARR>');
--FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_allow>' || TO_CHAR(ROUND(NVL(L_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_allow>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Allowance1>' || TO_CHAR(ROUND(NVL(L_Allowance1,0),2),'FM99999999999.00') || '</Allowance1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_allow>' || TO_CHAR(ROUND(NVL(L_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_allow>');-- Updated for Thai Allw pay
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_r_allow>' || TO_CHAR(ROUND(NVL(L_R_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_r_allow>');-- Updated for Retro Thai Allw
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy_ARR>' || TO_CHAR(ROUND(NVL(L_Ph_Subsidy_ARR,0),2),'FM99999999999.00') || '</PH_Subsidy_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy>' || TO_CHAR(ROUND(NVL(L_Ph_Subsidy,0),2),'FM99999999999.00') || '</PH_Subsidy>');-- Updated for Phone Subsidy
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy_rate>' || TO_CHAR(ROUND(NVL(L_PHONE_SUBSIDY_R,0),2),'FM99999999999.00') || '</PH_Subsidy_rate>');-- Updated for Phone Subsidy
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</RECORD_SET>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RECORD_SET1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_PETROL>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_PETROL,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_PETROL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_PETROL_ARR>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_PETROL_ARR,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_PETROL_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MEDICAL_ALLOW>' || TO_CHAR(ROUND(NVL(L_MEDICAL_ALLOW,0),2),'FM99999999999.00') || '</MEDICAL_ALLOW>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MEDICAL_ALLOW_ARR>' || TO_CHAR(ROUND(NVL(L_MEDICAL_ALLOW_ARR,0),2),'FM99999999999.00') || '</MEDICAL_ALLOW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_DRIVER>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_DRIVER,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_DRIVER>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_DRIVER_ARR>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_DRIVER_ARR,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_DRIVER_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL,0),2),'FM99999999999.00') || '</EDUCATION_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL_ARR>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL_ARR,0),2),'FM99999999999.00') || '</EDUCATION_ALL_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_UP_ALL>' || TO_CHAR(ROUND(NVL(L_GROSS_UP_ALL,0),2),'FM99999999999.00') || '</GROSS_UP_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALLW>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALLW,0),2),'FM99999999999.00') || '</SUBSIDY_ALLW>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALLW_ARR>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALLW_ARR,0),2),'FM99999999999.00') || '</SUBSIDY_ALLW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION,0),2),'FM99999999999.00') || '</SUPER_ANNUATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION_ARR>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION_ARR,0),2),'FM99999999999.00') || '</SUPER_ANNUATION_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE>' || TO_CHAR(ROUND(NVL(L_TELE,0),2),'FM99999999999.00') || '</TELE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE_ARR>' || TO_CHAR(ROUND(NVL(L_TELE_ARR,0),2),'FM99999999999.00') || '</TELE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_AMOUNT>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_AMOUNT,0),2),'FM99999999999.00') || '</SITE_ALL_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_AMOUNT_ARR>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_AMOUNT_ARR,0),2),'FM99999999999.00') || '</SITE_ALL_AMOUNT_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_AMOUNT_PAYABLE>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_AMOUNT_PAYABLE,0),2),'FM99999999999.00') || '</HARDSHIP_AMOUNT_PAYABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_AMOUNT_PAYABLE_ARR>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_AMOUNT_PAYABLE_ARR,0),2),'FM99999999999.00') || '</HARDSHIP_AMOUNT_PAYABLE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_PAYA>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_PAYA,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_PAYA>');
---
--NEW COLUMNS
---
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_PAYA_ARR>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_PAYA_ARR,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_PAYA_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RELOCATION_AMOUNT>' || TO_CHAR(ROUND(NVL(L_RELOCATION_AMOUNT,0),2),'FM99999999999.00') || '</RELOCATION_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_BONUS>' || TO_CHAR(ROUND(NVL(L_JOINING_BONUS,0),2),'FM99999999999.00') || '</JOINING_BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NOTICE_PAY_PAYMENT>' || TO_CHAR(ROUND(NVL(L_NOTICE_PAY_PAYMENT,0),2),'FM99999999999.00') || '</NOTICE_PAY_PAYMENT>');
---
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OT_AMOUNT>' || TO_CHAR(ROUND(NVL(L_OT_AMOUNT,0),2),'FM99999999999.00') || '</OT_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OFFSHORE_ALL>' || TO_CHAR(ROUND(NVL(L_OFFSHORE_ALL,0),2),'FM99999999999.00') || '</OFFSHORE_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LTA>' || TO_CHAR(ROUND(NVL(L_LTA,0),2),'FM99999999999.00') || '</LTA>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RETANTION_BONUS>' || TO_CHAR(ROUND(NVL(L_RETANTION_BONUS,0),2),'FM99999999999.00') || '</RETANTION_BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OTHER_EARNINGS_NON_TAXABLE>' || TO_CHAR(ROUND(NVL(L_OTHER_EARNINGS_NON_TAXABLE,0),2),'FM99999999999.00') || '</OTHER_EARNINGS_NON_TAXABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELEPHONE_ADVANCE>' || TO_CHAR(ROUND(NVL(L_TELEPHONE_ADVANCE,0),2),'FM99999999999.00') || '</TELEPHONE_ADVANCE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LEAVE_ENCASHMENT>' || TO_CHAR(ROUND(NVL(L_LEAVE_ENCASHMENT,0),2),'FM99999999999.00') || '</LEAVE_ENCASHMENT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BONUS>' || TO_CHAR(ROUND(NVL(L_BONUS,0),2),'FM99999999999.00') || '</BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NEG_SAL>' || TO_CHAR(ROUND(NVL(L_NEG_SAL,0),2),'FM99999999999.00') || '</NEG_SAL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_EARN>' || TO_CHAR(ROUND(NVL(L_GROSS_EARN,0),2),'FM99999999999.00') || '</GROSS_EARN>');
------
-- || GENERATING XML TAFS FOR DEDUCTION ELEMNTS ||
------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF>' || TO_CHAR(ROUND(NVL(L_PF,0),2),'FM99999999999.00') || '</PF>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ESI>' || TO_CHAR(ROUND(NVL(L_ESI,0),2),'FM99999999999.00') || '</ESI>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<IT>' || TO_CHAR(ROUND(NVL(L_IT,0),2),'FM99999999999.00') || '</IT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PT>' || TO_CHAR(ROUND(NVL(L_PT,0),2),'FM99999999999.00') || '</PT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LWF>' || TO_CHAR(ROUND(NVL(L_LWF,0),2),'FM99999999999.00') || '</LWF>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BUS_BEDN>' || TO_CHAR(ROUND(NVL(L_BUS_BEDN,0),2),'FM99999999999.00') || '</BUS_BEDN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CANTEEN_DEDN>' || TO_CHAR(ROUND(NVL(L_CANTEEN_DEDN,0),2),'FM99999999999.00') || '</CANTEEN_DEDN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_CITI_BANK_LOAN_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_CITI_BANK_LOAN_DEDUCTION,0),2),'FM99999999999.00') || '</L_CITI_BANK_LOAN_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_HDFC_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_HDFC_DEDUCTION,0),2),'FM99999999999.00') || '</L_HDFC_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_CANARA_BANK_LOAN_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_CANARA_BANK_LOAN_DEDUCTION,0),2),'FM99999999999.00') || '</L_CANARA_BANK_LOAN_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_INDUSIND_BANK_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_INDUSIND_BANK_DEDUCTION,0),2),'FM99999999999.00') || '</L_INDUSIND_BANK_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF_LOAN>' || TO_CHAR(ROUND(NVL(L_PF_LOAN,0),2),'FM99999999999.00') || '</PF_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PERSONAL_LOAN>' || TO_CHAR(ROUND(NVL(L_PERSONAL_LOAN,0),2),'FM99999999999.00') || '</PERSONAL_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HOUSING_LOAN>' || TO_CHAR(ROUND(NVL(L_HOUSING_LOAN,0),2),'FM99999999999.00') || '</HOUSING_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<IMPREST_AMOUNT>' || TO_CHAR(ROUND(NVL(L_IMPREST_AMOUNT,0),2),'FM99999999999.00') || '</IMPREST_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INSURANCE>' || TO_CHAR(ROUND(NVL(L_INSURANCE,0),2),'FM99999999999.00') || '</INSURANCE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OTHER_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_OTHER_DEDUCTION,0),2),'FM99999999999.00') || '</OTHER_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOAN_PERQUISITE>' || TO_CHAR(ROUND(NVL(L_LOAN_PERQUISITE,0),2),'FM99999999999.00') || '</LOAN_PERQUISITE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOAN_AMT_RECO>' || TO_CHAR(ROUND(NVL(L_LOAN_AMT_RECO,0),2),'FM99999999999.00') || '</LOAN_AMT_RECO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NEG_SAL_REC>' || TO_CHAR(ROUND(NVL(L_NEG_SAL_REC,0),2),'FM99999999999.00') || '</NEG_SAL_REC>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NOTICE_PAY_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_NOTICE_PAY_DEDUCTION,0),2),'FM99999999999.00') || '</NOTICE_PAY_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_GROSS_DEDUCTION,0),2),'FM99999999999.00') || '</GROSS_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NET_SALARY>' || TO_CHAR(ROUND(NVL(L_NET_SALARY,0),2),'FM99999999999.00') || '</NET_SALARY>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<C_REMARKS><![CDATA[' || L_C_REMARKS || ']]></C_REMARKS>');
--FND_FILE.PUT_LINE(FND_FILE.log,L_C_REMARKS);
-- FND_FILE.PUT_LINE(FND_FILE.log,'-------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</RECORD_SET1>');
END LOOP;
--DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'**~~~~~~~***ERROR**~~~~~**** :'||SQLERRM||'FOR ASSIGNMENT ID '||REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID);
END;
-------------------------------------------------
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</PR_EMPLOYEE_DETAILS>');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('DATA DOES NOT EXISTS');
FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA DOES NOT EXISTS'||SQLERRM);
END XXARJ_PAY_REGISTER_REPORT_PRC;
--------
-- ||END OF XXARJ_PAY_REGISTER_REPORT_PRC (PLL INDIA)||
--------
END XXARJ_PAY_REG_REP_PKG_TEST;
PACKAGE BODY XXARJ_PAY_REG_REP_PKG_TEST
AS
---
--- XXARJ_PR_CALC_SITE_FNC used to calculate RATE of an element
---
FUNCTION XXARJ_PR_CALC_SITE_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF
WHERE PRR.ASSIGNMENT_ACTION_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION WHEN OTHERS THEN
L_ELEMENT_VALUE :=-1;
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_CALC_SITE_FNC;
----------
--- ||XXARJ_PR_RATE_CALCULATION_FNC used to calculate RATE of an element||
-----------
FUNCTION "XXARJ_PR_RATE_CALCULATION_FNC"
(
P_ASSIGNMENT_ID NUMBER,
P_ELEMENT_NAME VARCHAR2,
P_INPUT_VALUE_NAME VARCHAR2,
P_DATE DATE
) RETURN NUMBER
AS
L_BASIC_RATE NUMBER:=0;
BEGIN
SELECT fnd_number.canonical_to_number(PEEVF.SCREEN_ENTRY_VALUE)
INTO L_BASIC_RATE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_ELEMENT_ENTRIES_F PEEF,
PAY_ELEMENT_ENTRY_VALUES_F PEEVF,
PAY_INPUT_VALUES_F PIVF
WHERE PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PEEF.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PEEF.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PEEF.ELEMENT_ENTRY_ID = PEEVF.ELEMENT_ENTRY_ID
AND PIVF.INPUT_VALUE_ID = PEEVF.INPUT_VALUE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PEEF.EFFECTIVE_START_DATE AND PEEF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PEEVF.EFFECTIVE_START_DATE AND PEEVF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_BASIC_RATE;
EXCEPTION WHEN OTHERS THEN
RETURN NULL;
END XXARJ_PR_RATE_CALCULATION_FNC;
-------------
-- ||XXARJ_PR_ELEMENT_VALUE_ASG_FNC used to calculate sum of Arrears From RUN RESULT VALUE of an element ||
-------------
FUNCTION XXARJ_PR_ELEMENT_VALUE_ASG_FNC
(
P_ASSIGNMENT_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
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 PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
And paa.source_action_id is not null
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_ELEMENT_VALUE_ASG_FNC;
------------
----||FUNCTION:XXARJ_PR_ELEMENT_VALUE_FNC used to Fetch RUN RESULT VALUE of an element ||
-----------
FUNCTION XXARJ_PR_ELEMENT_VALUE_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN NUMBER
AS
L_ELEMENT_VALUE NUMBER:=0;
BEGIN
SELECT SUM(fnd_number.canonical_to_number(NVL(PRRV.RESULT_VALUE,0)))
INTO L_ELEMENT_VALUE
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF
WHERE PRR.ASSIGNMENT_ACTION_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
RETURN L_ELEMENT_VALUE;
EXCEPTION
WHEN OTHERS THEN
RETURN L_ELEMENT_VALUE;
END XXARJ_PR_ELEMENT_VALUE_FNC;
---------------
---- ||FUNCTION:XXARJ_PR_RUN_RESULT_NAME_FNC used to Fetch RUN RESULT VALUE of an element which return Charecter value like Bank name ||
----------------
FUNCTION XXARJ_PR_RUN_RESULT_NAME_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER
,P_INPUT_VALUE_NAME VARCHAR2
,P_ELEMENT_NAME VARCHAR2
,P_DATE DATE
) RETURN VARCHAR2
AS
L_BANK_NAME VARCHAR2(150):='';
BEGIN
SELECT PRRV.RESULT_VALUE
INTO L_BANK_NAME
FROM PAY_ELEMENT_TYPES_F PETF,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRRV,
PAY_INPUT_VALUES_F PIVF,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_PAYROLL_ACTIONS PPA
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 PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ACTION_ID
AND PETF.ELEMENT_NAME = P_ELEMENT_NAME
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PIVF.NAME = P_INPUT_VALUE_NAME
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.value('PER_BUSINESS_GROUP_ID'))
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE
and rownum<=1;
RETURN L_BANK_NAME;
EXCEPTION
WHEN OTHERS THEN
RETURN L_BANK_NAME;
END XXARJ_PR_RUN_RESULT_NAME_FNC;
----------
---||FUNCTION:XXARJ_PR_NET_SAL_FNC used to CALCULATE NET SALARY||
---------
FUNCTION XXARJ_PR_NET_SAL_FNC
(
P_ASSIGNMENT_ACTION_ID NUMBER,
P_BAL_NAME IN VARCHAR2 ,
P_DIM_NAME IN VARCHAR2 )
RETURN VARCHAR2
AS
L_NET_SALARY NUMBER:=0;
V_NET_PAY_ID NUMBER;
BEGIN
/*V_NET_PAY_ID := XXARJ_pay_payroll_reports_pkg.get_defined_bal_id(P_BAL_NAME, P_DIM_NAME);*/
BEGIN
SELECT pdb.defined_balance_id
INTO V_NET_PAY_ID
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pdb.balance_type_id = pbt.balance_type_id
AND pdb.balance_dimension_id = pbd.balance_dimension_id
AND pbt.balance_name = P_BAL_NAME
AND (pbt.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbt.legislation_code = 'IN')
AND (pbd.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pbd.legislation_code = 'IN')
AND (pdb.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID') or pdb.legislation_code = 'IN')
AND pbd.database_item_suffix = P_DIM_NAME;
EXCEPTION WHEN OTHERS THEN
V_NET_PAY_ID :=1;
END;
L_NET_SALARY := pay_balance_pkg.get_value(V_NET_PAY_ID, P_ASSIGNMENT_ACTION_ID);
--FND_FILE.PUT_LINE(FND_FILE.log,'~~L_NET_SALARY :'|| ' ' ||L_NET_SALARY);
RETURN L_NET_SALARY;
EXCEPTION
WHEN OTHERS THEN
RETURN L_NET_SALARY;
END XXARJ_PR_NET_SAL_FNC;
--------
--||FUNCTION:XXARJ_PR_FHNAME_FNC used to FIND FATHERS NAME||
--------
FUNCTION XXARJ_PR_FHNAME_FNC
(
P_PERSON_ID IN NUMBER
,P_DATE IN DATE
)
RETURN VARCHAR2 AS
L_FHNAME VARCHAR2(150);
BEGIN
SELECT INITCAP(PAPF1.FIRST_NAME
||' '
|| PAPF1.MIDDLE_NAMES
||' '
|| PAPF1.LAST_NAME) INTO L_FHNAME
FROM PER_ALL_PEOPLE_F PAPF1,
PER_CONTACT_RELATIONSHIPS PCR
WHERE PCR.PERSON_ID = P_PERSON_ID
AND PAPF1.PERSON_ID = PCR.CONTACT_PERSON_ID
AND PCR.CONTACT_TYPE = 'JP_FT'
AND P_DATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE;
RETURN L_FHNAME;
EXCEPTION WHEN OTHERS THEN
RETURN L_FHNAME;
END XXARJ_PR_FHNAME_FNC;
---------------
-- ||FUNCTION:XXARJ_REMARKS_NON_REC_FNC used to FIND Remarks i.e. List of all non recurring elemnts||
---------------
FUNCTION XXARJ_REMARKS_NON_REC_FNC(
P_ASSIGNMENT_ID IN NUMBER
,P_DATE IN DATE
)
RETURN VARCHAR2 AS
L_REMARKS VARCHAR2(10000):='';
CURSOR CUR_NON_REC_VALUE IS
SELECT NVL(PETF.ELEMENT_NAME,'') ELEMENT_NAME,
(NVL(PRRV.RESULT_VALUE,0)) RESULT_VALUE
FROM PAY_RUN_RESULT_VALUES PRRV,
PAY_RUN_RESULTS PRR,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ELEMENT_TYPES_F PETF,
PAY_INPUT_VALUES_F PIVF,
PAY_PAYROLL_ACTIONS PPA
WHERE PPA.ACTION_TYPE IN('R', 'Q', 'I', 'B')
AND PPA.ACTION_STATUS = 'C'
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = P_ASSIGNMENT_ID
AND PETF.PROCESSING_TYPE = 'N'
AND PIVF.NAME = 'Pay Value'
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;
/*SELECT NVL(PETF.ELEMENT_NAME,'') ELEMENT_NAME,
(NVL(PRRV.RESULT_VALUE,0)) RESULT_VALUE
FROM PAY_RUN_RESULT_VALUES PRRV,
PAY_RUN_RESULTS PRR,
PAY_ASSIGNMENT_ACTIONS PAA,
PAY_ELEMENT_TYPES_F PETF,
PAY_INPUT_VALUES_F PIVF
WHERE PRRV.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND PRR.ELEMENT_TYPE_ID = PETF.ELEMENT_TYPE_ID
AND PIVF.INPUT_VALUE_ID = PRRV.INPUT_VALUE_ID
AND PAA.ACTION_STATUS = 'C'
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PAA.ASSIGNMENT_ID = 21786
AND PETF.PROCESSING_TYPE = 'N'
AND PIVF.NAME = 'Pay Value'
AND (PETF.LEGISLATION_CODE = 'IN' OR PETF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND (PIVF.LEGISLATION_CODE = 'IN' OR PIVF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND PETF.ELEMENT_TYPE_ID = PIVF.ELEMENT_TYPE_ID
AND PPA.DATE_EARNED BETWEEN P_DATE AND LAST_DAY(P_DATE)
AND P_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE
AND P_DATE BETWEEN PIVF.EFFECTIVE_START_DATE AND PIVF.EFFECTIVE_END_DATE;*/
BEGIN
L_REMARKS :='';
FOR REC_CUR_NON_REC_VALUE IN CUR_NON_REC_VALUE
LOOP
--L_REMARKS:=L_REMARKS||' '||REC_CUR_NON_REC_VALUE.ELEMENT_NAME||'='||REC_CUR_NON_REC_VALUE.RESULT_VALUE ||' ';
L_REMARKS:=L_REMARKS||' '||REC_CUR_NON_REC_VALUE.ELEMENT_NAME||' , ';
END LOOP;
RETURN L_REMARKS;
EXCEPTION WHEN OTHERS THEN
RETURN L_REMARKS;
END XXARJ_REMARKS_NON_REC_FNC;
-----------
-- ||PROCEDURE:XXARJ_PAY_REGISTER_REPORT_PROC ||
-- ||MODIFIED ON:18-AUG-2010 ||
-- ||MODIFIED BY:SAHA GOPAL ||
-- ||THIS PROCEDURE IS USED FOR PAY REGISTER REPORT PLL INDIA ||
-----------
PROCEDURE XXARJ_PAY_REGISTER_REPORT_PRC(
ERRBUFF OUT NOCOPY VARCHAR2 ,
RETCODE OUT NOCOPY NUMBER ,
P_FIN_YEAR IN VARCHAR2,
P_MONTH IN VARCHAR2,
P_PAYROLL_ID IN VARCHAR2,
P_ORG_ID IN VARCHAR2
) IS
V_DATE DATE;
L_ASSIGNMENT_ID NUMBER;
L_YEAR NUMBER;
L_BASIC_SALARY_RATE NUMBER;
L_HRA_RATE NUMBER;
L_CON_ALL_R NUMBER;
L_CON_REIM_R NUMBER;
L_SPECIAL_ALL_R NUMBER;
L_PETROL_R NUMBER;
L_MED_ALL_R NUMBER;
L_DRIVER_R NUMBER;
L_EDUCATION_ALL_R NUMBER;
L_SUBSIDY_ALL_R NUMBER;
L_TELE_R NUMBER;
L_SITE_ALL_R NUMBER;
L_HARDSHIP_ALL_R NUMBER;
L_HARDSHIP_OFF_SHORE_R NUMBER;
L_STD_DAYS NUMBER;
L_SUPER_ANNUATION_R NUMBER;
L_ONSITE_DAYS NUMBER;
L_OVERTIME_HOURS NUMBER;
L_BASIC NUMBER;
L_BASIC_ARR NUMBER;
L_HRA NUMBER;
L_HRA_ARR NUMBER;
L_CON_ALL NUMBER;
L_CON_ALLW_ARR NUMBER;
L_CON_REIM_PAYABLE NUMBER;
L_CON_REIM_PAYABLE_ARR NUMBER;
L_SPCIAL_ALL NUMBER;
L_SPCIAL_ALL_ARR NUMBER;
L_ADVANCE_AGAINST_PETROL NUMBER;
L_ADVANCE_AGAINST_PETROL_ARR NUMBER;
L_MEDICAL_ALLOW NUMBER;
L_MEDICAL_ALLOW_ARR NUMBER;
L_ADVANCE_AGAINST_DRIVER NUMBER;
L_ADVANCE_AGAINST_DRIVER_ARR NUMBER;
L_EDUCATION_ALL NUMBER;
L_EDUCATION_ALL_ARR NUMBER;
L_GROSS_UP_ALL NUMBER;
L_SUBSIDY_ALLW NUMBER;
L_SUBSIDY_ALLW_ARR NUMBER;
L_SUPER_ANNUATION NUMBER;
L_SUPER_ANNUATION_ARR NUMBER;
L_TELE NUMBER;
L_TELE_ARR NUMBER;
L_SITE_ALL_AMOUNT NUMBER;
L_SITE_ALL_AMOUNT_ARR NUMBER;
L_HARDSHIP_AMOUNT_PAYABLE NUMBER;
L_HARDSHIP_AMOUNT_PAYABLE_ARR NUMBER;
L_HARDSHIP_OFF_SHORE_PAYA NUMBER;
L_OT_AMOUNT NUMBER;
L_OFFSHORE_ALL NUMBER;
L_LTA NUMBER;
L_RETANTION_BONUS NUMBER;
L_NEG_SAL NUMBER;
L_OTHER_EARNINGS_NON_TAXABLE NUMBER;
L_TELEPHONE_ADVANCE NUMBER;
L_LEAVE_ENCASHMENT NUMBER;
L_BONUS NUMBER;
L_GROSS_EARN NUMBER;
L_PF NUMBER;
L_ESI NUMBER;
L_IT NUMBER;
L_LWF NUMBER;
L_BUS_BEDN NUMBER;
L_CANTEEN_DEDN NUMBER;
L_BANK_NAME VARCHAR2(50);
L_CITI_BANK_LOAN_DEDUCTION NUMBER:=0;
L_HDFC_DEDUCTION NUMBER:=0;
L_CANARA_BANK_LOAN_DEDUCTION NUMBER:=0;
L_INDUSIND_BANK_DEDUCTION NUMBER:=0;
L_PF_LOAN NUMBER;
L_LOAN_AMOUNT NUMBER;
L_PERSONAL_LOAN NUMBER;
L_IMPREST_AMOUNT NUMBER;
L_INSURANCE NUMBER;
L_OTHER_DEDUCTION NUMBER;
L_NEG_SAL_REC NUMBER;
L_GROSS_DEDUCTION NUMBER;
L_COUNT NUMBER;
L_NET_SALARY NUMBER;
L_GROSS_UP_ALL_R NUMBER;
L_TDS_LOCATION NUMBER;
L_PT_LOCATION NUMBER;
L_ASG_LOCATION NUMBER;
V_FH_NAME VARCHAR2(150);
L_LOAN_TYPE VARCHAR2(150);
L_HOUSING_LOAN NUMBER;
L_NOTICE_PAY_DEDUCTION NUMBER;
L_C_REMARKS VARCHAR2(10000):='';
L_LOP_DAYS NUMBER:=0;
L_WRK_DAYS NUMBER:=0;
L_B_OS_ASSIGN NUMBER:=0;
V_process_date DATE;
L_PT NUMBER;
L_HARDSHIP_OFF_SHORE_PAYA_ARR NUMBER;
L_RELOCATION_AMOUNT NUMBER;
L_JOINING_BONUS NUMBER;
L_NOTICE_PAY_PAYMENT NUMBER;
L_Allowance1 NUMBER;
L_Ph_Subsidy NUMBER;
L_Ph_Subsidy_ARR NUMBER;
L_Notice_pay NUMBER;
L_Notice_Deduc NUMBER;
L_LOAN_PERQUISITE NUMBER;
L_R_Thai_Allw NUMBER;
L_Thai_Allw NUMBER;
L_LOAN_AMT_RECO NUMBER;
L_PHONE_SUBSIDY_R NUMBER;
----------
----||CURSER USED TO FETCH ASSIGNMENT ACTION ID AND ASSIGNMENT ID||
---------
CURSOR CUR_ASSIGNMENT_ACTION_ID IS
SELECT MAX(PAA.ASSIGNMENT_ACTION_ID) ASG_ACT_ID
,PAA.ASSIGNMENT_ID ASSIGNMENT_ID
,PAAF.PERSON_ID PERSON_ID
FROM PER_TIME_PERIODS PTP
,PAY_ASSIGNMENT_ACTIONS PAA
,PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PAY_PAYROLL_ACTIONS PPA
,PER_PERIODS_OF_SERVICE PPOS
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 paa.source_action_id is not null
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAPF.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND PAAF.ORGANIZATION_ID = NVL(P_ORG_ID, PAAF.ORGANIZATION_ID)
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_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 PAAF.PAYROLL_ID = P_PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE)
AND ( V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
OR PAAF.EFFECTIVE_START_DATE BETWEEN V_DATE AND LAST_DAY(V_DATE))
GROUP BY PAA.ASSIGNMENT_ID ,PAAF.PERSON_ID;
--------
---||CURSER USED TO FETCH EMPLOYEE'S PERSONAL DETAILS||
-------
CURSOR CUR_EMP_PERSONAL_DETAILS(L_ASSIGNMENT_ID NUMBER,L_PERSON_ID NUMBER,V_DATE DATE) IS
SELECT PAPF.EMPLOYEE_NUMBER EMPCODE
,INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) EMPNAME
,HAOU.NAME ORGANIZATION_NAME
,PAPF.DATE_OF_BIRTH DOB
,PAPF.ORIGINAL_DATE_OF_HIRE DOJ
,PPOS.ACTUAL_TERMINATION_DATE DOL1
,HR_GENERAL.DECODE_LOOKUP('SEX',PAPF.SEX) GENDER
,PJ.NAME DESIGNATION
,DECODE(haou.type, 'XX_DEPARTMENT', haou.name) DEPARTMENT
,PG.NAME GRADE
,DECODE (HAOU.TYPE,'XX_DIVISION',HAOU.NAME) DIVISION
,PPT.PAYMENT_TYPE_NAME PAYMODE
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',hr_general.decode_lookup('IN_BANK',pea.segment3),'NA') BANK_NAME
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',PEA.SEGMENT1,'NA') ACCOUNTNO
,PAST.USER_STATUS PROCESS_STATUS
,PAPF.EMAIL_ADDRESS EMAIL_ID
,PAPF.PER_INFORMATION4 PAN
,PAPF.PER_INFORMATION8 PF_NO
,PAPF.PER_INFORMATION9 ESINO
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_PERIODS_OF_SERVICE PPOS
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ /*TABLE USED TO FETCH DESIGNATION*/
,PER_GRADES PG /*TABLE USED TO FETCH GRADE*/
,PAY_PAYMENT_TYPES PPT /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_PERSONAL_PAYMENT_METHODS_F PPPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_ORG_PAYMENT_METHODS_F POPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_EXTERNAL_ACCOUNTS PEA /*TABLE USED TO FETCH BANK DETAILS*/
,PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE PAAF.ASSIGNMENT_ID = L_ASSIGNMENT_ID
AND PAPF.PERSON_ID = L_PERSON_ID
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAAF.ASSIGNMENT_ID = PPPMF.ASSIGNMENT_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND PPPMF.ORG_PAYMENT_METHOD_ID = POPMF.ORG_PAYMENT_METHOD_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND POPMF.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID(+) /*JOINING USED TO FETCH PAYMENT TYPE*/
AND PPPMF.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID(+) /*JOINING USED TO FETCH BANK NAME AND ACCOUNT NO*/
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID /*JOINING USED TO FETCH ASSIGNMENT STATUS*/
AND PAAF.PRIMARY_FLAG = 'Y'
--AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
--AND PAPF.EMPLOYEE_NUMBER ='P1000012'
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND V_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')) )
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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')))
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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')))
)
union
(SELECT PAPF.EMPLOYEE_NUMBER EMPCODE
--,PAPF.PERSON_ID
,INITCAP(PAPF.FIRST_NAME
||' '
|| PAPF.MIDDLE_NAMES
||' '
|| PAPF.LAST_NAME) EMPNAME
,HAOU.NAME ORGANIZATION_NAME
,PAPF.DATE_OF_BIRTH DOB
,PAPF.ORIGINAL_DATE_OF_HIRE DOJ
,PPOS.ACTUAL_TERMINATION_DATE DOL1
,HR_GENERAL.DECODE_LOOKUP('SEX',PAPF.SEX) GENDER
,PJ.NAME DESIGNATION
,DECODE(haou.type, 'XX_DEPARTMENT', haou.name) DEPARTMENT
,PG.NAME GRADE
,DECODE (HAOU.TYPE,'XX_DIVISION',HAOU.NAME) DIVISION
,PPT.PAYMENT_TYPE_NAME PAYMODE
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',hr_general.decode_lookup('IN_BANK',pea.segment3),'NA') BANK_NAME
,DECODE(PPT.PAYMENT_TYPE_NAME,'India Direct Deposit',PEA.SEGMENT1,'NA') ACCOUNTNO
,PAST.USER_STATUS PROCESS_STATUS
,PAPF.EMAIL_ADDRESS EMAIL_ID
,PAPF.PER_INFORMATION4 PAN
,PAPF.PER_INFORMATION8 PF_NO
,PAPF.PER_INFORMATION9 ESINO
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,PER_PERIODS_OF_SERVICE PPOS
,HR_ALL_ORGANIZATION_UNITS HAOU
,PER_JOBS PJ /*TABLE USED TO FETCH DESIGNATION*/
,PER_GRADES PG /*TABLE USED TO FETCH GRADE*/
,PAY_PAYMENT_TYPES PPT /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_PERSONAL_PAYMENT_METHODS_F PPPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_ORG_PAYMENT_METHODS_F POPMF /*TABLE USED TO FETCH BANK DETAILS*/
,PAY_EXTERNAL_ACCOUNTS PEA /*TABLE USED TO FETCH BANK DETAILS*/
,PER_ASSIGNMENT_STATUS_TYPES PAST
,hr_person_deployments hpd
WHERE
PAPF.PERSON_ID = PAAF.PERSON_ID
--AND PAPF.PERSON_ID IN (9757)
--AND PAAF.ASSIGNMENT_ID IN(9736)
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID
AND PAAF.JOB_ID = PJ.JOB_ID(+)
AND PAAF.GRADE_ID = PG.GRADE_ID(+)
AND PAPF.PERSON_ID = hpd.TO_PERSON_ID
--AND HPD.TO_PERSON_ID IN (9757)
--and papf.employee_number ='C1002633'
AND HPD.TO_PERSON_ID = L_PERSON_ID
AND PAAF.ASSIGNMENT_ID = L_ASSIGNMENT_ID
AND PAAF.ASSIGNMENT_ID = PPPMF.ASSIGNMENT_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND PPPMF.ORG_PAYMENT_METHOD_ID = POPMF.ORG_PAYMENT_METHOD_ID(+) /*JOINING USED TO FETCH BANK DETAILS*/
AND POPMF.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID(+) /*JOINING USED TO FETCH PAYMENT TYPE*/
AND PPPMF.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID(+) /*JOINING USED TO FETCH BANK NAME AND ACCOUNT NO*/
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID /*JOINING USED TO FETCH ASSIGNMENT STATUS*/
AND PAAF.PRIMARY_FLAG = 'Y'
--AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PAAF.PERIOD_OF_SERVICE_ID = PPOS.PERIOD_OF_SERVICE_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND V_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY')) )
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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(PPPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(PPPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN NVL(POPMF.EFFECTIVE_START_DATE, to_date('01-Jan-1900','DD-Mon-YYYY')) AND NVL(POPMF.EFFECTIVE_END_DATE, to_date('31-Dec-4712','DD-Mon-YYYY'))))
OR (
last_day(V_DATE) between PAPF.EFFECTIVE_START_DATE and PAPF.EFFECTIVE_END_DATE
And last_day(V_DATE) between PAAF.EFFECTIVE_START_DATE and PAAF.EFFECTIVE_END_DATE
and hpd.start_date between V_DATE and last_day(V_DATE)
--AND nvl(PPOS.ACTUAL_TERMINATION_DATE,to_date('31-dec-4712')) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
--AND nvl(PPOS.ACTUAL_TERMINATION_DATE,to_date('31-dec-4712')) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
--AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN to_date('01-may-2011') AND LAST_DAY(to_date('01-may-2011'))
) ));
---------
---||CURSER USED TO FETCH LOCATION DETAILS||
----------
CURSOR CUR_LOCATION_DET(L_PERSON_ID NUMBER) IS
SELECT HRL.LOCATION_CODE LOCATION_NAME
,HRL1.LOCATION_CODE PT_LOCATION
,HRL2.LOCATION_CODE TDS_LOCATION
FROM PER_ALL_PEOPLE_F PAPF
,PER_ALL_ASSIGNMENTS_F PAAF
,HR_LOCATIONS HRL
,HR_ALL_ORGANIZATION_UNITS HAOU
,HR_ALL_ORGANIZATION_UNITS HAOU1
,PER_PERIODS_OF_SERVICE PPOS
,HR_LOCATIONS HRL1
,HR_LOCATIONS HRL2
,HR_SOFT_CODING_KEYFLEX HSCK
WHERE PAPF.PERSON_ID =L_PERSON_ID
AND PAPF.PERSON_ID =PAAF.PERSON_ID
AND PAPF.PERSON_ID =PPOS.PERSON_ID
AND PAAF.LOCATION_ID =HRL.LOCATION_ID
AND PAAF.SOFT_CODING_KEYFLEX_ID =HSCK.SOFT_CODING_KEYFLEX_ID
AND HSCK.SEGMENT3 =HAOU.ORGANIZATION_ID(+)
AND HAOU.LOCATION_ID =HRL1.LOCATION_ID(+)
AND HSCK.SEGMENT1 =HAOU1.ORGANIZATION_ID
AND HAOU1.LOCATION_ID =HRL2.LOCATION_ID
AND (( V_DATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND V_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.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
AND PPOS.ACTUAL_TERMINATION_DATE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.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
AND PAPF.ORIGINAL_DATE_OF_HIRE BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE));
--------------
---BEGIN START
--------------
BEGIN
-- FND_FILE.PUT_LINE(FND_FILE.log,'01');
--------
--Converting INPUT MONTH AND YEAR INTO A DATE FORMAT
--------
IF UPPER(P_MONTH) IN('JAN', 'FEB', 'MAR') THEN
V_DATE := FND_CONC_DATE.STRING_TO_DATE('01' || UPPER(P_MONTH) || SUBSTR(P_FIN_YEAR,5));
L_YEAR := SUBSTR(P_FIN_YEAR,6);
ELSE
V_DATE := FND_CONC_DATE.STRING_TO_DATE('01' || UPPER(P_MONTH) || SUBSTR(P_FIN_YEAR,1,4));
L_YEAR := SUBSTR(P_FIN_YEAR,1,4);
END IF;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<?xml version="1.0" encoding="UTF-8" ?>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PR_EMPLOYEE_DETAILS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MONTH>'||P_MONTH||'</MONTH>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FIN_YEAR>'||L_YEAR||'</FIN_YEAR>');
--------
---- || FETCHING STANDARD DAYS IN A PARTICULAR MONTH ||
--------
SELECT DISTINCT (END_DATE-START_DATE)+1 INTO L_STD_DAYS FROM PER_TIME_PERIODS WHERE START_DATE=V_DATE;
FOR REC_ASSIGNMENT_ACTION_ID IN CUR_ASSIGNMENT_ACTION_ID
LOOP
-- FND_FILE.PUT_LINE(FND_FILE.log,'02:'||REC_ASSIGNMENT_ACTION_ID.PERSON_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:Going to call CUR_EMP_PERSONAL_DETAILS~~~~~~~~~~~~~~~~~');
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID : '||REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID : '||REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:REC_ASSIGNMENT_ACTION_ID.PERSON_ID : '||REC_ASSIGNMENT_ACTION_ID.PERSON_ID);
-- FND_FILE.PUT_LINE(FND_FILE.log,'**2:V_DATE : '||to_char(V_DATE,'DD-Mon-YYYY'));
-------------------
BEGIN
FOR REC_EMP_PERSONAL_DETAILS IN CUR_EMP_PERSONAL_DETAILS(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID
,REC_ASSIGNMENT_ACTION_ID.PERSON_ID
,V_DATE)
LOOP
-- DBMS_OUTPUT.PUT_LINE('REC_EMP_PERSONAL_DETAILS.EMPNAME : '||REC_EMP_PERSONAL_DETAILS.EMPNAME);
-- FND_FILE.PUT_LINE(FND_FILE.log,'~~~~~~~~~~~~~:BEFORE FATHERS NAME');
--------
-- FETCHING FATHER'S NAME BY XXARJ_PR_FHNAME_FNC()
--------
BEGIN
V_FH_NAME :=XXARJ_PR_FHNAME_FNC(REC_ASSIGNMENT_ACTION_ID.PERSON_ID,V_DATE);
-- FND_FILE.PUT_LINE(FND_FILE.log,'03:'||V_FH_NAME);
EXCEPTION WHEN OTHERS THEN
V_FH_NAME:='';
FND_FILE.PUT_LINE(FND_FILE.LOG,'MORE THAN ONE FATHER NAME :'||SQLERRM);
END;
--------
---INTIALIZING VARIABLE
-------
L_BASIC_SALARY_RATE :=0;
L_HRA_RATE :=0;
L_CON_ALL_R :=0;
L_CON_REIM_R :=0;
L_SPECIAL_ALL_R :=0;
L_PETROL_R :=0;
L_MED_ALL_R :=0;
L_DRIVER_R :=0;
L_EDUCATION_ALL_R :=0;
L_SUBSIDY_ALL_R :=0;
L_TELE_R :=0;
L_SITE_ALL_R :=0;
L_HARDSHIP_ALL_R :=0;
L_HARDSHIP_OFF_SHORE_R :=0;
L_SUPER_ANNUATION_R :=0;
L_ONSITE_DAYS :=0;
L_OVERTIME_HOURS :=0;
L_BASIC :=0;
L_BASIC_ARR :=0;
L_HRA :=0;
L_HRA_ARR :=0;
L_CON_ALL :=0;
L_CON_ALLW_ARR :=0;
L_CON_REIM_PAYABLE :=0;
L_CON_REIM_PAYABLE_ARR :=0;
L_SPCIAL_ALL :=0;
L_SPCIAL_ALL_ARR :=0;
L_ADVANCE_AGAINST_PETROL :=0;
L_ADVANCE_AGAINST_PETROL_ARR :=0;
L_MEDICAL_ALLOW :=0;
L_MEDICAL_ALLOW_ARR :=0;
L_ADVANCE_AGAINST_DRIVER :=0;
L_ADVANCE_AGAINST_DRIVER_ARR :=0;
L_EDUCATION_ALL :=0;
L_EDUCATION_ALL_ARR :=0;
L_GROSS_UP_ALL :=0;
L_SUBSIDY_ALLW :=0;
L_SUBSIDY_ALLW_ARR :=0;
L_SUPER_ANNUATION :=0;
L_SUPER_ANNUATION_ARR :=0;
L_TELE :=0;
L_TELE_ARR :=0;
L_SITE_ALL_AMOUNT :=0;
L_SITE_ALL_AMOUNT_ARR :=0;
L_HARDSHIP_AMOUNT_PAYABLE :=0;
L_HARDSHIP_AMOUNT_PAYABLE_ARR :=0;
L_HARDSHIP_OFF_SHORE_PAYA :=0;
L_OT_AMOUNT :=0;
L_OFFSHORE_ALL :=0;
L_LTA :=0;
L_RETANTION_BONUS :=0;
L_OTHER_EARNINGS_NON_TAXABLE :=0;
L_TELEPHONE_ADVANCE :=0;
L_LEAVE_ENCASHMENT :=0;
L_BONUS :=0;
L_GROSS_EARN :=0;
L_PF :=0;
L_ESI :=0;
L_IT :=0;
L_LWF :=0;
L_BUS_BEDN :=0;
L_CANTEEN_DEDN :=0;
L_CITI_BANK_LOAN_DEDUCTION :=0;
L_HDFC_DEDUCTION :=0;
L_CANARA_BANK_LOAN_DEDUCTION :=0;
L_INDUSIND_BANK_DEDUCTION :=0;
L_PF_LOAN :=0;
L_LOAN_AMOUNT :=0;
L_PERSONAL_LOAN :=0;
L_IMPREST_AMOUNT :=0;
L_INSURANCE :=0;
L_OTHER_DEDUCTION :=0;
L_NEG_SAL :=0;
L_NEG_SAL_REC :=0;
L_GROSS_DEDUCTION :=0;
L_NET_SALARY :=0;
L_GROSS_UP_ALL_R :=0;
L_HOUSING_LOAN :=0;
L_NOTICE_PAY_DEDUCTION :=0;
L_PT :=0;
L_HARDSHIP_OFF_SHORE_PAYA_ARR :=0;
L_RELOCATION_AMOUNT :=0;
L_JOINING_BONUS :=0;
L_NOTICE_PAY_PAYMENT :=0;
L_Allowance1 :=0;
L_Ph_Subsidy :=0;
L_Ph_Subsidy_ARR :=0;
L_Notice_pay :=0;
L_Notice_Deduc :=0;
L_LOAN_PERQUISITE :=0;
L_Thai_Allw :=0;
L_R_Thai_Allw :=0;
L_LOAN_AMT_RECO :=0;
L_PHONE_SUBSIDY_R :=0;
----------
--- ||CHECKING IF THE PERSON JOIN IN BETWEEN ANY MONTH|| /
----------
IF REC_EMP_PERSONAL_DETAILS.DOJ BETWEEN V_DATE AND last_day(V_DATE) THEN
V_process_date := REC_EMP_PERSONAL_DETAILS.DOJ;
ELSE
V_process_date := V_DATE;
END IF;
----------
---- || FETCHING RATES OF ELEMENTS BY XXARJ_PR_RATE_CALCULATION_FNC()||
----------
L_BASIC_SALARY_RATE := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Basic Salary','Standard Value', V_process_date);
L_HRA_RATE := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'House Rent Allowance','Standard Value', V_process_date);
L_CON_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Conveyance Allowance','Allowance Amount', V_process_date);
L_CON_REIM_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Conveyance Reimbursement','Allowance Amount', V_process_date);
L_SPECIAL_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Special Allowance','Standard Value', V_process_date);
L_PETROL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Petrol Reimbursement','Allowance Amount', V_process_date);
L_MED_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Medical Reimbursement','Allowance Amount', V_process_date);
L_DRIVER_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Driver Reimbursement','Allowance Amount', V_process_date);
L_EDUCATION_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Education Allowance','Allowance Amount', V_process_date);
L_GROSS_UP_ALL_R := XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Pay Value','Gross UP Allowance', V_process_date);
L_SUBSIDY_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Subsidy Allowance','Allowance Amount', V_process_date);
L_SUPER_ANNUATION_R :=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Superannuation','Cost to Employer', V_process_date);
L_TELE_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Telephone Reimbursement','Allowance Amount', V_process_date);
L_HARDSHIP_ALL_R := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Hardship Allowance','Allowance Amount', V_process_date);
L_HARDSHIP_OFF_SHORE_R:=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Hardship Offshore Allowance','Allowance Amount', V_process_date);
L_PHONE_SUBSIDY_R :=XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Phone Subsidy','Allowance Amount', V_process_date);
---------
--- || CALCULATING WORKING DAYS,LOP DAYS AND ONSITE DAYS ||
--------
L_WRK_DAYS := XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Days','Payable Days', V_process_date);
L_LOP_DAYS := L_STD_DAYS - NVL(L_WRK_DAYS,0);
-- L_ONSITE_DAYS := XXARJ_PR_RATE_CALCULATION_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Site Allowance','Site Days', V_process_date);
L_ONSITE_DAYS := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Site Days','Site Allowance', V_process_date);
----------------
----- || ACTUAL VALUES AND ARREARS OF ELEMENTS FROM RUN RESULT BY XXARJ_PR_ELEMENT_VALUE_FNC()||
----------------
-- FND_FILE.PUT_LINE(FND_FILE.log,'06');
L_OVERTIME_HOURS :=XXARJ_PR_ELEMENT_VALUE_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Hours','Overtime', V_process_date);
L_BASIC :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Basic Salary', V_process_date);
L_BASIC_ARR :=nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Basic Salary', V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrear Basic Salary', V_process_date),0);
L_HRA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','House Rent Allowance', V_process_date);
L_HRA_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro House Rent Allowance', V_process_date);
L_CON_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Conveyance Allowance', V_process_date);
L_CON_ALLW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Conveyance Allowance', V_process_date);
L_CON_REIM_PAYABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Conveyance Reimbursement', V_process_date);
L_CON_REIM_PAYABLE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Conveyance Reimbursement', V_process_date);
L_SPCIAL_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Special Allowance', V_process_date);
L_SPCIAL_ALL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Special Allowance', V_process_date);
L_ADVANCE_AGAINST_PETROL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Petrol Reimbursement', V_process_date);
L_ADVANCE_AGAINST_PETROL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Petrol Reimbursement', V_process_date);
L_MEDICAL_ALLOW :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Medical Reimbursement', V_process_date);
L_MEDICAL_ALLOW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Medical Reimbursement', V_process_date);
L_ADVANCE_AGAINST_DRIVER :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Driver Reimbursement', V_process_date);
L_ADVANCE_AGAINST_DRIVER_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Driver Reimbursement', V_process_date);
L_EDUCATION_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Education Allowance', V_process_date);
L_EDUCATION_ALL_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Education Allowance', V_process_date);
L_GROSS_UP_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Gross UP Allowance', V_process_date);
L_SUBSIDY_ALLW :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Subsidy Allowance', V_process_date);
L_SUBSIDY_ALLW_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Subsidy Allowance', V_process_date);
L_SUPER_ANNUATION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Superannuation', V_process_date);
L_SUPER_ANNUATION_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Superannuation', V_process_date);
L_TELE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Telephone Reimbursement', V_process_date);
L_TELE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Telephone Reimbursement', V_process_date);
L_SITE_ALL_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Site Allowance', V_process_date);
-----
IF L_SITE_ALL_AMOUNT >= 0 THEN
L_SITE_ALL_R :=L_BASIC_SALARY_RATE/2;
Else
L_SITE_ALL_R := 0;
L_SITE_ALL_AMOUNT :=0;
END IF;
-------
L_SITE_ALL_AMOUNT_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Site Allowance', V_process_date);
L_HARDSHIP_AMOUNT_PAYABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Hardship Allowance', V_process_date);
L_HARDSHIP_AMOUNT_PAYABLE_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro HardShip Allowance', V_process_date);
L_HARDSHIP_OFF_SHORE_PAYA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Hardship Offshore Allowance', V_process_date);
L_HARDSHIP_OFF_SHORE_PAYA_ARR :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro HardShip Offshore Allowance', V_process_date);
--
--NEW COLUMNS(18-OCT-2010)
--
L_RELOCATION_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Relocation Allowance', V_process_date);
L_JOINING_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Joining Bonus', V_process_date);
--L_NOTICE_PAY_PAYMENT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay', V_process_date);
L_NOTICE_PAY_PAYMENT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Payment', V_process_date),0)+ NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay', V_process_date),0);
L_OT_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Overtime', V_process_date);
L_OFFSHORE_ALL :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Offshore Allowance', V_process_date);
L_LTA :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','LTA Payment', V_process_date);
L_RETANTION_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retention Bonus', V_process_date);
L_OTHER_EARNINGS_NON_TAXABLE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Non Taxable Allowance', V_process_date);
L_TELEPHONE_ADVANCE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Telephone NR Reimbursement', V_process_date);
L_LEAVE_ENCASHMENT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Leave Encashment', V_process_date);
L_BONUS :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Bonus Amount','Bonus', V_process_date);
L_Allowance1 := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Allowance1', V_process_date);
L_NEG_SAL := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Negative Salary Payment',V_process_date);
L_Ph_Subsidy := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Phone Subsidy', V_process_date); ---Updated By Arjjon
L_Ph_Subsidy_ARR := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Phone Subsidy',V_process_date);
L_Thai_Allw := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Thailand Allowance', V_process_date); ---Updated By Arjjon
L_R_Thai_Allw := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Thailand Allowance', V_process_date); ---Updated By Arjjon
--FND_FILE.PUT_LINE(FND_FILE.log,'*****L_Thai_Allw ++++++++++++++++++++++++++++++:'|| ' ' ||L_Thai_Allw);
L_GROSS_EARN := NVL(L_BASIC,0)
+NVL(L_BASIC_ARR,0)
+NVL(L_HRA,0)
+NVL(L_HRA_ARR,0)
+NVL(L_CON_ALL,0)
+NVL(L_CON_ALLW_ARR,0)
+NVL(L_CON_REIM_PAYABLE,0)
+NVL(L_CON_REIM_PAYABLE_ARR,0)
+NVL(L_SPCIAL_ALL,0)
+NVL(L_SPCIAL_ALL_ARR,0)
+NVL(L_ADVANCE_AGAINST_PETROL,0)
+NVL(L_ADVANCE_AGAINST_PETROL_ARR,0)
+NVL(L_MEDICAL_ALLOW,0)
+NVL(L_MEDICAL_ALLOW_ARR,0)
+NVL(L_ADVANCE_AGAINST_DRIVER,0)
+NVL(L_ADVANCE_AGAINST_DRIVER_ARR,0)
+NVL( L_EDUCATION_ALL,0)
+NVL(L_EDUCATION_ALL_ARR,0)
+NVL(L_GROSS_UP_ALL,0)
+NVL(L_SUBSIDY_ALLW,0)
+NVL(L_SUBSIDY_ALLW_ARR,0)
+NVL(L_SUPER_ANNUATION,0)
+NVL(L_SUPER_ANNUATION_ARR,0)
+NVL(L_TELE,0)
+NVL(L_TELE_ARR,0)
+NVL(L_SITE_ALL_AMOUNT,0)
+NVL(L_SITE_ALL_AMOUNT_ARR,0)
+NVL(L_HARDSHIP_AMOUNT_PAYABLE,0)
+NVL(L_HARDSHIP_AMOUNT_PAYABLE_ARR,0)
+NVL(L_HARDSHIP_OFF_SHORE_PAYA,0)
+NVL(L_HARDSHIP_OFF_SHORE_PAYA_ARR,0)
+NVL(L_RELOCATION_AMOUNT,0)
+NVL(L_JOINING_BONUS,0)
+NVL(L_NOTICE_PAY_PAYMENT,0)
+NVL(L_OT_AMOUNT,0)
+NVL(L_OFFSHORE_ALL,0)
+NVL(L_LTA,0)
+NVL(L_RETANTION_BONUS,0)
+NVL(L_OTHER_EARNINGS_NON_TAXABLE,0)
+NVL(L_TELEPHONE_ADVANCE,0)
+NVL(L_LEAVE_ENCASHMENT,0)
+NVL(L_BONUS,0)
+NVL(L_Allowance1,0)
+NVL(L_Ph_Subsidy,0)
+NVL(L_Ph_Subsidy_ARR,0)
+NVL(L_NEG_SAL,0)
+NVL(L_Thai_Allw,0)
+NVL(L_R_Thai_Allw,0); ---Updated for Phone Subsidy;
DBMS_OUTPUT.PUT_LINE( 'GROSS EARNINGS:'|| ' ' ||L_GROSS_EARN);
-----
-- || CALCULATING DIFFERENT DEDUCTION COLUMNS FROM RUN RESULT BY XXARJ_PR_ELEMENT_VALUE_ASG_FNC()||
-----
L_PF :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee Statutory PF Contribution',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Employee Statutory PF',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Employee Statutory PF',V_process_date),0);
L_ESI :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee ESI Contribution',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Employee ESI',V_process_date),0);
L_IT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Income Tax',V_process_date),0)+
NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','User Declared Income Tax',V_process_date),0)+
NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Tax on Bonus',V_process_date),0);
L_PT :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Professional Tax Deduction',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrears Professional Tax',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Retro Professional Tax',V_process_date),0);
L_LWF :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Employee LWF Contributions',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Employee Contribution','LWF Adjustment',V_process_date),0)+ nvl(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Arrear Employee LWF',V_process_date),0);
L_BUS_BEDN :=NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bus Deductions',V_process_date),0)+ NVL(XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bus Deduction',V_process_date),0);
L_CANTEEN_DEDN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Canteen deduction',V_process_date);
--------
-- ||FETCHING BANK NAME FROM RUN RESULT VALUES BY XXARJ_PR_RUN_RESULT_NAME_FNC() ||
-------
L_BANK_NAME :=XXARJ_PR_RUN_RESULT_NAME_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Bank Name','Bank Loan Recovery',V_process_date);
L_LOAN_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Bank Loan Recovery',V_process_date);
-----
-- ||PLACING LOAN AMOUNT INTO PROPER BANK LOAN DEDUCTION COLUMN ||
-----
IF L_BANK_NAME='CITI' THEN
L_CITI_BANK_LOAN_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='HDFC' THEN
L_HDFC_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='CANARA' THEN
L_CANARA_BANK_LOAN_DEDUCTION :=L_LOAN_AMOUNT;
ELSIF L_BANK_NAME='INDUSIND' THEN
L_INDUSIND_BANK_DEDUCTION :=L_LOAN_AMOUNT;
END IF;
L_PF_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','PF Loan Deduction',V_process_date);
L_LOAN_TYPE :=XXARJ_PR_RUN_RESULT_NAME_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Loan Type','Loan Amount Recovery',V_process_date);
IF L_LOAN_TYPE='Housing Loan' THEN
L_HOUSING_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Amount Recovery',V_process_date);
ELSIF L_LOAN_TYPE='Personal Loan' THEN
L_PERSONAL_LOAN :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Amount Recovery',V_process_date);
END IF;
L_IMPREST_AMOUNT :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Imprest Deduction',V_process_date);
L_INSURANCE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Insurance Deduction',V_process_date);
L_OTHER_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Other Deductions',V_process_date) ;
L_NEG_SAL_REC := XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Negative Salary Recovery',V_process_date);
-- L_NOTICE_PAY_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Pay',V_process_date);
L_NOTICE_PAY_DEDUCTION :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Notice Period Deduction',V_process_date);
L_LOAN_PERQUISITE :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan Perquisites',V_process_date);
L_LOAN_AMT_RECO :=XXARJ_PR_ELEMENT_VALUE_ASG_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,'Pay Value','Loan',V_process_date);
L_GROSS_DEDUCTION := NVL(L_PF,0)
+NVL(L_ESI,0)
+NVL(L_IT,0)
+NVL(L_PT,0)
+NVL(L_LWF,0)
+NVL(L_BUS_BEDN,0)
+NVL(L_CANTEEN_DEDN,0)
+NVL(L_LOAN_AMOUNT,0)
+NVL(L_PF_LOAN,0)
+NVL(L_PERSONAL_LOAN,0)
+NVL(L_HOUSING_LOAN,0)
+NVL(L_IMPREST_AMOUNT,0)
+NVL(L_INSURANCE,0)
+NVL(L_OTHER_DEDUCTION,0)
+NVL(L_NOTICE_PAY_DEDUCTION,0)
+NVL(L_LOAN_PERQUISITE,0)
+NVL(L_LOAN_AMT_RECO,0)
+NVL(L_NEG_SAL_REC,0);
-- FND_FILE.PUT_LINE(FND_FILE.log,'07');
L_NET_SALARY :=XXARJ_PR_NET_SAL_FNC(REC_ASSIGNMENT_ACTION_ID.ASG_ACT_ID,'Net Pay','_ASG_MTD');
DBMS_OUTPUT.PUT_LINE( 'L_NET_SALARY'|| ' ' ||L_NET_SALARY);
L_C_REMARKS :=XXARJ_REMARKS_NON_REC_FNC(REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID,V_process_date);
-- FND_FILE.PUT_LINE(FND_FILE.log,'*****L_REMARKS :'|| ' ' ||L_C_REMARKS);
-- FND_FILE.PUT_LINE(FND_FILE.log,'08');
-------
-- || GENERATING XML TAGS FOR PERSONAL INFORMATION COLUMNS||
-------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RECORD_SET>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPCODE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMPCODE || ']]></EMPCODE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMPNAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMPNAME || ']]></EMPNAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ORGANIZATION_NAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.ORGANIZATION_NAME || ']]></ORGANIZATION_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOB><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOB,'DD-MON-YYYY') || ']]></DOB>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOJ><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOJ,'DD-MON-YYYY') || ']]></DOJ>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DOL1><![CDATA[' ||TO_CHAR(REC_EMP_PERSONAL_DETAILS.DOL1 ,'DD-MON-YYYY') || ']]></DOL1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GENDER>' ||REC_EMP_PERSONAL_DETAILS.GENDER || '</GENDER>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DESIGNATION><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.DESIGNATION || ']]></DESIGNATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DEPARTMENT><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.DEPARTMENT || ']]></DEPARTMENT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GRADE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.GRADE || ']]></GRADE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DIVISION><![CDATA[' || REC_EMP_PERSONAL_DETAILS.DIVISION || ']]></DIVISION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PAYMODE><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PAYMODE || ']]></PAYMODE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BANK_NAME><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.BANK_NAME || ']]></BANK_NAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ACCOUNTNO>' ||REC_EMP_PERSONAL_DETAILS.ACCOUNTNO || '</ACCOUNTNO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PROCESS_STATUS>' ||REC_EMP_PERSONAL_DETAILS.PROCESS_STATUS || '</PROCESS_STATUS>');
--------
-- ||CURSOR CUR_LOCATION_DET USED TO FETCH LOCATION DETAILS||
--------
FOR REC_CUR_LOCATION_DET IN CUR_LOCATION_DET(REC_ASSIGNMENT_ACTION_ID.PERSON_ID)
LOOP
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.LOCATION_NAME || ']]></LOCATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PT_LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.PT_LOCATION || ']]></PT_LOCATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TDS_LOCATION><![CDATA[' ||REC_CUR_LOCATION_DET.TDS_LOCATION || ']]></TDS_LOCATION>');
END LOOP;
----
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EMAIL_ID><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.EMAIL_ID || ']]></EMAIL_ID>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PAN><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PAN || ']]></PAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<FHNAME><![CDATA[' ||V_FH_NAME || ']]></FHNAME>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF_NO><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.PF_NO || ']]></PF_NO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ESINO><![CDATA[' ||REC_EMP_PERSONAL_DETAILS.ESINO || ']]></ESINO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC_SALARY_RATE>' || TO_CHAR(ROUND(NVL(L_BASIC_SALARY_RATE,0),2),'FM99999999999.00') || '</BASIC_SALARY_RATE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA_RATE>' || TO_CHAR(ROUND(NVL(L_HRA_RATE,0),2),'FM99999999999.00') || '</HRA_RATE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALL_R>' || TO_CHAR(ROUND(NVL(L_CON_ALL_R,0),2),'FM99999999999.00') || '</CON_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_R>' || TO_CHAR(ROUND(NVL(L_CON_REIM_R,0),2),'FM99999999999.00') || '</CON_REIM_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPECIAL_ALL_R>' || TO_CHAR(ROUND(NVL(L_SPECIAL_ALL_R,0),2),'FM99999999999.00') || '</SPECIAL_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PETROL_R>' || TO_CHAR(ROUND(NVL(L_PETROL_R,0),2),'FM99999999999.00') || '</PETROL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MED_ALL_R>' || TO_CHAR(ROUND(NVL(L_MED_ALL_R,0),2),'FM99999999999.00') || '</MED_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<DRIVER_R>' || TO_CHAR(ROUND(NVL(L_DRIVER_R,0),2),'FM99999999999.00') || '</DRIVER_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL_R>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL_R,0),2),'FM99999999999.00') || '</EDUCATION_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_UP_ALL_R>' || TO_CHAR(ROUND(NVL(L_GROSS_UP_ALL_R,0),2),'FM99999999999.00') || '</GROSS_UP_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALL_R>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALL_R,0),2),'FM99999999999.00') || '</SUBSIDY_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION_R>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION_R,0),2),'FM99999999999.00') || '</SUPER_ANNUATION_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE_R>' || TO_CHAR(ROUND(NVL(L_TELE_R,0),2),'FM99999999999.00') || '</TELE_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_R>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_R,0),2),'FM99999999999.00') || '</SITE_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_ALL_R>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_ALL_R,0),2),'FM99999999999.00') || '</HARDSHIP_ALL_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_R>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_R,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_R>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOP_DAYS>' || NVL(L_LOP_DAYS,0) || '</LOP_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<STD_DAYS>' || NVL(L_STD_DAYS,0) || '</STD_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<WRK_DAYS>' || NVL(L_WRK_DAYS,0) || '</WRK_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ONSITE_DAYS>' || NVL(L_ONSITE_DAYS,0) || '</ONSITE_DAYS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<B_OS_ASSIGN>' || NVL(L_B_OS_ASSIGN,0) || '</B_OS_ASSIGN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OVERTIME_HOURS>' || NVL(L_OVERTIME_HOURS,0) || '</OVERTIME_HOURS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC>' || TO_CHAR(ROUND(NVL(L_BASIC,0),2),'FM99999999999.00') || '</BASIC>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BASIC_ARR>' || TO_CHAR(ROUND(NVL(L_BASIC_ARR,0),2),'FM99999999999.00') || '</BASIC_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA>' || TO_CHAR(ROUND(NVL(L_HRA,0),2),'FM99999999999.00') || '</HRA>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HRA_ARR>' || TO_CHAR(ROUND(NVL(L_HRA_ARR,0),2),'FM99999999999.00') || '</HRA_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALL>' || TO_CHAR(ROUND(NVL(L_CON_ALL,0),2),'FM99999999999.00') || '</CON_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_ALLW_ARR>' || TO_CHAR(ROUND(NVL(L_CON_ALLW_ARR,0),2),'FM99999999999.00') || '</CON_ALLW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_PAYABLE>' || TO_CHAR(ROUND(NVL(L_CON_REIM_PAYABLE,0),2),'FM99999999999.00') || '</CON_REIM_PAYABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CON_REIM_PAYABLE_ARR>' || TO_CHAR(ROUND(NVL(L_CON_REIM_PAYABLE_ARR,0),2),'FM99999999999.00') || '</CON_REIM_PAYABLE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPCIAL_ALL>' || TO_CHAR(ROUND(NVL(L_SPCIAL_ALL,0),2),'FM99999999999.00') || '</SPCIAL_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SPCIAL_ALL_ARR>' || TO_CHAR(ROUND(NVL(L_SPCIAL_ALL_ARR,0),2),'FM99999999999.00') || '</SPCIAL_ALL_ARR>');
--FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_allow>' || TO_CHAR(ROUND(NVL(L_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_allow>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Allowance1>' || TO_CHAR(ROUND(NVL(L_Allowance1,0),2),'FM99999999999.00') || '</Allowance1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_allow>' || TO_CHAR(ROUND(NVL(L_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_allow>');-- Updated for Thai Allw pay
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<Thai_r_allow>' || TO_CHAR(ROUND(NVL(L_R_Thai_Allw,0),2),'FM99999999999.00') || '</Thai_r_allow>');-- Updated for Retro Thai Allw
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy_ARR>' || TO_CHAR(ROUND(NVL(L_Ph_Subsidy_ARR,0),2),'FM99999999999.00') || '</PH_Subsidy_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy>' || TO_CHAR(ROUND(NVL(L_Ph_Subsidy,0),2),'FM99999999999.00') || '</PH_Subsidy>');-- Updated for Phone Subsidy
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PH_Subsidy_rate>' || TO_CHAR(ROUND(NVL(L_PHONE_SUBSIDY_R,0),2),'FM99999999999.00') || '</PH_Subsidy_rate>');-- Updated for Phone Subsidy
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</RECORD_SET>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RECORD_SET1>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_PETROL>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_PETROL,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_PETROL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_PETROL_ARR>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_PETROL_ARR,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_PETROL_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MEDICAL_ALLOW>' || TO_CHAR(ROUND(NVL(L_MEDICAL_ALLOW,0),2),'FM99999999999.00') || '</MEDICAL_ALLOW>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<MEDICAL_ALLOW_ARR>' || TO_CHAR(ROUND(NVL(L_MEDICAL_ALLOW_ARR,0),2),'FM99999999999.00') || '</MEDICAL_ALLOW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_DRIVER>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_DRIVER,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_DRIVER>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ADVANCE_AGAINST_DRIVER_ARR>' || TO_CHAR(ROUND(NVL(L_ADVANCE_AGAINST_DRIVER_ARR,0),2),'FM99999999999.00') || '</ADVANCE_AGAINST_DRIVER_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL,0),2),'FM99999999999.00') || '</EDUCATION_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<EDUCATION_ALL_ARR>' || TO_CHAR(ROUND(NVL(L_EDUCATION_ALL_ARR,0),2),'FM99999999999.00') || '</EDUCATION_ALL_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_UP_ALL>' || TO_CHAR(ROUND(NVL(L_GROSS_UP_ALL,0),2),'FM99999999999.00') || '</GROSS_UP_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALLW>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALLW,0),2),'FM99999999999.00') || '</SUBSIDY_ALLW>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUBSIDY_ALLW_ARR>' || TO_CHAR(ROUND(NVL(L_SUBSIDY_ALLW_ARR,0),2),'FM99999999999.00') || '</SUBSIDY_ALLW_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION,0),2),'FM99999999999.00') || '</SUPER_ANNUATION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SUPER_ANNUATION_ARR>' || TO_CHAR(ROUND(NVL(L_SUPER_ANNUATION_ARR,0),2),'FM99999999999.00') || '</SUPER_ANNUATION_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE>' || TO_CHAR(ROUND(NVL(L_TELE,0),2),'FM99999999999.00') || '</TELE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELE_ARR>' || TO_CHAR(ROUND(NVL(L_TELE_ARR,0),2),'FM99999999999.00') || '</TELE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_AMOUNT>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_AMOUNT,0),2),'FM99999999999.00') || '</SITE_ALL_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<SITE_ALL_AMOUNT_ARR>' || TO_CHAR(ROUND(NVL(L_SITE_ALL_AMOUNT_ARR,0),2),'FM99999999999.00') || '</SITE_ALL_AMOUNT_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_AMOUNT_PAYABLE>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_AMOUNT_PAYABLE,0),2),'FM99999999999.00') || '</HARDSHIP_AMOUNT_PAYABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_AMOUNT_PAYABLE_ARR>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_AMOUNT_PAYABLE_ARR,0),2),'FM99999999999.00') || '</HARDSHIP_AMOUNT_PAYABLE_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_PAYA>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_PAYA,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_PAYA>');
---
--NEW COLUMNS
---
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HARDSHIP_OFF_SHORE_PAYA_ARR>' || TO_CHAR(ROUND(NVL(L_HARDSHIP_OFF_SHORE_PAYA_ARR,0),2),'FM99999999999.00') || '</HARDSHIP_OFF_SHORE_PAYA_ARR>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RELOCATION_AMOUNT>' || TO_CHAR(ROUND(NVL(L_RELOCATION_AMOUNT,0),2),'FM99999999999.00') || '</RELOCATION_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<JOINING_BONUS>' || TO_CHAR(ROUND(NVL(L_JOINING_BONUS,0),2),'FM99999999999.00') || '</JOINING_BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NOTICE_PAY_PAYMENT>' || TO_CHAR(ROUND(NVL(L_NOTICE_PAY_PAYMENT,0),2),'FM99999999999.00') || '</NOTICE_PAY_PAYMENT>');
---
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OT_AMOUNT>' || TO_CHAR(ROUND(NVL(L_OT_AMOUNT,0),2),'FM99999999999.00') || '</OT_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OFFSHORE_ALL>' || TO_CHAR(ROUND(NVL(L_OFFSHORE_ALL,0),2),'FM99999999999.00') || '</OFFSHORE_ALL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LTA>' || TO_CHAR(ROUND(NVL(L_LTA,0),2),'FM99999999999.00') || '</LTA>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<RETANTION_BONUS>' || TO_CHAR(ROUND(NVL(L_RETANTION_BONUS,0),2),'FM99999999999.00') || '</RETANTION_BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OTHER_EARNINGS_NON_TAXABLE>' || TO_CHAR(ROUND(NVL(L_OTHER_EARNINGS_NON_TAXABLE,0),2),'FM99999999999.00') || '</OTHER_EARNINGS_NON_TAXABLE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<TELEPHONE_ADVANCE>' || TO_CHAR(ROUND(NVL(L_TELEPHONE_ADVANCE,0),2),'FM99999999999.00') || '</TELEPHONE_ADVANCE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LEAVE_ENCASHMENT>' || TO_CHAR(ROUND(NVL(L_LEAVE_ENCASHMENT,0),2),'FM99999999999.00') || '</LEAVE_ENCASHMENT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BONUS>' || TO_CHAR(ROUND(NVL(L_BONUS,0),2),'FM99999999999.00') || '</BONUS>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NEG_SAL>' || TO_CHAR(ROUND(NVL(L_NEG_SAL,0),2),'FM99999999999.00') || '</NEG_SAL>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_EARN>' || TO_CHAR(ROUND(NVL(L_GROSS_EARN,0),2),'FM99999999999.00') || '</GROSS_EARN>');
------
-- || GENERATING XML TAFS FOR DEDUCTION ELEMNTS ||
------
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF>' || TO_CHAR(ROUND(NVL(L_PF,0),2),'FM99999999999.00') || '</PF>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<ESI>' || TO_CHAR(ROUND(NVL(L_ESI,0),2),'FM99999999999.00') || '</ESI>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<IT>' || TO_CHAR(ROUND(NVL(L_IT,0),2),'FM99999999999.00') || '</IT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PT>' || TO_CHAR(ROUND(NVL(L_PT,0),2),'FM99999999999.00') || '</PT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LWF>' || TO_CHAR(ROUND(NVL(L_LWF,0),2),'FM99999999999.00') || '</LWF>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<BUS_BEDN>' || TO_CHAR(ROUND(NVL(L_BUS_BEDN,0),2),'FM99999999999.00') || '</BUS_BEDN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<CANTEEN_DEDN>' || TO_CHAR(ROUND(NVL(L_CANTEEN_DEDN,0),2),'FM99999999999.00') || '</CANTEEN_DEDN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_CITI_BANK_LOAN_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_CITI_BANK_LOAN_DEDUCTION,0),2),'FM99999999999.00') || '</L_CITI_BANK_LOAN_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_HDFC_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_HDFC_DEDUCTION,0),2),'FM99999999999.00') || '</L_HDFC_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_CANARA_BANK_LOAN_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_CANARA_BANK_LOAN_DEDUCTION,0),2),'FM99999999999.00') || '</L_CANARA_BANK_LOAN_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<L_INDUSIND_BANK_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_INDUSIND_BANK_DEDUCTION,0),2),'FM99999999999.00') || '</L_INDUSIND_BANK_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PF_LOAN>' || TO_CHAR(ROUND(NVL(L_PF_LOAN,0),2),'FM99999999999.00') || '</PF_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<PERSONAL_LOAN>' || TO_CHAR(ROUND(NVL(L_PERSONAL_LOAN,0),2),'FM99999999999.00') || '</PERSONAL_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<HOUSING_LOAN>' || TO_CHAR(ROUND(NVL(L_HOUSING_LOAN,0),2),'FM99999999999.00') || '</HOUSING_LOAN>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<IMPREST_AMOUNT>' || TO_CHAR(ROUND(NVL(L_IMPREST_AMOUNT,0),2),'FM99999999999.00') || '</IMPREST_AMOUNT>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<INSURANCE>' || TO_CHAR(ROUND(NVL(L_INSURANCE,0),2),'FM99999999999.00') || '</INSURANCE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<OTHER_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_OTHER_DEDUCTION,0),2),'FM99999999999.00') || '</OTHER_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOAN_PERQUISITE>' || TO_CHAR(ROUND(NVL(L_LOAN_PERQUISITE,0),2),'FM99999999999.00') || '</LOAN_PERQUISITE>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<LOAN_AMT_RECO>' || TO_CHAR(ROUND(NVL(L_LOAN_AMT_RECO,0),2),'FM99999999999.00') || '</LOAN_AMT_RECO>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NEG_SAL_REC>' || TO_CHAR(ROUND(NVL(L_NEG_SAL_REC,0),2),'FM99999999999.00') || '</NEG_SAL_REC>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NOTICE_PAY_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_NOTICE_PAY_DEDUCTION,0),2),'FM99999999999.00') || '</NOTICE_PAY_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<GROSS_DEDUCTION>' || TO_CHAR(ROUND(NVL(L_GROSS_DEDUCTION,0),2),'FM99999999999.00') || '</GROSS_DEDUCTION>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<NET_SALARY>' || TO_CHAR(ROUND(NVL(L_NET_SALARY,0),2),'FM99999999999.00') || '</NET_SALARY>');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'<C_REMARKS><![CDATA[' || L_C_REMARKS || ']]></C_REMARKS>');
--FND_FILE.PUT_LINE(FND_FILE.log,L_C_REMARKS);
-- FND_FILE.PUT_LINE(FND_FILE.log,'-------------------------------------------');
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</RECORD_SET1>');
END LOOP;
--DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------------------');
EXCEPTION
WHEN OTHERS THEN
NULL;
FND_FILE.PUT_LINE(FND_FILE.LOG,'**~~~~~~~***ERROR**~~~~~**** :'||SQLERRM||'FOR ASSIGNMENT ID '||REC_ASSIGNMENT_ACTION_ID.ASSIGNMENT_ID);
END;
-------------------------------------------------
END LOOP;
FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'</PR_EMPLOYEE_DETAILS>');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('DATA DOES NOT EXISTS');
FND_FILE.PUT_LINE(FND_FILE.LOG,'DATA DOES NOT EXISTS'||SQLERRM);
END XXARJ_PAY_REGISTER_REPORT_PRC;
--------
-- ||END OF XXARJ_PAY_REGISTER_REPORT_PRC (PLL INDIA)||
--------
END XXARJ_PAY_REG_REP_PKG_TEST;
No comments:
Post a Comment