The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT resource_id,source_email INTO x_resource_id,x_user_email
FROM jtf_rs_resource_extns
WHERE user_id = fnd_global.user_id;
SELECT p.payrun_id,p.pay_period_id INTO x_payrun_id,x_period_id
FROM cn_payment_worksheets w,cn_payruns p
WHERE w.payment_worksheet_id = p_worksheet_id
AND p.payrun_id = w.payrun_id
--R12
AND p.org_id = w.org_id
;
SELECT DISTINCT m1.parent_resource_id mgr_resource_id,
re1.source_email mgr_email,re1.user_id mgr_user_id
FROM cn_period_statuses pr,
jtf_rs_group_usages u1, jtf_rs_rep_managers m1,
jtf_rs_resource_extns re1
WHERE pr.period_id = p_period_id
AND pr.org_id = c_org_id
AND u1.usage = 'COMP_PAYMENT'
AND m1.resource_id = p_resource_id
AND ((m1.start_date_active <= pr.end_date) AND
(pr.start_date <= Nvl(m1.end_date_active,pr.start_date)))
AND u1.group_id = m1.group_id
AND m1.hierarchy_type IN ('MGR_TO_MGR','MGR_TO_REP')
AND m1.category <> 'TBH'
AND (m1.reports_to_flag = 'Y' -- Bug 2819874
OR (m1.reports_to_flag = 'N' AND m1.denorm_level = 1))
AND re1.resource_id = m1.parent_resource_id
;
SELECT org_id
INTO p_org_id
FROM cn_payruns
WHERE payrun_id = p_payrun_id;
SELECT cn_pay_approval_flow_s.NEXTVAL
INTO l_flow_rec.pay_approval_flow_id FROM dual;
l_flow_rec.updated_by_resource_id := p_resource_id;
cn_pay_approval_flow_pkg.insert_row
( p_pay_approval_flow_rec => l_flow_rec);
SELECT cn_pay_approval_flow_s.NEXTVAL
INTO l_flow_rec.pay_approval_flow_id FROM dual;
l_flow_rec.updated_by_resource_id := p_resource_id;
cn_pay_approval_flow_pkg.insert_row
( p_pay_approval_flow_rec => l_flow_rec);
DELETE FROM cn_pay_approval_flow
WHERE payment_worksheet_id = p_worksheet_id;
UPDATE cn_pay_approval_flow
SET approval_status = 'APPROVED', updated_by_resource_id = l_resource_id,
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE payment_worksheet_id = p_worksheet_id
;
UPDATE cn_pay_approval_flow
SET approval_status = 'REJECTED', updated_by_resource_id = l_resource_id,
last_updated_by = fnd_global.user_id,
last_update_date = Sysdate,
last_update_login = fnd_global.login_id
WHERE payment_worksheet_id = p_worksheet_id
;
DELETE FROM cn_pay_approval_flow
WHERE payrun_id = p_payrun_id
;