[Home] [Help]
PACKAGE: APPS.PA_REP_UTIL_GLOB
Source
1 PACKAGE PA_REP_UTIL_GLOB AUTHID CURRENT_USER AS
2 /* $Header: PARRGLBS.pls 120.1 2005/08/19 17:00:22 mwasowic noship $ */
3
4 /*
5 * Constants.
6 */
7 G_DEFAULT_FETCH_SIZE_C CONSTANT PLS_INTEGER := 200;
8 G_UNIT_OF_MEASURE_HRS_C CONSTANT VARCHAR2(10) := 'HOURS'; -- datatype length ok??
9 G_DUMMY_C CONSTANT VARCHAR2(15) := 'SUMM_DUMMY'; -- datatype length ok??
10 G_DUMMY_DATE_C CONSTANT DATE := to_date('01/01/1420', 'DD/MM/YYYY');
11
12 /*
13 * The following is a list of constants defined for each
14 * of the amount type codes.
15 */
16
17 G_RES_HRS_C CONSTANT VARCHAR2(30) := 'RES_HRS' ;
18 G_RES_WTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'RES_WTDHRS_ORG';
19 G_RES_WTDHRS_PEOPLE_C CONSTANT VARCHAR2(30) := 'RES_WTDHRS_PEOPLE';
20 G_RES_PRVHRS_C CONSTANT VARCHAR2(30) := 'RES_PRVHRS';
21 G_RES_PRVWTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'RES_PRVWTDHRS_ORG';
22 G_RES_PRVWTDHRS_PEOPLE_C CONSTANT VARCHAR2(30) := 'RES_PRVWTDHRS_PEOPLE';
23 G_RES_UTILPRCTGHRS_C CONSTANT VARCHAR2(30) := 'RES_UTILPRCTGHRS';
24 G_RES_UTILPRCTGCAP_C CONSTANT VARCHAR2(30) := 'RES_UTILPRCTGCAP';
25 G_RES_CAP_C CONSTANT VARCHAR2(30) := 'RES_CAP';
26 G_RES_REDUCEDCAP_C CONSTANT VARCHAR2(30) := 'RES_REDUCEDCAP';
27 G_ORG_SUB_HRS_C CONSTANT VARCHAR2(30) := 'ORG_SUB_HRS';
28 G_ORG_SUB_WTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_SUB_WTDHRS_ORG';
29 G_ORG_SUB_PRVHRS_C CONSTANT VARCHAR2(30) := 'ORG_SUB_PRVHRS';
30 G_ORG_SUB_PRVWTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_SUB_PRVWTDHRS_ORG';
31 G_ORG_SUB_UTILPRCTGHRS_C CONSTANT VARCHAR2(30) := 'ORG_SUB_UTILPRCTGHRS';
32 G_ORG_SUB_UTILPRCTGCAP_C CONSTANT VARCHAR2(30) := 'ORG_SUB_UTILPRCTGCAP';
33 G_ORG_SUB_CAP_C CONSTANT VARCHAR2(30) := 'ORG_SUB_CAP';
34 G_ORG_SUB_REDUCEDCAP_C CONSTANT VARCHAR2(30) := 'ORG_SUB_REDUCEDCAP';
35 G_ORG_SUB_HEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_SUB_HEADCOUNT';
36 G_ORG_SUB_EMPHEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_SUB_EMPHEADCOUNT';
37 G_ORG_DIR_HRS_C CONSTANT VARCHAR2(30) := 'ORG_DIR_HRS';
38 G_ORG_DIR_WTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_DIR_WTDHRS_ORG';
39 G_ORG_DIR_PRVHRS_C CONSTANT VARCHAR2(30) := 'ORG_DIR_PRVHRS';
40 G_ORG_DIR_PRVWTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_DIR_PRVWTDHRS_ORG';
41 G_ORG_DIR_UTILPRCTGHRS_C CONSTANT VARCHAR2(30) := 'ORG_DIR_UTILPRCTGHRS';
42 G_ORG_DIR_UTILPRCTGCAP_C CONSTANT VARCHAR2(30) := 'ORG_DIR_UTILPRCTGCAP';
43 G_ORG_DIR_CAP_C CONSTANT VARCHAR2(30) := 'ORG_DIR_CAP';
44 G_ORG_DIR_REDUCEDCAP_C CONSTANT VARCHAR2(30) := 'ORG_DIR_REDUCEDCAP';
45 G_ORG_DIR_HEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_DIR_HEADCOUNT';
46 G_ORG_DIR_EMPHEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_DIR_EMPHEADCOUNT';
47 G_ORG_TOT_HRS_C CONSTANT VARCHAR2(30) := 'ORG_TOT_HRS';
48 G_ORG_TOT_WTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_TOT_WTDHRS_ORG';
49 G_ORG_TOT_PRVHRS_C CONSTANT VARCHAR2(30) := 'ORG_TOT_PRVHRS';
50 G_ORG_TOT_PRVWTDHRS_ORG_C CONSTANT VARCHAR2(30) := 'ORG_TOT_PRVWTDHRS_ORG';
51 G_ORG_TOT_UTILPRCTGHRS_C CONSTANT VARCHAR2(30) := 'ORG_TOT_UTILPRCTGHRS';
52 G_ORG_TOT_UTILPRCTGCAP_C CONSTANT VARCHAR2(30) := 'ORG_TOT_UTILPRCTGCAP';
53 G_ORG_TOT_CAP_C CONSTANT VARCHAR2(30) := 'ORG_TOT_CAP';
54 G_ORG_TOT_REDUCEDCAP_C CONSTANT VARCHAR2(30) := 'ORG_TOT_REDUCEDCAP';
55 G_ORG_TOT_HEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_TOT_HEADCOUNT';
56 G_ORG_TOT_EMPHEADCOUNT_C CONSTANT VARCHAR2(30) := 'ORG_TOT_EMPHEADCOUNT';
57 /*
58 * The variables in the following Record are
59 * to be used as constants.
60 */
61 TYPE G_BAL_TYPE_REC_C
62 IS RECORD (
63 G_ACTUALS_C VARCHAR2(15)
64 ,G_FORECAST_C VARCHAR2(15)
65 );
66
67 TYPE G_OBJ_TYPE_REC_C
68 IS RECORD (
69 G_ORG_C VARCHAR2(15)
70 ,G_ORGUC_C VARCHAR2(15)
71 ,G_ORGWT_C VARCHAR2(15)
72 ,G_RES_C VARCHAR2(15)
73 ,G_RESUCO_C VARCHAR2(15)
74 ,G_RESUCR_C VARCHAR2(15)
75 ,G_RESWT_C VARCHAR2(15)
76 ,G_UTILDET_C VARCHAR2(15)
77 );
78
79 TYPE G_PERIOD_TYPE_REC_C
80 IS RECORD (
81 G_GL_C VARCHAR2(3)
82 ,G_PA_C VARCHAR2(3)
83 ,G_GE_C VARCHAR2(3)
84 );
85 /*
86 * Definitions for Global Variables.
87 */
88 G_global_week_start_day PLS_INTEGER;
89 G_util_fetch_size PLS_INTEGER DEFAULT G_DEFAULT_FETCH_SIZE_C;
90 G_u1_show_prctg_by VARCHAR2(30);
91
95 /*
92 G_period_type_qtr_c VARCHAR2(3) := 'QR';
93 G_period_type_year_c VARCHAR2(3) := 'YR';
94
96 ** Cacheing the organization_id for U1 screen
97 */
98 G_Organization_ID NUMBER;
99 G_Period_Type VARCHAR2(30);
100 G_Period_Name VARCHAR2(30);
101 G_Period_Year VARCHAR2(30);
102 G_Global_Exp_Period_End_Date DATE;
103 G_Period_Quarter VARCHAR2(30);
104
105
106
107 /*
108 * Define the variable which will ensure that the call to Capacity vector
109 * is made only once. The variable is defaulted to 'Y' and as soon as the first
110 * call to the Capacity Vector is made it is set to 'N'.
111 */
112 G_is_this_first_fetch VARCHAR2(1) := 'Y';
113
114 G_eff_ac_start_pa_period_num PLS_INTEGER;
115 G_eff_ac_start_gl_period_num PLS_INTEGER;
116 G_eff_fc_start_pa_period_num PLS_INTEGER;
117 G_eff_fc_start_gl_period_num PLS_INTEGER;
118
119 /*
120 * Definitions for Global Records.
121 */
122
123 TYPE G_input_parameters_rec
124 IS RECORD (
125 G_ac_start_date DATE
126 ,G_ac_end_date DATE
127 ,G_fc_start_date DATE
128 ,G_fc_end_date DATE
129 ,G_org_rollup_method VARCHAR2(1) DEFAULT 'I'
130 ,G_debug_mode VARCHAR2(2)
131 );
132
133 TYPE G_who_columns_rec
134 IS RECORD (
135 G_last_updated_by NUMBER(15)
136 ,G_created_by NUMBER(15)
137 ,G_creation_date DATE
138 ,G_last_update_date DATE
139 ,G_last_update_login NUMBER(15)
140 ,G_program_application_id NUMBER(15)
141 ,G_request_id NUMBER(15)
142 ,G_program_id NUMBER(15)
143 );
144
145 TYPE G_implementation_details_rec
146 IS RECORD (
147 G_org_id pa_implementations.org_id%TYPE
148 ,G_org_structure_version_id pa_implementations.org_structure_version_id%TYPE
149 ,G_start_organization_id pa_implementations.start_organization_id%TYPE
150 ,G_pa_period_type pa_implementations.pa_period_type%TYPE
151 ,G_gl_period_type gl_sets_of_books.accounted_period_type%TYPE
152 -- ,G_period_set_name gl_sets_of_books.period_set_name%TYPE
153 ,G_gl_period_set_name gl_sets_of_books.period_set_name%TYPE --bug 3434019
154 ,G_pa_period_set_name gl_sets_of_books.period_set_name%TYPE --bug 3434019
155 );
156
157 TYPE G_util_option_details_rec
158 IS RECORD (
159 G_gl_period_flag pa_utilization_options.gl_period_flag%TYPE
160 ,G_pa_period_flag pa_utilization_options.pa_period_flag%TYPE
161 ,G_ge_period_flag pa_utilization_options.global_exp_period_flag%TYPE
162 ,G_forecast_thru_date pa_utilization_options.forecast_thru_date%TYPE
163 ,G_actuals_thru_date pa_utilization_options.actuals_thru_date%TYPE
164 ,G_util_calc_method VARCHAR2(30)
165 );
166
167 TYPE G_u3_parameters_rec
168 IS RECORD (
169 G_period_type VARCHAR2(2)
170 ,G_period_name gl_periods.period_name%TYPE
171 ,G_qtr_or_mon_num NUMBER
172 ,G_year_num NUMBER
173 ,G_person_id NUMBER
174 ,G_ge_end_date VARCHAR2(15)
175 ,G_eff_period_num NUMBER
176 );
177 /*
178 * The following Record maintains the Ids for all the
179 * Amount types - available in tables pa_amount_types_b
180 * and pa_amount_types_tl.
181 * It is populated in the procedure PA_REP_UTIL_GLOB.initialize_amt_type_id_cache.
182 */
183 TYPE G_amt_type_details_rec
184 IS RECORD (
185 G_res_hrs_id pa_amount_types_b.amount_type_id%TYPE
186 ,G_res_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
187 ,G_res_wtdhrs_people_id pa_amount_types_b.amount_type_id%TYPE
188 ,G_res_prvhrs_id pa_amount_types_b.amount_type_id%TYPE
189 ,G_res_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
190 ,G_res_prvwtdhrs_people_id pa_amount_types_b.amount_type_id%TYPE
191 ,G_res_utilprctghrs_id pa_amount_types_b.amount_type_id%TYPE
192 ,G_res_utilprctgcap_id pa_amount_types_b.amount_type_id%TYPE
193 ,G_res_cap_id pa_amount_types_b.amount_type_id%TYPE
194 ,G_res_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
195 ,G_org_sub_hrs_id pa_amount_types_b.amount_type_id%TYPE
196 ,G_org_sub_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
197 ,G_org_sub_prvhrs_id pa_amount_types_b.amount_type_id%TYPE
198 ,G_org_sub_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
199 ,G_org_sub_utilprctghrs_id pa_amount_types_b.amount_type_id%TYPE
200 ,G_org_sub_utilprctgcap_id pa_amount_types_b.amount_type_id%TYPE
201 ,G_org_sub_cap_id pa_amount_types_b.amount_type_id%TYPE
202 ,G_org_sub_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
203 ,G_org_sub_headcount_id pa_amount_types_b.amount_type_id%TYPE
204 ,G_org_sub_empheadcount_id pa_amount_types_b.amount_type_id%TYPE
205 ,G_org_dir_hrs_id pa_amount_types_b.amount_type_id%TYPE
206 ,G_org_dir_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
210 ,G_org_dir_utilprctgcap_id pa_amount_types_b.amount_type_id%TYPE
207 ,G_org_dir_prvhrs_id pa_amount_types_b.amount_type_id%TYPE
208 ,G_org_dir_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
209 ,G_org_dir_utilprctghrs_id pa_amount_types_b.amount_type_id%TYPE
211 ,G_org_dir_cap_id pa_amount_types_b.amount_type_id%TYPE
212 ,G_org_dir_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
213 ,G_org_dir_headcount_id pa_amount_types_b.amount_type_id%TYPE
214 ,G_org_dir_empheadcount_id pa_amount_types_b.amount_type_id%TYPE
215 ,G_org_tot_hrs_id pa_amount_types_b.amount_type_id%TYPE
216 ,G_org_tot_wtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
217 ,G_org_tot_prvhrs_id pa_amount_types_b.amount_type_id%TYPE
218 ,G_org_tot_prvwtdhrs_org_id pa_amount_types_b.amount_type_id%TYPE
219 ,G_org_tot_utilprctghrs_id pa_amount_types_b.amount_type_id%TYPE
220 ,G_org_tot_utilprctgcap_id pa_amount_types_b.amount_type_id%TYPE
221 ,G_org_tot_cap_id pa_amount_types_b.amount_type_id%TYPE
222 ,G_org_tot_reducedcap_id pa_amount_types_b.amount_type_id%TYPE
223 ,G_org_tot_headcount_id pa_amount_types_b.amount_type_id%TYPE
224 ,G_org_tot_empheadcount_id pa_amount_types_b.amount_type_id%TYPE
225 ,G_quantity_id pa_amount_types_b.amount_type_id%TYPE
226 );
227
228 TYPE G_last_run_when_rec
229 IS RECORD (
230 G_ac_last_run_date DATE
231 ,G_fc_last_run_date DATE
232 );
233
234 /*
235 * Variable definitions for the Global Record types.
236 */
237 G_input_parameters G_input_parameters_rec;
238 G_who_columns G_who_columns_rec;
239 G_implementation_details G_implementation_details_rec;
240 G_util_option_details G_util_option_details_rec;
241 G_amt_type_details G_amt_type_details_rec;
242 G_u3_parameters G_u3_parameters_rec;
243 G_BAL_TYPE_C G_BAL_TYPE_REC_C;
244 G_OBJ_TYPE_C G_OBJ_TYPE_REC_C;
245 G_PERIOD_TYPE_C G_PERIOD_TYPE_REC_C;
246 G_last_run_when G_last_run_when_rec;
247
248 /*
249 * Procedures.
250 */
251 PROCEDURE Get_Util_AC_Parm(
252 errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
253 ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
254 ,p_ac_start_date IN VARCHAR2
255 ,p_ac_end_date IN VARCHAR2
256 ,p_fc_start_date IN VARCHAR2
257 ,p_fc_end_date IN VARCHAR2
258 ,p_org_rollup_method IN VARCHAR2
259 ,p_debug_mode IN VARCHAR2
260 );
261 PROCEDURE Get_Util_FC_Parm(
262 errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
263 ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
264 ,p_ac_start_date IN VARCHAR2
265 ,p_ac_end_date IN VARCHAR2
266 ,p_fc_start_date IN VARCHAR2
267 ,p_fc_end_date IN VARCHAR2
268 ,p_org_rollup_method IN VARCHAR2
269 ,p_debug_mode IN VARCHAR2
270 );
274 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
271 PROCEDURE Get_Util_Prc_Switch(
272 x_prc_switch OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
273 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
275 );
276
277
278 PROCEDURE Get_Effective_Start_Period_Num( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
279 ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
280 ,effective_start_period_num OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
281 ,p_period_set_name IN VARCHAR2
282 ,p_period_type IN VARCHAR2
283 ,p_start_date IN DATE
284 );
285
286 /*
287 * Procedure to populate the parameter cache with
288 * the parameters entered during the concurrent process.
289 */
290 PROCEDURE initialize_util_cache(
291 p_ac_start_date IN DATE
292 ,p_ac_end_date IN DATE
293 ,p_fc_start_date IN DATE
294 ,p_fc_end_date IN DATE
295 ,p_org_rollup_method IN VARCHAR2 default 'I' /* bug 2386679 */
296 ,p_debug_mode IN VARCHAR2
297 );
298
299 /*
300 * Procedure to initialize the variables holding the
301 * amount_type_ids with their corresponding values.
302 */
303 PROCEDURE initialize_amt_type_id_cache;
304
305 /*
306 * Procedures to set parameters from the U3 screen.
307 */
308 PROCEDURE SetU3PeriodType (p_period_type IN pa_implementations.pa_period_type%TYPE);
309 PROCEDURE SetU3PeriodName (p_period_name IN gl_periods.period_name%TYPE);
310 PROCEDURE SetU3QtrOrMonNum(p_qtr_or_mon_num IN VARCHAR2);
311 PROCEDURE SetU3YearNum (p_year_num IN VARCHAR2);
312 PROCEDURE SetU3PersonId (p_person_id IN VARCHAR2);
313 PROCEDURE SetU3GeEndDate (p_ge_end_date IN VARCHAR2);
314 PROCEDURE SetU3EffPeriodNum (p_eff_period_num IN VARCHAR2);
315
316 /*
317 * Procedures to set parameters from the U1 screen.
318 */
319 PROCEDURE SetU1ShowPrctgBy(p_showprctgby IN VARCHAR2);
320
321 /*
322 * Functions.
323 */
324
325 /*
326 * Functions to return Period_types.
327 */
328 FUNCTION GetPeriodTypeGl RETURN VARCHAR2;
329 FUNCTION GetPeriodTypePa RETURN VARCHAR2;
330 FUNCTION GetPeriodTypeGe RETURN VARCHAR2;
331
332 /*
333 * Functions to return Balance_types.
334 */
335 FUNCTION GetBalTypeActuals RETURN VARCHAR2;
336 FUNCTION GetBalTypeForecast RETURN VARCHAR2;
337
338 /*
339 * Functions to return Object_types..
340 */
341 FUNCTION GetObjectTypeOrg RETURN VARCHAR2;
342 FUNCTION GetObjectTypeOrgUc RETURN VARCHAR2;
343 FUNCTION GetObjectTypeOrgWt RETURN VARCHAR2;
344 FUNCTION GetObjectTypeRes RETURN VARCHAR2;
345 FUNCTION GetObjectTypeResUco RETURN VARCHAR2;
346 FUNCTION GetObjectTypeResUcr RETURN VARCHAR2;
347 FUNCTION GetObjectTypeResWt RETURN VARCHAR2;
348 FUNCTION GetObjectTypeUtilDet RETURN VARCHAR2;
349
350
351 /*
352 * Functions to get parameters from the U3 screen.
353 */
354 FUNCTION GetU3PeriodType RETURN pa_implementations.pa_period_type%TYPE;
355 FUNCTION GetU3PeriodName RETURN gl_periods.period_name%TYPE;
356 FUNCTION GetU3QtrOrMonNum RETURN NUMBER;
357 FUNCTION GetU3YearNum RETURN NUMBER;
358 FUNCTION GetU3PersonId RETURN NUMBER;
359 FUNCTION GetU3GeEndDate RETURN VARCHAR2;
360 FUNCTION GetU3EffPeriodNum RETURN NUMBER;
361
362 FUNCTION GetUtilCalcMethod RETURN VARCHAR2;
363 /*
364 * Function to return Fetch Size.
365 */
366 FUNCTION GetFetchSize RETURN NUMBER;
367
368 /*
369 * Function to return Period Set Name.
370 */
371 FUNCTION GetPeriodSetName RETURN gl_sets_of_books.period_set_name%TYPE;
372 /*
373 * Function to return org_id.
374 */
375 FUNCTION GetOrgId RETURN pa_implementations.org_id%TYPE;
376 FUNCTION GetOrgStructureVersionId RETURN pa_implementations.org_structure_version_id%TYPE;
377
378 /*
379 * Functions to return dummy values.
380 */
381 FUNCTION GetDummy RETURN VARCHAR2;
382 FUNCTION GetDummyDate RETURN DATE;
383 FUNCTION GetU1ShowPrctgBy RETURN VARCHAR2;
384 /* Functions to return Period Type Quarter and Period Type Year
385 */
386 FUNCTION GetPeriodTypeQr RETURN VARCHAR2;
387 FUNCTION GetperiodTypeYr RETURN VARCHAR2;
388 /*
389 * The following Functions are for getting the last run details
390 */
391 FUNCTION GetActualsLastRunDate RETURN DATE;
392 FUNCTION GetForecastLastRunDate RETURN DATE;
393
394 /*
395 * The following subprograms are for getting and setting
396 * the Organization ID and period information for U1 screen
397 */
398 FUNCTION GetU1OrganizationID return NUMBER;
399 PROCEDURE SetU1OrganizationID(p_organization_id IN NUMBER);
400
401 PROCEDURE SetU1Params(p_organization_id IN NUMBER, p_period_type IN VARCHAR2, p_period_name IN VARCHAR2, p_period_year IN NUMBER);
402 FUNCTION GetU1PeriodType return VARCHAR2;
403 FUNCTION GetU1PeriodName return VARCHAR2;
404 FUNCTION GetU1PeriodYear return VARCHAR2;
405 FUNCTION GetU1PeriodQuarter return VARCHAR2;
406 FUNCTION GetU1GlobalExpPeriodEndDate return DATE;
407
408 /*
409 * Bug 2447797 - This following procedure initializes Global variables.
410 * It replaces the procedure auto_util_cache.
411 */
412 PROCEDURE update_util_cache;
413
414
415 END PA_REP_UTIL_GLOB;