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