1 PACKAGE PA_UTILS2 AUTHID CURRENT_USER AS
2 /* $Header: PAXGUT2S.pls 120.7 2012/03/05 11:05:42 arbandyo ship $*/
3
4 /* For Archive purge MRC, these are global variable */
5
6 ARPUR_Commit_Size NUMBER := 0;
7 ARPUR_MRC_Commit_Size NUMBER := 0;
8 MRC_ROW_COUNT NUMBER := 0;
9
10
11 /* CBGA
12 * The following variable TempTab is defined - to be used as a default value
13 * for the table parameter expnd_id.
14 */
15 TempIDTab PA_PLSQL_DATATYPES.IDTabTyp ;
16 TempRowIDTab PA_PLSQL_DATATYPES.Char30TabTyp ;
17 /**CBGA**/
18
19 /**Global variables related to pa_date and recvr_pa_date caching **/
20 /*
21 * EPP.
22 * Modified variable names.
23 */
24 /*
25 * Provider.
26 */
27 g_prvdr_org_id pa_cost_distribution_lines_all.org_id%TYPE;
28 g_p_earliest_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE;
29 g_p_earliest_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE;
30 g_p_earliest_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
31
35 g_prvdr_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
32 g_prvdr_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE;
33 g_prvdr_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE;
34 g_prvdr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE;
36
37 /*
38 * Receiver.
39 */
40 g_recvr_org_id pa_cost_distribution_lines_all.org_id%TYPE;
41 g_r_earliest_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE;
42 g_r_earliest_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE;
43 g_r_earliest_pa_period_name pa_cost_distribution_lines_all.pa_period_name%TYPE;
44
45 g_recvr_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE;
46 g_recvr_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE;
47 g_recvr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE;
48 g_recvr_pa_period_name pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE;
49
50 G_Business_Group_Id NUMBER;
51 G_Return_Status VARCHAR2(30);
52 G_Application_Id NUMBER; /*Added for bug 7638790 */
53
54 /*
55 * Provider.
56 */
57 g_p_earliest_gl_start_date pa_cost_distribution_lines_all.gl_date%TYPE;
58 g_p_earliest_gl_end_date pa_cost_distribution_lines_all.gl_date%TYPE;
59 g_p_earliest_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
60
61 g_prvdr_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
62 g_prvdr_gl_start_date pa_cost_distribution_lines_all.gl_date%TYPE;
63 g_prvdr_gl_end_date pa_cost_distribution_lines_all.gl_date%TYPE;
64 g_prvdr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE ;
65 g_prvdr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE;
66
67 /*
68 * Receiver.
69 */
70 g_r_earliest_gl_start_date pa_cost_distribution_lines_all.gl_date%TYPE;
71 g_r_earliest_gl_end_date pa_cost_distribution_lines_all.gl_date%TYPE;
72 g_r_earliest_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
73
74 g_recvr_set_of_books_id pa_implementations_all.set_of_books_id%TYPE;
75 g_recvr_gl_start_date pa_cost_distribution_lines_all.gl_date%TYPE;
76 g_recvr_gl_end_date pa_cost_distribution_lines_all.gl_date%TYPE;
77 g_recvr_gl_period_name pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE ;
78 g_recvr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE;
79
80 /*
81 * EPP.
82 */
83 g_prev_expenditure_id pa_expenditure_items_all.expenditure_id%TYPE;
84 g_prev_prvdr_gl_date pa_cost_distribution_lines_all.gl_date%TYPE;
85 g_prev_prvdr_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
86 g_prev_recvr_gl_period_name pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE;
87 g_prev_recvr_gl_date pa_cost_distribution_lines_all.recvr_gl_date%TYPE;
88
89 /*
90 * These global variables are used by function get_gl_period_name.
91 */
92 g_org_id pa_cost_distribution_lines_all.org_id%TYPE ;
93 g_gl_period_start_date pa_cost_distribution_lines_all.gl_date%TYPE ;
94 g_gl_period_end_date pa_cost_distribution_lines_all.gl_date%TYPE ;
95 g_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE ;
96
97 /* Period End Accruals */
98 g_p_org_accr_start_date pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
99 g_p_org_accr_end_date pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
100 g_p_rev_accr_nxt_st_dt pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
101 g_p_rev_accr_nxt_end_dt pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
102 g_p_rev_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
103
104 g_r_org_accr_start_date pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
105 g_r_org_accr_end_date pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
106 g_r_rev_accr_nxt_st_dt pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
107 g_r_rev_accr_nxt_end_dt pa_expenditure_items_all.prvdr_accrual_date%TYPE := NULL;
108 g_r_rev_gl_period_name pa_cost_distribution_lines_all.gl_period_name%TYPE;
109
110 g_p_accr_gl_per_st_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
111 g_p_accr_gl_per_end_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
112 g_p_accr_gl_per_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
113
114 g_r_accr_gl_per_st_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
115 g_r_accr_gl_per_end_dt pa_cost_distribution_lines_all.gl_date%TYPE := NULL;
116 g_r_accr_gl_per_name pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
117
118 g_prv_accr_prvdr_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
119 g_prv_accr_prvdr_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
120 g_prv_accr_prvdr_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
121 g_prv_accr_recvr_pa_start_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
122 g_prv_accr_recvr_pa_end_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
123 g_p_accr_rev_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
124 g_r_accr_rev_pa_date pa_cost_distribution_lines_all.pa_date%TYPE := NULL;
125 g_prv_accr_prvdr_pa_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
126 g_prv_accr_recvr_pa_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
130 g_p_accr_rev_pa_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
127 g_prv_accr_prvdr_gl_period pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
128 g_prv_accr_recvr_gl_period pa_cost_distribution_lines_all.gl_period_name%TYPE := NULL;
129
131 g_r_accr_rev_pa_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
132 g_p_gl_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
133 g_r_gl_period pa_cost_distribution_lines_all.pa_period_name%TYPE := NULL;
134 g_prof_new_gldate_derivation VARCHAR2(1) := NULL; --EPP global variable
135
136
137 /* added the following two global variables for BUG# 3384892 */
138
139 g_profile_cache_first_time varchar2(2) :='Y';
140 g_profile_value varchar2(2);
141
142
143 /* Bug 5374282 */
144 g_gl_dt_period_str_dt pa_draft_revenues_all.gl_date%TYPE := NULL;
145 g_gl_dt_period_end_dt pa_draft_revenues_all.gl_date%TYPE := NULL;
146 g_gl_dt_period_name pa_draft_revenues_all.gl_period_name%TYPE := NULL;
147
148 PROCEDURE get_gl_dt_period (p_reference_date IN DATE,
149 x_gl_period_name OUT NOCOPY pa_draft_revenues_all.gl_period_name%TYPE,
150 x_gl_dt OUT NOCOPY pa_draft_revenues_all.gl_date%TYPE,
151 x_return_status OUT NOCOPY NUMBER,
152 x_error_code OUT NOCOPY VARCHAR2,
153 x_error_stage OUT NOCOPY VARCHAR2
154 );
155 /* 5374282 ends */
156
157
158 ---------------------------------------------------------------
159 -- This function returns 'Y' if a given org is a Exp organization ,
160 -- otherwise , it returns 'N'
161 ---------------------------------------------------------------
162 FUNCTION get_period_name return pa_cost_distribution_lines_all.pa_period_name%TYPE;/*2835063*/
163
164 /* added the following two functions for bug 3384892 for caching a the profile value.*/
165
166 FUNCTION pa_date_profile(exp_item_date IN DATE, accounting_date IN DATE, org_id IN NUMBER)
167 RETURN DATE;
168
169 FUNCTION pa_period_name_profile RETURN VARCHAR2;
170
171 FUNCTION CheckExpOrg (x_org_id IN NUMBER,
172 x_txn_date in date default trunc(sysdate)) RETURN VARCHAR2 ;
173 --pragma RESTRICT_REFERENCES ( CheckExpOrg, WNDS, WNPS);
174
175 FUNCTION CheckSysLinkFuncActive(x_exp_type IN VARCHAR2,
176 x_ei_date IN DATE,
177 x_sys_link_func IN VARCHAR2) RETURN BOOLEAN ;
178 -- pa_lookups purity level has been changed to WNDS only, so modifying
179 -- functions that use pa_lookups to purity level WNDS, however in 8i
180 -- there is no need to explicitly define purity level, so removing the
181 -- purity level all togeather.
182 -- PRAGMA RESTRICT_REFERENCES (CheckSysLinkFuncActive, WNDS, WNPS);
183
184 FUNCTION CheckAdjFlag (x_exp_item_id In Number) RETURN VARCHAR2;
185 PRAGMA RESTRICT_REFERENCES (CheckAdjFlag, WNDS, WNPS) ;
186
187
188 ---------------------------------------------------------------
189 --=================================================================================
190 -- These are the new procedures and functions added for Archive / Purge
191 --=================================================================================
192
193 FUNCTION IsProjectClosed ( X_project_system_status_code IN VARCHAR2 ) RETURN VARCHAR2 ;
194 PRAGMA RESTRICT_REFERENCES ( IsProjectClosed, WNDS, WNPS ) ;
195
196 FUNCTION IsProjectInPurgeStatus ( X_project_system_status_code IN VARCHAR2 ) RETURN VARCHAR2 ;
197 PRAGMA RESTRICT_REFERENCES ( IsProjectInPurgeStatus, WNDS, WNPS ) ;
198
199 FUNCTION IsDestPurged ( X_exp_id IN NUMBER ) RETURN VARCHAR2 ;
200 PRAGMA RESTRICT_REFERENCES ( IsDestPurged, WNDS, WNPS ) ;
201
202 FUNCTION IsSourcePurged ( X_exp_id IN NUMBER ) RETURN VARCHAR2 ;
203 PRAGMA RESTRICT_REFERENCES ( IsSourcePurged, WNDS, WNPS ) ;
204
205 PROCEDURE IsActivePrjTxnsPurged(p_project_id IN NUMBER,
206 x_message_code IN OUT NOCOPY VARCHAR2,
207 x_token IN OUT NOCOPY DATE) ;
208
209 function IsProjectTxnsPurged(p_project_id IN NUMBER) RETURN BOOLEAN ;
210
211 function IsProjectCapitalPurged(p_project_id IN NUMBER) RETURN BOOLEAN ;
212
213 function IsProjectBudgetsPurged(p_project_id IN NUMBER) RETURN BOOLEAN ;
214
215 function IsProjectSummaryPurged(p_project_id IN NUMBER) RETURN BOOLEAN ;
216
217 FUNCTION GetProductRelease RETURN VARCHAR2 ;
221 function GetLaborCostMultiplier (x_task_id In Number) RETURN VARCHAR2;
218 PRAGMA RESTRICT_REFERENCES ( GetProductRelease, WNDS, WNPS ) ;
219
220 ---------------------------------------------------------------
222 -- PRAGMA RESTRICT_REFERENCES (GetLaborCostMultiplier, WNDS, WNPS) ;
223
224 --=================================================================================
225
226 FUNCTION GetPrjOrgId(p_project_id NUMBER,
227 p_task_id NUMBER ) RETURN NUMBER ;
228 --PRAGMA RESTRICT_REFERENCES (GetPrjOrgId, WNDS, WNPS) ;
229 ---------------------------------------------------------------
230
231 --function : get_pa_date
232 -- Derive PA Date from GL date and ei date .
233 -- This function accepts the expenditure item date ,GL date and org id
234 -- and derives the PA date based on this. The function has been modified
235 -- to not use the gl_Date (though it is still accepted as a parameter
236 -- just in case the logic changes in the future to use the gl_Date).
237 -- This is mainly used for AP invoices and transactions imported from
238 -- other systems where the GL date is known in advance and the PA date
239 -- has to be determined.
240 -- This function is also modified to use caching.
241 ------------------------------------------------------------------------
242 FUNCTION get_pa_date( p_ei_date IN date, p_gl_date IN date, p_org_id IN number) return date ;
243 PRAGMA RESTRICT_REFERENCES ( get_pa_date, WNDS ) ; /**CBGA - removed WNPS**/
244
245 ----------------------------------------------------------------------
246
247 --function : get_recvr_pa_date
248 -- Introduced for CBGA changes.
249 -- Derive PA Date for recvr Org.
250 -- This function accepts the expenditure item date,the GL date and org_id and
251 -- derives the PA date based on this. The function has been modified
252 -- to not use the gl_Date (though it is still accepted as a parameter
253 -- just in case the logic changes in the future to use the gl_Date).
254 -- This is mainly used for AP invoices and transactions imported from
255 -- other systems where the GL date is known in advance and the PA date
256 -- has to be determined.
257 ------------------------------------------------------------------------
258 FUNCTION get_recvr_pa_date(p_ei_date IN date, p_gl_date IN date, p_org_id IN number) return date;
259 PRAGMA RESTRICT_REFERENCES ( get_recvr_pa_date, WNDS ) ;
260
261
262 ---------------------------------------------------------------
263 -- Procedure : refresh_pa_cache
264 -- This procedure is used for DB access and is called by get_pa_date and get_recvr_pa_date.
265 ---------------------------------------------------------------
266
267 PROCEDURE refresh_pa_cache ( p_org_id IN number ,
268 p_ei_date IN date ,
269 p_caller_flag IN varchar2
270 );
271 PRAGMA RESTRICT_REFERENCES ( refresh_pa_cache, WNDS ) ;
272
273 ---------------------------------------------------------------
274 -- Procedure : refresh_gl_cache.
275 -- This procedure is called by both get_prvdr_gl_date and get_recvr_gl_date.
276 ---------------------------------------------------------------
277
278 /*
279 * EPP.
280 * Modified the name of the first parameter.
281 */
282 PROCEDURE refresh_gl_cache ( p_reference_date IN DATE,
283 p_application_id IN NUMBER,
284 p_set_of_books_id IN NUMBER,
285 p_caller_flag IN VARCHAR2
286 );
287 ---------------------------------------------------------------
288
289 ---------------------------------------------------------------
290 -- Procedure : populate_gl_dates
291 -- This procedure is called by interface programs.
292 ---------------------------------------------------------------
293
294 PROCEDURE populate_gl_dates( p_local_set_size IN NUMBER,
295 p_application_id IN PA_PLSQL_DATATYPES.IDTabTyp,
296 p_request_id IN PA_PLSQL_DATATYPES.IDTabTyp,
297 p_cdl_rowid IN PA_PLSQL_DATATYPES.Char30TabTyp DEFAULT TempRowIDTab,
298 p_prvdr_sob_id IN PA_PLSQL_DATATYPES.IDTabTyp,
299 p_recvr_sob_id IN PA_PLSQL_DATATYPES.IDTabTyp,
300 p_expnd_id IN PA_PLSQL_DATATYPES.IDTabTyp DEFAULT TempIDTab,
301 p_sys_linkage_type IN VARCHAR2 DEFAULT NULL
302 );
303
304 ---------------------------------------------------------------
305 -- Function : get_prvdr_gl_date
306 -- This function is called by populate_gl_dates.
307 ---------------------------------------------------------------
308 /*
309 * EPP.
310 * changed the name of the first parameter.
311 */
312 FUNCTION get_prvdr_gl_date( p_reference_date IN DATE,
313 p_application_id IN NUMBER ,
314 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE
315 ) return date ;
316
317
318 ---------------------------------------------------------------
319 -- Function : get_recvr_gl_date
320 -- This function is called populate_gl_dates.
321 ---------------------------------------------------------------
322 /*
323 * EPP.
324 * changed the name of the first parameter.
325 */
326 FUNCTION get_recvr_gl_date( p_reference_date IN DATE,
327 p_application_id IN NUMBER ,
328 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE
329 ) return DATE ;
330 ---------------------------------------------------------------
331
335 -- cwk changes: added parameter P_Person_Type
332 -- Fixed Bug 1534973, 1581184
333 -- Added P_EiDate parameter and performing date check in the query
334 -- If adding new parameters please add before P_EiDate parameter
336 PROCEDURE GetEmpId ( P_Business_Group_Id IN NUMBER
337 , P_Employee_Number IN VARCHAR2
338 , X_Employee_Id OUT NOCOPY VARCHAR2
339 , P_Person_Type IN VARCHAR2 default null
340 , P_EiDate IN DATE default SYSDATE);
341
342 FUNCTION GetBusinessGroupId ( P_Business_Group_Name IN VARCHAR2 ) RETURN NUMBER;
343 -- pragma RESTRICT_REFERENCES ( GetBusinessGroupId, WNDS, WNPS );
344
345 ---------------------------------------------------------------
346 -- Function : GetGlPeriodName
347 -- This function is called by Transaction Import to get the gl period name.
348 ---------------------------------------------------------------
349 PROCEDURE GetGlPeriodNameDate( p_pa_date IN DATE,
350 p_application_id IN NUMBER ,
351 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
352 x_gl_date OUT NOCOPY DATE,
353 x_period_name OUT NOCOPY VARCHAR2
354 ) ;
355
356 ---------------------------------------------------------------
357 -- Procedure : get_period_information
358 ---------------------------------------------------------------
359 PROCEDURE get_period_information ( p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
360 ,p_expenditure_id IN pa_expenditure_items_all.expenditure_id%TYPE := NULL
361 ,p_system_linkage_function IN pa_expenditure_items_all.system_linkage_function%TYPE := NULL
362 ,p_line_type IN pa_cost_distribution_lines_all.line_type%TYPE := NULL
363 ,p_prvdr_raw_pa_date IN pa_cost_distribution_lines_all.pa_date%TYPE := NULL
364 ,p_recvr_raw_pa_date IN pa_cost_distribution_lines_all.pa_date%TYPE := NULL
365 ,p_prvdr_raw_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE := NULL
366 ,p_recvr_raw_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE := NULL
367 ,p_prvdr_org_id IN pa_expenditure_items_all.org_id%TYPE
368 ,p_recvr_org_id IN pa_expenditure_items_all.org_id%TYPE := NULL
369 ,p_prvdr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
370 ,p_recvr_sob_id IN pa_implementations_all.set_of_books_id%TYPE := NULL
371 ,p_calling_module IN VARCHAR2 := NULL
372 ,p_ou_context IN VARCHAR2 DEFAULT 'N'
373 ,x_prvdr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
374 ,x_prvdr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
378 ,x_recvr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE
375 ,x_prvdr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
376 ,x_prvdr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
377 ,x_recvr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_date%TYPE
379 ,x_recvr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_date%TYPE
380 ,x_recvr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE
381 ,x_return_status OUT NOCOPY NUMBER
382 ,x_error_code OUT NOCOPY VARCHAR2
383 ,x_error_stage OUT NOCOPY NUMBER
384 );
385 ---------------------------------------------------------------
386 PROCEDURE get_OU_period_information ( p_reference_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
387 ,p_calling_module IN VARCHAR2 := NULL
388 ,x_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
389 ,x_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
390 ,x_gl_date OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
391 ,x_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
392 ,x_return_status OUT NOCOPY NUMBER
393 ,x_error_code OUT NOCOPY VARCHAR2
394 ,x_error_stage OUT NOCOPY NUMBER
395 );
396
397 ---------------------------------------------------------------
398 FUNCTION get_gl_period_name ( p_gl_date IN pa_cost_distribution_lines_all.gl_date%TYPE
399 ,p_org_id IN pa_cost_distribution_lines_all.org_id%TYPE
400 )
401 RETURN pa_cost_distribution_lines_all.gl_period_name%TYPE;
402 ---------------------------------------------------------------
403 FUNCTION get_set_of_books_id (p_org_id IN pa_implementations_all.org_id%TYPE) RETURN NUMBER;
404 ---------------------------------------------------------------
405 /*
406 * This function returns the end_date of the PA period in which the input date falls.
407 */
408 FUNCTION get_pa_period_end_date_OU ( p_date IN pa_periods_all.end_date%TYPE ) RETURN pa_periods_all.end_date%TYPE;
409 ------------------------------------------------------------------------------------------------------------------
410 -- Procedure : get_accrual_gl_dt_period
411 ------------------------------------------------------------------------------------------------------------------
412 PROCEDURE get_accrual_gl_dt_period(p_calling_module IN VARCHAR2,
413 p_reference_date IN DATE,
414 p_application_id IN NUMBER ,
415 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
416 p_prvdr_recvr_flg IN VARCHAR2,
417 p_epp_flag IN VARCHAR2,
418 x_gl_accr_period_name OUT NOCOPY VARCHAR2,
419 x_gl_accr_dt OUT NOCOPY DATE,
420 x_return_status OUT NOCOPY VARCHAR2,
421 x_error_code OUT NOCOPY VARCHAR2,
422 x_error_stage OUT NOCOPY VARCHAR2
423 );
424 ------------------------------------------------------------------------------------------------------------------
425 -- Procedure : get_accrual_period_information
426 ------------------------------------------------------------------------------------------------------------------
427 PROCEDURE get_accrual_period_information(p_expenditure_item_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
428 ,x_prvdr_accrual_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
429 ,x_recvr_accrual_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
430 ,p_prvdr_org_id IN pa_expenditure_items_all.org_id%TYPE
431 ,p_recvr_org_id IN pa_expenditure_items_all.org_id%TYPE
432 ,p_prvdr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
433 ,p_recvr_sob_id IN pa_implementations_all.set_of_books_id%TYPE
434 ,p_calling_module IN VARCHAR2
435 ,x_prvdr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.pa_date%TYPE
436 ,x_prvdr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.pa_period_name%TYPE
437 ,x_prvdr_gl_date IN OUT NOCOPY pa_cost_distribution_lines_all.gl_date%TYPE
438 ,x_prvdr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.gl_period_name%TYPE
439 ,x_recvr_pa_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_date%TYPE
440 ,x_recvr_pa_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_pa_period_name%TYPE
441 ,x_recvr_gl_date OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_date%TYPE
442 ,x_recvr_gl_period_name OUT NOCOPY pa_cost_distribution_lines_all.recvr_gl_period_name%TYPE
443 ,p_adj_ei_id IN pa_expenditure_items_all.expenditure_item_id%type
444 ,p_acct_flag IN VARCHAR2
445 ,x_return_status OUT NOCOPY VARCHAR2
446 ,x_error_code OUT NOCOPY VARCHAR2
447 ,x_error_stage OUT NOCOPY VARCHAR2
448 );
449 ---------------------------------------------------------------------+
450 PROCEDURE get_accrual_pa_dt_period( p_gl_period IN VARCHAR2
451 ,p_ei_date IN DATE
452 ,p_org_id IN pa_expenditure_items_all.org_id%TYPE
453 ,p_prvdr_recvr_flg IN VARCHAR2
454 ,p_epp_flag IN VARCHAR2
455 ,p_org_rev_flg IN VARCHAR2
456 ,x_pa_date OUT NOCOPY DATE
457 ,x_pa_period_name OUT NOCOPY VARCHAR2
458 ,x_return_status OUT NOCOPY VARCHAR2
459 ,x_error_code OUT NOCOPY VARCHAR2
460 );
461 ---------------------------------------------------------------------+
462 FUNCTION get_rev_accrual_date( p_calling_module IN VARCHAR2,
463 p_reference_date IN DATE,
464 p_application_id IN NUMBER ,
465 p_set_of_books_id IN gl_sets_of_books.set_of_books_id%TYPE,
466 p_prvdr_recvr_flg IN VARCHAR2,
467 p_epp_flag IN VARCHAR2,
468 x_gl_period_name OUT NOCOPY VARCHAR2,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_error_code OUT NOCOPY VARCHAR2,
471 x_error_stage OUT NOCOPY VARCHAR2
472 )
473 RETURN DATE;
474 -----------------------------------------------------------------------
475 FUNCTION get_pa_period_name( p_txn_date IN pa_expenditure_items_all.expenditure_item_date%TYPE
476 ,p_org_id IN pa_implementations_all.org_id%TYPE
477 )
478 RETURN pa_periods.period_name%TYPE;
479 -----------------------------------------------------------------------
480 --Start Bug 3069632
481 FUNCTION get_ts_allow_burden_flag( p_transaction_source IN pa_expenditure_items_all.transaction_source%TYPE)
482 RETURN pa_transaction_sources.allow_burden_flag%TYPE;
483 -----------------------------------------------------------------------
484 FUNCTION get_capital_cost_type_code( p_project_id IN pa_projects_all.project_id%TYPE)
485 RETURN pa_project_types_all.CAPITAL_COST_TYPE_CODE%TYPE;
486
487 G_CapCostTypProjID_Tab PA_PLSQL_DATATYPES.Char1TabTyp;
488 -----------------------------------------------------------------------
489 g_project_id NUMBER;
490 g_project_type pa_projects_all.project_type%type;
491 g_proj_org_id pa_projects_all.org_id%type;
492 g_capital_cost_type_code pa_project_types_all.CAPITAL_COST_TYPE_CODE%TYPE;
493 g_ts_allow_burden_flag pa_transaction_sources.allow_burden_flag%TYPE;
494 g_transaction_source pa_expenditure_items_all.transaction_source%TYPE;
495 --End Bug 3069632
496
497 Function Get_Burden_Amt_Display_Method(P_Project_Id in Number) Return Varchar2;
498
499 G_BdMethodProjID_Tab PA_PLSQL_DATATYPES.Char1TabTyp;
500
501 /*S.N. Bug4746949 */
502 Function Proj_Type_Burden_Disp_Method(P_Project_Id in Number) Return Varchar2;
503 G_Bd_MethodProjID_Tab PA_PLSQL_DATATYPES.Char1TabTyp;
504 /*E.N. Bug4746949 */
505
506 G_PREV_ORG_ID NUMBER(15);
507 G_PREV_TXN_DATE DATE;
508 G_PREV_EXP_ORG VARCHAR2(1);
509 G_PREV_BUSGRP_ID NUMBER(15);
510 G_PREV_EMP_NUM VARCHAR2(30);
511 G_PREV_EI_DATE DATE;
512 G_PREV_EMP_ID NUMBER(15);
513 G_PREV_TASK_ID NUMBER(15);
514 G_PREV_LCM_NAME VARCHAR2(20);
515 G_PREV_PROJ_ID NUMBER(15);
516 G_PREV_TASK_ID2 NUMBER(15);
517 G_PREV_ORG_ID2 NUMBER(15);
518 G_PREV_BUS_GRP_NAME VARCHAR2(60);
519 G_PREV_BUS_GRP_ID NUMBER (15);
520 G_PREV_PERSON_TYPE VARCHAR2(30);
521
522 FUNCTION IsEnhancedBurdeningEnabled RETURN VARCHAR2;
523 PROCEDURE populate_person_type (p_date IN DATE DEFAULT NULL,
524 p_upd_type IN VARCHAR2 DEFAULT NULL,
525 p_request_id IN NUMBER); /* Added for bug 13554435 */
526
527 END PA_UTILS2;