1 PACKAGE BODY FA_CHK_BOOKSTS_PKG as
2 /* $Header: FAXCKBKB.pls 120.20.12010000.1 2008/07/28 13:19:38 appldev ship $ */
3
4 --
5 -- FUNCTION faxcbsx
6 --
7
8
9 FUNCTION faxcbsx(X_book IN VARCHAR2,
10 X_init_message_flag VARCHAR2 DEFAULT 'NO',
11 X_close_period in NUMBER DEFAULT 1,
12 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
13 return BOOLEAN is
14
15 h_txn_status BOOLEAN := FALSE; -- TRUE if txn allowed, FALSE otherwise
16
17 BEGIN
18
19 if (X_init_message_flag = 'YES') then
20 fa_srvr_msg.init_server_message;
21 fa_debug_pkg.initialize;
22 end if;
23
24 if (X_book is NULL) then
25 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcbsx',
26 name => 'FA_SHARED_ARGUMENTS',
27 token1 => 'PROGRAM',
28 value1 => 'CHECK_BOOK_STATUS',
29 p_log_level_rec => p_log_level_rec);
30 h_txn_status := FALSE;
31
32 -- faxcbs will check status of any processes running on this book
33
34 elsif (NOT faxcbs(X_book => X_book,
35 X_submit => TRUE,
36 X_start => FALSE,
37 X_asset_id => 0,
38 X_trx_type => 'OTHER',
39 X_txn_status => h_txn_status,
40 X_close_period => X_close_period,
41 p_log_level_rec => p_log_level_rec)) then
42
43 fa_srvr_msg.add_message(calling_fn =>'FA_CHK_BOOKSTS_PKG.faxcbsx',
44 name => 'FA_SHARED_END_WITH_ERROR',
45 token1 => 'PROGRAM',value1=>'CHECK_BOOK_STATUS',
46 p_log_level_rec => p_log_level_rec);
47 h_txn_status := FALSE;
48
49 end if;
50
51 -- h_txn_status, at this point, have value of TRUE if txn is approved
52 -- and FALSE if txn is not approved
53
54 if (p_log_level_rec.statement_level) then
55 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcbsx','txn status',h_txn_status, p_log_level_rec);
56 end if;
57
58 return(h_txn_status);
59
60 EXCEPTION
61 when others then
62
63 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcbsx',
64 p_log_level_rec => p_log_level_rec);
65 return (FALSE);
66
67 END faxcbsx;
68
69 --
70 -- FUNCTION faxcrb
71 --
72 -- This function has been added to handle MRC enabled books.
73 -- This function will check if the book on which a transaction is being
74 -- performed is a primary book with reporting books associated to it(MRC).
75 -- It will check to see if the reporting books depreciation status is complete
76 -- and whether the primary and reporting book periods are in synch.
77 -- When depreciation is submitted, this function will check to see if
78 -- depreciation has already been run for the reporting books and will return
79 -- false if this is not the case. When depreciation is submitted for a
80 -- reporting book, we will check to see if depreciation has already been run
81 -- for the period in which the Primary books is and will return false if
82 -- this is the case.
83
84 -- BUG# 1470923
85 -- removed the tax cursor and surrounding logic. instead, have
86 -- added a call to faxcrb from faxptb so it is called for each tax book
87 -- as it looped through
88
89 -- BUG# 1920416
90 -- modified to simulate the full logic of transaction approval including
91 -- mass requests, close period option for deprn, etc.
92
93 FUNCTION faxcrb (X_book IN VARCHAR2,
94 X_trx_type IN VARCHAR2,
95 X_asset_id IN NUMBER,
96 X_close_period IN NUMBER,
97 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
98
99 return BOOLEAN is
100
101 h_mc_source_flag varchar2(3);
102 h_last_period_counter NUMBER(15);
103 h_approve BOOLEAN := TRUE;
104 h_book VARCHAR2(15);
105 h_count NUMBER(15) := 0;
106 h_mrc_sob_type_code VARCHAR2(1);
107 h_set_of_books_id NUMBER(15);
108 conversion_exception exception;
109 efc_conversion_exception exception;
110
111
112 -- used for any
113 CURSOR check_efc_conversion_status IS
114 select count(*)
115 from fa_mc_conversion_history
116 where efc_status in ('I', 'U', 'E')
117 and book_type_code = h_book;
118
119 -- used for primary
120 -- Bug 4748124
121 -- Added an nvl to conversion_status to catch
122 -- the case when conversion_status is null
123
124 CURSOR check_conversion_status IS
125 select count(*)
126 from fa_mc_book_controls
127 where book_type_code = h_book
128 and nvl(conversion_status,' ') in ('S', 'R', 'E',' ')
129 and mrc_converted_flag = 'N'
130 and enabled_flag = 'Y';
131
132 CURSOR check_reporting_deprn_open IS
133 select count(1)
134 from fa_mc_book_controls mcbk
135 where mcbk.book_type_code = h_book
136 and (mcbk.deprn_status <> 'C' OR
137 ((mcbk.last_period_counter <> h_last_period_counter + 1) AND
138 (mcbk.last_period_counter <> h_last_period_counter)))
139 and enabled_flag = 'Y';
140
141 CURSOR check_reporting_deprn_close IS
142 select count(1)
143 from fa_mc_book_controls mcbk
144 where mcbk.book_type_code = h_book
145 and (mcbk.deprn_status <> 'C' OR
146 (mcbk.last_period_counter <> h_last_period_counter + 1))
147 and enabled_flag = 'Y';
148
149 CURSOR check_reporting_mass IS
150 select count(1)
151 from fa_mc_book_controls mcbk
152 where mcbk.book_type_code = h_book
153 and (mcbk.deprn_status <> 'C' OR
154 (mcbk.last_period_counter <> h_last_period_counter) OR
155 (mcbk.mass_request_id is not null))
156 and enabled_flag = 'Y';
157
158 -- just like faxcds, asset transactions should not necessarily be prevented
159 -- in cases where deprn_status is E as we'll check whether they were
160 -- processed in faxcdr.
161
162 CURSOR check_reporting_single IS
163 select count(1)
164 from fa_mc_book_controls mcbk
165 where mcbk.book_type_code = h_book
166 and (mcbk.deprn_status not in ('C', 'E') OR
167 (mcbk.last_period_counter <> h_last_period_counter) OR
168 (mcbk.mass_request_id is not null))
169 and enabled_flag = 'Y';
170
171
172 -- used when called from a reporting book
173 CURSOR check_primary_sync IS
174 select count(1)
175 from fa_book_controls_bas bc
176 where bc.book_type_code = X_book
177 and (bc.last_period_counter <> h_last_period_counter or
178 bc.mass_request_id is not null);
179
180 BEGIN
181
182 h_mc_source_flag := FA_CACHE_PKG.fazcbc_record.mc_source_flag;
183 h_last_period_counter := FA_CACHE_PKG.fazcbc_record.last_period_counter;
184 h_set_of_books_id := FA_CACHE_PKG.fazcbc_record.set_of_books_id;
185
186 if not fa_cache_pkg.fazcsob
187 (X_set_of_books_id => h_set_of_books_id,
188 X_mrc_sob_type_code => h_mrc_sob_type_code,
189 p_log_level_rec => p_log_level_rec) then
190 fa_srvr_msg.add_sql_error
191 (calling_fn => 'fa_chk_booksts_pkg.faxcrb', p_log_level_rec => p_log_level_rec);
192 return(FALSE);
193 end if;
194
195 if (p_log_level_rec.statement_level) then
196 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcrb',
197 'sob_id',h_set_of_books_id, p_log_level_rec);
198 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcrb',
199 'reporting_type',h_mrc_sob_type_code, p_log_level_rec);
200 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcrb',
201 'last period counter',h_last_period_counter, p_log_level_rec);
202 end if;
203
204 -- check to see if the efc conversion is in process
205 open check_efc_conversion_status;
206 fetch check_efc_conversion_status into h_count;
207 close check_efc_conversion_status;
208
209 if (h_count > 0) then
210 RAISE efc_conversion_exception;
211 end if;
212
213 h_book := X_book;
214
215 if (h_mrc_sob_type_code = 'P' and h_mc_source_flag = 'Y') then
216
217 if (p_log_level_rec.statement_level) then
218 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcrb','checking for ','Primary', p_log_level_rec);
219 end if;
220
221 -- check to see if mrc upgrade is in process for a reporting
222 -- book associated to this primary book
223 open check_conversion_status;
224 fetch check_conversion_status into h_count;
225 close check_conversion_status;
226
227 if (h_count > 0) then
228 RAISE conversion_exception;
229 end if;
230
231 if (X_trx_type = 'OTHER') then
232 -- txn type of OTHER is used when submitting depreciation from
233 -- FAXDPRUN, branch depending on close period flag
234 if (X_close_period = 1) then
235 open check_reporting_deprn_close;
236 fetch check_reporting_deprn_close into h_count;
237 if (h_count > 0) then
238 h_approve := FALSE;
239 FA_SRVR_MSG.add_message
240 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
241 NAME => 'FA_MRC_REP_BOOK_DEP_NOT_RUN',
242 p_log_level_rec => p_log_level_rec);
243 end if;
244 close check_reporting_deprn_close;
245 else
246 open check_reporting_deprn_open;
247 fetch check_reporting_deprn_open into h_count;
248 if (h_count > 0) then
249 h_approve := FALSE;
250 FA_SRVR_MSG.add_message
251 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
252 NAME => 'FA_MRC_REP_BOOK_DEP_NOT_RUN',
253 p_log_level_rec => p_log_level_rec);
254 end if;
255 close check_reporting_deprn_open;
256 end if;
257 elsif (X_trx_type = 'RB_DEP' or
258 X_trx_type = 'RB_CJE' or
259 X_trx_type = 'GAINLOSS') then
260 -- no need to check the reporting book here as they do not
261 -- impact the reporting books due to one-step, etc and no
262 -- other mass process on reporting would be relevant.
263 null;
264 elsif (X_asset_ID = 0) then -- for any mass transaction
265 open check_reporting_mass;
266 fetch check_reporting_mass into h_count;
267 if (h_count > 0) then
268 FA_SRVR_MSG.add_message
269 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
270 NAME => 'FA_MRC_PRI_REP_PERIOD_DIFF',
271 p_log_level_rec => p_log_level_rec);
272 h_approve := FALSE;
273 end if;
274 close check_reporting_mass;
275 else -- for any asset transaction
276 open check_reporting_single;
277 fetch check_reporting_single into h_count;
278 if (h_count > 0) then
279 FA_SRVR_MSG.add_message
280 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
281 NAME => 'FA_MRC_PRI_REP_PERIOD_DIFF',
282 p_log_level_rec => p_log_level_rec);
283 h_approve := FALSE;
284 end if;
285 close check_reporting_single;
286 end if;
287
288 -- else submitting mass process on reporting book (dep, gl, rbdep)
289 -- prevent if depreciation for prior period has not run for Primary
290 -- or if a mass request is locking the primary book
291
292 elsif (h_mrc_sob_type_code = 'R' and X_trx_type <> 'RB_DEP') then
293 if (p_log_level_rec.statement_level) then
294 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcrb','checking','reporting', p_log_level_rec);
295 end if;
296
297 open check_primary_sync;
298 fetch check_primary_sync into h_count;
299 if (h_count > 0) then
300 FA_SRVR_MSG.add_message
301 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
302 NAME => 'FA_MRC_RUN_DEP_CORP_FIRST',
303 p_log_level_rec => p_log_level_rec);
304 h_approve := FALSE;
305 end if;
306 close check_primary_sync;
307 end if; --h_mc_source_flag
308
309 RETURN(h_approve);
310
311 EXCEPTION
312 WHEN conversion_exception THEN
313 FA_SRVR_MSG.add_message
314 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
315 NAME => 'FA_MRC_CONV_PROCESS',
316 p_log_level_rec => p_log_level_rec);
317 return(FALSE);
318
319 WHEN efc_conversion_exception THEN
320 FA_SRVR_MSG.add_message
321 (CALLING_FN => 'FA_CHK_BOOKSTS_PKG.faxcrb',
322 NAME => 'FA_EFC_CONV_PROCESS',
323 p_log_level_rec => p_log_level_rec);
324 return(FALSE);
325
326 WHEN OTHERS THEN
327 fa_srvr_msg.add_sql_error(calling_fn => 'fa_chk_booksts_pkg.faxcrb', p_log_level_rec => p_log_level_rec);
328 return(FALSE);
329
330 END faxcrb;
331
332
333 --
334 -- FUNCTION faxcbs
335 --
336
337 FUNCTION faxcbs (X_book IN VARCHAR2,
338 X_submit IN BOOLEAN,
339 X_start IN BOOLEAN,
340 X_asset_id IN NUMBER,
341 X_trx_type IN VARCHAR2,
342 X_txn_status IN OUT NOCOPY BOOLEAN,
343 X_close_period IN NUMBER DEFAULT 1,
344 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
345 return BOOLEAN is
346
347
348 h_class_str VARCHAR2(15);
349 h_allow_cip_assets_flag VARCHAR2(3);
350 h_tmp BOOLEAN;
351
352 ERROR_FOUND EXCEPTION;
353
354 BEGIN
355
356 -- Note: X_txn_status is a boolean variable which is an argument
357 -- to several functions and gets updated to TRUE if no error occured or
358 -- no process is running on the book, etc.
359 --
360 -- It gets set to FALSE if non-complete process is running on the book,
361 -- fail to obtain lock for the book, or unknown error occurs, etc
362
363 if (NOT FA_CACHE_PKG.fazcbc(X_book => X_book, p_log_level_rec => p_log_level_rec)) then
364 raise ERROR_FOUND;
365 end if;
366
367 h_class_str := fa_cache_pkg.fazcbc_record.book_class;
368 h_allow_cip_assets_flag := fa_cache_pkg.fazcbc_record.allow_cip_assets_flag;
369
370 -- set savepoint, so that we can rollback when error occurs
371
372 if (NOT faxsav(X_action => 'S',
373 X_txn_status => X_txn_status,
374 p_log_level_rec => p_log_level_rec)) then
375 raise ERROR_FOUND;
376 end if;
377
378 -- lock the book row before checking for txn approval
379
380 if (NOT faxlck(X_book => X_book,
381 X_txn_status => X_txn_status,
382 X_asset_id => X_asset_id,
383 X_trx_type => X_trx_type,
384 p_log_level_rec => p_log_level_rec)) then
385 raise ERROR_FOUND;
386 end if;
387
388 if (p_log_level_rec.statement_level) then
389 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcbs','txn_status after faxlck',X_txn_status, p_log_level_rec);
390 end if;
391
392 -- X_txn_status is TRUE if nothing failed so far and check any
393 -- processes on this book
394
395 if (X_txn_status) then
396 if (NOT faxcps(X_book => X_book,
397 X_submit => X_submit,
398 X_start => X_start,
399 X_asset_id => X_asset_id,
400 X_trx_type => X_trx_type,
401 X_txn_status => X_txn_status,
402 X_close_period => X_Close_Period,
403 p_log_level_rec => p_log_level_rec)) then
404 raise ERROR_FOUND;
405 end if;
406 end if;
407
408 -- faxcps above returns X_txn_status false when process is in running, inactive,
409 -- or pending status
410
411 if (p_log_level_rec.statement_level) then
412 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcbs','txn status after faxcps',
413 X_txn_status, p_log_level_rec);
414 end if;
415 if (X_txn_status) then
416
417 if (h_class_str = 'CORPORATE') then
418
419 if (NOT X_submit) then
420
421 -- not submitting depreciation, check linked tax books if
422 -- reclass or transfer. Original code has been merged with
423 -- CIP-TAX code below for easier maintenance
424
425 -- cip-in-trx's have been removed since the trigger
426 -- solution has been replaced with a full api which
427 -- insures transaction approval is called for each book
428
429 if (X_trx_type = 'TRANSFER' OR
430 X_trx_type = 'RECLASS' OR
431 X_trx_type = 'TRANSFER OUT' OR
432 X_trx_type = 'UNIT ADJUSTMENT' OR
433 X_trx_type = 'PARTIAL UNIT RETIREMENT') then
434
435 if (NOT faxptb(X_book => X_book,
436 X_start => X_start,
437 X_asset_id => X_asset_id,
438 X_trx_type => X_trx_type,
439 X_txn_status => X_txn_status,
440 p_log_level_rec => p_log_level_rec)) then
441 raise ERROR_FOUND;
442 end if;
443 end if;
444 end if;
445
446 elsif (h_class_str = 'TAX') then
447
448 -- check for incompatable processes on CORP book
449 --
450 -- BUG# 1936983 - do not check for rollback deprn
451 -- since no conflicting corp transaction would have
452 -- been allowed if deprn had been run withut closing
453 -- period. Eventually we may need to enhance the
454 -- faxgcb function to accomidate other transactions
455 -- against mrc but currently this is the only one.
456
457 -- Fix for Bug #2381635. Added RB_CJE
458 if (X_trx_type not in ('RB_DEP', 'RB_CJE')) then
459
460 if (NOT faxgcb(X_book => X_book,
461 X_txn_status => X_txn_status,
462 p_log_level_rec => p_log_level_rec)) then
463 raise ERROR_FOUND;
464 end if;
465 end if;
466
467 else
468 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS.faxcbs',
469 name => 'FA_TRXAPP_WRONG_BOOK_CLASS',
470 token1 =>'CLASS',value1=>h_class_str,
471 p_log_level_rec => p_log_level_rec);
472
473 h_tmp := faxsav(X_action => 'R',
474 X_txn_status => X_txn_status,
475 p_log_level_rec => p_log_level_rec); -- rollback the lock
476 end if;
477 end if;
478
479 -- put the main book back on cache
480 if (NOT FA_CACHE_PKG.fazcbc(X_book => X_book,
481 p_log_level_rec => p_log_level_rec)) then
482 raise ERROR_FOUND;
483 end if;
484
485 -- check book status is complete and clear savepoint indicator for next txns,
486 -- but keep rows locked
487
488 if (NOT faxsav(X_action => 'C',
489 X_txn_status => X_txn_status,
490 p_log_level_rec => p_log_level_rec)) then
491 raise ERROR_FOUND;
492 end if;
493
494 return(TRUE);
495
496 EXCEPTION
497 WHEN ERROR_FOUND THEN
498 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS.faxcbs',
499 p_log_level_rec => p_log_level_rec);
500
501 h_tmp := faxsav(X_action => 'R',
502 X_txn_status => X_txn_status,
503 p_log_level_rec => p_log_level_rec);
504 -- rollback the lock, if any
505 return (FALSE);
506
507 WHEN OTHERS THEN
508 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS.faxcbs', p_log_level_rec => p_log_level_rec);
509 h_tmp := faxsav(X_action => 'R',
510 X_txn_status => X_txn_status,
511 p_log_level_rec => p_log_level_rec);
512 -- rollback the lock, if any
513
514 return(FALSE);
515 END faxcbs;
516
517 --
518 -- FUNTION faxcps
519 --
520
521 FUNCTION faxcps (X_book IN VARCHAR2,
522 X_submit IN BOOLEAN,
523 X_start IN BOOLEAN,
524 X_asset_id IN NUMBER,
525 X_trx_type IN VARCHAR2,
526 X_txn_status IN OUT NOCOPY BOOLEAN,
527 X_close_period IN NUMBER,
528 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
529 return BOOLEAN is
530
531 h_conc_request_id NUMBER := fnd_global.conc_request_id;
532 h_request_id NUMBER := 0; -- set to non-zero if there is non-complete running process
533 h_ca_request_id NUMBER := 0; -- set to non-zero if there is non-complete running create accounting process
534 h_tmp BOOLEAN;
535
536 h_deprn_stat varchar2(1);
537 h_deprn_req_id number;
538 h_mass_req_id number;
539 h_last_period_ctr number;
540 h_sob_id number;
541 h_mc_source_flag varchar2(1);
542 h_allow_cip_assets varchar2(3);
543
544 BEGIN
545
546 -- BUG# 1910467
547 --
548 -- We are reanabling the caching features in fazcbc which
549 -- will impact multiple places. Do to potential changes in values
550 -- like last_period_counter, we need to insure the data in the cache
551 -- is not stale. Thus, we will do a direct select here and compare
552 -- the values. If the data has changed, we will clear and reset
553 -- the cache accordingly.
554 --
555 -- caches have already been called from faxcbs or faxptb
556 -- also adding the new deprn period cache to this as well
557 --
558 -- -- bridgway 08/01/01
559
560 if not fa_cache_pkg.fazcdp
561 (x_book_type_code => x_book,
562 p_log_level_rec => p_log_level_rec) then
563 return false;
564 end if;
565
566 -- BUG# 2247404 / BUG# 2230178
567 -- do the following only when called standalone for an individual trx
568 -- or for a mass transaction at main entry from that process
569 --
570 -- do not do it for each transaction called from a mass process
571 -- this will allow mass transactions like FAMAPT after a deprn run
572 -- on a line by line basis
573
574 if (((nvl(fnd_global.conc_request_id, -1) >= 0) and
575 (X_asset_id = 0)) or
576 ((nvl(fnd_global.conc_request_id, -1) < 0) and
577 (X_asset_id <> 0))) then
578
579 select deprn_status,
580 deprn_request_id,
581 last_period_counter,
582 mass_request_id,
583 set_of_books_id,
584 mc_source_flag,
585 allow_cip_assets_flag
586 into h_deprn_stat,
587 h_deprn_req_id,
588 h_last_period_ctr,
589 h_mass_req_id,
590 h_sob_id,
591 h_mc_source_flag,
592 h_allow_cip_assets
593 from fa_book_controls
594 where book_type_code = X_book;
595
596 if (nvl(h_last_period_ctr, -99) <>
597 nvl(FA_CACHE_PKG.fazcbc_record.last_period_counter, -99) OR
598 nvl(h_deprn_stat, 'X') <>
599 nvl(FA_CACHE_PKG.fazcbc_record.deprn_status, 'X') OR
600 nvl(h_deprn_req_id, -99) <>
601 nvl(FA_CACHE_PKG.fazcbc_record.deprn_request_id, -99) OR
602 nvl(h_mass_req_id, -99) <>
603 nvl(FA_CACHE_PKG.fazcbc_record.mass_request_id, -99) OR
604 nvl(h_sob_id, -99) <>
605 nvl(FA_CACHE_PKG.fazcbc_record.set_of_books_id, -99) OR
606 nvl(h_mc_source_flag, 'X') <>
607 nvl(FA_CACHE_PKG.fazcbc_record.mc_source_flag, 'X') OR
608 nvl(h_allow_cip_assets, 'X') <>
609 nvl(FA_CACHE_PKG.fazcbc_record.allow_cip_assets_flag, 'X')) then
610
611 -- clear the book from the cache (member and array)
612 if (NOT FA_CACHE_PKG.fazcbc_clr(X_book => X_book, p_log_level_rec => p_log_level_rec)) then
613 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxcps', p_log_level_rec => p_log_level_rec);
614 return(FALSE);
615 end if;
616
617 -- now recall it
618 if (NOT FA_CACHE_PKG.fazcbc(X_book => X_book, p_log_level_rec => p_log_level_rec)) then
619 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxcps', p_log_level_rec => p_log_level_rec);
620 return(FALSE);
621 end if;
622 end if;
623
624 if (FA_CACHE_PKG.fazcbc_record.last_period_counter + 1 <>
625 FA_CACHE_PKG.fazcdp_record.period_counter) then
626
627 -- clear the book from the cache (member and array)
628 if (NOT FA_CACHE_PKG.fazcdp_clr(X_book => X_book, p_log_level_rec => p_log_level_rec)) then
629 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxcps', p_log_level_rec => p_log_level_rec);
630 return(FALSE);
631 end if;
632
633 -- now recall it
634 if (NOT FA_CACHE_PKG.fazcdp(X_book_type_code => X_book, p_log_level_rec => p_log_level_rec)) then
635 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxcps', p_log_level_rec => p_log_level_rec);
636 return(FALSE);
637 end if;
638 end if;
639
640 if (NOT faxcrb(X_book => X_book,
641 X_trx_type => X_trx_type,
642 X_asset_id => X_asset_id,
643 X_close_period => X_close_period,
644 p_log_level_rec => p_log_level_rec)) then
645 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
646 p_log_level_rec => p_log_level_rec);
647 return(FALSE);
648 end if;
649 end if;
650
651 -- check if depreciation is running or errored
652 if (NOT faxcds(X_book => X_book,
653 X_submit => X_submit,
654 X_asset_id => X_asset_id,
655 X_trx_type => X_trx_type,
656 X_txn_status => X_txn_status,
657 p_log_level_rec => p_log_level_rec)) then
658 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
659 p_log_level_rec => p_log_level_rec);
660 return(FALSE);
661 end if;
662
663 -- if trx_approval failed due to deprn running, return
664 if (NOT X_txn_status) then
665 return (TRUE);
666 end if;
667
668 -- check the status of mass request id, if any, in book_control
669 if(NOT faxcms(X_book => X_book,
670 X_request_id => h_request_id,
671 p_log_level_rec => p_log_level_rec)) then
672 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
673 p_log_level_rec => p_log_level_rec);
674 return(FALSE);
675 end if;
676
677 -- check if create accounting is running for cip reversals
678 -- deprn is done within faxcdr seperately
679 if x_trx_type in ('REVERSE') then
680
681 if(NOT faxcca(X_book => X_book,
682 X_request_id => h_ca_request_id,
683 p_log_level_rec => p_log_level_rec)) then
684 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
685 p_log_level_rec => p_log_level_rec);
686 return(FALSE);
687 end if;
688 else
689 h_ca_request_id := 0;
690 end if;
691
692 if (p_log_level_rec.statement_level) then
693 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcps','book',X_book, p_log_level_rec);
694 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcps','mass_request_id',
695 h_request_id, p_log_level_rec);
696 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcps','create_accounting_request_id',
697 h_ca_request_id, p_log_level_rec);
698 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcps','conc_request_id',
699 h_conc_request_id, p_log_level_rec);
700 end if;
701
702 if (h_request_id <> 0 ) then
703 -- req_id is in status of running,inactive,or pending
704 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
705 name => 'FA_TRXAPP_WAIT_REQUEST',
706 token1 => 'REQUEST_ID',
707 value1 => h_request_id,
708 token2 => 'BOOK',
709 value2 => X_book,
710 p_log_level_rec => p_log_level_rec);
711 h_tmp := faxsav(X_action => 'R',
712 X_txn_status => X_txn_status,
713 p_log_level_rec => p_log_level_rec);
714 --rollback the lock, if any
715 elsif (h_ca_request_id <> 0) then
716 -- req_id is in status of running,inactive,or pending
717 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
718 name => 'FA_TRXAPP_WAIT_REQUEST',
719 token1 => 'REQUEST_ID',
720 value1 => h_ca_request_id,
721 token2 => 'BOOK',
722 value2 => X_book,
723 p_log_level_rec => p_log_level_rec);
724 h_tmp := faxsav(X_action => 'R',
725 X_txn_status => X_txn_status,
726 p_log_level_rec => p_log_level_rec);
727 --rollback the lock, if any
728 end if;
729
730 if (X_start and (h_request_id <> 0 or h_ca_request_id <> 0)) then
731 -- for now faxwcr will always return TRUE
732 if (NOT faxwcr(X_request_id => h_request_id, p_log_level_rec => p_log_level_rec)) then
733 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
734 p_log_level_rec => p_log_level_rec);
735 return(FALSE);
736 end if;
737 end if;
738
739 return(TRUE);
740
741 EXCEPTION
742 WHEN OTHERS THEN
743 fa_srvr_msg.add_sql_error(calling_fn=>'FA_CHK_BOOKSTS_PKG.faxcps', p_log_level_rec => p_log_level_rec);
744 return(FALSE);
745
746 END faxcps;
747
748
749 --
750 -- FUNCTION faxwcr
751 --
752
753 FUNCTION faxwcr(X_request_id IN NUMBER,
754 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
755 return BOOLEAN is
756
757 BEGIN
758
759 -- funtion is currently disabled, will return TRUE immediately
760 return(TRUE);
761
762 -- The following is not converted and thus remains as C code
763
764 /*
765 * if (X_request_id <> 0) then
766 * select to_char(nvl(actual_completion_date,
767 * sysdate + least(greatest
768 * (sysdate - actual_start_date,5/1440),1/24)),
769 * 'DD/MM/YYYY HH24:MI:SS')
770 * into h_restart
771 * from fnd_concurrenct_requests
772 * where request_id = X_request_id
773 * end if;
774 */
775
776 -- Remaining code is in faxchk.lpc file
777
778 END faxwcr;
779
780 --
781 -- FUNCTION faxcds
782 --
783
784 FUNCTION faxcds(X_book IN VARCHAR2,
785 X_submit IN BOOLEAN,
786 X_asset_id IN NUMBER,
787 X_trx_type IN VARCHAR2,
788 X_txn_status IN OUT NOCOPY BOOLEAN,
789 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
790 return BOOLEAN is
791
792 h_deprn_stat varchar2(1);
793 h_deprn_req_id number;
794 h_req_id_sav number;
795 h_last_period_ctr number;
796 h_curr_period_ctr number;
797 h_tmp boolean;
798 h_error boolean := FALSE;
799 h_count number := 0;
800 h_adj_count number := 0;
801
802 h_phase varchar2(30);
803 h_status varchar2(30);
804 h_dev_phase varchar2(30);
805 h_dev_status varchar2(30);
806 h_message varchar2(240);
807
808 BEGIN
809
810 h_deprn_stat := FA_CACHE_PKG.fazcbc_record.deprn_status;
811 h_deprn_req_id := FA_CACHE_PKG.fazcbc_record.deprn_request_id;
812 h_last_period_ctr := FA_CACHE_PKG.fazcbc_record.last_period_counter;
813
814 if (h_deprn_stat = 'C') then -- status is completed, txn approved
815
816 -- BUG# 1924172
817 -- need to account for deprn running without closing period
818 -- for both mass and asset level transactions. faxcdr will
819 -- account for either mass or asset level transactions based
820 -- on asset_id passed below. -- bridgway
821
822 -- if submitting depreciation, deleteing an asset or rolling back
823 -- depreciation, ok to go ahead
824
825 if (X_submit or
826 X_trx_type = 'RB_DEP' or
827 X_trx_type = 'RB_CJE' or
828 X_trx_type = 'DELETE') then
829 return(TRUE);
830 end if;
831
832 if (NOT faxcdr(X_book => X_book,
833 X_asset_id => X_asset_id,
834 p_log_level_rec => p_log_level_rec)) then
835
836 h_tmp := faxsav(X_action => 'R',
837 X_txn_status => X_txn_status,
838 p_log_level_rec => p_log_level_rec); -- not approved
839 end if;
840 return(TRUE);
841
842 elsif (h_deprn_stat = 'S' or h_deprn_stat = 'R') then
843 -- stat is submitted or running
844 if (fnd_concurrent.get_request_status
845 (h_deprn_req_id,NULL,NULL,h_phase,
846 h_status,h_dev_phase,h_dev_status,h_message)) then
847 if (h_dev_phase = 'PENDING' OR
848 h_dev_phase = 'INACTIVE' OR
849 h_dev_phase = 'RUNNING') then
850
851 fa_srvr_msg.add_message(calling_fn =>'fa_chk_booksts_pkg.faxcds',
852 name => 'FA_TRXAPP_DEPRN_IS_RUNNING',
853 token1 =>'BOOK',
854 value1 =>X_book,
855 p_log_level_rec => p_log_level_rec);
856
857 h_tmp := faxsav(X_action => 'R',
858 X_txn_status => X_txn_status,
859 p_log_level_rec => p_log_level_rec);
860 -- rollback the lock,set X_txn_status FALSE
861
862 -- BUG# 1788850
863 -- this was previously breaking only on the dev_phase check
864 -- which is incorrect. It must only do this for "S' not "R"
865 -- as the request could have core dumped or been cancelled
866 --
867 -- bridgway 05/17/01
868
869 elsif ((h_dev_phase = 'COMPLETE') and
870 (h_deprn_stat = 'S') and
871 (not X_submit) and
872 (X_trx_type <> 'RB_CJE')) then
873 if (NOT faxcdr(X_book => X_book,
874 X_asset_id => X_asset_id,
875 p_log_level_rec => p_log_level_rec)) then
876
877 h_tmp := faxsav(X_action => 'R',
878 X_txn_status => X_txn_status,
879 p_log_level_rec => p_log_level_rec);
880 -- not approved
881 end if;
882 return (TRUE);
883 else
884 -- Deprn stat is R, but request failed. e.g core dumped,
885 -- treat it as error
886 h_error := TRUE;
887 end if;
888
889 else
890 fa_srvr_msg.add_message(calling_fn => 'fa_chk_booksts_pkg.faxcds',
891 name => 'FA_CONCURRENT_GET_STATUS',
892 p_log_level_rec => p_log_level_rec);
893 return(FALSE);
894 end if;
895 end if;
896
897 if (h_deprn_stat = 'E' or h_error = TRUE) then
898 -- if submitting depreciation or deleteing an asset ok to go ahead
899 if (X_submit or
900 X_trx_type = 'DELETE' or
901 X_trx_type = 'RB_CJE') then
902 return(TRUE);
903 end if;
904
905 if (X_asset_id = 0) then -- this is mass process request
906
907 -- This is a request for a mass process, no approval
908 -- because the last depreciation run failed.
909
910 fa_srvr_msg.add_message(calling_fn => 'fa_chk_booksts_pkg.faxcds',
911 name => 'FA_TRXAPP_DEPRN_FAILED',
912 token1 => 'BOOK',
913 value1 => X_book,
914 p_log_level_rec => p_log_level_rec);
915 h_tmp := faxsav(X_action => 'R',
916 X_txn_status => X_txn_status,
917 p_log_level_rec => p_log_level_rec); -- not approved
918 return(TRUE);
919 end if;
920
921 -- calling faxcdr so asset level transactions can be approved if
922 -- they have not been depreciated yet.
923 if (NOT faxcdr(X_book => X_book,
924 X_asset_id => X_asset_id,
925 p_log_level_rec => p_log_level_rec)) then
926 h_tmp := faxsav(X_action => 'R',
927 X_txn_status => X_txn_status,
928 p_log_level_rec => p_log_level_rec); -- not approved
929 end if;
930 return(TRUE);
931
932 else -- unknown status
933 fa_srvr_msg.add_message(calling_fn => 'fa_chk_booksts_pkg.faxcds',
934 name => 'FA_TRXAPP_UNKNOWN_STATUS',
935 token1 => 'STATUS',
936 value1 => h_deprn_stat,
937 p_log_level_rec => p_log_level_rec);
938 h_tmp := faxsav(X_action => 'R',
939 X_txn_status => X_txn_status,
940 p_log_level_rec => p_log_level_rec); -- not approved
941 return (TRUE);
942 end if;
943
944 EXCEPTION
945 when others then
946 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcds', p_log_level_rec => p_log_level_rec);
947 return(FALSE);
948 END faxcds;
949
950
951 --
952 -- FUNCTION faxcms
953 --
954
955 FUNCTION faxcms(X_book IN VARCHAR2,
956 X_request_id OUT NOCOPY NUMBER,
957 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
958 return BOOLEAN is
959
960 h_conc_req_id NUMBER;
961 h_parent_req_id NUMBER;
962 h_mass_req_id NUMBER;
963 h_phase VARCHAR2(200) := NULL;
964 h_status VARCHAR2(200) := NULL;
965 h_dev_phase VARCHAR2(200) := NULL;
966 h_dev_status VARCHAR2(200) := NULL;
967 h_message VARCHAR2(2000):= NULL;
968 h_count number;
969
970 cursor c_parent_request(p_request_id NUMBER) is
971 select parent_request_id
972 from fnd_concurrent_requests
973 where request_id = p_request_id;
974
975 BEGIN
976
977 X_request_id := 0;
978 h_mass_req_id := fa_cache_pkg.fazcbc_record.mass_request_id;
979 h_conc_req_id := fnd_global.conc_request_id;
980
981 if (h_mass_req_id IS NOT NULL) then
982
983 if (h_mass_req_id <> h_conc_req_id) then
984 -- account for the potential that this is a child
985 -- request by looking up parent_request_id
986 open c_parent_request (h_conc_req_id);
987 fetch c_parent_request into h_parent_req_id;
988
989 if (c_parent_request%FOUND) then
990 h_conc_req_id := nvl(h_parent_req_id, h_conc_req_id);
991 end if;
992 close c_parent_request;
993
994 if (h_mass_req_id <> h_conc_req_id) then
995 -- check for status of this mass request id
996 -- get the parent request if applicable
997
998 if (h_conc_req_id = 0 and
999 h_mass_req_id = 0) then
1000 -- this is a mass request from pro*c command line
1001 -- so allow the transaction. faxbmt will not allow
1002 -- another program to run in such a case, so we
1003 -- can be sure it's the same request. For pl/sql
1004 -- programs, it would be -1 from sql*plus
1005 X_request_id := 0;
1006
1007 elsif (fnd_concurrent.get_request_status
1008 (h_mass_req_id,NULL,NULL,h_phase,h_status,
1009 h_dev_phase, h_dev_status, h_message)) then
1010
1011 if (h_dev_phase = 'PENDING' OR
1012 h_dev_phase = 'INACTIVE' OR
1013 h_dev_phase = 'RUNNING') then
1014 X_request_id := h_mass_req_id;
1015 else -- completed request
1016 -- BUG# 5114320
1017 -- even if request was cancelled/terminated,
1018 -- make sure the db processes no longer exist
1019 select count(*)
1020 into h_count
1021 from v$session a
1022 where a.audsid in
1023 (select ORACLE_SESSION_ID
1024 from fnd_concurrent_requests
1025 where request_id = h_mass_req_id
1026 or parent_request_id = h_mass_req_id);
1027
1028 if (h_count = 0) then
1029 X_request_id := 0;
1030 else
1031 X_request_id := h_mass_req_id;
1032 end if;
1033 end if;
1034 else
1035 fa_srvr_msg.add_message(calling_fn => 'fa_chk_booksts_pkg.faxcms',
1036 name => 'FA_CONCURRENT_GET_STATUS');
1037 return(FALSE);
1038 end if;
1039 else -- same request based on parent
1040 X_request_id := 0;
1041 end if;
1042 else -- same request based on conc_req
1043 X_request_id := 0;
1044 end if;
1045 else -- no request is locking book
1046 X_request_id := 0;
1047 end if;
1048
1049 if (p_log_level_rec.statement_level) then
1050 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcms','mass_request_id',
1051 h_mass_req_id, p_log_level_rec);
1052 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcms','status',h_dev_phase, p_log_level_rec);
1053 end if;
1054
1055 return(TRUE);
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 fa_srvr_msg.add_sql_error(calling_fn => 'fa_chk_booksts_pkg.faxcms', p_log_level_rec => p_log_level_rec);
1060 return(FALSE);
1061
1062 END faxcms;
1063
1064 --
1065 -- FUNCTION faxcca
1066 --
1067
1068 FUNCTION faxcca(X_book IN VARCHAR2,
1069 X_request_id OUT NOCOPY NUMBER,
1070 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1071 return BOOLEAN is
1072
1073 h_ca_req_id NUMBER;
1074 h_phase VARCHAR2(200) := NULL;
1075 h_status VARCHAR2(200) := NULL;
1076 h_dev_phase VARCHAR2(200) := NULL;
1077 h_dev_status VARCHAR2(200) := NULL;
1078 h_message VARCHAR2(2000):= NULL;
1079 h_count number;
1080
1081 BEGIN
1082
1083 X_request_id := 0;
1084 h_ca_req_id := fa_cache_pkg.fazcbc_record.create_accounting_request_id;
1085
1086 if (h_ca_req_id IS NOT NULL) then
1087
1088 -- check for status of this mass request id
1089 -- get the parent request if applicable
1090
1091 if (fnd_concurrent.get_request_status
1092 (h_ca_req_id,NULL,NULL,h_phase,h_status,
1093 h_dev_phase, h_dev_status, h_message)) then
1094
1095 if (h_dev_phase = 'PENDING' OR
1096 h_dev_phase = 'INACTIVE' OR
1097 h_dev_phase = 'RUNNING') then
1098 X_request_id := h_ca_req_id;
1099 else -- completed request
1100 -- BUG# 5114320
1101 -- even if request was cancelled/terminated,
1102 -- make sure the db processes no longer exist
1103 select count(*)
1104 into h_count
1105 from v$session a
1106 where a.audsid in
1107 (select ORACLE_SESSION_ID
1108 from fnd_concurrent_requests
1109 where request_id = h_ca_req_id
1110 or parent_request_id = h_ca_req_id);
1111
1112 if (h_count = 0) then
1113 X_request_id := 0;
1114 else
1115 X_request_id := h_ca_req_id;
1116 end if;
1117 end if;
1118 else
1119 fa_srvr_msg.add_message(calling_fn => 'fa_chk_booksts_pkg.faxcms',
1120 name => 'FA_CONCURRENT_GET_STATUS');
1121 return(FALSE);
1122 end if;
1123 else -- no request is locking book
1124 X_request_id := 0;
1125 end if;
1126
1127 if (p_log_level_rec.statement_level) then
1128 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcca','create_accounting_request_id',
1129 h_ca_req_id, p_log_level_rec);
1130 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcca','status',h_dev_phase, p_log_level_rec);
1131 end if;
1132
1133 return(TRUE);
1134
1135 EXCEPTION
1136 WHEN OTHERS THEN
1137 fa_srvr_msg.add_sql_error(calling_fn => 'fa_chk_booksts_pkg.faxcca', p_log_level_rec => p_log_level_rec);
1138 return(FALSE);
1139
1140 END faxcca;
1141
1142
1143 --
1144 -- FUNCTION faxptb
1145 --
1146
1147 FUNCTION faxptb(X_book IN VARCHAR2,
1148 X_start IN BOOLEAN,
1149 X_asset_id IN NUMBER,
1150 X_trx_type IN VARCHAR2,
1151 X_txn_status IN OUT NOCOPY BOOLEAN,
1152 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1153 return BOOLEAN is
1154
1155 h_tax_book VARCHAR2(15);
1156 h_retval BOOLEAN := TRUE;
1157 h_cursor varchar2(6);
1158
1159 cursor tax_cursor is
1160 select bc.book_type_code
1161 from fa_book_controls bc
1162 where bc.distribution_source_book = X_book
1163 and bc.book_class = 'TAX'
1164 and bc.date_ineffective is null;
1165
1166 BEGIN
1167
1168 open tax_cursor;
1169
1170 while (X_txn_status) loop
1171 fetch tax_cursor into h_tax_book;
1172 exit when tax_cursor%NOTFOUND;
1173
1174 --call the cache to put the current tax book info in the record
1175 if (NOT FA_CACHE_PKG.fazcbc(X_book => h_tax_book,
1176 p_log_level_rec => p_log_level_rec)) then
1177 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxptb', p_log_level_rec => p_log_level_rec);
1178 return(FALSE);
1179 end if;
1180
1181 if (NOT faxlck(X_book => h_tax_book,
1182 X_txn_status => X_txn_status,
1183 X_asset_id => X_asset_id,
1184 X_trx_type => X_trx_type,
1185 p_log_level_rec => p_log_level_rec)) then -- lock the tax book
1186 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS.PKG.faxptb',
1187 p_log_level_rec => p_log_level_rec);
1188 h_retval := FALSE;
1189 exit;
1190 end if;
1191
1192
1193 if (NOT faxcps(X_book => h_tax_book,
1194 X_submit => FALSE,
1195 X_start => X_start,
1196 X_asset_id => X_asset_id,
1197 X_trx_type => X_trx_type,
1198 X_txn_status => X_txn_status,
1199 X_close_period => 0,
1200 p_log_level_rec => p_log_level_rec)) then
1201 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxptb',
1202 p_log_level_rec => p_log_level_rec);
1203 h_retval := FALSE;
1204 exit;
1205 end if;
1206
1207 if (p_log_level_rec.statement_level) then
1208 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxptb','book',h_tax_book, p_log_level_rec);
1209 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxptb','txn_status',X_txn_status, p_log_level_rec);
1210 end if;
1211 end loop;
1212
1213 close tax_cursor;
1214 return(h_retval);
1215
1216 EXCEPTION
1217 WHEN OTHERS THEN
1218 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxptb', p_log_level_rec => p_log_level_rec);
1219 close tax_cursor;
1220 return(FALSE);
1221
1222 END faxptb;
1223
1224 --
1225 -- FUNCTION faxgcb
1226 --
1227
1228 FUNCTION faxgcb(X_book IN VARCHAR2,
1229 X_txn_status IN OUT NOCOPY BOOLEAN,
1230 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1231 return BOOLEAN is
1232
1233 h_corp_book VARCHAR2(15);
1234 h_request_id NUMBER := 0;
1235 h_conc_program_id NUMBER;
1236 h_conflict NUMBER;
1237 h_tmp BOOLEAN;
1238
1239 BEGIN
1240
1241 h_corp_book := FA_CACHE_PKG.fazcbc_record.distribution_source_book;
1242
1243 -- call the cache to put the corp book info in the record
1244 if (NOT FA_CACHE_PKG.fazcbc(X_book => h_corp_book, p_log_level_rec => p_log_level_rec)) then
1245 fa_srvr_msg.add_sql_error(calling_fn=>'fa_chk_booksts_pkg.faxgcb', p_log_level_rec => p_log_level_rec);
1246 return(FALSE);
1247 end if;
1248
1249 if (NOT faxcms(X_book => h_corp_book,
1250 X_request_id => h_request_id,
1251 p_log_level_rec => p_log_level_rec)) then
1252 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxgcb',
1253 p_log_level_rec => p_log_level_rec);
1254 return(FALSE);
1255 end if;
1256
1257 if (h_request_id <> 0) then
1258
1259 -- process is ruuning,inactive,pending status
1260
1261 select re.concurrent_program_id
1262 into h_conc_program_id
1263 from fnd_concurrent_requests re
1264 where re.request_id = h_request_id
1265 and re.program_application_id = 140;
1266
1267 select decode(pr.concurrent_program_name,
1268 'FAMTFR',1,
1269 'FAMAPT',1,
1270 'GAINLOSS', 1,
1271 'FAMRCL', 1,
1272 'FAPPT', 1,
1273 0)
1274 into h_conflict
1275 from fnd_concurrent_programs pr
1276 where pr.concurrent_program_id = h_conc_program_id
1277 and pr.application_id = 140;
1278
1279 if (p_log_level_rec.statement_level) then
1280 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxgcb','book',h_corp_book, p_log_level_rec);
1281 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxgcb','mass_request_id',
1282 h_request_id, p_log_level_rec);
1283 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxgcb','conflict',h_conflict, p_log_level_rec);
1284 end if;
1285
1286 if (h_conflict <> 0) then -- curr program either FAMTFR or FAMAPT
1287 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS.PKG.faxgcb',
1288 name => 'FA_TRXAPP_WAIT_REQUEST',
1289 token1 => 'REQUEST_ID',
1290 value1 => h_request_id,
1291 token2 => 'BOOK',
1292 value2 => X_book,
1293 p_log_level_rec => p_log_level_rec);
1294 h_tmp := faxsav(X_action => 'R',
1295 X_txn_status => X_txn_status,
1296 p_log_level_rec => p_log_level_rec);
1297 -- rollback the lock, if any
1298 else
1299 X_txn_status := TRUE;
1300 end if;
1301 end if;
1302
1303 return (TRUE);
1304
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND then
1307 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxgcb',
1308 name => 'CONC_MISSING_REQUEST',
1309 token1 => 'ROUTINE',value1=>'FA_TRXAPP',
1310 token2 => 'REQUEST',value2=>h_request_id,
1311 p_log_level_rec => p_log_level_rec);
1312 return(FALSE);
1313
1314 WHEN OTHERS THEN
1315 fa_srvr_msg.add_sql_error(calling_fn=>'FA_CHK_BOOKSTS_PKG.faxgcb', p_log_level_rec => p_log_level_rec);
1316 return(FALSE);
1317 END faxgcb;
1318
1319 --
1320 -- FUNCTION faxlck
1321 --
1322
1323 FUNCTION faxlck(X_book IN VARCHAR2,
1324 X_txn_status IN OUT NOCOPY BOOLEAN,
1325 X_asset_id IN NUMBER,
1326 X_trx_type IN VARCHAR2,
1327 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1328 return BOOLEAN is
1329
1330 h_mass_id NUMBER;
1331 h_tmp BOOLEAN;
1332 h_trx_id_out NUMBER;
1333
1334 h_count number;
1335 h_adj_req_status VARCHAR2(4);
1336 lock_error exception;
1337
1338
1339 -- CURSOR defined for fix to bug 1067205
1340 -- snarayan
1341 CURSOR lock_asset IS
1342 SELECT transaction_header_id_out,
1343 adjustment_required_status
1344 FROM fa_books
1345 WHERE book_type_code = X_book
1346 AND asset_id = X_asset_id
1347 AND transaction_header_id_out is null
1348 FOR UPDATE OF
1349 transaction_header_id_out
1350 NOWAIT;
1351
1352 CURSOR lock_asset_mass IS
1353 SELECT transaction_header_id_out,
1354 adjustment_required_status
1355 FROM fa_books
1356 WHERE book_type_code = X_book
1357 AND asset_id = X_asset_id
1358 AND transaction_header_id_out is null
1359 FOR UPDATE OF
1360 transaction_header_id_out;
1361
1362 BEGIN
1363
1364 if (X_book is NULL) then /* Null book_type_code will rollback to savepoint */
1365
1366 if (NOT faxsav(X_action => 'R',
1367 X_txn_status => X_txn_status,
1368 p_log_level_rec => p_log_level_rec)) then
1369 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck',
1370 p_log_level_rec => p_log_level_rec);
1371 return(FALSE);
1372 end if;
1373
1374 -- ******************************************************************
1375 -- Lock Book Controls row only if a Mass Request is submitted.
1376 -- For individual asset transaction only lock the active FA_BOOKS row
1377 -- For ADDITION not necessary to lock row. ADDITION should be allowed
1378 -- as long as depreciation or no mass request is running on the book
1379 -- Fix for Bug 1067205 - snarayan
1380 -- ******************************************************************
1381 elsif (X_trx_type NOT IN ('ADDITION',
1382 'CIP ADDITION',
1383 'TRANSFER IN')) then
1384
1385 if (X_asset_id <> 0) then
1386
1387 if (X_trx_type = 'GROUP ADDITION') then
1388 -- if it is group addition need to lock if group
1389 -- is in period of addition and backdated transaction
1390 -- results in catchup
1391 select count(*)
1392 into h_count
1393 from fa_asset_history
1394 where asset_id = X_asset_id
1395 and date_ineffective is null;
1396
1397 if (h_count = 0) then
1398 return TRUE;
1399 end if;
1400 end if;
1401
1402 -- BUG# 3315327
1403 -- conditionally use NOWAIT for non-mass requests
1404 -- wait when it is a mass request to see if we avoid
1405 -- block contention here
1406 if (nvl(fnd_global.conc_request_id, -1) > 1) then
1407 OPEN lock_asset_mass;
1408 FETCH lock_asset_mass into h_trx_id_out, h_adj_req_status;
1409 CLOSE lock_asset_mass;
1410 else
1411 OPEN lock_asset;
1412 FETCH lock_asset into h_trx_id_out, h_adj_req_status;
1413 CLOSE lock_asset;
1414 end if;
1415
1416 if h_adj_req_status = 'GADJ' then
1417 raise lock_error;
1418 end if;
1419 else
1420 select mass_request_id
1421 into h_mass_id
1422 from fa_book_controls
1423 where book_type_code = X_book
1424 for update of mass_request_id
1425 NOWAIT;
1426 end if;
1427 end if;
1428
1429 return(TRUE);
1430
1431 EXCEPTION
1432 WHEN LOCK_ERROR THEN
1433 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck', p_log_level_rec => p_log_level_rec);
1434 h_tmp := faxsav(X_action => 'R',
1435 X_txn_status => X_txn_status,
1436 p_log_level_rec => p_log_level_rec);
1437 if (X_asset_id <> 0) then
1438 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck',
1439 name => 'FA_TRXAPP_ASSET_LOCK_FAILED',
1440 token1 => 'ASSET_ID',
1441 value1 => X_asset_id,
1442 token2 => 'BOOK',
1443 value2 => X_book,
1444 p_log_level_rec => p_log_level_rec);
1445 else
1446 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck',
1447 name => 'FA_TRXAPP_LOCK_FAILED',
1448 token1 => 'BOOK',
1449 value1 => X_book,
1450 p_log_level_rec => p_log_level_rec);
1451 end if;
1452
1453 return(FALSE);
1454
1455
1456 WHEN OTHERS THEN
1457 fa_srvr_msg.add_sql_error(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck', p_log_level_rec => p_log_level_rec);
1458 h_tmp := faxsav(X_action => 'R',
1459 X_txn_status => X_txn_status,
1460 p_log_level_rec => p_log_level_rec);
1461 if (X_asset_id <> 0) then
1462 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck',
1463 name => 'FA_TRXAPP_ASSET_LOCK_FAILED',
1464 token1 => 'ASSET_ID',
1465 value1 => X_asset_id,
1466 token2 => 'BOOK',
1467 value2 => X_book,
1468 p_log_level_rec => p_log_level_rec);
1469 else
1470 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxlck',
1471 name => 'FA_TRXAPP_LOCK_FAILED',
1472 token1 => 'BOOK',
1473 value1 => X_book,
1474 p_log_level_rec => p_log_level_rec);
1475 end if;
1476
1477 return(FALSE);
1478
1479 END faxlck;
1480
1481 --
1482 -- Function
1483 --
1484
1485 FUNCTION faxsav(X_action IN VARCHAR2,
1486 X_txn_status IN OUT NOCOPY BOOLEAN,
1487 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1488 return BOOLEAN is
1489
1490 BEGIN
1491
1492 if (X_action = 'S') then
1493 -- sets a savepoint to roll back when anything fails
1494 if (NOT savepoint_set) then
1495 SAVEPOINT lock_row;
1496 savepoint_set := TRUE;
1497 X_txn_status := TRUE;
1498 end if;
1499
1500 elsif (X_action = 'R') then -- rollback locks, error occured
1501 if (savepoint_set) then
1502 ROLLBACK TO SAVEPOINT lock_row;
1503 savepoint_set := FALSE;
1504 end if;
1505 X_txn_status := FALSE; -- txn not approved
1506
1507 elsif (X_action = 'C') then -- txn approved, clear savepoint for next call
1508 savepoint_set := FALSE; -- make row stay locked
1509
1510 else
1511 if (savepoint_set) then
1512 ROLLBACK TO SAVEPOINT lock_row;
1513 savepoint_set := FALSE;
1514 end if;
1515 X_txn_status := FALSE;
1516 return(FALSE);
1517
1518 end if;
1519
1520 if (p_log_level_rec.statement_level) then
1521 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxsav','action',X_action, p_log_level_rec);
1522 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxsav','savepoint',savepoint_set, p_log_level_rec);
1523 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxsav','txn_status',X_txn_status, p_log_level_rec);
1524 end if;
1525 return (TRUE);
1526
1527 EXCEPTION
1528 WHEN OTHERS THEN
1529 if (savepoint_set) then
1530 ROLLBACK TO SAVEPOINT lock_row;
1531 savepoint_set := FALSE;
1532 end if;
1533 X_txn_status := FALSE;
1534 return(FALSE);
1535
1536 END faxsav;
1537
1538
1539 FUNCTION faxcdr(X_book IN VARCHAR2,
1540 X_asset_id IN NUMBER DEFAULT 0,
1541 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return BOOLEAN IS
1542
1543 deprn_run VARCHAR2(1);
1544 h_count NUMBER;
1545 h_mc_source_flag VARCHAR2(3);
1546 h_set_of_books_id NUMBER;
1547 h_mrc_sob_type_code VARCHAR2(3);
1548 h_ca_request_id NUMBER;
1549
1550 -- variables for api calls
1551 l_msg_count NUMBER;
1552 l_msg_data VARCHAR2(4000);
1553 l_return_status VARCHAR2(1);
1554 l_deprn_run boolean := false;
1555 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
1556
1557 CURSOR check_deprn_run IS
1558 SELECT nvl(deprn_run, 'N')
1559 FROM fa_deprn_periods dp,
1560 fa_book_controls bc
1561 WHERE bc.book_type_code = X_book
1562 AND dp.book_type_code = bc.book_type_code
1563 AND dp.period_close_date is null;
1564
1565 BEGIN
1566
1567 -- need to call cache here because of the mass forms call
1568 -- faxcdr directly.
1569
1570 if (NOT FA_CACHE_PKG.fazcbc(X_book => X_book, p_log_level_rec => p_log_level_rec)) then
1571 fa_srvr_msg.add_sql_error
1572 (calling_fn => 'fa_chk_booksts_pkg.faxcdr', p_log_level_rec => p_log_level_rec);
1573 return (FALSE);
1574 end if;
1575
1576 h_mc_source_flag := FA_CACHE_PKG.fazcbc_record.mc_source_flag;
1577 h_set_of_books_id := FA_CACHE_PKG.fazcbc_record.set_of_books_id;
1578
1579 if not fa_cache_pkg.fazcsob
1580 (X_set_of_books_id => h_set_of_books_id,
1581 X_mrc_sob_type_code => h_mrc_sob_type_code,
1582 p_log_level_rec => p_log_level_rec) then
1583 fa_srvr_msg.add_sql_error
1584 (calling_fn => 'fa_chk_booksts_pkg.faxcdr', p_log_level_rec => p_log_level_rec);
1585 return(FALSE);
1586 end if;
1587
1588 if (X_asset_id = 0) then
1589
1590 -- SLA: allow mass programs even when deprn has been run
1591 -- we will rollback the processed deprn automatically
1592
1593 null;
1594
1595 /*
1596 OPEN check_deprn_run;
1597 FETCH check_deprn_run INTO deprn_run;
1598 CLOSE check_deprn_run;
1599
1600 if (deprn_run = 'Y') then
1601 l_deprn_run := true;
1602 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcdr',
1603 name => 'FA_DEPRN_ROLLBACK_BOOK',
1604 token1 => 'BOOK',
1605 value1 => X_book,
1606 p_log_level_rec => p_log_level_rec);
1607 return(FALSE);
1608 end if;
1609
1610 if (h_mc_source_flag = 'Y' and h_mrc_sob_type_code = 'P') then
1611 SELECT count(*)
1612 INTO h_count
1613 FROM fa_mc_deprn_periods dp,
1614 fa_mc_book_controls bc
1615 WHERE bc.book_type_code = X_book
1616 AND bc.book_type_code = dp.book_type_code
1617 AND bc.set_of_books_id = dp.set_of_books_id
1618 AND bc.enabled_flag = 'Y'
1619 AND dp.period_close_date is null
1620 AND dp.deprn_run = 'Y';
1621
1622 if (h_count <> 0) then
1623 l_deprn_run := true;
1624 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcdr',
1625 name => 'FA_DEPRN_ROLLBACK_BOOK',
1626 token1 => 'BOOK',
1627 value1 => X_book,
1628 p_log_level_rec => p_log_level_rec);
1629 return(FALSE);
1630 end if;
1631 end if;
1632 */
1633
1634 else
1635
1636 SELECT count(*)
1637 INTO h_count
1638 FROM fa_deprn_summary ds,
1639 fa_book_controls bc
1640 WHERE bc.book_type_code = X_book
1641 AND ds.book_type_code = bc.book_type_code
1642 AND ds.period_counter = bc.last_period_counter + 1
1643 AND ds.asset_id = X_asset_id
1644 AND ds.deprn_source_code in ('DEPRN','TRACK');
1645
1646 if (h_count <> 0) then
1647 l_deprn_run := true;
1648
1649 else
1650
1651 -- Fix for Bug #6528245. Still need check member assets for group.
1652 select count(*)
1653 into h_count
1654 from dual
1655 where exists
1656 (select 'x'
1657 FROM fa_deprn_summary ds,
1658 fa_book_controls bc,
1659 fa_books bks,
1660 fa_additions_b ad
1661 WHERE bc.book_type_code = X_Book
1662 AND ds.book_type_code = bc.book_type_code
1663 AND ds.period_counter = bc.last_period_counter + 1
1664 AND ds.asset_id = bks.asset_id
1665 AND ds.deprn_source_code in ('DEPRN','TRACK')
1666 AND bks.group_asset_id = X_Asset_ID
1667 AND bks.book_type_code = bc.book_type_code
1668 AND bks.transaction_header_id_out is null
1669 AND ad.asset_id = X_Asset_ID
1670 AND ad.asset_type = 'GROUP'
1671 );
1672
1673 if (h_count <> 0) then
1674 l_deprn_run := true;
1675 end if;
1676
1677 /*
1678 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcdr',
1679 name => 'FA_DEPRN_ROLLBACK_ASSET',
1680 token1 => 'BOOK',
1681 value1 => X_book,
1682 p_log_level_rec => p_log_level_rec);
1683 return(FALSE);
1684 */
1685 end if;
1686
1687 if (h_mc_source_flag = 'Y' and h_mrc_sob_type_code = 'P') then
1688 SELECT count(*)
1689 INTO h_count
1690 FROM fa_mc_deprn_summary ds,
1691 fa_book_controls bc
1692 WHERE bc.book_type_code = X_book
1693 AND ds.book_type_code = bc.book_type_code
1694 AND ds.period_counter = bc.last_period_counter + 1
1695 AND ds.asset_id = X_asset_id
1696 AND ds.deprn_source_code in ('DEPRN','TRACK');
1697
1698 if (h_count <> 0) then
1699 l_deprn_run := true;
1700 /*
1701 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcdr',
1702 name => 'FA_DEPRN_ROLLBACK_ASSET',
1703 token1 => 'BOOK',
1704 value1 => X_book,
1705 p_log_level_rec => p_log_level_rec);
1706 return(FALSE);
1707 */
1708 end if;
1709 end if;
1710
1711 -- SLA: removing period of addition validation
1712 -- adding call to the rollback api so we can automatixally
1713 -- reverse deprn and continue with trx unless error occurs
1714 if (l_deprn_run) then
1715
1716 l_asset_hdr_rec.asset_id := X_asset_id;
1717 l_asset_hdr_rec.book_type_code := X_book;
1718
1719 -- BUG# 5444002
1720 -- only allow automated rollback if create accounting is not currently running
1721
1722 if(NOT faxcca(X_book => X_book,
1723 X_request_id => h_ca_request_id,
1724 p_log_level_rec => p_log_level_rec)) then
1725 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
1726 p_log_level_rec => p_log_level_rec);
1727 return(FALSE);
1728 end if;
1729
1730 if (p_log_level_rec.statement_level) then
1731 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcdr','book',X_book, p_log_level_rec);
1732 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcdr','create_accounting_request_id',
1733 h_ca_request_id, p_log_level_rec);
1734 end if;
1735
1736 if (h_ca_request_id <> 0) then
1737 -- req_id is in status of running,inactive,or pending
1738 fa_srvr_msg.add_message(calling_fn => 'FA_CHK_BOOKSTS_PKG.faxcps',
1739 name => 'FA_TRXAPP_WAIT_REQUEST',
1740 token1 => 'REQUEST_ID',
1741 value1 => h_ca_request_id,
1742 token2 => 'BOOK',
1743 value2 => X_book,
1744 p_log_level_rec => p_log_level_rec);
1745 return(FALSE);
1746
1747 else
1748
1749 FA_DEPRN_ROLLBACK_PUB.do_rollback
1750 (p_api_version => 1.0,
1751 p_init_msg_list => FND_API.G_FALSE,
1752 p_commit => FND_API.G_FALSE,
1753 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1754 x_return_status => l_return_status,
1755 x_msg_count => l_msg_count,
1756 x_msg_data => l_msg_data,
1757 p_calling_fn => null,
1758 px_asset_hdr_rec => l_asset_hdr_rec);
1759
1760 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1761 return false;
1762 else
1763 if (p_log_level_rec.statement_level) then
1764 fa_debug_pkg.add('FA_CHK_BOOKSTS_PKG.faxcdr',
1765 'successfully rolled back deprn','',p_log_level_rec);
1766 end if;
1767 end if;
1768 end if;
1769 end if;
1770
1771 end if; -- end if asset_id = 0
1772
1773 return(TRUE);
1774
1775 EXCEPTION
1776 WHEN OTHERS THEN
1777 fa_srvr_msg.add_sql_error(calling_fn => 'fa_chk_booksts_pkg.faxcdr', p_log_level_rec => p_log_level_rec);
1778 return(FALSE);
1779 END faxcdr;
1780
1781
1782 END FA_CHK_BOOKSTS_PKG;
[Home] [Help]