[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT creation_date
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_app_id
AND object_version_number = p_app_ovn;
SELECT
details.TIME_BUILDING_BLOCK_ID,
details.TYPE,
details.MEASURE,
details.UNIT_OF_MEASURE,
DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.start_time),
FND_DATE.DATE_TO_CANONICAL(days.start_time) ) CANONICAL_START_TIME,
DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.stop_time),
FND_DATE.DATE_TO_CANONICAL(days.stop_time) ) CANONICAL_STOP_TIME,
details.PARENT_BUILDING_BLOCK_ID,
NULL PARENT_IS_NEW,
details.SCOPE,
details.OBJECT_VERSION_NUMBER,
details.APPROVAL_STATUS,
details.RESOURCE_ID,
details.RESOURCE_TYPE,
details.APPROVAL_STYLE_ID,
FND_DATE.DATE_TO_CANONICAL(details.date_from) CANONICAL_DATE_FROM,
FND_DATE.DATE_TO_CANONICAL(details.date_to) CANONICAL_DATE_TO,
details.COMMENT_TEXT,
details.PARENT_BUILDING_BLOCK_OVN,
'N' NEW,
'N' CHANGED,
'N' PROCESS,
details.APPLICATION_SET_ID,
details.TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks days
,hxc_time_building_blocks details
WHERE days.parent_building_block_id = p_timecard_id
AND days.parent_building_block_ovn = p_timecard_ovn
AND days.scope = 'DAY'
AND TRUNC(days.start_time) BETWEEN TRUNC(p_start_time) AND TRUNC(p_stop_time)
AND days.date_to = hr_general.end_of_time
AND details.scope = 'DETAIL'
AND details.parent_building_block_id = days.time_building_block_id
AND details.parent_building_block_ovn = days.object_version_number
AND details.date_to = hr_general.end_of_time;
SELECT
details.TIME_BUILDING_BLOCK_ID,
details.TYPE,
details.MEASURE,
details.UNIT_OF_MEASURE,
DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.start_time),
FND_DATE.DATE_TO_CANONICAL(days.start_time) ) CANONICAL_START_TIME,
DECODE(details.type, 'RANGE', FND_DATE.DATE_TO_CANONICAL(details.stop_time),
FND_DATE.DATE_TO_CANONICAL(days.stop_time) ) CANONICAL_STOP_TIME,
details.PARENT_BUILDING_BLOCK_ID,
NULL PARENT_IS_NEW,
details.SCOPE,
details.OBJECT_VERSION_NUMBER,
details.APPROVAL_STATUS,
details.RESOURCE_ID,
details.RESOURCE_TYPE,
details.APPROVAL_STYLE_ID,
FND_DATE.DATE_TO_CANONICAL(details.date_from) CANONICAL_DATE_FROM,
FND_DATE.DATE_TO_CANONICAL(details.date_to) CANONICAL_DATE_TO,
details.COMMENT_TEXT,
details.PARENT_BUILDING_BLOCK_OVN,
'N' NEW,
'N' CHANGED,
'N' PROCESS,
details.APPLICATION_SET_ID,
details.TRANSLATION_DISPLAY_KEY
FROM hxc_time_building_blocks days
,hxc_time_building_blocks details
WHERE days.parent_building_block_id = p_timecard_id
AND days.parent_building_block_ovn = p_timecard_ovn
AND days.scope = 'DAY'
AND TRUNC(days.start_time) BETWEEN TRUNC(p_start_time) AND TRUNC(p_stop_time)
AND days.date_to = hr_general.end_of_time
AND details.scope = 'DETAIL'
AND details.parent_building_block_id = days.time_building_block_id
AND details.parent_building_block_ovn = days.object_version_number
AND details.date_to = hr_general.end_of_time
AND EXISTS ( SELECT 1
FROM hxc_application_set_comps_v hasc
WHERE hasc.application_set_id = details.application_set_id
AND hasc.time_recipient_id = p_time_recipient_id) ;
select a.time_attribute_id
,au.time_building_block_id building_block_id
,bbit.bld_blk_info_type
,a.attribute_category
,a.attribute1
,a.attribute2
,a.attribute3
,a.attribute4
,a.attribute5
,a.attribute6
,a.attribute7
,a.attribute8
,a.attribute9
,a.attribute10
,a.attribute11
,a.attribute12
,a.attribute13
,a.attribute14
,a.attribute15
,a.attribute16
,a.attribute17
,a.attribute18
,a.attribute19
,a.attribute20
,a.attribute21
,a.attribute22
,a.attribute23
,a.attribute24
,a.attribute25
,a.attribute26
,a.attribute27
,a.attribute28
,a.attribute29
,a.attribute30
,a.bld_blk_info_type_id
,a.object_version_number
,'N' NEW
,'N' CHANGED
,'N' PROCESS
from hxc_time_attributes a,
hxc_time_attribute_usages au,
hxc_bld_blk_info_types bbit
where au.time_building_block_id = p_detail_id
and au.time_building_block_ovn = p_detail_ovn
and au.time_attribute_id = a.time_attribute_id
and a.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
select max(time_building_block_ovn)
from hxc_ap_detail_links
where application_period_id = p_app_id
and time_building_block_id = p_block_id;
select tbb2.object_version_number,
tbb1.translation_display_key, -- 9747521
tbb2.translation_display_key -- 9747521
from hxc_time_building_blocks tbb1,
hxc_time_building_blocks tbb2
where tbb1.time_building_block_id = p_block1_id
and tbb1.time_building_block_id = tbb2.time_building_block_id
and tbb1.object_version_number = p_block1_ovn
and tbb2.object_version_number = p_block2_ovn
and tbb1.type = tbb2.type
and nvl(tbb1.measure,hr_api.g_number) = nvl(tbb2.measure,hr_api.g_number)
and nvl(tbb1.unit_of_measure,hr_api.g_varchar2) = nvl(tbb2.unit_of_measure,hr_api.g_varchar2)
and nvl(tbb1.start_time,hr_api.g_date) = nvl(tbb2.start_time,hr_api.g_date)
and nvl(tbb1.stop_time,hr_api.g_date) = nvl(tbb2.stop_time,hr_api.g_date)
and tbb1.approval_status = tbb2.approval_status
and nvl(tbb1.approval_style_id,hr_api.g_number) = nvl(tbb2.approval_style_id,hr_api.g_number)
and nvl(tbb1.comment_text,hr_api.g_varchar2) = nvl(tbb2.comment_text,hr_api.g_varchar2)
and nvl(tbb1.application_set_id,hr_api.g_number) = nvl(tbb1.application_set_id,hr_api.g_number)
and nvl(tbb1.data_set_id,hr_api.g_number) = nvl(tbb1.data_set_id,hr_api.g_number)
and nvl(tbb1.translation_display_key,hr_api.g_varchar2) <> nvl(tbb2.translation_display_key,hr_api.g_varchar2);
SELECT count(p_app_id)
FROM hxc_ap_detail_links apdetail
,hxc_time_building_blocks days
,hxc_time_building_blocks details
WHERE apdetail.application_period_id = p_app_id
AND days.parent_building_block_id = p_timecard_id
AND details.parent_building_block_id = days.time_building_block_id
AND details.time_building_block_id = apdetail.time_building_block_id
AND details.object_version_number = apdetail.time_building_block_ovn
AND details.date_to <> hr_general.end_of_time
and not exists(
select 1
from hxc_time_building_blocks details2
where details2.time_building_block_id = details.time_building_block_id
and details2.date_to = hr_general.end_of_time
);
SELECT details.time_building_block_id
,details.object_version_number
FROM hxc_time_building_blocks days
,hxc_time_building_blocks details
WHERE days.parent_building_block_id = p_timecard_id
AND details.parent_building_block_id = days.time_building_block_id
AND days.scope = 'DAY'
AND details.scope = 'DETAIL';
SELECT details.time_building_block_id
,details.object_version_number
FROM hxc_ap_detail_links apdetail
,hxc_time_building_blocks details
WHERE apdetail.application_period_id = p_app_period
AND apdetail.time_building_block_id = details.time_building_block_id
AND apdetail.time_building_block_ovn = details.object_version_number
AND details.date_to <> hr_general.end_of_time;
delete from hxc_ap_detail_links
where time_building_block_id = l_detail_id
and time_building_block_ovn = l_detail_ovn
and application_period_id = p_app_id;
delete from hxc_ap_detail_links
where time_building_block_id = l_detail_id
and time_building_block_ovn = l_detail_ovn
and application_period_id = p_app_id;
hxc_ap_detail_links_pkg.insert_summary_row(
p_app_id
,p_detail_blocks(l_block_index).block_id
,p_detail_blocks(l_block_index).block_ovn
);
hr_utility.trace('inserting id=' || p_detail_blocks(l_block_index).block_id
|| '|ovn=' || p_detail_blocks(l_block_index).block_ovn);
hxc_ap_detail_links_pkg.insert_summary_row(
p_app_id
,p_detail_blocks(l_block_index).block_id
,p_detail_blocks(l_block_index).block_ovn
);
SELECT u.employee_id
FROM FND_USER u
WHERE u.user_name = p_user_name;
t_attributes.delete;
select wlr.orig_system, wlr.orig_system_id
from wf_notifications wn, wf_process_activities pa, wf_item_activity_statuses wias, wf_local_roles wlr
where pa.activity_name in('TC_APR_NOTIFICATION', 'TC_APR_NOTIFICATION_ABS')
and pa.activity_item_type = itemType
and pa.instance_id = wias.process_activity
and wias.notification_id = wn.notification_id
and wias.item_key = itemKey
and wlr.name = wn.recipient_role
and wias.item_type = pa.activity_item_type;
select employee_id
from fnd_user
where user_id = userId;
PROCEDURE update_latest_details(p_app_bb_id in number)
is
l_bb_id number;
select time_building_block_id, time_building_block_ovn
from hxc_ap_detail_links
where application_period_id = p_app_bb_id;
select adl.application_period_id
from hxc_ap_detail_links adl,
hxc_app_period_summary haps
where adl.time_building_block_id = p_bb_id
and adl.time_building_block_ovn = p_bb_ovn
and adl.application_period_id <> p_app_bb_id
and adl.application_period_id = haps.application_period_id
and haps.approval_status <> 'APPROVED';
update hxc_latest_details
set last_update_date = sysdate
where time_building_block_id = l_bb_id
and object_version_number = l_bb_ovn;
-- Added the following UPDATEs to take care of the
-- tables for Upgraded Retrieval Process.
update hxc_pa_latest_details
set last_update_date = sysdate
where time_building_block_id = l_bb_id
and object_version_number = l_bb_ovn;
update hxc_pay_latest_details
set last_update_date = sysdate
where time_building_block_id = l_bb_id
and object_version_number = l_bb_ovn;
END update_latest_details;
PROCEDURE update_app_period(
itemtype IN varchar2,
itemkey IN varchar2,
actid IN number,
funcmode IN varchar2,
result IN OUT NOCOPY varchar2
)
IS
t_attributes hxc_time_attributes_api.timecard;
l_proc varchar2(100) := 'HXC_APPROVAL_WF_PKG.update_appl_period';
update hxc_app_period_summary
set application_period_ovn = l_appl_period_bb_ovn
,approval_status = l_approval_status
,approver_id = l_approver
,notification_status = 'FINISHED'
,creation_date = l_creation_date
where application_period_id = l_appl_period_bb_id;
update_latest_details(l_appl_period_bb_id);
hr_utility.trace('IN EXCEPTION IN update_appl_period');
wf_core.context('HCAPPRWF', 'hxc_approval_wf_pkg.update_appl_period',
itemtype, itemkey, to_char(actid), funcmode);
END update_app_period;
SELECT *
FROM hxc_app_period_summary
WHERE application_period_id = p_app_id;
SELECT timecard_id
FROM hxc_tc_ap_links
WHERE application_period_id = p_app_id;
SELECT time_building_block_id
,time_building_block_ovn
FROM hxc_ap_detail_links
WHERE application_period_id = p_app_id;
INSERT INTO hxc_app_period_summary
(APPLICATION_PERIOD_ID
,APPLICATION_PERIOD_OVN
,APPROVAL_STATUS
,TIME_RECIPIENT_ID
,TIME_CATEGORY_ID
,START_TIME
,STOP_TIME
,RESOURCE_ID
,RECIPIENT_SEQUENCE
,CATEGORY_SEQUENCE
,CREATION_DATE
,NOTIFICATION_STATUS
,APPROVER_ID
,APPROVAL_COMP_ID
)
VALUES
*/
hxc_app_period_summary_pkg.insert_summary_row
(l_new_appl_bb_id
,l_new_appl_bb_ovn
,'SUBMITTED'
,l_current_period.time_recipient_id
,l_current_period.time_category_id
,l_current_period.start_time
,l_current_period.stop_time
,l_current_period.resource_id
,l_current_period.recipient_sequence
,l_current_period.category_sequence
,l_creation_date
,'NOTIFIED'
,NULL
,l_current_period.approval_comp_id
,NULL
,NULL
,Null
,l_current_period.data_set_id
);
hxc_tc_ap_links_pkg.insert_summary_row(
l_timecard_id
,l_new_appl_bb_id);
INSERT INTO hxc_ap_detail_links
(application_period_id,
time_building_block_id,
time_building_block_ovn)
VALUES
*/
hxc_ap_detail_links_pkg.insert_summary_row
(l_new_appl_bb_id,
l_detail_id,
l_detail_ovn);
select hapc.recurring_period_id
from hxc_approval_period_comps hapc,
hxc_approval_period_sets haps
where haps.approval_period_set_id = p_app_periods
and hapc.approval_period_set_id = haps.approval_period_set_id
and hapc.time_recipient_id = p_time_recipient_id;
select hrp.start_date,
hrp.period_type,
hrp.duration_in_days
from hxc_recurring_periods hrp
where hrp.recurring_period_id = p_recurring_period_id;
l_valid_periods.delete;
SELECT details.time_building_block_id
FROM hxc_ap_detail_links details
,hxc_time_building_blocks blocks
WHERE details.application_period_id = p_app_id
AND details.time_building_block_id = blocks.time_building_block_id
AND details.time_building_block_ovn = blocks.object_version_number
AND blocks.date_to = hr_general.end_of_time;
select time_building_block_id, time_building_block_ovn
from hxc_ap_detail_links
where application_period_id = p_app_id
and time_building_block_id
not in ( select detail.time_building_block_id
from hxc_time_building_blocks detail,
hxc_time_building_blocks day
where detail.parent_building_block_id = day.time_building_block_id
and detail.parent_building_block_ovn = day.object_version_number
and day.scope = 'DAY'
and detail.scope = 'DETAIL'
and day.parent_building_block_id = p_timecard_id
);
select 1
from hxc_ap_detail_links
where application_period_id = p_app_id
and time_building_block_id = p_time_building_block_id;
hxc_ap_detail_links_pkg.insert_summary_row(
p_app_id
,p_removed_blocks(l_block_index).block_id
,p_removed_blocks(l_block_index).block_ovn
);
select 'Y'
into l_dummy
from wf_item_attribute_values
where item_type = p_item_type
and item_key = p_item_key
and name = p_name;
SELECT application_period_id
,application_period_ovn
,approval_status
,notification_status
,approval_comp_id
FROM hxc_app_period_summary
WHERE resource_id = p_resource_id
AND start_time = p_start_time
AND stop_time = p_stop_time
AND time_recipient_id = p_time_recipient_id
AND recipient_sequence = p_recipient_sequence
AND NVL(time_category_id, -1) = NVL(p_time_category_id, -1)
AND NVL(category_sequence, -1) = NVL(p_category_sequence, -1)
--following added may12 for hr supervisor
ORDER BY application_period_id asc;
SELECT 'Y'
FROM hxc_tc_ap_links
WHERE timecard_id = p_timecard_id
AND application_period_id = p_app_period_id;
select approver_id
from hxc_app_period_summary
where application_period_id = p_app_period_id;
select adl.time_building_block_id,
adl.time_building_block_ovn
from hxc_ap_detail_links adl
where adl.application_period_id = p_application_period_id;
select max(object_version_number)
from hxc_time_building_blocks
where time_building_block_id = p_bb_id;
select approval_item_key
from hxc_app_period_summary
where application_period_id = p_bb_id;
hxc_app_period_summary_api.app_period_delete(l_app_id_temp);
hxc_app_period_summary_api.app_period_delete(l_app_id);
select count(*) into l_number_of_details
from hxc_ap_detail_links
where application_period_id = l_app_id;
select count(*) into l_tc_details
from hxc_latest_details
where resource_id = p_resource_id
and trunc(start_time) >= trunc(p_start_time)
and trunc(stop_time) <= trunc(p_stop_time);
select nvl(recorded_hours,0) + nvl(absence_hours,0)
into l_total_hours
from hxc_timecard_summary
where resource_id = p_resource_id
and start_time = p_start_time
and trunc(stop_time) = trunc(p_stop_time);
select nvl(recorded_hours,0) + nvl(absence_hours,0)
into l_total_hours
from hxc_timecard_summary
where timecard_id = p_timecard_id;
select count(*)
into l_item_key_exists
from wf_items
where item_key = to_char(l_item_key)
and item_type = 'HXCEMP'
and rownum < 2;
deletes a rejected/approved timecard row, then a blank notification has to be sent to the approver.
If the row deletion is followed by a SAVE and SUBMIT operation then the foll. happens
changed( p_detail_blocks,p_detail_attributes,l_time_category_id,l_app_id, p_timecard_id) = TRUE
l_number_of_details <> 0
g_block_exist_for_ap <> 'Y',
BUT no_blocks(l_app_id, p_timecard_id) = l_number_of_details CONDITION FAILS.
In case of ELA approval, if a rejected timecard row is FULLY deleted, a blank FYI notification
has to be sent to the previous approver
Hence substituting the BLANK notification if the number of active detail RECORDS
for the l_app_id is 0 and set l_blank = 'Y'
*/
-- modified this query for bug 8920827
SELECT count(*)
INTO l_active_details
FROM hxc_ap_detail_links apdetail
,hxc_time_building_blocks detail
,hxc_latest_details latest
WHERE apdetail.application_period_id = l_app_id
AND apdetail.time_building_block_id = latest.time_building_block_id
AND latest.time_building_block_id = detail.time_building_block_id
AND latest.object_version_number = detail.object_version_number
AND detail.date_to = hr_general.end_of_time;
select count(*)
into l_item_key_exists
from wf_items
where item_key = to_char(l_item_key)
and item_type = 'HXCEMP'
and rownum < 2;
UPDATE hxc_app_period_summary
SET notification_status = 'NOT_NOTIFIED'
,approval_comp_id = p_approval_comp.approval_comp_id
WHERE application_period_id = l_app_id;
UPDATE hxc_app_period_summary
SET approval_comp_id = p_approval_comp.approval_comp_id
WHERE application_period_id = l_app_id;
hxc_ap_detail_links_pkg.insert_summary_row(l_app_id, l_tab_type_a(i).p_id, l_max_ovn);
hxc_tc_ap_links_pkg.insert_summary_row(
p_timecard_id => p_timecard_id
,p_application_period_id => l_app_id
);
SELECT day1.approval_style_id
FROM hxc_time_building_blocks day1,
hxc_time_building_blocks timecard
WHERE day1.resource_id = p_resource_id
AND day1.scope = 'DAY'
AND day1.start_time BETWEEN p_period_start_date AND p_period_end_date
AND day1.date_to = hr_general.end_of_time
AND timecard.time_building_block_id = day1.parent_building_block_id
AND timecard.object_version_number = day1.parent_building_block_ovn
AND timecard.scope = 'TIMECARD'
AND timecard.date_to = hr_general.end_of_time
ORDER BY day1.start_time desc;
select to_char(application_set_id)
from hxc_time_building_blocks
where time_building_block_id = p_timecard_id
and object_version_number = p_timecard_ovn;
select tc.resource_id, tc.start_time, tc.stop_time
from hxc_time_building_blocks tc
where tc.time_building_block_id = p_bld_blk_id
and tc.object_version_number = p_ovn;
select htr.name,
htr.application_id,
htr.application_period_function,
htr.time_recipient_id
from hxc_application_sets_v has,
hxc_application_set_comps_v hasc,
hxc_time_recipients htr
where to_char(has.application_set_id) = p_app_set
and hasc.application_set_id = has.application_set_id
and hasc.time_recipient_id = htr.time_recipient_id;
select day.time_building_block_id,
day.start_time,
day.stop_time,
day.object_version_number
from hxc_time_building_blocks day
where day.parent_building_block_id = p_tc_bld_blk_id
and day.parent_building_block_ovn = p_tc_ovn
and day.scope = 'DAY'
and day.object_version_number = (select max(day2.object_version_number)
from hxc_time_building_blocks day2
where day.time_building_block_id =
day2.time_building_block_id)
order by 2;
SELECT approval_comp_id
,object_version_number
,approval_mechanism
,approval_mechanism_id
,wf_item_type
,wf_name
,time_category_id
,approval_order
FROM hxc_approval_comps
WHERE approval_style_id = p_approval_style
AND time_recipient_id = p_time_recipient;
SELECT approval_comp_id
,object_version_number
,approval_mechanism
,approval_mechanism_id
,wf_item_type
,wf_name
,time_category_id
,approval_order
FROM hxc_approval_comps
WHERE parent_comp_id = p_comp_id
AND parent_comp_ovn = p_comp_ovn
ORDER BY time_category_id desc;
SELECT approval_comp_id
,object_version_number
,approval_mechanism
,approval_mechanism_id
,wf_item_type
,wf_name
,time_category_id
,approval_order
FROM hxc_approval_comps
WHERE parent_comp_id = p_comp_id
AND parent_comp_ovn = p_comp_ovn
AND ( time_category_id = 0
OR time_category_id IN ( SELECT detail_id
FROM hxc_archive_temp))
ORDER BY time_category_id desc;
IS SELECT /*+ LEADING(tmp)
INDEX(tcc HXC_HTCC2_FK1) */
DISTINCT time_category_id
FROM hxc_tmp_atts tmp,
hxc_time_category_comps2 tcc
WHERE tmp.bld_blk_info_type_id = tcc.bld_blk_info_type_id
AND tmp.attribute_category = tcc.attribute_category
AND tmp.attribute1 = tcc.attribute1
AND (tmp.attribute2 = tcc.attribute2
OR tcc.attribute2 = 'NULL')
AND (tmp.attribute3 = tcc.attribute3
OR tcc.attribute3 = 'NULL')
AND (tmp.attribute4 = tcc.attribute4
OR tcc.attribute4 = 'NULL')
AND (tmp.attribute5 = tcc.attribute5
OR tcc.attribute5 = 'NULL')
AND (tmp.attribute6 = tcc.attribute6
OR tcc.attribute6 = 'NULL')
AND (tmp.attribute7 = tcc.attribute7
OR tcc.attribute7 = 'NULL')
AND (tmp.attribute8 = tcc.attribute8
OR tcc.attribute8 = 'NULL')
AND (tmp.attribute9 = tcc.attribute9
OR tcc.attribute9 = 'NULL')
AND (tmp.attribute10 = tcc.attribute10
OR tcc.attribute10 = 'NULL')
AND (tmp.attribute11 = tcc.attribute11
OR tcc.attribute11 = 'NULL')
AND (tmp.attribute12 = tcc.attribute12
OR tcc.attribute12 = 'NULL')
AND (tmp.attribute13 = tcc.attribute13
OR tcc.attribute13 = 'NULL')
AND (tmp.attribute14 = tcc.attribute14
OR tcc.attribute14 = 'NULL')
AND (tmp.attribute15 = tcc.attribute15
OR tcc.attribute15 = 'NULL')
AND (tmp.attribute16 = tcc.attribute16
OR tcc.attribute16 = 'NULL')
AND (tmp.attribute17 = tcc.attribute17
OR tcc.attribute17 = 'NULL')
AND (tmp.attribute18 = tcc.attribute18
OR tcc.attribute18 = 'NULL')
AND (tmp.attribute19 = tcc.attribute19
OR tcc.attribute19 = 'NULL')
AND (tmp.attribute20 = tcc.attribute20
OR tcc.attribute20 = 'NULL')
AND (tmp.attribute21 = tcc.attribute21
OR tcc.attribute21 = 'NULL')
AND (tmp.attribute22 = tcc.attribute22
OR tcc.attribute22 = 'NULL')
AND (tmp.attribute23 = tcc.attribute23
OR tcc.attribute23 = 'NULL')
AND (tmp.attribute24 = tcc.attribute24
OR tcc.attribute24 = 'NULL')
AND (tmp.attribute25 = tcc.attribute25
OR tcc.attribute25 = 'NULL')
AND (tmp.attribute26 = tcc.attribute26
OR tcc.attribute26 = 'NULL')
AND (tmp.attribute27 = tcc.attribute27
OR tcc.attribute27 = 'NULL')
AND (tmp.attribute28 = tcc.attribute28
OR tcc.attribute28 = 'NULL')
AND (tmp.attribute29 = tcc.attribute29
OR tcc.attribute29 = 'NULL')
AND (tmp.attribute30 = tcc.attribute30
OR tcc.attribute30 = 'NULL');
select '1' from hxc_data_sets
where (((l_app_start_date between start_date and end_date) and (l_app_start_date
DELETE FROM hxc_archive_temp;
INSERT INTO hxc_archive_temp
(detail_id)
VALUES
(l_cat_tab(i));
SELECT aps.approval_status
FROM hxc_app_period_summary aps
WHERE aps.resource_id = p_resource_id
AND p_date BETWEEN aps.start_time AND aps.stop_time
AND aps.approval_status <> 'APPROVED'
AND (
(aps.recipient_sequence < p_recipient_sequence)
OR (p_category_sequence IS NOT NULL
AND aps.time_recipient_id = p_time_recipient_id
AND aps.recipient_sequence = p_recipient_sequence
AND aps.time_category_id IS NOT NULL
AND aps.category_sequence IS NOT NULL
AND aps.time_category_id = p_time_category_id
AND aps.category_sequence < p_category_sequence)
)
AND exists
(select 'Y'
from hxc_tc_ap_links tcl
where tcl.application_period_id = aps.application_period_id
);
SELECT 'Y'
FROM hxc_time_building_blocks day
,hxc_time_building_blocks tc
WHERE TRUNC(day.start_time) = p_day
AND day.scope = 'DAY'
AND day.approval_status = 'SUBMITTED'
AND day.resource_id = p_resource_id
AND day.date_to = hr_general.end_of_time
AND day.parent_building_block_id = tc.time_building_block_id
AND day.parent_building_block_ovn = tc.object_version_number
AND tc.scope = 'TIMECARD'
AND tc.date_to = hr_general.end_of_time;
select tc.resource_id, tc.start_time, tc.stop_time,tc.last_updated_by
from hxc_time_building_blocks tc
where tc.time_building_block_id = p_bld_blk_id
and tc.object_version_number = p_ovn;
select aps.application_period_id,
aps.start_time, -- period_start_date
aps.stop_time, -- period_end_date
aps.application_period_ovn,
aps.time_recipient_id,
aps.recipient_sequence,
aps.time_category_id,
aps.category_sequence,
aps.approval_item_key
from hxc_app_period_summary aps, hxc_tc_ap_links tcl
where aps.resource_id = p_resource_id
and aps.approval_status = 'SUBMITTED'
and aps.notification_status = 'NOT_NOTIFIED'
and aps.application_period_id = tcl.application_period_id
and tcl.timecard_id = p_timecard_id;
select 'N'
from hxc_app_period_summary
where application_period_id = p_period_id
and approval_status = 'SUBMITTED'
and notification_status = 'NOT_NOTIFIED';
l_last_updated_by number;
l_last_updated_by;
SELECT hxc_approval_item_key_s.nextval
INTO l_item_key
FROM dual;
update hxc_app_period_summary
set notification_status = 'NOTIFIED',
approval_item_type = itemtype,
approval_process_name = l_process_name,
approval_item_key = l_item_key
where application_period_id = l_appl_period_bb_id
and application_period_ovn = l_appl_period_bb_ovn;
p_user_id => l_last_updated_by)
);
hr_utility.trace('Before Update');
update hxc_app_period_summary
set notification_status = 'NOTIFIED'
where application_period_id = l_appl_period_bb_id
and application_period_ovn = l_appl_period_bb_ovn;
SELECT tc.approval_style_id
FROM hxc_timecard_summary tc
,hxc_timecard_application_summary ta
WHERE ta.application_period_id = p_app_id
AND tc.time_building_block_id = ta.time_building_block_id
BEGIN
OPEN c_approval_style(p_app_id);
select approval_style_id
from hxc_timecard_summary
where timecard_id = p_timecard_id;
g_error_table.delete;
g_error_table.delete;
select attribute1, attribute2,
attribute8, attribute9
from hxc_time_attributes
where time_attribute_id = (select min(time_attribute_id)
from hxc_time_attribute_usages
where time_building_block_id = p_app_bb_id
and time_building_block_ovn = p_app_bb_ovn);
update hxc_time_attributes
set attribute4 = l_notified_status,
attribute5 = l_approved_time,
attribute6 = l_approval_comment,
attribute7 = l_approved_status,
attribute8 = l_delegated_for
where time_attribute_id in (select time_attribute_id
from hxc_time_attribute_usages
where time_building_block_id = p_app_bb_id
and time_building_block_ovn = p_app_bb_ovn)
and attribute3 = to_char(l_approver_id)
and attribute_category = 'APPROVAL';
SELECT 'Y' into l_dummy
FROM SYS.OBJ$ O, SYS.SOURCE$ S
WHERE O.OBJ# = S.OBJ#
AND O.TYPE# = 11 --PACKAGE BODY
AND O.OWNER# = USERENV('SCHEMAID')
AND O.NAME = l_package_name
AND S.LINE = 1;
SELECT 'Y'
INTO l_dummy
FROM user_procedures
WHERE object_name = l_package_name
AND object_type = 'PACKAGE'
AND procedure_name = l_proc_name;
select htr.extension_function1
from hxc_time_recipients htr
where htr.time_recipient_id = p_time_recipient;
select approval_comp_id
from hxc_app_period_summary
where application_period_id = p_app_bb_id
and application_period_ovn = p_app_bb_ovn;
select hac.approval_comp_id
from hxc_approval_comps hac,
hxc_approval_styles has,
hxc_time_building_blocks htb
where htb.time_building_block_id =p_bb_id
and htb.object_version_number = p_bb_ovn
and htb.approval_style_id = has.approval_style_id
and has.approval_style_id = hac.APPROVAL_STYLE_ID
and hac.approval_mechanism = 'PROJECT_MANAGER'
and hac.parent_comp_id is null
and hac.parent_comp_ovn is null;
UPDATE hxc_time_building_blocks
SET comment_text = substr(l_exception, 1, 2000)
WHERE time_building_block_id = l_app_bld_blk_id
AND object_version_number = l_app_ovn;
select day.resource_id,
day.time_building_block_id,
day.approval_style_id,
max(day.object_version_number)
from hxc_time_building_blocks day,
hxc_time_building_blocks app
where app.time_building_block_id = p_app_bld_blk_id
and app.object_version_number = p_app_ovn
and app.scope = 'APPLICATION_PERIOD'
and app.resource_id = day.resource_id
and day.scope = 'DAY'
and day.start_time between app.start_time and app.stop_time
group by day.resource_id,
day.time_building_block_id,
day.approval_style_id,
day.object_version_number
order by day.time_building_block_id;
select dru.time_entry_rule_id
from hxc_data_app_rule_usages dru
where dru.approval_style_id = p_appr_style_id
and to_char(dru.time_recipient_id) = p_time_recipient_id;
SELECT 'Y'
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_app_period_id
AND object_version_number < p_app_period_ovn
AND approval_status = 'APPROVED';
SELECT COUNT(*)
FROM hxc_time_building_blocks
START WITH time_building_block_id = p_timecard_id
AND object_version_number = p_timecard_ovn
CONNECT by prior time_building_block_id =
parent_building_block_id
and prior object_version_number =
parent_building_block_ovn;
SELECT object_version_number
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_timecard_id
AND approval_status = 'SUBMITTED'
AND object_version_number < p_timecard_ovn
ORDER BY object_version_number desc;
select dar.name
,NVL( dar.description, dar.name ) ter_message_name
,dar.rule_usage
,dar.formula_id
,dar.mapping_id
,dar.attribute1
,dar.attribute2
,dar.attribute3
,dar.attribute4
,dar.attribute5
,dar.attribute6
,dar.attribute7
,dar.attribute8
,dar.attribute9
,dar.attribute10
,dar.attribute11
,dar.attribute12
,dar.attribute13
,dar.attribute14
,dar.attribute15
,ff.formula_name
,''
from ff_formulas_f ff
,hxc_time_entry_rules dar
where dar.time_entry_rule_id = p_data_appr_rule_id
and p_end_date between dar.start_date and dar.end_date
and ff.formula_id(+) = dar.formula_id
and dar.start_date BETWEEN ff.effective_start_date(+)
AND ff.effective_end_date(+)
order by dar.start_date;
SELECT start_time, stop_time
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_bb_id
AND object_version_number = p_bb_ovn;
l_error_table.delete;
select to_number(ta.attribute10)
from hxc_time_attributes ta,
hxc_time_attribute_usages tau,
hxc_time_building_blocks tbb
where tbb.time_building_block_id = p_timecard_bb_id
and tbb.object_version_number = p_timecard_ovn
and tbb.time_building_block_id = tau.time_building_block_id
and tbb.object_version_number = tau.time_building_block_ovn
and ta.time_attribute_id = tau.time_attribute_id
and ta.attribute_category = 'APPROVAL';