[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_audit_code(p_report_header_id IN NUMBER, p_audit_code IN VARCHAR2);
PROCEDURE insert_audit_reason(p_report_header_id IN NUMBER, p_audit_reason_code IN VARCHAR2);
PROCEDURE update_audit_reason(p_report_header_id IN NUMBER, p_audit_reason_code IN VARCHAR2);
SELECT shortpay_parent_id INTO l_parent_report_id
FROM ap_expense_report_headers_all
WHERE report_header_id = p_report_header_id;
SELECT audit_code into l_audit_code
FROM ap_expense_report_headers_all
WHERE report_header_id = l_parent_report_id;
select rs.rule_set_id,
rs.rule_set_type,
rs.description,
rs.start_date,
rs.end_date,
rs.paperless_audit_cc_only_flag,
rs.paperless_audit_violation_flag,
rs.paperless_audit_pdm_only_flag,
rs.assign_auditor_stage_code,
rs.audit_all_violations_flag,
rs.audit_all_from_audit_list_flag,
rs.audit_all_old_receipts_flag,
rs.audit_all_receipt_days_limit,
rs.random_audit_flag,
rs.random_audit_percentage,
rs.ignore_credit_only_flag,
rs.ignore_rj_not_req_only_flag,
rs.auto_approval_tag,
rs.requires_audit_tag,
rs.paperless_audit_tag,
aerh.employee_id,
aerh.audit_code,
rs.rule_set_name,
aerh.org_id,
rs.audit_all_amount_code,
rs.audit_all_amount_limit,
rs.audit_all_amount_currency_code,
rs.audit_all_receipts_code,
rs.audit_all_justification_code,
rs.audit_all_inactive_code,
rs.audit_all_unused_advances
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where aerh.report_header_id = p_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'RULE'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
update_audit_code(p_report_header_id, audit_code);
update_audit_code(p_report_header_id, audit_code);
select audit_reason_code, audit_code
into l_old_audit_reason_code, l_old_audit_code
from
(
select audit_code, audit_reason_code
from ap_expense_report_headers_all aerh,
ap_aud_audit_reasons aud
where aerh.report_header_id = p_report_header_id
and aerh.report_header_id = aud.report_header_id
and aud.audit_reason_code = 'RANDOM'
)
where rownum=1;
update_audit_reason(p_report_header_id, 'RANDOM');
update_audit_code(p_report_header_id, audit_code);
insert_audit_reason(p_report_header_id, 'POLICY_VIOLATION');
select auto_audit_id
from AP_AUD_AUTO_AUDITS
where employee_id = p_employee_id
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(START_DATE,SYSDATE))
AND TRUNC(NVL(END_DATE,SYSDATE));
insert_audit_reason(p_report_header_id, 'AUDIT_LIST');
select min(start_expense_date) oldest_receipt_date
from AP_EXPENSE_REPORT_LINES_ALL
where report_header_id = p_report_header_id;
insert_audit_reason(p_report_header_id, 'OVERDUE_RECEIPTS');
select count(1) required_count
from AP_EXPENSE_REPORT_LINES_ALL
where report_header_id = p_report_header_id
and NVL(receipt_required_flag,'N') = 'Y';
insert_audit_reason(p_report_header_id, 'RECEIPT_REQUIRED');
select count(aerl.report_header_id) required_count
from AP_EXPENSE_REPORT_LINES_ALL aerl
where aerl.report_header_id = p_report_header_id
and ( NVL(aerl.justification_required_flag,'N') = 'Y'
OR (
NVL(aerl.justification_required_flag,'N') = 'V'
AND EXISTS(select 1
from ap_pol_violations_all pv
where pv.report_header_id = aerl.report_header_id
and pv.distribution_line_number = aerl.distribution_line_number
)
)
);
insert_audit_reason(p_report_header_id, 'REQUIRED_JUSTIFICATION');
select AP_WEB_AUDIT_UTILS.is_employee_active(employee_id, org_id) active
from AP_EXPENSE_REPORT_HEADERS_ALL
where report_header_id = p_report_header_id;
insert_audit_reason(p_report_header_id, 'INACTIVE_EMPLOYEE');
SELECT 'Y'
INTO adv_not_applied_flag
FROM dual
WHERE EXISTS(
SELECT 'Y'
FROM ap_expense_report_headers_all XH,
ap_invoices_all AI,
po_vendors PV
WHERE XH.report_header_id = p_report_header_id
AND XH.prepay_num IS NULL
AND XH.advance_invoice_to_apply IS NULL
AND Nvl(XH.maximum_amount_to_apply,0) = 0
AND PV.employee_id = XH.employee_id
AND AI.vendor_id = PV.vendor_id
AND AI.invoice_type_lookup_code = 'PREPAYMENT'
AND AI.earliest_settlement_date <= sysdate
AND AI.payment_status_flag = 'Y'
AND AI.invoice_currency_code = XH.payment_currency_code
AND AP_WEB_PAYMENTS_PKG.get_prepay_amount_remaining(AI.invoice_id,AI.invoice_num,PV.employee_id,XH.default_currency_code,null,null,200) > 0
);
insert_audit_reason(p_report_header_id, 'UNAPPLIED_ADVANCE');
select aerh.default_currency_code,
aerh.total,
sp.default_exchange_rate_type
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
ap_system_parameters_all sp
where aerh.report_header_id = p_report_header_id
and sp.org_id = aerh.org_id;
insert_audit_reason(p_report_header_id, 'AMOUNT');
| This procedure randomly selects expense reports for audit.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
|
| PARAMETERS
| p_report_header_id IN Expense Report identifier
| p_random_audit_percentage IN Probability this report will be audited
| p_ignore_credit_only_flag IN Ignore reports which contain only credit lines
| p_ignore_rj_not_req_only_flag IN Ignore reports which contain only receipts where
| receipt and justification is not required.
| p_audit_report OUT NOCOPY TRUE if report needs to be audited
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Oct-2002 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE process_random_audit(p_report_header_id IN NUMBER,
p_random_audit_percentage IN NUMBER,
p_ignore_credit_only_flag IN VARCHAR2,
p_ignore_rj_not_req_only_flag IN VARCHAR2,
p_audit_report OUT NOCOPY BOOLEAN) IS
CURSOR debit_cur IS
select count(1) debit_line_count
from ap_expense_report_lines_all aerl
where aerl.report_header_id = p_report_header_id
and amount > 0;
insert_audit_reason(p_report_header_id, 'RANDOM');
insert_audit_reason(p_report_header_id, 'RANDOM');
insert_audit_reason(p_report_header_id, p_audit_reason_code);
| PRIVATE PROCEDURE update_audit_code
|
| DESCRIPTION
| This procedure update expense report header with the audit code.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
|
| PARAMETERS
| p_report_header_id IN Expense Report identifier
| p_audit_code IN Audit code, one of the following:
| AUTO_APPROVE
| PAPERLESS_AUDIT
| AUDIT
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Oct-2002 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE update_audit_code(p_report_header_id IN NUMBER,
p_audit_code IN VARCHAR2) IS
BEGIN
IF ( p_report_header_id IS NOT NULL
AND p_audit_code IS NOT NULL) THEN
UPDATE ap_expense_report_headers_all erh
SET audit_code = p_audit_code
WHERE report_header_id = p_report_header_id;
END update_audit_code;
select count(1) non_cc_line_count
from ap_expense_report_lines_all aerl
where aerl.report_header_id = p_report_header_id
and ( CREDIT_CARD_TRX_ID is null
OR ( CREDIT_CARD_TRX_ID is not null
AND NVL(receipt_required_flag,'N') = 'Y'
)
);
select count(1) non_pdm_line_count
from ap_expense_report_lines_all aerl
where aerl.report_header_id = p_report_header_id
and NVL(aerl.category_code,'NONE') not in ('PER_DIEM','MILEAGE');
select count(1) rr_line_count
from ap_expense_report_lines_all aerl
where aerl.report_header_id = p_report_header_id
and nvl(aerl.receipt_required_flag, 'N') = 'Y';
select rs.receipt_delay_rule_flag,
rs.receipt_delay_days,
rs.monthly_total_rule_flag,
rs.monthly_total_allowed,
rs.monthly_violations_rule_flag,
rs.monthly_violations_allowed,
rs.monthly_reports_rule_flag,
rs.monthly_reports_allowed,
rs.audit_term_duration_days,
aerh.employee_id,
rs.rule_set_name,
aerh.org_id,
rs.rule_set_type,
rs.start_date,
rs.end_date,
rs.rule_set_id
from AP_EXPENSE_REPORT_HEADERS_ALL aerh,
AP_AUD_RULE_SETS rs,
AP_AUD_RULE_ASSIGNMENTS_ALL rsa
where aerh.report_header_id = p_report_header_id
and aerh.org_id = rsa.org_id
and rsa.rule_set_id = rs.rule_set_id
and rs.rule_set_type = 'AUDIT_LIST'
and TRUNC(SYSDATE)
BETWEEN TRUNC(NVL(rsa.START_DATE,SYSDATE))
AND TRUNC(NVL(rsa.END_DATE,SYSDATE));
select sum(NVL(aerh.total,0)*NVL(aerh.default_exchange_rate,1)) expense_report_total,
count(aerh.expense_report_id) expense_report_count
from AP_EXPENSE_REPORT_HEADERS_ALL aerh
where employee_id = p_employee_id
and aerh.org_id = p_org_id
and aerh.report_submitted_date > add_months(sysdate,-1)
and ( aerh.source in ('WebExpense', 'SelfService')
or aerh.report_header_id = p_report_header_id)
and exists(select 'Y'
from AP_EXPENSE_REPORT_LINES_ALL aerl
where aerh.report_header_id = aerl.report_header_id);
| This procedure inserts given employee to audit list.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
|
| PARAMETERS
| p_employee_id IN Employee identifier
| p_reason_code IN Reason code
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Oct-2002 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE add_to_audit_list(p_employee_id IN NUMBER,
p_duration IN NUMBER,
p_reason_code IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
SELECT auto_audit_id,
employee_id,
audit_reason_code,
start_date,
end_date
FROM ap_aud_auto_audits
WHERE employee_id = p_employee_id
AND trunc(sysdate) between trunc(start_date) and trunc(NVL(end_date, sysdate))
order by end_date desc;
UPDATE AP_AUD_AUTO_AUDITS
SET END_DATE = ld_end_date, START_DATE = ld_start_date
WHERE AUTO_AUDIT_ID = audit_rec.auto_audit_id;
INSERT INTO AP_AUD_AUTO_AUDITS(
AUTO_AUDIT_ID,
EMPLOYEE_ID,
AUDIT_REASON_CODE,
START_DATE,
END_DATE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
AP_AUD_AUTO_AUDITS_S.nextval,
p_employee_id,
p_reason_code,
SYSDATE,
DECODE(p_duration,
null, null,
SYSDATE+p_duration),
SYSDATE,
nvl(fnd_global.user_id, -1),
fnd_global.conc_login_id,
SYSDATE,
nvl(fnd_global.user_id, -1));
| PRIVATE PROCEDURE insert_audit_reason
|
| DESCRIPTION
| This procedure inserts given audit reason code for a expense report.
|
| Changes to this procedure may require changes to update_audit_reason
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
|
| PARAMETERS
| p_report_header_id IN Expense Report identifier
| p_audit_reson_code IN Audit reason code
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 13-Oct-2002 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE insert_audit_reason(p_report_header_id IN NUMBER,
p_audit_reason_code IN VARCHAR2) IS
--PRAGMA AUTONOMOUS_TRANSACTION;
INSERT INTO AP_AUD_AUDIT_REASONS(
AUDIT_REASON_ID,
REPORT_HEADER_ID,
AUDIT_REASON_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
LAST_UPDATE_DATE,
LAST_UPDATED_BY)
VALUES (
AP_AUD_AUDIT_REASONS_S.nextval,
p_report_header_id,
p_audit_reason_code,
SYSDATE,
nvl(fnd_global.user_id, -1),
fnd_global.conc_login_id,
SYSDATE,
nvl(fnd_global.user_id, -1));
END insert_audit_reason;
| PRIVATE PROCEDURE update_audit_reason
|
| DESCRIPTION
| This procedure updates the given audit reason code entry
| in AP_AUD_AUDIT_REASONS table for an expense report .
|
| Changes to this procedure may require changes to insert_audit_reason
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| Called from BC4J.
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
|
| PARAMETERS
| p_report_header_id IN Expense Report identifier
| p_audit_reson_code IN Audit reason code
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 01-May-2008 STALASIL Created
|
*=======================================================================*/
PROCEDURE update_audit_reason(p_report_header_id IN NUMBER,
p_audit_reason_code IN VARCHAR2) IS
BEGIN
IF (p_report_header_id IS NOT NULL AND p_audit_reason_code IS NOT NULL) THEN
UPDATE AP_AUD_AUDIT_REASONS
SET LAST_UPDATE_LOGIN = fnd_global.conc_login_id,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATED_BY = nvl(fnd_global.user_id, -1)
WHERE report_header_id = p_report_header_id
AND AUDIT_REASON_CODE = p_audit_reason_code ;
END update_audit_reason;
select count(1) violation_count
from AP_POL_VIOLATIONS_ALL
where report_header_id = p_report_header_id;
select count(1) violation_count
from AP_POL_VIOLATIONS_ALL viol,
AP_EXPENSE_REPORT_HEADERS_ALL aerh
where aerh.employee_id = p_employee_id
and viol.report_header_id = aerh.report_header_id
and aerh.week_end_date > add_months(sysdate,-p_months);
SELECT AERL.*
FROM AP_EXPENSE_REPORT_LINES_ALL AERL
WHERE REPORT_HEADER_ID = p_report_header_id
AND (itemization_parent_id is null OR itemization_parent_id <> -1)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
* Line was adjusted, Update the CC transaction with the adjustment for credit card line
*/
IF report_lines_rec.CREDIT_CARD_TRX_ID IS NOT NULL THEN
update_cc_transaction(report_lines_rec);
| PUBLIC PROCEDURE update_cc_transaction
|
| DESCRIPTION
| This procedure updates the CC transaction amounts to match the
| amounts on the expense line.
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| process_audit_actions
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
|
| RETURNS
| None
| PARAMETERS
| Expense line record containing the data on the modified expense line
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 21-Jul-2003 J Rautiainen Created
|
*=======================================================================*/
PROCEDURE update_cc_transaction(expense_line_rec IN AP_EXPENSE_REPORT_LINES_ALL%ROWTYPE) IS
CURSOR trx_c(p_trx_id IN NUMBER) IS
SELECT cct.*
FROM AP_CREDIT_CARD_TRXNS_ALL cct
WHERE cct.trx_id = p_trx_id
FOR UPDATE OF TRX_ID NOWAIT;
SELECT sum(amount) total_amount
FROM AP_EXPENSE_REPORT_LINES_ALL
WHERE report_header_id = p_report_header_id
AND web_parameter_id <> p_personal_expense_id
AND credit_card_trx_id = p_trx_id
AND (itemization_parent_id is null OR itemization_parent_id <> -1);
UPDATE AP_CREDIT_CARD_TRXNS_ALL
SET expensed_amount = total_rec.total_amount
WHERE CURRENT OF trx_c;
UPDATE AP_CREDIT_CARD_TRXNS_ALL
SET category = 'PERSONAL'
WHERE trx_id = expense_line_rec.credit_card_trx_id;
END update_cc_transaction;
UPDATE AP_EXPENSE_REPORT_LINES_ALL
SET policy_shortpay_flag = DECODE(expense_line_rec.policy_shortpay_flag,
'Y','Y',
policy_shortpay_flag),
receipt_verified_flag = DECODE(NVL(expense_line_rec.receipt_verified_flag,'N'),
'N','N',
receipt_verified_flag)
WHERE report_header_id = expense_line_rec.report_header_id
AND (itemization_parent_id = expense_line_rec.itemization_parent_id
OR
/* Deal with itemized personal CC line */
( expense_line_rec.credit_card_trx_id IS NOT NULL
AND credit_card_trx_id = expense_line_rec.credit_card_trx_id)
);
SELECT itemization_parent_id,
credit_card_trx_id,
sum(amount) total_amount,
sum(daily_amount
* NVL((end_expense_date - start_expense_date)+1,1)
* NVL(receipt_conversion_rate,1)) total_daily_amount,
currency_code
FROM ap_expense_report_lines_all
WHERE report_header_id = p_report_header_id
AND currency_code <> NVL(receipt_currency_code,currency_code)
AND web_parameter_id <> p_personal_expense_id
AND web_parameter_id <> p_rounding_expense_id
AND itemization_parent_id is not null
AND itemization_parent_id <> -1 -- do not include parent line
GROUP BY itemization_parent_id, credit_card_trx_id, currency_code;
SELECT AERL.*
FROM AP_EXPENSE_REPORT_LINES_ALL AERL
WHERE REPORT_HEADER_ID = p_report_header_id
AND WEB_PARAMETER_ID = p_rounding_expense_id
AND ITEMIZATION_PARENT_ID = p_itemization_parent_id
AND ( (p_trx_id IS NULL AND credit_card_trx_id is NULL)
OR
(p_trx_id IS NOT NULL AND credit_card_trx_id IS NOT NULL)
)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
SELECT AERL.*
FROM AP_EXPENSE_REPORT_LINES_ALL AERL
WHERE REPORT_HEADER_ID = p_report_header_id
AND WEB_PARAMETER_ID <> p_personal_expense_id
AND ITEMIZATION_PARENT_ID = p_itemization_parent_id
AND ( (p_trx_id IS NULL AND credit_card_trx_id is NULL)
OR
(p_trx_id IS NOT NULL AND credit_card_trx_id IS NOT NULL)
)
ORDER BY distribution_line_number;
SELECT parameter_id,
LINE_TYPE_LOOKUP_CODE,
category_code, prompt
FROM ap_expense_report_params_all
WHERE parameter_id = p_parameter_id;
SELECT REPORT_HEADER_ID
FROM AP_EXP_REPORT_DISTS
WHERE (REPORT_LINE_ID = p_report_line_id)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
UPDATE AP_EXPENSE_REPORT_LINES_ALL
SET amount = ln_rounding_amount
WHERE CURRENT OF rounding_line_c;
new_report_line_rec.last_update_login := FND_GLOBAL.login_id;
new_report_line_rec.last_updated_by := FND_GLOBAL.user_id;
new_report_line_rec.last_update_date := SYSDATE;
AP_WEB_DB_EXPLINE_PKG.InsertLine(new_report_line_rec);
AP_WEB_DB_EXPDIST_PKG.updateAccountValues (
p_report_header_id => rounding_line_rec.report_header_id,
p_report_line_id => rounding_line_rec.report_line_id,
p_report_distribution_id => null,
p_ccid => l_exp_line_ccid);
DELETE AP_EXPENSE_REPORT_LINES_ALL
WHERE CURRENT OF rounding_line_c;
DELETE AP_EXP_REPORT_DISTS_ALL
WHERE CURRENT OF distribution_c;
SELECT COUNT(REPORT_LINE_ID) into line_count
FROM AP_EXPENSE_REPORT_LINES_ALL
WHERE REPORT_HEADER_ID = p_report_header_id;