[Home] [Help]
PACKAGE BODY: APPS.CE_CASHFLOW_PKG
Source
1 package body CE_CASHFLOW_PKG as
2 /* $Header: cecashpb.pls 120.4.12010000.3 2008/09/15 12:39:31 vnetan ship $ */
3
4 l_DEBUG varchar2(1) := NVL(FND_PROFILE.value('CE_DEBUG'), 'N');
5 -- l_DEBUG varchar2(1) := 'Y';
6
7 /* ---------------------------------------------------------------------
8 | PRIVATE PROCEDURE |
9 | update_ce_cashflows
10 | DESCRIPTION |
11 | update records in CE_CASHFLOWS |
12 | CALLED BY |
13 | clear_cashflow
14 --------------------------------------------------------------------- */
15 PROCEDURE update_ce_cashflows(
16 X_CASHFLOW_ID number,
17 X_TRX_STATUS varchar2,
18 X_actual_value_date date,
19 X_CLEARED_DATE date,
20 X_CLEARED_AMOUNT number,
21 X_CLEARED_ERROR_AMOUNT number,
22 X_CLEARED_CHARGE_AMOUNT number,
23 X_CLEARED_EXCHANGE_RATE_TYPE varchar2,
24 X_CLEARED_EXCHANGE_RATE_DATE date,
25 X_CLEARED_EXCHANGE_RATE number,
26 X_NEW_TRX_STATUS varchar2,
27 X_CLEARED_BY_FLAG VARCHAR2 ,
28 X_LAST_UPDATE_DATE date,
29 X_LAST_UPDATED_BY number,
30 X_LAST_UPDATE_LOGIN number,
31 X_STATEMENT_LINE_ID number,
32 X_PASSIN_MODE varchar2
33 ) IS
34 BEGIN
35 IF l_DEBUG in ('Y', 'C') THEN
36 cep_standard.debug('>>CE_CASHFLOW_PKG.UPDATE_CE_CASHFLOWS');
37 cep_standard.debug('X_PASSIN_MODE: '|| X_PASSIN_MODE ||
38 ', X_STATEMENT_LINE_ID: '|| X_STATEMENT_LINE_ID||
39 ', X_TRX_STATUS: '||X_TRX_STATUS||
40 ', X_NEW_TRX_STATUS: '|| X_NEW_TRX_STATUS
41 );
42 cep_standard.debug('X_CLEARED_BY_FLAG: '|| X_CLEARED_BY_FLAG ||
43 ', X_CASHFLOW_ID: '|| X_CASHFLOW_ID ||
44 ', X_CLEARED_AMOUNT: '|| X_CLEARED_AMOUNT
45 );
46 END IF;
47
48 -- MANUAL_L, MANUAL_H, AUTO (X_TRX_STATUS = 'CLEARED', X_NEW_TRX_STATUS = 'RECONCILED')
49 -- MANUAL (X_TRX_STATUS = 'RECONCILED', X_NEW_TRX_STATUS = 'CLEARED'),
50 IF ((X_PASSIN_MODE <> 'MANUAL_UC' and X_TRX_STATUS = 'CLEARED') or
51 ((X_PASSIN_MODE <> 'MANUAL_C' or X_PASSIN_MODE = 'MANUAL') and X_NEW_TRX_STATUS = 'CLEARED')) THEN
52 -- for cleared cashflows, keep current clear info
53 UPDATE CE_CASHFLOWS
54 SET CASHFLOW_STATUS_CODE = X_NEW_TRX_STATUS,
55 CLEARED_BY_FLAG = X_CLEARED_BY_FLAG,
56 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
57 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
58 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
59 STATEMENT_LINE_ID = X_STATEMENT_LINE_ID
60 WHERE CASHFLOW_ID = X_CASHFLOW_ID;
61 ELSE
62 -- MANUAL_C (X_TRX_STATUS = 'CREATED', X_NEW_TRX_STATUS = 'CLEARED'),
63 -- MANUAL_UC (X_TRX_STATUS = 'CLEARED', X_NEW_TRX_STATUS = 'CREATED' ),
64 -- MANUAL (X_TRX_STATUS = 'CLEARED', X_NEW_TRX_STATUS = 'CREATED')
65 -- MANUAL_L, MANUAL_H, AUTO (X_TRX_STATUS = 'CREATED', X_NEW_TRX_STATUS = 'RECONCILED')
66 UPDATE CE_CASHFLOWS
67 SET CASHFLOW_STATUS_CODE = X_NEW_TRX_STATUS,
68 ACTUAL_VALUE_DATE = X_actual_value_date,
69 CLEARED_DATE = X_CLEARED_DATE ,
70 CLEARED_AMOUNT = abs(X_CLEARED_AMOUNT),
71 CLEARED_EXCHANGE_RATE = X_CLEARED_EXCHANGE_RATE,
72 CLEARED_EXCHANGE_DATE = X_CLEARED_EXCHANGE_RATE_DATE,
73 CLEARED_EXCHANGE_RATE_TYPE = X_CLEARED_EXCHANGE_RATE_TYPE ,
74 CLEARING_CHARGES_AMOUNT = X_CLEARED_CHARGE_AMOUNT,
75 CLEARING_ERROR_AMOUNT = X_CLEARED_ERROR_AMOUNT,
76 CLEARED_BY_FLAG = X_CLEARED_BY_FLAG,
77 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
78 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
79 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
80 STATEMENT_LINE_ID = X_STATEMENT_LINE_ID
81 WHERE CASHFLOW_ID = X_CASHFLOW_ID;
82
83 END IF;
84
85 IF l_DEBUG in ('Y', 'C') THEN
86 cep_standard.debug('<<CE_CASHFLOW_PKG.UPDATE_CE_CASHFLOWS');
87 END IF;
88
89 EXCEPTION
90 WHEN OTHERS THEN
91 IF l_DEBUG in ('Y', 'C') THEN
92 cep_standard.debug('EXCEPTION: CE_CASHFLOW_PKG.UPDATE_CE_CASHFLOWS');
93 END IF;
94 RAISE;
95 END UPDATE_CE_CASHFLOWS;
96
97 /* ---------------------------------------------------------------------
98 | PRIVATE PROCEDURE |
99 | RAISE_ACCT_EVENT
100 | CALLED BY |
101 | clear_cashflow |
102 --------------------------------------------------------------------- */
103 PROCEDURE RAISE_ACCT_EVENT(
104 X_CASHFLOW_ID number,
105 X_ACCTG_EVENT varchar2,
106 X_ACCOUNTING_DATE date,
107 X_EVENT_STATUS_CODE VARCHAR2,
108 X_EVENT_ID IN OUT NOCOPY NUMBER) IS
109 --x_event_id number;
110 BEGIN
111 IF l_DEBUG in ('Y', 'C') THEN
112 cep_standard.debug('>>CE_CASHFLOW_PKG.RAISE_ACCT_EVENT');
113 END IF;
114
115 ce_xla_acct_events_pkg.create_event(
116 X_trx_id => X_CASHFLOW_ID,
117 X_event_type_code => X_ACCTG_EVENT ,
118 X_GL_DATE => X_ACCOUNTING_DATE);
119
120 /* x_event_id := ce_xla_acct_events_pkg.create_events(
121 X_trx_id => X_CASHFLOW_ID,
122 X_event_type_code => X_ACCTG_EVENT ,
123 X_event_date => X_ACCOUNTING_DATE,
124 X_event_status_code => X_EVENT_STATUS_CODE);
125
126 */
127 IF l_DEBUG in ('Y', 'C') THEN
128 cep_standard.debug('<<CE_CASHFLOW_PKG.RAISE_ACCT_EVENT');
129 END IF;
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 IF l_DEBUG in ('Y', 'C') THEN
134 cep_standard.debug('EXCEPTION: CE_CASHFLOW_PKG.RAISE_ACCT_EVENT');
135 END IF;
136 RAISE;
137 END RAISE_ACCT_EVENT;
138
139 /* ---------------------------------------------------------------------
140 | PRIVATE PROCEDURE |
141 | clear_cashflow |
142 | DESCRIPTION |
143 | CALLS |
144 | update_ce_cashflows |
145 | CALLED BY |
146 | CE_AUTO_BANK_CLEAR1.reconcile_trx |
147 | CE_AUTO_BANK_CLEAR1.unclear_process |
148 --------------------------------------------------------------------- */
149 PROCEDURE clear_cashflow (
150 x_cashflow_id NUMBER,
151 x_trx_status VARCHAR2,
152 x_actual_value_date DATE,
153 x_accounting_date DATE,
154 x_cleared_date DATE,
155 x_cleared_amount NUMBER,
156 x_cleared_error_amount NUMBER,
157 x_cleared_charge_amount NUMBER,
158 x_cleared_exchange_rate_type VARCHAR2,
159 x_cleared_exchange_rate_date DATE,
160 x_cleared_exchange_rate NUMBER,
161 x_passin_mode VARCHAR2,
162 x_statement_line_id NUMBER,
163 x_statement_line_type VARCHAR2
164 ) IS
165
166 x_cleared_by_flag VARCHAR2(1);
167 x_current_cleared_by_flag VARCHAR2(1);
168 x_new_trx_status VARCHAR2(15);
169 x_event_id NUMBER;
170 x_source_trxn_type VARCHAR2(30);
171 x_new_stmt_ln_id NUMBER;
172 x_trx_stmt_ln_id NUMBER;
173 x_new_statement_line_type VARCHAR2(30);
174 x_cashflow_status_code VARCHAR2(30);
175 x_cf_trx_status VARCHAR2(30);
176 l_acctg_event varchar2(30);
177
178 BEGIN
179 IF l_DEBUG in ('Y', 'C') THEN
180 cep_standard.debug('>>CE_CASHFLOW_PKG.clear_cashflow');
181 END IF;
182
183 /* bug 7395052 - added CLEARED_BY_FLAG column which was earlier being
184 * fetched by a separate query after
185 * ELSIF (X_PASSIN_MODE in ( 'MANUAL', 'MANUAL_UC'))
186 */
187 SELECT SOURCE_TRXN_TYPE,
188 STATEMENT_LINE_ID,
189 CASHFLOW_STATUS_CODE,
190 cleared_by_flag
191 INTO x_source_trxn_type,
192 x_trx_stmt_ln_id,
193 x_cashflow_status_code,
194 x_current_cleared_by_flag
195 FROM ce_cashflows
196 WHERE cashflow_id = X_CASHFLOW_ID;
197
198 X_NEW_STMT_LN_ID := nvl(X_STATEMENT_LINE_ID, x_trx_stmt_ln_id);
199 X_NEW_STATEMENT_LINE_TYPE := X_STATEMENT_LINE_TYPE;
200
201 -- IBY will not pass X_TRX_STATUS for cashflows in IBY batch
202 IF (X_TRX_STATUS is null) THEN
203 X_CF_TRX_STATUS := X_CASHFLOW_STATUS_CODE;
204 ELSE
205 X_CF_TRX_STATUS := X_TRX_STATUS;
206 END IF;
207
208 IF (X_PASSIN_MODE = 'MANUAL_C')
209 THEN -- manual clearing
210 X_NEW_TRX_STATUS := 'CLEARED';
211 X_CLEARED_BY_FLAG := 'M';
212
213 ELSIF (X_PASSIN_MODE in ( 'MANUAL_L', 'MANUAL_H', 'AUTO'))
214 THEN --manual/auto reconciliation
215 IF (X_CF_TRX_STATUS = 'CLEARED')
216 THEN
217 X_NEW_TRX_STATUS := 'RECONCILED';
218 X_CLEARED_BY_FLAG := 'M';
219 ELSIF (X_CF_TRX_STATUS = 'CREATED')
220 THEN
221 X_NEW_TRX_STATUS := 'RECONCILED';
222 X_CLEARED_BY_FLAG := 'R';
223 ELSE
224 FND_MESSAGE.set_name( 'CE', 'CE_CF_CANNOT_RECON' );
225 RAISE APP_EXCEPTION.application_exception;
226 END IF;
227
228 ELSIF (X_PASSIN_MODE in ( 'MANUAL', 'MANUAL_UC'))
229 THEN -- manual unreconciled/ manual unclearing
230
231 /* Bug 7395052 - Removed redundant query call */
232 -- select cleared_by_flag
233 -- into x_current_cleared_by_flag
234 -- from ce_cashflows
235 -- where cashflow_id = X_CASHFLOW_ID;
236
237 /* Bug 7395052 - Added condition to ensure that if the trx is a
238 statement cashflow, its status should not become created */
239 IF (x_source_trxn_type = 'STMT')
240 THEN
241 X_NEW_TRX_STATUS := 'CLEARED';
242 ELSE
243 X_NEW_TRX_STATUS := 'CREATED';
244 END IF;
245
246 IF (x_current_cleared_by_flag = 'R')
247 THEN
248 X_CLEARED_BY_FLAG := NULL;
249 ELSIF (x_current_cleared_by_flag = 'M')
250 THEN
251 X_CLEARED_BY_FLAG := 'M';
252 ELSE
253 FND_MESSAGE.set_name( 'CE', 'CE_CF_CANNOT_UNRECON' );
254 RAISE APP_EXCEPTION.application_exception;
255 END IF;
256
257 END IF;
258
259
260 IF (X_PASSIN_MODE in ('MANUAL_C','MANUAL_L', 'MANUAL_H', 'AUTO')) THEN
261
262 -- UPDATE CE_CASHFLOWS
263 update_ce_cashflows(
264 X_CASHFLOW_ID => X_CASHFLOW_ID,
265 X_TRX_STATUS => X_CF_TRX_STATUS,
266 X_actual_value_date => X_actual_value_date,
267 X_CLEARED_DATE => X_CLEARED_DATE ,
268 X_CLEARED_AMOUNT => abs(X_CLEARED_AMOUNT),
269 X_CLEARED_ERROR_AMOUNT => X_CLEARED_ERROR_AMOUNT,
270 X_CLEARED_CHARGE_AMOUNT => X_CLEARED_CHARGE_AMOUNT,
271 X_CLEARED_EXCHANGE_RATE_TYPE => X_CLEARED_EXCHANGE_RATE_TYPE ,
272 X_CLEARED_EXCHANGE_RATE_DATE => X_CLEARED_EXCHANGE_RATE_DATE,
273 X_CLEARED_EXCHANGE_RATE => X_CLEARED_EXCHANGE_RATE,
274 X_NEW_TRX_STATUS => X_NEW_TRX_STATUS,
275 X_CLEARED_BY_FLAG => X_CLEARED_BY_FLAG,
276 X_LAST_UPDATE_DATE => sysdate,
277 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
278 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
279 X_STATEMENT_LINE_ID => x_new_stmt_ln_id,
280 X_PASSIN_MODE => X_PASSIN_MODE
281 );
282
283 -- bug 5203892 do not raise any acctg event for STMT (JEC) cashflows
284 -- when clearing/unclearing (reconciliation/unreconciliation)
285 IF x_source_trxn_type = 'BAT' THEN
286 l_acctg_event := 'CE_BAT_CLEARED';
287 --ELSE
288 --l_acctg_event := 'CE_STMT_RECORDED';
289 --END IF;
290
291 -- RAISE CLEARING/UNCLEARING ACCOUNTING EVENT
292 IF (X_CF_TRX_STATUS = 'CREATED') THEN
293 RAISE_ACCT_EVENT
294 (X_CASHFLOW_ID => X_CASHFLOW_ID,
295 X_ACCTG_EVENT => l_acctg_event, --'CLEARING',
296 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
297 X_EVENT_STATUS_CODE => 'UNPROCESSED',
298 X_EVENT_ID => X_EVENT_ID);
299 END IF;
300 END IF; --x_source_trxn_type = 'BAT'
301
302 ELSIF (X_PASSIN_MODE in ('MANUAL', 'MANUAL_UC')) THEN
303 -- MANUAL (unreconciled), MANUAL_UC (uncleared)
304 -- do not remove ce_cashflows.statement_line_id when unclearing/unreconciling JEC/ZBA trx
305 -- x_source_trxn_type: 'STMT' (JEC trx), 'BAT' (ZBA trx)
306
307 IF (X_STATEMENT_LINE_TYPE is null) and
308 ( X_NEW_STMT_LN_ID is not null) THEN
309 select trx_type
310 into X_NEW_STATEMENT_LINE_TYPE
311 from ce_statement_lines
312 where statement_line_id = X_NEW_STMT_LN_ID;
313 END IF;
314
315 IF (x_source_trxn_type <> 'STMT') THEN
316 IF (X_NEW_STATEMENT_LINE_TYPE not in ('SWEEP_IN', 'SWEEP_OUT')) THEN
317 X_NEW_STMT_LN_ID := NULL;
318 END IF;
319 END IF;
320
321 IF ((X_PASSIN_MODE = 'MANUAL_UC') or (X_NEW_TRX_STATUS = 'CREATED' and X_CLEARED_BY_FLAG is null)) THEN
322 update_ce_cashflows(
323 X_CASHFLOW_ID => X_CASHFLOW_ID,
324 X_TRX_STATUS => X_CF_TRX_STATUS,
325 X_actual_value_date => null,
326 X_CLEARED_DATE => null,
327 X_CLEARED_AMOUNT => null,
328 X_CLEARED_ERROR_AMOUNT => null,
329 X_CLEARED_CHARGE_AMOUNT => null,
330 X_CLEARED_EXCHANGE_RATE_TYPE => null,
331 X_CLEARED_EXCHANGE_RATE_DATE => null,
332 X_CLEARED_EXCHANGE_RATE => null,
333 X_NEW_TRX_STATUS => X_NEW_TRX_STATUS,
334 X_CLEARED_BY_FLAG => X_CLEARED_BY_FLAG ,
335 X_LAST_UPDATE_DATE => sysdate,
336 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
337 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
338 X_STATEMENT_LINE_ID => x_new_stmt_ln_id,
339 X_PASSIN_MODE => X_PASSIN_MODE
340 );
341
342 ELSE
343
344 -- UPDATE CE_CASHFLOWS
345 update_ce_cashflows(
346 X_CASHFLOW_ID => X_CASHFLOW_ID,
347 X_TRX_STATUS => X_CF_TRX_STATUS,
348 X_actual_value_date => X_actual_value_date,
349 X_CLEARED_DATE => X_CLEARED_DATE ,
350 X_CLEARED_AMOUNT => abs(X_CLEARED_AMOUNT),
351 X_CLEARED_ERROR_AMOUNT => X_CLEARED_ERROR_AMOUNT,
352 X_CLEARED_CHARGE_AMOUNT => X_CLEARED_CHARGE_AMOUNT,
353 X_CLEARED_EXCHANGE_RATE_TYPE => X_CLEARED_EXCHANGE_RATE_TYPE ,
354 X_CLEARED_EXCHANGE_RATE_DATE => X_CLEARED_EXCHANGE_RATE_DATE,
355 X_CLEARED_EXCHANGE_RATE => X_CLEARED_EXCHANGE_RATE,
356 X_NEW_TRX_STATUS => X_NEW_TRX_STATUS,
357 X_CLEARED_BY_FLAG => X_CLEARED_BY_FLAG ,
358 X_LAST_UPDATE_DATE => sysdate,
359 X_LAST_UPDATED_BY => NVL(FND_GLOBAL.user_id,-1),
360 X_LAST_UPDATE_LOGIN => NVL(FND_GLOBAL.user_id,-1),
361 X_STATEMENT_LINE_ID => x_new_stmt_ln_id,
362 X_PASSIN_MODE => X_PASSIN_MODE
363 );
364 END IF;
365
366 -- bug 5203892 do not raise any acctg event for STMT (JEC) cashflows
367 -- when clearing/unclearing (reconciliation/unreconciliation)
368 IF x_source_trxn_type = 'BAT' THEN
369 l_acctg_event := 'CE_BAT_UNCLEARED';
370 --ELSE
371 -- l_acctg_event := 'CE_STMT_CANCELED';
372 --END IF;
373
374 -- RAISE CLEARING/UNCLEARING ACCOUNTING EVENT
375 IF (X_CF_TRX_STATUS in ( 'CLEARED', 'RECONCILED')) THEN
376 RAISE_ACCT_EVENT
377 (X_CASHFLOW_ID => X_CASHFLOW_ID,
378 X_ACCTG_EVENT => l_acctg_event, --'UNCLEARING',
379 X_ACCOUNTING_DATE => X_ACCOUNTING_DATE,
380 X_EVENT_STATUS_CODE => 'UNPROCESSED',
381 X_EVENT_ID => X_EVENT_ID);
382
383 END IF;
384 END IF; --x_source_trxn_type = 'BAT'
385
386 END IF;
387
388 IF l_DEBUG in ('Y', 'C') THEN
389 cep_standard.debug('<<CE_CASHFLOW_PKG.clear_cashflow');
390 END IF;
391 EXCEPTION
392 WHEN OTHERS THEN
393 IF l_DEBUG in ('Y', 'C') THEN
394 cep_standard.debug('EXCEPTION: CE_CASHFLOW_PKG.clear_cashflow');
395 END IF;
396 RAISE;
397 END clear_cashflow;
398
399
400 END CE_CASHFLOW_PKG;