[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT name
INTO lp_org
FROM hr_all_organization_units_tl
WHERE organization_id = p_org_id
AND language = USERENV('LANG');
SELECT location_code
INTO lp_location
FROM hr_locations_all_tl
WHERE location_id = p_locn_id
AND language = USERENV('LANG');
SELECT payroll_name
INTO lp_payroll
FROM pay_all_payrolls_f
WHERE payroll_id = p_payroll_id
AND fnd_date.canonical_TO_DATE(p_from_date) BETWEEN effective_start_date
AND effective_END_date;
SELECT full_name
INTO lp_supervisor
FROM per_all_people_f
WHERE person_id = p_supervisor_id
AND SYSDATE BETWEEN effective_start_date
AND effective_END_date;
SELECT full_name
INTO lp_person
FROM per_all_people_f
WHERE person_id = p_person_id
AND SYSDATE BETWEEN effective_start_date
AND effective_END_date;
SELECT fnd.user_name||' ['||ppf.full_name||']'
INTO lp_user
FROM per_all_people_f ppf,
fnd_user fnd
WHERE ppf.person_id = fnd.employee_id
AND SYSDATE BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
AND fnd.user_id = FND_GLOBAL.USER_ID;
IS SELECT resource_id,
tc_start_time,
tc_stop_time
FROM hxc_rpt_tc_hist_log log
WHERE request_id = p_request_id ;
IS SELECT transaction_id,
MAX(creation_date),
MIN(creation_date),
MIN(created_by_user),
MIN(tc_comments),
MIN(tc_bb_id),
MIN(tc_bb_ovn),
MAX(tc_bb_id),
MIN(resource_name),
MIN(status)
FROM hxc_rpt_tc_details_all
WHERE resource_id = p_resource_id
AND tc_start_time = p_start_time
AND tc_stop_time = p_stop_time
AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
AND (day_date_to + (2/(24*60*60)))
AND transaction_id IS NOT NULL
AND transaction_detail_id IS NOT NULL
AND status <> 'WORKING'
GROUP BY transaction_id
ORDER BY MIN(creation_date) ;
IS SELECT transaction_id,
MAX(creation_date),
MIN(creation_date),
MIN(created_by_user),
MIN(tc_comments),
MIN(tc_bb_id),
MIN(tc_bb_ovn),
MAX(tc_bb_id),
MIN(resource_name),
MIN(decode(transaction_detail_id,NULL,'WORKING',status))
FROM hxc_rpt_tc_details_all
WHERE resource_id = p_resource_id
AND tc_start_time = p_start_time
AND tc_stop_time = p_stop_time
AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
AND (day_date_to + (2/(24*60*60)))
AND transaction_id IS NOT NULL
GROUP BY transaction_id
ORDER BY MIN(creation_date) ;
INSERT INTO hxc_rpt_tc_audit
( resource_id,
tc_start_time,
tc_stop_time,
resource_name,
action,
action_date,
action_by,
comments,
transaction_id,
tc_bb_id,
tc_bb_ovn,
action_type )
VALUES ( p_resource_id,
p_start_time,
p_stop_time,
l_trans_name_tab(i),
INITCAP(DECODE(l_trans_status_tab(i),'WORKING','Saved',l_trans_status_tab(i))),
l_trans_max_date_tab(i),
l_trans_user_tab(i),
l_comments_tab(i),
l_trans_id_tab(i),
l_bb_id_tab(i),
l_bb_ovn_tab(i),
'TS' );
IS SELECT 0 transaction_id,
0 tc_bb_id,
hxc.creation_date,
fnd.user_name ,
NVL(fnd.employee_id,-1),
INITCAP(approval_status),
DECODE(comment_text,
'LIGHT_APPROVAL','Approval On Submit',
'AUTO_APPROVE', 'Auto Approved',
comment_text),
' '
FROM hxc_time_building_blocks hxc,
fnd_user fnd
WHERE scope = 'APPLICATION_PERIOD'
AND resource_id = p_resource_id
AND p_start_time BETWEEN start_time
AND stop_time
AND TRUNC(p_stop_time) BETWEEN start_time
AND stop_time
AND approval_status IN ('APPROVED','REJECTED')
AND fnd.user_id = hxc.created_by
AND NVL(hxc.comment_text,' ') <> 'TIMED_OUT'
AND NVL(hxc.comment_text,' ') <> 'BLANK_NOTIFICATION'
ORDER BY hxc.creation_date ;
IS SELECT full_name
FROM per_all_people_f
WHERE person_id = p_person_id
AND p_appr_date BETWEEN effective_start_date
AND effective_end_date ;
INSERT INTO hxc_rpt_tc_audit
( resource_id,
tc_start_time,
tc_stop_time,
resource_name,
action,
action_date,
action_by,
comments,
transaction_id,
tc_bb_id,
action_type )
VALUES ( p_resource_id,
p_start_time,
p_stop_time,
l_appr_resource_tab(i),
l_appr_status_tab(i),
l_appr_date_tab(i),
l_appr_user_tab(i),
l_appr_comments_tab(i),
l_appr_trans_tab(i),
l_appr_bb_tab(i),
'TSA' );
l_appr_trans_tab.DELETE;
l_appr_bb_tab.DELETE;
l_appr_date_tab.DELETE;
l_appr_user_tab.DELETE;
l_appr_resource_tab.DELETE;
l_appr_status_tab.DELETE;
l_appr_comments_tab.DELETE;
IS SELECT transaction_id,
MIN(creation_date),
MIN(created_by_user),
MIN(tc_comments),
MIN(tc_bb_id),
MIN(tc_bb_ovn),
MIN(resource_name)
FROM hxc_rpt_tc_details_all det
WHERE resource_id = p_resource_id
AND tc_start_time = p_start_time
AND tc_stop_time = p_stop_time
AND day_date_to <> hr_general.end_of_time
AND creation_date = date_to
AND transaction_id IS NOT NULL
AND status <> 'WORKING'
AND NOT EXISTS ( SELECT 1
FROM hxc_timecard_summary hxc
WHERE timecard_id = det.tc_bb_id
AND hxc.resource_id = det.resource_id
AND hxc.start_time = det.tc_start_time
)
GROUP BY transaction_id
ORDER BY MIN(creation_date) ;
INSERT INTO hxc_rpt_tc_audit
( resource_id,
tc_start_time,
tc_stop_time,
resource_name,
action,
action_date,
action_by,
comments,
transaction_id,
tc_bb_id,
tc_bb_ovn,
action_type )
VALUES ( p_resource_id,
p_start_time,
p_stop_time,
l_del_name_tab(i),
'Deleted Timecard',
l_del_date_tab(i),
l_del_user_tab(i),
l_del_comments_tab(i),
l_del_id_tab(i),
l_del_bb_id_tab(i),
l_del_bb_ovn_tab(i),
'TSD' );
l_del_comments_tab.DELETE;
l_del_user_tab.DELETE;
l_del_name_tab.DELETE;
l_delete_done BOOLEAN;
IS SELECT *
FROM hxc_rpt_tc_details_all
WHERE resource_id = p_resource_id
AND tc_start_time = p_start_time
AND tc_stop_time = p_stop_time
AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
AND (day_date_to + (2/(24*60*60)))
AND transaction_id IS NOT NULL
AND transaction_detail_id IS NOT NULL
AND status <> 'WORKING'
ORDER BY detail_bb_id,
detail_bb_ovn ;
IS SELECT *
FROM hxc_rpt_tc_details_all
WHERE resource_id = p_resource_id
AND tc_start_time = p_start_time
AND tc_stop_time = p_stop_time
AND creation_date NOT BETWEEN (day_date_to - (2/(24*60*60)))
AND (day_date_to + (2/(24*60*60)))
AND transaction_id IS NOT NULL
ORDER BY detail_bb_id,
detail_bb_ovn ;
PROCEDURE insert_details
AS
BEGIN
-- Private Procedure insert_details
-- Inserts the details collected into audit record pl/sql table, into
-- HXC_RPT_TC_AUDIT.
IF g_debug
THEN
hr_utility.trace('Inserting details into hxc_rpt_tc_audit ');
INSERT INTO hxc_rpt_tc_audit
VALUES l_audit_details(i);
END insert_details ;
l_delete_done := FALSE;
l_delete_done := TRUE;
l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
ELSE -- its deleted (l_tc_details(i).detail_bb_id = l_tc_details(i+1).detail_bb_id
-- is false )
FOR j IN l_trans_id_tab.FIRST..l_trans_id_tab.LAST
LOOP
IF (l_trans_date_tab(j) >= l_tc_details(i).date_to)
AND (l_bb_id_tab(j) = l_tc_details(i).tc_bb_id)
THEN
l_audit_details.EXTEND(1);
l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
l_audit_details(l_audit_cnt).action := 'Deleted';
AND NOT l_delete_done
THEN
l_audit_details.EXTEND(1);
l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
l_audit_details(l_audit_cnt).action := 'Deleted';
l_audit_details(l_audit_cnt).action := 'Deleted';
l_audit_details(l_audit_cnt).action_by := l_tc_details(i).last_updated_by_user;
insert_details;
l_trans_id_tab.DELETE;
l_trans_date_tab.DELETE;
l_trans_user_tab.DELETE;
l_comments_tab.DELETE;
l_bb_id_tab.DELETE;
l_bb_ovn_tab.DELETE;
l_max_bb_id_tab.DELETE;
l_trans_status_tab.DELETE;
l_del_bb_id_tab.DELETE;
l_del_bb_ovn_tab.DELETE;
l_del_date_tab.DELETE;
l_del_id_tab.DELETE;
l_tc_details.DELETE;
l_audit_details.DELETE;
DELETE FROM hxc_rpt_tc_audit;