[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT employee_id
INTO p_employee_id
FROM AP_EXPENSE_REPORT_HEADERS
WHERE report_header_id = p_bothpay_parent_id;
SELECT ap_erh.REPORT_HEADER_ID,
ap_erh.INVOICE_NUM,
hr_empcur.FULL_NAME,
ap_erh.WEEK_END_DATE,
ap_erh.DEFAULT_CURRENCY_CODE,
ap_erh.DESCRIPTION,
ap_erh.TOTAL,
fndl.MEANING
FROM AP_EXPENSE_REPORT_HEADERS ap_erh,
PER_PEOPLE_X hr_empcur,
FND_LOOKUPS fndl
WHERE (ap_erh.EMPLOYEE_ID IN
(SELECT NUMBER_VALUE
FROM AK_WEB_USER_SEC_ATTR_VALUES
WHERE ATTRIBUTE_CODE = C_UserAttributeCode AND
WEB_USER_ID = P_WebUserID))
AND ap_erh.SOURCE = C_RestorableReportSource
AND ap_erh.WORKFLOW_APPROVED_FLAG IN
(C_WORKFLOW_APPROVED_SAVED, C_WORKFLOW_APPROVED_REJECTED,
--ER 1552747 - withdraw expense report
C_WORKFLOW_APPROVED_RETURNED, C_WORKFLOW_APPROVED_WITHDRAW)
AND ap_erh.EMPLOYEE_ID = hr_empcur.PERSON_ID
AND fndl.LOOKUP_TYPE = 'YES_NO'
AND DECODE(WORKFLOW_APPROVED_FLAG, C_WORKFLOW_APPROVED_REJECTED, 'Y', 'N') = fndl.LOOKUP_CODE
ORDER BY hr_empcur.FULL_NAME, ap_erh.REPORT_HEADER_ID;
SELECT INVOICE_NUM,
WORKFLOW_APPROVED_FLAG
INTO P_WorkflowRec.doc_num,
P_WorkflowRec.workflow_flag
FROM AP_EXPENSE_REPORT_HEADERS
WHERE REPORT_HEADER_ID = P_ReportID;
SELECT employee_id,
default_currency_code,
invoice_num,
total,
payment_currency_code,
week_end_date
INTO p_exp_info_rec.emp_id,
p_exp_info_rec.default_curr_code,
p_exp_info_rec.doc_num,
p_exp_info_rec.total,
p_exp_info_rec.payment_curr_code,
p_exp_info_rec.week_end_date
FROM ap_expense_report_headers
WHERE report_header_id = p_expenseReportId;
SELECT override_approver_id
INTO p_id
FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id;
SELECT org_id
INTO p_org_id
FROM ap_expense_report_headers_all
WHERE report_header_id = p_header_id;
SELECT exp.employee_id,
exp.flex_concatenated,
exp.attribute_category,
exp.attribute1,
exp.attribute2,
exp.attribute3,
exp.attribute4,
exp.attribute5,
exp.attribute6,
exp.attribute7,
exp.attribute8,
exp.attribute9,
exp.attribute10,
exp.attribute11,
exp.attribute12,
exp.attribute13,
exp.attribute14,
exp.attribute15
INTO p_emp_id,
p_flex_concat,
p_attr_category,
p_attr1,
p_attr2,
p_attr3,
p_attr4,
p_attr5,
p_attr6,
p_attr7,
p_attr8,
p_attr9,
p_attr10,
p_attr11,
p_attr12,
p_attr13,
p_attr14,
p_attr15
FROM ap_expense_report_headers exp
WHERE exp.report_header_id = p_report_header_id;
SELECT TO_CHAR(expense_report_id),
TO_CHAR(week_end_date),
description,
default_currency_code,
flex_concatenated,
TO_CHAR(override_approver_id),
override_approver_name,
employee_id,
TO_CHAR(last_update_date,AP_WEB_DB_UTIL_PKG.C_DetailedDateFormat)
INTO P_ExpHdrRec.template_id,
P_ExpHdrRec.last_receipt_date,
P_ExpHdrRec.description,
P_ExpHdrRec.default_curr_code,
P_ExpHdrRec.flex_concat,
P_ExpHdrRec.override_appr_id,
P_ExpHdrRec.override_appr_name,
P_ExpHdrRec.emp_id,
P_ExpHdrRec.last_update_date
FROM AP_EXPENSE_REPORT_HEADERS
WHERE REPORT_HEADER_ID = P_ReportID
AND SOURCE = C_RestorableReportSource;
SELECT nvl(default_exchange_rate,1),
nvl(precision,0)
INTO p_exch_rate,
p_reimb_precision
FROM fnd_currencies_vl,
ap_expense_report_headers
WHERE report_header_id = p_report_id
AND currency_code = default_currency_code;
SELECT ap_expense_report_headers_s.nextval
INTO p_new_report_id
FROM sys.dual;
SELECT ap_expense_report_headers_s.nextval
INTO p_new_report_id
FROM dual;
SELECT nvl(S.apply_advances_default, 'N'),
nvl(S.allow_awt_flag, 'N'),
decode(S.base_currency_code, RH.default_currency_code, null,
S.default_exchange_rate_type),
nvl(S.make_rate_mandatory_flag, 'N'),
RH.default_currency_code,
week_end_date,
flex_concatenated,
RH.employee_id
INTO p_sys_apply_advances_default,
p_sys_allow_awt_flag,
p_sys_default_xrate_type,
p_sys_make_rate_mandatory,
p_default_currency_code,
p_week_end_date,
p_flex_concatenated,
p_employee_id
FROM ap_system_parameters S,
ap_expense_report_headers RH
WHERE RH.report_header_id = p_report_header_id;
SELECT expense_check_address_flag
INTO l_exp_check_address_flag
FROM (
SELECT emp.expense_check_address_flag
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.expense_check_address_flag
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
);
SELECT expense_check_address_flag
INTO l_fin_exp_check_address_flag
FROM financials_system_parameters;
SELECT DISTINCT icc.masked_cc_number || '/' || emp.full_name,
erh.description,
emp.full_name,
erh.amt_due_ccard_company,
erh.total
INTO l_emp_masked_cc_number,
l_exp_rpt_purpose,
l_emp_full_name,
p_ccard_amt,
p_total
FROM ap_expense_report_headers erh,
ap_credit_card_trxns cc,
/* hr_employees emp Bug 3006221 */
per_people_f emp,
ap_cards aca,
iby_creditcard icc
WHERE cc.card_id = aca.card_id
AND aca.card_reference_id = icc.instrid
AND erh.report_header_id = p_report_header_id
AND cc.report_header_id = p_report_header_id
AND erh.employee_id = emp.person_id
AND TRUNC(sysdate) BETWEEN emp.effective_start_date
AND emp.effective_end_date /* Bug 3111161 */
AND cc.category='BUSINESS';
SELECT shortpay_parent_id
INTO l_shortpay_id
FROM ap_expense_report_headers
WHERE report_header_id = P_ReportID;
FUNCTION InsertReportHeader(p_xpense_rec IN XpenseInfoRec,
p_ExpReportHeaderInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec
) RETURN BOOLEAN IS
-------------------------------------------------------------------
l_curr_calling_sequence VARCHAR2(100) := 'AddReportHeader';
INSERT INTO AP_EXPENSE_REPORT_HEADERS
(report_header_id,
employee_id,
override_approver_id,
override_approver_name,
week_end_date,
vouchno,
total,
invoice_num,
expense_report_id,
set_of_books_id,
source,
description,
flex_concatenated,
default_currency_code,
payment_currency_code, --1396360
creation_date,
created_by,
last_update_date,
last_updated_by,
workflow_approved_flag,
amt_due_employee,
amt_due_ccard_company,
org_id)
VALUES
(p_xpense_rec.report_header_id,
p_ExpReportHeaderInfo.employee_id,
p_xpense_rec.approver_id,
p_ExpReportHeaderInfo.override_approver_name,
p_xpense_rec.week_end_date,
p_xpense_rec.vouchno,
p_xpense_rec.total,
p_xpense_rec.document_number,
to_number(p_ExpReportHeaderInfo.template_id),
p_xpense_rec.set_of_books_id,
p_xpense_rec.source,
AP_WEB_UTILITIES_PKG.RtrimMultiByteSpaces(p_ExpReportHeaderInfo.purpose),
p_ExpReportHeaderInfo.cost_center,
p_ExpReportHeaderInfo.reimbursement_currency_code,
p_ExpReportHeaderInfo.reimbursement_currency_code,
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
sysdate,
icx_sec.getID(icx_sec.PV_USER_ID), -- Bug 1733370
p_xpense_rec.workflow_flag,
p_ExpReportHeaderInfo.amt_due_employee,
p_ExpReportHeaderInfo.amt_due_ccCompany,
nvl( p_xpense_rec.org_id, mo_global.get_current_org_id() ) );
EXCEPTION -- Block which encapsulates the delete and insert code
WHEN TOO_MANY_ROWS OR NO_DATA_FOUND THEN
AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
'AP_WEB_SAVESUB_DELETE_FAILED',
'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
||', Invoice Num = '|| p_xpense_rec.document_number);
AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeader',l_debug_info,
'AP_WEB_SAVESUB_LOCK_FAILED',
'V_ReportHeaderID = ' || p_xpense_rec.report_header_id
);
END InsertReportHeader; -- Block which encapsulates the delete and insert code
FUNCTION InsertReportHeaderLikeExisting(p_orig_report_header_id IN expHdr_headerID,
p_xpense_rec IN XpenseInfoRec
) RETURN BOOLEAN IS
-------------------------------------------------------------------
BEGIN
INSERT INTO ap_expense_report_headers
(report_header_id,
employee_id,
week_end_date,
creation_date,
created_by,
last_update_date,
last_updated_by,
vouchno,
total,
vendor_id,
vendor_site_id,
expense_check_address_flag,
reference_1,
reference_2,
invoice_num,
expense_report_id,
accts_pay_code_combination_id,
set_of_books_id,
source,
expense_status_code,
purgeable_flag,
accounting_date,
maximum_amount_to_apply,
advance_invoice_to_apply,
apply_advances_default,
employee_ccid,
reject_code,
hold_lookup_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
default_currency_code,
default_exchange_rate_type,
default_exchange_rate,
default_exchange_date,
last_update_login,
voucher_num,
doc_category_code,
awt_group_id,
org_id,
workflow_approved_flag,
flex_concatenated,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
override_approver_id,
payment_cross_rate_type,
payment_cross_rate_date,
payment_cross_rate,
payment_currency_code,
core_wf_status_flag,
amt_due_employee,
amt_due_ccard_company,
description,
bothpay_parent_id,
shortpay_parent_id,
paid_on_behalf_employee_id,
report_submitted_date, -- 2646985
receipts_received_date, -- jrautiai 3008468
last_audited_by, -- jrautiai 2987037
audit_code, -- jrautiai 3255738
report_filing_number
)
SELECT p_xpense_rec.report_header_id,
erh.employee_id, --2446559
week_end_date,
sysdate,
-- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.created_by),
NVL(p_xpense_rec.preparer_id, erh.created_by),
sysdate,
-- Bug 2473070 NVL(icx_sec.getID(icx_sec.PV_USER_ID), erh.last_updated_by),
NVL(p_xpense_rec.last_updated_by, erh.last_updated_by),
p_xpense_rec.vouchno,
decode(p_xpense_rec.total,-1,erh.amt_due_ccard_company, p_xpense_rec.total), --result of combining the apis
NVL(p_xpense_rec.vendor_id, erh.vendor_id),
NVL(p_xpense_rec.vendor_site_id, erh.vendor_site_id),
NVL(p_xpense_rec.expense_check_address_flag, erh.expense_check_address_flag),
reference_1,
reference_2,
p_xpense_rec.document_number, --invoice_num
expense_report_id,
NVL(p_xpense_rec.accts_pay_comb_id,erh.accts_pay_code_combination_id),
set_of_books_id,
NVL(p_xpense_rec.source,erh.source),
p_xpense_rec.expense_status_code,
purgeable_flag,
accounting_date,
'', -- Bug 3654956
'', -- 4001778 advance_invoice_to_apply,
'', -- 4001778 apply_advances_default,
employee_ccid,
reject_code,
hold_lookup_code,
attribute_category,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
default_currency_code,
default_exchange_rate_type,
default_exchange_rate,
default_exchange_date,
NVL(p_xpense_rec.last_update_login, erh.last_update_login),
voucher_num,
doc_category_code,
awt_group_id,
NVL(p_xpense_rec.org_id, erh.org_id),
decode(p_xpense_rec.workflow_flag, NULL,erh.workflow_approved_flag, decode(p_xpense_rec.workflow_flag,'POLICY','M',null)),
flex_concatenated,
global_attribute_category,
global_attribute1,
global_attribute2,
global_attribute3,
global_attribute4,
global_attribute5,
global_attribute6,
global_attribute7,
global_attribute8,
global_attribute9,
global_attribute10,
global_attribute11,
global_attribute12,
global_attribute13,
global_attribute14,
global_attribute15,
global_attribute16,
global_attribute17,
global_attribute18,
global_attribute19,
global_attribute20,
override_approver_id,
payment_cross_rate_type,
payment_cross_rate_date,
payment_cross_rate,
payment_currency_code,
core_wf_status_flag,
nvl(p_xpense_rec.amt_due_employee,0), --amt_due_employee
decode(p_xpense_rec.amt_due_ccard, NULL, nvl(erh.amt_due_ccard_company,0), p_xpense_rec.amt_due_ccard), --amt_due_ccard_company
NVL(p_xpense_rec.description,erh.description), --description
p_xpense_rec.bothpay_report_header_id, --bothpay_parent_id
NVL(p_xpense_rec.shortpay_parent_id, erh.shortpay_parent_id),
decode(p_xpense_rec.behalf_employee_id, -1, erh.employee_id, erh.paid_on_behalf_employee_id), --paid_on_behalf_employee_id
report_submitted_date, -- 2646985
receipts_received_date, -- jrautiai 3008468
last_audited_by, -- jrautiai 2987037
audit_code, -- jrautiai 3255738
report_filing_number
FROM ap_expense_report_headers erh
WHERE report_header_id = p_orig_report_header_id;
AP_WEB_DB_UTIL_PKG.RaiseException('InsertReportHeaderLikeExisting');
END InsertReportHeaderLikeExisting;
UPDATE ap_expense_report_headers
SET default_exchange_rate_type = p_xrate_type
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers RH
SET expense_check_address_flag = p_exp_check_address_flag,
source = p_source,
workflow_approved_flag = p_workflow_approve_flag,
apply_advances_default = decode(apply_advances_default,'Y','Y',decode(p_sys_apply_advances_default, 'Y',
decode(sign(p_available_prepays), 1, 'Y', 'N'), 'N')),
awt_group_id = decode(p_sys_allow_awt_flag, 'Y',
decode(p_ven_allow_awt_flag, 'Y', p_ven_awt_group_id,
null), null),
default_exchange_rate_type = p_sys_default_xrate_type,
default_exchange_date = decode(p_sys_default_xrate_type, null, null,
p_week_end_date),
default_exchange_rate = p_default_exchange_rate,
employee_ccid = p_employee_ccid
WHERE report_header_id = p_report_header_id;
SELECT decode (maximum_amount_to_apply, NULL, NULL,least( p_total, maximum_amount_to_apply))-- Bug 3654956
INTO l_max_amt_to_apply
from ap_expense_report_headers
where report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers
SET amt_due_ccard_company = p_amt_due_ccard_company,
amt_due_employee = l_amt_due_employee,
total = p_total,
maximum_amount_to_apply = l_max_amt_to_apply
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers
SET total = decode(p_total, NULL, total, total - p_sub_total),
vendor_id = p_vendor_id,
vendor_site_id = p_vendor_site_id,
amt_due_ccard_company = NVL(p_amt_due_ccard_company, amt_due_ccard_company),
bothpay_parent_id = p_bothpay_id,
paid_on_behalf_employee_id = DECODE(p_paid_on_behalf_id, -1, employee_id, p_paid_on_behalf_id),
employee_id = NVL(p_employee_id, employee_id),
description = NVL(p_description, description),
source = NVL(p_source, source),
accts_pay_code_combination_id = NVL(p_accts_comb_id, accts_pay_code_combination_id),
maximum_amount_to_apply = decode (maximum_amount_to_apply, NULL, NULL,
decode(p_total, NULL,NULL,
least( total - p_sub_total, maximum_amount_to_apply)))
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers erh
SET workflow_approved_flag = NVL(p_flag, decode(erh.workflow_approved_flag, 'M', 'A', erh.workflow_approved_flag)),
source = NVL(p_source, erh.source)
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers
SET workflow_approved_flag = decode(workflow_approved_flag,
'P','Y',
C_WORKFLOW_APPROVED_REQUEST, C_WORKFLOW_APPROVED_REQUEST, -- AP already rejected
C_WORKFLOW_APPROVED_REJECTED, C_WORKFLOW_APPROVED_REJECTED, -- AP already requests more info
'M')
WHERE report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers
SET workflow_approved_flag = decode(workflow_approved_flag,
'P', 'Y',
'Y', 'Y',
'M')
WHERE report_header_id = p_report_header_id;
FUNCTION DeleteReportHeaderAtDate(
P_ReportID IN expHdr_headerID,
P_LastUpdateDate IN expHdr_lastUpdateDate)
RETURN BOOLEAN IS
-------------------------------------------------------------------
l_TempReportHeaderID NUMBER;
SELECT REPORT_HEADER_ID
INTO l_TempReportHeaderID
FROM AP_EXPENSE_REPORT_HEADERS
WHERE REPORT_HEADER_ID = P_ReportID
AND SOURCE = C_RestorableReportSource
AND LAST_UPDATE_DATE = NVL(P_LastUpdateDate, LAST_UPDATE_DATE)
FOR UPDATE OF REPORT_HEADER_ID NOWAIT;
if (DeleteExpenseReport(P_ReportID)) then null; end if;
AP_WEB_DB_UTIL_PKG.RaiseException('DeleteReportHeaderAtDate');
END DeleteReportHeaderAtDate;
FUNCTION DeleteExpenseReport(p_report_header_id IN expHdr_headerID)
RETURN BOOLEAN IS
-------------------------------------------------------------------
l_curr_calling_sequence VARCHAR2(100) := 'DeleteExpenseReport';
DELETE FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id;
/* Delete All Notes associated with Expense Report */
AP_WEB_NOTES_PKG.DeleteERNotes (
p_src_report_header_id => p_report_header_id
);
/* Delete attachments assocated with the header */
fnd_attached_documents2_pkg.delete_attachments(
X_entity_name => 'OIE_HEADER_ATTACHMENTS',
X_pk1_value => p_report_header_id,
X_delete_document_flag => 'Y'
);
AP_WEB_DB_UTIL_PKG.RaiseException('DeleteExpenseReport');
END DeleteExpenseReport;
select EMPLOYEE_ID into l_employee_id
from AP_EXPENSE_REPORT_HEADERS
where REPORT_HEADER_ID = p_source_report_header_id;
insert into AP_EXPENSE_REPORT_HEADERS
(
REPORT_HEADER_ID,
WEEK_END_DATE,
EMPLOYEE_ID,
VOUCHNO,
TOTAL,
EXPENSE_REPORT_ID,
SET_OF_BOOKS_ID,
SOURCE,
DESCRIPTION,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DEFAULT_CURRENCY_CODE,
DEFAULT_EXCHANGE_RATE_TYPE,
DEFAULT_EXCHANGE_RATE,
DEFAULT_EXCHANGE_DATE,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
FLEX_CONCATENATED,
OVERRIDE_APPROVER_ID,
PAYMENT_CURRENCY_CODE,
OVERRIDE_APPROVER_NAME,
DEFAULT_RECEIPT_CURRENCY_CODE,
MULTIPLE_CURRENCIES_FLAG,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
INVOICE_NUM
)
select
p_target_report_header_id AS REPORT_HEADER_ID,
WEEK_END_DATE,
EMPLOYEE_ID,
0 AS VOUCHNO,
0 AS TOTAL,
EXPENSE_REPORT_ID,
SET_OF_BOOKS_ID,
C_RestorableReportSource AS SOURCE,
DESCRIPTION,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
DEFAULT_CURRENCY_CODE,
DEFAULT_EXCHANGE_RATE_TYPE,
DEFAULT_EXCHANGE_RATE,
DEFAULT_EXCHANGE_DATE,
ORG_ID,
GLOBAL_ATTRIBUTE_CATEGORY,
GLOBAL_ATTRIBUTE1,
GLOBAL_ATTRIBUTE2,
GLOBAL_ATTRIBUTE3,
GLOBAL_ATTRIBUTE4,
GLOBAL_ATTRIBUTE5,
GLOBAL_ATTRIBUTE6,
GLOBAL_ATTRIBUTE7,
GLOBAL_ATTRIBUTE8,
GLOBAL_ATTRIBUTE9,
GLOBAL_ATTRIBUTE10,
GLOBAL_ATTRIBUTE11,
GLOBAL_ATTRIBUTE12,
GLOBAL_ATTRIBUTE13,
GLOBAL_ATTRIBUTE14,
GLOBAL_ATTRIBUTE15,
GLOBAL_ATTRIBUTE16,
GLOBAL_ATTRIBUTE17,
GLOBAL_ATTRIBUTE18,
GLOBAL_ATTRIBUTE19,
GLOBAL_ATTRIBUTE20,
FLEX_CONCATENATED,
OVERRIDE_APPROVER_ID,
PAYMENT_CURRENCY_CODE,
OVERRIDE_APPROVER_NAME,
DEFAULT_RECEIPT_CURRENCY_CODE,
MULTIPLE_CURRENCIES_FLAG,
sysdate AS CREATION_DATE,
p_user_id AS CREATED_BY,
sysdate AS LAST_UPDATE_DATE,
p_user_id AS LAST_UPDATED_BY,
l_invoice_num as INVOICE_NUM
from AP_EXPENSE_REPORT_HEADERS
where REPORT_HEADER_ID = p_source_report_header_id;
FUNCTION UpdateHeaderTotal(
p_report_header_id IN expHdr_headerID
) RETURN BOOLEAN IS
--------------------------------------------------------------------------------
l_total number;
select sum(amount)
into l_total
from ap_expense_report_lines
where report_header_id = p_report_header_id;
select(l_total - total)
into l_diff
from ap_expense_report_headers
where report_header_id = p_report_header_id;
update ap_expense_report_headers
set total = l_total,
amt_due_employee = amt_due_employee + l_diff
where report_header_id = p_report_header_id;
AP_WEB_DB_UTIL_PKG.RaiseException('UpdateHeaderTotal');
END UpdateHeaderTotal;
SELECT payment_currency_code
INTO p_payment_curr_code
FROM ap_expense_report_headers
WHERE report_header_id = p_expenseReportId;
SELECT total
INTO p_total
FROM ap_expense_report_headers
WHERE report_header_id = p_report_header_id;
SELECT payment_due_from_code
INTO p_paymentDueFromCode
FROM ap_credit_card_trxns_all trx
WHERE trx.report_header_id = p_report_header_id
AND rownum = 1; --Data Corruption might give two Distinct Pay Methods.
SELECT AP_WEB_POLICY_UTILS.get_lookup_meaning('OIE_AUD_RETURN_REASONS',aerh.return_reason_code),
AP_WEB_POLICY_UTILS.get_lookup_description('OIE_AUD_RETURN_REASONS',aerh.return_reason_code)||' '||aerh.return_instruction
INTO p_return_reason,
p_return_instruction
FROM ap_expense_report_headers aerh
WHERE aerh.report_header_id = p_report_header_id;
UPDATE ap_expense_report_headers aerh
SET aerh.return_reason_code = '',
aerh.return_instruction = ''
WHERE aerh.report_header_id = p_report_header_id;
SELECT default_code_combination_id
INTO p_default_emp_ccid
FROM (
SELECT emp.default_code_combination_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.default_code_combination_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
);
SELECT set_of_books_id
INTO l_emp_set_of_books_id
FROM (
SELECT emp.set_of_books_id
FROM per_employees_x emp
WHERE emp.employee_id = p_employee_id
AND NOT AP_WEB_DB_HR_INT_PKG.ispersoncwk(emp.employee_id)='Y'
UNION ALL
SELECT emp.set_of_books_id
FROM per_cont_workers_current_x emp
WHERE emp.person_id = p_employee_id
);
SELECT GS.chart_of_accounts_id
INTO p_chart_of_accounts_id
FROM gl_sets_of_books GS
WHERE GS.set_of_books_id=l_emp_set_of_books_id;
SELECT Flex_Concactenated
INTO p_FlexConcactenated
FROM AP_EXPENSE_REPORT_PARAMS
WHERE parameter_id = p_parameter_id;
SELECT invoice_num
INTO l_invoice_num
FROM ap_expense_report_headers_all
WHERE report_header_id = p_report_header_id;
SELECT workflow_approved_flag
INTO l_workflow_approved_flag
FROM ap_expense_report_headers_all
WHERE report_header_id = p_report_header_id;
FUNCTION GetERLastUpdateDate(p_report_header_id IN NUMBER)
RETURN VARCHAR2 IS
l_last_update_date VARCHAR2(30);
SELECT to_char(last_update_date, 'DD-MON-RRRR HH:MI:SS', 'NLS_DATE_LANGUAGE = ENGLISH')
INTO l_last_update_date
FROM ap_expense_report_headers_all
WHERE report_header_id = p_report_header_id;
return l_last_update_date;
AP_WEB_DB_UTIL_PKG.RaiseException('GetERLastUpdateDate');
END GetERLastUpdateDate;