CREATE OR REPLACE PROCEDURE APPS.IFL_C_OTL_UPLOAD_TIMECARD_API (ERRBUF OUT VARCHAR2,RETCODE OUT VARCHAR2 ,
P_DATE_FROM VARCHAR2, P_DATE_TO VARCHAR2 )
IS
--v_work_start_date date := fnd_conc_date.string_to_date (p_work_start_date);
cursor c_nt is
/*select inn.person_id person_id , inn.start_time strat_time, outt.out_time out_time , inn.typ in_type , outt.typ out_type , inn.in_id , outt.out_id,inn.work_start_date
from
(
select to_number(ta.person_id) person_id, (ta.TIME_IN_OUT) start_time , ta.TYPE_IN_OUT typ , ta.id in_id,ta.WORK_START_DATE
from IFL_ATTendance ta ,
hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
where 1=1
and ta.person_id = htgqc.CRITERIA_ID
and htgq.TK_GROUP_QUERY_ID = htgqc.TK_GROUP_QUERY_ID
and htg.TK_GROUP_ID = htgq.TK_GROUP_ID
-- and htg.TK_GROUP_ID = p_tkp
--and ta.WORK_START_DATE = v_work_start_date
and ta.TYPE_IN_OUT = '1'
--and ta.ELEMENT_ID = 481
and ta.PROCESS_STATUS is null
--group by ta.person_id , ta.TYPE_IN_OUT,ta.id
) inn ,
(
select to_number(ta.person_id) person_id, (ta.TIME_IN_OUT) out_time , ta.type_in_out typ , ta.id out_id,ta.WORK_START_DATE
from IFL_ATTendance ta ,
hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
where 1=1
and ta.person_id = htgqc.CRITERIA_ID
and htgq.TK_GROUP_QUERY_ID = htgqc.TK_GROUP_QUERY_ID
and htg.TK_GROUP_ID = htgq.TK_GROUP_ID
-- and htg.TK_GROUP_ID = p_tkp
--and ta.WORK_START_DATE = v_work_start_date
and ta.TYPE_IN_OUT = '2'
--and ta.ELEMENT_ID = 481
and ta.process_status is null
--group by ta.person_id , ta.TYPE_IN_OUT,ta.id
) outt
where inn.person_id = outt.person_id
and inn.work_start_date = outt.work_start_date
order by 8;*/
SELECT TR.EMPLOYEEID PERSON_ID,
TR.CLOCKIN START_TIME,
TR.CLOCKOUT OUT_TIME,
TR.ATTENDANCEDATE WORK_START_DATE
FROM TAD_REGISTER TR
WHERE TR.ATTENDANCEDATE BETWEEN FND_CONC_DATE.STRING_TO_DATE(P_DATE_FROM) AND FND_CONC_DATE.STRING_TO_DATE(P_DATE_TO)
---AND TR.CLOCKOUT-TR.CLOCKINCL
--AND TR.EMPLOYEEID =623
AND TR.OTL_STATUS IS NULL
and tr.ATTENDANCETYPE = 'P'
and tr.WORKEDHOURS >=4 ;
--and inn.person_id = p_person_id;
c_otl_appl_id CONSTANT NUMBER (3) := 809; -- This is the appl_id for OTL, do not change
-- Variable declarations
------------------------
-- declare the PL/SQL Table that will hold the complete timecard (all the BBs)
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
-- declare the PL/SQL Table that will hold all the attributes
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
-- declare the PL/SQL Table that will hold the messages returned by the API
l_tbl_messages hxc_self_service_time_deposit.message_table;
-- Will hold TC_ID, returned by the deposit process
l_new_timecard_id NUMBER;
-- Will hold TC ovn, returned by the deposit process
l_new_timecard_ovn NUMBER;
l_time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_user NUMBER := 1070; --(OTLEXCEPT2) Replace with your own IDs
l_responsibility NUMBER := 23128; ---23818; --(OTL Time and Expense) Replace with your own IDs
--l_person_id per_all_people_f.person_id%TYPE := p_person_id; --(OTLExcept2,) Replace with your own
i PLS_INTEGER;
l_message fnd_new_messages.message_text%TYPE;
-- l_start_date VARCHAR2 (30) := '2002/12/30'; --UPGQA11i
l_start_date VARCHAR2 (30) := '2016/01/01'; -- HRQA11i
BEGIN
--x_return_status := 0;
--
--fnd_global.apps_initialize (1110,23128,809); --- Global OTL Application Developer RESP_APPL_ID
fnd_global.apps_initialize (fnd_profile.value('USER_ID'),fnd_profile.value('RESP_ID'),fnd_profile.value('RESP_APPL_ID'));
MO_GLOBAL.INIT('HXC');
--OTL Timekeeper responsibility id 23819
--
-- hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
-- hr_utility.trace_on;
for c_nt_rec in c_nt loop
hxc_timestore_deposit.create_time_entry (
--p_measure=> 8,
--p_day => FND_DATE.CANONICAL_TO_DATE ('2010/03/01'),
p_start_time=> c_nt_rec.start_time, ----fnd_date.canonical_to_date (p_ti),--('2010-JAN-08 01:30'),
p_stop_time=> c_nt_rec.out_time , ----fnd_date.canonical_to_date (p_to),--('2010-JAN-08 20:30'),
p_resource_id=> c_nt_rec.person_id, -----p_person_id, -- Identifies a person on our DB, REPLACE WITH YOURIDs
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info,
p_time_building_block_id=> l_time_building_block_id
);
dbms_output.put_line ('tim entry: '||l_time_building_block_id || '---' ||'p_app_attributes : ');
--Normal Hours
hxc_timestore_deposit.create_attribute (
p_building_block_id=> l_time_building_block_id,
---------p_attribute_name=> 'Dummy Element Context', -- 'Normal Hours', --',----'Normal Regular Hours' ,--' 313
p_attribute_name=> 'Dummy Element Context',
p_attribute_value=> 'Normal Hours' , --- Normal Hours
p_attribute_id => 242,
p_app_attributes=> l_tbl_attributes_info
);
for i in l_tbl_attributes_info.first ..l_tbl_attributes_info.last loop
dbms_output.put_line ('ATTRIB : '||l_tbl_attributes_info(i).BLD_BLK_INFO_TYPE);
end loop;
--
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=> 'SAVE', ---'SUBMIT',
p_deposit_process=> 'OTL Deposit Process Mapping',
p_timecard_id=> l_new_timecard_id,
p_timecard_ovn=> l_new_timecard_ovn
);
----hr_utility.trace_off;
dbms_output.put_line ('depo process : '||l_new_timecard_id);
dbms_output.put_line ('depo process: '||l_new_timecard_ovn);
IF (l_tbl_messages.COUNT <> 0)
THEN -- messages have been returned
--x_return_status := 1;
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
);
DBMS_OUTPUT.put_line (l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
DBMS_OUTPUT.put_line (' ********** ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
DBMS_OUTPUT.put_line (' Normal Time ' );
DBMS_OUTPUT.put_line (' Start Time >>>>>>>: ' || c_nt_rec.start_time );
DBMS_OUTPUT.put_line (' Out Time >>>>>>>: ' || c_nt_rec.out_time );
DBMS_OUTPUT.put_line (' person_id >>>>>>>: ' || c_nt_rec.person_id);
DBMS_OUTPUT.put_line (' >>>>>>>>>>>> : ' || l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (' >>>>>>>>>>>> : ' || l_message);
DBMS_OUTPUT.put_line (' ********** END ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' ********** ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' Normal Time ' );
apps.fnd_file.put_line (apps.fnd_file.log,' Start Time >>>>>>>: ' || c_nt_rec.start_time );
apps.fnd_file.put_line (apps.fnd_file.log,' Out Time >>>>>>>: ' || c_nt_rec.out_time );
apps.fnd_file.put_line (apps.fnd_file.log,' person_id >>>>>>>: ' || c_nt_rec.person_id);
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>>>> : ' || l_tbl_messages (i).message_name);
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>>>> : ' || l_message);
i := l_tbl_messages.NEXT (i);
END LOOP;
ELSE
update TAD_REGISTER TR
set tR.OTL_status = 'Y'
where TR.EMPLOYEEid = c_nt_rec.person_id
AND TR.ATTENDANCEDATE = C_NT_REC.WORK_START_DATE ;
--- and ta.TYPE_IN_OUT in (c_nt_rec.in_type , c_nt_rec.out_type);
--and ta.WORK_START_DATE = v_work_start_date
-- and ta.id in (c_nt_rec.in_id, c_nt_rec.out_id);
dbms_output.put_line (' ********** NORMAL HOUR UPDATED : ' || '************');
dbms_output.put_line (' Normal Time ' );
dbms_output.put_line (' Start Time >>>>>>>: ' || c_nt_rec.staRt_time );
dbms_output.put_line (' Out Time >>>>>>>: ' || c_nt_rec.out_time );
dbms_output.put_line (' person_id >>>>>>>: ' || c_nt_rec.person_id);
dbms_output.put_line (' ********** : NORMAL HOUR ************');
apps.fnd_file.put_line (apps.fnd_file.output,' ********** UPDATED ifl_attendance : ' || '************');
apps.fnd_file.put_line (apps.fnd_file.output,' Normal Time ' );
apps.fnd_file.put_line (apps.fnd_file.output,' Start Time >>>>>>>: ' || c_nt_rec.staRt_time );
apps.fnd_file.put_line (apps.fnd_file.output,' Out Time >>>>>>>: ' || c_nt_rec.out_time );
apps.fnd_file.put_line (apps.fnd_file.output,' person_id >>>>>>>: ' || c_nt_rec.person_id);
apps.fnd_file.put_line (apps.fnd_file.output,' ********** : ' || '************');
commit;
END IF;
end loop;
EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
DBMS_OUTPUT.PUT_LINE ('HELLO NORMAL TIME');
-- DBMS_OUTPUT.put_line (l_tbl_messages (i).message_name);
--DBMS_OUTPUT.put_line (l_message);
DBMS_OUTPUT.PUT_LINE (' ********** EXCEPTION NORMAL TIME LOAD: ' || '************');
DBMS_OUTPUT.PUT_LINE (' >>>>>>>>>> : '|| SQLERRM );
DBMS_OUTPUT.PUT_LINE (' ********** END EXCEPTION NORMAL TIME LOAD: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' ********** EXCEPTION NORMAL TIME LOAD: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>> : '|| SQLERRM );
apps.fnd_file.put_line (apps.fnd_file.log,' ********** END EXCEPTION NORMAL TIME LOAD: ' || '************');
END IFL_C_OTL_UPLOAD_TIMECARD_API;
/
P_DATE_FROM VARCHAR2, P_DATE_TO VARCHAR2 )
IS
--v_work_start_date date := fnd_conc_date.string_to_date (p_work_start_date);
cursor c_nt is
/*select inn.person_id person_id , inn.start_time strat_time, outt.out_time out_time , inn.typ in_type , outt.typ out_type , inn.in_id , outt.out_id,inn.work_start_date
from
(
select to_number(ta.person_id) person_id, (ta.TIME_IN_OUT) start_time , ta.TYPE_IN_OUT typ , ta.id in_id,ta.WORK_START_DATE
from IFL_ATTendance ta ,
hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
where 1=1
and ta.person_id = htgqc.CRITERIA_ID
and htgq.TK_GROUP_QUERY_ID = htgqc.TK_GROUP_QUERY_ID
and htg.TK_GROUP_ID = htgq.TK_GROUP_ID
-- and htg.TK_GROUP_ID = p_tkp
--and ta.WORK_START_DATE = v_work_start_date
and ta.TYPE_IN_OUT = '1'
--and ta.ELEMENT_ID = 481
and ta.PROCESS_STATUS is null
--group by ta.person_id , ta.TYPE_IN_OUT,ta.id
) inn ,
(
select to_number(ta.person_id) person_id, (ta.TIME_IN_OUT) out_time , ta.type_in_out typ , ta.id out_id,ta.WORK_START_DATE
from IFL_ATTendance ta ,
hxc_tk_groups htg ,
hxc_tk_group_queries htgq ,
hxc_tk_group_query_criteria htgqc
where 1=1
and ta.person_id = htgqc.CRITERIA_ID
and htgq.TK_GROUP_QUERY_ID = htgqc.TK_GROUP_QUERY_ID
and htg.TK_GROUP_ID = htgq.TK_GROUP_ID
-- and htg.TK_GROUP_ID = p_tkp
--and ta.WORK_START_DATE = v_work_start_date
and ta.TYPE_IN_OUT = '2'
--and ta.ELEMENT_ID = 481
and ta.process_status is null
--group by ta.person_id , ta.TYPE_IN_OUT,ta.id
) outt
where inn.person_id = outt.person_id
and inn.work_start_date = outt.work_start_date
order by 8;*/
SELECT TR.EMPLOYEEID PERSON_ID,
TR.CLOCKIN START_TIME,
TR.CLOCKOUT OUT_TIME,
TR.ATTENDANCEDATE WORK_START_DATE
FROM TAD_REGISTER TR
WHERE TR.ATTENDANCEDATE BETWEEN FND_CONC_DATE.STRING_TO_DATE(P_DATE_FROM) AND FND_CONC_DATE.STRING_TO_DATE(P_DATE_TO)
---AND TR.CLOCKOUT-TR.CLOCKINCL
--AND TR.EMPLOYEEID =623
AND TR.OTL_STATUS IS NULL
and tr.ATTENDANCETYPE = 'P'
and tr.WORKEDHOURS >=4 ;
--and inn.person_id = p_person_id;
c_otl_appl_id CONSTANT NUMBER (3) := 809; -- This is the appl_id for OTL, do not change
-- Variable declarations
------------------------
-- declare the PL/SQL Table that will hold the complete timecard (all the BBs)
l_tbl_timecard_info hxc_self_service_time_deposit.timecard_info;
-- declare the PL/SQL Table that will hold all the attributes
l_tbl_attributes_info hxc_self_service_time_deposit.app_attributes_info;
-- declare the PL/SQL Table that will hold the messages returned by the API
l_tbl_messages hxc_self_service_time_deposit.message_table;
-- Will hold TC_ID, returned by the deposit process
l_new_timecard_id NUMBER;
-- Will hold TC ovn, returned by the deposit process
l_new_timecard_ovn NUMBER;
l_time_building_block_id hxc_time_building_blocks.time_building_block_id%TYPE;
l_user NUMBER := 1070; --(OTLEXCEPT2) Replace with your own IDs
l_responsibility NUMBER := 23128; ---23818; --(OTL Time and Expense) Replace with your own IDs
--l_person_id per_all_people_f.person_id%TYPE := p_person_id; --(OTLExcept2,) Replace with your own
i PLS_INTEGER;
l_message fnd_new_messages.message_text%TYPE;
-- l_start_date VARCHAR2 (30) := '2002/12/30'; --UPGQA11i
l_start_date VARCHAR2 (30) := '2016/01/01'; -- HRQA11i
BEGIN
--x_return_status := 0;
--
--fnd_global.apps_initialize (1110,23128,809); --- Global OTL Application Developer RESP_APPL_ID
fnd_global.apps_initialize (fnd_profile.value('USER_ID'),fnd_profile.value('RESP_ID'),fnd_profile.value('RESP_APPL_ID'));
MO_GLOBAL.INIT('HXC');
--OTL Timekeeper responsibility id 23819
--
-- hr_utility.set_trace_options('TRACE_DEST:DBMS_OUTPUT');
-- hr_utility.trace_on;
for c_nt_rec in c_nt loop
hxc_timestore_deposit.create_time_entry (
--p_measure=> 8,
--p_day => FND_DATE.CANONICAL_TO_DATE ('2010/03/01'),
p_start_time=> c_nt_rec.start_time, ----fnd_date.canonical_to_date (p_ti),--('2010-JAN-08 01:30'),
p_stop_time=> c_nt_rec.out_time , ----fnd_date.canonical_to_date (p_to),--('2010-JAN-08 20:30'),
p_resource_id=> c_nt_rec.person_id, -----p_person_id, -- Identifies a person on our DB, REPLACE WITH YOURIDs
p_app_blocks=> l_tbl_timecard_info,
p_app_attributes=> l_tbl_attributes_info,
p_time_building_block_id=> l_time_building_block_id
);
dbms_output.put_line ('tim entry: '||l_time_building_block_id || '---' ||'p_app_attributes : ');
--Normal Hours
hxc_timestore_deposit.create_attribute (
p_building_block_id=> l_time_building_block_id,
---------p_attribute_name=> 'Dummy Element Context', -- 'Normal Hours', --',----'Normal Regular Hours' ,--' 313
p_attribute_name=> 'Dummy Element Context',
p_attribute_value=> 'Normal Hours' , --- Normal Hours
p_attribute_id => 242,
p_app_attributes=> l_tbl_attributes_info
);
for i in l_tbl_attributes_info.first ..l_tbl_attributes_info.last loop
dbms_output.put_line ('ATTRIB : '||l_tbl_attributes_info(i).BLD_BLK_INFO_TYPE);
end loop;
--
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=> 'SAVE', ---'SUBMIT',
p_deposit_process=> 'OTL Deposit Process Mapping',
p_timecard_id=> l_new_timecard_id,
p_timecard_ovn=> l_new_timecard_ovn
);
----hr_utility.trace_off;
dbms_output.put_line ('depo process : '||l_new_timecard_id);
dbms_output.put_line ('depo process: '||l_new_timecard_ovn);
IF (l_tbl_messages.COUNT <> 0)
THEN -- messages have been returned
--x_return_status := 1;
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
);
DBMS_OUTPUT.put_line (l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (l_message);
DBMS_OUTPUT.put_line (' ********** ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
DBMS_OUTPUT.put_line (' Normal Time ' );
DBMS_OUTPUT.put_line (' Start Time >>>>>>>: ' || c_nt_rec.start_time );
DBMS_OUTPUT.put_line (' Out Time >>>>>>>: ' || c_nt_rec.out_time );
DBMS_OUTPUT.put_line (' person_id >>>>>>>: ' || c_nt_rec.person_id);
DBMS_OUTPUT.put_line (' >>>>>>>>>>>> : ' || l_tbl_messages (i).message_name);
DBMS_OUTPUT.put_line (' >>>>>>>>>>>> : ' || l_message);
DBMS_OUTPUT.put_line (' ********** END ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' ********** ERR MESSAGE NORMAL HOUR ERROR: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' Normal Time ' );
apps.fnd_file.put_line (apps.fnd_file.log,' Start Time >>>>>>>: ' || c_nt_rec.start_time );
apps.fnd_file.put_line (apps.fnd_file.log,' Out Time >>>>>>>: ' || c_nt_rec.out_time );
apps.fnd_file.put_line (apps.fnd_file.log,' person_id >>>>>>>: ' || c_nt_rec.person_id);
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>>>> : ' || l_tbl_messages (i).message_name);
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>>>> : ' || l_message);
i := l_tbl_messages.NEXT (i);
END LOOP;
ELSE
update TAD_REGISTER TR
set tR.OTL_status = 'Y'
where TR.EMPLOYEEid = c_nt_rec.person_id
AND TR.ATTENDANCEDATE = C_NT_REC.WORK_START_DATE ;
--- and ta.TYPE_IN_OUT in (c_nt_rec.in_type , c_nt_rec.out_type);
--and ta.WORK_START_DATE = v_work_start_date
-- and ta.id in (c_nt_rec.in_id, c_nt_rec.out_id);
dbms_output.put_line (' ********** NORMAL HOUR UPDATED : ' || '************');
dbms_output.put_line (' Normal Time ' );
dbms_output.put_line (' Start Time >>>>>>>: ' || c_nt_rec.staRt_time );
dbms_output.put_line (' Out Time >>>>>>>: ' || c_nt_rec.out_time );
dbms_output.put_line (' person_id >>>>>>>: ' || c_nt_rec.person_id);
dbms_output.put_line (' ********** : NORMAL HOUR ************');
apps.fnd_file.put_line (apps.fnd_file.output,' ********** UPDATED ifl_attendance : ' || '************');
apps.fnd_file.put_line (apps.fnd_file.output,' Normal Time ' );
apps.fnd_file.put_line (apps.fnd_file.output,' Start Time >>>>>>>: ' || c_nt_rec.staRt_time );
apps.fnd_file.put_line (apps.fnd_file.output,' Out Time >>>>>>>: ' || c_nt_rec.out_time );
apps.fnd_file.put_line (apps.fnd_file.output,' person_id >>>>>>>: ' || c_nt_rec.person_id);
apps.fnd_file.put_line (apps.fnd_file.output,' ********** : ' || '************');
commit;
END IF;
end loop;
EXCEPTION
-- WHEN NO_DATA_FOUND THEN
-- NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
DBMS_OUTPUT.PUT_LINE ('HELLO NORMAL TIME');
-- DBMS_OUTPUT.put_line (l_tbl_messages (i).message_name);
--DBMS_OUTPUT.put_line (l_message);
DBMS_OUTPUT.PUT_LINE (' ********** EXCEPTION NORMAL TIME LOAD: ' || '************');
DBMS_OUTPUT.PUT_LINE (' >>>>>>>>>> : '|| SQLERRM );
DBMS_OUTPUT.PUT_LINE (' ********** END EXCEPTION NORMAL TIME LOAD: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' ********** EXCEPTION NORMAL TIME LOAD: ' || '************');
apps.fnd_file.put_line (apps.fnd_file.log,' >>>>>>>>>> : '|| SQLERRM );
apps.fnd_file.put_line (apps.fnd_file.log,' ********** END EXCEPTION NORMAL TIME LOAD: ' || '************');
END IFL_C_OTL_UPLOAD_TIMECARD_API;
/
No comments:
Post a Comment