[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_num_records_updated NUMBER;
SELECT decode(nvl(fl.create_distribution_flag,'N'),
'N', 'I', cp.emp_notification_lookup_code),
decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
decode(cp.emp_notification_lookup_code, 'Y',
fd.status_lookup_code, null)
FROM ap_expense_feed_lines fl,
ap_expense_feed_dists_all fd,
ap_cards_all c,
ap_card_profiles_all cp,
IBY_CREDITCARD IBY
WHERE fl.create_distribution_flag = 'Y' AND
fd.feed_line_id = fl.feed_line_id AND
fd.status_lookup_code =
nvl(p_status_lookup_code,fd.status_lookup_code) AND
fd.status_lookup_code IN ('VALIDATED', 'HOLD', 'REJECTED') AND
fd.employee_verification_id IS NULL AND
fl.card_id = c.card_id AND
c.card_reference_id=IBY.instrid AND
c.profile_id = cp.profile_id AND
(
(p_card_program_id IS NULL) OR
(cp.card_program_id = p_card_program_id)
)
AND (
(p_employee_id IS NULL) OR
(fl.employee_id = p_employee_id)
)
GROUP BY decode(nvl(fl.create_distribution_flag,'N'),
'N', 'I', cp.emp_notification_lookup_code),
decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
decode(cp.emp_notification_lookup_code, 'Y',
fd.status_lookup_code, null),fl.org_id
UNION
SELECT decode(nvl(fl.create_distribution_flag,'N'),
'N', 'I', cp.emp_notification_lookup_code),
decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
decode(cp.emp_notification_lookup_code, 'Y',
fd.status_lookup_code, null)
FROM ap_expense_feed_lines fl,
ap_expense_feed_dists_all fd,
ap_cards_all c,
ap_card_profiles_all cp,
IBY_CREDITCARD IBY
WHERE
nvl(fl.create_distribution_flag,'N') = 'N' AND
fl.employee_verification_id IS NULL AND
fd.feed_line_id(+) = fl.feed_line_id AND
fl.employee_verification_id is null AND
fl.card_id = c.card_id AND
c.card_reference_id=IBY.instrid AND
c.profile_id = cp.profile_id AND
(
(p_card_program_id IS NULL) OR
(cp.card_program_id = p_card_program_id)
) AND
(
(p_employee_id IS NULL) OR
(fl.employee_id = p_employee_id)
)
GROUP BY decode(nvl(fl.create_distribution_flag,'N'),
'N', 'I', cp.emp_notification_lookup_code),
decode(cp.emp_notification_lookup_code, 'N', null, fl.employee_id),
decode(cp.emp_notification_lookup_code, 'Y',
fd.status_lookup_code, null),fl.org_id;
SELECT ap_card_emp_verify_s.nextval
INTO l_new_emp_verification_id
FROM sys.dual;
UPDATE ap_expense_feed_lines fl
SET employee_verification_id = l_new_emp_verification_id
WHERE (create_distribution_flag = 'N' OR
create_distribution_flag IS NULL)
AND employee_verification_id IS NULL
AND EXISTS (SELECT 'feed distribution falls in this workflow'
FROM ap_expense_feed_dists fd,
ap_cards c,
ap_card_profiles cp,
IBY_CREDITCARD IBY
WHERE fl.feed_line_id = fd.feed_line_id
AND c.card_reference_id=IBY.instrid
AND fl.card_id = c.card_id
AND c.profile_id = cp.profile_id
AND cp.emp_notification_lookup_code
= l_employee_notification_method
AND decode(cp.emp_notification_lookup_code, 'N',
1, fl.employee_id) = nvl(l_employee_id,1)
AND decode(cp.emp_notification_lookup_code, 'R',
fd.status_lookup_code,1) = nvl(l_status_lookup_code,1)
AND ((p_card_program_id IS NULL) OR
(cp.card_program_id = p_card_program_id))
AND ((p_employee_id IS NULL) OR
(fl.employee_id = p_employee_id)) );
UPDATE ap_expense_feed_dists fd
SET employee_verification_id = l_new_emp_verification_id
WHERE status_lookup_code in ('VALIDATED', 'HOLD', 'REJECTED')
AND employee_verification_id IS NULL
AND EXISTS (SELECT 'feed distribution falls in this workflow'
FROM ap_expense_feed_lines fl,
ap_cards c,
ap_card_profiles cp,
IBY_CREDITCARD IBY
WHERE fl.feed_line_id = fd.feed_line_id
AND c.card_reference_id=IBY.instrid
AND fl.card_id = c.card_id
AND c.profile_id = cp.profile_id
AND cp.emp_notification_lookup_code
= l_employee_notification_method
AND decode(cp.emp_notification_lookup_code, 'N',
1, fl.employee_id) = nvl(l_employee_id,1));
l_debug_info := 'Make sure records where updated with this verification_id';
SELECT count(*)
INTO l_num_records_updated
FROM ap_expense_feed_lines fl,
ap_expense_feed_dists fd
WHERE fl.employee_verification_id = l_new_emp_verification_id
OR fd.employee_verification_id = l_new_emp_verification_id;
IF (l_num_records_updated > 0) THEN
l_item_key := to_char(l_new_emp_verification_id);
l_num_records_updated NUMBER;
SELECT cp.mgr_approval_lookup_code,
decode(cp.mgr_approval_lookup_code, 'N', null, hr.supervisor_id),
decode(cp.mgr_approval_lookup_code, 'N', null, fl.employee_id)
FROM ap_expense_feed_lines fl,
ap_expense_feed_dists_all fd,
ap_cards_all c,
ap_card_profiles_all cp,
per_employees_x hr,
IBY_CREDITCARD IBY
WHERE fd.status_lookup_code = 'VERIFIED'
AND fd.manager_approval_id IS NULL
AND fd.feed_line_id = fl.feed_line_id
AND fl.card_id = c.card_id
AND c.card_reference_id=IBY.instrid
AND c.profile_id = cp.profile_id
AND fl.employee_id = hr.employee_id
AND (hr.supervisor_id = p_manager_id OR
p_manager_id IS NULL)
GROUP BY cp.mgr_approval_lookup_code,
decode(cp.mgr_approval_lookup_code, 'N', null, hr.supervisor_id),
decode(cp.mgr_approval_lookup_code, 'N', null, fl.employee_id),fd.org_id;
select full_name,
employee_num
into l_log_employee_name,
l_log_employee_num
from hr_employees_current_v
where employee_id = l_employee_id;
SELECT ap_card_mgr_approval_s.nextval
INTO l_new_manager_approval_id
FROM sys.dual;
UPDATE ap_expense_feed_dists fd
SET manager_approval_id = l_new_manager_approval_id
WHERE status_lookup_code = 'VERIFIED'
AND manager_approval_id IS NULL
AND EXISTS (SELECT 'feed distribution falls in this workflow'
FROM ap_expense_feed_lines fl,
ap_cards c,
ap_card_profiles cp,
hr_employees_current_v hr,
IBY_CREDITCARD IBY
WHERE fl.feed_line_id = fd.feed_line_id
AND c.card_reference_id=IBY.instrid
AND fl.card_id = c.card_id
AND c.profile_id = cp.profile_id
AND fl.employee_id = hr.employee_id
AND (hr.supervisor_id = p_manager_id OR
p_manager_id IS NULL)
AND decode(cp.mgr_approval_lookup_code, 'N',
1, hr.supervisor_id) = nvl(l_manager_id,1)
AND decode(cp.mgr_approval_lookup_code, 'N',
1, hr.employee_id) = nvl(l_employee_id,1));
SELECT count(*)
INTO l_num_records_updated
FROM ap_expense_feed_dists
WHERE manager_approval_id = l_new_manager_approval_id;
IF (l_num_records_updated > 0) THEN
l_item_key := to_char(l_new_manager_approval_id);
errbuf := 'No records selected for Manager Approval';
SELECT rowidtochar(ROWID)
INTO c_rowid
FROM AK_FLOW_REGION_RELATIONS
WHERE FROM_REGION_CODE = 'AP_EXP_NOTIFICATIONS'
AND FROM_REGION_APPL_ID = l_application_id
AND FROM_PAGE_CODE = 'AP_EXP_EMP_NOTIFY'
AND FROM_PAGE_APPL_ID = l_application_id
AND TO_PAGE_CODE = 'AP_EXP_FEED_DISTS_OPEN'
AND TO_PAGE_APPL_ID = l_application_id
AND FLOW_CODE = 'AP_CARD_INQUIRIES'
AND FLOW_APPLICATION_ID = l_application_id;
SELECT rowidtochar(ROWID)
INTO c_rowid
FROM AK_FLOW_REGION_RELATIONS
WHERE FROM_REGION_CODE = 'AP_EXP_NOTIFICATIONS'
AND FROM_REGION_APPL_ID = l_application_id
AND FROM_PAGE_CODE = 'AP_EXP_MGR_NOTIFY'
AND FROM_PAGE_APPL_ID = l_application_id
AND TO_PAGE_CODE = 'AP_EXP_FEED_DISTS_HIST'
AND TO_PAGE_APPL_ID = l_application_id
AND FLOW_CODE = 'AP_CARD_INQUIRIES'
AND FLOW_APPLICATION_ID = l_application_id;
SELECT distinct(nvl(fl.org_id,fd.org_id))
INTO l_org_id
FROM ap_expense_feed_lines_all fl,
ap_expense_feed_dists_all fd
WHERE fl.employee_verification_id = p_employee_verification_id
OR (fd.feed_line_id = fl.feed_line_id AND
fd.employee_verification_id = p_employee_verification_id);
SELECT distinct(org_id)
INTO l_org_id
FROM ap_expense_feed_dists_all fd
WHERE fd.manager_approval_id = p_manager_approval_id;
l_debug_info := 'Update Expense Feed Dists';
UPDATE ap_expense_feed_dists
SET status_lookup_code = 'VERIFIED'
WHERE employee_verification_id = l_emp_verification_id
AND (status_lookup_code = l_status_lookup_code
OR l_status_lookup_code IS NULL);
UPDATE ap_expense_feed_dists fd
SET status_lookup_code = 'APPROVED'
WHERE employee_verification_id = l_emp_verification_id
AND status_lookup_code = 'VERIFIED'
AND exists (select 'no manager approval required'
from ap_expense_feed_lines fl,
ap_cards c,
ap_card_profiles cp,
IBY_CREDITCARD IBY
WHERE fl.feed_line_id = fd.feed_line_id
AND c.card_reference_id=IBY.instrid
AND fl.card_id = c.card_id
AND c.profile_id = cp.profile_id
AND nvl(cp.mgr_approval_lookup_code,'N') = 'N');
SELECT count(*)
INTO l_num_dists_not_processed
FROM ap_expense_feed_dists
WHERE employee_verification_id = l_emp_verification_id
AND status_lookup_code = l_orig_status_lookup_code;
SELECT fl.transaction_date,
rpad(merchant_name,30),
fl.amount,
nvl(fl.posted_currency_code, cpr.card_program_currency_code),
fd.description
FROM ap_expense_feed_dists fd,
ap_expense_feed_lines fl,
ap_cards c,
ap_card_profiles cp,
ap_card_programs cpr,
IBY_CREDITCARD IBY
WHERE ((fd.employee_verification_id = l_emp_verification_id AND
fd.feed_line_id = fl.feed_line_id) OR
(fl.employee_verification_id = l_emp_verification_id))
AND fl.card_id = c.card_id
AND c.card_reference_id=IBY.instrid
AND c.profile_id = cp.profile_id
AND cp.card_program_id = cpr.card_program_id;
UPDATE ap_expense_feed_dists
SET status_lookup_code = 'REJECTED'
WHERE manager_approval_id = l_manager_approval_id;
UPDATE ap_expense_feed_dists
SET status_lookup_code = 'APPROVED'
WHERE manager_approval_id = l_manager_approval_id;
SELECT fl.transaction_date,
fl.employee_id,
rpad(merchant_name,30),
fl.amount,
nvl(fl.posted_currency_code, cpr.card_program_currency_code),
fd.description
FROM ap_expense_feed_dists fd,
ap_expense_feed_lines fl,
ap_cards c,
ap_card_profiles cp,
ap_card_programs cpr,
IBY_CREDITCARD IBY
WHERE fd.manager_approval_id = l_manager_approval_id
AND fd.feed_line_id = fl.feed_line_id
AND fl.card_id = c.card_id
AND c.card_reference_id=IBY.instrid
AND c.profile_id = cp.profile_id
AND cp.card_program_id = cpr.card_program_id
ORDER BY fl.employee_id, fl.transaction_date;