[Home] [Help]
PACKAGE: APPS.PSB_WORKSHEET
Source
1 PACKAGE PSB_WORKSHEET AS
2 /* $Header: PSBVWCMS.pls 120.11 2005/11/14 11:52:27 viraghun ship $ */
3
4 /* ----------------------------------------------------------------------- */
5
6 -- API name : Check_Reentrant_Status
7 -- Type : Private <Implementation>
8 -- Pre-reqs : FND_API, FND_MESSAGE
9 -- .
10 -- Version : Current version 1.0
11 -- Initial version 1.0
12 -- Created 07/22/1997 by Supriyo Ghosh
13 --
14 -- Notes : Check Reentrant Status
15 --
16 g_gl_cutoff_period DATE;
17 -- made the above variable public as part of bug fix 3469514.
18
19 /* Bug 3458191 start */
20 -- introduce the following variables to caching worksheet information
21 g_ws_creation_flag BOOLEAN := FALSE;
22 g_worksheet_id NUMBER;
23
24 -- made the following variables public
25 g_global_worksheet_id NUMBER;
26 g_allocrule_set_id NUMBER;
27 g_budget_calendar_id NUMBER;
28 g_rounding_factor NUMBER;
29 g_stage_set_id NUMBER;
30 g_flex_mapping_set_id NUMBER;
31 g_current_stage_seq NUMBER;
32 g_local_copy_flag VARCHAR2(1);
33 /* Bug 3458191 end */
34
35 g_chart_of_accounts_id NUMBER; -- Bug#4571412
36
37 /* Bug 3543845 start */
38 -- Introduced to improving performance for first time worksheet creation
39 g_ws_first_time_creation_flag BOOLEAN := FALSE;
40 g_ps_acct_pos_set_id NUMBER;
41 g_nps_acct_pos_set_id NUMBER;
42
43
44 -- Made the following variable public
45 g_parameter_set_id NUMBER;
46 g_num_years_to_allocate NUMBER;
47 g_budget_by_position VARCHAR2(1);
48 g_root_budget_group_id NUMBER;
49 /* Bug 3543845 End */
50
51 PROCEDURE Check_Reentrant_Status
52 ( p_return_status OUT NOCOPY VARCHAR2,
53 p_worksheet_id IN NUMBER,
54 p_parameter_set_id IN NUMBER,
55 p_constraint_set_id IN NUMBER,
56 p_allocrule_set_id IN NUMBER,
57 p_budget_calendar_id IN NUMBER,
58 p_budget_group_id IN NUMBER,
59 p_data_extract_id IN NUMBER,
60 p_gl_budget_set_id IN NUMBER
61 );
62
63 /* ----------------------------------------------------------------------- */
64
65 -- API name : Create_WS_Line_Items
66 -- Type : Private <Implementation>
67 -- Pre-reqs : FND_API, FND_MESSAGE, PSB_WS_ACCT1, PSB_WS_ACCT2
68 -- .
69 -- Version : Current version 1.0
70 -- Initial version 1.0
71 -- Created 05/16/1997 by Supriyo Ghosh
72 --
73 -- Notes : Create Worksheet Account and Position Line Items
74 --
75
76 PROCEDURE Create_WS_Line_Items
77 ( p_api_version IN NUMBER,
78 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
79 p_return_status OUT NOCOPY VARCHAR2,
80 p_worksheet_id IN NUMBER
81 );
82
83 /* ----------------------------------------------------------------------- */
84
85 -- API name : Delete_WS_Line_Items
86 -- Type : Private <Implementation>
87 -- Pre-reqs : FND_API, FND_MESSAGE
88 -- .
89 -- Version : Current version 1.0
90 -- Initial version 1.0
91 -- Created 07/02/1997 by Supriyo Ghosh
92 --
93 -- Notes : Delete Worksheet Account and Position Line Items
94 --
95
96 PROCEDURE Delete_WS_Line_Items
97 ( p_api_version IN NUMBER,
98 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
99 p_return_status OUT NOCOPY VARCHAR2,
100 p_worksheet_id IN NUMBER,
101 p_global_worksheet IN VARCHAR2 := FND_API.G_TRUE
102 );
103
104 /* ----------------------------------------------------------------------- */
105
106 -- API name : Create_Worksheet
107 -- Type : Private <Implementation>
108 -- Pre-reqs : FND_API, FND_MESSAGE
109 -- .
110 -- Version : Current version 1.0
111 -- Initial version 1.0
112 -- Modified 03/10/1998 by Supriyo Ghosh
113 -- Added p_use_revised_element_rates
114 -- Modified 09/08/1997 by Supriyo Ghosh
115 -- Modified 08/29/1997 by Kumaresh Sankar
116 -- Modified 06/24/1997 by Supriyo Ghosh
117 -- Created 05/16/1997 by Supriyo Ghosh
118 --
119 -- Notes : Create Worksheet Header
120 --
121
122 PROCEDURE Create_Worksheet
123 ( p_api_version IN NUMBER,
124 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
125 p_return_status OUT NOCOPY VARCHAR2,
126 p_budget_group_id IN NUMBER,
127 p_budget_calendar_id IN NUMBER,
128 p_worksheet_type IN VARCHAR2,
129 p_name IN VARCHAR2,
130 p_description IN VARCHAR2,
131 p_ws_creation_complete IN VARCHAR2,
132 p_stage_set_id IN NUMBER,
133 p_current_stage_seq IN NUMBER,
134 p_global_worksheet_id IN NUMBER,
135 p_global_worksheet_flag IN VARCHAR2,
136 p_global_worksheet_option IN VARCHAR2,
137 p_local_copy_flag IN VARCHAR2,
138 p_copy_of_worksheet_id IN NUMBER,
139 p_freeze_flag IN VARCHAR2,
140 p_budget_by_position IN VARCHAR2,
141 p_use_revised_element_rates IN VARCHAR2,
142 p_num_proposed_years IN NUMBER,
143 p_num_years_to_allocate IN NUMBER,
144 p_rounding_factor IN NUMBER,
145 p_gl_cutoff_period IN DATE,
146 p_budget_version_id IN NUMBER,
147 p_gl_budget_set_id IN NUMBER,
148 p_include_stat_balance IN VARCHAR2,
149 p_include_trans_balance IN VARCHAR2,
150 p_include_adj_period IN VARCHAR2,
151 p_data_extract_id IN NUMBER,
152 p_parameter_set_id IN NUMBER,
153 p_constraint_set_id IN NUMBER,
154 p_allocrule_set_id IN NUMBER,
155 p_date_submitted IN DATE,
156 p_submitted_by IN NUMBER,
157 p_attribute1 IN VARCHAR2,
158 p_attribute2 IN VARCHAR2,
159 p_attribute3 IN VARCHAR2,
160 p_attribute4 IN VARCHAR2,
161 p_attribute5 IN VARCHAR2,
162 p_attribute6 IN VARCHAR2,
163 p_attribute7 IN VARCHAR2,
164 p_attribute8 IN VARCHAR2,
165 p_attribute9 IN VARCHAR2,
166 p_attribute10 IN VARCHAR2,
167 p_context IN VARCHAR2,
168 p_create_non_pos_line_items IN VARCHAR2,
169 p_apply_element_parameters IN VARCHAR2,
170 p_apply_position_parameters IN VARCHAR2,
171 p_create_positions IN VARCHAR2,
172 p_create_summary_totals IN VARCHAR2,
173 p_apply_constraints IN VARCHAR2,
174 p_flex_mapping_set_id IN NUMBER,
175 p_include_gl_commit_balance IN VARCHAR2,
176 p_include_gl_oblig_balance IN VARCHAR2,
177 p_include_gl_other_balance IN VARCHAR2,
178 p_include_cbc_commit_balance IN VARCHAR2,
179 p_include_cbc_oblig_balance IN VARCHAR2,
180 p_include_cbc_budget_balance IN VARCHAR2,
181 /* Included federal_ws_flag for Bug 3157960 */
182 p_federal_ws_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
183 /* bug no 4725091 */
184 p_include_gl_forwd_balance IN VARCHAR2,
185 p_worksheet_id OUT NOCOPY NUMBER
186 );
187
188 /* ----------------------------------------------------------------------- */
189
190 -- API name : Update_Worksheet
191 -- Type : Private <Implementation>
192 -- Pre-reqs : FND_API, FND_MESSAGE
193 -- .
194 -- Version : Current version 1.0
195 -- Initial version 1.0
196 -- Modified 03/10/1998 by Supriyo Ghosh
197 -- Added p_use_revised_element_rates
198 -- Modified 06/24/1997 by Supriyo Ghosh
199 -- Created 05/16/1997 by Supriyo Ghosh
200 --
201 -- Notes : Update Worksheet Header
202 --
203
204 PROCEDURE Update_Worksheet
205 ( p_api_version IN NUMBER,
206 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
207 p_return_status OUT NOCOPY VARCHAR2,
208 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
209 p_worksheet_type IN VARCHAR2 := FND_API.G_MISS_CHAR,
210 p_description IN VARCHAR2 := FND_API.G_MISS_CHAR,
211 p_ws_creation_complete IN VARCHAR2 := FND_API.G_MISS_CHAR,
212 p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
213 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
214 p_local_copy_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
215 p_copy_of_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
216 p_freeze_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
217 p_use_revised_element_rates IN VARCHAR2 := FND_API.G_MISS_CHAR,
218 /* Bug # 3083970 */
219 p_num_proposed_years IN NUMBER := FND_API.G_MISS_NUM,
220 p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
221 /* End bug */
222 p_date_submitted IN DATE := FND_API.G_MISS_DATE,
223 p_submitted_by IN NUMBER := FND_API.G_MISS_NUM,
224 p_attribute1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
225 p_attribute2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
226 p_attribute3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
227 p_attribute4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
228 p_attribute5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
229 p_attribute6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
230 p_attribute7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
231 p_attribute8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
232 p_attribute9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
233 p_attribute10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
234 p_context IN VARCHAR2 := FND_API.G_MISS_CHAR,
235 p_create_non_pos_line_items IN VARCHAR2 := FND_API.G_MISS_CHAR,
236 p_apply_element_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
237 p_apply_position_parameters IN VARCHAR2 := FND_API.G_MISS_CHAR,
238 p_create_positions IN VARCHAR2 := FND_API.G_MISS_CHAR,
239 p_create_summary_totals IN VARCHAR2 := FND_API.G_MISS_CHAR,
240 p_apply_constraints IN VARCHAR2 := FND_API.G_MISS_CHAR,
241 p_include_gl_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
242 p_include_gl_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
243 p_include_gl_other_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
244 p_include_cbc_commit_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
245 p_include_cbc_oblig_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
246 p_include_cbc_budget_balance IN VARCHAR2 := FND_API.G_MISS_CHAR,
247 /* For Bug No. 2312657 : Start */
248 p_gl_cutoff_period IN DATE := NULL,
249 p_gl_budget_set_id IN NUMBER := NULL,
250 /* For Bug No. 2312657 : End */
251 /* Included federal_ws_flag for Bug 3157960 */
252 p_federal_ws_flag IN VARCHAR2 := FND_API.G_MISS_CHAR,
253 /* bug no 4725091 */
254 p_include_gl_forwd_balance IN VARCHAR2 := FND_API.G_MISS_CHAR
255 );
256
257 /* ----------------------------------------------------------------------- */
258
259 -- API name : Delete_Worksheet
260 -- Type : Private <Implementation>
261 -- Pre-reqs : FND_API, FND_MESSAGE
262 -- .
263 -- Version : Current version 1.0
264 -- Initial version 1.0
265 -- Created 07/09/1997 by Supriyo Ghosh
266 --
267 -- Notes : Delete Worksheet Header
268 --
269
270 PROCEDURE Delete_Worksheet
271 ( p_api_version IN NUMBER,
272 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
273 p_return_status OUT NOCOPY VARCHAR2,
274 p_worksheet_id IN NUMBER
275 );
276
277 /* ----------------------------------------------------------------------- */
278
279 -- API name : Delete_WAL
280 -- Type : Private <Implementation>
281 -- Pre-reqs : FND_API, FND_MESSAGE
282 -- .
283 -- Version : Current version 1.0
284 -- Initial version 1.0
285 -- Created 08/27/1997 by Supriyo Ghosh
286 --
287 -- Notes : Delete Worksheet Account Line
288 --
289
290 PROCEDURE Delete_WAL
291 ( p_api_version IN NUMBER,
292 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
293 p_return_status OUT NOCOPY VARCHAR2,
294 p_account_line_id IN NUMBER
295 );
296
297 /* ----------------------------------------------------------------------- */
298
299 -- API name : Delete_WPL
300 -- Type : Private <Implementation>
301 -- Pre-reqs : FND_API, FND_MESSAGE
302 -- .
303 -- Version : Current version 1.0
304 -- Initial version 1.0
305 -- Created 12/08/1997 by Supriyo Ghosh
306 --
307 -- Notes : Delete Worksheet Position Line
308 --
309
310 PROCEDURE Delete_WPL
311 ( p_api_version IN NUMBER,
312 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
313 p_return_status OUT NOCOPY VARCHAR2,
314 p_worksheet_id IN NUMBER,
315 p_position_line_id IN NUMBER
316 );
317
318 /* ----------------------------------------------------------------------- */
319
320 -- API name : Delete_WFL
321 -- Type : Private <Implementation>
322 -- Pre-reqs : FND_API, FND_MESSAGE
323 -- .
324 -- Version : Current version 1.0
325 -- Initial version 1.0
326 -- Created 12/08/1997 by Supriyo Ghosh
327 --
328 -- Notes : Delete Worksheet Position FTE Line
329 --
330
331 PROCEDURE Delete_WFL
332 ( p_api_version IN NUMBER,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
334 p_return_status OUT NOCOPY VARCHAR2,
335 p_fte_line_id IN NUMBER
336 );
337
338 /* ----------------------------------------------------------------------- */
339
340 -- API name : Delete_WEL
341 -- Type : Private <Implementation>
342 -- Pre-reqs : FND_API, FND_MESSAGE
343 -- .
344 -- Version : Current version 1.0
345 -- Initial version 1.0
346 -- Created 12/08/1997 by Supriyo Ghosh
347 --
348 -- Notes : Delete Worksheet Position Element Cost Line
349 --
350
351 PROCEDURE Delete_WEL
355 p_element_line_id IN NUMBER
352 ( p_api_version IN NUMBER,
353 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
354 p_return_status OUT NOCOPY VARCHAR2,
356 );
357
358 /* ----------------------------------------------------------------------- */
359
360 -- API name : Delete_Summary_Lines
361 -- Type : Private <Implementation>
362 -- Pre-reqs : FND_API, FND_MESSAGE
363 -- .
364 -- Version : Current version 1.0
365 -- Initial version 1.0
366 -- Created 11/13/1997 by Supriyo Ghosh
367 --
368 -- Notes : Delete Worksheet Summary Account Lines
369 --
370
371 PROCEDURE Delete_Summary_Lines
372 ( p_api_version IN NUMBER,
373 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
374 p_return_status OUT NOCOPY VARCHAR2,
375 p_worksheet_id IN NUMBER
376 );
377
378 /* ----------------------------------------------------------------------- */
379
380 -- API name : Apply_Constraints
381 -- Type : Private
382 -- Pre-reqs : FND_API, FND_MESSAGE
383 -- .
384 -- Version : Current version 1.0
385 -- Initial version 1.0
386 -- Created 01/06/1998 by Supriyo Ghosh
387 --
388 -- Notes : Apply Constraints and log all Constraint
389 -- Validation Errors
390
391 PROCEDURE Apply_Constraints
392 ( p_api_version IN NUMBER,
393 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
394 p_return_status OUT NOCOPY VARCHAR2,
395 p_validation_status OUT NOCOPY VARCHAR2,
396 p_worksheet_id IN NUMBER,
397 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
398 p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
399 p_func_currency IN VARCHAR2 := FND_API.G_MISS_CHAR,
400 p_global_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
401 p_constraint_set_id IN NUMBER := FND_API.G_MISS_NUM,
402 p_constraint_set_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
403 p_constraint_set_threshold IN NUMBER := FND_API.G_MISS_NUM,
404 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
405 p_data_extract_id IN NUMBER := FND_API.G_MISS_NUM,
406 p_business_group_id IN NUMBER := FND_API.G_MISS_NUM,
407 p_budget_by_position IN VARCHAR2 := FND_API.G_MISS_CHAR
408 );
409
410 /* ----------------------------------------------------------------------- */
411
412 -- API name : Validate_Entity_Set
413 -- Type : Private
414 -- Pre-reqs : FND_API, FND_MESSAGE
415 -- .
416 -- Version : Current version 1.0
417 -- Initial version 1.0
418 -- Created 05/01/1998 by Supriyo Ghosh
419 --
420 -- Notes : Validate Entity Sets for a Data Extract
421
422 PROCEDURE Validate_Entity_Set
423 ( p_api_version IN NUMBER,
424 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
425 p_return_status OUT NOCOPY VARCHAR2,
426 p_data_extract_id IN NUMBER,
427 p_parameter_set_id IN NUMBER,
428 p_constraint_set_id IN NUMBER
429 );
430
431 /* ----------------------------------------------------------------------- */
432
433 PROCEDURE Pre_Create_Line_Items
434 ( p_api_version IN NUMBER,
435 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
436 p_return_status OUT NOCOPY VARCHAR2,
437 p_worksheet_id IN NUMBER
438 );
439
440 /* ----------------------------------------------------------------------- */
441
442 PROCEDURE Create_Acct_Line_Items
443 ( p_api_version IN NUMBER,
444 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
445 p_return_status OUT NOCOPY VARCHAR2,
446 p_worksheet_id IN NUMBER
447 );
448
449 /* ----------------------------------------------------------------------- */
450
451 PROCEDURE Create_Pos_Line_Items
452 ( p_api_version IN NUMBER,
453 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
454 p_return_status OUT NOCOPY VARCHAR2,
455 p_worksheet_id IN NUMBER
456 );
457
458 /* ----------------------------------------------------------------------- */
459
460 PROCEDURE Apply_Acct_Constraints
461 ( p_api_version IN NUMBER,
462 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
463 p_return_status OUT NOCOPY VARCHAR2,
464 p_worksheet_id IN NUMBER
465 );
466
467 /* ----------------------------------------------------------------------- */
468
469 PROCEDURE Apply_Pos_Constraints
470 ( p_api_version IN NUMBER,
471 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
472 p_return_status OUT NOCOPY VARCHAR2,
473 p_worksheet_id IN NUMBER
474 );
475
476 /* ----------------------------------------------------------------------- */
477
478 PROCEDURE Apply_Elem_Constraints
479 ( p_api_version IN NUMBER,
480 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
481 p_return_status OUT NOCOPY VARCHAR2,
482 p_worksheet_id IN NUMBER
483 );
484
485 /* ----------------------------------------------------------------------- */
486
487 PROCEDURE Post_Create_Line_Items
488 ( p_api_version IN NUMBER,
489 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
490 p_return_status OUT NOCOPY VARCHAR2,
491 p_worksheet_id IN NUMBER
492 );
493
494 /* ----------------------------------------------------------------------- */
495
496 -- API name : Get_Debug
497 -- Type : Private
498 -- Pre-reqs : None
499
500 FUNCTION Get_Debug RETURN VARCHAR2;
501
502 /* ----------------------------------------------------------------------- */
503
504 END PSB_WORKSHEET;