[Home] [Help]
PACKAGE BODY: APPS.CE_JE_CREATION
Source
1 PACKAGE BODY CE_JE_CREATION AS
2 /* $Header: cejecrnb.pls 120.24.12010000.2 2008/08/10 14:27:49 csutaria ship $ */
3
4 --
5 -- Global Variables
6 --
7 --g_p_gl_date VARCHAR2(40);
8 g_p_report_mode CE_LOOKUPS.lookup_code%TYPE;
9 g_request_id NUMBER(15);
10 g_p_bank_branch_id NUMBER(15);
11 g_p_bank_account_id NUMBER(15);
12 g_p_statement_number_from CE_STATEMENT_HEADERS.statement_number%TYPE;
13 g_p_statement_number_to CE_STATEMENT_HEADERS.statement_number%TYPE;
14 g_p_statement_date_from CE_STATEMENT_HEADERS.statement_date%TYPE;
15 g_p_statement_date_to CE_STATEMENT_HEADERS.statement_date%TYPE;
16 g_multi_currency CE_BANK_ACCOUNTS.multi_currency_allowed_flag%TYPE;
17
18 -- Main cursors
19 --
20 -- Cursor for bank accounts as per the
21 -- submitted bank branch parameter
22 --
23 CURSOR bank_branch_cursor (p_bank_branch_id NUMBER,
24 p_bank_account_id NUMBER)IS
25 SELECT
26 ba.bank_account_id,
27 ba.account_owner_org_id
28 FROM ce_bank_accounts ba
29 WHERE
30 ba.bank_branch_id = p_bank_branch_id
31 AND ba.bank_account_id = NVL(p_bank_account_id, ba.bank_account_id);
32 -- AND ba.account_type =
33 -- CE_AUTO_BANK_MATCH.get_security_account_type(ba.account_type);
34
35 --
36 -- Cursor for statement headers as per the
37 -- submitted statement numbers and statement
38 -- dates
39 --
40 CURSOR statement_headers_cursor (p_bank_account_id NUMBER,
41 p_statement_number_from VARCHAR2,
42 p_statement_number_to VARCHAR2,
43 p_statement_date_from DATE,
44 p_statement_date_to DATE) IS
45 SELECT
46 csh.statement_header_id
47 FROM
48 ce_bank_accounts ba,
49 ce_statement_headers csh
50 WHERE
51 ba.bank_account_id = NVL(p_bank_account_id,ba.bank_account_id)
52 AND csh.bank_account_id = ba.bank_account_id
53 AND csh.statement_number BETWEEN
54 NVL(p_statement_number_from,csh.statement_number) AND
55 NVL(p_statement_number_to,csh.statement_number)
56 AND csh.statement_date BETWEEN
57 NVL(p_statement_date_from,csh.statement_date) AND
58 NVL(p_statement_date_to,csh.statement_date);
59
60 --
61 -- Cursor for statement lines
62 --
63 CURSOR statement_lines_cursor (p_statement_header_id NUMBER) IS
64 SELECT
65 sl.rowid,
66 sl.statement_line_id,
67 sl.trx_code_id,
68 sl.amount,
69 sl.status,
70 sl.currency_code,
71 NVL(ba.currency_code, sh.currency_code),
72 sh.statement_date,
73 sh.gl_date,
74 ba.currency_code,
75 sl.effective_date,
76 sl.trx_date,
77 sl.trx_type,
78 sl.original_amount,
79 sl.exchange_rate_type,
80 sl.exchange_rate,
81 sl.exchange_rate_date,
82 --sl.je_status_flag,
83 sl.trx_text,
84 sh.statement_header_id,
85 sh.bank_account_id,
86 jem.gl_account_ccid,
87 jem.search_string_txt,
88 jem.reference_txt,
89 --cc.asset_code_combination_id,
90 sl.bank_trx_number,
91 sl.bank_account_text,
92 sl.customer_text,
93 sl.cashflow_id,
94 -- ba.asset_code_combination_id
95 ba.multi_currency_allowed_flag,
96 jem.trxn_subtype_code_id
97 FROM
98 ce_statement_lines sl,
99 ce_statement_headers sh,
100 -- ap_bank_accounts ba,
101 ce_bank_accounts ba,
102 ce_je_mappings_v jem
103 --ce_bank_acct_uses_all use,
104 --ce_gl_accounts_ccid cc
105 WHERE
106 sh.statement_header_id = p_statement_header_id AND
107 sl.statement_header_id = sh.statement_header_id AND
108 NVL(sh.statement_complete_flag,'N') = 'N' AND
109 ba.bank_account_id = sh.bank_account_id AND
110 -- ba.account_type = 'INTERNAL' AND
111 jem.bank_account_id = sh.bank_account_id AND
112 --cc.bank_acct_use_id = use.bank_acct_use_id AND
113 --use.bank_account_id = sh.bank_account_id AND
114 --use.ce_use_enabled_flag = 'Y' AND
115 sl.trx_code = jem.trx_code AND
116 sl.status = 'UNRECONCILED' AND
117 --NVL(sl.je_status_flag,'S') <> 'C' AND
118 (sl.trx_text like jem.search_string_txt OR
119 jem.search_string_txt is null)
120 ORDER BY
121 sl.statement_line_id, jem.trx_code_id, jem.search_string_txt;
122
123
124
125 FUNCTION body_revision RETURN VARCHAR2 IS
126 BEGIN
127 RETURN '$Revision: 120.24.12010000.2 $';
128 END body_revision;
129
130 FUNCTION spec_revision RETURN VARCHAR2 IS
131 BEGIN
132 RETURN G_spec_revision;
133 END spec_revision;
134
135
136 PROCEDURE log(p_msg varchar2) is
137 BEGIN
138 -- FND_FILE.PUT_LINE(FND_FILE.LOG,p_msg);
139 cep_standard.debug(p_msg);
140 END log;
141
142 /* --------------------------------------------------------------------
143 | PRIVATE FUNCITON |
144 | valid_accounting_date
145 | DESCRIPTION |
146 | This function returns true if the accouting date
147 | falls in an open or future-enterable period in GL |
148 | |
149 | HISTORY |
150 | 16-SEP-2004 Shaik Vali Created |
151 --------------------------------------------------------------------- */
152 FUNCTION valid_accounting_date (p_accounting_date IN DATE)
153 RETURN BOOLEAN IS
154 l_count NUMBER;
155 BEGIN
156 log('>>valid_accounting_date');
157 SELECT count(*)
158 INTO l_count
159 FROM gl_period_statuses glp,
160 ce_system_parameters sys
161 WHERE glp.set_of_books_id = sys.set_of_books_id
162 AND sys.legal_entity_id = CE_JE_CREATION.ba_legal_entity_id
163 AND glp.closing_status in ('O','F')
164 AND glp.application_id = 101
165 AND glp.adjustment_period_flag = 'N'
166 AND to_char(p_accounting_date,'YYYY/MM/DD') BETWEEN
167 to_char(glp.start_date,'YYYY/MM/DD') AND to_char(glp.end_date,'YYYY/MM/DD');
168
169 IF l_count > 0 THEN
170 RETURN true;
171 END IF;
172 log('<<valid_accounting_date');
173 RETURN FALSE;
174
175 END valid_accounting_date;
176
177 /* --------------------------------------------------------------------
178 | PRIVATE PROCEDURE |
179 | Determine_cleared_date |
180 | DESCRIPTION |
181 | This procedure determines the cleared_date, which is used |
182 | as the accounting date when creating accounting event. |
183 | The cleared_date is determined as |
184 | 1) statement line Effective_date or |
185 | 2) statement line Trx_date |
186 | If the date is not in an open or future period, raise an error |
187 | |
188 | HISTORY |
189 | 28-JUL-2004 xxwang Created |
190 --------------------------------------------------------------------- */
191 PROCEDURE Determine_cleared_date (p_result IN OUT NOCOPY VARCHAR2) IS
192 BEGIN
193 log('>>Determine_cleared_date');
194 IF CE_JE_CREATION.csl_effective_date IS NOT NULL THEN
195 CE_JE_CREATION.cf_cleared_date := CE_JE_CREATION.csl_effective_date;
196 log('cleared date sl effective date: ' || CE_JE_CREATION.cf_cleared_date);
197 ELSE
198 CE_JE_CREATION.cf_cleared_date := CE_JE_CREATION.csl_trx_date;
199 log('cleared date sl trx date: ' || CE_JE_CREATION.cf_cleared_date);
200 END IF;
201
202 IF valid_accounting_date(CE_JE_CREATION.cf_cleared_date) THEN
203 p_result := 'S';
204 log('cleared date is in a open or future period');
205 ELSE
206 p_result := 'F';
207 log('cleared date is not in an open or future period');
208 END IF;
209 log('<<Determine_cleared_date');
210 EXCEPTION
211 WHEN OTHERS THEN
212 log('Exception in Determine_cleared_date');
213 RAISE;
214 END Determine_cleared_date;
215
216
217 /* --------------------------------------------------------------------
218 | PRIVATE PROCEDURE |
219 | Valid_GL_account
220 | DESCRIPTION |
221 | This procedure validates that the GL account is valid.
222 | |
223 | HISTORY |
224 | 16-SEP-2004 Shaik Vali Created |
225 --------------------------------------------------------------------- */
226 PROCEDURE Validate_GL_account(p_gl_account_ccid IN NUMBER,
227 p_result IN OUT NOCOPY VARCHAR2) IS
228 l_enabled_flag CHAR(1);
229 l_detail_posting_allowed_flag CHAR(1);
230 l_start_date_active DATE;
231 l_end_date_active DATE;
232 l_count NUMBER;
233 BEGIN
234 p_result := 'S';
235 log('>>Validate_GL_account');
236 SELECT count(1)
237 INTO l_count
238 FROM
239 gl_code_combinations
240 WHERE
241 code_combination_id = p_gl_account_ccid;
242 IF l_count =1 THEN
243 p_result := 'S';
244 ELSE
245 p_result := 'E';
246 END IF;
247 log('<<Validate_GL_account');
248 EXCEPTION
249 WHEN OTHERS THEN
250 p_result := 'E';
251 log('Exception Validate_GL_account');
252 log(SQLCODE || substr(SQLERRM, 1, 100));
253 RAISE;
254 END Validate_GL_account;
255
256 /* --------------------------------------------------------------------
257 | PRIVATE PROCEDURE |
258 | Initialize_CF_data
259 | DESCRIPTION |
260 | This procedure sets the CF variables to NULL
261 | |
262 | HISTORY |
263 | 16-SEP-2004 Shaik Vali Created |
264 --------------------------------------------------------------------- */
265 PROCEDURE Initialize_CF_data IS
266 BEGIN
267 CE_JE_CREATION.cf_ledger_id := NULL;
268 CE_JE_CREATION.cf_legal_entity_id := NULL;
269 CE_JE_CREATION.cf_bank_account_id := NULL;
270 CE_JE_CREATION.cf_direction := NULL;
271 CE_JE_CREATION.cf_currency_code := NULL;
272 CE_JE_CREATION.cf_cashflow_date := NULL;
273 CE_JE_CREATION.cf_cashflow_amount := NULL;
274 CE_JE_CREATION.cf_description := NULL;
275 CE_JE_CREATION.cf_trxn_reference_number := NULL;
276 CE_JE_CREATION.cf_bank_trxn_number := NULL;
277 CE_JE_CREATION.cf_source_trxn_type := NULL;
278 CE_JE_CREATION.cf_statement_line_id := NULL;
279 CE_JE_CREATION.cf_actual_value_date := NULL;
280 CE_JE_CREATION.cf_offset_ccid := NULL;
281 CE_JE_CREATION.cf_status_code := NULL;
282 CE_JE_CREATION.cf_cleared_date := NULL;
283 CE_JE_CREATION.cf_cleared_amount := NULL;
284 CE_JE_CREATION.cf_cleared_exchange_rate := NULL;
285 CE_JE_CREATION.cf_cleared_exchange_date := NULL;
286 CE_JE_CREATION.cf_cleared_exchange_rate_type := NULL;
287 CE_JE_CREATION.cf_base_amount := NULL;
288 CE_JE_CREATION.cf_reference_text := NULL;
289 CE_JE_CREATION.cf_source_trxn_subtype_code_id := NULL;
290 CE_JE_CREATION.cf_bank_account_text := NULL;
291 CE_JE_CREATION.cf_customer_text := NULL;
292
293 END Initialize_CF_data;
294
295
296 /* ---------------------------------------------------------------------|
297 | PRIVATE FUNCTION |
298 | Currency_type |
299 | DESCRIPTION |
300 | This function returns the currency type: |
301 | DOMESTIC, INTERNATIONAL, FOREIGN. |
302 | |
303 | HISTORY |
304 | 28-JUL-2004 xxwang Created |
305 --------------------------------------------------------------------- */
306 FUNCTION Currency_type RETURN VARCHAR2 IS
307 l_type VARCHAR2(30);
308 BEGIN
309 log('>> Currency_type');
310
311 IF (CE_JE_CREATION.sys_currency_code = CE_JE_CREATION.ba_currency_code) THEN
312 IF (CE_JE_CREATION.ba_currency_code = NVL(CE_JE_CREATION.csl_currency_code, CE_JE_CREATION.csh_currency_code)) THEN
313 l_type := 'DOMESTIC';
314 ELSE
315 l_type := 'INTERNATIONAL';
316 END IF;
317 ELSE
318 l_type := 'FOREIGN';
319 END IF;
320 return l_type;
321 log('<< Currency_type');
322
323 END Currency_type;
324
325 /* --------------------------------------------------------------------
326 | PRIVATE PROCEDURE |
327 | Validate_Multi_Currency
328 | DESCRIPTION |
329 | This procedure validates that if statement line currency is
330 | different from bank account currency which is also functional |
331 | currency then bank account should be multi currency enabled |
332 | HISTORY |
333 | 31-May-2006 Jinesh Kumar Created |
334 --------------------------------------------------------------------- */
335 PROCEDURE Validate_Multi_Currency(p_result IN OUT NOCOPY VARCHAR2) IS
336 l_multi VARCHAR2(1);
337 BEGIN
338 p_result := 'S';
339 IF (Currency_Type = 'INTERNATIONAL') THEN
340 IF (nvl(g_multi_currency,'N') = 'Y') THEN
341 p_result := 'S';
342 ELSE
343 p_result := 'E';
344 END IF;
345 END IF;
346 EXCEPTION
347 WHEN OTHERS THEN
348 p_result := 'E';
349 RAISE;
350 END Validate_Multi_Currency;
351
352 /* --------------------------------------------------------------------
353 | PRIVATE PROCEDURE |
354 | Determine_exchnage_info |
355 | DESCRIPTION |
356 | This procedure determines the cleared exchange info as follows: |
357 | 1) Domestic and International: |
358 | these fields are null; |
359 | 2) Foreign: |
360 | i) use stmt line exchange info; |
361 | ii) if stmt line has no exchange info, get type and date |
362 | from sys param, and calculate rate |
363 | |
364 | HISTORY |
365 | 28-JUL-2004 xxwang Created |
366 --------------------------------------------------------------------- */
367 PROCEDURE Determine_exchange_info (p_result IN OUT NOCOPY VARCHAR2) IS
368 l_xchange_rate_date VARCHAR2(10); --CE_SYSTEM_PARAMETERS.exchange_rate_date%TYPE;
369 l_xchange_rate_type GL_DAILY_RATES.conversion_type%TYPE;
370 l_xchange_rate GL_DAILY_RATES.conversion_rate%TYPE;
371 precision NUMBER;
372 ext_precision NUMBER;
373 min_acct_unit NUMBER;
374 BEGIN
375
376 p_result := 'S';
377
378 IF (Currency_type = 'FOREIGN') THEN
379 IF CE_JE_CREATION.csl_exchange_rate is NOT NULL THEN
380 CE_JE_CREATION.cf_cleared_exchange_rate := CE_JE_CREATION.csl_exchange_rate;
381 CE_JE_CREATION.cf_cleared_exchange_date := CE_JE_CREATION.csl_exchange_rate_date;
382 CE_JE_CREATION.cf_cleared_exchange_rate_type := CE_JE_CREATION.csl_exchange_rate_type;
383 -- Bug 6980331: Cashflow amount was not being set for FOREIGN
384 -- currency_type when the exchange rate was given manually.
385 CE_JE_CREATION.cf_cashflow_amount := CE_JE_CREATION.csl_amount;
386 ELSE
387 l_xchange_rate_date := CE_JE_CREATION.sys_exchange_rate_date;
388 log('sys xchange_rate_date='||l_xchange_rate_date);
389 CE_JE_CREATION.cf_cleared_exchange_rate_type := CE_JE_CREATION.sys_exchange_rate_type;
390 IF (l_xchange_rate_date = 'CFD') THEN
391 CE_JE_CREATION.cf_cleared_exchange_date :=
392 CE_JE_CREATION.csl_trx_date;
393 ELSIF (l_xchange_rate_date = 'BSG') THEN
394 CE_JE_CREATION.cf_cleared_exchange_date :=
395 CE_JE_CREATION.cf_cleared_date;
396 ELSIF (l_xchange_rate_date = 'CLD') THEN
397 CE_JE_CREATION.cf_cleared_exchange_date :=
398 CE_JE_CREATION.cf_cleared_date; -- CE_JE_CREATION.cf_cleared_date has already been
399 -- determined at this point.
400 ELSIF (l_xchange_rate_date = 'BSD') THEN
401 CE_JE_CREATION.cf_cleared_exchange_date :=
402 CE_JE_CREATION.csh_statement_date;
403 ELSE
404 -- error: exchange date cannot be determined
405 p_result := 'F';
406 END IF;
407
408 IF (p_result <> 'F') THEN
409 log('calling gl_currency_api');
410 log('>>Determine_exchange_info');
411 log('ba_curr='||ce_je_Creation.ba_currency_code);
412 log('sys_curr='||ce_je_creation.sys_currency_code);
413 log('cf_clared_ex_date='||ce_je_creation.cf_cleared_exchange_date);
414 log('stmt_line_id='||ce_je_creation.csl_statement_line_id);
415 log('cf_cleared_ex_type='||ce_je_creation.cf_cleared_exchange_rate_type);
416
417 CE_JE_CREATION.cf_cleared_exchange_rate :=
418 gl_currency_api.get_rate(CE_JE_CREATION.ba_currency_code,
419 CE_JE_CREATION.sys_currency_code,
420 CE_JE_CREATION.cf_cleared_exchange_date,
421 CE_JE_CREATION.cf_cleared_exchange_rate_type);
422 CE_JE_CREATION.cf_cashflow_amount := CE_JE_CREATION.csl_amount;
423 END IF;
424 END IF;
425 ELSE -- domestic and international
426 CE_JE_CREATION.cf_cleared_exchange_rate := null;
427 CE_JE_CREATION.cf_cleared_exchange_date := null;
428 CE_JE_CREATION.cf_cleared_exchange_rate_type := null;
429 --Bug 5016835
430 IF (currency_type = 'INTERNATIONAL') THEN
431 CE_JE_CREATION.cf_cashflow_amount :=
432 CE_JE_CREATION.csl_original_amount;
433 IF (CE_JE_CREATION.cf_Cashflow_amount is null) then
434 IF (CE_JE_CREATION.csl_exchange_rate IS NOT NULL) THEN
435
436 --bug5328385
437 If (CE_JE_CREATION.csl_exchange_rate_type <> 'User') THEN
438 CE_JE_CREATION.cf_cashflow_amount:= gl_currency_api.convert_amount(
439 CE_JE_CREATION.sys_currency_code,
440 CE_JE_CREATION.csl_currency_code,
441 nvl(CE_JE_CREATION.csl_exchange_rate_date,
442 CE_JE_CREATION.csl_trx_date),
443 CE_JE_CREATION.csl_exchange_rate_type,
444 CE_JE_CREATION.csl_amount);
445 ELSE
446 fnd_currency.get_info(CE_JE_CREATION.csl_currency_code,
447 precision, ext_precision, min_acct_unit);
448 CE_JE_CREATION.cf_cashflow_amount :=
449 round(CE_JE_CREATION.csl_amount/CE_JE_CREATION.csl_exchange_rate,precision);
450 END IF;
451 ELSE
452 p_result := 'F';
453 END IF;
454 END IF;
455 ELSE
456 CE_JE_CREATION.cf_cashflow_amount := CE_JE_CREATION.csl_amount;
457 END IF;
458 END IF;
459 log('<<Determine_exchange_info');
460 EXCEPTION
461 WHEN OTHERS THEN
462 log('EXCEPTION in Determine_exchange_info');
463 log(SQLCODE || substr(SQLERRM, 1, 100));
464 RAISE;
465 END Determine_exchange_info;
466
467
468 /* --------------------------------------------------------------------
469 | PRIVATE PROCEDURE |
470 | Determine_base_amount |
471 | DESCRIPTION |
472 | This procedure determines the base amount as follows: |
473 | 1) Domestic and International: |
474 | statement_lines.amount; |
475 | 2) Foreign: |
476 | statement_lines.amount/exchange_rate
477 | |
478 | HISTORY |
479 | 28-JUL-2004 xxwang Created |
480 --------------------------------------------------------------------- */
481 PROCEDURE Determine_base_amount IS
482 BEGIN
483 log('>>Determine_base_amount');
484 IF Currency_type = 'FOREIGN' THEN
485 CE_JE_CREATION.cf_base_amount :=
486 CE_JE_CREATION.csl_amount / CE_JE_CREATION.cf_cleared_exchange_rate;
487 ELSE
488 CE_JE_CREATION.cf_base_amount := CE_JE_CREATION.csl_amount;
489 END IF;
490 log('>>Determine_base_amount');
491 END Determine_base_amount;
492
493
494 /* ---------------------------------------------------------------------|
495 | PRIVATE PROCEDURE |
496 | Populate_CF_data |
497 | DESCRIPTION |
498 | This procedure gathers the data required to put into |
499 | the CE_CASHFLOWS table. |
500 | |
501 | HISTORY |
502 | 28-JUL-2004 xxwang Created |
503 --------------------------------------------------------------------- */
504 PROCEDURE Populate_CF_data IS
505 BEGIN
506 log('>> Populate_CF_data');
507 CE_JE_CREATION.cf_ledger_id := CE_JE_CREATION.sys_sob_id;
508 CE_JE_CREATION.cf_legal_entity_id := CE_JE_CREATION.ba_legal_entity_id;
509 CE_JE_CREATION.cf_bank_account_id := CE_JE_CREATION.csh_bank_account_id;
510 IF (CE_JE_CREATION.csl_trx_type = 'CREDIT') or
511 (CE_JE_CREATION.csl_trx_type = 'MISC_CREDIT') THEN
512 CE_JE_CREATION.cf_direction := 'RECEIPT';
513 ELSIF (CE_JE_CREATION.csl_trx_type = 'DEBIT') or
514 (CE_JE_CREATION.csl_trx_type = 'MISC_DEBIT') THEN
515 CE_JE_CREATION.cf_direction := 'PAYMENT';
516 END IF;
517 IF currency_type = 'INTERNATIONAL' THEN
518 CE_JE_CREATION.cf_currency_code := CE_JE_CREATION.csl_currency_code;
519 ELSE
520 CE_JE_CREATION.cf_currency_code := CE_JE_CREATION.csh_currency_code;
521 END IF;
522 CE_JE_CREATION.cf_cashflow_date := CE_JE_CREATION.csl_trx_date;
523 CE_JE_CREATION.cf_description := CE_JE_CREATION.csl_trx_text;
524 CE_JE_CREATION.cf_bank_trxn_number := CE_JE_CREATION.csl_bank_trx_number;
525 CE_JE_CREATION.cf_source_trxn_type := 'STMT';
526 CE_JE_CREATION.cf_statement_line_id := CE_JE_CREATION.csl_statement_line_id;
527 CE_JE_CREATION.cf_actual_value_date := NVL(CE_JE_CREATION.csl_effective_date,
528 CE_JE_CREATION.csl_trx_date);
529 CE_JE_CREATION.cf_offset_ccid := CE_JE_CREATION.jem_gl_account_ccid;
530 CE_JE_CREATION.cf_status_code := 'CLEARED';
531 CE_JE_CREATION.cf_cleared_amount := CE_JE_CREATION.csl_amount;
532 CE_JE_CREATION.cf_source_trxn_subtype_code_id := CE_JE_CREATION.jem_trxn_subtype_code_id;
533 CE_JE_CREATION.cf_reference_text := CE_JE_CREATION.jem_reference_txt;
534 CE_JE_CREATION.cf_bank_account_text := CE_JE_CREATION.csl_bank_account_text;
535 CE_JE_CREATION.cf_customer_text := CE_JE_CREATION.csl_customer_text;
536
537 log('<< Populate_CF_data');
538 EXCEPTION
539 WHEN OTHERS THEN
540 log('EXCEPTION is Populate_CF_data');
541 log(SQLCODE || substr(SQLERRM, 1, 100));
542 RAISE;
543 END Populate_CF_data;
544
545
546 /* ---------------------------------------------------------------------|
547 | PRIVATE PROCEDURE |
548 | Populate_CF_table |
549 | DESCRIPTION |
550 | This procedure inserts data into CE_CASHFLOWS table |
551 | |
552 | HISTORY |
553 | 29-JUL-2004 xxwang Created |
554 --------------------------------------------------------------------- */
555 PROCEDURE Populate_CF_table (x_cashflow_id OUT NOCOPY NUMBER) IS
556 x_rowid VARCHAR2(1000);
557 BEGIN
558 log('>> Populate_CF_table');
559 CE_CASHFLOWS_PKG.insert_row (
560 x_rowid,
561 x_cashflow_id,
562 CE_JE_CREATION.cf_ledger_id,
563 CE_JE_CREATION.cf_legal_entity_id,
564 CE_JE_CREATION.cf_bank_account_id,
565 CE_JE_CREATION.cf_direction,
566 CE_JE_CREATION.cf_currency_code,
567 CE_JE_CREATION.cf_cashflow_date,
568 CE_JE_CREATION.cf_cashflow_amount,
569 CE_JE_CREATION.cf_base_amount,
570 CE_JE_CREATION.cf_description,
571 null, -- cashflow_exchange_rate
572 null, -- cashflow_exchange_date
573 null, -- cashflow_exchange_rate_type
574 CE_JE_CREATION.cf_trxn_reference_number,
575 CE_JE_CREATION.cf_bank_trxn_number,
576 CE_JE_CREATION.cf_source_trxn_type,
577 CE_JE_CREATION.cf_source_trxn_subtype_code_id,
578 CE_JE_CREATION.cf_statement_line_id,
579 CE_JE_CREATION.cf_actual_value_date,
580 null, -- counterparty_party_id
581 null, -- counterparty_bank_account_id
582 CE_JE_CREATION.cf_offset_ccid,
583 CE_JE_CREATION.cf_status_code,
584 CE_JE_CREATION.cf_cleared_date,
585 CE_JE_CREATION.cf_cleared_amount,
586 CE_JE_CREATION.cf_cleared_exchange_rate,
587 CE_JE_CREATION.cf_cleared_exchange_date,
588 CE_JE_CREATION.cf_cleared_exchange_rate_type,
589 null, -- clearing_charges_amount
590 null, -- clearing_error_amount
591 null, -- cleared_by_flag
592 CE_JE_CREATION.cf_reference_text,
593 CE_JE_CREATION.cf_bank_account_text,
594 CE_JE_CREATION.cf_customer_text,
595 NVL(FND_GLOBAL.user_id,-1),
596 sysdate,
597 NVL(FND_GLOBAL.user_id,-1),
598 sysdate,
599 NVL(FND_GLOBAL.user_id,-1));
600 log('<<Populate_CF_table');
601 EXCEPTION
602 WHEN OTHERS THEN
603 log('Exception in Populate_CF_table');
604 log(SQLCODE || substr(SQLERRM, 1, 100));
605 RAISE;
606 END Populate_CF_table;
607
608
609
610 /* ---------------------------------------------------------------------
611 | PRIVATE PROCEDURE |
612 | lock_statement_line |
613 | |
614 | DESCRIPTION |
615 | Lock the statement line before processing |
616 | |
617 | HISTORY |
618 | 26-SEP-2004 Shaik Vali Created |
619 |--------------------------------------------------------------------- */
620 FUNCTION lock_statement_line RETURN BOOLEAN IS
621 l_dummy NUMBER;
622 BEGIN
623 log('>>lock_statement_line');
624 SELECT 1
625 INTO l_dummy
626 FROM
627 ce_statement_lines
628 WHERE rowid = CE_JE_CREATION.csl_rowid
629 FOR UPDATE OF je_status_flag NOWAIT;
630
631 RETURN true;
632 log('<<lock_statement_line');
633 EXCEPTION
634 WHEN OTHERS THEN
635 log('Exception in lock_statement_line');
636 log(SQLCODE || substr(SQLERRM, 1, 100));
637 RETURN false;
638 END;
639
640
641 /* --------------------------------------------------------------------
642 | PRIVATE PROCEDURE |
643 | Update_statement_line
644 | DESCRIPTION
645 | This procedure updates the cashflow_id
646 | |
647 | HISTORY |
648 | 16-SEP-2004 Shaik Vali Created |
649 --------------------------------------------------------------------- */
650 PROCEDURE Update_statement_line(p_statement_line_id IN NUMBER,
651 p_cashflow_id IN NUMBER,
652 p_je_status_flag IN VARCHAR2) IS
653 BEGIN
654 UPDATE ce_statement_lines
655 SET cashflow_id = p_cashflow_id,
656 je_status_flag = p_je_status_flag
657 WHERE statement_line_id = p_statement_line_id;
658 END Update_statement_line;
659
660
661 /* --------------------------------------------------------------------
662 | PRIVATE PROCEDURE |
663 | Process_statement_line |
664 | DESCRIPTION |
665 | This procedure processes each statement line: |
666 | 1)Validate and Identify accounting date |
667 | 2)Validate the GL account |
668 | 3)If PREVIEW mode then only gether the JE data. |
669 | 4)IF ACTUAL mode then gather JE data and also populate |
670 | the gl interface. |
671 | 5)update the statement line |
672 | |
673 | HISTORY |
674 | 21-SEP-2004 Shaik Vali Created |
675 --------------------------------------------------------------------- */
676 PROCEDURE Process_statement_line IS
677 l_result VARCHAR2(50);
678 l_cashflow_id NUMBER;
679 BEGIN
680 log('>>Process_statement_line');
681 Initialize_cf_data;
682 Determine_cleared_date(l_result);
683 IF (l_result = 'S') THEN
684 Determine_exchange_info(l_result);
685 IF(l_result = 'S') THEN
686 Validate_Multi_Currency(l_result);
687 IF (l_result = 'S') THEN
688 Validate_GL_account(CE_JE_CREATION.jem_gl_account_ccid,l_result);
689 IF(l_result = 'S') THEN
690 Determine_base_amount;
691 --
692 -- validations are done. now
693 -- Gather the data for the JE from
694 -- the stmt line.
695 Populate_CF_data;
696 Populate_CF_table(l_cashflow_id);
697 -- insert cashflow_id into statement_line table
698 Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
699 l_cashflow_id,'S');
700 -- create accounting event
701 CE_XLA_ACCT_EVENTS_PKG.create_event(l_cashflow_id,
702 'CE_STMT_RECORDED',
703 null);
704 /* Bug 4997215 -- populated error messages table even for
705 successes with a dummy error message.*/
706 CE_JE_CREATION_ERRORS_PKG.insert_row(
707 CE_JE_CREATION.csh_statement_header_id,
708 CE_JE_CREATION.csl_statement_line_id,
709 'DUMMY',
710 NVL(FND_GLOBAL.user_id,-1),
711 sysdate,
712 sysdate,
713 NVL(FND_GLOBAL.user_id,-1),
714 NVL(FND_GLOBAL.user_id,-1),
715 g_request_id);
716
717 ELSE -- Invalid gl account
718 Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
719 null,'E');
720 CE_JE_CREATION_ERRORS_PKG.insert_row(
721 CE_JE_CREATION.csh_statement_header_id,
722 CE_JE_CREATION.csl_statement_line_id,
723 'CE_INVALID_GL_ACCOUNT',
724 NVL(FND_GLOBAL.user_id,-1),
725 sysdate,
726 sysdate,
727 NVL(FND_GLOBAL.user_id,-1),
728 NVL(FND_GLOBAL.user_id,-1),
729 g_request_id);
730 log('invalid gl account');
731 END IF;
732 ELSE --bank account not multi currency enabled
733 Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
734 null,'E');
735 CE_JE_CREATION_ERRORS_PKG.insert_row(
736 CE_JE_CREATION.csh_statement_header_id,
737 CE_JE_CREATION.csl_statement_line_id,
738 'CE_NOT_MULTI_CURR',
739 NVL(FND_GLOBAL.user_id,-1),
740 sysdate,
741 sysdate,
742 NVL(FND_GLOBAL.user_id,-1),
743 NVL(FND_GLOBAL.user_id,-1),
744 g_request_id);
745 END IF;
746 ELSE -- exchange info cannot be determined
747 Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
748 null,'E');
749 CE_JE_CREATION_ERRORS_PKG.insert_row(
750 CE_JE_CREATION.csh_statement_header_id,
751 CE_JE_CREATION.csl_statement_line_id,
752 'CE_MISSING_USER_RATE',
753 NVL(FND_GLOBAL.user_id,-1),
754 sysdate,
755 sysdate,
756 NVL(FND_GLOBAL.user_id,-1),
757 NVL(FND_GLOBAL.user_id,-1),
758 g_request_id);
759 log('Exchange info cannot be determined.');
760 END IF;
761 ELSE -- invalid cleared date
762 Update_statement_line(CE_JE_CREATION.csl_statement_line_id,
763 null,'E');
764 CE_JE_CREATION_ERRORS_PKG.insert_row(
765 CE_JE_CREATION.csh_statement_header_id,
766 CE_JE_CREATION.csl_statement_line_id,
767 'CE_INVALID_CLEARED_DATE',
768 NVL(FND_GLOBAL.user_id,-1),
769 sysdate,
770 sysdate,
771 NVL(FND_GLOBAL.user_id,-1),
772 NVL(FND_GLOBAL.user_id,-1),
773 g_request_id);
774 log('invalid accounting date');
775 END IF;
776 log('<<Process_statement_line');
777 EXCEPTION
778 WHEN OTHERS THEN
779 log('EXCEPTION in processing statement line');
780 log(SQLCODE || substr(SQLERRM, 1, 100));
781 RAISE;
782 END Process_statement_line;
783
784
785
786
787 /* --------------------------------------------------------------------
788 | PRIVATE PROCEDURE |
789 | create_journal |
790 | |
791 | HISTORY |
792 | 20-SEP-2004 Shaik Vali Created |
793 --------------------------------------------------------------------- */
794 PROCEDURE create_journal (
795 errbuf OUT NOCOPY VARCHAR2,
796 retcode OUT NOCOPY NUMBER,
797 p_bank_branch_id NUMBER,
798 p_bank_account_id NUMBER,
799 p_statement_number_from VARCHAR2,
800 p_statement_number_to VARCHAR2,
801 p_statement_date_from VARCHAR2,
802 p_statement_date_to VARCHAR2) IS
803 --p_gl_date VARCHAR2,
804 --p_report_mode VARCHAR2) IS
805 l_last_statement_line_id CE_STATEMENT_LINES.statement_line_id%TYPE := -1;
806 l_statement_header_id CE_STATEMENT_HEADERS.statement_header_id%TYPE;
807 l_bank_account_id CE_BANK_ACCOUNTS.bank_account_id%TYPE;
808 l_result VARCHAR2(50);
809 l_req_id NUMBER;
810 BEGIN
811 log('>> create_journal');
812
813 -- populate ce_security_profiles_gt table with ce_security_procfiles_v
814 CEP_STANDARD.init_security;
815
816 g_p_bank_branch_id := p_bank_branch_id;
817 g_p_bank_account_id := p_bank_account_id;
818 g_p_statement_number_from := p_statement_number_from;
819 g_p_statement_number_to := p_statement_number_to;
820 g_p_statement_date_from := to_date(p_statement_date_from,'YYYY/MM/DD HH24:MI:SS');
821 g_p_statement_date_to := to_date(p_statement_date_to,'YYYY/MM/DD HH24:MI:SS');
822 --g_p_gl_date := to_char(to_date(p_gl_date,'YYYY/MM/DD HH24:MI:SS'));
823 --g_p_report_mode := p_report_mode;
824 g_request_id := FND_GLOBAL.CONC_REQUEST_ID;
825
826 --
827 -- fetch the bank accounts for the submitted
828 -- bank branch (and) bank account
829 --
830 OPEN bank_branch_cursor(g_p_bank_branch_id,g_p_bank_account_id);
831 LOOP
832 FETCH bank_branch_cursor
833 INTO l_bank_account_id,
834 CE_JE_CREATION.ba_legal_entity_id;
835 EXIT WHEN bank_branch_cursor%NOTFOUND OR
836 bank_branch_cursor%NOTFOUND IS NULL;
837 BEGIN
838 SELECT sob.currency_code,
839 sys.bsc_exchange_date_type,
840 sys.cashflow_exchange_rate_type,
841 --xle.ledger_id
842 sys.set_of_books_id
843 INTO
844 CE_JE_CREATION.sys_currency_code,
845 CE_JE_CREATION.sys_exchange_rate_date,
846 CE_JE_CREATION.sys_exchange_rate_type,
847 CE_JE_CREATION.sys_sob_id
848 FROM
849 ce_system_parameters sys, -- change to base table per BH's request
850 gl_sets_of_books sob,
851 ce_bank_accounts ba
852 --xle_fp_ou_ledger_v xle
853 WHERE
854 sys.set_of_books_id = sob.set_of_books_id
855 AND sys.legal_entity_id = ba.account_owner_org_id
856 AND ba.bank_account_id = l_bank_account_id;
857 EXCEPTION
858 WHEN NO_DATA_FOUND THEN
859 cep_standard.debug('Legal enityt is not set up in System parameters');
860 END;
861
862 --
863 -- fetch the statement headers the bank account
864 -- from the bank cursor and submitted stmt
865 -- numbers and statement dates
866
867 OPEN statement_headers_cursor(l_bank_account_id,
868 g_p_statement_number_from,
869 g_p_statement_number_to,
870 g_p_statement_date_from,
871 g_p_statement_date_to);
872 LOOP
873 FETCH statement_headers_cursor INTO l_statement_header_id;
874 EXIT WHEN statement_headers_cursor%NOTFOUND OR
875 statement_headers_cursor%NOTFOUND IS NULL;
876
877 --
878 -- fetch the statement lines for the header
879 -- from the headers cursor
880 --
881 OPEN statement_lines_cursor(l_statement_header_id);
882 LOOP
883 FETCH statement_lines_cursor INTO
884 CE_JE_CREATION.csl_rowid,
885 CE_JE_CREATION.csl_statement_line_id,
886 CE_JE_CREATION.csl_trx_code_id,
887 CE_JE_CREATION.csl_amount,
888 CE_JE_CREATION.csl_status,
889 CE_JE_CREATION.csl_currency_code,
890 CE_JE_CREATION.csh_currency_code,
891 CE_JE_CREATION.csh_statement_date,
892 CE_JE_CREATION.csh_statement_gl_date,
893 CE_JE_CREATION.ba_currency_code,
894 CE_JE_CREATION.csl_effective_date,
895 CE_JE_CREATION.csl_trx_date,
896 CE_JE_CREATION.csl_trx_type,
897 CE_JE_CREATION.csl_original_amount,
898 CE_JE_CREATION.csl_exchange_rate_type,
899 CE_JE_CREATION.csl_exchange_rate,
900 CE_JE_CREATION.csl_exchange_rate_date,
901 --CE_JE_CREATION.csl_je_status_flag,
902 CE_JE_CREATION.csl_trx_text,
903 CE_JE_CREATION.csh_statement_header_id,
904 CE_JE_CREATION.csh_bank_account_id,
905 CE_JE_CREATION.jem_gl_account_ccid,
906 CE_JE_CREATION.jem_search_string_txt,
907 CE_JE_CREATION.jem_reference_txt,
908 --CE_JE_CREATION.csh_bank_account_ccid,
909 CE_JE_CREATION.csl_bank_trx_number,
910 CE_JE_CREATION.csl_bank_account_text,
911 CE_JE_CREATION.csl_customer_text,
912 CE_JE_CREATION.csl_cashflow_id,
913 g_multi_currency,
914 CE_JE_CREATION.jem_trxn_subtype_code_id;
915 EXIT WHEN statement_lines_cursor%NOTFOUND or
916 statement_lines_cursor%NOTFOUND IS NULL;
917
918
919 IF CE_JE_CREATION.csl_cashflow_id IS NULL THEN
920
921 IF CE_JE_CREATION.sys_sob_id IS NULL THEN
922 CE_JE_CREATION_ERRORS_PKG.insert_row(
923 CE_JE_CREATION.csh_statement_header_id,
924 CE_JE_CREATION.csl_statement_line_id,
925 'CE_NO_BA_LE_IN_SYS',
926 NVL(FND_GLOBAL.user_id,-1),
927 sysdate,
928 sysdate,
929 NVL(FND_GLOBAL.user_id,-1),
930 NVL(FND_GLOBAL.user_id,-1),
931 g_request_id);
932 EXIT;
933 END IF;
934 --
935 -- do not process the same statement line again
936 -- if it matches more than one JE mapping. its
937 -- difficult and performance intensive if we put
938 -- this logic in the sql stmt
939 --
940 IF l_last_statement_line_id <> CE_JE_CREATION.csl_statement_line_id THEN
941 IF(lock_statement_line) THEN
942 Process_statement_line;
943 ELSE
944 CE_JE_CREATION_ERRORS_PKG.insert_row(
945 CE_JE_CREATION.csh_statement_header_id,
946 CE_JE_CREATION.csl_statement_line_id,
947 'CE_LINE_LOCKED',
948 NVL(FND_GLOBAL.user_id,-1),
949 sysdate,
950 sysdate,
951 NVL(FND_GLOBAL.user_id,-1),
952 NVL(FND_GLOBAL.user_id,-1),
953 g_request_id);
954 END IF;
955 l_last_statement_line_id := CE_JE_CREATION.csl_statement_line_id;
956 END IF;
957 END IF;
958 END LOOP; -- statement_lines_cursor
959 CLOSE statement_lines_cursor;
960 END LOOP; -- statement_headers_cursor
961 CLOSE statement_headers_cursor;
962 END LOOP; -- bank_branches_cursor
963 CLOSE bank_branch_cursor;
964
965 l_req_id := FND_REQUEST.SUBMIT_REQUEST('CE',
966 'CEJEEXER',
967 NULL,
968 to_char(sysdate,'YYYY/MM/DD'),
969 FALSE,
970 'P_REQUEST_ID=' || g_request_id,
971 'P_BANK_BRANCH_ID=' || g_p_bank_branch_id,
972 'P_BANK_ACCOUNT_ID=' || g_p_bank_account_id,
973 'P_STAT_NUMBER_FROM=' || g_p_statement_number_from,
974 'P_STAT_NUMBER_TO='||g_p_statement_number_to,
975 'P_STAT_DATE_FROM=' || g_p_statement_date_from,
976 'P_STAT_DATE_TO=' || g_p_statement_date_to);
977
978 COMMIT;
979 log('<< create_journal');
980 EXCEPTION
981 WHEN OTHERS THEN
982 log('Exception in create_journal');
983 log(SQLCODE || substr(SQLERRM, 1, 100));
984 RAISE;
985 END create_journal;
986
987
988 END CE_JE_CREATION;