参考文档:文档 ID 365238.1
根据文档显示,只需要在程序AP_WEB_OA_CUSTOM_PKG.GetNewExpenseReportInvoice 添加生成逻辑即可
程序举例:
FUNCTION GetNewExpenseReportInvoice(p_employeeId IN ap_expense_report_headers.employee_id%TYPE,
p_userId IN fnd_user.user_id%TYPE,
p_reportHeaderId IN ap_expense_report_headers.report_header_id%TYPE) RETURN VARCHAR2 IS
l_reportNumber ap_expense_report_headers.invoice_num%TYPE := NULL;
l_userName fnd_user.user_name%TYPE := null;
l_nReport NUMBER := 0;
BEGIN
IF (p_employeeId is not NULL AND p_userId is not NULL) THEN
-- SAMPLE CODE
-- If you want to access the AP_WEB_REPNUM_PREFIX profile option,
-- use the following function:
-- fnd_profile.value_specific( 'AP_WEB_REPNUM_PREFIX', p_userId )
--
-- The following sample code will generate the report invoice of <userid><YY-DDMMHHMI><-><number> format
-- where:
-- userid : the employee's user name
-- YY : the las 2 digit of the current year and
-- number : the total number of expense reports the employee has
-- for the current year including the current report.
--
--
-- Get the user name
SELECT user_name INTO l_userName
FROM fnd_user fnd
WHERE fnd.employee_id = p_employeeId
AND fnd.user_id = p_userId
AND sysdate <= nvl(fnd.end_date, sysdate)
AND rownum = 1;
-- Get the total of the number of expense report
-- this employee has for current year
SELECT count(*) INTO l_nReport
FROM ap_expense_report_headers
WHERE employee_id = p_employeeId
AND to_char(sysdate, 'RR') = to_char(CREATION_DATE, 'RR');
l_nReport := l_nReport + 1;
-- Max length of the report number is 50
l_reportNumber := SUBSTR(l_userName, 1, 30) || to_char(sysdate,'RR-DDMMHHMI') || '-'|| ltrim(to_char(l_nReport,'0999'));
END IF;
RETURN l_reportNumber;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
WHEN OTHERS THEN
return NULL;
END GetNewExpenseReportInvoice;