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