[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Procedure insert_audit_header
(p_overall_status in varchar2
,p_transaction_info in hxc_timecard.transaction_info
,p_messages in out nocopy hxc_message_table_type
,p_transaction_id out nocopy hxc_transactions.transaction_id%type
) is
cursor c_transaction_sequence is
select hxc_transactions_s.nextval from dual;
select deposit_process_id
into l_deposit_process_id
from hxc_deposit_processes
where name = 'OTL Deposit Process';
insert into hxc_dep_transactions
(transaction_id
,transaction_date
,type
,transaction_process_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,status
,data_set_id
) values
(l_transaction_id
,sysdate
,'DEPOSIT'
,l_deposit_process_id
,null
,sysdate
,null
,sysdate
,null
,p_overall_status
,l_data_set_id
);
End insert_audit_header;
Procedure insert_audit_details
(p_transaction_info in out nocopy hxc_timecard.transaction_info
,p_messages in out nocopy hxc_message_table_type
,p_transaction_id in hxc_transactions.transaction_id%type
) is
l_index NUMBER;
select hxc_transaction_details_s.nextval from dual;
insert into hxc_dep_transaction_details
(transaction_detail_id
,time_building_block_id
,transaction_id
,created_by
,creation_date
,last_updated_by
,last_update_date
,last_update_login
,time_building_block_ovn
,status
,exception_description
,data_set_id
) values
(l_transaction_detail_id
,p_transaction_info(l_index).time_building_block_id
,p_transaction_id
,null
,sysdate
,null
,sysdate
,null
,p_transaction_info(l_index).object_version_number
,p_transaction_info(l_index).status
,p_transaction_info(l_index).exception_desc
,p_transaction_info(l_index).data_set_id
);
End insert_audit_details;
insert_audit_header
(find_overall_status(p_transaction_info)
,p_transaction_info
,p_messages
,l_transaction
);
insert_audit_details
(p_transaction_info
,p_messages
,l_transaction
);
pa_last_update_date_tab DATETAB;
pay_last_update_date_tab DATETAB;
pay_last_update_date_tab.EXTEND(1);
pa_last_update_date_tab.EXTEND(1);
PROCEDURE update_transferred_to(p_timecard_id IN NUMBER,
p_application_set IN NUMBER )
IS
CURSOR get_row
IS
SELECT transferred_to,
ROWIDTOCHAR(sum.ROWID)
FROM hxc_timecard_summary sum
WHERE timecard_id = p_timecard_id;
IS SELECT time_recipient_name
FROM hxc_application_set_comps_v
WHERE application_set_id = p_application_set;
UPDATE hxc_timecard_summary
SET transferred_to = l_transferred_to
WHERE ROWID = CHARTOROWID(l_rowid);
END update_transferred_to;
pa_last_update_date_tab :=DATETAB();
pay_last_update_date_tab :=DATETAB();
-- Override status for deleted building blocks. These should always be passed to
-- recipient applications for processing once deleted.
IF ( fnd_date.canonical_to_date(( p_blocks(l_blk_ind).date_to )) = hr_general.end_of_time )
THEN
l_status := p_blocks(l_blk_ind).approval_status;
-- insert row
INSERT INTO hxc_latest_details (
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type,
org_id,
business_group_id ) -- Bug 8888911
VALUES (
p_blocks(l_blk_ind).resource_id,
p_blocks(l_blk_ind).time_building_block_id,
p_blocks(l_blk_ind).object_version_number,
l_status,
hxc_timecard_block_utils.date_value(
p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
hxc_timecard_block_utils.date_value(
p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
p_blocks(l_blk_ind).application_set_id,
sysdate,
p_blocks(l_blk_ind).comment_text,
p_blocks(l_blk_ind).resource_type ,
l_org_id,
l_bg_id ); -- Bug 8888911
pay_last_update_date_tab(l_pay_index) := SYSDATE;
pa_last_update_date_tab(l_pa_index) := SYSDATE;
-- update row
UPDATE hxc_latest_details
SET object_version_number = p_blocks(l_blk_ind).object_version_number,
approval_status = l_status,
application_set_id = p_blocks(l_blk_ind).application_set_id,
last_update_date = sysdate,
comment_text = p_blocks(l_blk_ind).comment_text,
resource_type = p_blocks(l_blk_ind).resource_type,
org_id = l_org_id, -- Bug 8888911
business_group_id = l_bg_id -- Bug 8888911
WHERE time_building_block_id = p_blocks(l_blk_ind).time_building_block_id;
-- nothing to update, insert
INSERT INTO hxc_latest_details (
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id, -- Bug 8888911
business_group_id) -- Bug 8888911
VALUES (
p_blocks(l_blk_ind).resource_id,
p_blocks(l_blk_ind).time_building_block_id,
p_blocks(l_blk_ind).object_version_number,
l_status,
hxc_timecard_block_utils.date_value(
p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).start_time),
hxc_timecard_block_utils.date_value(
p_blocks(l_day_blocks(p_blocks(l_blk_ind).parent_building_block_id)).stop_time),
p_blocks(l_blk_ind).application_set_id,
sysdate,
p_blocks(l_blk_ind).comment_text,
p_blocks(l_blk_ind).resource_type ,
l_org_id, -- Bug 8888911
l_bg_id ); -- Bug 8888911
pay_last_update_date_tab(l_pay_index) := SYSDATE;
pa_last_update_date_tab(l_pa_index) := SYSDATE;
pay_last_update_date_tab(l_pay_index) := SYSDATE;
pa_last_update_date_tab(l_pa_index) := SYSDATE;
update_transferred_to(REPLACE(REGEXP_SUBSTR(l_app_tc(i),'.*-'),'-'),
REPLACE(REGEXP_SUBSTR(l_app_tc(i),'-.*'),'-'));
UPDATE hxc_pa_latest_details
SET object_version_number = pa_object_version_number_tab(i),
approval_status = pa_approval_status_tab(i),
application_set_id = pa_application_set_id_tab(i),
last_update_date = pa_last_update_date_tab(i),
comment_text = pa_comment_text_tab(i),
resource_type = pa_resource_type_tab(i),
org_id = pa_org_id_tab(i),
business_group_id = pa_business_group_id_tab(i)
WHERE time_building_block_id = pa_time_building_block_id_tab(i);
UPDATE hxc_pay_latest_details
SET object_version_number = pay_object_version_number_tab(i),
approval_status = pay_approval_status_tab(i),
application_set_id = pay_application_set_id_tab(i),
last_update_date = pay_last_update_date_tab(i),
comment_text = pay_comment_text_tab(i),
resource_type = pay_resource_type_tab(i),
org_id = pay_org_id_tab(i),
business_group_id = pay_business_group_id_tab(i)
WHERE time_building_block_id = pay_time_building_block_id_tab(i);
INSERT INTO hxc_pay_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id)
VALUES (
pay_resource_id_tab(i),
pay_time_building_block_id_tab(i),
pay_object_version_number_tab(i),
pay_approval_status_tab(i),
pay_start_time_tab(i),
pay_stop_time_tab(i),
pay_application_set_id_tab(i),
pay_last_update_date_tab(i),
pay_comment_text_tab(i),
pay_resource_type_tab(i) ,
pay_org_id_tab(i),
pay_business_group_id_tab(i),
pay_timecard_id_tab(i));
INSERT INTO hxc_pa_latest_details
(
resource_id,
time_building_block_id,
object_version_number,
approval_status,
start_time,
stop_time,
application_set_id,
last_update_date,
comment_text,
resource_type ,
org_id,
business_group_id,
timecard_id)
VALUES (
pa_resource_id_tab(i),
pa_time_building_block_id_tab(i),
pa_object_version_number_tab(i),
pa_approval_status_tab(i),
pa_start_time_tab(i),
pa_stop_time_tab(i),
pa_application_set_id_tab(i),
pa_last_update_date_tab(i),
pa_comment_text_tab(i),
pa_resource_type_tab(i) ,
pa_org_id_tab(i),
pa_business_group_id_tab(i),
pa_timecard_id_tab(i));
SELECT 1
INTO l_exists
FROM hxc_application_set_comps_v
WHERE application_set_id = p_app_set_id
AND time_recipient_name = p_recipient;
SELECT 1
INTO l_exists
FROM hxc_application_set_comps_v
WHERE application_set_id = p_app_set_id
AND time_recipient_name IN (p_recipient,'Human Resources')
AND ROWNUM < 2;
PA_DELETED VTAB;
PAY_DELETED VTAB;
SELECT 'Y'
FROM HXC_UPGRADE_DEFINITIONS
WHERE upg_type = 'RDB_ATTRIB_SNAPSHOT'
AND status = 'COMPLETE';
select bld_blk_info_type_id
into l_bld_blk_info_type_id
from hxc_bld_blk_info_types
where bld_blk_info_type = p_bld_blk_info_type;
PAY_DELETED.EXTEND(1);
PA_DELETED.EXTEND(1);
PA_DELETED := VTAB();
PAY_DELETED := VTAB();
3. Make one forall update.
*/
if p_attributes.COUNT > 0 then
l_index := p_attributes.FIRST;
l_measure:=0; -- deleted entry
PAY_DELETED(l_pay_att_index) := 'Y';
l_measure:=0; -- deleted entry
PA_DELETED(l_pa_att_index) := 'Y';
UPDATE hxc_pay_latest_details
SET ATTRIBUTE_CATEGORY = PAY_ATTRIBUTE_CATEGORY(i),
ATTRIBUTE1 = PAY_ATTRIBUTE1(i),
ATTRIBUTE2 = PAY_ATTRIBUTE2(i),
ATTRIBUTE3 = PAY_ATTRIBUTE3(i),
ATTRIBUTE4 = PAY_ATTRIBUTE4(i),
ATTRIBUTE5 = PAY_ATTRIBUTE5(i),
ATTRIBUTE6 = PAY_ATTRIBUTE6(i),
ATTRIBUTE7 = PAY_ATTRIBUTE7(i),
MEASURE = PAY_MEASURE(i)
WHERE TIME_BUILDING_BLOCK_ID = PAY_BUILDING_BLOCK_ID(i)
AND OBJECT_VERSION_NUMBER = PAY_BUILDING_BLOCK_OVN(i);
-- Deleting tbb ids which are LOGICALLY DELETED in hxc_time_building_blocks
-- and never retrieved. Such bb ids need not be considered for retrieval.
-- and can be removed from the HXC_PAY table for upgraded retrieval.
FORALL i IN PAY_TIME_ATTRIBUTE_ID.FIRST..PAY_TIME_ATTRIBUTE_ID.LAST
DELETE FROM hxc_pay_latest_details pre
WHERE TIME_BUILDING_BLOCK_ID = PAY_BUILDING_BLOCK_ID(i)
AND OBJECT_VERSION_NUMBER = PAY_BUILDING_BLOCK_OVN(i)
AND pay_measure(i) = 0
AND pay_deleted(i) = 'Y'
AND NOT EXISTS ( SELECT 1
FROM hxc_ret_pay_latest_details ret
WHERE pre.time_building_block_id = ret.time_building_block_id
);
UPDATE hxc_pa_latest_details
SET ATTRIBUTE_CATEGORY = PA_ATTRIBUTE_CATEGORY(i),
ATTRIBUTE1 = PA_ATTRIBUTE1(i),
ATTRIBUTE2 = PA_ATTRIBUTE2(i),
ATTRIBUTE3 = PA_ATTRIBUTE3(i),
ATTRIBUTE4 = PA_ATTRIBUTE4(i),
ATTRIBUTE5 = PA_ATTRIBUTE5(i),
ATTRIBUTE6 = PA_ATTRIBUTE6(i),
ATTRIBUTE7 = PA_ATTRIBUTE7(i),
MEASURE = PA_MEASURE(i)
WHERE TIME_BUILDING_BLOCK_ID = PA_BUILDING_BLOCK_ID(i)
AND OBJECT_VERSION_NUMBER = PA_BUILDING_BLOCK_OVN(i);
-- Deleting tbb ids which are LOGICALLY DELETED in hxc_time_building_blocks
-- and never retrieved. Such bb ids need not be considered for retrieval.
-- and can be removed from the HXC_PA table for upgraded retrieval.
FORALL i IN PA_TIME_ATTRIBUTE_ID.FIRST..PA_TIME_ATTRIBUTE_ID.LAST
DELETE FROM hxc_pa_latest_details pre
WHERE TIME_BUILDING_BLOCK_ID = PA_BUILDING_BLOCK_ID(i)
AND OBJECT_VERSION_NUMBER = PA_BUILDING_BLOCK_OVN(i)
AND pa_measure(i) = 0
AND pa_deleted(i) = 'Y'
AND NOT EXISTS ( SELECT 1
FROM hxc_ret_pa_latest_details ret
WHERE pre.time_building_block_id = ret.time_building_block_id
);