1 PACKAGE PSB_WS_ACCT1 AS
2 /* $Header: PSBVWA1S.pls 120.8 2006/04/07 11:17:46 shtripat ship $ */
3
4 cursor c_Distribute_WS (GlobalWSID NUMBER, BudgetGroupID NUMBER,
5 StartDate DATE, EndDate DATE) is
6 select worksheet_id
7 from PSB_WORKSHEETS
8 where nvl(global_worksheet_id, worksheet_id) = GlobalWSID
9 /*For Bug No : 2440100 Start*/
10 and worksheet_type <> 'L'
11 /*For Bug No : 2440100 End*/
12 and budget_group_id in
13 (select budget_group_id
14 from PSB_BUDGET_GROUPS
15 where budget_group_type = 'R'
16 and effective_start_date <= StartDate
17 and (effective_end_date is null or effective_end_date >= EndDate)
18 start with budget_group_id = BudgetGroupID
19 connect by prior parent_budget_group_id = budget_group_id);
20
21 TYPE g_budgetyear_rec_type IS RECORD
22 ( budget_year_id NUMBER,
23 budget_year_type_id NUMBER,
24 year_type VARCHAR2(10),
25 year_name VARCHAR2(15),
26 start_date DATE,
27 end_date DATE,
28 num_budget_periods NUMBER,
29 last_period_index NUMBER );
30
31 TYPE g_budgetyear_tbl_type IS TABLE OF g_budgetyear_rec_type
32 INDEX BY BINARY_INTEGER;
33
34 g_budget_years g_budgetyear_tbl_type;
35 g_num_budget_years NUMBER;
36
37 TYPE g_budgetperiod_rec_type IS RECORD
38 ( budget_period_id NUMBER,
39 budget_period_type VARCHAR2(1),
40 long_sequence_no NUMBER,
41 start_date DATE,
42 end_date DATE,
43 budget_year_id NUMBER,
44 num_calc_periods NUMBER );
45
46 TYPE g_budgetperiod_tbl_type IS TABLE OF g_budgetperiod_rec_type
47 INDEX BY BINARY_INTEGER;
48
49 g_budget_periods g_budgetperiod_tbl_type;
50 g_num_budget_periods NUMBER;
51
52 -- Bug#3126462: Support Percent type allocation rules for CY estimates
53 -- Global variable to store number of periods in CY.
54 g_cy_num_periods NUMBER := 0 ;
55
56 TYPE g_calcperiod_rec_type IS RECORD
57 ( calc_period_id NUMBER,
58 calc_period_type VARCHAR2(1),
59 start_date DATE,
60 end_date DATE,
61 budget_period_id NUMBER );
62
63 TYPE g_calcperiod_tbl_type IS TABLE OF g_calcperiod_rec_type
64 INDEX BY BINARY_INTEGER;
65
66 g_calculation_periods g_calcperiod_tbl_type;
67 g_num_calc_periods NUMBER;
68
69 g_max_num_amounts CONSTANT NUMBER := 60;
70 g_checkpoint_save CONSTANT NUMBER := 500;
71 g_limit_bulk_numrows CONSTANT NUMBER := 1000;
72
73 TYPE g_prdamt_tbl_type IS TABLE OF NUMBER
74 INDEX BY BINARY_INTEGER;
75
76 TYPE g_ccid_rec_type IS RECORD
77 ( ccid NUMBER,
78 start_date DATE,
79 end_date DATE );
80
81 TYPE g_ccid_tbl_type IS TABLE OF g_ccid_rec_type
82 INDEX BY BINARY_INTEGER;
83
84 g_account_set_id NUMBER;
85 g_ccids g_ccid_tbl_type;
86 g_num_ccids NUMBER;
87
88 TYPE SegNamArray IS TABLE OF VARCHAR2(9)
89 INDEX BY BINARY_INTEGER;
90
91 g_flex_code NUMBER;
92 g_seg_name SegNamArray;
93 g_num_segs NUMBER;
94
95 g_budget_calendar_id NUMBER;
96 g_startdate_pp DATE;
97 g_startdate_cy DATE;
98 g_enddate_cy DATE;
99 g_end_est_date DATE;
100 g_max_num_years NUMBER;
101
102 /* Bug No 2354918 Start */
103 g_cy_start_index NUMBER := 0;
104 /* Bug No 2354918 End */
105
106 -- Bug#4675858
107 -- Introducing packaged global variable to change
108 -- "Revise Projections" CP if it holds TRUE value.
109 g_soft_error_flag BOOLEAN := FALSE ;
110
111 /* ----------------------------------------------------------------------- */
112
113 -- API name : Cache_Budget_Calendar
114 -- Type : Private <Implementation>
115 -- Pre-reqs : FND_API, FND_MESSAGE
116 -- .
117 -- Version : Current version 1.0
118 -- Initial version 1.0
119 -- Created 05/16/1997 by Supriyo Ghosh
120 --
121 -- Notes : Cache Budget Calendar
122 --
123
124 PROCEDURE Cache_Budget_Calendar
125 ( p_return_status OUT NOCOPY VARCHAR2,
126 p_budget_calendar_id IN NUMBER
127 );
128
129 /* ----------------------------------------------------------------------- */
130
131 -- API name : Get_Budget_Calendar_Info
132 -- Type : Private <Implementation>
133 -- Pre-reqs : FND_API, FND_MESSAGE
134 -- .
135 -- Version : Current version 1.0
136 -- Initial version 1.0
137 -- Created 26-MAR-1998 by Shailendra Rawat
138 --
139 -- Notes : This API is a wrapper for Cache_Budget_Calendar API.
140 -- Created as PL/SQL 1.0 cannot use Cache_Budget_Calendar
141 -- API because it sets values in package variables.
142 -- This API will be basically used by Oracle*Reports.
143 --
144
145 PROCEDURE Get_Budget_Calendar_Info
146 ( p_return_status OUT NOCOPY VARCHAR2,
147 p_budget_calendar_id IN NUMBER,
148 p_startdate_pp OUT NOCOPY DATE,
149 p_enddate_cy OUT NOCOPY DATE
150 );
151
152 /* ----------------------------------------------------------------------- */
153
154 -- Map Account based on flex mapping set
155
156 FUNCTION Map_Account
157 ( p_flex_mapping_set_id IN NUMBER,
158 p_ccid IN NUMBER,
159 p_budget_year_type_id IN NUMBER
160 ) RETURN NUMBER;
161
162 /* ----------------------------------------------------------------------- */
163
164 -- API name : Check_CCID_Type
165 -- Type : Private <Implementation>
166 -- Pre-reqs : FND_API, FND_MESSAGE
167 -- .
168 -- Version : Current version 1.0
169 -- Initial version 1.0
170 -- Created 05/16/1997 by Supriyo Ghosh
171 -- .
172 -- Notes : Return the CCID Type for an Account; return values
173 -- are 'PERSONNEL_SERVICES', 'NON_PERSONNEL_SERVICES'
174 --
175
176 PROCEDURE Check_CCID_Type
177 ( p_api_version IN NUMBER,
178 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
179 p_return_status OUT NOCOPY VARCHAR2,
180 p_ccid_type OUT NOCOPY VARCHAR2,
181 p_flex_code IN NUMBER,
182 p_ccid IN NUMBER,
183 p_budget_group_id IN NUMBER
184 );
185
186 /* ----------------------------------------------------------------------- */
187
188 -- API name : Find_CCIDs
189 -- Type : Private <Implementation>
190 -- Pre-reqs : FND_API, FND_MESSAGE
191 -- .
192 -- Version : Current version 1.0
193 -- Initial version 1.0
194 -- Created 05/16/1997 by Supriyo Ghosh
195 --
196 -- Notes : Cache all CCIDs for specified Account Set
197 --
198
199 PROCEDURE Find_CCIDs
200 ( p_return_status OUT NOCOPY VARCHAR2,
201 p_account_set_id IN NUMBER
202 );
203
204 /* ----------------------------------------------------------------------- */
205
206 -- API name : Create_Account_Dist
207 -- Type : Private <Implementation>
208 -- Pre-reqs : FND_API, FND_MESSAGE, FND_FLEX_EXT
209 -- .
210 -- Version : Current version 1.0
211 -- Initial version 1.0
212 -- Created 05/16/1997 by Supriyo Ghosh
213 -- Modified 06/25/1997 by Supriyo Ghosh
214 -- Modified 09/02/1997 by Supriyo Ghosh
215 -- Changed Rounding Factor Logic
216 -- Modified 10/28/1997 by Supriyo Ghosh
217 -- Added Parameters for Volume Inserts
218 --
219 -- Notes : Insert Worksheet Account Distributions for Account
220 -- and Position Lines
221 --
222
223 PROCEDURE Create_Account_Dist
224 ( p_api_version IN NUMBER,
225 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
226 p_return_status OUT NOCOPY VARCHAR2,
227 p_account_line_id OUT NOCOPY NUMBER,
228 p_worksheet_id IN NUMBER,
229 p_check_spal_exists IN VARCHAR2 := FND_API.G_TRUE,
230 p_gl_cutoff_period IN DATE := FND_API.G_MISS_DATE,
231 p_allocrule_set_id IN NUMBER := FND_API.G_MISS_NUM,
232 p_budget_calendar_id IN NUMBER := FND_API.G_MISS_NUM,
233 p_rounding_factor IN NUMBER := FND_API.G_MISS_NUM,
234 p_stage_set_id IN NUMBER := FND_API.G_MISS_NUM,
235 p_budget_year_id IN NUMBER,
236 p_budget_group_id IN NUMBER,
237 p_ccid IN NUMBER := FND_API.G_MISS_NUM,
238 p_flex_mapping_set_id IN NUMBER := FND_API.G_MISS_NUM,
239 p_map_accounts IN BOOLEAN := FALSE,
240 p_functional_transaction IN VARCHAR2 := NULL,
241 p_flex_code IN NUMBER := FND_API.G_MISS_NUM,
242 p_concatenated_segments IN VARCHAR2 := FND_API.G_MISS_CHAR,
243 p_startdate_pp IN DATE := FND_API.G_MISS_DATE,
244 p_template_id IN NUMBER := FND_API.G_MISS_NUM,
245 p_currency_code IN VARCHAR2,
246 p_balance_type IN VARCHAR2,
247 p_ytd_amount IN NUMBER,
248 p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
249 p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
250 p_period_amount IN g_prdamt_tbl_type,
251 p_position_line_id IN NUMBER := FND_API.G_MISS_NUM,
252 p_element_set_id IN NUMBER := FND_API.G_MISS_NUM,
253 p_salary_account_line IN VARCHAR2 := FND_API.G_FALSE,
254 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
255 p_start_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
256 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
257 p_end_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
258 p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
259 /* bug start 3996052 */
260 p_update_cy_estimate IN VARCHAR2 := 'N'
261 /* bug end 3996052 */
262 );
263
264 /* ----------------------------------------------------------------------- */
265
266 -- API name : Create_Account_Dist
267 -- Type : Private <Implementation>
268 -- Pre-reqs : FND_API, FND_MESSAGE, FND_FLEX_EXT
269 -- .
270 -- Version : Current version 1.0
271 -- Initial version 1.0
272 -- Created 07/09/1997 by Supriyo Ghosh
273 -- Modified 09/02/1997 by Supriyo Ghosh
274 -- Changed Rounding Factor Logic
275 --
276 -- Notes : Modify Worksheet Account Distributions for Account
277 -- Lines
278 --
279
280 PROCEDURE Create_Account_Dist
281 ( p_api_version IN NUMBER,
282 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
283 p_return_status OUT NOCOPY VARCHAR2,
284 p_worksheet_id IN NUMBER,
285 p_distribute_flag IN VARCHAR2 := FND_API.G_FALSE,
286 p_account_line_id IN NUMBER,
287 p_check_stages IN VARCHAR2 := FND_API.G_TRUE,
288 p_ytd_amount IN NUMBER := FND_API.G_MISS_NUM,
289 p_annual_fte IN NUMBER := FND_API.G_MISS_NUM,
290 p_period_amount IN g_prdamt_tbl_type,
291 p_budget_group_id IN NUMBER := FND_API.G_MISS_NUM,
292 p_service_package_id IN NUMBER := FND_API.G_MISS_NUM,
293 p_current_stage_seq IN NUMBER := FND_API.G_MISS_NUM,
294 p_copy_of_account_line_id IN NUMBER := FND_API.G_MISS_NUM,
295 /* start bug 4128196 */
296 p_update_cy_estimate IN VARCHAR2 := 'N'
297 /* end bug 4128196 */
298 );
299
300 /* ----------------------------------------------------------------------- */
301
302 -- API name : Copy_CY_Estimates
303 -- Type : Private <Implementation>
304 -- Pre-reqs : FND_API, FND_MESSAGE, FND_FLEX_EXT
305 -- .
306 -- Version : Current version 1.0
307 -- Initial version 1.0
308 -- Created 08/26/1997 by Supriyo Ghosh
309 -- Modified 10/28/1997 by Supriyo Ghosh
310 -- Added Parameters for Volume Inserts
311 --
312 -- Notes : Copy CY Estimates from Actuals
313 --
314
315 PROCEDURE Copy_CY_Estimates
316 ( p_return_status OUT NOCOPY VARCHAR2,
317 p_worksheet_id IN NUMBER,
318 p_service_package_id IN NUMBER,
319 p_rounding_factor IN NUMBER,
320 p_start_stage_seq IN NUMBER,
321 p_budget_group_id IN NUMBER,
322 p_stage_set_id IN NUMBER,
323 p_budget_year_id IN NUMBER,
324 p_ccid IN NUMBER,
325 p_currency_code IN VARCHAR2
326 );
327
328 /* ----------------------------------------------------------------------- */
329
330 -- API name : Update_YTD_Amount
331 -- Type : Private <Implementation>
332 -- Pre-reqs : FND_API, FND_MESSAGE
333 -- .
334 -- Version : Current version 1.0
335 -- Initial version 1.0
336 -- Created 05/18/1997 by Supriyo Ghosh
337 --
338 -- Notes : Update YTD Amount for Account Line
339 --
340
341 PROCEDURE Update_YTD_Amount
342 ( p_api_version IN NUMBER,
343 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_NONE,
344 p_return_status OUT NOCOPY VARCHAR2,
345 p_account_line_id IN NUMBER
346 );
347
348 /* ----------------------------------------------------------------------- */
349
350 -- API name : Flex_Info
351 -- Type : Private <Implementation>
352 -- Pre-reqs : FND_API, FND_MESSAGE
353 -- .
354 -- Version : Current version 1.0
355 -- Initial version 1.0
356 -- Created 07/08/1997 by Supriyo Ghosh
357 --
358 -- Notes : Get Flex Info
359 --
360
361 PROCEDURE Flex_Info
362 ( p_return_status OUT NOCOPY VARCHAR2,
363 p_flex_code IN NUMBER
364 );
365
366 /* ----------------------------------------------------------------------- */
367
368 -- API name : Dsql_Execute
369 -- Type : Private <Implementation>
370 -- Pre-reqs : FND_API, FND_MESSAGE
371 -- .
372 -- Version : Current version 1.0
373 -- Initial version 1.0
374 -- Created 07/08/1997 by Supriyo Ghosh
375 --
376 -- Notes : Execute dynamic sql statement
377 --
378
379 FUNCTION dsql_execute
380 ( sql_statement IN VARCHAR2
381 ) RETURN NUMBER;
382
383 /* ----------------------------------------------------------------------- */
384
385 -- API name : DSQL_Budget_Balance
386 -- Type : Private <Implementation>
387 -- .
388 -- Version : Current version 1.1
389 -- Initial version 1.1
393 --
390 -- Created 05/24/1999 by Supriyo Ghosh
391 --
392 -- Notes : Create dynamic sql for extracting GL Balances
394
395 PROCEDURE DSQL_Budget_Balance
396 ( p_return_status OUT NOCOPY VARCHAR2,
397 p_sql_statement OUT NOCOPY VARCHAR2,
398 p_set_of_books_id IN NUMBER,
399 p_budgetary_control IN VARCHAR2,
400 p_budget_version_id IN NUMBER,
401 p_gl_budget_set_id IN NUMBER,
402 p_incl_adj_period IN VARCHAR2,
403 p_map_criteria IN VARCHAR2
404 );
405
406 /* ----------------------------------------------------------------------- */
407
408 -- API name : DSQL_Actual_Balance
409 -- Type : Private <Implementation>
410 -- .
411 -- Version : Current version 1.1
412 -- Initial version 1.1
413 -- Created 05/24/1999 by Supriyo Ghosh
414 --
415 -- Notes : Create dynamic sql for extracting GL Balances
416 --
417
418 PROCEDURE DSQL_Actual_Balance
419 ( p_return_status OUT NOCOPY VARCHAR2,
420 p_sql_statement OUT NOCOPY VARCHAR2,
421 p_set_of_books_id IN NUMBER,
422 p_incl_adj_period IN VARCHAR2,
423 p_map_criteria IN VARCHAR2
424 );
425
426 /* ----------------------------------------------------------------------- */
427
428 -- API name : DSQL_Encum_Balance
429 -- Type : Private <Implementation>
430 -- .
431 -- Version : Current version 1.1
432 -- Initial version 1.1
433 -- Created 05/24/1999 by Supriyo Ghosh
434 --
435 -- Notes : Create dynamic sql for extracting GL Balances
436 --
437
438 PROCEDURE DSQL_Encum_Balance
439 ( p_return_status OUT NOCOPY VARCHAR2,
440 p_sql_statement OUT NOCOPY VARCHAR2,
441 p_set_of_books_id IN NUMBER,
442 p_incl_adj_period IN VARCHAR2,
443 p_map_criteria IN VARCHAR2,
444 p_include_gl_commit_balance IN VARCHAR2,
445 p_include_gl_oblig_balance IN VARCHAR2,
446 p_include_gl_other_balance IN VARCHAR2
447 );
448
449 /* ----------------------------------------------------------------------- */
450
451 -- API name : Apply_Account_Constraints
452 -- Type : Private <Implementation>
453 -- Pre-reqs : FND_API, FND_MESSAGE
454 -- .
455 -- Version : Current version 1.0
456 -- Initial version 1.0
457 -- Created 06/03/1997 by Supriyo Ghosh
458 --
459 -- Notes : Apply Account Constraints and log all Constraint
460 -- Validation Errors
461
462 PROCEDURE Apply_Account_Constraints
463 ( p_return_status OUT NOCOPY VARCHAR2,
464 p_validation_status OUT NOCOPY VARCHAR2,
465 p_worksheet_id IN NUMBER,
466 p_flex_mapping_set_id IN NUMBER,
467 p_budget_group_id IN NUMBER,
468 p_flex_code IN NUMBER,
469 p_func_currency IN VARCHAR2,
470 p_constraint_set_id IN NUMBER,
471 p_constraint_set_name IN VARCHAR2,
472 p_constraint_threshold IN NUMBER,
473 p_budget_calendar_id IN NUMBER
474 );
475
476 /* ----------------------------------------------------------------------- */
477
478 -- API name : Create_Note
479 -- Type : Private <Implementation>
480 -- Pre-reqs : FND_API, FND_MESSAGE
481 -- Created By : On 03/20/2001 by Manish Goel
482 --
483 -- Notes : Create a Note for Standard Budget Item in PSB_WS_ACCOUNT_LINE_NOTES
484 -- for the Bug No 1584464
485
486
487 -- Bug#4571412
488 -- Added parameters p_chart_of_accounts_id, p_budget_year, p_cc_id
489 -- to explain newly created message if update statment for
490 -- PSB_WS_ACCOUNT_LINE_NOTES fails.
491
492 PROCEDURE Create_Note
493 ( p_return_status OUT NOCOPY VARCHAR2,
494 p_account_line_id IN NUMBER,
495 p_note IN VARCHAR2,
496 p_chart_of_accounts_id IN NUMBER,
497 p_budget_year IN VARCHAR2,
498 p_cc_id IN NUMBER,
499 p_concatenated_segments IN VARCHAR2
500 );
501
502 /* ----------------------------------------------------------------------- */
503
504 -- API name : Get_Debug
505 -- Type : Private
506 -- Pre-reqs : None
507
508 FUNCTION Get_Debug RETURN VARCHAR2;
509
510 /* ----------------------------------------------------------------------- */
511
512 END PSB_WS_ACCT1;
[Home] [Help]