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