[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
into l_is_notif_rule_setup
from AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where rsa.org_id = p_org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = C_NOTIFY_RULE
and TRUNC(p_report_submitted_date)
BETWEEN TRUNC(NVL(rsa.START_DATE, p_report_submitted_date))
AND TRUNC(NVL(rsa.END_DATE, p_report_submitted_date))
and rownum = 1;
select report_submitted_date
into l_report_submitted_date
from ap_expense_report_headers_all
where report_header_id = l_expense_report_id;
select to_char(sysdate, 'DD-MON-RRRR HH:MI:SS')
into l_timestamp
from dual;
select 'Y'
into l_event_key_exists
from wf_items
where item_type = C_APWRECPT
and item_key = p_event_key
and rownum = 1;
l_debug_info := 'Update Receipts Status if not Received or Waived';
update ap_expense_report_headers_all
set receipts_status = ''
where report_header_id = p_expense_report_id
and nvl(receipts_status, AP_WEB_RECEIPTS_WF.C_NOT_REQUIRED) not in (AP_WEB_RECEIPTS_WF.C_RECEIVED, AP_WEB_RECEIPTS_WF.C_RECEIVED_RESUBMITTED, AP_WEB_RECEIPTS_WF.C_WAIVED)
and receipts_received_date is null;
update ap_expense_report_headers_all
set expense_status_code = C_PENDING_HOLDS,
holding_report_header_id = null,
expense_last_status_date = sysdate
where holding_report_header_id = p_expense_report_id
and expense_status_code = C_PAYMENT_HELD;
select created_by,
employee_id,
invoice_num,
flex_concatenated,
to_char(nvl(AMT_DUE_CCARD_COMPANY,0)+nvl(AMT_DUE_EMPLOYEE,0),
FND_CURRENCY.Get_Format_Mask(default_currency_code,22))||' '||default_currency_code,
description,
report_submitted_date
into l_created_by,
l_employee_id,
l_invoice_num,
l_cost_center,
l_total,
l_purpose,
l_report_submitted_date
from ap_expense_report_headers_all
where report_header_id = l_expense_report_id;
select item_key
into l_found_item_key
from wf_items
where item_type = p_item_type
and item_key like l_item_key
and end_date is null
and rownum = 1;
select item_key
into l_found_item_key
from wf_items
where item_type = p_item_type
and item_key like l_item_key
and end_date is null
and rownum = 1;
select item_key
from wf_items
where item_type = p_item_type
and item_key like l_item_key
and end_date is null;
select receipts_status
into l_receipts_status
from ap_expense_report_headers_all
where report_header_id = p_report_header_id;
select receipts_status
into l_orig_receipts_status
from ap_expense_report_headers_all
where report_header_id = p_report_header_id
for update of receipts_status nowait;
l_debug_info := 'Update current Receipt Status';
update ap_expense_report_headers_all
set receipts_status = p_receipts_status
where report_header_id = p_report_header_id;
l_debug_info := 'Update current Receipt Status';
l_debug_info := 'Update current Receipt Status to Overdue if In Transit';
select 'Y'
into l_is_shortpay
from ap_expense_report_headers_all aerh,
wf_items wf
where aerh.report_header_id = l_report_header_id
and aerh.shortpay_parent_id is not null
and wf.item_type = C_APEXP
and wf.item_key = to_char(aerh.report_header_id) -- Bug 6841589 (sodash) to solve the invalid number exception
and wf.end_date is null
and wf.root_activity = p_shortpay_type
and rownum = 1;
select nls_language
into l_orig_language
from fnd_languages
where language_code = l_orig_language_code;
select note_language_code
into l_new_language_code
from ap_expense_params_all
where org_id = l_org_id;
select language_code
into l_new_language_code
from fnd_languages
where installed_flag in ('B');
select nls_language
into l_new_language
from fnd_languages
where language_code = l_new_language_code;
select created_by
into l_created_by
from ap_expense_report_headers_all
where report_header_id = l_report_header_id;
select message_text
into l_note_prefix
from fnd_new_messages
where application_id = 200
and message_name = l_message_name
and language_code = l_new_language_code;
select aerh.report_header_id,
aerh.receipts_status
from AP_EXPENSE_REPORT_HEADERS aerh,
AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS rsa
where aerh.receipts_status in (C_REQUIRED, C_MISSING)
and aerh.bothpay_parent_id is null
and rsa.org_id = nvl(p_org_id, rsa.org_id)
and rsa.org_id = aerh.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = C_NOTIFY_RULE
and TRUNC(aerh.report_submitted_date)
BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
and
(
(aerh.RECEIPTS_STATUS = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
or
(aerh.RECEIPTS_STATUS = C_MISSING and rs.NOTIFY_DOCUMENT_REQUIRED_CODE = C_REQUIRED and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.NOTIFY_RCT_OVERDUE_DAYS) > 0)
)
and not exists
(select 1 from wf_items where aerh.RECEIPTS_STATUS = C_REQUIRED and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.overdue%' and end_date is null and rownum=1)
and not exists
(select 1 from wf_items where aerh.RECEIPTS_STATUS = C_MISSING and item_type = 'APWRECPT' and item_key like to_char(aerh.report_header_id)||':receipts.missing%' and end_date is null and rownum=1);
select aerh.employee_id,
hr.business_group_id,
max(rs.audit_term_duration_days)
from AP_EXPENSE_REPORT_HEADERS aerh,
hr_organization_units hr,
AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS rsa
where aerh.org_id = nvl(p_org_id, aerh.org_id)
and aerh.bothpay_parent_id is null
and aerh.report_submitted_date is not null
and hr.organization_id = aerh.org_id
and rsa.org_id = aerh.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'AUDIT_LIST'
and rs.receipt_delay_rule_flag = 'Y'
and TRUNC(aerh.report_submitted_date)
BETWEEN TRUNC(NVL(rsa.START_DATE, aerh.report_submitted_date))
AND TRUNC(NVL(rsa.END_DATE, aerh.report_submitted_date))
and aerh.receipts_status in ('REQUIRED', 'MISSING', 'OVERDUE', 'IN_TRANSIT', 'RESOLUTN')
and trunc(sysdate) - (trunc(aerh.report_submitted_date) + rs.receipt_delay_days) > 0
group by employee_id, business_group_id;
l_debug_info := 'Update current Receipt Status';