[Home] [Help]
PACKAGE BODY: APPS.FA_TRX_APPROVAL_PKG
Source
1 PACKAGE BODY FA_TRX_APPROVAL_PKG AS
2 /* $Header: FATRXAPB.pls 120.5.12010000.2 2008/07/31 06:55:49 sbhaskar ship $ */
3
4
5
6 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
7
8 FUNCTION faxcat (X_book VARCHAR2,
9 X_asset_id NUMBER,
10 X_trx_type VARCHAR2,
11 X_trx_date DATE,
12 X_init_message_flag VARCHAR2 DEFAULT 'NO',
13 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
14 RETURN BOOLEAN IS
15 h_result BOOLEAN;
16 X_result BOOLEAN;
17 BEGIN
18
19
20 IF (X_init_message_flag = 'YES') THEN
21 -- initialize Message and Debug stacks
22 FA_SRVR_MSG.Init_Server_Message;
23 FA_DEBUG_PKG.Initialize;
24 END if;
25
26
27 h_result := TRUE;
28
29 IF NOT faxcti(X_book=>X_book,
30 X_asset_id=>X_asset_id,
31 X_trx_type=>X_trx_type,
32 X_trx_date=>X_trx_date,
33 X_result=>h_result
34 ,p_log_level_rec => p_log_level_rec) THEN
35 FA_SRVR_MSG.add_message
36 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcat'
37 ,p_log_level_rec => p_log_level_rec);
38 RETURN (h_result); /*FALSE */
39 END IF;
40
41 IF (p_log_level_rec.statement_level) THEN
42 FA_DEBUG_PKG.ADD ('FA:faxcat. After returning from faxcti','h_result=',h_result
43 ,p_log_level_rec => p_log_level_rec);
44 END IF;
45
46
47 IF (NOT FA_CHK_BOOKSTS_PKG.faxcbs(X_book=>X_book,
48 X_submit=>FALSE,
49 X_start=>FALSE,
50 X_asset_id=>X_asset_id ,
51 X_trx_type=>X_trx_type ,
52 X_txn_status=>h_result
53 ,p_log_level_rec => p_log_level_rec)) THEN
54 FA_SRVR_MSG.add_message
55 (CALLING_FN=>'FA_TRX_APPROVAL_PKG.faxcat'
56 ,p_log_level_rec => p_log_level_rec);
57 RETURN (h_result); /*FALSE*/
58 END IF;
59
60 RETURN (h_result); /*TRUE*/
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 FA_SRVR_MSG.Add_SQL_Error
65 (CALLING_FN=>'FA_TRX_APPROVAL_PKG.faxcat'
66 ,p_log_level_rec => p_log_level_rec);
67 h_result := FALSE;
68 RETURN (h_result); /*FALSE*/
69 --dbms_output.put_line('end of func1');
70
71 END faxcat;
72
73
74 /*=========================================================================
75 |
76 | This function checks transaction integrity. It checks whether there any
77 | transactions entered for this asset on a date after this transaction date.
78 | Also checks whether there are retirements pending for the asset.
79 |
80 | Modifies: X_result = TRUE (boolean) if transaction is allowed
81 |
82 | Returns: TRUE(boolean) if no error
83 +==========================================================================*/
84
85
86 FUNCTION faxcti (X_book VARCHAR2,
87 X_asset_id NUMBER,
88 X_trx_type VARCHAR2,
89 X_trx_date DATE,
90 X_result IN OUT NOCOPY BOOLEAN,
91 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
92 h_count NUMBER := 0;
93 h_count_mrc NUMBER := 0;
94 h_mrc_book_count NUMBER := 0;
95 h_ah_units NUMBER := 0;
96 h_dh_units NUMBER := 0;
97
98 l_corp_book varchar2(15);
99 error_found exception;
100
101 BEGIN
102 --X_result would be always TRUE at this stage.
103 IF (p_log_level_rec.statement_level)THEN
104 FA_DEBUG_PKG.ADD ('FA:faxcti','X_result in faxcti in the beginning = ',X_result
105 ,p_log_level_rec => p_log_level_rec);
106 END IF;
107
108 if not (fa_cache_pkg.fazcbc(X_book => X_book
109 ,p_log_level_rec => p_log_level_rec)) then
110 raise error_found;
111 end if;
112
113
114 -- added for BUG# 1338191 for approving UNDO RETIREMENT trx's
115 -- added enabled_clause for reporting books due to BUG# 1486157
116
117 SELECT count(*)
118 INTO h_mrc_book_count
119 FROM fa_mc_book_controls
120 WHERE book_type_code = X_book
121 AND enabled_flag = 'Y';
122
123 --IF (X_result) THEN
124 SELECT COUNT(*) INTO h_count
125 FROM fa_retirements faret
126 WHERE faret.asset_id = X_asset_id
127 AND faret.book_type_code = X_book
128 AND UPPER(faret.status) in ('PENDING', 'REINSTATE');
129
130 IF (p_log_level_rec.statement_level)THEN
131 FA_DEBUG_PKG.ADD ('FA:faxcti','h_count in faxcti = ',h_count
132 ,p_log_level_rec => p_log_level_rec);
133 END IF;
134
135 SELECT COUNT(*) INTO h_count_mrc
136 FROM fa_retirements ret,
137 fa_mc_retirements mc_ret,
138 fa_mc_book_controls mc_bks
139 WHERE ret.asset_id = X_asset_id
140 AND ret.book_type_code = X_book
141 AND mc_bks.book_type_code = X_book
142 AND mc_bks.enabled_flag = 'Y'
143 AND ret.retirement_id = mc_ret.retirement_id
144 AND UPPER(mc_ret.status) in ('PENDING', 'REINSTATE')
145 AND mc_ret.set_of_books_id = mc_bks.set_of_books_id;
146
147 IF (p_log_level_rec.statement_level)THEN
148 FA_DEBUG_PKG.ADD ('FA:faxcti','h_count_mrc in faxcti = ',h_count_mrc
149 ,p_log_level_rec => p_log_level_rec);
150 END IF;
151
152 /* changed the following for BUG# 1338191
153 UNDO RETIRE can not check for PENDING retirements or else
154 the transaction would never be allowed. However, we must
155 check the primary and reporting to verify that gain/loss
156 has not been run on the reporting books. --bridgway 06/23/00
157 */
158
159 IF ((X_trx_type = 'UNDO RETIRE') or
160 (X_trx_type = 'DELETE REINSTATEMENT')) then
161 /* correcting BUG# 1340968 to not use "!=" */
162 IF (h_mrc_book_count <> 0) THEN
163 IF (h_count <> (h_count_mrc/h_mrc_book_count)) THEN
164 FA_SRVR_MSG.add_message
165 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti',
166 NAME => 'FA_SHARED_PENDING_RETIREMENT'
167 ,p_log_level_rec => p_log_level_rec);
168 X_result := FALSE;
169 RETURN (X_result); /*FALSE*/
170 ELSE
171 X_result := TRUE;
172 END IF;
173 ELSE
174 X_result := TRUE;
175 END IF;
176 ELSE
177 IF (h_count + h_count_mrc > 0) THEN
178 FA_SRVR_MSG.add_message
179 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti',
180 NAME => 'FA_SHARED_PENDING_RETIREMENT'
181 ,p_log_level_rec => p_log_level_rec);
182 X_result := FALSE;
183 RETURN (X_result); /*FALSE*/
184 ELSE
185 X_result := TRUE;
186 END IF;
187 END IF;
188
189 -- BUG# 5444344
190 -- for tax, need to check corp retirements to due to
191 -- partial unit impacts
192
193 if (fa_cache_pkg.fazcbc_record.book_class = 'TAX') then
194
195 l_corp_book := fa_cache_pkg.fazcbc_record.distribution_source_book;
196
197 SELECT COUNT(*) INTO h_count
198 FROM fa_retirements faret
199 WHERE faret.asset_id = X_asset_id
200 AND faret.book_type_code = l_corp_book
201 AND UPPER(faret.status) in ('PENDING');
202
203 IF (p_log_level_rec.statement_level)THEN
204 FA_DEBUG_PKG.ADD ('FA:faxcti','h_count in faxcti = ',h_count
205 ,p_log_level_rec => p_log_level_rec);
206 END IF;
207
208 SELECT COUNT(*) INTO h_count_mrc
209 FROM fa_retirements ret,
210 fa_mc_retirements mc_ret,
211 fa_mc_book_controls mc_bks
212 WHERE ret.asset_id = X_asset_id
213 AND ret.book_type_code = l_corp_book
214 AND mc_bks.book_type_code = l_corp_book
215 AND mc_bks.enabled_flag = 'Y'
216 AND ret.retirement_id = mc_ret.retirement_id
217 AND UPPER(mc_ret.status) in ('PENDING')
218 AND mc_ret.set_of_books_id = mc_bks.set_of_books_id;
219
220 IF (p_log_level_rec.statement_level)THEN
221 FA_DEBUG_PKG.ADD ('FA:faxcti','h_count_mrc in faxcti = ',h_count_mrc
222 ,p_log_level_rec => p_log_level_rec);
223 END IF;
224
225 /* changed the following for BUG# 1338191
226 UNDO RETIRE can not check for PENDING retirements or else
227 the transaction would never be allowed. However, we must
228 check the primary and reporting to verify that gain/loss
229 has not been run on the reporting books. --bridgway 06/23/00
230 */
231 IF ((X_trx_type = 'UNDO RETIRE') or
232 (X_trx_type = 'DELETE REINSTATEMENT')) then
233 /* correcting BUG# 1340968 to not use "!=" */
234 IF (h_mrc_book_count <> 0) THEN
235 IF (h_count <> (h_count_mrc/h_mrc_book_count)) THEN
236 FA_SRVR_MSG.add_message
237 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti',
238 NAME => 'FA_SHARED_PENDING_RETIREMENT'
239 ,p_log_level_rec => p_log_level_rec);
240 X_result := FALSE;
241 RETURN (X_result); /*FALSE*/
242 ELSE
243 X_result := TRUE;
244 END IF;
245 ELSE
246 X_result := TRUE;
247 END IF;
248 ELSE
249 IF (h_count + h_count_mrc > 0) THEN
250 FA_SRVR_MSG.add_message
251 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti',
252 NAME => 'FA_SHARED_PENDING_RETIREMENT'
253 ,p_log_level_rec => p_log_level_rec);
254 X_result := FALSE;
255 RETURN (X_result); /*FALSE*/
256 ELSE
257 X_result := TRUE;
258 END IF;
259 END IF;
260 END IF; -- tax class
261 --END IF;
262
263
264 --IF (X_result) THEN /* X_result */
265 IF UPPER(X_trx_type) IN('TRANSFER', 'CIP TRANSFER', 'RECLASS',
266 'CIP RECLASS') THEN
267 SELECT units
268 INTO h_ah_units
269 FROM fa_asset_history
270 WHERE asset_id = X_asset_id
271 AND date_ineffective IS NULL;
272
273 IF (p_log_level_rec.statement_level)THEN
274 FA_DEBUG_PKG.ADD ('FA:faxcat','h_ah_units in faxcti',h_ah_units
275 ,p_log_level_rec => p_log_level_rec);
276 END IF;
277
278 SELECT SUM(units_assigned)
279 INTO h_dh_units
280 FROM fa_distribution_history
281 WHERE asset_id = X_asset_id
282 AND date_ineffective IS NULL;
283
284 IF (p_log_level_rec.statement_level)THEN
285 FA_DEBUG_PKG.ADD ('FA:faxcat','h_dh_units in facti',h_dh_units
286 ,p_log_level_rec => p_log_level_rec);
287 END IF;
288
289 IF (h_ah_units <> h_dh_units) THEN
290 FA_SRVR_MSG.add_message
291 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti',
292 NAME => 'FA_SHARED_UNITS_UNBAL'
293 ,p_log_level_rec => p_log_level_rec);
294 X_result := FALSE;
295 RETURN (X_result); /*FALSE*/
296 ELSE
297 X_result := TRUE;
298 END IF;
299 END IF;
300 --END IF; /* X_result */
301
302 /* mwoodwar 02/22/00. CRL stub call. */
303 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
304 if not fa_cua_trx_approval_ext_pkg.facuas1(x_trx_type,
305 x_book,
306 x_asset_id) then
307 x_result:= FALSE;
308 end if;
309 end if;
310
311 --bug6933756
312 FA_TRACK_MEMBER_PVT.p_track_member_table.delete;
313
314 RETURN (X_result); /*TRUE*/
315
316 EXCEPTION
317 WHEN ERROR_FOUND THEN
318 FA_SRVR_MSG.Add_Message
319 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti'
320 ,p_log_level_rec => p_log_level_rec);
321 X_result := FALSE;
322 RETURN (X_result); /*FALSE*/
323
324 WHEN OTHERS THEN
325 FA_SRVR_MSG.Add_SQL_Error
326 (CALLING_FN => 'FA_TRX_APPROVAL_PKG.faxcti'
327 ,p_log_level_rec => p_log_level_rec);
328 X_result := FALSE;
329 RETURN (X_result); /*FALSE*/
330
331
332 END faxcti;
333
334
335 END FA_TRX_APPROVAL_PKG;