[Home] [Help]
PACKAGE: APPS.AP_WEB_DB_EXPRPT_PKG
Source
1 PACKAGE AP_WEB_DB_EXPRPT_PKG AUTHID CURRENT_USER AS
2 /* $Header: apwdbers.pls 120.24 2010/09/20 12:46:48 dsadipir ship $ */
3
4 ------------------------
5 -- Types definition
6 ------------------------
7
8 SUBTYPE expHdr_headerID IS AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE;
9 SUBTYPE expHdr_employeeID IS AP_EXPENSE_REPORT_HEADERS.employee_id%TYPE;
10 SUBTYPE expHdr_vouchno IS AP_EXPENSE_REPORT_HEADERS.vouchno%TYPE;
11 SUBTYPE expHdr_total IS AP_EXPENSE_REPORT_HEADERS.total%TYPE;
12 SUBTYPE expHdr_vendorID IS AP_EXPENSE_REPORT_HEADERS.vendor_id%TYPE;
13 SUBTYPE expHdr_vendorSiteID IS AP_EXPENSE_REPORT_HEADERS.vendor_site_id%TYPE;
14 SUBTYPE expHdr_expCheckAddrFlag IS AP_EXPENSE_REPORT_HEADERS.expense_check_address_flag%TYPE;
15 SUBTYPE expHdr_invNum IS AP_EXPENSE_REPORT_HEADERS.invoice_num%TYPE;
16 SUBTYPE expHdr_source IS AP_EXPENSE_REPORT_HEADERS.source%TYPE;
17 SUBTYPE expHdr_expenseStatusCode IS AP_EXPENSE_REPORT_HEADERS.expense_status_code%TYPE;
18 SUBTYPE expHdr_employeeCCID IS AP_EXPENSE_REPORT_HEADERS.employee_ccid%TYPE;
19 SUBTYPE expHdr_description IS AP_EXPENSE_REPORT_HEADERS.description%TYPE;
20 SUBTYPE expHdr_defaultCurrCode IS AP_EXPENSE_REPORT_HEADERS.default_currency_code%TYPE;
21 SUBTYPE expHdr_defaultXchRateType IS AP_EXPENSE_REPORT_HEADERS.default_exchange_rate_type%TYPE;
22 SUBTYPE expHdr_orgID IS AP_EXPENSE_REPORT_HEADERS.org_id%TYPE;
23 SUBTYPE expHdr_wkflApprvdFlag IS AP_EXPENSE_REPORT_HEADERS.workflow_approved_flag%TYPE;
24 SUBTYPE expHdr_flexConcat IS AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE;
25 SUBTYPE expHdr_overrideApprID IS AP_EXPENSE_REPORT_HEADERS.override_approver_id%TYPE;
26 SUBTYPE expHdr_overrideApprName IS AP_EXPENSE_REPORT_HEADERS.override_approver_name%TYPE;
27 SUBTYPE expHdr_amtDueCCardCompany IS AP_EXPENSE_REPORT_HEADERS.amt_due_ccard_company%TYPE;
28 SUBTYPE expHdr_amtDueEmployee IS AP_EXPENSE_REPORT_HEADERS.amt_due_employee%TYPE;
29 SUBTYPE expHdr_bothpayParentId IS AP_EXPENSE_REPORT_HEADERS.bothpay_parent_iD%TYPE;
30 SUBTYPE expHdr_shortpayParentId IS AP_EXPENSE_REPORT_HEADERS.shortpay_parent_id%TYPE;
31 SUBTYPE expHdr_paidOnBehalfEmpID IS AP_EXPENSE_REPORT_HEADERS.paid_on_behalf_employee_id%TYPE;
32 SUBTYPE expHdr_expRptID IS AP_EXPENSE_REPORT_HEADERS.expense_report_id%TYPE;
33 SUBTYPE expHdr_weekEndDate IS AP_EXPENSE_REPORT_HEADERS.week_end_date%TYPE;
34 SUBTYPE expHdr_createdBy IS AP_EXPENSE_REPORT_HEADERS.created_by%TYPE;
35 SUBTYPE expHdr_lastUpdatedBy IS AP_EXPENSE_REPORT_HEADERS.last_updated_by%TYPE;
36 SUBTYPE expHdr_lastUpdateDate IS AP_EXPENSE_REPORT_HEADERS.last_update_date%TYPE;
37 SUBTYPE expHdr_setOfBooksID IS AP_EXPENSE_REPORT_HEADERS.set_of_books_id%TYPE;
38 SUBTYPE expHdr_lastUpdateLogin IS AP_EXPENSE_REPORT_HEADERS.last_update_login%TYPE;
39 SUBTYPE expHdr_applyAdvDefault IS AP_EXPENSE_REPORT_HEADERS.apply_advances_default%TYPE;
40 SUBTYPE expHdr_awtGroupID IS AP_EXPENSE_REPORT_HEADERS.awt_group_id%TYPE;
41 SUBTYPE expHdr_defaultExchDate IS AP_EXPENSE_REPORT_HEADERS.default_exchange_date%TYPE;
42 SUBTYPE expHdr_defaultExchRate IS AP_EXPENSE_REPORT_HEADERS.default_exchange_rate%TYPE;
43 SUBTYPE expHdr_acctsPayCodeCombID IS AP_EXPENSE_REPORT_HEADERS.accts_pay_code_combination_id%TYPE;
44 SUBTYPE expHdr_attrCategory IS AP_EXPENSE_REPORT_HEADERS.attribute_category%TYPE;
45 SUBTYPE expHdr_attr1 IS AP_EXPENSE_REPORT_HEADERS.attribute1%TYPE;
46 SUBTYPE expHdr_attr2 IS AP_EXPENSE_REPORT_HEADERS.attribute2%TYPE;
47 SUBTYPE expHdr_attr3 IS AP_EXPENSE_REPORT_HEADERS.attribute3%TYPE;
48 SUBTYPE expHdr_attr4 IS AP_EXPENSE_REPORT_HEADERS.attribute4%TYPE;
49 SUBTYPE expHdr_attr5 IS AP_EXPENSE_REPORT_HEADERS.attribute5%TYPE;
50 SUBTYPE expHdr_attr6 IS AP_EXPENSE_REPORT_HEADERS.attribute6%TYPE;
51 SUBTYPE expHdr_attr7 IS AP_EXPENSE_REPORT_HEADERS.attribute7%TYPE;
52 SUBTYPE expHdr_attr8 IS AP_EXPENSE_REPORT_HEADERS.attribute8%TYPE;
53 SUBTYPE expHdr_attr9 IS AP_EXPENSE_REPORT_HEADERS.attribute9%TYPE;
54 SUBTYPE expHdr_attr10 IS AP_EXPENSE_REPORT_HEADERS.attribute10%TYPE;
55 SUBTYPE expHdr_attr11 IS AP_EXPENSE_REPORT_HEADERS.attribute11%TYPE;
56 SUBTYPE expHdr_attr12 IS AP_EXPENSE_REPORT_HEADERS.attribute12%TYPE;
57 SUBTYPE expHdr_attr13 IS AP_EXPENSE_REPORT_HEADERS.attribute13%TYPE;
58 SUBTYPE expHdr_attr14 IS AP_EXPENSE_REPORT_HEADERS.attribute14%TYPE;
59 SUBTYPE expHdr_attr15 IS AP_EXPENSE_REPORT_HEADERS.attribute15%TYPE;
60 SUBTYPE expHdr_payemntCurrCode IS AP_EXPENSE_REPORT_HEADERS.payment_currency_code%TYPE;
61 SUBTYPE expHdr_maxAmountApplied IS AP_EXPENSE_REPORT_HEADERS.maximum_amount_to_apply%TYPE;
62 SUBTYPE expHdr_receiptsStatus IS AP_EXPENSE_REPORT_HEADERS.receipts_status%TYPE;
63 SUBTYPE expHdr_receivedDate IS AP_EXPENSE_REPORT_HEADERS.receipts_received_date%TYPE;
64 SUBTYPE expHdr_imgReceiptsStatus IS AP_EXPENSE_REPORT_HEADERS.image_receipts_status%TYPE;
65 SUBTYPE expHdr_imgReceivedDate IS AP_EXPENSE_REPORT_HEADERS.image_receipts_received_date%TYPE;
66
67 /* From DB AP INTERFACE PACKAGE */
68 SUBTYPE finSysParams_checkAddrFlag IS FINANCIALS_SYSTEM_PARAMETERS.expense_check_address_flag%TYPE;
69
70 SUBTYPE glsob_chartOfAccountsID IS GL_SETS_OF_BOOKS.chart_of_accounts_id%TYPE;
71
72 SUBTYPE apSetUp_defaultExchRateType IS AP_SYSTEM_PARAMETERS.default_exchange_rate_type%TYPE;
73 SUBTYPE apSetUp_applyAdvDefault IS AP_SYSTEM_PARAMETERS.apply_advances_default%TYPE;
74 SUBTYPE apSetUp_allowAWTFlag IS AP_SYSTEM_PARAMETERS.allow_awt_flag%TYPE;
75 SUBTYPE apSetUp_makeMandatoryFlag IS AP_SYSTEM_PARAMETERS.make_rate_mandatory_flag%TYPE;
76
77
78 /* From DB CCard Package */
79 SUBTYPE cardProgs_vendorID IS AP_CARD_PROGRAMS.vendor_id%TYPE;
80 SUBTYPE cardProgs_vendorSiteID IS AP_CARD_PROGRAMS.vendor_site_id%TYPE;
81
82 ------------------------
83 -- Constants definition
84 ------------------------
85
86 -- Workflow status flags
87 C_WORKFLOW_APPROVED_SAVED CONSTANT VARCHAR2(1) := 'S';
88 C_WORKFLOW_APPROVED_REJECTED CONSTANT VARCHAR2(1) := 'R';
89 C_WORKFLOW_APPROVED_RETURNED CONSTANT VARCHAR2(1) := 'T';
90 C_WORKFLOW_APPROVED_SUBMIT CONSTANT VARCHAR2(1) := '';
91 --ER 1552747 - withdraw expense report
92 C_WORKFLOW_APPROVED_WITHDRAW CONSTANT VARCHAR2(1) := 'W';
93 -- Auditor Requesting More Info
94 C_WORKFLOW_APPROVED_REQUEST CONSTANT VARCHAR2(1) := 'Q';
95 -- Used in query of header and lines
96 C_UserAttributeCode CONSTANT VARCHAR2(50) := 'ICX_HR_PERSON_ID';
97 C_RestorableReportSource CONSTANT VARCHAR2(50) := 'NonValidatedWebExpense';
98
99
100
101 --------------------------------
102 -- Cursor Reference definition
103 --------------------------------
104
105 TYPE RestorableReportsCursor IS REF CURSOR;
106
107 TYPE ExpWorkflowRec IS RECORD (
108 doc_num expHdr_invNum,
109 workflow_flag expHdr_wkflApprvdFlag
110 ); /* end TYPE WorkflowRec */
111
112
113 TYPE ExpInfoRec IS RECORD (
114 emp_id expHdr_employeeID,
115 default_curr_code expHdr_defaultCurrCode,
116 doc_num expHdr_invNum,
117 total expHdr_total,
118 payment_curr_code expHdr_payemntCurrCode,
119 week_end_date expHdr_weekEndDate
120 ); /* end TYPE ExpInfoRec */
121
122 TYPE ExpHeaderRec IS RECORD (
123 template_id VARCHAR2(20),
124 last_receipt_date VARCHAR2(25),
125 description expHdr_description,
126 default_curr_code expHdr_defaultCurrCode,
127 flex_concat expHdr_flexConcat,
128 override_appr_id VARCHAR2(15),
129 override_appr_name expHdr_overrideApprName,
130 emp_id expHdr_employeeID,
131 last_update_date VARCHAR2(25)
132 ); /* end TYPE ExpHeaderRec */
133
134 TYPE XpenseInfoRec IS RECORD (
135 report_header_id expHdr_headerID,
136 document_number expHdr_invNum,
137 employee_id expHdr_employeeID,
138 org_id expHdr_orgID,
139 vouchno expHdr_vouchno,
140 total expHdr_total,
141 vendor_id expHdr_vendorID,
142 vendor_site_id expHdr_vendorSiteID,
143 amt_due_employee expHdr_amtDueEmployee,
144 amt_due_ccard expHdr_amtDueCCardCompany,
145 description expHdr_description,
146 preparer_id expHdr_createdBy,
147 last_update_login expHdr_lastUpdateLogin,
148 last_updated_by expHdr_lastUpdatedBy,
149 workflow_flag VARCHAR2(20),
150 expense_check_address_flag expHdr_expCheckAddrFlag,
151 bothpay_report_header_id expHdr_bothpayParentID,
152 shortpay_parent_id expHdr_shortpayParentID,
153 behalf_employee_id expHdr_paidOnBehalfEmpID, --end of one function
154 approver_id expHdr_overrideApprID,
155 week_end_date expHdr_weekEndDate,
156 set_of_books_id expHdr_setOfBooksID,
157 source expHdr_source,
158 accts_pay_comb_id expHdr_acctsPayCodeCombID,
159 expense_status_code expHdr_expenseStatusCode,
160 receipts_status expHdr_receiptsStatus,
161 receipts_received_date expHdr_receivedDate,
162 image_receipts_status expHdr_imgReceiptsStatus,
163 image_receipts_received_date expHdr_imgReceivedDate
164 ); /* end TYPE XpenseInfoRec */
165
166
167 ----------------------------------------------------------------------------------------
168 -- Name: GetEmployeeIdFromBothPayParent
169 -- Desc: get employee_id from both pay parent report
170 -- Input: p_bothpay_parent_id - parent report header id
171 -- Output: p_employee_id - employee_id from both pay parent report
172 ---------------------------------------------------------------------------------------
173 PROCEDURE GetEmployeeIdFromBothPayParent(
174 p_bothpay_parent_id IN NUMBER,
175 p_employee_id OUT NOCOPY NUMBER);
176
177 ----------------------------------------------------------------------------------------
178 -- Name: GetRestorableReportsCursor
179 -- Desc: get invoice_num, employee full name, total, etc. for restorable expense reports
180 -- that employee with P_WebUserID has access to
181 -- Input: P_WebUserID - securing attribute's web_user_id
182 -- Output: p_cursor - cursor reference for RestorableReportsCursor
183 -- that stores information about the selected expense reports
184 -- Returns: true - succeeded
185 -- false - failed
186 ---------------------------------------------------------------------------------------
187 FUNCTION GetRestorableReportsCursor(P_WebUserID IN AK_WEB_USER_SEC_ATTR_VALUES.web_user_id%TYPE,
188 p_cursor OUT NOCOPY RestorableReportsCursor)
189 RETURN BOOLEAN;
190
191
192 ---------------------------------------------------------------------------
193 -- Name: GetExpWorkflowInfo
194 -- Desc: get invoice_num and workflow_approved_flag for a particular
195 -- expense report
196 -- Input: P_ReportID - report_header_id for the expense report
197 -- Output: P_WorkflowRec - stores information about the selected expense report in
198 -- ExpWorkflowRec
199 -- Returns: true - succeeded
200 -- false - failed
201 ---------------------------------------------------------------------------
202 FUNCTION GetExpWorkflowInfo(
203 P_ReportID IN expHdr_headerID,
204 P_WorkflowRec OUT NOCOPY ExpWorkflowRec)
205 RETURN BOOLEAN;
206
207
208 -------------------------------------------------------------------
209 -- Name: GetReportInfo
210 -- Desc: get employee_id, default_currency_code, invoice_num, and total
211 -- for a particular expense report
212 -- Input: p_expenseReportId - report_header_id for the expense report
213 -- Output: p_exp_info_rec - stores information about the selected expense report in
214 -- ExpInfoRec
215 -- Returns: true - succeeded
216 -- false - failed
217 -------------------------------------------------------------------
218 FUNCTION GetReportInfo(
219 p_expenseReportId IN expHdr_headerID,
220 p_exp_info_rec OUT NOCOPY ExpInfoRec
221 ) RETURN BOOLEAN;
222
223
224 -------------------------------------------------------------------------------------
225 -- Name: GetOverrideApproverID
226 -- Desc: get override_approver_id of a particular expense report
227 -- Input: p_report_header_id - report_header_id for the expense report
228 -- Output: p_id - stores override_approver_id of expense report
229 -- Returns: true - succeeded
230 -- false - failed
231 -------------------------------------------------------------------------------------
232 FUNCTION GetOverrideApproverID(p_report_header_id IN expHdr_headerID,
233 p_id OUT NOCOPY expHdr_overrideApprID)
234 RETURN BOOLEAN;
235
236
237 ----------------------------------------------------------------------------------
238 -- Name: GetOrgIdByReportHeaderId
239 -- Desc: get org_id of a particular expense report
240 -- Input: p_header_id - report_header_id for the expense report
241 -- Output: p_org_id - stores org_id of expense report
242 -- Returns: true - succeeded
243 -- false - failed
244 ----------------------------------------------------------------------------------
245 FUNCTION GetOrgIdByReportHeaderId(
246 p_header_id IN expHdr_headerID,
247 p_org_id OUT NOCOPY expHdr_orgID) RETURN BOOLEAN;
248
249 ---------------------------------------------------------------------------------------------
250 -- Name: GetReportHeaderAttributes
251 -- Desc: get hr_employee's default_code_combination_id, employee_id, expense report's
252 -- flex_concatenated, attribute_category, and attributes 1 thru 15
253 -- Input: p_report_header_id - report_header_id for the expense report
254 -- Output:
255 -- Returns: true - succeeded
256 -- false - failed
257 ---------------------------------------------------------------------------------------------
258 -------------------------------------------------------------------
259 FUNCTION GetReportHeaderAttributes(p_report_header_id IN expHdr_headerID,
260 p_default_comb_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
261 p_emp_id OUT NOCOPY expHdr_employeeID,
262 p_flex_concat OUT NOCOPY expHdr_flexConcat,
263 p_attr_category OUT NOCOPY expHdr_attrCategory,
264 p_attr1 OUT NOCOPY expHdr_attr1,
265 p_attr2 OUT NOCOPY expHdr_attr2,
266 p_attr3 OUT NOCOPY expHdr_attr3,
267 p_attr4 OUT NOCOPY expHdr_attr4,
268 p_attr5 OUT NOCOPY expHdr_attr5,
269 p_attr6 OUT NOCOPY expHdr_attr6,
270 p_attr7 OUT NOCOPY expHdr_attr7,
271 p_attr8 OUT NOCOPY expHdr_attr8,
272 p_attr9 OUT NOCOPY expHdr_attr9,
273 p_attr10 OUT NOCOPY expHdr_attr10,
274 p_attr11 OUT NOCOPY expHdr_attr11,
275 p_attr12 OUT NOCOPY expHdr_attr12,
276 p_attr13 OUT NOCOPY expHdr_attr13,
277 p_attr14 OUT NOCOPY expHdr_attr14,
278 p_attr15 OUT NOCOPY expHdr_attr15)
279 RETURN BOOLEAN;
280 -------------------------------------------------------------------
281
282 -------------------------------------------------------------------
283 -- Name: GetReportHeaderInfo
284 -- Desc: get expense_report_id, description, default_currency_code,
285 -- flex_concatenated, employee_id, override_approver_name, etc.
286 -- for a particular expense report
287 -- Input: P_ReportID - report_header_id for the expense report
288 -- Output: P_ExpHdrRec - stores information about the selected expense report in
289 -- ExpHeaderRec
290 -- Returns: true - succeeded
291 -- false - failed
292 -------------------------------------------------------------------
293 FUNCTION GetReportHeaderInfo(P_ReportID IN expHdr_headerID,
294 P_ExpHdrRec OUT NOCOPY ExpHeaderRec)
295 RETURN BOOLEAN;
296
297 -------------------------------------------------------------------
298 -- Name: GetExpReportExchCurrInfo
299 -- Desc: get default_exchange_rate, precision of the expense report
300 -- and of the default currency
301 -- Input: p_report_id - report_header_id for the expense report
302 -- Output: p_exch_rate - the selected expense report's default exchange rate
303 -- p_reimb_precision - default currency's precision
304 -- Returns: true - succeeded
305 -- false - failed
306 -------------------------------------------------------------------
307 FUNCTION GetExpReportExchCurrInfo(p_report_id IN expHdr_headerID,
308 p_exch_rate OUT NOCOPY expHdr_defaultExchRate,
309 p_reimb_precision OUT NOCOPY FND_CURRENCIES_VL.PRECISION%TYPE
310 ) RETURN BOOLEAN;
311
312 -------------------------------------------------------------------
313 -- Name: GetNextExpReportID
314 -- Desc: get the next sequence for expense report's report_header_id
315 -- column
316 -- Output: p_new_report_id - new report_header_id for expense report
317 -- Returns: true - succeeded
318 -- false - failed
319 -------------------------------------------------------------------
320 FUNCTION GetNextExpReportID(p_new_report_id OUT NOCOPY NUMBER) RETURN BOOLEAN;
321
322 -------------------------------------------------------------------
323 FUNCTION GetNextRptHdrID(p_new_report_id OUT NOCOPY NUMBER) RETURN BOOLEAN;
324 -------------------------------------------------------------------
325
326 -------------------------------------------------------------------
327 -- Name: GetAccountingInfo
328 -- Desc: get some system accounting parameters, chart_of_accounts_id,
329 -- and employee information from the HR tables
330 -- Input: p_report_header_id - report_header_id for a given expense report
331 -- Output: p_sys_apply_advances_default - ap system parameter's apply_advances_default
332 -- p_sys_allow_awt_flag - ap system parameter's allow_awt_flag
333 -- p_sys_default_xrate_type - ap system parameter's default_exchange_rate_type
334 -- p_sys_make_rate_mandatory - ap system parameter's make_rate_mandatory_flag
335 -- p_exp_check_address_flag - HR's or financial system parameter's expense_check_address_flag
336 -- p_default_currency_code - expense report's default_currency_code
337 -- p_week_end_date - expense report's week_end_date
338 -- p_flex_concatenated - expense report's flex_concatenated
339 -- p_employee_id - expense report's employee_id
340 -- Returns: true - succeeded
341 -- false - failed
342 -------------------------------------------------------------------
343 FUNCTION GetAccountingInfo(
344 p_report_header_id IN expHdr_headerID,
345 p_sys_apply_advances_default OUT NOCOPY apSetUp_applyAdvDefault,
346 p_sys_allow_awt_flag OUT NOCOPY apSetUp_allowAWTFlag,
347 p_sys_default_xrate_type OUT NOCOPY apSetUp_defaultExchRateType,
348 p_sys_make_rate_mandatory OUT NOCOPY apSetUp_makeMandatoryFlag,
349 p_exp_check_address_flag OUT NOCOPY finSysParams_checkAddrFlag,
350 p_default_currency_code OUT NOCOPY expHdr_defaultCurrCode,
351 p_week_end_date OUT NOCOPY expHdr_weekEndDate,
352 p_flex_concatenated OUT NOCOPY expHdr_flexConcat,
353 p_employee_id OUT NOCOPY expHdr_employeeID)
354 RETURN BOOLEAN;
355
356 -------------------------------------------------------------------
357 -- Name: GetExpReportInfo
358 -- Desc: get concatenation of card_number/full_name, vendor_id's,
359 -- amt_due_ccard_company, and total for the credit card
360 -- expense report in both pay
361 -- Input: p_report_header_id - report_header_id for the expense report
362 -- Output: p_description - stores concatenation of credit card_number/employee full_name
363 -- p_ccard_amt - expense report's amt_due_ccard_company
364 -- p_total - expense report's total
365 -- Returns: true - succeeded
366 -- false - failed
367 -------------------------------------------------------------------
368 FUNCTION GetExpReportInfo(
369 p_report_header_id IN expHdr_headerID,
370 p_description OUT NOCOPY VARCHAR2,
371 p_ccard_amt OUT NOCOPY expHdr_amtDueCCardCompany,
372 p_total OUT NOCOPY expHdr_total
373 ) RETURN BOOLEAN;
374
375
376 FUNCTION ExpReportShortpaid(P_ReportID IN expHdr_headerID,
377 P_Shortpaid OUT NOCOPY BOOLEAN)
378 RETURN BOOLEAN;
379
380
381 -------------------------------------------------------------------
382 -- Name: InsertReportHeader
383 -- Desc: get
384 -- Input: p_report_header_id - report_header_id for the expense report
385 -- p_ExpReportHeaderInfo -
386 -- Returns: true - succeeded
387 -- false - failed
388 -------------------------------------------------------------------
389 FUNCTION InsertReportHeader(p_xpense_rec IN XpenseInfoRec,
390 p_ExpReportHeaderInfo IN AP_WEB_DFLEX_PKG.ExpReportHeaderRec
391 ) RETURN BOOLEAN;
392
393 -------------------------------------------------------------------
394 -- Name: InsertReportHeaderLikeExisting
395 -- Desc: get
396 -- Input: p_orig_report_header_id - report_header_id for the expense report
397 -- p_xpense_rec
398 -- Returns: true - succeeded
399 -- false - failed
400 -------------------------------------------------------------------
401 FUNCTION InsertReportHeaderLikeExisting(p_orig_report_header_id IN expHdr_headerID,
402 p_xpense_rec IN XpenseInfoRec
403 ) RETURN BOOLEAN;
404
405
406 -------------------------------------------------------------------
407 -- Name: SetDefaultExchRateType
408 -- Desc: Updates ap_expense_report_headers with a new default_exchange_rate_type
409 -- Input: p_report_header_id - report_header_id for the expense report
410 -- p_xrate_type - default_exchange_rate_type
411 -- Returns: true - succeeded
412 -- false - failed
413 -------------------------------------------------------------------
414 FUNCTION SetDefaultExchRateType(p_report_header_id IN expHdr_headerID,
415 p_xrate_type IN expHdr_defaultXchRateType)
416 RETURN BOOLEAN;
417
418 -------------------------------------------------------------------
419 -- Name: SetExpenseHeaderInfo
420 -- Desc: get
421 -- Input: p_report_header_id - report_header_id for the expense report
422 -- p_exp_check_address_flag
423 -- p_source
424 -- p_workflow_approve_flag
425 -- p_sys_apply_advances_default
426 -- p_available_prepays
427 -- p_sys_allow_awt_flag
428 -- p_ven_allow_awt_flag
429 -- p_ven_awt_group_id
430 -- p_sys_default_xrate_type
431 -- p_week_end_date
432 -- p_default_exchange_rate
433 -- p_employee_ccid
434 -- Returns: true - succeeded
435 -- false - failed
436 -------------------------------------------------------------------
437 FUNCTION SetExpenseHeaderInfo(
438 p_report_header_id IN expHdr_headerID,
439 p_exp_check_address_flag IN expHdr_expCheckAddrFlag,
440 p_source IN expHdr_source,
441 p_workflow_approve_flag IN expHdr_wkflApprvdFlag,
442 p_sys_apply_advances_default IN expHdr_applyAdvDefault,
443 p_available_prepays IN NUMBER,
444 p_sys_allow_awt_flag IN AP_SYSTEM_PARAMETERS.allow_awt_flag%TYPE,
445 p_ven_allow_awt_flag IN AP_SUPPLIERS.allow_awt_flag%TYPE,
446 p_ven_awt_group_id IN expHdr_awtGroupID,
447 p_sys_default_xrate_type IN expHdr_defaultXchRateType,
448 p_week_end_date IN expHdr_defaultExchDate,
449 p_default_exchange_rate IN expHdr_defaultExchRate,
450 p_employee_ccid IN expHdr_employeeCCID
451 ) RETURN BOOLEAN;
452
453
454 -------------------------------------------------------------------
455 -- Name: SetAmtDuesAndTotal
456 -- Desc: get
457 -- Input: p_report_header_id - report_header_id for the expense report
458 -- Returns: true - succeeded
459 -- false - failed
460 -------------------------------------------------------------------
461 FUNCTION SetAmtDuesAndTotal(
462 p_report_header_id IN expHdr_headerID,
463 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany,
464 p_amt_due_employee IN expHdr_amtDueEmployee,
465 p_total IN expHdr_total
466 ) RETURN BOOLEAN;
467
468 -------------------------------------------------------------------
469 -- Name: SetBothpayReportHeader
470 -- Desc: get
471 -- Input: p_report_header_id - report_header_id for the expense report
472 -- Returns: true - succeeded
473 -- false - failed
474 -------------------------------------------------------------------
475 FUNCTION SetBothpayReportHeader(
476 p_report_header_id IN expHdr_headerID,
477 p_sub_total IN NUMBER,
478 p_vendor_id IN expHdr_vendorID,
479 p_vendor_site_id IN expHdr_vendorSiteID,
480 p_bothpay_id IN expHdr_bothpayParentID,
481 p_paid_on_behalf_id IN expHdr_paidOnBehalfEmpID,
482 p_total IN expHdr_total DEFAULT NULL,
483 p_amt_due_ccard_company IN expHdr_amtDueCCardCompany DEFAULT NULL,
484 p_employee_id IN expHdr_employeeID DEFAULT NULL,
485 p_description IN expHdr_description DEFAULT NULL,
486 p_source IN expHdr_source DEFAULT NULL,
487 p_accts_comb_id IN expHdr_acctsPayCodeCombID DEFAULT NULL
488 ) RETURN BOOLEAN;
489
490
491 -------------------------------------------------------------------
492 FUNCTION SetWkflApprvdFlag(p_report_header_id IN expHdr_headerID)
493 RETURN BOOLEAN;
494 -------------------------------------------------------------------
495
496 -------------------------------------------------------------------
497 FUNCTION SetWkflApprvdFlag2(p_report_header_id IN expHdr_headerID)
498 RETURN BOOLEAN;
499 -------------------------------------------------------------------
500
501 -------------------------------------------------------------------
502 -- Name: SetWkflApprvdFlagAndSource
503 -- Desc: get
504 -- Input: p_report_header_id - report_header_id for the expense report
505 -- Returns: true - succeeded
506 -- false - failed
507 -------------------------------------------------------------------
508 FUNCTION SetWkflApprvdFlagAndSource(
509 p_report_header_id IN expHdr_headerID,
510 p_flag IN expHdr_wkflApprvdFlag,
511 p_source IN expHdr_source
512 ) RETURN BOOLEAN;
513
514 -------------------------------------------------------------------
515 -- Name: DeleteReportHeaderAtDate
516 -- Desc: get
517 -- Input: P_ReportID - report_header_id for the expense report
518 -- Returns: true - succeeded
519 -- false - failed
520 -------------------------------------------------------------------
521 FUNCTION DeleteReportHeaderAtDate(
522 P_ReportID IN expHdr_headerID,
523 P_LastUpdateDate IN expHdr_lastUpdateDate DEFAULT NULL)
524 RETURN BOOLEAN;
525
526 -------------------------------------------------------------------
527 -- Name: DeleteExpenseReport
528 -- Desc: get
529 -- Input: p_report_header_id - report_header_id for the expense report
530 -- Returns: true - succeeded
531 -- false - failed
532 -------------------------------------------------------------------
533 FUNCTION DeleteExpenseReport(p_report_header_id IN expHdr_headerID)
534 RETURN BOOLEAN;
535
536 -------------------------------------------------------------------
537 -- Name: ResubmitExpenseReport
538 -- Desc: checks to see if report needs to be resubmitted
539 -- Input: p_workflow_approved_flag - workflow_approved_flag for the expense report
540 -- Returns: true - needs to be resubmitted
541 -- false - does not need to be resubmitted
542 -------------------------------------------------------------------
543 FUNCTION ResubmitExpenseReport(p_workflow_approved_flag IN VARCHAR2)
544 RETURN BOOLEAN;
545
546 -------------------------------------------------------------------
547 -- Name: DuplicateHeader
548 -- Desc: duplicates an Expense Report Header
549 -- Input: p_source_report_header_id - source expense report header id
550 -- Returns: p_target_report_header_id - target expense report header id
551 -------------------------------------------------------------------
552 PROCEDURE DuplicateHeader(
553 p_user_id IN NUMBER,
554 p_source_report_header_id IN expHdr_headerID,
555 p_target_report_header_id IN OUT NOCOPY expHdr_headerID);
556
557 --------------------------------------------------------------------------------
558
559 FUNCTION UpdateHeaderTotal(
560 p_report_header_id IN expHdr_headerID
561 ) RETURN BOOLEAN;
562 --------------------------------------------------------------------------------
563 FUNCTION GetReimbCurr(
564 p_expenseReportId IN expHdr_headerID,
565 p_payment_curr_code OUT NOCOPY expHdr_payemntCurrCode
566 ) RETURN BOOLEAN;
567 --------------------------------------------------------------------------------
568 FUNCTION GetHeaderTotal(p_report_header_id IN expHdr_headerID,
569 p_total OUT NOCOPY NUMBER)
570 RETURN BOOLEAN;
571
572 -------------------------------------------------------------------
573 FUNCTION getPaymentDueFromReport(
574 p_report_header_id IN expHdr_headerID,
575 p_paymentDueFromCode OUT NOCOPY VARCHAR2)
576 RETURN BOOLEAN;
577 ------------------------------------------------------------------------------
578
579 FUNCTION getPaymentDueFromReport(p_report_header_id IN expHdr_headerID)
580 RETURN VARCHAR2;
581 -------------------------------------------------------------------
582 FUNCTION getAuditReturnReasonInstr(
583 p_report_header_id IN expHdr_headerID,
584 p_return_reason OUT NOCOPY VARCHAR2,
585 P_return_instruction OUT NOCOPY VARCHAR2)
586 RETURN BOOLEAN;
587 ------------------------------------------------------------------------------
588
589 -------------------------------------------------------------------
590 PROCEDURE clearAuditReturnReasonInstr(
591 p_report_header_id IN expHdr_headerID);
592 ------------------------------------------------------------------------------
593
594 FUNCTION GetDefaultEmpCCID(
595 p_employee_id IN NUMBER,
596 p_default_emp_ccid OUT NOCOPY expHdr_employeeCCID)
597 RETURN BOOLEAN;
598
599 --------------------------------------------------------------------------------
600 FUNCTION GetChartOfAccountsID(
601 p_employee_id IN NUMBER,
602 p_chart_of_accounts_id OUT NOCOPY glsob_chartOfAccountsID)
603 RETURN BOOLEAN;
604
605 --------------------------------------------------------------------------------
606 FUNCTION GetFlexConcactenated(p_parameter_id IN ap_expense_report_params.parameter_id%TYPE,
607 p_FlexConcactenated OUT NOCOPY ap_expense_report_params.FLEX_CONCACTENATED%TYPE)
608 RETURN BOOLEAN;
609
610 --------------------------------------------------------------------------------
611 FUNCTION GetERInvoiceNumber(p_report_header_id IN NUMBER)
612 RETURN VARCHAR2;
613
614 --------------------------------------------------------------------------------
615 FUNCTION GetERWorkflowApprovedFlag(p_report_header_id IN NUMBER)
616 RETURN VARCHAR2;
617
618 --------------------------------------------------------------------------------
619 FUNCTION GetERLastUpdateDate(p_report_header_id IN NUMBER)
620 RETURN VARCHAR2;
621
622 --------------------------------------------------------------------------------
623 PROCEDURE CopyAttachments(p_source_id IN NUMBER,
624 p_target_id IN NUMBER,
625 p_entity_name IN VARCHAR2);
626
627 --------------------------------------------------------------------------------
628
629
630 END AP_WEB_DB_EXPRPT_PKG;