1 PACKAGE BODY PA_CAPITAL_PROJECT_UTILS as
2 /* $Header: PAXCAUTB.pls 120.1 2007/02/07 13:52:31 anuagraw ship $ */
3
4 -----------------------------------------------------------------------------------
5 -- FUNCTION
6 -- UnReversed_Assets_Exist
7 -- PURPOSE
8 -- Returns 'Y' if there exists asset lines that have not been reversed
9 -- for the given Asset Id.
10 -- Returns 'N' if there are no asset lines or all of its asset lines
11 -- reversed.
12 -- If 'Y' then in the Capital Projects form we should not allow the asset
13 -- to be detached from the capital event.
14 -- In the calling API, If 'Y' then raise error - PA_UNREVERSED_LINES_EXIST
15 -----------------------------------------------------------------------------------
16 Function UnReversed_Assets_Exist
17 (P_Asset_Id In Number) Return Varchar2 Is
18
19 l_reversed number := 0;
20
21 Begin
22
23 Begin
24 --check if asset lines have not been reversed
25 Select 1
26 Into l_reversed
27 From Dual
28 where exists (
29 SELECT 'X'
30 FROM pa_project_asset_lines pal1
31 WHERE pal1.project_asset_id = P_Asset_Id
32 AND pal1.rev_proj_asset_line_id is null
33 AND NOT EXISTS (select null from pa_project_asset_lines pal2
34 where pal2.project_asset_id = P_asset_id
35 and pal2.rev_proj_asset_line_id = pal1.project_asset_line_id));
36
37 RETURN 'Y';
38 Exception
39 When No_Data_Found Then
40 RETURN 'N';
41 End;
42
43 Exception
44 When Others Then
45 RAISE;
46 End UnReversed_Assets_Exist;
47
48 -----------------------------------------------------------------------------------
49 -- FUNCTION
50 -- UnReversed_Costs_Exist
51 -- PURPOSE
52 -- Returns 'Y' if there exists asset line details that have not been reversed
53 -- for the given EI Id.
54 -- Returns 'N' if there are no asset line details or all of its asset line
55 -- details reversed.
59 -----------------------------------------------------------------------------------
56 -- If 'Y' then in the Capital Projects form we should not allow the EI
57 -- to be detached from the capital event.
58 -- In the calling API, If 'Y' then raise error - PA_UNREVERSED_DETAILS_EXIST
60 Function UnReversed_Costs_Exist
61 (P_Ei_Id In Number) Return Varchar2 Is
62
63 l_reversed number := 0;
64
65 Begin
66
67 Begin
68 --check if asset line details have been reversed
69 Select 1
70 Into l_reversed
71 From Dual
72 where exists (
73 SELECT 'X'
74 FROM pa_project_asset_line_details pald
75 WHERE pald.expenditure_item_id = P_Ei_Id
76 AND pald.reversed_flag = 'N');
77
78 RETURN 'Y';
79 Exception
80 When No_Data_Found Then
81 RETURN 'N';
82 End;
83
84 Exception
85 When Others Then
86 RAISE;
87
88 End UnReversed_Costs_Exist;
89
90 -----------------------------------------------------------------------------------
91 -- FUNCTION
92 -- Reverse_Event
93 -- PURPOSE
94 -- Return 'Y' or 'N' depending on the below conditions for reversal
95 -- is true or not.
96 -- The conditions are similar to the ones existing in the form currently
97 -- namely, Do not reverse asset if:
98 -- 1. Not capitalized
99 -- 2. Capitalized but capitalized before previous reversal or not reversed before
100 -- 3. Corresponding FA Asset is not adjustable.
101 -- In the calling API, if event cannot be reversed then raise the error : PA_REVERSE_EVENT_WARN
102 -----------------------------------------------------------------------------------
103 Function Reverse_Event_Allow(P_Project_Id in Number, P_Capital_Event_Id in Number)
104 Return Varchar2 Is
105
106 --existing conditions for not allowing reversal for an individual asset
107 Cursor C_ReverseAsset Is
108 Select 1
109 From Dual
110 Where Exists (
111 Select 1
112 From Pa_Project_Assets
113 Where Project_Id = P_Project_Id
114 And Capital_Event_Id = P_Capital_Event_Id
115 And (CAPITALIZED_FLAG = 'N' or
116 (trunc(CAPITALIZED_DATE) < trunc(REVERSAL_DATE)
117 or
118 REVERSAL_DATE is not null )
119 or (Fa_Asset_Id is not null and Is_Asset_Adj_Allowed(Fa_Asset_Id,Book_Type_Code) = 0)
120 )
121 ) ;
122
123 L_Exists Number;
124
125 Begin
126
127 Open C_ReverseAsset;
128 Fetch C_ReverseAsset into l_Exists;
129 If C_ReverseAsset%NOTFOUND Then
130 --none of the above conditions met, go ahead and update all assets with reverse_flag = Y
131 Return 'Y';
132 Else
133 --any of the above conditions met, go ahead and ask user to reverse or not
134 Return 'N';
135 End If;
136 Close C_ReverseAsset;
137
138 Exception
139 When Others Then
140 Raise;
141 End Reverse_Event_Allow;
142
143 -----------------------------------------------------------------------------------
144 -- FUNCTION
145 -- Reverse_Event_Upd
146 -- PURPOSE
147 -- Perform the actual update of setting the reverse_flag to Y
148 -- And return the number of rows updated
149 -----------------------------------------------------------------------------------
150 Function Reverse_Event_Upd(P_Project_Id in Number,
151 P_Capital_Event_Id in Number)
152 Return Number Is
153
154 Begin
155
156 Update Pa_Project_Assets
157 Set Reverse_Flag = 'Y'
158 Where Project_Id = P_Project_Id
159 And Reverse_Flag = 'N'
160 And Capital_Event_Id = P_Capital_Event_Id;
161
162 Return SQL%ROWCOUNT;
163
164 Exception
165 When Others Then
166 Raise;
167 End Reverse_Event_Upd;
168
169 -----------------------------------------------------------------------------------
170 -- FUNCTION
171 -- Is_Asset_Adj_Allowed
172 -- PURPOSE
173 -- Given the Fa_Asset_Id/Book_Type_Code, check if the fa_asset_id
174 -- is adjustable in FA. By calling the fa_mass_add_validate.can_add_to_asset
175 -- Returns 1 if adjustment allowed
176 -- Returns 0 if adjustment not allowed
177 -- In the calling API, FA asset cannot be adjusted then raise the error : PA_FA_ASSET_RETIRED
178 -----------------------------------------------------------------------------------
179 Function Is_Asset_Adj_Allowed(P_Fa_Asset_Id in Number,
180 P_Book_Type_Code in Varchar2)
181 Return Number Is
182
183 l_Adj_allowed Number;
184
185 Begin
186
187 l_Adj_allowed := fa_mass_add_validate.can_add_to_asset(
188 x_asset_id => P_Fa_Asset_Id,
189 x_book_type_code => P_Book_Type_Code);
190
191 Return l_Adj_allowed;
192
193 Exception
194 When Others Then
195 Raise;
196
197 End Is_Asset_Adj_Allowed;
198
199 -----------------------------------------------------------------------------------
200 -- FUNCTION
201 -- Tag_Number_Exists
202 -- PURPOSE
203 -- Check if the tag_number exists in FA or PA
204 -- This function called from both the Projects/Capital Projects form
205 -- and AMG
209 Return Varchar2 Is
206 -- In the calling API, If tag number exists then raise the error : PA_ASSET_TAG_EXISTS
207 -----------------------------------------------------------------------------------
208 Function Tag_Number_Exists(P_Tag_Number in Varchar2)
210
211 CURSOR c_tag_exists (p_tag_number in varchar2) is
212 select 'Y' from dual
213 where exists (select tag_number
214 from fa_additions
215 where tag_number = p_tag_number)
216 or exists (select tag_number
217 from pa_project_assets_all
218 where tag_number = p_tag_number);
219
220 L_Tag_Exists Varchar2(1) := 'N';
221
222 Begin
223
224 OPEN C_TAG_EXISTS(P_TAG_NUMBER);
225 FETCH C_TAG_EXISTS INTO L_TAG_EXISTS;
226 IF (C_TAG_EXISTS%FOUND) THEN
227 Return L_Tag_Exists;
228 END IF;
229 CLOSE C_TAG_EXISTS;
230
231 Return L_Tag_Exists;
232
233 Exception
234 When Others Then
235 Return 'N';
236 End Tag_Number_Exists;
237
238 -----------------------------------------------------------------------------------
239 -- FUNCTION
240 -- Allow_AssetType_Change
241 -- PURPOSE
242 -- Given From and To Asset Types, this function will return
243 -- whether the change is allowed ('Y') or not ('N')
244 -- This function is called from both the Projects/Capital Projects form
245 -- and AMG
246 -- In the calling API, If type change not allowed then raise the error : PA_TYPE_CHG_NOTALLOWED
247 -----------------------------------------------------------------------------------
248 /* For the given From and To Asset Types, this function will return
249 whether the change is allowed ('Y') or not ('N')
250
251 Combinations:
252 From To Allowed
253 ------- ------ ---------
254 EST ASB Yes
255 EST RET Perform validations [And make sure dependent fields are enabled/disabled]
256 ASB EST Perform validations [Note: Its ok to leave the DPIS as is]
257 ASB RET No [Since DPIS need not be Date Retired]
258 RET EST Perform validations
259 [User could have mistakenly saved the asset as RET,
260 in which case we should allow them to change
261 And make sure dependent fields are enabled/disabled]
262 RET ASB Perform validations [And make sure dependent fields are enabled/disabled]
263
264 Validations are as follows:
265 Do not allow type change if:
266 1. Capitalized
267 2. Not Capitalized but Asset Lines Exist
268 3. If asset is associated with an event (not -1) or null (probable candidate to be associated)
269
270 */
271 Function Allow_AssetType_Change(P_From_Asset_Type in Varchar2,
272 P_To_Asset_Type in Varchar2,
273 P_Project_Asset_Id in Number,
274 P_Capitalized_Flag in Varchar2,
275 P_Capital_Event_Id in Number
276 ) Return Varchar2
277 Is
278
279 Cursor C_Asset_Lines_Exist(P_Asset_Id in Number) Is
280 Select 1
281 From Dual
282 Where Exists (select project_asset_id
283 from pa_project_asset_lines_all
284 where project_asset_id = P_Asset_Id);
285
286 L_Exists Number;
287
288 Begin
289
290
291 If P_From_Asset_Type = 'AS-BUILT'
292 and
293 P_To_Asset_Type = 'RETIREMENT_ADJUSTMENT' Then
294 Return 'N';
295 End If;
296
297 If P_From_Asset_Type = 'ESTIMATED'
298 and
299 P_To_Asset_Type = 'AS-BUILT'
300 Then
301 Return 'Y';
302 End If;
303
304 If P_Capitalized_Flag = 'Y' Then
305 Return 'N';
306 Else
307 Open C_Asset_Lines_Exist(P_Project_Asset_Id);
308 Fetch C_Asset_Lines_Exist INTO L_Exists;
309 If (C_Asset_Lines_Exist%FOUND) THEN
310 Return 'N';
311 End If;
312 Close C_Asset_Lines_Exist;
313
314 End If;
315
316 If P_Capital_Event_Id > 0 Then
317 Return 'N';
318 End If;
319
320 Return 'Y'; --All validations passed
321
322 Exception
323 When Others Then
324 Return 'N';
325 End Allow_AssetType_Change;
326
327 -----------------------------------------------------------------------------------
328 -- PROCEDURE
329 -- get_depreciation_expense_ccid
330 -- PURPOSE
331 -- This procedure is called when complete asset definition is required
332 -- to derive/validate the Depreciation Expense CCID.
333 -- This calls the client extension override API. If overwritten by the extension
334 -- then validate if the CCID is a valid expense account.
335 -- If not an expense account then return the original CCID, else return the
336 -- CCID overwritten/derived in the client extension.
337 -- In the calling API, If returned CCID is still NULL
338 -- then raise standard error : PA_CP_COMPLETE_ASSET_DEF
339 -----------------------------------------------------------------------------------
340 PROCEDURE get_depreciation_expense_ccid
344 P_date_placed_in_service IN DATE,
341 (P_project_asset_id IN NUMBER,
342 P_book_type_code IN VARCHAR2,
343 P_asset_category_id IN NUMBER,
345 P_Deprn_expense_CCID IN NUMBER,
346 X_Deprn_expense_CCID OUT NOCOPY NUMBER,
347 X_Error_Message_Code OUT NOCOPY VARCHAR2
348 ) IS
349
350 l_deprn_expense_ccid NUMBER;
351
352 --Used to determine if the Depreciation Expense CCID is valid for the current COA
353 CURSOR deprn_expense_cur IS
354 SELECT 1
355 FROM gl_code_combinations gcc,
356 gl_sets_of_books gsob,
357 pa_implementations pi
358 WHERE gcc.code_combination_id = l_deprn_expense_ccid
359 AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
360 AND gsob.set_of_books_id = pi.set_of_books_id
361 AND gcc.account_type = 'E';
362
363 l_deprn_expense NUMBER;
364
365 BEGIN
366
367 l_deprn_expense_ccid := PA_CLIENT_EXTN_DEPRN_EXP_OVR.DEPRN_EXPENSE_ACCT_OVERRIDE
368 (p_project_asset_id => p_project_asset_id,
369 p_book_type_code => p_book_type_code,
370 p_asset_category_id => p_asset_category_id,
371 p_date_placed_in_service => p_date_placed_in_service,
372 p_deprn_expense_acct_ccid => p_deprn_expense_ccid);
373
374 IF NVL(p_deprn_expense_ccid,-999) <> NVL(l_deprn_expense_ccid,-999) THEN
375
376 --Return NULL if the client extension has returned a NULL CCID
377 IF l_deprn_expense_ccid IS NULL THEN
378 X_Deprn_expense_CCID := l_deprn_expense_ccid;
379 RETURN;
380 END IF;
381
382 --Validate the new ccid against the current Set of Books
383 OPEN deprn_expense_cur;
384 FETCH deprn_expense_cur INTO l_deprn_expense;
385
386 IF deprn_expense_cur%NOTFOUND THEN
387 --Value returned by client extension is invalid, return original CCID
388 x_deprn_expense_ccid := p_deprn_expense_ccid;
389 ELSE
390 --Value is valid, return new CCID
391 x_deprn_expense_ccid := l_deprn_expense_ccid;
392 END IF;
393
394 CLOSE deprn_expense_cur;
395
396 ELSE
397
398 x_deprn_expense_ccid := p_deprn_expense_ccid;
399
400 END IF;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 x_error_message_code := SQLCODE;
405 RAISE;
406 END get_depreciation_expense_ccid;
407
408 -----------------------------------------------------------------------------------
409 -- FUNCTION
410 -- Can_Delete_Event
411 -- PURPOSE
412 -- This function checks for the given project and capital event
413 -- if there exists any event assets or event costs
414 -----------------------------------------------------------------------------------
415
416 FUNCTION Can_Delete_Event(P_Project_Id in NUMBER,
417 P_Capital_Event_Id in NUMBER) RETURN VARCHAR2 Is
418
419 l_Exist Varchar2(1) := 'N';
420
421 Cursor EventAssetExist Is
422 Select 'Y'
423 From Dual
424 Where Exists (Select 1 From Pa_Project_Assets_All
425 Where ProjecT_Id = P_Project_Id
426 And Capital_Event_Id = P_Capital_Event_Id);
427
428 Cursor EventCostExist Is
429 Select 'Y'
430 From Dual
431 Where Exists (Select 1 From Pa_Expenditure_Items_All
432 Where ProjecT_Id = P_Project_Id
433 And Capital_Event_Id = P_Capital_Event_Id);
434
435 Begin
436
437 Open EventAssetExist;
438 Fetch EventAssetExist Into l_Exist;
439 Close EventAssetExist;
440
441 If l_Exist = 'N' Then
442 Open EventCostExist;
443 Fetch EventCostExist Into l_Exist;
444 Close EventCostExist;
445 End If;
446
447 If l_Exist = 'Y' Then
448 Return 'N';
449 Else
450 Return 'Y';
451 End If;
452
453 Exception
454 When Others Then
455 Return 'N';
456 End Can_Delete_Event;
457
458 Function IsValidExpCCID(P_CCID IN NUMBER) RETURN VARCHAR2
459 Is
460 l_Found BOOLEAN := FALSE;
461 X_CCID_VALID VARCHAR2(1);
462 Begin
463 -- Check if there are any records in the pl/sql table.
464 If G_CCID_Tab.COUNT > 0 Then
465 --Dbms_Output.Put_Line('count > 0');
466
467 Begin
468 X_CCID_VALID := G_CCID_Tab(P_CCID);
469 l_Found := TRUE;
470 --Dbms_Output.Put_Line('l_found TRUE');
471 Exception
472 When No_Data_Found Then
473 l_Found := FALSE;
474 When Others Then
475 Raise;
476 End;
477
478 End If;
479
480 If Not l_Found Then
481 --Dbms_Output.Put_Line('l_found FALSE');
482
483 If G_CCID_Tab.COUNT > 199 Then
484 --Dbms_Output.Put_Line('count > 199');
485 G_CCID_Tab.Delete;
486 End If;
487
488 Begin
489 --Dbms_Output.Put_Line('select');
490 SELECT 'Y'
491 INTO X_CCID_VALID
492 FROM gl_code_combinations gcc,
493 gl_sets_of_books gsob,
494 pa_implementations pi
495 WHERE gcc.code_combination_id = p_ccid
496 AND gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
497 AND gsob.set_of_books_id = pi.set_of_books_id
498 AND gcc.account_type = 'E';
499
500 G_CCID_Tab(P_CCID) := X_CCID_VALID;
501 --Dbms_Output.Put_Line('after select');
502 Exception
503 When No_Data_Found Then
504 --Dbms_Output.Put_Line('wndf ');
505 X_CCID_VALID := 'N';
506 G_CCID_Tab(P_CCID) := 'N';
507 End;
508
509 End If;
510
511 Return X_CCID_VALID;
512
513 Exception
514 When Others Then
515 Raise;
516
517 End IsValidExpCCID;
518
519 END PA_CAPITAL_PROJECT_UTILS;
[Home] [Help]