CREATE OR REPLACE PACKAGE BODY APPS.

XXHXT_TIMECARD_PKG
AS
v_public_dir VARCHAR2 (200);
v_public_error VARCHAR2 (200);
v_sql_error_code VARCHAR2 (50);

PROCEDURE update_timecard (
p_tbb_id IN hxc_time_building_blocks.time_building_block_id%TYPE,p_purpose in
VARCHAR2 )
IS
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_time_building_block_id hxc_time_building_blocks.time_building_block_id
%TYPE;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
l_error_code VARCHAR2 (200);
l_error_msg VARCHAR2 (200);

l_new_timecard_id NUMBER; -- Will hold TC_ID, returned by the

deposit process
l_new_timecard_ovn NUMBER; -- Will hold TC ovn, returned by the
deposit process

l_tbl_messages HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE;
l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
i PLS_INTEGER;
BEGIN
hxc_timestore_deposit.update_building_block (
p_building_block_id => p_tbb_id,
p_app_blocks => l_tbl_timecard_info,
p_comment_text => p_purpose,
p_app_attributes => l_tbl_attributes_info);

hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks => l_tbl_timecard_info,
p_app_attributes => l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit Process',
p_retrieval_process => 'BEE Retrieval Process', -- Only used for
MIGRATION
p_timecard_id => l_new_timecard_id,
p_timecard_ovn => l_new_timecard_ovn);

IF l_tbl_messages.COUNT != 0
THEN
i := l_tbl_messages.FIRST;

LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (i));
l_message :=
fnd_message.get_string (
appin => l_tbl_messages (i).application_short_name,
namein => l_tbl_messages (i).message_name);
fnd_file.put_line (fnd_file.LOG,
' ########## MSG FROM TABLE : ' || p_tbb_id);

i := l_tbl_messages.NEXT (i);
END LOOP;
END IF;

Fnd_file.put_line (fnd_file.LOG,
' ########## Timecard Updated with : ' || p_tbb_id);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
l_error_code := SQLCODE;
l_error_msg := SQLERRM;
fnd_file.put_line (
fnd_file.LOG,
' ***FAILURE IN UPDATE TIMEENTRY FOR TIMECARD ID : '
|| p_tbb_id
|| ' :'
|| SQLERRM);
END;

PROCEDURE create_timecard_history (p_holiday_Name IN VARCHAR2,

p_alternate_Name IN VARCHAR2)
IS
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
l_time_building_block_id hxc_time_building_blocks.time_building_block_id
%TYPE;
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;

l_new_timecard_id NUMBER; -- Will hold TC_ID, returned by the

deposit process
l_new_timecard_ovn NUMBER; -- Will hold TC ovn, returned by the
deposit process

l_tbl_messages HXC_SELF_SERVICE_TIME_DEPOSIT.MESSAGE_TABLE;
l_holiday_Name VARCHAR2 (200) := p_holiday_Name; -- 'India Public
Holidays';
l_alternate_Name VARCHAR2 (200); --'India Alternate Names';
l_error_code VARCHAR2 (200);
l_error_msg VARCHAR2 (200);
l_date_tbc DATE;
l_no_days NUMBER;
l_from_date DATE;
l_to_date DATE;
l_Holiday_date DATE;
l_psn_id per_all_people_f.person_id%TYPE;
l_uom NUMBER := 0; -- To capture the 8 / 12 hours unit of
measure for leave
l_Element_code
FND_DESCR_FLEX_CONTEXTS_VL.DESCRIPTIVE_FLEX_CONTEXT_CODE%TYPE;
l_error VARCHAR2 (200);
l_day_name VARCHAR2 (10);
l_leave_type VARCHAR2 (100);
l_emp_code VARCHAR2 (10);
l_emp_name VARCHAR2 (100);
l_timecard_entered_count NUMBER := 0;
l_log_from_date DATE;
l_purpose VARCHAR2 (300);
i PLS_INTEGER;
l_message fnd_new_messages.MESSAGE_TEXT%TYPE;
l_leave_days NUMBER;

/* Added by Ravi for Singapore Implementation */

CURSOR chk_person_validity (
p_employee_number VARCHAR2)
IS
SELECT papf.person_id, to_number(flv.tag) uom, flv.description alternate_name
FROM per_all_people_f papf, per_all_assignments_f paaf,
fnd_lookup_values flv, HR_SOFT_CODING_KEYFLEX hsck
WHERE papf.current_employee_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND papf.employee_number = p_employee_number
AND papf.person_id = paaf.person_id
and TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.soft_coding_keyflex_id =
hsck.soft_coding_keyflex_id
AND hsck.segment5 = flv.lookup_code
AND flv.lookup_type = 'XXHXT_SG_SETUP_MAPPING';

/* Commented by Ravi for Singapore Implementation

SELECT *
FROM per_all_people_f
WHERE current_employee_flag = 'Y'
AND TRUNC (SYSDATE) BETWEEN effective_start_date
AND effective_end_date
AND employee_number = p_employee_number; --c1.employee_number;
*/

r_person_valid chk_person_validity%ROWTYPE;

CURSOR chk_for_holiday (
p_date_tbc DATE)
IS
SELECT HD.HOLIDAY_DATE
INTO l_Holiday_date
FROM HXT_HOLIDAY_CALENDARS HC, HXT_HOLIDAY_DAYS_VL HD
WHERE HC.ID = HD.HCL_ID
AND HC.name = p_holiday_Name
AND HD.HOLIDAY_DATE = p_date_tbc;

r_chk_for_holiday chk_for_holiday%ROWTYPE;

CURSOR cur_get_element_code (
p_element_code VARCHAR2,p_alternate_name1 VARCHAR2)
IS
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE
INTO l_Element_code
FROM FND_DESCR_FLEX_CONTEXTS_VL
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'OTC Information Types'
AND DESCRIPTIVE_FLEX_CONTEXT_NAME IN
(SELECT pet.ELEMENT_NAME
FROM HXC_ALIAS_DEFINITIONS_V had,
HXC_ALIAS_VALUES_V hav,
pay_element_types_f pet
WHERE had.ALIAS_DEFINITION_ID =
hav.ALIAS_DEFINITION_ID
AND hav.attribute1 = pet.element_type_id
AND ALIAS_DEFINITION_NAME = p_alternate_name1
AND ALIAS_VALUE_NAME = p_element_code)
AND ENABLED_FLAG = 'Y';

r_get_element_code cur_get_element_code%ROWTYPE;

--Fetching leaves history data from external table

CURSOR mig_leaves_cur
IS
SELECT xoml.employee_number,
xoml.employee_name,
xoml.Leave_type,
xoml.number_of_days,
xoml.from_date,
xoml.TO_DATE,
xoml.Purpose
FROM XXHXT_MIGRATE_LEAVES xoml
WHERE xoml.from_date >= TO_DATE ('01-Apr-2012', 'DD-Mon-YYYY')
AND xoml.employee_number IS NOT NULL
AND NOT EXISTS --code add on 08-Nov-2012 for validation (timecard
data whether already exist in the system or not)
(SELECT 1
FROM hxc_time_building_blocks htbb,
per_all_people_f papf
WHERE htbb.resource_id = papf.person_id
AND TRUNC (SYSDATE) BETWEEN
papf.effective_start_date
AND
papf.effective_end_date
AND current_employee_flag = 'Y'
AND ( htbb.scope = 'DAY'
OR htbb.scope = 'TIMECARD'
OR htbb.scope = 'DETAIL')
AND TRUNC (htbb.date_to) =
TO_DATE ('12/31/4712',
'MM/DD-YYYY')
AND employee_number = xoml.employee_number
AND (xoml.from_date BETWEEN TRUNC (
start_time)
AND TRUNC (
stop_time)
OR xoml.from_date =
TRUNC (start_time)
OR xoml.from_date = TRUNC (stop_time)));
BEGIN
fnd_file.put_line (fnd_file.LOG,
'******** BEGIN Of Load Program***********');

DELETE FROM xxhxt_load_accr_errors;

FOR c1 IN mig_leaves_cur
LOOP
BEGIN
fnd_file.put_line (fnd_file.LOG,
'employee_number : ' || c1.employee_number);
fnd_file.put_line (fnd_file.LOG,
'Employee_name : ' || c1.EMPLOYEE_NAME);
l_emp_code := c1.employee_number;
l_emp_name := c1.EMPLOYEE_NAME;

OPEN chk_person_validity (c1.employee_number);

FETCH chk_person_validity INTO r_person_valid;

IF chk_person_validity%FOUND
THEN
CLOSE chk_person_validity;

l_psn_id := r_person_valid.person_id;
l_uom := r_person_valid.uom;
l_alternate_name := r_person_valid.alternate_name;
l_from_date := c1.from_date;
l_to_date := c1.TO_DATE;
l_no_days := (c1.TO_DATE - c1.from_date) + 1;
l_Holiday_date := NULL;
l_leave_type := c1.Leave_type;
l_log_from_date := c1.from_date;
l_purpose := c1.Purpose;
-- fnd_file.put_line (fnd_file.LOG, 'PERSON : '||l_psn_id);
fnd_file.put_line (fnd_file.LOG,
'LEAVE_TYPE : ' || c1.Leave_type);

fnd_file.put_line ( fnd_file.LOG,'Entering '||l_no_days);

l_leave_days := c1.number_of_days * l_uom;

FOR n IN 1 .. l_no_days
LOOP

fnd_file.put_line ( fnd_file.LOG,'Entering 0');

BEGIN
OPEN chk_for_holiday (l_from_date);

FETCH chk_for_holiday INTO r_chk_for_holiday;

fnd_file.put_line ( fnd_file.LOG,TO_CHAR(l_from_date));

IF chk_for_holiday%NOTFOUND
THEN
l_day_name :=
RTRIM (LTRIM (TO_CHAR (l_from_date, 'Day')));

IF (l_day_name <> 'Saturday'

AND l_day_name <> 'Sunday'
OR l_leave_type = 'Maternity Leave')
THEN
BEGIN

fnd_file.put_line
( fnd_file.LOG,'Entering 0.2');
OPEN cur_get_element_code
(c1.Leave_type,l_alternate_name);

FETCH cur_get_element_code
INTO r_get_element_code;

IF cur_get_element_code%NOTFOUND
THEN
fnd_file.put_line (
fnd_file.LOG,
' *** FAILURE IN CREATING
TIME ENTRY for Employee : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date));

INSERT
INTO xxhxt_load_accr_errors (
XXHXT_LOAD_ACCR_ERRORS_ID,
ERROR_CODE,
ERROR_MESSAGE)
VALUES (
1,
-1,
' *** FAILURE IN CREATING TIME ENTRY FOR
Employee : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date));
ELSE
CLOSE cur_get_element_code;

l_Element_code :=

r_get_element_code.DESCRIPTIVE_FLEX_CONTEXT_CODE;
l_new_timecard_ovn := NULL;

fnd_file.put_line ( fnd_file.LOG,'Entering 0.3'||l_Element_code);

BEGIN

fnd_file.put_line
( fnd_file.LOG,'Entering 1');

if (l_leave_days = 4 or l_leave_days = 6) and

c1.number_of_days > 0.5 then
l_uom := l_uom *
0.5;
end if;
if c1.number_of_days = 0.5 then
l_uom := l_uom * 0.5;
fnd_file.put_line
( fnd_file.LOG,'Entering 0.3'|| to_char(l_uom));

end if;

hxc_timestore_deposit.create_time_entry (
p_measure => l_uom, /*
Added by Ravi for Singapore implementation changed from 1 to uom */
p_day => TRUNC (

fnd_date.canonical_to_date (
TO_CHAR (

l_from_date,

'YYYY/MM/DD'))),
p_resource_id => l_psn_id,
p_resource_type => 'PERSON',
-- p_comment_text => 'HARDCODED
VALUE',
p_deposit_process => 'OTL Deposit
Process',
p_app_blocks =>
l_tbl_timecard_info,
p_app_attributes =>
l_tbl_attributes_info,
p_time_building_block_id =>
l_time_building_block_id);

hxc_timestore_deposit.create_attribute (
p_building_block_id =>
l_time_building_block_id, --returned by create_time_entry
p_attribute_name => 'Dummy Element
Context',
p_attribute_value => l_Element_code,
p_app_attributes =>
l_tbl_attributes_info);

hxc_timestore_deposit.execute_deposit_process (
p_validate => FALSE,
p_app_blocks =>
l_tbl_timecard_info,
p_app_attributes =>
l_tbl_attributes_info,
p_messages => l_tbl_messages,
p_mode => 'SUBMIT',
p_deposit_process => 'OTL Deposit
Process',
p_retrieval_process => 'BEE Retrieval
Process', -- Only used for MIGRATION
p_timecard_id => l_new_timecard_id,
p_timecard_ovn =>
l_new_timecard_ovn);

COMMIT;
fnd_file.put_line ( fnd_file.LOG,'Entering 2');

IF l_new_timecard_id > 0 then

update_timecard
(l_new_timecard_id,l_purpose);
COMMIT;

END IF;

l_leave_days := l_leave_days - l_uom;

l_timecard_entered_count :=
l_timecard_entered_count + 1;

fnd_file.put_line (
fnd_file.LOG,
' *** SUCCESS IN
CREATING TIME ENTRY FOR EMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date));

IF l_tbl_messages.COUNT != 0
THEN
i := l_tbl_messages.FIRST;

LOOP
EXIT WHEN (NOT l_tbl_messages.EXISTS (
i));
l_message :=
fnd_message.get_string (
appin => l_tbl_messages
(i).application_short_name,
namein => l_tbl_messages
(i).message_name);

fnd_file.put_line (
fnd_file.LOG,
' ########## MSG FROM TABLE : '
|| l_tbl_messages (i).message_name
|| ': '
|| l_message);

i := l_tbl_messages.NEXT (i);
END LOOP;
END IF;

INSERT
INTO xxhxt_load_accr_errors (
XXHXT_LOAD_ACCR_ERRORS_ID,
ERROR_CODE,
ERROR_MESSAGE)
VALUES (
1,
-1,
'*** SUCCESS IN CREATING TIME ENTRY
FOR EMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date));
EXCEPTION
WHEN OTHERS
THEN
l_error_code := SQLCODE;
l_error_msg := SQLERRM;
fnd_file.put_line (
fnd_file.LOG,
' ***FAILURE IN CREATING
TIMEENTRY FOREMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date)
|| ' ERROR **** '
|| SQLERRM);

INSERT
INTO xxhxt_load_accr_errors (
XXHXT_LOAD_ACCR_ERRORS_ID,
ERROR_CODE,
ERROR_MESSAGE)
VALUES (
1,
l_error_code,
'***FAILURE IN CREATING TIMEENTRY
FOR EMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') '
|| l_Element_code
|| ' ,date : '
|| TO_CHAR (l_from_date)
|| ' ERROR **** '
|| l_error_msg);
END;
END IF;
END;
END IF;
CLOSE chk_for_holiday;
ELSE
-- l_holiday_count := l_holiday_count + 1;
fnd_file.put_line (
fnd_file.LOG,
' HOLIDAY ON ' || l_from_date);

CLOSE chk_for_holiday;
END IF;
END;

l_from_date := l_from_date + 1;
END LOOP;

INSERT INTO xxhxt_load_accr_errors (XXHXT_LOAD_ACCR_ERRORS_ID,

ERROR_CODE,
ERROR_MESSAGE,
ERROR_CHECK)
VALUES (
1,
-1,
l_emp_code
|| ','
|| l_emp_name
|| ','
|| l_timecard_entered_count
|| ','
|| l_leave_type
|| ','
|| ','
|| l_log_from_date
|| ','
|| l_to_date
|| ','
|| l_purpose,
'Inserted Time Entry FOR person, ' || l_psn_id);

COMMIT;
l_timecard_entered_count := 0;
ELSE
CLOSE chk_person_validity;

fnd_file.put_line (
fnd_file.LOG,
'***FAILURE IN CREATING TIMEENTRY FOR EMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') , ERROR: Not a valid Person as of SYSDATE');

INSERT
INTO xxhxt_load_accr_errors (XXHXT_LOAD_ACCR_ERRORS_ID,
ERROR_CODE,
ERROR_MESSAGE)
VALUES (
1,
-1,
'***FAILURE IN CREATING TIMEENTRY FOR EMPLOYEE : '
|| l_emp_name
|| ' ('
|| l_emp_code
|| ') , ERROR: Not a valid Person as of SYSDATE');
END IF;
EXCEPTION
WHEN OTHERS
THEN
l_error_code := SQLCODE;
l_error_msg := SQLERRM;

fnd_file.put_line (
fnd_file.LOG,
'***FAILURE IN CREATING TIMEENTRY FOR person '
|| l_psn_id
|| ' ,element starting from date: '
|| TO_CHAR (l_from_date)
|| ' with ERROR: '
|| SQLERRM);

INSERT
INTO xxhxt_load_accr_errors (XXHXT_LOAD_ACCR_ERRORS_ID,
ERROR_CODE,
ERROR_MESSAGE)
VALUES (
1,
l_error_code,
'***FAILURE IN CREATING TIMEENTRY FOR person '
|| l_psn_id
|| ' ,element starting from date: '
|| TO_CHAR (l_from_date)
|| ' with ERROR: '
|| l_error_msg);
END;
END LOOP;

fnd_file.put_line (fnd_file.LOG,
'******** END Of Load Program***********');
END create_timecard_history;

PROCEDURE create_directory_path (p_error_code OUT VARCHAR2,

p_error_message OUT VARCHAR2,
p_filename IN VARCHAR2,
p_holiday_Name IN VARCHAR2,
p_alternate_Name IN VARCHAR2)
IS
v_directory_path VARCHAR2 (500);
v_env VARCHAR2 (20);
v_file VARCHAR2 (100);
v_dir VARCHAR2 (100);
v_path VARCHAR2 (100);
v_holiday VARCHAR2 (100);
v_alternate VARCHAR2 (100);
BEGIN
-- Get the directory path of the 'oracle' directory

BEGIN
v_path := LOWER (p_filename);
v_holiday := INITCAP (p_holiday_Name);
v_alternate := INITCAP (p_alternate_Name);

IF INSTR (v_path, '/') > 0

THEN
v_file :=
SUBSTR (v_path,
(INSTR (v_path,
'/',
-1,
1)
+ 1),
LENGTH (v_path));

IF v_file IS NULL
THEN
v_dir := v_path;
ELSE
v_dir := SUBSTR (v_path, 0, LENGTH (v_path) - LENGTH (v_file));
END IF;
ELSE
v_file := v_path;
END IF;

fnd_file.put_line (fnd_file.LOG, 'User Input: ' || p_filename);

fnd_file.put_line (fnd_file.LOG, 'Directory Path: ' || v_dir);
fnd_file.put_line (fnd_file.LOG, 'File Name: ' || v_file);

-- Create the directory

--mdirectory_name := 'XXPER_HR_DIR' ;
EXECUTE IMMEDIATE
'create or replace directory '
|| gv_location
|| ' as '''
|| v_dir
|| '''';

EXECUTE IMMEDIATE
'grant read on directory ' || gv_location || ' to public';

----
SELECT UNIQUE directory_path
INTO v_directory_path
FROM all_directories
WHERE directory_name = 'XXPER_HR_DIR';

IF v_directory_path IS NOT NULL

THEN
APPS.XXHXT_TIMECARD_PKG.create_timecard_history (v_holiday,
v_alternate);
END IF;
EXCEPTION
WHEN OTHERS
THEN
v_directory_path := NULL;
END;
END create_directory_path;
PROCEDURE OVERTIME_SUM_DETAIL_RPT (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
P_PERIOD_START IN DATE,
P_PERIOD_END IN DATE,
P_EMPNO IN VARCHAR2,
P_RESPONSIBILITY_ID NUMBER,
P_USER_ID NUMBER,
P_CONC_REQUEST_ID NUMBER,
P_CHANGES_SINCE DATE
)
AS
CURSOR c_shift_sup
IS
SELECT papf.person_id,papf.employee_number
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
HR_SOFT_CODING_KEYFLEX hsck,
PAY_PAYROLLS_F PPF
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PAYROLL_ID = PPF.PAYROLL_ID
AND PPF.PAYROLL_NAME = 'Singapore Monthly'
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID
AND HSCK.SEGMENT5 IN ('SNG A', 'SNG B', 'SNG C', 'SNG D')
AND HSCK.SEGMENT2 = (SELECT EMPLOYEE_ID FROM FND_USER WHERE USER_ID =
P_USER_ID)
AND papf.employee_number = DECODE (p_empno, NULL,
papf.employee_number, p_empno)
UNION
SELECT papf.person_id,papf.employee_number
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_ALL_PEOPLE_F PAPF1,
HR_SOFT_CODING_KEYFLEX hsck,
PAY_PAYROLLS_F PPF
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PAYROLL_ID = PPF.PAYROLL_ID
AND PPF.PAYROLL_NAME = 'Singapore Monthly'
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID

AND HSCK.SEGMENT5 IN ('SNG A', 'SNG B', 'SNG C', 'SNG D')
AND PAAF.SUPERVISOR_ID = PAPF1.PERSON_ID
AND PAAF.SUPERVISOR_ID = (SELECT EMPLOYEE_ID FROM FND_USER WHERE
USER_ID = P_USER_ID)
AND TRUNC (SYSDATE) BETWEEN PAPF1.EFFECTIVE_START_DATE
AND PAPF1.EFFECTIVE_END_DATE
AND papf.employee_number = DECODE (p_empno,NULL, papf.employee_number,
p_empno)
UNION
SELECT papf.person_id,papf.employee_number
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
HR_SOFT_CODING_KEYFLEX hsck,
PAY_PAYROLLS_F PPF,
FND_RESPONSIBILITY_TL FRT
WHERE PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PAYROLL_ID = PPF.PAYROLL_ID
AND PPF.PAYROLL_NAME = 'Singapore Monthly'
AND TRUNC (SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE
AND PAPF.EFFECTIVE_END_DATE
AND TRUNC (SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE
AND PAAF.EFFECTIVE_END_DATE
AND PAAF.SOFT_CODING_KEYFLEX_ID = HSCK.SOFT_CODING_KEYFLEX_ID

AND HSCK.SEGMENT5 IN ('SNG A', 'SNG B', 'SNG C', 'SNG D')
AND papf.employee_number = DECODE (p_empno,NULL, papf.employee_number,
p_empno)
AND 'Y' IN (select 'Y'
from FND_USER_RESP_GROUPS_DIRECT furgd,
fnd_responsibility_tl frt
where furgd.user_id = P_USER_ID
and furgd.responsibility_id = frt.responsibility_id
and frt.responsibility_name = 'Xilinx Singapore OTL
Administrator'
and furgd.end_date is null)
;

c_shift_sup_rec c_shift_sup%ROWTYPE;

CURSOR c_emp_tc_details (
l_emp_no VARCHAR2)
IS
SELECT NVL (((DETAIL.STOP_TIME - DETAIL.START_TIME) * 24),DETAIL.MEASURE)
Leave_Hours,
(SELECT lookup_code FROM FND_LOOKUP_VALUES WHERE UPPER(meaning) =
UPPER(petf.element_name) AND LOOKUP_TYPE = 'XXHXT_SG_SETUP_MAPPING') element_code,
ppx.person_id,
TO_CHAR (DETAIL.START_TIME, 'HH24:MI') IN_TIME,
TO_CHAR (DETAIL.STOP_TIME, 'HH24:MI') OUT_TIME,
NVL(trunc(detail.start_time), DAY.START_TIME) date_worked,
ppx.employee_number,
hts.APPROVAL_STATUS
FROM hxc_time_building_blocks TIMECARD,
hxc_time_building_blocks DAY,
hxc_time_building_blocks DETAIL,
per_people_x PPX,
per_assignments_x PAX,
hxc_bld_blk_info_types HBB,
hxc_bld_blk_info_type_usages HBBI,
hxc_time_attributes HTA,
hxc_time_attribute_usages HTAU,
pay_element_types_f PETF,
pay_element_links_f PELF,
hxc_alias_values_tl HAVT,
hxc_alias_values HAV,
hxc_alias_definitions HAD,
hxc_alias_types HAT,
hxc_timecard_summary hts
WHERE TIMECARD.time_building_block_id = DAY.parent_building_block_id
AND DAY.time_building_block_id = DETAIL.parent_building_block_id
AND TIMECARD.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
AND TIMECARD.scope = 'TIMECARD'
AND TIMECARD.TYPE = 'RANGE'
AND TIMECARD.Resource_Type = 'PERSON'
AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
AND DAY.start_time BETWEEN P_PERIOD_START AND P_PERIOD_END
AND DAY.Scope = 'DAY'
AND DAY.TYPE = 'RANGE'
AND DETAIL.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
AND DETAIL.Scope = 'DETAIL'
AND TIMECARD.resource_id = PPX.person_id
AND TIMECARD.resource_id = PAX.person_id
AND DAY.resource_id = PPX.person_id
AND DAY.resource_id = PAX.person_id
AND DETAIL.resource_id = PPX.person_id
AND DETAIL.resource_id = PAX.person_id
AND PPX.employee_number IS NOT NULL
AND PPX.person_id = PAX.person_id
AND PAX.Assignment_Type = 'E'
AND PAX.Primary_Flag = 'Y'
AND DETAIL.Time_Building_Block_Id = Htau.Time_Building_Block_Id
AND DETAIL.Object_Version_Number = HTAU.Time_Building_Block_Ovn
AND HTA.Time_Attribute_Id = HTAU.Time_Attribute_Id
AND HBBI.bld_blk_info_type_id = HTA.bld_blk_info_type_id
AND HBB.bld_blk_info_type_id = HBBI.bld_blk_info_type_id
AND HBBI.building_block_category = 'ELEMENT'
AND TO_CHAR (PETF.Element_Type_Id) =
REPLACE (HTA.Attribute_Category, 'ELEMENT - ', '')
AND PETF.Element_Type_Id = PELF.Element_Type_Id
AND PAX.Payroll_Id = PELF.Payroll_Id
AND HAVT.Alias_Value_Id = HAV.Alias_Value_Id
AND HAV.Attribute_Category = 'PAYROLL_ELEMENTS'
AND HAV.Enabled_Flag = 'Y'
AND HAV.date_to IS NULL
AND HAV.Attribute1 = PETF.Element_Type_Id
AND DAY.Start_Time BETWEEN HAV.date_from
AND NVL (
HAV.date_to,
TO_DATE ('31/12/4712',
'DD/MM/YYYY'))
AND HAD.Alias_Definition_Id = HAV.Alias_Definition_Id
AND HAD.alias_type_id = HAT.alias_type_id
AND HAT.reference_object = 'PAYROLL_ELEMENTS'
AND PPX.EMPLOYEE_NUMBER = l_emp_no
AND HAD.Alias_Definition_id IN
(hxc_preference_evaluation.resource_preferences (
PPX.Person_Id,
'TC_W_TCRD_ALIASES',
1),
hxc_preference_evaluation.resource_preferences (
PPX.Person_Id,
'TC_W_TCRD_ALIASES',
2))
AND DAY.parent_building_block_ovn = hts.timecard_ovn
AND DAY.parent_building_block_id = hts.timecard_id
AND hts.approval_status IN ('SUBMITTED','APPROVED') order by 6,4;

c_emp_tc_details_rec c_emp_tc_details%ROWTYPE;
CURSOR c_shift_emp (l_empno varchar2)
IS
SELECT DISTINCT papf.person_id,
papf.employee_number,
papf.full_name,
-- hdhwf.date_worked,
hsck.segment5,
DECODE(hsck.segment5,'SNG A','SG 12 Hrs Shift Holiday
Calendar','SNG C','SG 12 Hrs Shift Holiday Calendar','SG 8 Hrs Shift Holiday
Calendar') Calendar
FROM hxt_det_hours_worked_f hdhwf,
hxt_timecards_f HTF,
pay_element_types_f petf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf,
HR_SOFT_CODING_KEYFLEX hsck
WHERE hdhwf.tim_id = HTF.id
AND hdhwf.element_type_id = petf.element_type_id
AND HTF.for_person_id = papf.person_id
AND hdhwf.effective_end_date = '31-DEC-4712'
AND PAPF.employee_number = DECODE (l_empno, NULL,
papf.EMPLOYEE_NUMBER, l_empno)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND hdhwf.date_worked BETWEEN P_PERIOD_START

AND P_PERIOD_END
AND papf.person_id = paaf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment5 IN ('SNG A', 'SNG B', 'SNG C', 'SNG D');

c_shift_rec c_shift_emp%ROWTYPE;

CURSOR c_timecard(l_emp_no varchar2)

IS
SELECT * FROM (
SELECT papf.person_id,
papf.employee_number,
papf.full_name,
hdhwf.date_worked,
hdhwf.time_in,
hdhwf.time_OUT,
TO_CHAR (hdhwf.time_in, 'HH24:MI:SS') in_Time,
TO_CHAR (hdhwf.time_OUT, 'HH24:MI:SS') out_time,
hdhwf.hours,
(SELECT lookup_code from FND_LOOKUP_VALUES WHERE UPPER(meaning) =
UPPER(petf.element_name) and lookup_type = 'XXHXT_SG_SETUP_MAPPING') element_code,
HTF.for_person_id,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id = paaf.organization_id)
org_name,
TO_NUMBER (
REPLACE (
SUBSTR (TO_CHAR (hdhwf.time_in, 'HH24:MI:SS'), 1, 5),
':',
NULL))
in_time_number,
TO_NUMBER (
REPLACE (
SUBSTR (TO_CHAR (hdhwf.time_out, 'HH24:MI:SS'), 1, 5),
':',
NULL))
out_time_number,
'APPROVED' APPROVAL_STATUS,
'PAYROLL' USER_TYPE
FROM hxt_det_hours_worked_f hdhwf,
hxt_timecards_f HTF,
pay_element_types_f petf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf
WHERE hdhwf.tim_id = HTF.id
AND hdhwf.element_type_id = petf.element_type_id
AND HTF.for_person_id = papf.person_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND hdhwf.effective_end_date = '31-DEC-4712'
AND PAPF.EMPLOYEE_NUMBER =
DECODE (l_emp_no, NULL, papf.EMPLOYEE_NUMBER, l_emp_no)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND hdhwf.date_worked BETWEEN P_PERIOD_START
AND P_PERIOD_END
AND papf.person_id = paaf.person_id
-- AND hdhwf.date_worked = '03-OCT-2013'
AND hdhwf.hours <> 0
AND DECODE(P_CHANGES_SINCE,NULL,'1',HDHWF.CREATION_DATE) >=
DECODE(P_CHANGES_SINCE,NULL,'1',P_CHANGES_SINCE)
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
) ORDER BY TIME_IN
;

CURSOR c_timecard_shift_ldr(l_emp_no varchar2)

IS
SELECT * FROM (
SELECT ppx.person_id,
ppx.employee_number,
ppx.full_name,
NVL(trunc(detail.start_time), DAY.START_TIME) date_worked,
null time_in,
null time_OUT,
TO_CHAR (DETAIL.START_TIME, 'HH24:MI') IN_TIME,
TO_CHAR (DETAIL.STOP_TIME, 'HH24:MI') OUT_TIME,
NVL (((DETAIL.STOP_TIME - DETAIL.START_TIME) * 24),DETAIL.MEASURE)
Hours,
(SELECT lookup_code FROM FND_LOOKUP_VALUES WHERE UPPER(meaning) =
UPPER(petf.element_name) AND LOOKUP_TYPE = 'XXHXT_SG_SETUP_MAPPING') element_code,
null for_person_id,
null org_name,
null in_time_number,
null out_time_number,
hts.APPROVAL_STATUS,
'EMPLOYEE' USER_TYPE
FROM hxc_time_building_blocks TIMECARD,
hxc_time_building_blocks DAY,
hxc_time_building_blocks DETAIL,
per_people_x PPX,
per_assignments_x PAX,
hxc_bld_blk_info_types HBB,
hxc_bld_blk_info_type_usages HBBI,
hxc_time_attributes HTA,
hxc_time_attribute_usages HTAU,
pay_element_types_f PETF,
pay_element_links_f PELF,
hxc_alias_values_tl HAVT,
hxc_alias_values HAV,
hxc_alias_definitions HAD,
hxc_alias_types HAT,
hxc_timecard_summary hts
-- FND_LOOKUP_VALUES FLV
WHERE TIMECARD.time_building_block_id = DAY.parent_building_block_id
AND DAY.time_building_block_id = DETAIL.parent_building_block_id
AND TIMECARD.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
AND TIMECARD.scope = 'TIMECARD'
AND TIMECARD.TYPE = 'RANGE'
AND TIMECARD.Resource_Type = 'PERSON'
AND DAY.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
-- AND DAY.start_time ='16-DEC-2013'
AND DAY.start_time BETWEEN P_PERIOD_START AND P_PERIOD_END
AND DAY.Scope = 'DAY'
AND DAY.TYPE = 'RANGE'
AND DETAIL.date_to = TO_DATE ('31/12/4712', 'DD/MM/YYYY')
AND DETAIL.Scope = 'DETAIL'
AND TIMECARD.resource_id = PPX.person_id
AND TIMECARD.resource_id = PAX.person_id
AND DAY.resource_id = PPX.person_id
AND DAY.resource_id = PAX.person_id
AND DETAIL.resource_id = PPX.person_id
AND DETAIL.resource_id = PAX.person_id
AND PPX.employee_number IS NOT NULL
AND PPX.person_id = PAX.person_id
AND PAX.Assignment_Type = 'E'
AND PAX.Primary_Flag = 'Y'
AND DETAIL.Time_Building_Block_Id = Htau.Time_Building_Block_Id
AND DETAIL.Object_Version_Number = HTAU.Time_Building_Block_Ovn
AND HTA.Time_Attribute_Id = HTAU.Time_Attribute_Id
AND HBBI.bld_blk_info_type_id = HTA.bld_blk_info_type_id
AND HBB.bld_blk_info_type_id = HBBI.bld_blk_info_type_id
AND HBBI.building_block_category = 'ELEMENT'
AND TO_CHAR (PETF.Element_Type_Id) =
REPLACE (HTA.Attribute_Category, 'ELEMENT - ', '')
AND PETF.Element_Type_Id = PELF.Element_Type_Id
AND PAX.Payroll_Id = PELF.Payroll_Id
AND HAVT.Alias_Value_Id = HAV.Alias_Value_Id
AND HAV.Attribute_Category = 'PAYROLL_ELEMENTS'
AND HAV.Enabled_Flag = 'Y'
AND HAV.date_to IS NULL
AND HAV.Attribute1 = PETF.Element_Type_Id
AND DAY.Start_Time BETWEEN HAV.date_from
AND NVL (
HAV.date_to,
TO_DATE ('31/12/4712',
'DD/MM/YYYY'))
AND HAD.Alias_Definition_Id = HAV.Alias_Definition_Id
AND HAD.alias_type_id = HAT.alias_type_id
AND HAT.reference_object = 'PAYROLL_ELEMENTS'
AND PPX.EMPLOYEE_NUMBER = DECODE (l_emp_no, NULL,
ppx.EMPLOYEE_NUMBER, l_emp_no)
AND HAD.Alias_Definition_id IN
(hxc_preference_evaluation.resource_preferences (
PPX.Person_Id,
'TC_W_TCRD_ALIASES',
1),
hxc_preference_evaluation.resource_preferences (
PPX.Person_Id,
'TC_W_TCRD_ALIASES',
2))
AND DAY.parent_building_block_ovn = hts.timecard_ovn
AND DAY.parent_building_block_id = hts.timecard_id
AND hts.approval_status IN ('SUBMITTED','APPROVED')
-- AND NVL(trunc(detail.start_time), DAY.START_TIME) = '16-SEP-2013'
-- AND UPPER(flv.meaning) = UPPER(petf.element_name) AND
flv.LOOKUP_TYPE = 'XXHXT_SG_SETUP_MAPPING'
-- AND flv.lookup_code in ('SRH','SA2','SA3','SA5','SA6')
) ORDER BY DATE_WORKED,IN_TIME
;

c_timecard_rec c_timecard%ROWTYPE;

c_timecard_shift_ldr_rec c_timecard_shift_ldr%ROWTYPE;

CURSOR c_detail (
l_empno VARCHAR2)
IS
SELECT *
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE EMPLOYEE_NUMBER =
DECODE (l_empno, 'NULL', employee_number, l_empno);

c_detail_rec c_detail%ROWTYPE;

v_pub_holiday VARCHAR2 (10);

l_request_id FND_CONCURRENT_REQUESTS.REQUEST_ID%TYPE;
lc_boolean2 BOOLEAN;
cnt NUMBER;
v_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
v_default_value PAY_INPUT_VALUES_F.DEFAULT_VALUE%TYPE;
v_leave_code VARCHAR2(10);

CURSOR c_work_schedule (
l_person_id NUMBER,
l_date_worked DATE)
IS
SELECT FLOOR ( (standard_start / 100))
+ (MOD (standard_start, 100) / 60)
hrs_start,
FLOOR ( (standard_stop / 100)) + (MOD (standard_stop, 100) / 60)
hrs_stop,
hs.hours,
hws.week_day,
hs.NAME,
(hrs.start_date + hws.seq_no - 1)
FROM hxt.hxt_rotation_plans hrp,
hxt.hxt_add_assign_info_f haai,
per_all_assignments_f paa,
hxt.hxt_rotation_schedules hrs,
hxt.hxt_weekly_work_schedules hwws,
hxt.hxt_work_shifts hws,
hxt.hxt_shifts hs
WHERE haai.rotation_plan = hrp.ID(+)
AND haai.assignment_id = paa.assignment_id
AND paa.person_id = l_person_id
AND paa.assignment_type = 'E'
AND TRUNC (SYSDATE) BETWEEN haai.effective_start_date
AND haai.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND hrp.ID = hrs.rtp_id(+)
AND hrs.tws_id = hwws.ID(+)
AND hwws.ID = hws.tws_id(+)
AND hws.sht_id = hs.ID(+)
AND (hrs.start_date + hws.seq_no - 1) = l_date_worked
ORDER BY 6;

c_work_schedule_rec c_work_schedule%ROWTYPE;

CURSOR c_shift_allow (
l_person_id1 NUMBER)
IS
SELECT petf.ELEMENT_NAME, hsdf.start_time,
hsdf.stop_time,decode(hsck.segment5,'SNG A',11.25,'SNG B',8,'SNG C',11.25,'SNG
D',8) maxhours,hrp.name
FROM HXT_ADD_ASSIGN_INFO_F haaif,
HXT_SHIFT_DIFF_RULES hsdf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_element_types_f petf,
HR_SOFT_CODING_KEYFLEX hsck,
APPS.hxt_rotation_plans hrp
WHERE haaif.assignment_id = paaf.assignment_id
AND paaf.person_id = papf.person_id
AND haaif.shift_differential_policy = sdp_id
AND papf.person_id = l_person_id1
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsdf.element_type_id = petf.element_type_id
AND haaif.rotation_plan = hrp.id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;

c_shift_allow_rec c_shift_allow%ROWTYPE;
CURSOR c_element_name (
l_element_name VARCHAR)
IS
SELECT DEFAULT_VALUE
FROM pay_element_types_f petf, pay_input_values_f pivf
WHERE petf.element_name = l_element_name
AND petf.element_type_id = pivf.element_type_id
AND (UPPER(pivf.name) = UPPER('Amount') or UPPER(pivf.name) =
UPPER('Hours'));

c_element_name_rec c_element_name%ROWTYPE;

v_transaction_id NUMBER;
v_responsibility_name FND_RESPONSIBILITY_TL.RESPONSIBILITY_NAME%TYPE;
v_date_worked DATE;
v_exist VARCHAR2(100);
v_count NUMBER;
v_emp_type VARCHAR2 (50);
v_day_rate NUMBER;
l_Holiday_date DATE;

CURSOR c_sum_hours(l_emp_no NUMBER)

IS
SELECT person_id,employee_number,date_worked,sum(hours) total_hours FROM (
SELECT papf.person_id,
papf.employee_number,
papf.full_name,
hdhwf.date_worked,
hdhwf.time_in,
hdhwf.time_OUT,
TO_CHAR (hdhwf.time_in, 'HH24:MI:SS') in_Time,
TO_CHAR (hdhwf.time_OUT, 'HH24:MI:SS') out_time,
hdhwf.hours,
(SELECT lookup_code from FND_LOOKUP_VALUES WHERE UPPER(meaning) =
UPPER(petf.element_name) and lookup_type = 'XXHXT_SG_SETUP_MAPPING') element_code,
HTF.for_person_id,
(SELECT name
FROM hr_all_organization_units
WHERE organization_id = paaf.organization_id)
org_name,
TO_NUMBER (
REPLACE (
SUBSTR (TO_CHAR (hdhwf.time_in, 'HH24:MI:SS'), 1, 5),
':',
NULL))
in_time_number,
TO_NUMBER (
REPLACE (
SUBSTR (TO_CHAR (hdhwf.time_out, 'HH24:MI:SS'), 1, 5),
':',
NULL))
out_time_number,
'APPROVED' APPROVAL_STATUS,
'PAYROLL' USER_TYPE
FROM hxt_det_hours_worked_f hdhwf,
hxt_timecards_f HTF,
pay_element_types_f petf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf
WHERE hdhwf.tim_id = HTF.id
AND hdhwf.element_type_id = petf.element_type_id
AND HTF.for_person_id = papf.person_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND hdhwf.effective_end_date = '31-DEC-4712'
AND PAPF.EMPLOYEE_NUMBER =
DECODE (l_emp_no, NULL, papf.EMPLOYEE_NUMBER, l_emp_no)
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
AND papf.effective_end_date
AND hdhwf.date_worked BETWEEN P_PERIOD_START
AND P_PERIOD_END
AND papf.person_id = paaf.person_id
AND hdhwf.hours <> 0
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND P_RESPONSIBILITY_ID IN (SELECT RESPONSIBILITY_ID FROM
FND_RESPONSIBILITY_TL WHERE UPPER(RESPONSIBILITY_NAME) = UPPER('Xilinx Singapore
OTL Administrator'))
) WHERE element_code = 'SORP'
GROUP BY person_id,employee_number,date_worked
;

c_sum_hours_rec c_sum_hours%ROWTYPE;

CURSOR chk_for_holiday (p_date_tbc DATE,p_holiday_name varchar2)

IS
SELECT HD.HOLIDAY_DATE
FROM HXT_HOLIDAY_CALENDARS HC, HXT_HOLIDAY_DAYS_VL HD
WHERE HC.ID = HD.HCL_ID
AND HC.name = p_holiday_Name
AND HD.HOLIDAY_DATE = p_date_tbc;

c_holiday_rec chk_for_holiday%ROWTYPE;

CURSOR c_upd_ph (p_request_id number)

IS
SELECT DISTINCT PERSON_ID,DATE_WORKED FROM XXHXT_OVERTIME_DETAILS_SG WHERE
REQUEST_ID = p_request_id;

c_upd_ph_rec c_upd_ph%ROWTYPE;

PROCEDURE INSERT_REC_OT(
P_TRANSACTION_ID1 NUMBER,
P_PERSON_ID1 NUMBER,
P_EMPLOYEE_NUMBER1 VARCHAR2,
P_PERIOD_START1 DATE,
P_PERIOD_END1 DATE,
P_DATE_WORKED1 DATE,
P_USER_ID1 NUMBER,
P_STATUS1 VARCHAR2,
P_IN_TIME1 VARCHAR2,
P_OUT_TIME1 VARCHAR2,
P_HOURS_TYPE1 VARCHAR2,
P_HOURS1 NUMBER,
P_REC_TYPE1 VARCHAR2,
P_USER_TYPE1 VARCHAR2
)
IS

BEGIN

INSERT INTO
XXHXT_OVERTIME_DETAILS_SG(TRANSACTION_ID,
PERSON_ID,

EMPLOYEE_NUMBER,

PERIOD_START_DATE,

PERIOD_END_DATE,

date_worked,
created_by,

created_date,

approval_status,
request_id
)
VALUES (P_TRANSACTION_ID1,
P_PERSON_ID1,
P_EMPLOYEE_NUMBER1,
P_PERIOD_START1,
P_PERIOD_END1,
P_DATE_WORKED1,
P_USER_ID1,
sysdate,
P_STATUS1,
P_CONC_REQUEST_ID
);

IF P_REC_TYPE1 = 'SOAR' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET O_TIME_IN = P_IN_time1,
O_TIME_OUT =P_OUT_time1,
O_HOURS_TYPE = P_HOURS_TYPE1,
O_1_5_RATE_HRS =
DECODE(P_USER_TYPE1,'PAYROLL',P_HOURS1,NULL)
WHERE person_id = P_PERSON_ID1
AND date_worked = P_DATE_WORKED1
AND created_by = P_USER_ID1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID IN (SELECT
MIN(TRANSACTION_ID)
FROM
XXHXT_OVERTIME_DETAILS_SG
WHERE PERSON_ID =
P_PERSON_ID1
AND DATE_WORKED =
P_DATE_WORKED1
AND CREATED_BY =
P_USER_ID1
AND REQUEST_ID =
P_CONC_REQUEST_ID
AND O_TIME_IN IS NULL)

AND O_TIME_IN IS NULL;

END IF;

IF P_REC_TYPE1 = 'SOOO' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'OFF',
O_TIME_IN = P_IN_time1,
O_TIME_OUT =P_OUT_time1,
O_HOURS_TYPE = P_HOURS_TYPE1,
O_1_5_RATE_HRS =
DECODE(P_USER_TYPE1,'PAYROLL',P_HOURS1,NULL)
WHERE person_id = P_PERSON_ID1
AND date_worked = P_DATE_WORKED1
AND created_by = P_USER_ID1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND O_TIME_IN IS NULL;
END IF;

IF P_REC_TYPE1 = 'SORP' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'REST',
O_TIME_IN = P_IN_time1,
O_TIME_OUT = P_OUT_time1,
O_HOURS_TYPE =P_HOURS_TYPE1
WHERE person_id = P_PERSON_ID1

AND date_worked = P_DATE_WORKED1

AND created_by = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND O_TIME_IN IS NULL
;
END IF;

IF P_REC_TYPE1 = 'BRK' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET B_TIME_IN = P_IN_TIME1,
B_TIME_OUT = P_OUT_TIME1,
B_BREAK_HRS = P_HOURS1
WHERE person_id = P_PERSON_ID1

AND date_worked = P_DATE_WORKED1

AND created_by = P_USER_ID1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID IN (SELECT
MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE PERSON_ID = P_PERSON_ID1
AND DATE_WORKED =
P_DATE_WORKED1
AND CREATED_BY = P_USER_ID1
AND REQUEST_ID =
P_CONC_REQUEST_ID
AND
B_TIME_IN IS NULL)
AND B_TIME_IN IS NULL
;
END IF;

IF P_REC_TYPE1 = 'SRH' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = P_IN_TIME1,
R_TIME_OUT = P_OUT_TIME1

WHERE person_id = P_PERSON_ID1

AND date_worked = P_DATE_WORKED1

AND created_by = P_USER_ID1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND R_TIME_IN IS NULL
;
END IF;

IF P_REC_TYPE1 = 'AL' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'AL',
O_HOURS_TYPE = P_HOURS_TYPE1

WHERE person_id = P_PERSON_ID1

AND created_by = P_USER_ID1
AND date_worked = P_DATE_WORKED1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND R_TIME_IN IS NULL;
/*
UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_OUT = 'AL',
O_HOURS_TYPE = P_HOURS_TYPE1

WHERE person_id = P_PERSON_ID1

AND created_by = P_USER_ID1
AND date_worked = P_DATE_WORKED1
AND REQUEST_ID = P_CONC_REQUEST_ID

AND R_TIME_IN IS NOT NULL

;*/
END IF;

IF P_REC_TYPE1 = 'SL' OR P_REC_TYPE1 = 'NPL' OR P_REC_TYPE1 = 'OPL' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = P_REC_TYPE1,
O_HOURS_TYPE = P_HOURS_TYPE1

WHERE person_id = P_PERSON_ID1

AND created_by = P_USER_ID1
AND date_worked = P_DATE_WORKED1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND R_TIME_IN IS NULL;
END IF;
IF P_REC_TYPE1 = 'SHL' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'RL',
O_HOURS_TYPE = P_HOURS_TYPE1

WHERE person_id = P_PERSON_ID1

AND created_by = P_USER_ID1
AND date_worked = P_DATE_WORKED1
AND REQUEST_ID = P_CONC_REQUEST_ID
AND R_TIME_IN IS NULL;
END IF;

END INSERT_REC_OT;

PROCEDURE SUMMARY_DETAIL_REPORT(
L_PERSON_ID NUMBER,
L_DATE_WORKED DATE,
l_employee_number VARCHAR2,
l_IN_time VARCHAR2,
l_OUT_time VARCHAR2,
l_element_code VARCHAR2,
l_hours NUMBER,
l_user_type VARCHAR2,
l_approval_status VARCHAR2,
l_in_time_number NUMBER,
l_out_time_number NUMBER)
AS

v_emp_type VARCHAR2 (50);

v_day_rate NUMBER;
v_in_time_number NUMBER;
v_out_time_number NUMBER;
v_min_in_time1 NUMBER;
v_max_out_time1 NUMBER;
v_max_in_time1 NUMBER;
v_min_out_time1 NUMBER;
v_total_hrs NUMBER;
v_exist VARCHAR2(1);
CURSOR c_annual_leave(C_PERSON_ID NUMBER,C_DATE_WORKED DATE)
IS
SELECT DISTINCT 'Y'
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = C_PERSON_ID
AND date_worked = C_DATE_WORKED
AND CREATED_BY = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND R_TIME_IN IS NOT NULL;

CURSOR c_ot_ar(C_PERSON_ID NUMBER, C_DATE_WORKED DATE)

IS
SELECT DISTINCT 'Y'
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = C_PERSON_ID
AND date_worked = C_DATE_WORKED
AND CREATED_BY = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND O_TIME_IN IS NOT NULL
AND R_TIME_IN IS NOT NULL
;

CURSOR c_ot_off(C_PERSON_ID NUMBER, C_DATE_WORKED DATE)

IS
SELECT DISTINCT 'Y'
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = C_PERSON_ID
AND date_worked = C_DATE_WORKED
AND CREATED_BY = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND O_TIME_IN IS NOT NULL;

CURSOR c_ot_rp(C_PERSON_ID NUMBER, C_DATE_WORKED DATE)

IS
SELECT DISTINCT 'Y'
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = C_PERSON_ID
AND date_worked = C_DATE_WORKED
AND CREATED_BY = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND O_TIME_IN IS NOT NULL;

CURSOR c_ot_bk(C_PERSON_ID NUMBER, C_DATE_WORKED DATE)

IS
SELECT DISTINCT 'Y'
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = C_PERSON_ID
AND date_worked = C_DATE_WORKED
AND CREATED_BY = P_USER_ID
AND REQUEST_ID = P_CONC_REQUEST_ID
AND B_TIME_IN IS NOT NULL
;

CURSOR c_time_inout(l_person_id number,l_date_worked date)

IS
SELECT
(FLOOR ((standard_start / 100))+ (MOD (standard_start, 100) / 60)) * 100 hrs_start,
(FLOOR ((standard_stop / 100))+ (MOD (standard_stop, 100) / 60)) *100 hrs_stop
FROM hxt.hxt_rotation_plans hrp,
hxt.hxt_add_assign_info_f haai,
per_all_assignments_f paa,
hxt.hxt_rotation_schedules hrs,
hxt.hxt_weekly_work_schedules hwws,
hxt.hxt_work_shifts hws,
hxt.hxt_shifts hs
WHERE haai.rotation_plan = hrp.ID(+)
AND haai.assignment_id = paa.assignment_id
AND paa.person_id = l_person_id
AND paa.assignment_type = 'E'
AND sysdate BETWEEN haai.effective_start_date AND haai.effective_end_date
AND sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
AND hrp.ID = hrs.rtp_id(+)
AND hrs.tws_id = hwws.ID(+)
AND hwws.ID = hws.tws_id(+)
AND hws.sht_id = hs.ID(+)
AND ( hrs.start_date+hws.seq_no-1) = l_date_worked;

CURSOR c_shift_allow_offRest(l_person_id number,l_date_worked date,l_element_code

VARCHAR2)
IS
SELECT
MIN(IN_TIME),
MAX(OUT_TIME),
MAX(IN_TIME),
MIN(OUT_TIME),
SUM(HOURS)
FROM (
SELECT papf.person_id,
HDHWF.DATE_WORKED,
to_number(TO_CHAR (hdhwf.time_in, 'HH24MI')) in_Time,
to_number(TO_CHAR (hdhwf.time_OUT, 'HH24MI')) out_time,
hdhwf.hours,
(SELECT lookup_code from FND_LOOKUP_VALUES WHERE UPPER(meaning) =
UPPER(petf.element_name) and lookup_type = 'XXHXT_SG_SETUP_MAPPING') element_code
FROM hxt_det_hours_worked_f hdhwf,
hxt_timecards_f HTF,
pay_element_types_f petf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf
WHERE hdhwf.tim_id = HTF.id
AND hdhwf.element_type_id = petf.element_type_id
AND HTF.for_person_id = papf.person_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND hdhwf.effective_end_date = '31-DEC-4712'
AND PAPF.person_id = l_person_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND hdhwf.date_worked = l_date_worked
AND hdhwf.hours <> 0
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date)
WHERE ELEMENT_CODE = l_element_code
group by person_id,DATE_WORKED,element_code;

CURSOR c_chk_leave(l_person_id number,l_date_worked date)

IS
SELECT 'Y'
FROM hxt_det_hours_worked_f hdhwf,
hxt_timecards_f HTF,
pay_element_types_f petf,
per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf,
fnd_lookup_values flv
WHERE hdhwf.tim_id = HTF.id
AND hdhwf.element_type_id = petf.element_type_id
AND HTF.for_person_id = papf.person_id
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND hdhwf.effective_end_date = '31-DEC-4712'
AND PAPF.person_id = l_person_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
AND papf.person_id = paaf.person_id
AND hdhwf.date_worked = l_date_worked
AND hdhwf.hours <> 0
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date
AND paaf.effective_end_date
AND petf.element_name = flv.meaning
AND flv.lookup_type = 'XXHXT_SG_SETUP_MAPPING'
AND flv.lookup_code IN ('SSL','SNPL');

BEGIN
v_emp_type := NULL;
v_day_rate := 0;

v_exist := 'N';

IF l_element_code = 'SRH'
THEN

-- INSERT INTO XXHXT_ERROR_MSG (MESSAGE) VALUES ('ENTER1');

open
c_annual_leave(l_person_id, l_date_worked);
fetch c_annual_leave into
v_exist;
close c_annual_leave;

IF v_exist = 'Y' THEN

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,

l_person_id,

l_employee_number,

P_PERIOD_START,

P_PERIOD_END,

l_date_worked,

P_USER_ID,

l_approval_status,

l_IN_time,

l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup(l_element_code),
l_hours,

l_element_code,

l_user_type);

ELSE
-- INSERT INTO XXHXT_ERROR_MSG (MESSAGE) VALUES ('ENTER3');

UPDATE
XXHXT_OVERTIME_DETAILS_SG
SET
R_TIME_IN = l_IN_time,

R_TIME_OUT = l_OUT_time,

R_HOURS_TYPE = XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),

APPROVAL_STATUS = l_approval_status
WHERE
person_id = l_person_id

AND date_worked = l_date_worked

AND REQUEST_ID = P_CONC_REQUEST_ID

AND R_TIME_IN IS NULL

AND created_by = P_USER_ID

;
END IF;

IF l_user_type = 'EMPLOYEE' THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND employee_number = l_employee_number
AND REQUEST_ID = P_CONC_REQUEST_ID
AND created_by = p_user_id;
END IF;

END IF;

IF l_element_code = 'SOAR'
THEN

open c_ot_ar(l_person_id,l_date_worked);
fetch c_ot_ar into v_exist;
close c_ot_ar;

IF v_exist = 'Y' THEN

v_transaction_id :=
XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();
INSERT_REC_OT(v_transaction_id,
l_person_id,
l_employee_number,
P_PERIOD_START,
P_PERIOD_END,
l_date_worked,
P_USER_ID,
l_approval_status,
l_IN_time,
l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
l_hours,
l_element_code,
l_user_type
);
ELSE
UPDATE
XXHXT_OVERTIME_DETAILS_SG
SET O_TIME_IN = l_IN_time,
O_TIME_OUT =l_OUT_time,
O_HOURS_TYPE =
XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
O_1_5_RATE_HRS =
DECODE(l_user_type,'PAYROLL',l_hours,NULL),

APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id

AND date_worked =
l_date_worked
AND created_by =
P_USER_ID
AND REQUEST_ID =
P_CONC_REQUEST_ID
AND O_TIME_IN IS NULL;
END IF;

END IF;

IF l_element_code = 'SOOO'
THEN

v_exist := 'N';

open c_ot_off(l_person_id, l_date_worked);

fetch c_ot_off into v_exist;
close c_ot_off;

-- INSERT INTO XXHXT_ERROR_MSG (MESSAGE) VALUES (v_exist);

IF v_exist = 'Y' THEN

v_transaction_id :=
XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,
l_person_id,
l_employee_number,
P_PERIOD_START,
P_PERIOD_END,
l_date_worked,
P_USER_ID,
l_approval_status,
l_IN_time,
l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
l_hours,
l_element_code,
l_user_type
);
ELSE

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'OFF',
O_TIME_IN = l_IN_time,
O_TIME_OUT = l_OUT_time,
O_HOURS_TYPE =
XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
O_1_5_RATE_HRS =
DECODE(l_user_type,'PAYROLL',l_hours,NULL),

APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id

AND date_worked = l_date_worked

AND O_TIME_IN IS NULL
AND REQUEST_ID =
P_CONC_REQUEST_ID
AND created_by = P_USER_ID
;
END IF;

-- insert into xxper_errors (step,message) values

(1,l_person_id);
-- insert into xxper_errors (step,message) values
(2,l_date_worked);
-- insert into xxper_errors (step,message) values
(3,l_element_code);

open
c_shift_allow_offRest(l_person_id,l_date_worked,l_element_code);
fetch c_shift_allow_offRest into
v_min_in_time1,v_max_out_time1,v_max_in_time1,v_min_out_time1,v_total_hrs;
close c_shift_allow_offRest;

v_element_name := '';

FOR c_shift_allow_rec IN c_shift_allow

(l_person_id)
LOOP
-- IF ((c_shift_allow_rec.start_time =
v_min_in_time1 AND c_shift_allow_rec.stop_time = v_max_out_time1) OR
-- (c_shift_allow_rec.start_time =
v_max_in_time1 AND c_shift_allow_rec.stop_time = v_min_out_time1)) AND
( v_total_hrs >= 11 or v_total_hrs >= 8)
-- THEN
-- insert into xxper_errors
(step,message) values (4, v_total_hrs);
-- Commented by Ravi IF ((c_shift_allow_rec.start_time =
v_min_in_time1) OR (c_shift_allow_rec.start_time = v_max_in_time1)) AND
v_total_hrs >= c_shift_allow_rec.maxhours

/* Added by Ravi */
IF ((c_shift_allow_rec.start_time =
v_min_in_time1 and v_max_out_time1 <= c_shift_allow_rec.stop_time)
OR (c_shift_allow_rec.start_time =
v_max_in_time1 and v_max_in_time1 <= c_shift_allow_rec.stop_time)) AND v_total_hrs
>= c_shift_allow_rec.maxhours
THEN
v_element_name :=
c_shift_allow_rec.element_name;
EXIT;
END IF;
END LOOP;

-- insert into xxper_errors (step,message) values

(4, c_shift_allow_rec.element_name);
COMMIT;
v_default_value := '';

FOR c_element_name_rec IN c_element_name

(v_element_name)
LOOP
v_default_value :=
c_element_name_rec.DEFAULT_VALUE;
END LOOP;

IF v_default_value IS NOT NULL

THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE =
DECODE(l_user_type,'PAYROLL',v_default_value,NULL)
WHERE person_id = l_person_id

AND date_worked = l_date_worked

AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT
MIN(TRANSACTION_ID)
FROM
XXHXT_OVERTIME_DETAILS_SG
WHERE person_id =
l_person_id
AND date_worked =
l_date_worked
AND REQUEST_ID =
P_CONC_REQUEST_ID
)
AND SHIFT_ALLOWANCE IS NULL

AND created_by = P_USER_ID

;
END IF;

END IF;

IF l_element_code = 'SORP'
THEN

open c_ot_rp(l_person_id, l_date_worked);

fetch c_ot_rp into v_exist;
close c_ot_rp;

IF v_exist = 'Y' THEN

v_transaction_id :=
XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,
l_person_id,
l_employee_number,
P_PERIOD_START,
P_PERIOD_END,
l_date_worked,
P_USER_ID,
l_approval_status,
l_IN_time,
l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
l_hours,

l_element_code,
l_user_type
);
ELSE
UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'REST',
O_TIME_IN = l_IN_time,
O_TIME_OUT = l_OUT_time,
O_HOURS_TYPE =
XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),

APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id

AND date_worked = l_date_worked

AND O_TIME_IN IS NULL

AND REQUEST_ID = P_CONC_REQUEST_ID

AND created_by = P_USER_ID;

END IF;

open
c_shift_allow_offRest(l_person_id,l_date_worked,l_element_code);
fetch c_shift_allow_offRest into
v_min_in_time1,v_max_out_time1,v_max_in_time1,v_min_out_time1,v_total_hrs;
close c_shift_allow_offRest;
v_element_name := '';
FOR c_shift_allow_rec IN c_shift_allow
(l_person_id)
LOOP
-- IF ((c_shift_allow_rec.start_time =
v_min_in_time1 AND c_shift_allow_rec.stop_time = v_max_out_time1) OR
-- (c_shift_allow_rec.start_time =
v_max_in_time1 AND c_shift_allow_rec.stop_time = v_min_out_time1)) AND
( v_total_hrs >= 11 or v_total_hrs >= 8)
-- THEN
IF ((c_shift_allow_rec.start_time =
v_min_in_time1 ) OR (c_shift_allow_rec.start_time = v_max_in_time1 )) AND
v_total_hrs >= c_shift_allow_rec.maxhours
THEN
v_element_name :=
c_shift_allow_rec.element_name;
END IF;
END LOOP;

v_default_value := '';

FOR c_element_name_rec IN c_element_name

(v_element_name)
LOOP
v_default_value :=
c_element_name_rec.DEFAULT_VALUE;
END LOOP;

IF v_default_value IS NOT NULL

THEN
UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE =
DECODE(l_user_type,'PAYROLL',v_default_value,NULL)
WHERE person_id = l_person_id

AND date_worked = l_date_worked

AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT
MIN(TRANSACTION_ID)
FROM
XXHXT_OVERTIME_DETAILS_SG
WHERE person_id =
l_person_id
AND date_worked =
l_date_worked
AND REQUEST_ID =
P_CONC_REQUEST_ID
)
AND SHIFT_ALLOWANCE IS NULL

AND created_by = P_USER_ID

;
END IF;

END IF;
IF l_element_code = 'SPHO' or l_element_code = 'SHL' THEN
open
c_annual_leave(l_person_id, l_date_worked);
fetch c_annual_leave into
v_exist;
close c_annual_leave;

IF v_exist = 'Y' THEN

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,

l_person_id,

l_employee_number,

P_PERIOD_START,

P_PERIOD_END,

l_date_worked,

P_USER_ID,

l_approval_status,

l_IN_time,

l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup(l_element_code),
null,

l_element_code,

l_user_type);
ELSE

UPDATE
XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN =
decode( l_element_code,'SPHO','PH','SHL','RL'),

APPROVAL_STATUS = l_approval_status
WHERE person_id =
l_person_id
AND
date_worked = l_date_worked
AND
R_TIME_IN IS NULL
AND
REQUEST_ID = P_CONC_REQUEST_ID
AND
created_by = P_USER_ID
;
END IF;

END IF;

IF l_element_code = 'SSL' OR l_element_code = 'SNPL' OR l_element_code =

'SOPL'
THEN

open
c_annual_leave(l_person_id, l_date_worked);
fetch c_annual_leave into
v_exist;
close c_annual_leave;

IF v_exist = 'Y' THEN

BEGIN
SELECT
DECODE(l_element_code,'SSL','SL','SNPL','NPL','SOPL','OPL')
INTO
v_leave_code
FROM
DUAL;
EXCEPTION
WHEN
OTHERS THEN

v_leave_code := 'Incorrect Leave Code';

END;

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,

l_person_id,

l_employee_number,

P_PERIOD_START,

P_PERIOD_END,

l_date_worked,

P_USER_ID,

l_approval_status,

l_IN_time,

l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup(l_element_code),
null,
v_leave_code,

l_user_type);

ELSE
UPDATE
XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN =
DECODE(l_element_code,'SSL','SL','SNPL','NPL','SOPL','OPL'),

APPROVAL_STATUS = l_approval_status
WHERE person_id =
l_person_id
AND
date_worked = l_date_worked
AND
R_TIME_IN IS NULL
AND
REQUEST_ID = P_CONC_REQUEST_ID
AND
created_by = P_USER_ID
;
END IF;

END IF;

IF l_element_code = 'SBH'
THEN
open c_ot_bk(l_person_id, l_date_worked);
fetch c_ot_bk into v_exist;
close c_ot_bk;

IF v_exist = 'Y' THEN

v_transaction_id :=
XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

-- INSERT INTO XXPER_ERRORS

(STEP,MESSAGE) VALUES (1,'ENTERING');

INSERT_REC_OT(v_transaction_id,
l_person_id,
l_employee_number,
P_PERIOD_START,
P_PERIOD_END,
l_date_worked,
P_USER_ID,
l_approval_status,
l_IN_time,
l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup( l_element_code),
l_hours,
'BRK',
l_user_type
);
ELSE

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET B_TIME_IN = l_IN_time,
B_TIME_OUT = l_OUT_time,
B_BREAK_HRS = l_hours,

APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id

AND date_worked = l_date_worked

AND B_TIME_IN IS NULL
AND REQUEST_ID = P_CONC_REQUEST_ID
AND created_by = P_USER_ID

;
END IF;
END IF;

IF l_element_code = 'SDR'
THEN

FOR c_shift_rec IN c_shift_emp (l_employee_number)

LOOP
v_emp_type := c_shift_rec.segment5;
END LOOP;

BEGIN
SELECT 'PH'
INTO v_pub_holiday
FROM HXT_HOLIDAY_CALENDARS HHC, HXT_HOLIDAY_DAYS_VL
HHDV
WHERE HHC.ID = HHDV.HCL_ID
AND HHC.NAME = DECODE(v_emp_type,'SNG A','SG 12
Hrs Shift Holiday Calendar','SNG C','SG 12 Hrs Shift Holiday Calendar','SG 8 Hrs
Shift Holiday Calendar')
AND TRUNC (SYSDATE) BETWEEN
HHC.EFFECTIVE_START_DATE
AND
HHC.EFFECTIVE_END_DATE
AND HHDV.HOLIDAY_DATE = l_date_worked;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
v_pub_holiday := 'REST';
WHEN OTHERS
THEN
v_pub_holiday := 'REST';
END;

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = v_pub_holiday,
D_TIME_IN = l_IN_TIME,
D_TIME_OUT = l_OUT_time,
O_2_0_RATE_HRS = DECODE(l_user_type,'PAYROLL',l_hours,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MAX(TRANSACTION_ID)
FROM
XXHXT_OVERTIME_DETAILS_SG
WHERE person_id =
l_person_id
AND REQUEST_ID =
P_CONC_REQUEST_ID
AND date_worked =
l_date_worked)
AND created_by = P_USER_ID
;
END IF;

IF l_element_code = 'SPH'
THEN
UPDATE XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN = 'PH',
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND created_by = P_USER_ID
AND R_TIME_IN IS NULL
;
END IF;

IF l_element_code = 'S10A'
THEN

v_exist := '';
open c_chk_leave(l_person_id,l_date_worked);
fetch c_chk_leave into v_exist;
close c_chk_leave;

IF v_exist IS NULL THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE = DECODE(l_user_type,'PAYROLL',10,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID)

AND created_by = P_USER_ID

;
END IF;
END IF;

IF l_element_code = 'SSA'
THEN

IF l_in_time_number is null and l_out_time_number is null THEN

open c_time_inout(l_person_id,l_date_worked);
fetch c_time_inout into
v_in_time_number,v_out_time_number;
close c_time_inout;
-- insert into xxper_errors (step,message) values
(1,to_char(l_date_worked));
-- insert into xxper_errors (step,message) values
(2,v_in_time_number);
-- insert into xxper_errors (step,message) values
(3,v_out_time_number);
-- commit;
ELSE
v_in_time_number := l_in_time_number;
v_out_time_number := l_out_time_number;

END IF;

v_element_name := '';

FOR c_shift_allow_rec IN c_shift_allow (l_person_id)

LOOP

IF c_shift_allow_rec.name <> 'SG 8 Hrs Shift1 08:30-12:30(Mon) ,05-

14(Tue-Fri),05-09(Sat) Rotation Plan'
THEN
IF c_shift_allow_rec.start_time = v_in_time_number AND
c_shift_allow_rec.stop_time = v_out_time_number
THEN
v_element_name := c_shift_allow_rec.element_name;

END IF;

IF v_in_time_number = c_shift_allow_rec.start_time AND

v_out_time_number = c_shift_allow_rec.stop_time
THEN
v_element_name := c_shift_allow_rec.element_name;

END IF;
ELSE
IF REPLACE(TO_CHAR(l_date_worked,'DAY'),CHR(32),'') <> 'MONDAY'
THEN
v_element_name := c_shift_allow_rec.element_name;

ELSE
v_element_name := '';
END IF;
END IF;

END LOOP;

v_default_value := '';

FOR c_element_name_rec IN c_element_name (v_element_name)

LOOP
v_default_value := c_element_name_rec.DEFAULT_VALUE;
END LOOP;

IF v_default_value IS NOT NULL

THEN
UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE =
DECODE(l_user_type,'PAYROLL',v_default_value,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID)
AND created_by = P_USER_ID
;
END IF;

END IF;

IF l_element_code = 'S22A'
THEN

v_exist := '';
open c_chk_leave(l_person_id,l_date_worked);
fetch c_chk_leave into v_exist;
close c_chk_leave;

IF v_exist IS NULL THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE = DECODE(l_user_type,'PAYROLL',22,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID)
AND created_by = P_USER_ID
;
END IF;
END IF;

IF l_element_code = 'S8A'
THEN

v_exist := '';
open c_chk_leave(l_person_id,l_date_worked);
fetch c_chk_leave into v_exist;
close c_chk_leave;

IF v_exist IS NULL THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE = DECODE(l_user_type,'PAYROLL',8,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID)

AND created_by = P_USER_ID

;
END IF;
END IF;

IF l_element_code = 'S15A'
THEN

v_exist := '';
open c_chk_leave(l_person_id,l_date_worked);
fetch c_chk_leave into v_exist;
close c_chk_leave;

IF v_exist IS NULL THEN

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET SHIFT_ALLOWANCE = DECODE(l_user_type,'PAYROLL',15,NULL),
APPROVAL_STATUS = l_approval_status
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID
AND TRANSACTION_ID = (SELECT MIN(TRANSACTION_ID)
FROM XXHXT_OVERTIME_DETAILS_SG
WHERE person_id = l_person_id
AND date_worked = l_date_worked
AND REQUEST_ID = P_CONC_REQUEST_ID)

AND created_by = P_USER_ID

;
END IF;
END IF;

IF l_element_code = 'SA5' or l_element_code = 'SA6' or l_element_code =

'SA2' or l_element_code = 'SA3'
THEN
open
c_annual_leave(l_person_id, l_date_worked);
fetch c_annual_leave into
v_exist;
close c_annual_leave;

IF v_exist = 'Y' THEN

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT_REC_OT(v_transaction_id,

l_person_id,

l_employee_number,
P_PERIOD_START,

P_PERIOD_END,

l_date_worked,

P_USER_ID,

l_approval_status,

l_IN_time,

l_OUT_time,

XXPER_FUNCTIONS.get_ele_name_lookup(l_element_code),
null,
'AL',

l_user_type);

ELSE

UPDATE
XXHXT_OVERTIME_DETAILS_SG
SET R_TIME_IN =
'AL',

O_HOURS_TYPE = XXPER_FUNCTIONS.get_ele_name_lookup(l_element_code),

APPROVAL_STATUS = l_approval_status
WHERE person_id =
l_person_id
AND
created_by = p_user_id
AND
REQUEST_ID = P_CONC_REQUEST_ID
AND
date_worked = l_date_worked;
END IF;

END IF;

END SUMMARY_DETAIL_REPORT;

BEGIN

DELETE FROM XXHXT_OVERTIME_DETAILS_SG

WHERE created_by = P_USER_ID;
COMMIT;

SELECT responsibility_name
INTO v_responsibility_name
FROM FND_RESPONSIBILITY_TL
WHERE responsibility_id = P_RESPONSIBILITY_ID;
IF UPPER (v_responsibility_name) = UPPER('Xilinx Singapore OTL Administrator')
THEN
FOR c_shift_rec IN c_shift_emp (P_EMPNO)
LOOP

v_date_worked := P_PERIOD_START;

LOOP

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT INTO XXHXT_OVERTIME_DETAILS_SG (TRANSACTION_ID,

PERSON_ID,
EMPLOYEE_NUMBER,
PERIOD_START_DATE,
PERIOD_END_DATE,
date_worked,
created_by,
created_date,
approval_status,
REQUEST_ID
)
VALUES (v_transaction_id,
c_shift_rec.person_id,
c_shift_rec.employee_number,
P_PERIOD_START,
P_PERIOD_END,
v_date_worked,
p_user_id,
sysdate,
'APPROVED',
P_CONC_REQUEST_ID
);

COMMIT;

EXIT WHEN TO_CHAR(v_date_worked,'YYYY/MM/DD') =

TO_CHAR(P_PERIOD_END,'YYYY/MM/DD');

v_date_worked := v_date_worked + 1 ;

END LOOP;

END LOOP;

FOR c_timecard_rec IN c_timecard(P_EMPNO)

LOOP

SUMMARY_DETAIL_REPORT( c_timecard_rec.person_id,
c_timecard_rec.date_worked,
c_timecard_rec.employee_number,
c_timecard_rec.in_time,
c_timecard_rec.out_time,
c_timecard_rec.element_code,
c_timecard_rec.hours,
c_timecard_rec.user_type,
c_timecard_rec.approval_status,
c_timecard_rec.in_time_number,
c_timecard_rec.out_time_number);

END LOOP;

FOR c_sum_hours_rec IN c_sum_hours(P_EMPNO) LOOP

FOR c_shift_rec IN c_shift_emp(c_sum_hours_rec.employee_number)

LOOP
v_emp_type := c_shift_rec.segment5;
END LOOP;

IF v_emp_type = 'SNG A' or v_emp_type = 'SNG C' THEN

IF c_sum_hours_rec.total_hours <= 5.5 then

v_day_rate := 1;
ELSE
v_day_rate := 2;
END IF;

ELSE
IF c_sum_hours_rec.total_hours <= 4 then
v_day_rate := 1;
ELSE
v_day_rate := 2;
END IF;

END IF;

UPDATE XXHXT_OVERTIME_DETAILS_SG
SET O_DAY_RATE = v_day_rate
WHERE person_id =
c_sum_hours_rec.person_id
AND date_worked =
c_sum_hours_rec.date_worked
AND O_DAY_RATE IS NULL
AND ROWNUM < 2

AND REQUEST_ID = P_CONC_REQUEST_ID

AND created_by = P_USER_ID;

END LOOP;

ELSE
FOR c_shift_sup_rec IN c_shift_sup
LOOP

v_date_worked := P_PERIOD_START;

LOOP

v_transaction_id := XXHXT_OVERTIME_REPORT_SEQ.NEXTVAL ();

INSERT INTO XXHXT_OVERTIME_DETAILS_SG (TRANSACTION_ID,
PERSON_ID,
EMPLOYEE_NUMBER,
PERIOD_START_DATE,
PERIOD_END_DATE,
date_worked,
created_by,
created_date,
request_id
)
VALUES (v_transaction_id,
c_shift_sup_rec.person_id,
c_shift_sup_rec.employee_number,
P_PERIOD_START,
P_PERIOD_END,
v_date_worked,
p_user_id,
sysdate,
P_CONC_REQUEST_ID
);

COMMIT;

EXIT WHEN TO_CHAR(v_date_worked,'YYYY/MM/DD') =

TO_CHAR(P_PERIOD_END,'YYYY/MM/DD');

v_date_worked := v_date_worked + 1 ;

END LOOP;

END LOOP;

FOR c_shift_sup_rec IN c_shift_sup

LOOP

FOR c_timecard_shift_ldr_rec IN
c_timecard_shift_ldr(c_shift_sup_rec.employee_number)
LOOP

/* Formatted on 10/25/2013 6:04:03 PM (QP5

v5.163.1008.3004) */

SUMMARY_DETAIL_REPORT( c_timecard_shift_ldr_rec.person_id,

c_timecard_shift_ldr_rec.date_worked,

c_timecard_shift_ldr_rec.employee_number,

c_timecard_shift_ldr_rec.in_time,

c_timecard_shift_ldr_rec.out_time,

c_timecard_shift_ldr_rec.element_code,

c_timecard_shift_ldr_rec.hours,

c_timecard_shift_ldr_rec.user_type,
c_timecard_shift_ldr_rec.approval_status,

c_timecard_shift_ldr_rec.in_time_number,

c_timecard_shift_ldr_rec.out_time_number);

END LOOP;

END LOOP;

END IF;

/* Clear blank records for the report */

DELETE FROM XXHXT_OVERTIME_DETAILS_SG WHERE R_TIME_IN IS NULL AND O_TIME_IN IS NULL
AND D_TIME_IN IS NULL AND B_BREAK_HRS = 0 AND REQUEST_ID = P_CONC_REQUEST_ID;

FOR c_upd_ph_rec IN c_upd_ph(P_CONC_REQUEST_ID) LOOP

BEGIN
SELECT HD.HOLIDAY_DATE
INTO l_Holiday_date
FROM HXT_HOLIDAY_CALENDARS HC, HXT_HOLIDAY_DAYS_VL HD
WHERE HC.ID = HD.HCL_ID
AND HD.HOLIDAY_DATE = c_upd_ph_rec.date_worked
AND HC.name IN (SELECT DISTINCT DECODE(hsck.segment5,'SNG A','SG
12Hrs Shift Holiday Calendar','SNG C','SG 12Hrs Shift Holiday Calendar','SG 8Hrs
Shift Holiday Calendar')
FROM per_all_people_f papf,
per_all_assignments_f paaf,
pay_payrolls_f ppf,
HR_SOFT_CODING_KEYFLEX hsck
WHERE PAPF.PERSON_ID = c_upd_ph_rec.person_id
AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND papf.person_id = paaf.person_id
AND TRUNC (SYSDATE) BETWEEN paaf.effective_start_date AND
paaf.effective_end_date
AND paaf.payroll_id = ppf.payroll_id
AND ppf.payroll_name = 'Singapore Monthly'
AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
AND hsck.segment5 IN ('SNG A', 'SNG B', 'SNG C', 'SNG D'));

UPDATE XXHXT_OVERTIME_DETAILS_SG SET R_TIME_IN = 'PH' WHERE

DATE_WORKED = c_upd_ph_rec.DATE_WORKED AND REQUEST_ID = P_CONC_REQUEST_ID AND
PERSON_ID = c_upd_ph_rec.person_id AND R_TIME_IN IS NULL;

UPDATE XXHXT_OVERTIME_DETAILS_SG SET R_TIME_OUT = 'PH' WHERE

DATE_WORKED = c_upd_ph_rec.DATE_WORKED AND REQUEST_ID = P_CONC_REQUEST_ID AND
PERSON_ID = c_upd_ph_rec.person_id AND (R_TIME_IN = 'REST' OR R_TIME_IN = 'OFF');

EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;
END LOOP;
COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No data found');
END OVERTIME_SUM_DETAIL_RPT;
END XXHXT_TIMECARD_PKG;
/