1 PACKAGE AP_WEB_OA_MAINFLOW_PKG AS
2 /* $Header: apwoamfs.pls 120.41.12010000.2 2008/08/06 10:17:47 rveliche ship $ */
3
4 SUBTYPE expLines_currCode IS AP_EXPENSE_REPORT_LINES.currency_code%TYPE;
5 SUBTYPE expLines_expOrgID IS AP_EXPENSE_REPORT_LINES.expenditure_organization_id%TYPE;
6 SUBTYPE expHdr_headerID IS AP_EXPENSE_REPORT_HEADERS.report_header_id%TYPE;
7
8
9 TYPE PARAM_TBL_TYPE IS TABLE OF NUMBER
10 INDEX BY BINARY_INTEGER;
11
12 TYPE CONCATENATED_TBL_TYPE IS TABLE OF VARCHAR2(2000)
13 INDEX BY BINARY_INTEGER;
14
15
16 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := 'S';
17 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := 'E';
18 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := 'U';
19
20 G_EXC_ERROR EXCEPTION;
21 G_EXC_UNEXPECTED_ERROR EXCEPTION;
22
23 PROCEDURE DeleteExpenseReport(
24 ReportID IN expHdr_headerID);
25
26
27 PROCEDURE GetEmployeeIdFromBothPayParent(
28 p_bothpay_parent_id IN NUMBER,
29 p_employee_id OUT NOCOPY NUMBER);
30
31 PROCEDURE GetEmployeeInfo(
32 p_employee_id IN NUMBER,
33 p_employee_name OUT NOCOPY VARCHAR2,
34 p_employee_num OUT NOCOPY VARCHAR2,
35 p_cost_center OUT NOCOPY VARCHAR2,
36 p_is_project_enabled OUT NOCOPY VARCHAR2,
37 p_default_reimb_currency_code OUT NOCOPY VARCHAR2,
38 p_is_cc_enabled OUT NOCOPY VARCHAR2,
39 p_max_num_segments OUT NOCOPY NUMBER,
40 p_userId OUT NOCOPY VARCHAR2
41 );
42
43 PROCEDURE GetGeneralInfo(
44 p_preparer_id IN NUMBER,
45 p_default_expense_template_id OUT NOCOPY NUMBER,
46 p_default_approver_name OUT NOCOPY VARCHAR2,
47 p_default_purpose OUT NOCOPY VARCHAR2,
48 p_default_validate_detail_page OUT NOCOPY VARCHAR2,
49 p_default_skip_cc_if_no_trxn OUT NOCOPY VARCHAR2,
50 p_default_foreign_curr_flag OUT NOCOPY VARCHAR2,
51 p_set_of_books_id OUT NOCOPY NUMBER,
52 p_is_grants_enabled OUT NOCOPY VARCHAR2
53 );
54
55 FUNCTION IsGrantsEnabled RETURN VARCHAR2;
56
57 PROCEDURE ValidateReportHeader(
58 report_header_id IN VARCHAR2,
59 employee_id IN VARCHAR2,
60 cost_center IN VARCHAR2,
61 template_id IN VARCHAR2,
62 template_name IN VARCHAR2,
63 purpose IN VARCHAR2,
64 summary_start_date IN VARCHAR2,
65 last_receipt_date IN VARCHAR2,
66 reimbursement_currency_code IN VARCHAR2,
67 reimbursement_currency_name IN VARCHAR2,
68 multi_currency_flag IN VARCHAR2,
69 override_approver_id IN OUT NOCOPY VARCHAR2,
70 override_approver_name IN OUT NOCOPY VARCHAR2,
71 number_max_flexfield IN VARCHAR2,
72 amt_due_employee IN VARCHAR2,
73 amt_due_ccCompany IN VARCHAR2,
74 p_IsSessionProjectEnabled IN VARCHAR2,
75 p_return_status OUT NOCOPY VARCHAR2,
76 p_msg_count OUT NOCOPY NUMBER,
77 p_msg_data OUT NOCOPY VARCHAR2);
78
79 PROCEDURE ValidateReceiptLine(
80 employee_id IN VARCHAR2,
81 template_id IN VARCHAR2,
82 summary_start_date IN VARCHAR2,
83 reimbursement_currency_code IN VARCHAR2,
84 reimbursement_currency_name IN VARCHAR2,
85 multi_currency_flag IN VARCHAR2,
86 override_approver_name IN VARCHAR2,
87 number_max_flexfield IN VARCHAR2,
88 start_date IN DATE,
89 end_date IN DATE,
90 days IN VARCHAR2,
91 daily_amount IN NUMBER,
92 receipt_amount IN NUMBER,
93 rate IN VARCHAR2,
94 amount IN NUMBER,
95 parameter_id IN VARCHAR2,
96 currency_code IN VARCHAR2,
97 merchant IN VARCHAR2,
98 merchantDoc IN VARCHAR2,
99 taxReference IN VARCHAR2,
100 taxRegNumber IN VARCHAR2,
101 taxPayerId IN VARCHAR2,
102 supplyCountry IN VARCHAR2,
103 itemizeId IN VARCHAR2,
104 cCardTrxnId IN VARCHAR2,
105 group_value IN VARCHAR2,
106 justification IN OUT NOCOPY VARCHAR2,
107 receipt_missing_flag IN VARCHAR2,
108 validation_required IN VARCHAR2,
109 taxOverrideFlag IN VARCHAR2,
110 project_number IN VARCHAR2,
111 task_number IN VARCHAR2,
112 project_name OUT NOCOPY VARCHAR2,
113 task_name OUT NOCOPY VARCHAR2,
114 attribute1 IN VARCHAR2,
115 attribute2 IN VARCHAR2,
116 attribute3 IN VARCHAR2,
117 attribute4 IN VARCHAR2,
118 attribute5 IN VARCHAR2,
119 attribute6 IN VARCHAR2,
120 attribute7 IN VARCHAR2,
121 attribute8 IN VARCHAR2,
122 attribute9 IN VARCHAR2,
123 attribute10 IN VARCHAR2,
124 attribute11 IN VARCHAR2,
125 attribute12 IN VARCHAR2,
126 attribute13 IN VARCHAR2,
127 attribute14 IN VARCHAR2,
128 attribute15 IN VARCHAR2,
129 p_IsSessionTaxEnabled IN VARCHAR2,
130 p_IsSessionProjectEnabled IN VARCHAR2,
131 p_calculate_amt_index IN INTEGER,
132 p_calculated_receipt_amount OUT NOCOPY VARCHAR2,
133 p_return_status OUT NOCOPY VARCHAR2,
134 p_msg_count OUT NOCOPY NUMBER,
135 p_msg_data OUT NOCOPY VARCHAR2,
136 p_userId IN VARCHAR2,
137 award_number IN VARCHAR2,
138 p_cost_center IN VARCHAR2,
139 p_template_name IN VARCHAR2,--Bug 2510993
140 p_purpose IN VARCHAR2,--Bug 2510993
141 p_override_approver_id IN NUMBER, --Bug 2510993
142 p_last_update_date IN DATE, --Bug 2510993
143 -- skaneshi: temporarily put default null so does not cause plsql error
144 receipt_cost_center IN VARCHAR2 DEFAULT NULL,
145 p_transaction_currency_type IN VARCHAR2,--Bug 2510993
146 p_inverse_rate_flag IN VARCHAR2,--Bug 2510993
147 p_report_header_id IN NUMBER,
148 p_category_code IN VARCHAR2, --Bug 2292854
149 -- Per Diem data
150 p_nFreeBreakfasts1 IN NUMBER,
151 p_nFreeBreakfasts2 IN NUMBER,
152 p_nFreeBreakfasts3 IN NUMBER,
153 p_nFreeLunches1 IN NUMBER,
154 p_nFreeLunches2 IN NUMBER,
155 p_nFreeLunches3 IN NUMBER,
156 p_nFreeDinners1 IN NUMBER,
157 p_nFreeDinners2 IN NUMBER,
158 p_nFreeDinners3 IN NUMBER,
159 p_nFreeAccommodations1 IN NUMBER,
160 p_nFreeAccommodations2 IN NUMBER,
161 p_nFreeAccommodations3 IN NUMBER,
162 p_location IN VARCHAR2,
163 -- Mileage data
164 p_dailyDistance IN NUMBER,
165 p_tripDistance IN NUMBER,
166 p_mileageRate IN NUMBER,
167 p_vehicleCategory IN VARCHAR2,
168 p_vehicleType IN VARCHAR2,
169 p_fuelType IN VARCHAR2,
170 p_numberPassengers IN NUMBER,
171 p_default_currency_code IN VARCHAR2,
172 p_default_exchange_rate_type IN VARCHAR2,
173 p_header_attribute_category IN VARCHAR2,
174 p_header_attribute1 IN VARCHAR2,
175 p_header_attribute2 IN VARCHAR2,
176 p_header_attribute3 IN VARCHAR2,
177 p_header_attribute4 IN VARCHAR2,
178 p_header_attribute5 IN VARCHAR2,
179 p_header_attribute6 IN VARCHAR2,
180 p_header_attribute7 IN VARCHAR2,
181 p_header_attribute8 IN VARCHAR2,
182 p_header_attribute9 IN VARCHAR2,
183 p_header_attribute10 IN VARCHAR2,
184 p_header_attribute11 IN VARCHAR2,
185 p_header_attribute12 IN VARCHAR2,
186 p_header_attribute13 IN VARCHAR2,
187 p_header_attribute14 IN VARCHAR2,
188 p_header_attribute15 IN VARCHAR2,
189 p_receipt_index IN NUMBER,
190 p_passenger_rate_used IN NUMBER,
191 p_license_plate_number IN VARCHAR2,
192 p_destination_from IN VARCHAR2,
193 p_destination_to IN VARCHAR2,
194 p_distance_unit_code IN VARCHAR2,
195 p_addon_rates IN OIE_ADDON_RATES_T DEFAULT NULL,
196 p_report_line_id IN NUMBER,
197 p_itemization_parent_id IN NUMBER,
198 -- daily breakup array
199 p_daily_breakup_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
200 p_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
201 p_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
202 p_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
203 p_number_of_meals IN OIE_PDM_NUMBER_T DEFAULT NULL,
204 p_meals_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
205 p_breakfast_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
206 p_lunch_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
207 p_dinner_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
208 p_accommodation_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
209 p_accommodation_flag IN OIE_PDM_VARCHAR_1_T DEFAULT NULL,
210 p_hotel_name IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
211 p_night_rate_Type IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
212 p_night_rate_amount IN OIE_PDM_NUMBER_T DEFAULT NULL,
213 p_pdm_rate IN OIE_PDM_NUMBER_T DEFAULT NULL,
214 p_rate_Type_code IN OIE_PDM_VARCHAR_80_T DEFAULT NULL,
215 p_pdm_breakup_dest_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
216 -- destination array
217 p_pdm_destination_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
218 p_dest_start_date IN OIE_PDM_DATE_T DEFAULT NULL,
219 p_dest_end_date IN OIE_PDM_DATE_T DEFAULT NULL,
220 p_location_id IN OIE_PDM_NUMBER_T DEFAULT NULL,
221 p_cust_meals_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
222 p_cust_accommodation_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
223 p_cust_night_rate_amount IN OUT NOCOPY OIE_PDM_NUMBER_T,
224 p_cust_pdm_rate IN OUT NOCOPY OIE_PDM_NUMBER_T,
225 p_vat_code IN VARCHAR2 DEFAULT NULL, -- Bug: 6719467
226 p_emp_attendee_count IN NUMBER DEFAULT NULL, -- Bug 6919132
227 p_nonemp_attendee_count IN NUMBER DEFAULT NULL -- Bug 6919132
228 );
229
230
231 PROCEDURE OASubmitWorkflow (p_report_header_id IN VARCHAR2,
232 p_preparer_id IN VARCHAR2,
233 p_employee_id IN VARCHAR2,
234 p_invoice_number IN VARCHAR2,
235 p_reimb_curr IN VARCHAR2,
236 p_cost_center IN VARCHAR2,
237 p_purpose IN VARCHAR2,
238 p_approver_id IN VARCHAR2,
239 p_week_end_date IN DATE, -- Bug 3322390
240 p_workflow_appr_flag IN VARCHAR2,
241 p_msg_count OUT NOCOPY NUMBER);
242
243
244 PROCEDURE GetItemDescLookupCode(p_parameter_id IN VARCHAR2,
245 p_item_description OUT NOCOPY VARCHAR2,
246 p_line_type_lookup_code OUT NOCOPY VARCHAR2);
247
248 PROCEDURE GetUserID(p_employee_id IN VARCHAR2,
249 p_user_id OUT NOCOPY VARCHAR2);
250
251 PROCEDURE WithdrawExpenseReport(
252 p_report_header_id IN expHdr_headerID);
253
254 PROCEDURE GetFunctionalCurrencyInfo(p_currencyCode out nocopy varchar2,
255 p_currencyType out nocopy varchar2);
256
257 -------------------------------------------------------------------
258 -- Name: DuplicateExpenseReport
259 -- Desc: duplicates an Expense Report
260 -- Input: p_source_report_header_id - source expense report header id
261 -- Returns: p_target_report_header_id - target expense report header id
262 -------------------------------------------------------------------
263 PROCEDURE DuplicateExpenseReport(
264 p_user_id IN NUMBER,
265 p_source_report_header_id IN expHdr_headerID,
266 p_target_report_header_id IN OUT NOCOPY expHdr_headerID);
267
268 /*------------------------------------------------------------+
269 Created By: Amulya Mishra
270 Bug 2751642: Wrapper function to get ORG_ID value from
271 AP_WEB_DB_HR_INT_PKG.GetEmpOrgId().
272 +-------------------------------------------------------------*/
273
274 FUNCTION GetOrgIDFromHR(p_employee_id IN NUMBER,
275 p_effective_date IN Date) RETURN NUMBER;
276
277 ----------------------------------------------------------------------
278 PROCEDURE GetDefaultAcctgSegValues(
279 P_REPORT_HEADER_ID IN NUMBER,
280 P_REPORT_LINE_ID IN NUMBER,
281 P_OLD_EMPLOYEE_ID IN NUMBER,
282 P_OLD_HEADER_COST_CENTER IN AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE,
283 P_OLD_PARAMETER_ID IN NUMBER,
284 P_NEW_EMPLOYEE_ID IN NUMBER,
285 P_NEW_HEADER_COST_CENTER IN AP_EXPENSE_REPORT_HEADERS.flex_concatenated%TYPE,
286 P_NEW_PARAMETER_ID IN NUMBER,
287 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
288 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
289 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
290 X_MSG_COUNT OUT NOCOPY NUMBER,
291 X_MSG_DATA OUT NOCOPY VARCHAR2,
292 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
293 ----------------------------------------------------------------------
294 PROCEDURE validateAccountSegments(
295 P_REPORT_HEADER_ID IN NUMBER,
296 P_REPORT_LINE_ID IN NUMBER,
297 P_EMPLOYEE_ID IN NUMBER,
298 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
299 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
300 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
301 X_MSG_COUNT OUT NOCOPY NUMBER,
302 X_MSG_DATA OUT NOCOPY VARCHAR2,
303 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
304 ----------------------------------------------------------------------
305 PROCEDURE rebuildAccountSegments(
306 P_REPORT_HEADER_ID IN NUMBER,
307 P_REPORT_LINE_ID IN NUMBER,
308 P_EMPLOYEE_ID IN NUMBER,
309 P_SEGMENTS IN AP_OIE_KFF_SEGMENTS_T,
310 X_SEGMENTS OUT NOCOPY AP_OIE_KFF_SEGMENTS_T,
311 x_combination_id OUT NOCOPY HR_EMPLOYEES_CURRENT_V.default_code_combination_id%TYPE,
312 X_MSG_COUNT OUT NOCOPY NUMBER,
313 X_MSG_DATA OUT NOCOPY VARCHAR2,
314 X_RETURN_STATUS OUT NOCOPY VARCHAR2);
315
316
317
318 ----------------------------------------------------------------------
319
320
321 PROCEDURE updateExpensedAmount(
322 p_trxIds IN AP_WEB_PARENT_PKG.Number_Array,
323 p_expensedAmt IN AP_WEB_PARENT_PKG.Number_Array,
324 p_reportId IN AP_CREDIT_CARD_TRXNS.report_header_id%TYPE);
325
326 ----------------------------------------------------------------------
327
328
329 PROCEDURE DeleteReport(
330 ReportID IN AP_EXPENSE_REPORT_LINES.report_header_id%TYPE);
331
332 ----------------------------------------------------------------------
333
334
335 PROCEDURE updChargesFromDeletedReport(
336 p_idArray IN AP_WEB_PARENT_PKG.number_Array,
337 p_amtArray IN AP_WEB_PARENT_PKG.number_Array);
338
339
340
341 END AP_WEB_OA_MAINFLOW_PKG;