[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
g_update_phase BOOLEAN := FALSE;
select bld_blk_info_type_id into l_bld_blk_id
from hxc_bld_blk_info_types
where bld_blk_info_type = p_info_type;
select mc.segment,
mc.field_name,
bbui.building_block_category,
bbit.bld_blk_info_type_id,
bbit.bld_blk_info_type
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_retrieval_processes rp
,hxc_bld_blk_info_types bbit
,hxc_bld_blk_info_type_usages bbui
where rp.mapping_id = m.mapping_id
and rp.retrieval_process_id = p_retrieval_process_id
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id;
select mc.segment,
mc.field_name,
bbui.building_block_category,
bbit.bld_blk_info_type_id,
bbit.bld_blk_info_type
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_deposit_processes dp
,hxc_bld_blk_info_types bbit
,hxc_bld_blk_info_type_usages bbui
where dp.mapping_id = m.mapping_id
and dp.deposit_process_id = p_deposit_process_id
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id;
INSERT INTO hxc_timecard_debug
(LINE
,LOCATION
,REFERENCE
,TEXT
)
VALUES
(g_debug_count
,l_loc
,p_reference
,p_text
);
INSERT INTO hxc_timecard_debug
(LINE
,LOCATION
,REFERENCE
,TEXT
)
VALUES
(g_debug_count
,l_loc
,p_reference
,p_text
);
FUNCTION update_required(p_old IN VARCHAR2
,p_new IN VARCHAR2) RETURN BOOLEAN IS
BEGIN
IF ((p_old <> p_new)
OR((p_old IS null) AND (p_new IS NOT NULL))
OR((p_old IS NOT NULL) AND (p_new IS null))) THEN
RETURN TRUE;
END update_required;
select approval_status
from hxc_time_building_blocks
where resource_id = p_r_id
and scope = 'APPLICATION_PERIOD'
and approval_status = 'APPROVED'
and p_date between start_time and stop_time
and NOT exists
(select 'Y'
from hxc_time_building_blocks
where resource_id = p_r_id
and scope = 'APPLICATION_PERIOD'
and date_to = hr_general.end_of_time
and approval_status = 'REJECTED'
and p_date between start_time and stop_time
);
select approval_status
from hxc_time_building_blocks
where time_building_block_id = p_time_building_block_id
and date_to = hr_general.end_of_time
and approval_status = 'WORKING';
SELECT 'Y'
FROM PER_ALL_ASSIGNMENTS_F paa,
per_assignment_status_types typ
WHERE paa.PERSON_ID = p_resource_id
AND paa.ASSIGNMENT_TYPE = 'E'
AND paa.PRIMARY_FLAG = 'Y'
AND paa.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
AND typ.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND p_check_date between paa.effective_start_date and paa.effective_end_date;
select *
from hxc_time_building_blocks
where time_building_block_id = p_time_building_block_id
and object_version_number = p_ovn;
(p_mode = 'UPDATE')
) then
--
-- Check if it is a new block
--
if (g_timecard(p_block_number).new = 'Y') then
l_not_just_approval_status := true;
if (update_required(g_timecard(p_block_number).measure,block_rec.measure))then
l_process := true;
if (update_required(g_timecard(p_block_number).comment_text,block_rec.comment_text)) then
l_process := true;
if (update_required(g_timecard(p_block_number).unit_of_measure,block_rec.unit_of_measure)) then
l_process := true;
if (update_required(g_timecard(p_block_number).approval_status,block_rec.approval_status)) then
l_process := true;
if(update_required(g_timecard(p_block_number).parent_building_block_ovn, block_rec.parent_building_block_ovn)) then
l_process := true;
(p_mode = 'DELETE')) then
--
-- Here we are asking if a previously existing block
-- should be deleted because it's parameters are
-- nulled out
--
if ((g_timecard(p_block_number).measure is NULL)
AND
(g_timecard(p_block_number).start_time is NULL)
AND
(g_timecard(p_block_number).stop_time is NULL)
AND
(g_timecard(p_block_number).new <> 'Y')
) then
-- Go ahead and delete the block
l_process := true;
if(p_mode='DELETE') then
if (g_timecard(p_block_number).date_to <> hr_general.end_of_time) then
l_process := true;
PROCEDURE correct_update_flags(p_tk in BOOLEAN)
IS
l_block number;
'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
'Entering');
if(process_block(x,'UPDATE',p_tk) = FALSE) then
g_timecard(x).changed:='N';
'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
'Updating CHANGED flag for tbbid:'||g_timecard(x).time_building_block_id);
'HXC_SELF_SERVICE_TIME_DEPOSIT.CORRECT_UPDATE_FLAGS',
'Leaving');
END correct_update_flags;
select 'Y'
from hr_lookups
where lookup_type = 'HXC_DYNAMIC_TEMPLATES'
and meaning = p_name_to_check;
select 'Y'
from hxc_time_building_blocks tbb
,hxc_time_attribute_usages tau
,hxc_time_attributes ta
where ta.time_attribute_id = tau.time_attribute_id
and tau.time_building_block_id = tbb.time_building_block_id
and tau.time_building_block_ovn = tbb.object_version_number
and tbb.date_to = hr_general.end_of_time
and ta.attribute_category = 'TEMPLATES'
and ta.attribute2 = 'PRIVATE'
and tbb.scope = 'TIMECARD_TEMPLATE'
and tbb.time_building_block_id <> p_time_building_block_id
and tbb.resource_id = p_resource_id
and ta.attribute1 = p_name_to_check;
select 'Y'
from hxc_time_building_blocks tbb
,hxc_time_attribute_usages tau
,hxc_time_attributes ta
where ta.time_attribute_id = tau.time_attribute_id
and tau.time_building_block_id = tbb.time_building_block_id
and tau.time_building_block_ovn = tbb.object_version_number
and tbb.date_to = hr_general.end_of_time
and ta.attribute_category = 'TEMPLATES'
and ta.attribute2 = 'PUBLIC'
and tbb.scope = 'TIMECARD_TEMPLATE'
and tbb.time_building_block_id <> p_time_building_block_id
and ta.attribute1 = p_name_to_check;
g_attributes.delete;
g_timecard.delete;
g_messages.delete;
SELECT retrieval_process_id
FROM HXC_RETRIEVAL_PROCESSES
WHERE name = p_process_name
AND time_recipient_id = p_app;
l_sql := 'select '||p_app_function||' from dual';
g_timecard_block_order.delete;
SELECT
mc.segment,
mc.field_name,
bbui.building_block_category
FROM
hxc_mapping_components mc,
hxc_bld_blk_info_type_usages bbui,
hxc_bld_blk_info_types bbit
WHERE
mc.mapping_component_id in (Select mcu.mapping_component_id from
hxc_mappings m ,
hxc_mapping_comp_usages mcu,
hxc_retrieval_processes rp
WHERE
rp.mapping_id = m.mapping_id and
rp.retrieval_process_id = p_retrieval_process_id and
m.mapping_id = mcu.mapping_id )
AND
mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id AND
bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id AND
bbit.bld_blk_info_type = p_attribute_category;
select mc.segment, mc.field_name, bbui.building_block_category
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_deposit_processes dp
,hxc_bld_blk_info_types bbit
,hxc_bld_blk_info_type_usages bbui
where dp.mapping_id = m.mapping_id
and dp.deposit_process_id = p_deposit_process_id --AI3
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
AND bbit.bld_blk_info_type = p_attribute_category;
l_app_attributes.delete;
SELECT bld_blk_info_type_id
FROM hxc_bld_blk_info_types
WHERE bld_blk_info_type = p_type;
,p_update_required IN OUT NOCOPY BOOLEAN)
IS
CURSOR csr_bb_attributes(p_time_attribute_id NUMBER,
p_ta_ovn NUMBER) IS
select *
from hxc_time_attributes
where time_attribute_id = p_time_attribute_id
and (
(object_version_number = p_ta_ovn)
OR
(p_ta_ovn IS NULL)
);
select mc.segment, mc.field_name,bbit.bld_blk_info_type
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_deposit_processes dp
,hxc_bld_blk_info_types bbit
where dp.mapping_id = m.mapping_id
and dp.deposit_process_id = p_deposit_process_id
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = p_bbit_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
p_update_required := FALSE;
p_update_required := TRUE;
IF p_update_required <> TRUE then
p_update_required:= update_required(l_old_attribute.attribute1, g_attributes(y).attribute1);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute2, g_attributes(y).attribute2);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute3, g_attributes(y).attribute3);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute4, g_attributes(y).attribute4);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute5, g_attributes(y).attribute5);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute6, g_attributes(y).attribute6);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute7, g_attributes(y).attribute7);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute8, g_attributes(y).attribute8);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute9, g_attributes(y).attribute9);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute10, g_attributes(y).attribute10);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute11, g_attributes(y).attribute11);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute12, g_attributes(y).attribute12);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute13, g_attributes(y).attribute13);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute14, g_attributes(y).attribute14);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute15, g_attributes(y).attribute15);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute16, g_attributes(y).attribute16);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute17, g_attributes(y).attribute17);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute18, g_attributes(y).attribute18);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute19, g_attributes(y).attribute19);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute20, g_attributes(y).attribute20);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute21, g_attributes(y).attribute21);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute22, g_attributes(y).attribute22);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute23, g_attributes(y).attribute23);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute24, g_attributes(y).attribute24);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute25, g_attributes(y).attribute25);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute26, g_attributes(y).attribute26);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute27, g_attributes(y).attribute27);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute28, g_attributes(y).attribute28);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute29, g_attributes(y).attribute29);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute30, g_attributes(y).attribute30);
IF p_update_required <> TRUE THEN
p_update_required:= update_required(l_old_attribute.attribute_category, g_attributes(y).attribute_category);
((g_attributes(y).changed = 'Y')AND(p_update_required))
) then
if(check_approval_locked(p_block))then
hxc_time_entry_rules_utils_pkg.add_error_to_table (
p_message_table => g_messages
, p_message_name => 'HXC_NO_MODIFY_APPROVED_DETAIL'
, p_message_token => NULL
, p_message_level => 'ERROR'
, p_message_field => NULL
, p_timecard_bb_id => g_timecard(p_block).time_building_block_id
, p_timecard_bb_ovn => g_timecard(p_block).object_version_number --added 2822462
, p_time_attribute_id => NULL);
PROCEDURE update_attribute_record
(p_attribute_number IN BINARY_INTEGER
,p_app_attribute_count IN NUMBER
,p_mapping IN t_mapping
,p_app_attributes IN OUT NOCOPY app_attributes_info
) IS
CURSOR csr_segment(p_retrieval_process_id NUMBER
,p_attribute_category VARCHAR2
,p_field_name VARCHAR2)
IS
select mc.segment
from hxc_mapping_components mc
,hxc_mapping_comp_usages mcu
,hxc_mappings m
,hxc_retrieval_processes rp
,hxc_bld_blk_info_types bbit
,hxc_bld_blk_info_type_usages bbui
where rp.mapping_id = m.mapping_id
AND mc.field_name = p_field_name
and rp.retrieval_process_id = p_retrieval_process_id
and m.mapping_id = mcu.mapping_id
and mcu.mapping_component_id = mc.mapping_component_id
and mc.bld_blk_info_type_id = bbit.bld_blk_info_type_id
AND bbit.bld_blk_info_type_id = bbui.bld_blk_info_type_id
AND bbit.bld_blk_info_type = p_attribute_category;
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
p_app_attributes(l_att_count).updated := 'Y';
select 1 into l_dummy
from hxc_time_attributes
where time_attribute_id = p_app_attributes(p_app_attribute_count).time_attribute_id
and object_version_number = (select max(object_version_number)
from hxc_time_attributes ta2
where ta2.time_attribute_id = time_attribute_id)
and NVL(attribute1,'NULL') = NVL(g_attributes(p_attribute_number).attribute1,'NULL')
and NVL(attribute2,'NULL') = NVL(g_attributes(p_attribute_number).attribute2,'NULL')
and NVL(attribute3,'NULL') = NVL(g_attributes(p_attribute_number).attribute3,'NULL')
and NVL(attribute4,'NULL') = NVL(g_attributes(p_attribute_number).attribute4,'NULL')
and NVL(attribute5,'NULL') = NVL(g_attributes(p_attribute_number).attribute5,'NULL')
and NVL(attribute6,'NULL') = NVL(g_attributes(p_attribute_number).attribute6,'NULL')
and NVL(attribute7,'NULL') = NVL(g_attributes(p_attribute_number).attribute7,'NULL')
and NVL(attribute8,'NULL') = NVL(g_attributes(p_attribute_number).attribute8,'NULL')
and NVL(attribute9,'NULL') = NVL(g_attributes(p_attribute_number).attribute9,'NULL')
and NVL(attribute10,'NULL') = NVL(g_attributes(p_attribute_number).attribute10,'NULL')
and NVL(attribute11,'NULL') = NVL(g_attributes(p_attribute_number).attribute11,'NULL')
and NVL(attribute12,'NULL') = NVL(g_attributes(p_attribute_number).attribute12,'NULL')
and NVL(attribute13,'NULL') = NVL(g_attributes(p_attribute_number).attribute13,'NULL')
and NVL(attribute14,'NULL') = NVL(g_attributes(p_attribute_number).attribute14,'NULL')
and NVL(attribute15,'NULL') = NVL(g_attributes(p_attribute_number).attribute15,'NULL')
and NVL(attribute16,'NULL') = NVL(g_attributes(p_attribute_number).attribute16,'NULL')
and NVL(attribute17,'NULL') = NVL(g_attributes(p_attribute_number).attribute17,'NULL')
and NVL(attribute18,'NULL') = NVL(g_attributes(p_attribute_number).attribute18,'NULL')
and NVL(attribute19,'NULL') = NVL(g_attributes(p_attribute_number).attribute19,'NULL')
and NVL(attribute20,'NULL') = NVL(g_attributes(p_attribute_number).attribute20,'NULL')
and NVL(attribute21,'NULL') = NVL(g_attributes(p_attribute_number).attribute21,'NULL')
and NVL(attribute22,'NULL') = NVL(g_attributes(p_attribute_number).attribute22,'NULL')
and NVL(attribute23,'NULL') = NVL(g_attributes(p_attribute_number).attribute23,'NULL')
and NVL(attribute24,'NULL') = NVL(g_attributes(p_attribute_number).attribute24,'NULL')
and NVL(attribute25,'NULL') = NVL(g_attributes(p_attribute_number).attribute25,'NULL')
and NVL(attribute26,'NULL') = NVL(g_attributes(p_attribute_number).attribute26,'NULL')
and NVL(attribute27,'NULL') = NVL(g_attributes(p_attribute_number).attribute27,'NULL')
and NVL(attribute28,'NULL') = NVL(g_attributes(p_attribute_number).attribute28,'NULL')
and NVL(attribute29,'NULL') = NVL(g_attributes(p_attribute_number).attribute29,'NULL')
and NVL(attribute30,'NULL') = NVL(g_attributes(p_attribute_number).attribute30,'NULL')
and attribute_category = g_attributes(p_attribute_number).attribute_category;
l_proc := 'UpdateAttributeRecord';
END update_attribute_record;
select 1
from hxc_time_attributes ta
where ta.time_attribute_id = p_time_attribute_id;
update_attribute_record
(p_attribute_number => g_attributes.last
,p_app_attribute_count => p_app_attribute_count
,p_mapping => p_mapping
,p_app_attributes => p_app_attributes
);
debug(l_proc,40,'Updated :'||g_app_attributes(l_attribute).updated);
PROCEDURE update_deposit_globals(
p_retrieval_process_id IN NUMBER default null,
p_deposit_process_id IN NUMBER default null
) IS
l_blocks timecard_info;
l_proc VARCHAR2(30) := 'UPDATE_DEPOSIT_GLOBALS';
(NVL(g_app_attributes(l_count).updated,'N') <> 'Y')
) THEN
--
-- This can't be a new attribute
--
l_new_attribute := FALSE;
update_attribute_record
(p_attribute_number => l_att_count
,p_app_attribute_count => l_count
,p_mapping => l_mapping
,p_app_attributes => g_app_attributes
);
(g_app_attributes(l_count).updated <> 'Y')
) THEN
--
-- Create the new attribute record
--
create_attribute_record
(p_app_attribute_count => l_count
,p_mapping => l_mapping
,p_app_attributes => g_app_attributes
);
END update_deposit_globals;
select 'Y' into l_dummy
from hxc_bld_blk_info_types
where bld_blk_info_type_id = p_id;
select bld_blk_info_type_id
from hxc_bld_blk_info_types
where bld_blk_info_type = p_info_type;
procedure update_ovns
(p_block_id in NUMBER
,p_new_parent_ovn in NUMBER
) is
l_block number;
end update_ovns;
l_update_required BOOLEAN := FALSE;
l_attribute_update_req BOOLEAN := FALSE;
l_mode VARCHAR2(30) := 'UPDATE';
select *
from hxc_time_building_blocks
where time_building_block_id = p_building_block_id
and object_version_number = p_bb_ovn;
SELECT tr.TIME_RECIPIENT_ID
,tr.NAME
,tr.APPLICATION_ID
,tr.OBJECT_VERSION_NUMBER
,tr.APPLICATION_RETRIEVAL_FUNCTION
,tr.APPLICATION_UPDATE_PROCESS
,tr.APPL_VALIDATION_PROCESS
FROM hxc_time_recipients tr
,hxc_application_set_comps_v asc1
where p_application_set_id = asc1.application_set_id
and asc1.time_recipient_id = tr.time_recipient_id;
SELECT 'YES'
FROM hxc_time_building_blocks
WHERE time_building_block_id = p_timecard_id
AND object_version_number < p_max_ovn
AND approval_status = 'SUBMITTED';
SELECT NVL(MAX(TO_NUMBER(item_key)),1)
FROM wf_item_attribute_values
WHERE item_type = p_item_type;
select hxc_approval_item_key_s.nextval
from dual;
SELECT has.approval_style_id
FROM hxc_approval_styles has
WHERE has.name = 'OTL Auto Approve';
SELECT rp.retrieval_process_id
FROM hxc_retrieval_processes rp
WHERE name = p_retrieval_process;
SELECT dp.deposit_process_id, dp.time_source_id, ts.name
INTO l_deposit_process_id, l_time_source_id, l_time_source_name
FROM hxc_time_sources ts
, hxc_deposit_processes dp
WHERE dp.name = p_deposit_process -- GPM v115.42
AND ts.time_source_id = dp.time_source_id;
'start the correct_update_flags');
correct_update_flags(p_allow_error_tc);
'end the correct_update_flags');
set_update_phase(TRUE); --AI2.5
IF recipt_rec.application_update_process IS NOT NULL THEN
IF code_chk(recipt_rec.application_update_process) THEN
l_upd_sql := 'BEGIN '||fnd_global.newline
||recipt_rec.application_update_process ||fnd_global.newline
||'(p_operation => :1);'||fnd_global.newline
update_deposit_globals (p_retrieval_process_id => l_app_valid_proc_id);
set_update_phase(FALSE); --AI2.5
g_app_attributes.delete;
IF(process_block(x,'UPDATE',p_allow_error_tc))
THEN
build_timecard_structure(
p_block => x
,p_deposit_process_id => l_deposit_process_id
,p_timecard => l_timecard
,p_update_required => l_attribute_update_req);
l_update_required := FALSE;
l_attribute_update_req := FALSE;
l_update_required := process_block(x,'UPDATE',p_allow_error_tc);
,p_update_required => l_attribute_update_req);
(l_update_required OR l_attribute_update_req)
AND
(NOT process_block(x,'DELETE',p_allow_error_tc))
) THEN
BEGIN
if(FND_LOG.LEVEL_STATEMENT>=FND_LOG.G_CURRENT_RUNTIME_LEVEL) then
fnd_log.string(FND_LOG.LEVEL_STATEMENT,
'hxc_self_service_time_deposit.start_deposit_wrapper',
'process :'||g_timecard(x).time_building_block_id);
update_ovns(g_timecard(x).time_building_block_id,
g_timecard(x).object_version_number);
ELSIF ( process_block(x,'DELETE',p_allow_error_tc)
) THEN
--
-- In this case, we've deleted a block in the timecard
-- typically this will happen when the user clicks on the
-- delete attribute row icon of the timecard
--
-- In this case, call the building block API directly, to
-- date effectively end date the building block.
-- FIX: Will this cause inconsistencies with the deposit API?
--
IF (g_timecard(x).measure IS NULL) THEN
l_date := SYSDATE;
hxc_building_block_api.delete_building_block
(p_validate => FALSE
,p_object_version_number => g_timecard(x).object_version_number
,p_time_building_block_id => g_timecard(x).time_building_block_id
,p_effective_date => l_date
);
update_ovns(g_timecard(x).time_building_block_id,
g_timecard(x).object_version_number);
l_timecard.delete;
if((l_approval_timecard_id is not null) AND ((p_mode <> 'DELETE') or (p_mode <> 'SAVE'))) THEN
--
-- Next include the approvals process
--
-- Until we workflow the timecard, we need to start the
-- approvals process here.
-- Get the item key
--
OPEN csr_item_key;
procedure delete_timecard
(p_time_building_block_id in number
,p_effective_date in date
,p_mode varchar2
,p_deposit_process varchar2
,p_retrieval_process varchar2
) is
cursor get_attributes(p_tbb_id in number,p_tbb_ovn in number)
is
select
ta.TIME_ATTRIBUTE_ID ta_id
,bbit.BLD_BLK_INFO_TYPE ta_BLD_BLK_INFO_TYPE
,ta.ATTRIBUTE_CATEGORY ta_ATTRIBUTE_CATEGORY
,ta.ATTRIBUTE1 ta_ATTRIBUTE1
,ta.ATTRIBUTE2 ta_ATTRIBUTE2
,ta.ATTRIBUTE3 ta_ATTRIBUTE3
,ta.ATTRIBUTE4 ta_ATTRIBUTE4
,ta.ATTRIBUTE5 ta_ATTRIBUTE5
,ta.ATTRIBUTE6 ta_ATTRIBUTE6
,ta.ATTRIBUTE7 ta_ATTRIBUTE7
,ta.ATTRIBUTE8 ta_ATTRIBUTE8
,ta.ATTRIBUTE9 ta_ATTRIBUTE9
,ta.ATTRIBUTE10 ta_ATTRIBUTE10
,ta.ATTRIBUTE11 ta_ATTRIBUTE11
,ta.ATTRIBUTE12 ta_ATTRIBUTE12
,ta.ATTRIBUTE13 ta_ATTRIBUTE13
,ta.ATTRIBUTE14 ta_ATTRIBUTE14
,ta.ATTRIBUTE15 ta_ATTRIBUTE15
,ta.ATTRIBUTE16 ta_ATTRIBUTE16
,ta.ATTRIBUTE17 ta_ATTRIBUTE17
,ta.ATTRIBUTE18 ta_ATTRIBUTE18
,ta.ATTRIBUTE19 ta_ATTRIBUTE19
,ta.ATTRIBUTE20 ta_ATTRIBUTE20
,ta.ATTRIBUTE21 ta_ATTRIBUTE21
,ta.ATTRIBUTE22 ta_ATTRIBUTE22
,ta.ATTRIBUTE23 ta_ATTRIBUTE23
,ta.ATTRIBUTE24 ta_ATTRIBUTE24
,ta.ATTRIBUTE25 ta_ATTRIBUTE25
,ta.ATTRIBUTE26 ta_ATTRIBUTE26
,ta.ATTRIBUTE27 ta_ATTRIBUTE27
,ta.ATTRIBUTE28 ta_ATTRIBUTE28
,ta.ATTRIBUTE29 ta_ATTRIBUTE29
,ta.ATTRIBUTE30 ta_ATTRIBUTE30
,ta.BLD_BLK_INFO_TYPE_ID ta_INFO_TYPE
,ta.OBJECT_VERSION_NUMBER ta_OVN
from hxc_time_attribute_usages tau,
hxc_time_attributes ta,
hxc_bld_blk_info_types bbit
where tau.time_building_block_id = p_tbb_id
and tau.time_building_block_ovn = p_tbb_ovn
and ta.time_attribute_id = tau.time_attribute_id
and ta.bld_blk_info_type_id = bbit.bld_blk_info_type_id;
select
tbb_tim.time_building_block_id tbb_tim_id,
tbb_tim.object_version_number tbb_tim_ovn,
tbb_tim.type tbb_tim_type,
tbb_tim.measure tbb_tim_measure,
tbb_tim.unit_of_measure tbb_tim_unit_of_measure,
tbb_tim.start_time tbb_tim_start_time,
tbb_tim.stop_time tbb_tim_stop_time,
tbb_tim.scope tbb_tim_scope,
tbb_tim.approval_status tbb_tim_approval_status,
tbb_tim.resource_id tbb_tim_resource_id,
tbb_tim.resource_type tbb_tim_resource_type,
tbb_tim.approval_style_id tbb_tim_approval_style,
tbb_tim.date_from tbb_tim_date_from,
tbb_tim.date_to tbb_tim_date_to,
tbb_tim.comment_text tbb_tim_comment_text
from hxc_time_building_blocks tbb_tim
where tbb_tim.time_building_block_id = p_timecard_block
and tbb_tim.date_to = hr_general.end_of_time
and (tbb_tim.scope='TIMECARD' or tbb_tim.scope='TIMECARD_TEMPLATE');
select
tbb_day.time_building_block_id tbb_day_id,
tbb_day.object_version_number tbb_day_ovn,
tbb_day.type tbb_day_type,
tbb_day.measure tbb_day_measure,
tbb_day.unit_of_measure tbb_day_unit_of_measure,
tbb_day.start_time tbb_day_start_time,
tbb_day.stop_time tbb_day_stop_time,
tbb_day.scope tbb_day_scope,
tbb_day.approval_status tbb_day_approval_status,
tbb_day.resource_id tbb_day_resource_id,
tbb_day.resource_type tbb_day_resource_type,
tbb_day.approval_style_id tbb_day_approval_style,
tbb_day.date_from tbb_day_date_from,
tbb_day.date_to tbb_day_date_to,
tbb_day.comment_text tbb_day_comment_text
from hxc_time_building_blocks tbb_day
where tbb_day.parent_building_block_ovn = p_timecard_ovn
and tbb_day.parent_building_block_id = p_timecard_block
and tbb_day.date_to = hr_general.end_of_time
and tbb_day.scope='DAY';
select
tbb_det.time_building_block_id tbb_det_id,
tbb_det.object_version_number tbb_det_ovn,
tbb_day.time_building_block_id tbb_day_id,
tbb_day.object_version_number tbb_day_ovn,
tbb_det.type tbb_det_type,
tbb_det.measure tbb_det_measure,
tbb_det.unit_of_measure tbb_det_unit_of_measure,
tbb_det.start_time tbb_det_start_time,
tbb_det.stop_time tbb_det_stop_time,
tbb_det.scope tbb_det_scope,
tbb_det.approval_status tbb_det_approval_status,
tbb_det.resource_id tbb_det_resource_id,
tbb_det.resource_type tbb_det_resource_type,
tbb_det.approval_style_id tbb_det_approval_style,
tbb_det.date_from tbb_det_date_from,
tbb_det.date_to tbb_det_date_to,
tbb_det.comment_text tbb_det_comment_text
from hxc_time_building_blocks tbb_day,
hxc_time_building_blocks tbb_det
where tbb_day.parent_building_block_ovn = p_timecard_ovn
and tbb_day.parent_building_block_id = p_timecard_block
and tbb_det.parent_building_block_ovn = tbb_day.object_version_number
and tbb_det.parent_building_block_id = tbb_day.time_building_block_id
and tbb_det.date_to = hr_general.end_of_time
-- Why this condition is not met for a submitted timecard but is for saved timecards?
-- and tbb_day.date_to = hr_general.end_of_time
and tbb_det.scope='DETAIL';
select attribute2||'|'||tau.time_building_block_id
from hxc_time_attributes ta,
hxc_time_attribute_usages tau
where ta.time_attribute_id=tau.time_attribute_id
and ta.attribute_category='TEMPLATES'
and tau.time_building_block_id=p_time_building_block_id;
end delete_timecard;
if(g_update_phase) then --AI2.5
g_timecard := p_building_blocks;
PROCEDURE set_update_phase(p_mode in BOOLEAN)
IS
BEGIN
g_update_phase := p_mode;
END set_update_phase;
SELECT tr.application_retrieval_function
FROM hxc_time_recipients tr
WHERE tr.time_recipient_id = p_time_recipient_id;
SELECT htbb.time_building_block_id, htbb.TYPE, htbb.measure,
htbb.unit_of_measure, htbb.start_time, htbb.stop_time,
htbb.parent_building_block_id, 'N' parent_is_new, htbb.SCOPE,
htbb.object_version_number, htbb.approval_status,
htbb.resource_id, htbb.resource_type, htbb.approval_style_id,
htbb.date_from, htbb.date_to, htbb.comment_text,
htbb.parent_building_block_ovn, 'N' NEW, 'N' changed, 'N' process,
htbb.application_set_id, htbb.translation_display_key
FROM hxc_time_building_blocks htbb
WHERE SYSDATE BETWEEN htbb.date_from AND htbb.date_to
START WITH ( htbb.time_building_block_id = p_timecard_id
AND htbb.object_version_number = p_timecard_ovn
)
CONNECT BY PRIOR htbb.time_building_block_id =
htbb.parent_building_block_id
AND PRIOR htbb.object_version_number =
htbb.parent_building_block_ovn
ORDER BY htbb.time_building_block_id ASC;
SELECT hta.time_attribute_id, htau.time_building_block_id,
hbbit.bld_blk_info_type, hta.attribute_category, hta.attribute1,
hta.attribute2, hta.attribute3, hta.attribute4, hta.attribute5,
hta.attribute6, hta.attribute7, hta.attribute8, hta.attribute9,
hta.attribute10, hta.attribute11, hta.attribute12,
hta.attribute13, hta.attribute14, hta.attribute15,
hta.attribute16, hta.attribute17, hta.attribute18,
hta.attribute19, hta.attribute20, hta.attribute21,
hta.attribute22, hta.attribute23, hta.attribute24,
hta.attribute25, hta.attribute26, hta.attribute27,
hta.attribute28, hta.attribute29, hta.attribute30,
hta.bld_blk_info_type_id, hta.object_version_number, 'N' NEW,
'N' changed, 'N' process
FROM hxc_time_attributes hta,
hxc_time_attribute_usages htau,
hxc_bld_blk_info_types hbbit
WHERE htau.time_building_block_id = p_build_block_id
AND htau.time_building_block_ovn = p_build_block_ovn
AND htau.time_attribute_id = hta.time_attribute_id
AND hta.bld_blk_info_type_id = hbbit.bld_blk_info_type_id;
g_timecard.DELETE;
g_attributes.DELETE;
g_app_attributes.DELETE;