PL/SQL Product Sample Code
create or replace package XXPAY_ETN_PAY_DIFF_REPORT_PKG is
-- $ETNHeader: $
--------------------------------------------------------------------------------------------------
-- Owner : EATON CORPORATION.
-- Application : Human Resources
-- Schema : APPS
-- Compile AS : APPS
-- File Name : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKS
-- Date : 28-Jun-2014
-- Author : TCS
-- Description : Package for custom program for XXPAY Payroll Difference Report
--
-- Version : $ETNHeader: $
--
-- Parameters : ERRBUF -StANDar concurrent prog parameters
-- RETCODE -StANDar concurrent prog parameters
-- p_payroll_id - Payroll for report.
-- p_payroll_period_id - pay period for report.
-- p_organization_id - organization id for report.
-- p_element_bal - elements and balance for report
-- p_threshold - threshold for report
-- p_percent - percent for report
--
-- Change History
-- ========================================================================================
-- Ver 1.0 Pushkar 28-Jun-2014 Creation
-- ========================================================================================
-- =============================================================================================
-- Procedure: XXPAY_PAY_DIFF_REPORT
-- =============================================================================================
-- This procedure generates the XML for the Payroll Difference Report
-- =============================================================================================
PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_payroll_id IN NUMBER,
p_payroll_period_id IN NUMBER,
p_organization_id IN NUMBER,
p_element_bal IN VARCHAR2,
p_threshold IN VARCHAR2,
p_percent IN VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY XXPAY_ETN_PAY_DIFF_REPORT_PKG IS
-- $ETNHeader: $
--------------------------------------------------------------------------------------------------
-- Owner : EATON CORPORATION.
-- Application : Human Resources
-- Schema : APPS
-- Compile AS : APPS
-- File Name : XXPAY_ETN_PAY_DIFF_REPORT_PKG.PKB
-- Date : 28-Jun-2014
-- Author : TCS
-- Description : Package for custom program for XXPAY Payroll Difference Report
--
-- Version : $ETNHeader: $
--
-- Parameters : ERRBUF -StANDar concurrent prog parameters
-- RETCODE -StANDar concurrent prog parameters
-- p_payroll_id - Payroll for report.
-- p_payroll_period_id - pay period for report.
-- p_organization_id - organization id for report.
-- p_element_bal - elements and balance for report
-- p_threshold - threshold for report
-- p_percent - percent for report
--
-- Change History
-- ========================================================================================
-- Ver 1.0 Pushkar 28-Jun-2014 Creation
-- Ver 2.0 Pushkar 13-Aug-2014 Change for termination date
-- and '&' character to'&'
-- ========================================================================================
PROCEDURE XXPAY_PAY_DIFF_REPORT(errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_payroll_id IN NUMBER,
p_payroll_period_id IN NUMBER,
p_organization_id IN NUMBER,
p_element_bal IN VARCHAR2,
p_threshold IN VARCHAR2,
p_percent IN VARCHAR2) IS
CURSOR EMP_PAY_DIFF_CUR(v_payroll_id IN NUMBER,
v_prev_pay_period_id IN NUMBER,
v_organization_id IN NUMBER) IS
SELECT *
FROM (SELECT papf1.employee_number Employee_Number,
papf1.Full_name Full_Name,
papf1.PER_INFORMATION14 Employee_Name,
REPLACE(apps.XXPAY_ETN_RPT_UTIL.get_activity_center(paa.assignment_id,
ptp.end_date),
'&',
'&') Activity_centre,
REPLACE((SELECT name
FROM apps.hr_all_organization_units haou
WHERE haou.organization_id =
paaf.organization_id),
'&',
'&') Organization,
papf1.national_identifier National_Identifier,
REPLACE(PG.name, '&', '&') GRADE_NAME,
REPLACE((SELECT location_code
FROM apps.hr_locations_all
WHERE location_id = paaf.location_id),
'&',
'&') Location,
to_char(ppos.date_start, 'DD-MON-YYYY') NEW_HIRE_DATE,
to_char(ppos.actual_termination_date, 'DD-MON-YYYY') TERMINATION_DATE,
ppa.payroll_action_id,
ptp.period_name,
papf.payroll_name,
ptp.time_period_id,
paa.assignment_action_id,
papf1.business_group_id,
ptp.end_date,
paaf.assignment_id,
ptp.payroll_id,
to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) TOT_PAY_BAL_CURRENT,
to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.PREV_PAY_DATE)) TOT_PAY_BAL_PREVIOUS,
(to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) GAP,
ABS(round(((to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.CURR_PAY_DATE)) -
to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.PREV_PAY_DATE))) /
decode(to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.CURR_PAY_DATE)),
0,
1,
to_number(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(paaf.assignment_id,
paaf.business_group_id,
'Total Payments',
'_ASG_PTD',
PAYROLL_PERIOD_DATES.CURR_PAY_DATE)))),
2)) GAP_PERCENT,
payroll_period_dates.curr_period_name CURRENT_PAYROLL,
payroll_period_dates.prev_period_name PREVIOUS_PAYROLL,
payroll_period_dates.curr_time_period_id CURRENT_PAYPERIOD_ID,
payroll_period_dates.CURR_PAY_DATE CURRENT_PAYPERIOD_END_DATE,
payroll_period_dates.prev_time_period_id PREVIOUS_PAYPERIOD_ID,
payroll_period_dates.PREV_PAY_DATE PREVIOUS_PAYPERIOD_END_DATE
FROM apps.pay_all_payrolls_f papf,
apps.pay_payroll_actions ppa,
apps.pay_assignment_actions paa,
apps.per_time_periods ptp,
apps.per_people_f papf1,
apps.per_assignments_f paaf,
apps.per_periods_of_service ppos,
apps.per_grades pg,
(SELECT ptp2.end_date PREV_PAY_DATE,
ptp1.end_date CURR_PAY_DATE,
ptp2.period_name PREV_PERIOD_NAME,
PTP1.PERIOD_NAME curr_period_name,
ptp2.time_period_id PREV_TIME_PERIOD_ID,
ptp1.time_period_id CURR_TIME_PERIOD_ID,
ptp1.payroll_id
FROM per_time_periods ptp1, per_time_periods ptp2
WHERE ptp1.time_period_id = P_Payroll_period_id
AND ptp2.time_period_id = V_PREV_PAY_PERIOD_ID
AND ptp2.payroll_id = ptp1.payroll_id) PAYROLL_PERIOD_DATES
WHERE papf.payroll_id = (v_payroll_id)
AND payroll_period_dates.payroll_id = ptp.payroll_id
AND ptp.time_period_id IN
((p_payroll_period_id), v_prev_pay_period_id)
AND paaf.assignment_id = paa.assignment_id
AND papf.payroll_id = ppa.payroll_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.time_period_id = ptp.time_period_id
AND paaf.period_of_service_id = ppos.period_of_service_id
AND paa.assignment_id <> -1
AND paaf.assignment_type = 'E'
AND ppa.effective_date BETWEEN ptp.start_date AND
ptp.end_date
AND EXISTS
(SELECT 'X'
FROM per_all_assignments_f paaf1,
pay_assignment_actions paa2,
pay_payroll_actions PPA2,
Per_time_periods ptp2
WHERE paaf1.person_id = paaf.person_id
AND paaf1.assignment_id = paa2.assignment_id
AND ppa2.payroll_action_id = paa2.payroll_action_id
AND ppa2.time_period_id = ptp2.time_period_id
AND ppa2.action_status = 'C'
AND ppa2.action_type IN ('R', 'Q', 'B', 'V', 'I')
AND ptp2.time_period_id IN
((P_Payroll_period_id), v_prev_pay_period_id)
HAVING COUNT(DISTINCT(ptp2.period_name)) = 2)
AND paaf.grade_id = pg.grade_id(+)
AND papf1.person_id = paaf.person_id
AND trunc(ptp.end_date) between papf1.effective_start_date AND
papf1.effective_end_date
AND trunc(ptp.end_date) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND ppos.person_id = paaf.person_id
AND trunc(nvl(ppos.actual_termination_date, ptp.end_date)) between
paaf.effective_start_date AND paaf.effective_end_date
AND (Paaf.organization_id IN (v_organization_id) OR
LEAST(v_organization_id) IS NULL)
AND primary_flag = 'Y'
AND ppa.action_status = 'C'
AND ppa.action_type IN ('R', 'Q', 'B', 'V', 'I'))
order by ACTIVITY_CENTRE, EMPLOYEE_NUMBER, TIME_PERIOD_ID;
CURSOR Elements_balance_VALUE_CUR(V_Payroll_NAME IN VARCHAR,
V_time_period_id IN NUMBER,
V_assignment_id IN NUMBER,
V_business_group_id IN NUMBER,
V_END_DATE IN DATE) IS
SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
substr(flv.meaning,
instr(flv.meaning, '|') + 1,
length(flv.meaning)) UNIT_VALUE_NAME,
flv.description ELEMENT_UNIT,
APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_Payroll_NAME,
V_time_period_id,
V_assignment_id) UNIT_RESULT,
flv.lookup_code ELE_BAL_CODE
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
AND flv.attribute1 = 'E'
AND flv.enabled_flag = 'Y'
AND language = 'US'
AND (flv.meaning IN (P_element_bal) or
LEAST(P_element_bal) IS NULL)
AND flv.tag = 'CN'
UNION
SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
substr(flv.meaning,
instr(flv.meaning, '|') + 1,
length(flv.meaning)) UNIT_VALUE_NAME,
flv.description ELEMENT_UNIT,
to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_END_DATE)) UNIT_RESULT,
flv.lookup_code ELE_BAL_CODE
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
AND flv.attribute1 = 'B'
AND flv.enabled_flag = 'Y'
AND language = 'US'
AND (flv.meaning IN (P_element_bal) or
LEAST(P_element_bal) IS NULL)
AND flv.tag = 'CN'
UNION
SELECT 'TOTAL_PAYMENT' UNIT_NAME,
'ASG_RUN' UNIT_VALUE_NAME,
'Total Payment' ELEMENT_UNIT,
to_char(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
'Total Payments',
'_ASG_PTD',
V_END_DATE)) UNIT_RESULT,
'00001' ELE_BAL_CODE
FROM DUAL
UNION
SELECT 'Percentage' UNIT_NAME,
'Percentage' UNIT_VALUE_NAME,
'Percentage' ELEMENT_UNIT,
NULL UNIT_RESULT,
'000015' ELE_BAL_CODE
FROM DUAL
ORDER BY ELE_BAL_CODE;
CURSOR Elements_balance_VALUE_DIFF(V_Payroll_NAME IN VARCHAR,
V_CURR_time_period_id IN NUMBER,
V_PREV_time_period_id IN NUMBER,
V_assignment_id IN NUMBER,
V_business_group_id IN NUMBER,
V_CURR_END_DATE IN DATE,
V_PREV_END_DATE IN DATE,
V_PERCENTAGE IN NUMBER) IS
SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
substr(flv.meaning,
instr(flv.meaning, '|') + 1,
length(flv.meaning)) UNIT_VALUE_NAME,
flv.description ELEMENT_UNIT,
TO_CHAR(TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_Payroll_NAME,
V_CURR_time_period_id,
V_assignment_id)) -
TO_NUMBER(APPS.XXPAY_ETN_RPT_UTIL.GET_RUN_RESULT(substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_Payroll_NAME,
V_PREV_time_period_id,
V_assignment_id))) UNIT_RESULT,
flv.lookup_code ELE_BAL_CODE
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
AND flv.attribute1 = 'E'
AND flv.enabled_flag = 'Y'
AND language = 'US'
AND (flv.meaning IN (P_element_bal) or
LEAST(P_element_bal) IS NULL)
AND flv.tag = 'CN'
UNION
SELECT substr(flv.meaning, 0, instr(flv.meaning, '|') - 1) UNIT_NAME,
substr(flv.meaning,
instr(flv.meaning, '|') + 1,
length(flv.meaning)) UNIT_VALUE_NAME,
flv.description ELEMENT_UNIT,
to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_CURR_END_DATE)) -
(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
substr(flv.meaning,
0,
instr(flv.meaning,
'|') - 1),
substr(flv.meaning,
instr(flv.meaning,
'|') + 1,
length(flv.meaning)),
V_PREV_END_DATE))) UNIT_RESULT,
flv.lookup_code ELE_BAL_CODE
FROM apps.fnd_lookup_values flv
WHERE flv.lookup_type = 'XXPAY_CN_ELEMENTS_BALANCE'
AND flv.attribute1 = 'B'
AND flv.enabled_flag = 'Y'
AND language = 'US'
AND (flv.meaning IN (P_element_bal) or
LEAST(P_element_bal) IS NULL)
AND flv.tag = 'CN'
UNION
SELECT 'TOTAL_PAYMENT' UNIT_NAME,
'ASG_RUN' UNIT_VALUE_NAME,
'Total Payment' ELEMENT_UNIT,
to_char((APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
'Total Payments',
'_ASG_PTD',
V_CURR_END_DATE)) -
(APPS.XXPAY_ETN_RPT_UTIL.GET_BALANCE(V_assignment_id,
V_business_group_id,
'Total Payments',
'_ASG_PTD',
V_PREV_END_DATE))) UNIT_RESULT,
'00001' ELE_BAL_CODE
FROM DUAL
UNION
SELECT 'Percentage' UNIT_NAME,
'Percentage' UNIT_VALUE_NAME,
'Percentage' ELEMENT_UNIT,
TO_CHAR(V_PERCENTAGE) UNIT_RESULT,
'000015' ELE_BAL_CODE
FROM DUAL
ORDER BY ELE_BAL_CODE;
V_PREV_PAY_PERIOD_ID NUMBER;
V_payroll_name VARCHAR2(100);
V_TIME_PERIOD VARCHAR2(100);
V_organization_name VARCHAR2(100);
V_period_name VARCHAR2(100);
e_error_EXCEPTION EXCEPTION;
v_error_msg VARCHAR2(1000);
BEGIN
BEGIN
SELECT Payroll_name
INTO V_payroll_name
FROM pay_all_payrolls_f
WHERE payroll_id = P_Payroll_id;
SELECT ptp2.time_period_id
INTO V_PREV_PAY_PERIOD_ID
FROM per_time_periods ptp1, per_time_periods ptp2
WHERE ptp2.end_date = ptp1.start_date - 1
AND ptp2.payroll_id = ptp1.payroll_id
AND ptp1.time_period_id = P_Payroll_period_id;
SELECT Period_name
INTO V_period_name
FROM per_time_periods
WHERE time_period_id = P_Payroll_period_id;
EXCEPTION
WHEN OTHERS THEN
v_error_msg := 'Error In fetching parameters' || '-' || SQLERRM;
RAISE e_error_EXCEPTION;
END;
BEGIN
SELECT name
INTO V_organization_name
FROM hr_all_organization_units
WHERE organization_id = P_organization_id;
EXCEPTION
WHEN OTHERS THEN
V_organization_name := NULL;
End;
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
'' || P_Payroll_id || ' ');
fnd_file.put_line(fnd_file.output,
'' || P_Payroll_period_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' || V_organization_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || 'NULL' ||
' ');
fnd_file.put_line(fnd_file.output,
'' || P_THRESHOLD || ' ');
fnd_file.put_line(fnd_file.output,
'' || P_PERCENT || ' ');
For I_EMPLOYEE_REC IN EMP_PAY_DIFF_CUR(P_Payroll_id,
V_PREV_PAY_PERIOD_ID,
P_organization_id) loop
IF (I_EMPLOYEE_REC.GAP >= P_threshold or
I_EMPLOYEE_REC.GAP_PERCENT >= P_PERCENT) THEN
IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
I_EMPLOYEE_REC.Current_Payperiod_Id THEN
V_TIME_PERIOD := '2.CURRENT';
ELSIF I_EMPLOYEE_REC.TIME_PERIOD_ID =
I_EMPLOYEE_REC.Previous_payperiod_id THEN
V_time_period := '1.PREVIOUS';
END IF;
FOR I_ELE_BAL_REC IN Elements_balance_VALUE_CUR(I_EMPLOYEE_REC.payroll_name,
I_EMPLOYEE_REC.TIME_PERIOD_ID,
I_EMPLOYEE_REC.Assignment_Id,
I_EMPLOYEE_REC.Business_Group_Id,
I_EMPLOYEE_REC.END_DATE) loop
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Employee_Number ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.Employee_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.activity_centre ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.ORGANIZATION ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.national_identifier ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.grade_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.location ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.NEW_HIRE_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.TERMINATION_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Payroll_action_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.Payroll_Name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.Period_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.time_period_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Assignment_action_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.business_group_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.END_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.ASSIGNMENT_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_ELE_BAL_REC.UNIT_Name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_ELE_BAL_REC.UNIT_Value_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_ELE_BAL_REC.ELEMENT_UNIT ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_ELE_BAL_REC.UNIT_RESULT ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_ELE_BAL_REC.ELE_BAL_CODE ||
' ');
fnd_file.put_line(fnd_file.output,
'' || V_TIME_PERIOD || '-' ||
I_EMPLOYEE_REC.PERIOD_NAME || ' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.payroll_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Tot_Pay_Bal_Current ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Tot_Pay_Bal_Previous ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.GAP || ' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.GAP_PERCENT ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYROLL ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYPERIOD_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYROLL ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
' ');
fnd_file.put_line(fnd_file.output, ' ');
END LOOP;
IF I_EMPLOYEE_REC.TIME_PERIOD_ID =
I_EMPLOYEE_REC.Current_Payperiod_Id THEN
BEGIN
FOR I_EMP_DIFF IN Elements_balance_VALUE_DIFF(I_EMPLOYEE_REC.payroll_name,
I_EMPLOYEE_REC.TIME_PERIOD_ID,
I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_ID,
I_EMPLOYEE_REC.Assignment_Id,
I_EMPLOYEE_REC.Business_Group_Id,
I_EMPLOYEE_REC.END_DATE,
I_EMPLOYEE_REC.PREVIOUS_PAYPERIOD_END_DATE,
I_EMPLOYEE_REC.GAP_PERCENT) LOOP
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Employee_Number ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Employee_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.activity_centre ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.ORGANIZATION ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.national_identifier ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.grade_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.location ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.NEW_HIRE_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.TERMINATION_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Payroll_action_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Payroll_Name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Period_name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.time_period_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Assignment_action_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.business_group_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.END_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.ASSIGNMENT_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_EMP_DIFF.UNIT_Name ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_EMP_DIFF.UNIT_Value_Name ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_EMP_DIFF.ELEMENT_UNIT ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_EMP_DIFF.UNIT_RESULT ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_EMP_DIFF.ELE_BAL_CODE ||
' ');
fnd_file.put_line(fnd_file.output,
'' || '3.GAP-GAP' ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.payroll_id ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Tot_Pay_Bal_Current ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.Tot_Pay_Bal_Previous ||
' ');
fnd_file.put_line(fnd_file.output,
'' || I_employee_rec.GAP || ' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.GAP_PERCENT ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYROLL ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYPERIOD_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.CURRENT_PAYPERIOD_END_DATE ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYROLL ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYPERIOD_ID ||
' ');
fnd_file.put_line(fnd_file.output,
'' ||
I_employee_rec.PREVIOUS_PAYPERIOD_END_DATE ||
' ');
fnd_file.put_line(fnd_file.output, ' ');
END LOOP;
END;
END IF;
END IF;
END LOOP;
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
'' || V_payroll_name ||
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, '');
fnd_file.put_line(fnd_file.output,
'' || V_period_name ||
' ');
fnd_file.put_line(fnd_file.output, ' ');
fnd_file.put_line(fnd_file.output, ' ');
EXCEPTION
WHEN e_error_exception THEN
fnd_file.put_line(fnd_file.log, V_error_msg);
ERRBUF := V_error_msg;
RETCODE := 2;
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log, SQLERRM);
ERRBUF := SQLERRM;
RETCODE := 2;
END;
END;
/
Leave a Reply