1 PACKAGE PA_ACCUM_UTILS AS
2 /* $Header: PAACUTIS.pls 120.1 2005/08/19 16:15:06 mwasowic noship $ */
3 -- This package contains the following procedures
4
5 -- Proj_level_record - This verifies for the existence of the Project level
6 -- record (Task id = 0 and Resource list member id = 0)
7 -- If available, returns the Project_Accum_id else
8 -- creates a record in PA_PROJECT_ACCUM_HEADERS and
9 -- returns the Project_Accum_Id
10
11 -- Get_Impl_Option - This returns the Accumulation option as specified
12 -- in PA_IMPLEMENTATIONS table . Returns whether
13 -- accumulation is maintained by PA_PERIOD or GL_PERIOD
14
15 -- Get_Current_period_Info - This returns all relevant details pertaining
16 -- to the current pa period
17
18 -- Get_pa_period_info - This returns all details pertaining to the
19 -- following
20 -- Current Pa period,Previous pa period, current
21 -- gl period , previous gl period, year pertaining
22 -- to the previously accumulated period
23
24 -- Check_Actuals_Details - For the given Project,Task and Resource
25 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
26 -- checks for detail records in
27 -- PA_PROJECT_ACCUM_ACTUALS table. It is possible
28 -- that the Headers table might have a record
29 -- but no corresponding detail record. This procedure
30 -- creates the detail records for all the tasks in
31 -- the hierarchy
32
33 -- Check_Cmt_Details - For the given Project,Task and Resource
34 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
35 -- checks for detail records in
36 -- PA_PROJECT_ACCUM_COMMITMENTS table. It is possible
37 -- that the Headers table might have a record
38 -- but no corresponding detail record. This procedure
39 -- creates the detail records for all the tasks in
40 -- the hierarchy
41
42
43 -- Check_Budget_Details - For the given Project,Task and Resource
44 -- combination in the PA_PROJECT_ACCUM_HEADERS table,
45 -- checks for detail records in
46 -- PA_PROJECT_ACCUM_COMMITMENTS table. It is possible
47 -- that the Headers table might have a record
48 -- but no corresponding detail record. This procedure
49 -- creates the detail records for all the tasks in
50 -- the hierarchy
51
52 -- Get_Config_Option - For the given Accumulation Category
53 -- checks whether the given column is configured
54 -- for Accumulation. The Accum_flag 'Y' or 'N'
55 -- determines whether the said column is to be
56 -- accumulated or not
57
58 -- Get_existing_higher_tasks - For the given task, returns all the higher level
59 -- tasks which are available in
60 -- PA_PROJECT_ACCUM_HEADERS .
61
62 -- set_check_reporting_end_Date This sets the global variable to the end date of the reporting
63 -- period for which the user wants to check
64 -- project summarization information.
65 --
66 -- get_check_reporting_end_date This returns the end date of the currently set reporting period
67 -- for which the user wants to check project
68 -- summarization information. If the global
69 -- variable has not been set, this returns the
70 -- current reporting period
71
72 -- Set_current_period_info This procedure sets the global variables
73 -- g_current_pa_period and g_current_gl_period
74
75 -- Get_current_pa_period This function returns the value of the global
76 -- variable g_current_pa_period, if set, else it
77 -- fetches pa period from database
78
79 -- Get_current_gl_period This function returns the value of the global
80 -- variable g_current_gl_period, if set, else it
81 -- fetches gl period from database
82
83 -- Get_Project_Info This function returns the value of the global
84 -- variables G_start_proj or G_end_proj
85 -- conditionally (depending on the value of
86 -- the parameter passed)
87
88 -- Get_Context_Info This function returns the value of the global
89 -- variable G_context
90
91 -- Get_Grouping_Id This function returns the value of the global
92 -- variable G_grouping_id
93
94
95 -- Package global variable to hold reporting period name used for summarization
96 -- checks
97 g_check_reporting_end_date date;
98
99 -- Package global variables for current PA and GL Periods
100
101 g_current_pa_period varchar2(20); -- current PA period
102 g_current_gl_period varchar2(15); -- current GL period
103
104 -- Global variables defined as part of Project Allocation Enhancements
105 G_context VARCHAR2(25);
106 G_start_proj VARCHAR2(25);
107 G_end_proj VARCHAR2(25);
108 G_grouping_id NUMBER;
109
110 PROCEDURE set_current_period_info ;
111
112 FUNCTION Get_current_pa_period RETURN Varchar2;
113 PRAGMA RESTRICT_REFERENCES( Get_current_pa_period, WNDS,WNPS );
114
115 FUNCTION Get_current_gl_period RETURN Varchar2;
116 PRAGMA RESTRICT_REFERENCES( Get_current_gl_period, WNDS,WNPS );
117
118 TYPE task_id_tabtype IS TABLE OF PA_TASKS.TASK_ID%TYPE INDEX BY BINARY_INTEGER;
119 Procedure Proj_level_record (X_project_id In Number,
120 X_current_pa_period In Varchar2,
121 X_current_gl_period In Varchar2,
122 X_impl_Option In Varchar2,
123 X_accum_id Out NOCOPY Number, --File.Sql.39 bug 4440895
124 X_Prev_Accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
125 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
126 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
127 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
128
129 Procedure Get_Impl_Option (X_Impl_Option Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
130 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
131 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
132 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
133
134
135 Procedure Get_Current_period_Info (X_Current_Pa_Period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
136 X_Current_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
137 x_current_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
138 x_current_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
139 x_current_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
140 x_current_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
141 x_current_year Out NOCOPY Number, --File.Sql.39 bug 4440895
142 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
143 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
144 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
145
146
147 Procedure Get_pa_period_Info
148 (X_impl_opt In Varchar2,
149 X_Prev_Accum_period in Varchar2,
150 X_Current_Pa_Period In Varchar2,
151 X_Current_gl_period In Varchar2,
152 x_current_pa_start_date In Date,
153 x_current_pa_end_date In Date,
154 x_current_gl_start_date In Date,
155 x_current_gl_end_date In Date,
156 X_Prev_pa_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
157 X_prev_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
158 x_prev_pa_year Out NOCOPY Number, --File.Sql.39 bug 4440895
159 x_prev_gl_year Out NOCOPY Number, --File.Sql.39 bug 4440895
160 x_prev_accum_year Out NOCOPY number, --File.Sql.39 bug 4440895
161 x_prev_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
162 x_prev_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
163 x_prev_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
164 x_prev_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
165 x_prev_accum_start_date In Out NOCOPY Date, --File.Sql.39 bug 4440895
166 x_prev_accum_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
167 x_prev_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
168 x_accum_period_type_changed IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
169 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
170 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
171 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
172
173
174 Procedure Check_Actuals_Details ( x_project_id In Number,
175 x_task_id In Number,
176 x_resource_list_member_id In Number,
177 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
178 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
179 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
180 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
181
182 Procedure Check_Cmt_Details ( x_project_id In Number,
183 x_task_id In Number,
184 x_resource_list_member_id In Number,
185 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
186 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
187 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
188 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
189
190 Procedure Check_Budget_Details ( x_project_id In Number,
191 x_task_id In Number,
192 x_resource_list_member_id In Number,
193 x_Budget_type_code In Varchar2,
194 x_recs_processed Out NOCOPY Number, --File.Sql.39 bug 4440895
195 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
196 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
197 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
198
199
200 Procedure Get_Config_Option (X_project_id In Number,
201 x_Accum_category_code In Varchar2,
202 x_Accum_column_code In Varchar2,
203 x_Accum_Flag Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
204 x_err_code In Out NOCOPY Number, --File.Sql.39 bug 4440895
205 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
206 x_err_stack In Out NOCOPY Varchar2 ) ; --File.Sql.39 bug 4440895
207
208 Procedure Get_existing_higher_tasks (x_project_id in Number,
209 X_task_id in Number,
210 X_resource_list_member_id In Number,
211 x_task_array Out NOCOPY task_id_tabtype, --File.Sql.39 bug 4440895
212 x_noof_tasks Out NOCOPY number, --File.Sql.39 bug 4440895
213 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
214 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
215 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
216
217 Procedure update_proj_accum_header (x_project_accum_id IN Number,
218 x_accum_period IN Varchar2,
219 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
220 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
221 x_err_code IN OUT NOCOPY Number ); --File.Sql.39 bug 4440895
222
223 Procedure update_tasks_restructured_flag (x_project_accum_id IN Number,
224 x_tasks_restructured_flag IN Varchar2,
225 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
226 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
227 x_err_code IN OUT NOCOPY Number ); --File.Sql.39 bug 4440895
228
229 Procedure check_tasks_restructured_flag (x_project_accum_id IN Number,
230 x_tasks_restructured_flag IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
231 x_err_stack IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
232 x_err_stage IN OUT NOCOPY Varchar2, --File.Sql.39 bug 4440895
233 x_err_code IN OUT NOCOPY Number ); --File.Sql.39 bug 4440895
234
235 PROCEDURE get_first_accum_period
236 (x_project_id IN NUMBER,
237 x_resource_list_id IN NUMBER DEFAULT NULL,
238 x_amount_type IN VARCHAR2 DEFAULT 'C',
239 x_period_type IN VARCHAR2 DEFAULT 'P',
240 x_period_name IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
241 x_period_start_date IN OUT NOCOPY DATE, --File.Sql.39 bug 4440895
242 x_err_code IN OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
243 x_err_stage IN OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
244 x_err_stack IN OUT NOCOPY VARCHAR2); --File.Sql.39 bug 4440895
245
246 PROCEDURE set_check_reporting_end_date
247 ( x_period_name IN VARCHAR2);
248 PRAGMA RESTRICT_REFERENCES( set_check_reporting_end_date, WNDS );
249
250 FUNCTION get_check_reporting_end_date
251 return date;
252 -- PRAGMA RESTRICT_REFERENCES( get_check_reporting_end_date, WNDS );
253
254 PROCEDURE Get_period_year_Info
255 (x_current_gl_period In Varchar2,
256 x_period_yr_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
257 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
258 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
259 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
260
261 FUNCTION Get_spread_amount_val
262 (x_from_date IN DATE,
263 x_to_date IN DATE,
264 x_amt_to_be_spread IN NUMBER,
265 x_which_date_flag IN VARCHAR2)
266 RETURN NUMBER;
267 PRAGMA RESTRICT_REFERENCES(Get_spread_amount_val, WNDS,WNPS );
268
269 Function Get_Project_Info
270 (x_From_Or_To IN Varchar2)
271 Return Varchar2;
272 PRAGMA RESTRICT_REFERENCES(Get_Project_Info, WNDS,WNPS );
273
274 Function Get_Context_Info
275 Return Varchar2;
276 PRAGMA RESTRICT_REFERENCES(Get_Context_Info, WNDS,WNPS );
277
278 Function Get_Grouping_Id
279 Return Number;
280 PRAGMA RESTRICT_REFERENCES(Get_Grouping_Id, WNDS,WNPS );
281
282
283 /*********************************************************************************
284 New procedures added for the performance issue 3653978
285 *********************************************************************************/
286
287 Procedure Get_pa_period_Info1
288 (X_impl_opt In Varchar2,
289 x_current_pa_start_date In Date,
290 x_current_gl_start_date In Date,
291 X_Prev_pa_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
292 X_prev_gl_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
293 x_prev_pa_year Out NOCOPY Number, --File.Sql.39 bug 4440895
294 x_prev_gl_year Out NOCOPY Number, --File.Sql.39 bug 4440895
295 x_prev_pa_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
296 x_prev_pa_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
297 x_prev_gl_start_date Out NOCOPY Date, --File.Sql.39 bug 4440895
298 x_prev_gl_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
299 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
300 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
301 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
302
303 Procedure Get_pa_period_Info2
304 (X_impl_opt In Varchar2,
305 X_Prev_Accum_period in Varchar2,
306 x_prev_accum_year Out NOCOPY number, --File.Sql.39 bug 4440895
307 x_prev_accum_start_date In Out NOCOPY Date, --File.Sql.39 bug 4440895
308 x_prev_accum_end_date Out NOCOPY Date, --File.Sql.39 bug 4440895
309 x_prev_prev_accum_period Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
310 x_accum_period_type_changed IN OUT NOCOPY BOOLEAN, --File.Sql.39 bug 4440895
311 x_err_stack In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
312 x_err_stage In Out NOCOPY Varchar2, --File.Sql.39 bug 4440895
313 x_err_code In Out NOCOPY Number ); --File.Sql.39 bug 4440895
314
315 /*********************************************************************************
316 End. New procedures added for the performance issue 3653978
317 *********************************************************************************/
318
319 End PA_ACCUM_UTILS;
[Home] [Help]