1 PACKAGE PA_UTILS AUTHID CURRENT_USER AS
2 /* $Header: PAXGUTLS.pls 120.10.12020000.5 2013/04/03 10:09:38 byeturi ship $ */
3
4 -- Global variable for business group id
5 G_Business_Group_Id NUMBER;
6
7 TYPE Char1TabTyp IS TABLE OF VARCHAR2(1)
8 INDEX BY BINARY_INTEGER;
9
10 EmptyChar1Tab Char1TabTyp;
11
12 TYPE Char10TabTyp IS TABLE OF VARCHAR2(10)
13 INDEX BY BINARY_INTEGER;
14
15 EmptyChar10Tab Char10TabTyp;
16
17 TYPE Char20TabTyp IS TABLE OF VARCHAR2(20)
18 INDEX BY BINARY_INTEGER;
19
20 EmptyChar20Tab Char20TabTyp;
21
22 TYPE Char25TabTyp IS TABLE OF VARCHAR2(25)
23 INDEX BY BINARY_INTEGER;
24
25 EmptyChar25Tab Char25TabTyp;
26
27 TYPE Char30TabTyp IS TABLE OF VARCHAR2(30)
28 INDEX BY BINARY_INTEGER;
29
30 EmptyChar30Tab Char30TabTyp;
31
32 TYPE Char50TabTyp IS TABLE OF VARCHAR2(50) -- Added for Bug 12371923
33 INDEX BY BINARY_INTEGER;
34
35 EmptyChar50Tab Char50TabTyp; -- Added for Bug 12371923
36
37 TYPE Char150TabTyp IS TABLE OF VARCHAR2(150)
38 INDEX BY BINARY_INTEGER;
39
40 EmptyChar150Tab Char150TabTyp;
41
42 TYPE Char240TabTyp IS TABLE OF VARCHAR2(240)
43 INDEX BY BINARY_INTEGER;
44
45 EmptyChar240Tab Char240TabTyp;
46
47 TYPE DateTabTyp IS TABLE OF DATE
48 INDEX BY BINARY_INTEGER;
49
50 EmptyDateTab DateTabTyp;
51
52 TYPE IdTabTyp IS TABLE OF NUMBER(15)
53 INDEX BY BINARY_INTEGER;
54
55 EmptyIdTab IdTabTyp;
56
57 TYPE AmtTabTyp IS TABLE OF NUMBER --Bug 12772417: Changed datatype from NUMBER(22) to NUMBER
58 INDEX BY BINARY_INTEGER;
59
60 EmptyAmtTab AmtTabTyp;
61
62 TYPE NewAmtTabTyp IS TABLE OF NUMBER
63 INDEX BY BINARY_INTEGER;
64
65 EmptyNewAmtTab NewAmtTabTyp;
66
67 TYPE Char15TabTyp IS TABLE OF VARCHAR2(15)
68 INDEX BY BINARY_INTEGER;
69
70 EmptyChar15Tab Char15TabTyp;
71
72 record_count BINARY_INTEGER DEFAULT 0;
73
74 --Global variable to store employee_id
75 Global_Employee_Id NUMBER := NULL;
76
77 TYPE WeekEndDateTab IS TABLE OF DATE
78 INDEX BY BINARY_INTEGER;
79 G_WeekEndDateTab WeekEndDateTab;
80
81 /* Start changes for bug#12646514 */
82
83 G_Return_Status VARCHAR2(30);
84
85 /* End changes for bug#12646514 */
86
87 -- PUBLIC PROCEDURES and FUNCTIONS
88 --
89
90 PROCEDURE GetProjInfo ( X_proj_id IN NUMBER
91 , X_proj_num OUT NOCOPY VARCHAR2
92 , X_proj_name OUT NOCOPY VARCHAR2 );
93
94 -- This procedure accepts as input a project ID and returns as output the
95 -- project's project number and name. If there is no project record that
96 -- matches the given project ID, then this procedure returns NULL for both
97 -- the project number and project name.
98
99 PROCEDURE GetTaskInfo ( X_task_id IN NUMBER
100 , X_task_num OUT NOCOPY VARCHAR2
101 , X_task_name OUT NOCOPY VARCHAR2 );
102
103 -- This procedure accepts as input a task ID and returns as output the
104 -- task's task number and name. If there is no task record that
105 -- matches the given task ID, then this procedure returns NULL for both
106 -- the task number and task name.
107
108 FUNCTION GetProjId ( X_project_num IN VARCHAR2 ) RETURN NUMBER;
109 -- pragma RESTRICT_REFERENCES ( GetProjId, WNDS, WNPS);
110
111 -- This function accepts as input a project number and returns as output the
112 -- project's project ID. If there is no project that matches the project
113 -- number given, then this function returns NULL.
114
115 FUNCTION GetEmpId ( X_emp_num IN VARCHAR2 ) RETURN NUMBER;
116 pragma RESTRICT_REFERENCES ( GetEmpId, WNDS, WNPS );
117
118 -- This function accepts as input an employee number and returns as output the
119 -- employee's person ID. If there is no employee record that matches the
120 -- employee number given, then this function returns NULL.
121
122 FUNCTION GetEmpIdFromUser ( X_userid IN NUMBER ) RETURN NUMBER;
123 pragma RESTRICT_REFERENCES ( GetEmpIdFromUser, WNDS, WNPS);
124
125 -- This function accepts as input a user ID and returns as output the user's
126 -- person ID. If the user is not an employee, or the user ID given is not
127 -- valid, then this function returns NULL.
128
129 FUNCTION GetEmpName ( X_person_id IN NUMBER ) RETURN VARCHAR2;
130 pragma RESTRICT_REFERENCES ( GetEmpName, WNDS, WNPS );
131
132 -- This function accepts as input a person ID and returns as output the
133 -- person's full name. If there is no person record that matches the person
134 -- ID given, then this function returns NULL.
135
136 FUNCTION GetTaskId ( X_proj_id IN NUMBER
137 , X_task_num IN VARCHAR2 ) RETURN NUMBER;
138 -- pragma RESTRICT_REFERENCES ( GetTaskId, WNDS, WNPS );
139
140 -- This function accepts as input a project ID and a task number and returns
141 -- as output the task's task ID. If there is no task record for the given
142 -- project ID and task number, then this function returns NULL.
143
144 FUNCTION GetOrgId ( X_org_name IN VARCHAR2 ) RETURN NUMBER;
145 -- pragma RESTRICT_REFERENCES ( GetOrgId, WNDS, WNPS );
146
147 --Bug#3010848
148 PROCEDURE GetOrgnId ( X_org_name IN VARCHAR2
149 ,X_bg_id IN NUMBER DEFAULT NULL
150 ,X_Orgn_Id OUT NOCOPY Number
151 ,X_Return_Status OUT NOCOPY Varchar2);
152
153
154 -- This function accepts as input an organization name and business group id.
155 -- and returns as output the organization's organization ID.
156 -- If there is no organization record
157 -- for the given organization name, then this function returns NULL.
158
159 FUNCTION GetOrgName ( X_org_id IN NUMBER ) RETURN VARCHAR2;
160 pragma RESTRICT_REFERENCES ( GetOrgName, WNDS, WNPS );
161
162 -- This function accepts as input an organization ID and returns as output
163 -- the organization's name. If there is no organization record for the
164 -- given organization ID, then this function returns NULL.
165
166 FUNCTION GetWeekEnding ( X_date IN DATE ) RETURN DATE;
167 pragma RESTRICT_REFERENCES ( GetWeekEnding, WNDS, WNPS );
168
169 -- This function accepts as input a date in the format DD-MON-YY and returns
170 -- as output the ending date of the expenditure week in which this date
171 -- occurs.
172
173 FUNCTION DateInExpWeek ( X_date IN DATE
174 , X_week_end IN DATE ) RETURN BOOLEAN;
175 -- pragma RESTRICT_REFERENCES ( DateInExpWeek, WNDS, WNPS );
176
177 -- This function accepts as input a test date and a week ending date. If the
178 -- the test date is within the expenditure week that ends on the week end
179 -- date provided, then this function returns TRUE. Otherwise, it returns
180 -- FALSE. Note: this function assumes that the week end date provided is
181 -- a valid week ending date.
182
183 FUNCTION GetEmpOrgId ( X_person_id IN NUMBER
184 , X_date IN DATE ) RETURN NUMBER;
185 -- pragma RESTRICT_REFERENCES ( GetEmpOrgId, WNDS, WNPS );
186
187 -- This function accepts as input a person ID and a date and returns the
188 -- organization ID of the person's organization assignment as of the input
189 -- date. If the person does not have a current assignment, or if the person
190 -- ID passed is not valid, then this function returns NULL.
191
192 FUNCTION GetEmpCostRate ( X_person_id IN NUMBER
193 , X_date IN DATE ) RETURN NUMBER;
194 -- pragma RESTRICT_REFERENCES ( GetEmpCostRate, WNDS, WNPS );
195
196 -- This function accepts as input a person ID and a date and returns the
197 -- hourly cost rate defined for the person as of the input date. If the
198 -- person does not have a cost rate as of the input date, then this function
199 -- returns NULL.
200
201 FUNCTION GetExpTypeCostRate ( X_expenditure_type IN VARCHAR2
202 , X_date IN DATE ) RETURN NUMBER;
203 pragma RESTRICT_REFERENCES ( GetExpTypeCostRate, WNDS, WNPS );
204
205 FUNCTION GetEmpJobId ( X_person_id IN NUMBER
206 , X_date IN DATE
207 , X_person_type IN VARCHAR2 DEFAULT NULL
208 , X_po_number IN VARCHAR2 DEFAULT NULL -- Bug 4044057
209 , X_po_line_num IN NUMBER DEFAULT NULL -- Bug 4044057
210 , X_po_header_id IN NUMBER DEFAULT NULL -- Bug 4044057
211 , X_po_line_id IN NUMBER DEFAULT NULL ) RETURN NUMBER;
212 -- pragma RESTRICT_REFERENCES ( GetEmpJobId, WNDS, WNPS );
213
214 FUNCTION GetNextEiId RETURN NUMBER;
215
216 -- This function selects and returns the next value in the sequence,
217 -- PA_EXPENDITURE_ITEMS_S.
218
219 FUNCTION CheckExpTypeActive( X_expenditure_type IN VARCHAR2
220 , X_date IN DATE ) RETURN BOOLEAN;
221 pragma RESTRICT_REFERENCES (CheckExpTypeActive, WNDS, WNPS );
222
223 -- This function accepts as input an expenditure type and a date and returns
224 -- either TRUE or FALSE depending on wheter the expenditure type exists and
225 -- is active as of the date parameter.
226
227
228 FUNCTION get_org_hierarchy_top ( X_org_structure_version_id IN NUMBER )
229 RETURN NUMBER;
230 pragma RESTRICT_REFERENCES (get_org_hierarchy_top, WNDS, WNPS);
231
232 -- This function accepts as input the ORG_STRUCTURE_VERSION_ID for the
233 -- organization hierarchy for which you want to get the top organization.
234 -- The return value is the organization_id for the top organization in that
235 -- hierarchy.
236
237 FUNCTION Get_business_group_id RETURN NUMBER;
238 pragma RESTRICT_REFERENCES ( Get_business_group_id, WNDS, WNPS );
239
240 PROCEDURE Set_business_group_id ; --sets global var. G_Business_Group_Id
241
242 FUNCTION business_group_id RETURN NUMBER;
243 pragma RESTRICT_REFERENCES (business_group_id, WNDS, WNPS,RNPS);
244
245 -- Ramesh Krishnamurthy - 01-APR-1997 -- Added pragma RNPS since in the
246 -- absence of this pragma,views which reference this function cannot
247 -- be accessed remotely.
248
249 -- This function returns the BUSINESS_GROUP_ID defined in PA_IMPLEMENTATIONS
250 -- for the current operating unit.
251
252 FUNCTION is_project_costing_installed RETURN VARCHAR2;
253 -- pragma RESTRICT_REFERENCES (is_project_costing_installed, WNDS, WNPS );
254
255 -- This function returns the 'Y' if Project Costing is installed.
256 -- Otherwise, the function returns 'N'.
257
258 FUNCTION IsCrossChargeable( X_Project_Id Number ) RETURN BOOLEAN;
259 -- pragma RESTRICT_REFERENCES (IsCrossChargeable, WNDS, WNPS);
260
261 -- This function returns if the project Id passed is cross chargeable
262
263
264 FUNCTION pa_morg_implemented RETURN VARCHAR2;
265 pragma RESTRICT_REFERENCES (pa_morg_implemented, WNDS, WNPS);
266
267 -- This function returns 'Y' if multi-org is implemented, otherwise, it
268 -- returns 'N'.
269
270 FUNCTION CheckProjectOrg (x_org_id IN NUMBER) RETURN VARCHAR2;
271 pragma RESTRICT_REFERENCES ( CheckProjectOrg, WNDS, WNPS);
272
273 -- G.Prothia - 15-AUG-97 - Removed pragma RNPS
274 --
275 -- Ramesh Krishnamurthy - 01-APR-1997 -- Added pragma RNPS since in the
276 -- absence of this pragma,views which reference this function cannot
277 -- be accessed remotely.
278
279 -- This function returns 'Y' if a given org is a project organization ,
280 -- otherwise , it returns 'N'
281
282 ------------------------------------------------------------------------
283 --function : get_pa_date
284 -- (Formerly called pa_date_from_gl_date)
285 -- Derive PA Date from GL date and ei date .
286 -- This function accepts the expenditure item date and the GL date and
287 -- derives the PA date based on this. The function has been modified
288 -- to not use the gl_Date (though it is still accepted as a parameter
289 -- just in case the logic changes in the future to use the gl_Date).
293 ------------------------------------------------------------------------
290 -- This is mainly used for AP invoices and transactions imported from
291 -- other systems where the GL date is known in advance and the PA date
292 -- has to be determined.
294 FUNCTION get_pa_date( x_ei_date IN date, x_gl_date IN date ) return date ;
295 -- PRAGMA RESTRICT_REFERENCES ( get_pa_date, WNDS, WNPS ) ;
296
297 ----------------------------------------------------------------------
298 -- Function : get_pa_end_date
299 -- Derive the period end date based on the period name
300 --
301 -- This function accepts the period name and gets the period end
302 -- date from the pa_periods table. The function created for
303 -- burden cost accounting.
304 -- Created by Sandeep 04-MAR-1998
305 -----------------------------------------------------------------------
306 FUNCTION get_pa_end_date( x_pa_period_name IN varchar2 ) return date ;
307 PRAGMA RESTRICT_REFERENCES ( get_pa_end_date, WNDS, WNPS ) ;
308
309 ------------------------------------------------------------------------
310 -- function : get_pa_period_name
311 -- Derive Period name from GL date and ei date .
312 -- This function accepts the expenditure item date and the GL date and
313 -- derives the period name based on this. In its current form, it does
314 -- not use the GL date but derives the period name solely based on the
315 -- expenditure item date. However, the GL date is retained as a
316 -- parameter just in case it is required to derive the pa_date based
317 -- on it in the future.
318 -- This is mainly used for AP invoices and transactions imported from
319 -- other systems where the GL date is known in advance and the PA date
320 -- has to be determined. The pa_date_from_gl_date function returns the
321 -- PA date. This function is identical except that it returns the
322 -- corresponding period name.
323 ------------------------------------------------------------------------
324 FUNCTION get_pa_period_name( x_ei_date IN date, x_gl_date IN date ) return varchar2 ;
325 -- PRAGMA RESTRICT_REFERENCES ( get_pa_period_name, WNDS, WNPS ) ;
326
327 FUNCTION GetETypeClassCode (x_system_linkage IN VARCHAR2) RETURN VARCHAR2;
328 --pragma RESTRICT_REFERENCES ( GetETypeClassCode, WNDS, WNPS );
329
330 ---------------------------------------------------------------
331 -- function : Get_Org_Window_Title
332 -- This function that returns the organization name. If multi-org
333 -- is enabled then the organization name (operating Unit) is derived from
334 -- the profile option instead of implementation options. If multi-org is not
335 -- enabled then display set of books name in window title.
336 ---------------------------------------------------------------
337
338 FUNCTION Get_Org_Window_Title return varchar2;
339
340
341 ---------------------------------------------------------------
342 -- Function : GetGlobalEmpId
343 -- This function returns the packaged variable Global_Employee_Id.
344 ---------------------------------------------------------------
345 FUNCTION GetGlobalEmpId RETURN NUMBER;
346
347 ---------------------------------------------------------------
348 -- Procedure : SetGlobalEmpId
349 -- This procedure sets the packaged variable Global_Employee_Id.
350 ---------------------------------------------------------------
351
352 PROCEDURE SetGlobalEmpId( p_emp_id NUMBER );
353
354 ---------------------------------------------------------------
355 -- Procedure : Get_Encoded_Msg
356 -- This procedure serves as a wrapper to the function
357 -- FND_MSG_PUB.Get. It is needed to access the call from
358 -- client FORMS.
359 ---------------------------------------------------------------
360
361 Procedure Get_Encoded_Msg(p_index IN NUMBER,
362 p_msg_out IN OUT NOCOPY VARCHAR2 );
363
364 ---------------------------------------------------------------
365 -- Procedure : Add_Message
366 -- This procedure serves as a wrapper to the FND_MEG_PUB
367 -- procedures to add the specified message onto the message
368 -- stack.
369 ---------------------------------------------------------------
370
371 Procedure Add_Message( p_app_short_name IN VARCHAR2,
372 p_msg_name IN VARCHAR2,
373 p_token1 IN VARCHAR2 DEFAULT NULL,
374 p_value1 IN VARCHAR2 DEFAULT NULL,
375 p_token2 IN VARCHAR2 DEFAULT NULL,
376 p_value2 IN VARCHAR2 DEFAULT NULL,
377 p_token3 IN VARCHAR2 DEFAULT NULL,
378 p_value3 IN VARCHAR2 DEFAULT NULL,
379 p_token4 IN VARCHAR2 DEFAULT NULL,
380 p_value4 IN VARCHAR2 DEFAULT NULL,
381 p_token5 IN VARCHAR2 DEFAULT NULL,
382 p_value5 IN VARCHAR2 DEFAULT NULL );
383
384 ---------------------------------------------------------------
385 -- Function : IsCrossBGProfile_WNPS
386 -- This procedure serves as a wrapper to the FND_PROFILE.VALUE_WNPS.
387 ---------------------------------------------------------------
388 FUNCTION IsCrossBGProfile_WNPS
389 RETURN VARCHAR2 ;
390 PRAGMA RESTRICT_REFERENCES ( IsCrossBGProfile_WNPS, WNDS, WNPS);
391
392 ---------------------------------------------------------------
393 -- Function : Conv_Special_JS_Chars
394 -- This function converts special characters in javascript link.
395 -- Currently, this function only handles apostrophe sign.
396 ---------------------------------------------------------------
397 FUNCTION Conv_Special_JS_Chars(p_string in varchar2) RETURN VARCHAR2;
398
399 ---------------------------------------------------------------
400 -- Function :Pa_Round_Currency
401 -- This function rounds the amount to the required precision based
402 -- on the currency code
403 ---------------------------------------------------------------
404 FUNCTION Pa_Round_Currency (P_Amount IN NUMBER, P_Currency_Code IN VARCHAR2)
405 RETURN NUMBER;
406
407 ---------------------------------------------------------------
408 FUNCTION get_party_id (
409 p_user_id in number ) return number;
410
411
412 PROCEDURE GetEmpOrgJobId ( X_person_id IN NUMBER
413 , X_date IN DATE
414 , X_Emp_Org_Id OUT NOCOPY NUMBER
415 , X_Emp_Job_Id OUT NOCOPY NUMBER
416 , X_po_number IN VARCHAR2 DEFAULT NULL -- Bug 4044057
417 , X_po_line_num IN NUMBER DEFAULT NULL); -- Bug 4044057
418
419 FUNCTION NewGetWeekEnding ( X_date IN DATE ) RETURN DATE;
420
421 /* Added for bug 5067511 */
422 FUNCTION GetPersonInfo( p_person_id IN per_all_people_f.person_id%TYPE,
423 p_data IN VARCHAR2 DEFAULT 'PERSON_ID') RETURN VARCHAR2;
424
425 G_PREV_TASK_ID NUMBER(15);
426 G_PREV_TASK_NUM VARCHAR2(25);
427 G_PREV_TASK_NAME VARCHAR2(20);
428 G_PREV_ORG_NAME VARCHAR2(240); -- fix for bug : 4598283
429 G_PREV_ORG_ID NUMBER(15);
430 G_PREV_DATE DATE;
431 G_PREV_WEEK_END DATE;
432 G_PREV_DATEIN NUMBER(15);
433 G_PREV_PROJ_ID NUMBER(15);
434 G_PREV_CHARGE NUMBER(15);
435 G_PREV_PROJ_NUM VARCHAR2(25);
436 G_PREV_PROJECT_ID NUMBER(15);
437 G_PREV_PROJ_ID2 NUMBER(15);
438 G_PREV_TASK_NUM2 VARCHAR2(25);
439 G_PREV_TASK_ID2 NUMBER(15);
440 G_PREV_DATE2 DATE;
441 G_PREV_WEEK_END2 DATE;
442 G_PREV_PERSON_ID NUMBER(15);
443 G_PREV_DATE3 DATE;
444 G_PREV_EMPJOB_ID NUMBER(15);
445 G_PREV_PERSON_ID2 NUMBER(15);
446 G_PREV_DATE4 DATE;
447 G_PREV_ORG_ID2 NUMBER(15);
448 G_EmpOrgId NUMBER;
449 G_EmpJobID NUMBER;
450 G_PersonIdPrev NUMBER;
451 G_DatePrev DATE;
452 G_PREV_SYS_LINK VARCHAR(30);
453 G_PREV_FUNCTION VARCHAR(30);
454 G_PREV_DATE5 DATE;
455 G_PREV_DAY_INDEX NUMBER;
456 G_PREV_END_DAY VARCHAR2(80);
457 G_PREV_WEEK_END3 DATE;
458
459
460
461 -- ==========================================================================
462 -- 10253400: Payroll integration enhancement for 12.2. 2 new functions added.
463 -- = FUNCTION GetJobId
464 -- ==========================================================================
465 /* Start changes for bug#12646514 */
466
467 FUNCTION GetJobId (X_Business_Group_Id IN NUMBER DEFAULT NULL
468 , X_job_id IN NUMBER DEFAULT NULL
469 , X_job_name IN VARCHAR2 DEFAULT NULL
470 , X_date IN DATE) RETURN NUMBER;
471
472 /* End changes for bug#12646514 */
473
474 FUNCTION GetLocationId ( p_location_id IN NUMBER DEFAULT NULL
475 , p_location_code IN VARCHAR2 DEFAULT NULL
476 , X_Date IN DATE)
477 return number;
478 FUNCTION GetAsgnLocationId(p_person_id IN NUMBER
479 ,p_ei_date IN DATE) RETURN NUMBER;
480
481 PROCEDURE get_rate_source_cost_method(p_exp_item_id IN NUMBER,
482 p_exp_item_date IN DATE,
483 x_rate_source_code OUT NOCOPY VARCHAR2,
484 x_costing_method OUT NOCOPY VARCHAR2,
485 x_enable_accrual OUT NOCOPY VARCHAR2);
486
487
488 ---- Added procedure for payroll billable_flag changes for 12.2 start bug#12360223
489
490 procedure get_rate_source_cost_method1(p_incurred_by_person_id IN NUMBER,
491 p_exp_item_date IN DATE,
492 p_incurred_by_organization_id IN NUMBER,
493 p_org_id IN NUMBER ,
494 x_rate_source_code OUT NOCOPY VARCHAR2,
495 x_costing_method OUT NOCOPY VARCHAR2,
496 x_enable_accrual OUT NOCOPY VARCHAR2);
497
498 ---- Added for payroll billable_flag changes for 12.2 end bug#12360223
499
500 procedure check_cm_on_related_items(p_exp_item_id NUMBER,
501 p_xfer_item_id NUMBER,
502 x_costing_method OUT NOCOPY VARCHAR2);
503 /* Added Bug 14851933 */
504 ----------------------------------------------------------------------------------------------------
505 -- Function :INITIALIZE
506 -- This function initialize the global variables which are present in procedure pa_security.initialize
507 ------------------------------------------------------------------------------------------------------
508
509 FUNCTION INITIALIZE RETURN VARCHAR2;
510
511 END PA_UTILS;
[Home] [Help]