1 PACKAGE AP_WEB_UTILITIES_PKG AUTHID CURRENT_USER AS
2 /* $Header: apwxutls.pls 120.28 2011/11/17 10:02:04 meesubra ship $ */
3
4 -- rlangi - used for converting OIE specific messages to OME messages
5 C_WebApplicationVersion CONSTANT VARCHAR2(1) := 'W';
6 C_MobileApplicationVersion CONSTANT VARCHAR2(1) := 'M';
7 C_PAMessageCategory CONSTANT VARCHAR2(15) := 'PA'; -- Project/Task errors
8 C_PATCMessageCategory CONSTANT VARCHAR2(15) := 'PATC'; -- PATC errors
9 C_GMSMessageCategory CONSTANT VARCHAR2(15) := 'GMS'; -- GMS errors
10 C_TaxMessageCategory CONSTANT VARCHAR2(15) := 'TAX'; -- Tax errors
11 C_ItemizationMessageCategory CONSTANT VARCHAR2(15) := 'ITEMIZATION'; -- Itemization errors
12 C_DFFMessageCategory CONSTANT VARCHAR2(15) := 'DFF'; -- Flexfield errors
13 C_AcctgMessageCategory CONSTANT VARCHAR2(15) := 'ACCTG'; -- Line accounting errors
14 C_OtherMessageCategory CONSTANT VARCHAR2(15) := 'OTHER'; -- All other errors OME will display same OIE error
15
16 C_ErrorMessageType CONSTANT VARCHAR2(10) := 'ERROR';
17 C_WarningMessageType CONSTANT VARCHAR2(10) := 'WARNING';
18 C_ProjectNumber_VRPrompt CONSTANT NUMBER := 34;
19 C_TaskNumber_VRPrompt CONSTANT NUMBER := 35;
20
21 -- chiho:1203036:
22 C_MSG_FIELD_LEN CONSTANT NUMBER := 2000;
23 C_MSG_TEXT_LEN CONSTANT NUMBER := 2000;
24
25 MSG_FIELD_TYPE VARCHAR2(2000);
26 MSG_TEXT_TYPE VARCHAR2(2000);
27
28
29 C_IMG_DIR CONSTANT VARCHAR2(10) := '/OA_MEDIA/';
30
31 TYPE MiniString_Array IS TABLE OF VARCHAR2(25)
32 INDEX BY BINARY_INTEGER;
33 TYPE MedString_Array IS TABLE OF VARCHAR2(80)
34 INDEX BY BINARY_INTEGER;
35 TYPE BigString_Array IS TABLE OF VARCHAR2(240)
36 INDEX BY BINARY_INTEGER;
37 TYPE LongString_Array IS TABLE OF VARCHAR2(1000)
38 INDEX BY BINARY_INTEGER;
39 TYPE Number_Array IS TABLE OF NUMBER
40 INDEX BY BINARY_INTEGER;
41 TYPE Boolean_Array IS TABLE OF BOOLEAN
42 INDEX BY BINARY_INTEGER;
43
44 TYPE MessageRec IS RECORD (
45 receipt_num INTEGER,
46 message_type VARCHAR2(10), -- ERROR/WARNING
47 message_text long := '',
48 field_id1 integer,
49 field_id2 integer,
50 field_id3 integer,
51 field_id4 integer,
52 field_id5 integer );
53
54 TYPE MessageArray IS TABLE OF MessageRec
55 INDEX BY BINARY_INTEGER;
56
57 TYPE expErrors IS RECORD (
58 text LONG,
59 type VARCHAR2(25), -- Constant: error, warning
60 field VARCHAR2(35) := '',
61 ind BINARY_INTEGER := 0);
62
63 TYPE expError IS TABLE OF expErrors
64 INDEX BY BINARY_INTEGER;
65
66 TYPE prompts_table IS table of varchar2(80)
67 index by binary_integer;
68
69 -- Bug: 6220330
70 TYPE ExpensesToUpdate IS REF CURSOR;
71
72
73 FUNCTION GetIsMobileApp RETURN NUMBER;
74 FUNCTION IsMobileApp RETURN BOOLEAN;
75
76 -- rlangi: Diagnostic Logging
77 PROCEDURE LogException(p_pkgname IN VARCHAR2,
78 p_message IN VARCHAR2);
79
80 PROCEDURE LogEvent(p_pkgname IN VARCHAR2,
81 p_message IN VARCHAR2);
82
83 PROCEDURE LogProcedure(p_pkgname IN VARCHAR2,
84 p_message IN VARCHAR2);
85
86 PROCEDURE LogStatement(p_pkgname IN VARCHAR2,
87 p_message IN VARCHAR2);
88
89 PROCEDURE AddExpError(p_errors IN OUT NOCOPY expError,
90 p_text IN VARCHAR2,
91 p_type IN VARCHAR2,
92 p_field IN VARCHAR2 DEFAULT NULL,
93 p_index IN BINARY_INTEGER DEFAULT 0,
94 p_MessageCategory IN VARCHAR2 DEFAULT C_OtherMessageCategory,
95 p_IsMobileApp IN BOOLEAN DEFAULT false);
96
97 PROCEDURE AddExpErrorNotEncoded(p_errors IN OUT NOCOPY expError,
98 p_text IN VARCHAR2,
99 p_type IN VARCHAR2,
100 p_field IN VARCHAR2 DEFAULT NULL,
101 p_index IN BINARY_INTEGER DEFAULT 0,
102 p_MessageCategory IN VARCHAR2 DEFAULT C_OtherMessageCategory);
103
104 -- chiho:1203036:expand the fields length:
105 TYPE RECEIPT_ERROR_REC IS RECORD
106 (
107 error_text MSG_TEXT_TYPE%TYPE := '',
108 error_fields MSG_FIELD_TYPE%TYPE := '',
109 warning_text MSG_TEXT_TYPE%TYPE := '',
110 warning_fields MSG_FIELD_TYPE%TYPE := ''
111 );
112
113 TYPE RECEIPT_ERROR_STACK IS TABLE OF RECEIPT_ERROR_REC
114 INDEX BY BINARY_INTEGER;
115
116
117 PROCEDURE DisplayException (P_ErrorText Long);
118
119 PROCEDURE GetEmployeeInfo(p_employee_name IN OUT NOCOPY VARCHAR2,
120 p_employee_num IN OUT NOCOPY VARCHAR2,
121 p_cost_center IN OUT NOCOPY VARCHAR2,
122 p_employee_id IN NUMBER);
123
124
125 PROCEDURE MakeArray;
126 PROCEDURE MoneyFormat;
127 PROCEDURE MoneyFormat2;
128 PROCEDURE SetReceiptWarningErrorMessage;
129
130
131
132 PROCEDURE JustifFlagElement;
133 PROCEDURE RetrieveJustifFlag;
134 PROCEDURE RetrieveJustifFlagIndex;
135 PROCEDURE CurrencyInfo;
136 PROCEDURE RetrieveCurrencyIndex;
137 PROCEDURE PopulateCurrencyArray;
138 FUNCTION NDaysInCalendar(p_cal_end_date IN DATE,
139 p_start_dow IN NUMBER) RETURN NUMBER;
140 PROCEDURE GoBack;
141 PROCEDURE CancelExpenseReport;
142 PROCEDURE ExitExpenseReport;
143 PROCEDURE GetUserAgent(p_user_agent IN OUT NOCOPY VARCHAR2);
144 PROCEDURE DisplayHelp(v_defHlp IN VARCHAR2);
145 PROCEDURE OverrideRequired(p_apprReqCC IN varchar2,
146 p_overrideReq IN varchar2);
147
148 PROCEDURE PrepArg(p_arg in out nocopy long);
149 PROCEDURE DownloadHTML(P_FileName IN VARCHAR2);
150
151 FUNCTION ContainsErrorOrWarning(
152 P_MessageArray IN receipt_error_stack)
153 RETURN BOOLEAN;
154
155 FUNCTION ContainsError(
156 P_MessageArray IN receipt_error_stack)
157 RETURN BOOLEAN;
158
159 FUNCTION ContainsWarning(
160 P_MessageArray IN receipt_error_stack)
161 RETURN BOOLEAN;
162
163 FUNCTION ReceiptContainsError(
164 P_MessageArray IN receipt_error_stack,
165 P_ReceiptNumber IN INTEGER)
166 RETURN BOOLEAN;
167
168 FUNCTION ReceiptContainsWarning(
169 P_MessageArray IN receipt_error_stack,
170 P_ReceiptNumber IN INTEGER)
171 RETURN BOOLEAN;
172
173 FUNCTION NumOfReceiptWithError(
174 P_MessageArray IN receipt_error_stack)
175 RETURN NUMBER;
176
177 FUNCTION NumOfReceiptWithWarning(
178 P_MessageArray IN receipt_error_stack)
179 RETURN NUMBER;
180
181 FUNCTION NumOfValidReceipt(
182 P_MessageArray IN receipt_error_stack)
183 RETURN NUMBER;
184
185 PROCEDURE AddMessage(
186 P_MessageArray IN OUT NOCOPY receipt_error_stack,
187 P_ReceiptNum IN INTEGER,
188 P_MessageType IN VARCHAR2,
189 P_MessageText IN VARCHAR2,
190 P_Field1 IN VARCHAR2 DEFAULT NULL,
194 P_Field5 IN VARCHAR2 DEFAULT NULL);
191 P_Field2 IN VARCHAR2 DEFAULT NULL,
192 P_Field3 IN VARCHAR2 DEFAULT NULL,
193 P_Field4 IN VARCHAR2 DEFAULT NULL,
195
196 PROCEDURE MergeErrorStacks(p_ReceiptNum IN INTEGER,
197 P_Src1ReceiptStack IN
198 receipt_error_stack,
199 P_Src2ReceiptStack IN
200 receipt_error_stack,
201 P_TargetReceiptStack IN OUT
202 receipt_error_stack);
203
204 PROCEDURE MergeErrors(P_ExpErrors IN expError,
205 P_TargetReceiptStack IN OUT NOCOPY receipt_error_stack);
206
207 PROCEDURE MergeExpErrors(P_Src1 IN OUT NOCOPY expError,
208 P_Src2 IN expError);
209
210 PROCEDURE PrintMessages(P_SrcReceiptStack IN
211 receipt_error_stack);
212
213 PROCEDURE InitMessages(P_NumOfReceipts IN INTEGER,
214 P_SrcReceiptStack OUT
215 receipt_error_stack);
216
217 FUNCTION FieldContainsError(
218 P_MessageArray IN receipt_error_stack,
219 P_ReceiptNumber IN INTEGER,
220 P_FieldNumber IN VARCHAR2)
221 RETURN BOOLEAN;
222
223 PROCEDURE ClearMessages(
224 P_TargetReceiptStack OUT NOCOPY receipt_error_stack);
225
226 PROCEDURE CopyMessages(
227 P_SrcReceiptStack IN receipt_error_stack,
228 P_TargetReceiptStack IN OUT NOCOPY receipt_error_stack);
229
230 PROCEDURE ArrayifyText(P_ErrorText IN LONG,
231 P_ErrorTextArray OUT NOCOPY LongString_Array);
232
233 PROCEDURE ArrayifyErrorText(P_ReceiptErrors IN receipt_error_stack,
234 P_ReceiptNum IN INTEGER,
235 P_ErrorTextArray OUT NOCOPY LongString_Array);
236
237 PROCEDURE ArrayifyWarningText(P_ReceiptErrors IN receipt_error_stack,
238 P_ReceiptNum IN INTEGER,
239 P_ErrorTextArray OUT NOCOPY LongString_Array);
240
241 PROCEDURE ArrayifyErrorFields(P_ReceiptErrors IN receipt_error_stack,
242 P_ReceiptNum IN INTEGER,
243 P_ErrorFieldArray OUT NOCOPY Number_Array);
244
245 PROCEDURE ArrayifyWarningFields(P_ReceiptErrors IN receipt_error_stack,
246 P_ReceiptNum IN INTEGER,
247 P_ErrorFieldArray OUT NOCOPY Number_Array);
248
249 PROCEDURE ConvertDate;
250
251 PROCEDURE DetermineConversion;
252
253
254 FUNCTION getEuroCode RETURN VARCHAR2;
255
256 /*------------------------------------------------------------+
257 Fix 1435885 : To prevent pcard packages from getting
258 Invalid. These functions are not used by SSE.
259 Functions include IsNum, DisplayHelp, GenericButton,
260 GenToolBarScript, GenToolBar, GenButton, StyleSheet
261 +-------------------------------------------------------------*/
262
263 PROCEDURE IsNum;
264
265 PROCEDURE DisplayHelp;
266
267 PROCEDURE GenToolbarScript;
268
269 PROCEDURE GenToolbar(p_title VARCHAR2,
270 p_print_frame VARCHAR2,
271 p_save_flag BOOLEAN,
272 p_save_disabled_flag BOOLEAN,
273 p_save_call VARCHAR2 DEFAULT NULL);
274
275 PROCEDURE GenButton(P_Button_Text varchar2,
276 P_OnMouseOverText varchar2,
277 P_HyperTextCall varchar2);
278
279 PROCEDURE StyleSheet;
280
281 FUNCTION RtrimMultiByteSpaces(p_input_string IN varchar2) RETURN VARCHAR2;
282
283 FUNCTION GetDistanceDisplayValue(p_value IN NUMBER,
284 p_format IN VARCHAR2) RETURN NUMBER;
285
286 FUNCTION VALUE_SPECIFIC(p_name IN VARCHAR2,
287 p_user_id IN NUMBER default null,
288 p_resp_id IN NUMBER default null,
289 p_apps_id IN NUMBER default null) RETURN VARCHAR2;
290
291 FUNCTION getMinipackVersion RETURN VARCHAR2;
292
293 /*
294 Written by:
295 Maulik Vadera
296 Purpose:
297 To get the override approver name, when profile option,
298 IE:Approver Required = "Yes with Default" and approver name is not provided
299 in the upload SpreadSheet data.
300 Fix for bug 3786831
301 Input:
302 p_EmpId: Employee Id of the employee
303 Output:
304 Override approver Id for that Employee Id
305 Override approver name for that Employee Id
306 Date:
307 21-Mar-2005
308 */
309
310 PROCEDURE GetOverrideApproverDetail(p_EmpId IN NUMBER,
311 p_appreq IN VARCHAR2,
312 p_ApproverId OUT NOCOPY HR_EMPLOYEES.employee_num%TYPE,
313 p_OverrideApproverName OUT NOCOPY HR_EMPLOYEES.full_name%TYPE);
314
315
316
317 PROCEDURE ExpenseSetOrgContext(p_report_header_id IN NUMBER);
318
319
320 /*=======================================================================
321 | PUBLIC FUNCITON: OrgSecurity
322 |
323 | DESCRIPTION: This function will return the security predicate
324 | for expense report templates and expense types table.
325 | It ensures that the seeded template and expense types
326 | with org_id = -99 are also picked up when querying
327 | the secured synonym
328 |
329 | PARAMETERS
330 | obj_schema IN VARCHAR2 Object Schema
331 | obj_name IN VARCHAR2 Object Name
332 |
333 | RETURNS
334 | Where clause to be appended to the object.
335 *=======================================================================*/
336 FUNCTION OrgSecurity ( obj_schema VARCHAR2,
337 obj_name VARCHAR2) RETURN VARCHAR2;
338
339 -- Bug: 6220330, added a new parameter so that the trigger on ap_invoices_all can use this.
340 PROCEDURE UpdateExpenseStatusCode(
341 p_invoice_id AP_INVOICES_ALL.invoice_id%TYPE,
342 p_pay_status_flag AP_INVOICES_ALL.payment_status_flag%TYPE DEFAULT NULL
343 );
344
345 /*--------------------------------------------------+
346 Fix for bug 5586280 Do not pad the cost center if it
347 contains any character or special character including
348 a period
349 +--------------------------------------------------*/
350
351 FUNCTION ContainsChars(p_element IN VARCHAR2) RETURN BOOLEAN;
352
353 -- Bug: 6220330
354 PROCEDURE GetExpensesToUpdate(p_invoice_id IN AP_INVOICES_ALL.invoice_id%TYPE,
355 p_pay_status_flag IN AP_INVOICES_ALL.payment_status_flag%TYPE,
356 p_expenses_to_update OUT NOCOPY ExpensesToUpdate);
357
358 ------------------------------------------------------------------------
359 -- FUNCTION Oie_Round_Currency
360 -- Bug 6136103
361 -- Returns Amount in the Rounded format per spec in fnd_currencies
362 -- Introduced as aputilsb.ap_round_currency errors out due to Caching.
363 ------------------------------------------------------------------------
364 FUNCTION Oie_Round_Currency
365 (P_Amount IN number
366 ,P_Currency_Code IN varchar2)
367 RETURN NUMBER;
368
369 PROCEDURE UpdateImageReceiptStatus(p_report_header_id IN NUMBER);
370
371 FUNCTION GetImageAttachmentStatus(p_report_header_id IN NUMBER) RETURN VARCHAR2;
372
373 FUNCTION GetAttachmentExists(p_entity_name IN VARCHAR2, p_value IN VARCHAR2) RETURN VARCHAR2;
374
375 -----------------------------------------------------------------------------------------------
376 FUNCTION GetShortPaidReportMsg(p_report_header_id in NUMBER) RETURN VARCHAR2;
377 -----------------------------------------------------------------------------------------------
378
379 -----------------------------------------------------------------------------------------------
380 PROCEDURE AddReportToAuditQueue(p_report_header_id IN NUMBER, p_receipt_type IN VARCHAR2 DEFAULT NULL);
381 -----------------------------------------------------------------------------------------------
382
383 FUNCTION GetReceiptRuleExists(p_report_header_id IN NUMBER, p_org_id IN NUMBER) RETURN VARCHAR2;
384
385 FUNCTION GetReceiptRequiredStatus(p_report_header_id IN NUMBER, p_org_id IN NUMBER, p_receipt_type IN VARCHAR2) RETURN VARCHAR2;
386
387 -----------------------------------------------------------------------------------------------
388 FUNCTION ReportHasViolations(p_report_header_id IN NUMBER) RETURN BOOLEAN;
389 FUNCTION ReportHasOnlyCCLines(p_report_header_id IN NUMBER) RETURN BOOLEAN;
390 FUNCTION ReportHasNonRecLines(p_report_header_id IN NUMBER) RETURN BOOLEAN;
391
392 ------------------------------------------------------------------------------------------------
393 -- PROCEDURE ProcessImageReceiptStatus
394 -- Invoked from the ExpensesAttachmentService
395 -----------------------------------------------------------------------------------------------
396 PROCEDURE ProcessImageReceiptStatus(p_report_header_id IN NUMBER);
397 ------------------------------------------------------------------------------------------------
398 ------------------------------------------------------------------------------------------------
399 FUNCTION GetMissingReceiptStatus(p_report_header_id IN NUMBER) RETURN VARCHAR2;
400 -----------------------------------------------------------------------------------------------
401
402
403 END AP_WEB_UTILITIES_PKG;
[Home] [Help]