The following lines contain the word 'select', 'insert', 'update' or 'delete':
if (upper(p_action) = 'UPDATE' OR upper(p_action) = 'DELETE') then
--Get the mandatory data to create a new row with the above action.
l_stage := 'Getting ready to UPDATE or DELETE';
select VENDOR_ID, VENDOR_SITE_ID, PO_HEADER_ID, PO_NUMBER, PO_LINE_ID, PO_LINE_NUMBER,
ORG_ID, PO_CREATION_DATE,
PO_CONTRACTOR_FULL_NAME, TC_UOM, TC_APPROVED_DATE,
TC_START_DATE, TC_ENTRY_DATE, TC_END_DATE,
CONTINGENT_WORKER_ID, LINE_RATE_TYPE, LINE_RATE, LINE_RATE_CURRENCY
into l_vendor_id, l_vendor_site_id, l_po_header_id, l_po_number,
l_po_line_id, l_po_line_number,
l_org_id, l_po_creation_date,
l_contractor_full_name, l_tc_uom, l_tc_approved_date,
l_tc_start_date, l_tc_entry_date, l_tc_end_date,
l_contingent_worker_id, l_line_rate_type, l_line_rate, l_po_currency
from po_retrieved_timecards
where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and
tc_id = p_tc_id and rownum = 1; --all we need is mandatory data; so first row is sufficient.
l_stage := 'Got data for UPDATE or DELETE';
IF ( (NOT (upper(p_action) = 'UPDATE' OR upper(p_action) = 'DELETE') ) OR
l_get_fresh = 'Y') THEN
l_action := 'I';
l_stage := 'Getting ready for INSERT';
select po_header_id, currency_code, creation_date,
vendor_id, vendor_site_id
into l_po_header_id, l_po_currency, l_po_creation_date,
l_vendor_id, l_vendor_site_id
from po_headers_all
where segment1 = p_po_num and org_id = p_org_id;
SELECT po_line_id
INTO l_PO_LINE_ID
FROM po_lines_all pla
WHERE pla.po_header_id = l_po_header_id
AND pla.line_num = p_po_line_number;
SELECT ptlv.rate_value
INTO l_line_rate
FROM po_temp_labor_rates_v ptlv, po_lines_all pla
WHERE ptlv.po_line_id = pla.po_line_id
AND ptlv.asg_rate_type = p_line_rate_type
AND pla.po_header_id = l_po_header_id
AND pla.line_num = p_po_line_number;
select first_name || last_name contractor_full_name,
first_name, last_name
into l_contractor_full_name,
l_contractor_first_name, l_contractor_last_name
from per_all_people_f
where person_id = p_contingent_worker_id and
sysdate between effective_start_date and effective_end_date;
select SEGMENT1
into l_project_name
from PA_PROJECTS_ALL
where project_id = p_project_id;
select TASK_NUMBER
into l_task_number
from pa_tasks_expend_v
where project_id = p_project_id and task_id = p_task_id;
/* set the local vars right to get ready to insert */
l_stage := 'Getting some misc data before INSERT';
end if; --end of if insert
if (upper(p_action) = 'UPDATE') then
l_stage := 'updating';
update PO_RETRIEVED_TIMECARDS
set tc_time_received = p_tc_time_received, tc_comment_text = p_tc_comment_text,
line_rate_type = p_line_rate_type, line_rate = p_line_rate
where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and tc_id = p_tc_id;
elsif (upper(p_action) = 'DELETE') then
l_stage := 'deleting';
delete PO_RETRIEVED_TIMECARDS
where tc_detail_id = p_tc_detail_id and tc_day_id = p_tc_day_id and tc_id = p_tc_id;
select po_timecards_entry_s.nextval into l_tc_entry_seq from dual;
l_stage := 'inserting';
insert into PO_RETRIEVED_TIMECARDS
(
PO_HEADER_ID,
PO_NUMBER,
PO_LINE_ID,
PO_LINE_NUMBER,
ORG_ID,
PO_CREATION_DATE,
PO_CONTRACTOR_FULL_NAME,
PROJECT_ID,
PROJECT_NAME,
TASK_ID,
TASK_NAME,
TC_ID,
TC_DAY_ID,
TC_DETAIL_ID,
TC_SCOPE,
TC_UOM,
TC_START_DATE,
TC_END_DATE,
TC_ENTRY_DATE,
TC_TIME_RECEIVED,
TC_SUBMISSION_DATE,
TC_APPROVED_DATE,
TC_APPROVAL_STATUS,
CONTINGENT_WORKER_ID,
TC_COMMENT_TEXT,
LINE_RATE_TYPE,
LINE_RATE,
LINE_RATE_CURRENCY,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
PO_CONTRACTOR_FIRST_NAME,
PO_CONTRACTOR_LAST_NAME,
INTERFACE_TRANSACTION_ID,
ACTION_FLAG,
TC_ENTRY_SEQUENCE
)
values
(
l_PO_HEADER_ID,
l_PO_NUMBER,
l_PO_LINE_ID,
l_PO_LINE_NUMBER,
l_ORG_ID,
l_po_creation_date,
l_CONTRACTOR_FULL_NAME,
p_PROJECT_ID,
l_PROJECT_NAME,
p_TASK_ID,
l_TASK_NUMBER,
p_TC_ID,
p_TC_DAY_ID,
p_TC_DETAIL_ID,
'DETAIL',
l_TC_UOM,
l_TC_START_DATE,
l_TC_END_DATE,
l_TC_ENTRY_DATE,
p_TC_TIME_RECEIVED,
p_TC_SUBMISSION_DATE,
l_tc_approved_date,
p_TC_APPROVAL_STATUS,
l_CONTINGENT_WORKER_ID,
p_TC_COMMENT_TEXT,
l_LINE_RATE_TYPE,
l_LINE_RATE,
l_PO_CURRENCY,
l_VENDOR_ID,
l_VENDOR_SITE_ID,
p_VENDOR_CONTACT_ID,
l_contractor_first_name,
l_contractor_last_name,
p_interface_transaction_id,
l_action,
l_tc_entry_seq
);
select /*+ PO_RETRIEVED_TIMECARDS_N6 */ tc_id, tc_day_id, tc_detail_id,
action_flag, tc_time_received,
tc_comment_text, interface_transaction_id
from po_retrieved_timecards
where action_flag in ('I', 'U', 'D')
order by TC_ENTRY_SEQUENCE;
delete /*+ PO_RETRIEVED_TIMECARDS_N6 */po_retrieved_timecards prt
where action_flag in ('I', 'U', 'D') and
not exists (select interface_transaction_id from rcv_transactions
where interface_transaction_id = prt.interface_transaction_id);
update po_retrieved_timecards
set action_flag = 'P'
where tc_id = l_tc_id and
tc_day_id = l_tc_day_id and
tc_detail_id = l_tc_detail_id and
action_flag = 'I';
update po_retrieved_timecards
set tc_time_received = l_tc_time_received,
tc_comment_text = l_tc_comment_text
where tc_id = l_tc_id and
tc_day_id = l_tc_day_id and
tc_detail_id = l_tc_detail_id and
action_flag = 'P';
update po_retrieved_timecards
set action_flag = 'DP'
where tc_id = l_tc_id and
tc_day_id = l_tc_day_id and
tc_detail_id = l_tc_detail_id and
action_flag = 'P';
delete po_retrieved_timecards
where action_flag in ('U', 'D');
if (upper(p_action) = 'UPDATE') then
forall i in p_rtrvd_tcs.po_number.first..p_rtrvd_tcs.po_number.last
SAVE EXCEPTIONS
update PO_RETRIEVED_TIMECARDS
set tc_time_received = p_rtrvd_tcs.tc_time_received(i),
tc_comment_text = p_rtrvd_tcs.tc_comment_text(i),
line_rate_type = p_rtrvd_tcs.line_rate_type(i),
line_rate = p_rtrvd_tcs.line_rate(i)
where tc_detail_id = p_rtrvd_tcs.tc_detail_id(i) and
tc_day_id = p_rtrvd_tcs.tc_day_id(i) and
tc_id = p_rtrvd_tcs.tc_id(i);
elsif (upper(p_action) = 'DELETE') then
forall i in p_rtrvd_tcs.po_number.first..p_rtrvd_tcs.po_number.last
SAVE EXCEPTIONS
delete PO_RETRIEVED_TIMECARDS
where tc_detail_id = p_rtrvd_tcs.tc_detail_id(i) and
tc_day_id = p_rtrvd_tcs.tc_day_id(i) and
tc_id = p_rtrvd_tcs.tc_id(i);
insert into PO_RETRIEVED_TIMECARDS
(
PO_HEADER_ID,
PO_NUMBER,
PO_LINE_ID,
PO_LINE_NUMBER,
ORG_ID,
PO_APPROVED_DATE,
PO_CONTRACTOR_FULL_NAME,
PROJECT_ID,
PROJECT_NAME,
TASK_ID,
TASK_NAME,
TC_ID,
TC_DAY_ID,
TC_DETAIL_ID,
TC_SCOPE,
TC_UOM,
TC_START_DATE,
TC_END_DATE,
TC_ENTRY_DATE,
TC_TIME_RECEIVED,
TC_SUBMISSION_DATE,
TC_APPROVED_DATE,
TC_APPROVAL_STATUS,
CONTINGENT_WORKER_ID,
TC_COMMENT_TEXT,
LINE_RATE_TYPE,
LINE_RATE,
LINE_RATE_CURRENCY,
VENDOR_ID,
VENDOR_SITE_ID,
VENDOR_CONTACT_ID,
PO_CONTRACTOR_FIRST_NAME,
PO_CONTRACTOR_LAST_NAME
)
values
(
p_rtrvd_tcs.PO_HEADER_ID(i),
p_rtrvd_tcs.PO_NUMBER(i),
p_rtrvd_tcs.PO_LINE_ID(i),
p_rtrvd_tcs.PO_LINE_NUMBER(i),
p_rtrvd_tcs.ORG_ID(i),
p_rtrvd_tcs.po_CREATION_DATE(i),
p_rtrvd_tcs.CONTRACTOR_FULL_NAME(i),
p_rtrvd_tcs.PROJECT_ID(i),
p_rtrvd_tcs.PROJECT_NAME(i),
p_rtrvd_tcs.TASK_ID(i),
p_rtrvd_tcs.TASK_NUMBER(i),
p_rtrvd_tcs.TC_ID(i),
p_rtrvd_tcs.TC_DAY_ID(i),
p_rtrvd_tcs.TC_DETAIL_ID(i),
'DETAIL',
p_rtrvd_tcs.TC_UOM(i),
p_rtrvd_tcs.TC_START_DATE(i),
p_rtrvd_tcs.TC_END_DATE(i),
p_rtrvd_tcs.TC_ENTRY_DATE(i),
p_rtrvd_tcs.TC_TIME_RECEIVED(i),
p_rtrvd_tcs.TC_SUBMISSION_DATE(i),
p_rtrvd_tcs.TC_APPROVAL_DATE(i),
p_rtrvd_tcs.TC_APPROVAL_STATUS(i),
p_rtrvd_tcs.CONTINGENT_WORKER_ID(i),
p_rtrvd_tcs.TC_COMMENT_TEXT(i),
p_rtrvd_tcs.LINE_RATE_TYPE(i),
p_rtrvd_tcs.LINE_RATE(i),
p_rtrvd_tcs.PO_CURRENCY(i),
p_rtrvd_tcs.VENDOR_ID(i),
p_rtrvd_tcs.VENDOR_SITE_ID(i),
p_rtrvd_tcs.VENDOR_CONTACT_ID(i),
p_rtrvd_tcs.contractor_first_name(i),
p_rtrvd_tcs.contractor_last_name(i)
);