[Home] [Help]
PACKAGE BODY: APPS.GL_JE_LINES_PKG
Source
1 PACKAGE BODY GL_JE_LINES_PKG as
2 /* $Header: glijelnb.pls 120.18.12010000.2 2008/11/13 22:00:28 sommukhe ship $ */
3
4 --
5 -- PRIVATE VARIABLES
6 --
7
8 -- Keeps track of the last entered and accounted currencies
9 -- and their minimum accountable unit and precision
10 current_entered_currency VARCHAR2(15);
11 entered_precision NUMBER;
12 entered_mau NUMBER;
13 current_accounted_currency VARCHAR2(15);
14 accounted_precision NUMBER;
15 accounted_mau NUMBER;
16
17 -- Keeps track of the current delimiter
18 delim VARCHAR2(1) := '';
19 delim_coa_id NUMBER := '';
20
21 --
22 -- PUBLIC FUNCTIONS
23 --
24 PROCEDURE check_unique(header_id NUMBER, line_num NUMBER,
25 row_id VARCHAR2) IS
26 CURSOR chk_duplicates is
27 SELECT 'Duplicate'
28 FROM GL_JE_LINES jel
29 WHERE jel.je_header_id = header_id
30 AND jel.je_line_num = line_num
31 AND ( row_id is null
32 OR jel.rowid <> row_id);
33 dummy VARCHAR2(100);
34 BEGIN
35 OPEN chk_duplicates;
36 FETCH chk_duplicates INTO dummy;
37
38 IF chk_duplicates%FOUND THEN
39 CLOSE chk_duplicates;
40 fnd_message.set_name('SQLGL', 'GL_DUPLICATE_JE_LINE_NUM');
41 app_exception.raise_exception;
42 END IF;
43
44 CLOSE chk_duplicates;
45
46 EXCEPTION
47 WHEN app_exceptions.application_exception THEN
48 RAISE;
49 WHEN OTHERS THEN
50 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
51 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.check_unique');
52 RAISE;
53 END check_unique;
54
55 PROCEDURE delete_lines(header_id NUMBER) IS
56 BEGIN
57 -- Delete all of the lines in that header
58 DELETE gl_je_lines
59 WHERE je_header_id = header_id;
60
61 -- Delete the reference lines if any.
62 GL_IMPORT_REFERENCES_PKG.delete_lines (header_id);
63
64 EXCEPTION
65 WHEN app_exceptions.application_exception THEN
66 RAISE;
67 WHEN OTHERS THEN
68 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
69 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.delete_lines');
70 RAISE;
71 END delete_lines;
72
73 PROCEDURE update_lines(header_id NUMBER,
74 x_period_name VARCHAR2,
75 x_effective_date DATE,
76 conversion_denom_rate NUMBER,
77 conversion_numer_rate NUMBER,
78 entered_currency VARCHAR2,
79 accounted_currency VARCHAR2,
80 ignore_ignore_flag VARCHAR2,
81 clear_stat VARCHAR2,
82 user_id NUMBER,
83 login_id NUMBER) IS
84 ext_precision NUMBER;
85 entered_divisor NUMBER := 1;
86 accounted_divisor NUMBER := 1;
87 BEGIN
88
89 IF (conversion_numer_rate <> -1) THEN
90 -- Get the minimum accountable unit and the precision
91 -- of the accounted currency
92 IF (accounted_currency <> nvl(current_accounted_currency,
93 '01234567890123456789')
94 ) THEN
95 current_accounted_currency := accounted_currency;
96
97 -- Get the precision and minimum accountable
98 -- unit for the accounted currency
99 fnd_currency.get_info(current_accounted_currency,
100 accounted_precision,
101 ext_precision,
102 accounted_mau);
103 END IF;
104
105 -- Get the minimum accountable unit and the precision
106 -- of the entered currency
107 IF (entered_currency <> nvl(current_entered_currency,
108 '01234567890123456789')
109 ) THEN
110 current_entered_currency := entered_currency;
111
112 IF (current_entered_currency = current_accounted_currency) THEN
113 entered_precision := accounted_precision;
114 entered_mau := accounted_mau;
115 ELSE
116 -- Get the precision and minimum accountable
117 -- unit for the accounted currency
118 fnd_currency.get_info(current_entered_currency,
119 entered_precision,
120 ext_precision,
121 entered_mau);
122 END IF;
123 END IF;
124
125 -- Get the minimum unit for the entered currency
126 IF (entered_mau IS NULL) THEN
127 entered_divisor := power(10, -1 * entered_precision);
128 ELSE
129 entered_divisor := entered_mau;
130 END IF;
131
132 -- Get the minimum unit for the accounted currency
133 IF (accounted_mau IS NULL) THEN
134 accounted_divisor := power(10, -1 * accounted_precision);
135 ELSE
136 accounted_divisor := accounted_mau;
137 END IF;
138 END IF;
139
140 UPDATE gl_je_lines
141 SET period_name = x_period_name,
142 effective_date = x_effective_date,
143 entered_dr = decode(conversion_numer_rate, -1, entered_dr,
144 round(entered_dr/entered_divisor)*entered_divisor),
145 entered_cr = decode(conversion_numer_rate, -1, entered_cr,
146 round(entered_cr/entered_divisor)*entered_divisor),
147 accounted_dr = decode(conversion_numer_rate, -1, accounted_dr,
148 decode(decode(ignore_ignore_flag,
149 'Y', 'N',
150 ignore_rate_flag),
151 'Y', accounted_dr,
152 round((((round(entered_dr/entered_divisor)
153 *entered_divisor)
154 /conversion_denom_rate)
155 *conversion_numer_rate)
156 / accounted_divisor)*accounted_divisor)),
157 accounted_cr = decode(conversion_numer_rate, -1, accounted_cr,
158 decode(decode(ignore_ignore_flag,
159 'Y', 'N',
160 ignore_rate_flag),
161 'Y', accounted_cr,
162 round((((round(entered_cr/entered_divisor)
163 *entered_divisor)
164 /conversion_denom_rate)
165 *conversion_numer_rate)
166 / accounted_divisor)*accounted_divisor)),
167 ignore_rate_flag = decode(ignore_ignore_flag, 'Y', null,
168 ignore_rate_flag),
169 stat_amount = decode(clear_stat, 'Y', null, stat_amount),
170 last_update_date = sysdate,
171 last_updated_by = user_id,
172 last_update_login = login_id
173 WHERE je_header_id = header_id;
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
178 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.update_lines');
179 RAISE;
180 END update_lines;
181
182 PROCEDURE calculate_totals( header_id NUMBER,
183 running_total_dr IN OUT NOCOPY NUMBER,
184 running_total_cr IN OUT NOCOPY NUMBER,
185 running_total_accounted_dr IN OUT NOCOPY NUMBER,
186 running_total_accounted_cr IN OUT NOCOPY NUMBER
187 ) IS
188 CURSOR calc_totals is
189 SELECT sum(nvl(entered_dr, 0)),
190 sum(nvl(entered_cr, 0)),
191 sum(nvl(accounted_dr, 0)),
192 sum(nvl(accounted_cr, 0))
193 FROM GL_JE_LINES jel
194 WHERE jel.je_header_id = header_id;
195 BEGIN
196 OPEN calc_totals;
197 FETCH calc_totals INTO running_total_dr, running_total_cr,
198 running_total_accounted_dr,
199 running_total_accounted_cr;
200
201 IF calc_totals%NOTFOUND THEN
202 CLOSE calc_totals;
203 RAISE NO_DATA_FOUND;
204 END IF;
205
206 CLOSE calc_totals;
207
208 EXCEPTION
209 WHEN OTHERS THEN
210 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
211 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.calculate_totals');
212 RAISE;
213 END calculate_totals;
214
215 FUNCTION header_has_stat(header_id NUMBER) RETURN BOOLEAN IS
216 CURSOR has_stat is
217 SELECT stat_amount
218 FROM GL_JE_LINES jel
219 WHERE jel.je_header_id = header_id
220 AND stat_amount IS NOT NULL;
221 dummy NUMBER;
222 BEGIN
223 OPEN has_stat;
224 FETCH has_stat INTO dummy;
225
226 IF has_stat%NOTFOUND THEN
227 CLOSE has_stat;
228 RETURN(FALSE);
229 END IF;
230
231 CLOSE has_stat;
232 RETURN(TRUE);
233 EXCEPTION
234 WHEN OTHERS THEN
235 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
236 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.header_has_stat');
237 RAISE;
238 END header_has_stat;
239
240 FUNCTION header_has_tax(header_id NUMBER) RETURN BOOLEAN IS
241 CURSOR has_tax is
242 SELECT 'Has tax'
243 FROM GL_JE_LINES jel
244 WHERE jel.je_header_id = header_id
245 AND tax_type_code IS NOT NULL;
246 dummy VARCHAR2(100);
247 BEGIN
248 OPEN has_tax;
249 FETCH has_tax INTO dummy;
250
251 IF has_tax%NOTFOUND THEN
252 CLOSE has_tax;
253 RETURN(FALSE);
254 END IF;
255
256 CLOSE has_tax;
257 RETURN(TRUE);
258 EXCEPTION
259 WHEN OTHERS THEN
260 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
261 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.header_has_tax');
262 RAISE;
263 END header_has_tax;
264
265 PROCEDURE populate_fields(x_ledger_id NUMBER,
266 x_org_id NUMBER,
267 x_coa_id NUMBER,
268 x_ccid NUMBER,
269 x_account_num IN OUT NOCOPY VARCHAR2,
270 x_account_type IN OUT NOCOPY VARCHAR2,
271 x_jgzz_recon_flag IN OUT NOCOPY VARCHAR2,
272 x_tax_enabled VARCHAR2,
273 x_taxable_account IN OUT NOCOPY VARCHAR2,
274 x_stat_enabled VARCHAR2,
275 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
276 x_tax_code_id NUMBER,
277 x_tax_type_code VARCHAR2,
278 x_tax_code IN OUT NOCOPY VARCHAR2) IS
279 dummy_tax_type_code VARCHAR2(1);
280 dummy_tax_code VARCHAR2(50);
281 dummy_tax_code_id NUMBER;
282 dummy_rounding_code VARCHAR2(1);
283 dummy_incl_tax_flag VARCHAR2(1);
284 temp_return_status VARCHAR2(1);
285 err_msg VARCHAR2(2000);
286 BEGIN
287 IF (nvl(delim_coa_id, -1) <> x_coa_id) THEN
288 -- Get the delimiter
289 delim := fnd_flex_apis.get_segment_delimiter(
290 x_application_id => 101,
291 x_id_flex_code => 'GL#',
292 x_id_flex_num => x_coa_id);
293 END IF;
294
295 IF (fnd_flex_keyval.validate_ccid(
296 appl_short_name => 'SQLGL',
297 key_flex_code => 'GL#',
298 structure_number => x_coa_id,
299 combination_id => x_ccid,
300 displayable => 'GL_ACCOUNT')
301 ) THEN
302 x_account_num := replace(fnd_flex_keyval.concatenated_values,
303 '
304 ', delim);
305 x_account_type := fnd_flex_keyval.qualifier_value('GL_ACCOUNT_TYPE');
306
307 IF (x_tax_code_id IS NOT NULL) THEN
308
309 temp_return_status :=null;
310 err_msg :=null;
311
312 zx_gl_tax_options_pkg.get_tax_rate_code
313 ( 1.0,
314 x_tax_type_code,
315 x_tax_code_id,
316 x_tax_code,
317 temp_return_status, err_msg
318 );
319
320 IF (temp_return_status = 'E') THEN
321 FND_MESSAGE.Set_Name('ZX', err_msg);
322 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.populate_fields');
323 -- APP_EXCEPTION.Raise_Exception;
324 ELSIF (temp_return_status = 'U') THEN
325 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
326 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.populate_fields');
327 APP_EXCEPTION.Raise_Exception;
328 END IF;
329 END IF;
330
331 gl_je_lines_pkg.init_acct_dependencies(
332 x_ledger_id => x_ledger_id,
333 x_org_id => x_org_id,
334 x_coa_id => x_coa_id,
335 x_ccid => x_ccid,
336 x_account_num => x_account_num,
337 x_account_type => x_account_type,
338 x_tax_enabled => x_tax_enabled,
339 x_taxable_account => x_taxable_account,
340 x_get_default_tax_info => 'N',
341 x_eff_date => sysdate,
342 x_default_tax_type_code => dummy_tax_type_code,
343 x_default_tax_code => dummy_tax_code,
344 x_default_tax_code_id => dummy_tax_code_id,
345 x_default_rounding_code => dummy_rounding_code,
346 x_default_incl_tax_flag => dummy_incl_tax_flag,
347 x_stat_enabled => x_stat_enabled,
348 x_unit_of_measure => x_unit_of_measure,
349 x_jgzz_recon_flag => x_jgzz_recon_flag);
350
351 ELSE
352 fnd_message.set_encoded(fnd_flex_keyval.encoded_error_message);
353 app_exception.raise_exception;
354 END IF;
355
356 END populate_fields;
357
358 PROCEDURE init_acct_dependencies(
359 x_ledger_id NUMBER,
360 x_org_id NUMBER,
361 x_coa_id NUMBER,
362 x_ccid NUMBER,
363 x_account_num VARCHAR2,
364 x_account_type VARCHAR2,
365 x_tax_enabled VARCHAR2,
366 x_taxable_account IN OUT NOCOPY VARCHAR2,
367 x_get_default_tax_info VARCHAR2,
368 x_eff_date DATE,
369 x_default_tax_type_code IN OUT NOCOPY VARCHAR2,
370 x_default_tax_code IN OUT NOCOPY VARCHAR2,
371 x_default_tax_code_id IN OUT NOCOPY NUMBER,
372 x_default_rounding_code IN OUT NOCOPY VARCHAR2,
373 x_default_incl_tax_flag IN OUT NOCOPY VARCHAR2,
374 x_stat_enabled VARCHAR2,
375 x_unit_of_measure IN OUT NOCOPY VARCHAR2,
376 x_jgzz_recon_flag IN OUT NOCOPY VARCHAR2) IS
377
378 description VARCHAR2(255);
379
380 coa_id NUMBER := x_coa_id;
381 acct_num VARCHAR2(25) := x_account_num;
382
383 dummy NUMBER;
384
385 x_default_taxable_flag VARCHAR2(1);
386 tmp_recon_flag VARCHAR2(1);
387 dummy_return_status VARCHAR2(1);
388 err_msg VARCHAR2(2000);
389 return_status VARCHAR2(30);
390 msg_count NUMBER;
391 msg_data VARCHAR2(2000);
392 le_id NUMBER;
393 p_date DATE;
394 BEGIN
395 -- initialize everything
396 x_unit_of_measure := null;
397
398 -- If tax is enabled, then determine if by default this
399 -- account is tax
400 x_default_taxable_flag := 'N';
401 x_taxable_account := 'N';
402
403 IF (nvl(x_tax_enabled,'N') = 'Y') THEN
404
405 le_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(
406 x_org_id);
407 ZX_API_PUB.set_tax_security_context(1.0, null, null, null,
408 return_status, msg_count, msg_data, x_org_id, le_id,
409 x_eff_date, NULL, NULL, p_date);
410
411 SELECT max(decode(allow_rate_override_flag, 'N', 1, 0))
412 INTO dummy
413 FROM zx_account_rates rates
414 WHERE rates.ledger_id = x_ledger_id
415 AND rates.account_segment_value = x_account_num
416 AND rates.tax_class = 'NON_TAXABLE';
417
418 IF (dummy IS NULL) THEN
419 x_default_taxable_flag := 'Y';
420 x_taxable_account := 'Y';
421 ELSIF (dummy = 0) THEN
422 x_default_taxable_flag := 'N';
423 x_taxable_account := 'Y';
424 ELSE
425 x_default_taxable_flag := 'N';
426 x_taxable_account := 'N';
427 END IF;
428
429 ELSE
430 x_default_taxable_flag := 'N';
431 x_taxable_account := 'Y';
432 END IF;
433
434 -- Get the other defaults, if desired
435 IF ( (x_tax_enabled = 'Y')
436 AND (x_default_taxable_flag = 'Y')
437 AND (x_get_default_tax_info = 'Y')
438 ) THEN
439
440 x_default_tax_type_code := null;
441 x_default_tax_code := null;
442 x_default_tax_code_id := null;
443 x_default_rounding_code := null;
444 x_default_incl_tax_flag := null;
445
446 -- Get the default tax code id
447 x_default_tax_type_code := default_tax_type(
448 'I', x_ledger_id, x_org_id,
449 x_account_num, x_account_type);
450
451 IF (x_default_tax_type_code IS NOT NULL) THEN
452
453 x_default_tax_code_id := to_number(default_tax_code(
454 'I', x_ledger_id, x_org_id,
455 x_account_num,
456 x_default_tax_type_code,
457 x_eff_date));
458
459 -- Get the tax code associated with the default tax code id
460 IF (x_default_tax_code_id IS NOT NULL) THEN
461 dummy_return_status :=null;
462 err_msg :=null;
463
464 zx_gl_tax_options_pkg.get_tax_rate_code
465 ( 1.0,
466 x_default_tax_type_code,
467 x_default_tax_code_id,
468 x_default_tax_code,
469 dummy_return_status, err_msg
470 );
471
472 IF (dummy_return_status = 'E') THEN
473 FND_MESSAGE.Set_Name('ZX', err_msg);
474 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.init_acct_dependencies');
475 -- APP_EXCEPTION.Raise_Exception;
476 ELSIF (dummy_return_status = 'U') THEN
477 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
478 fnd_message.set_token('PROCEDURE', 'gl_je_lines_pkg.init_acct_dependencies');
479 APP_EXCEPTION.Raise_Exception;
480 END IF;
481
482 -- Get the default rounding rule code and default include tax flag
483 get_tax_defaults(x_ledger_id, x_org_id, x_account_num,
484 x_default_tax_type_code, x_default_rounding_code,
485 x_default_incl_tax_flag);
486 END IF;
487 END IF;
488 END IF;
489
490 IF (nvl(x_stat_enabled,'N') = 'Y') THEN
491 BEGIN
492 gl_stat_account_uom_pkg.select_columns(
493 coa_id,
494 acct_num,
495 x_unit_of_measure,
496 description);
497 EXCEPTION
498 WHEN NO_DATA_FOUND THEN
499 x_unit_of_measure := null;
500 WHEN OTHERS THEN
501 RAISE;
502 END;
503 END IF;
504
505 -- If the ccid exists, get the jgzz_recon_flag from it
506 IF (nvl(x_ccid,-1) <> -1) THEN
507 BEGIN
508 SELECT jgzz_recon_flag
509 INTO tmp_recon_flag
510 FROM gl_code_combinations
511 WHERE code_combination_id = x_ccid;
512
513 x_jgzz_recon_flag := tmp_recon_flag;
514 EXCEPTION
515 WHEN NO_DATA_FOUND THEN
516 x_jgzz_recon_flag := null;
517 END;
518 ELSE
519 IF (fnd_flex_keyval.validate_segs(
520 operation => 'CHECK_SEGMENTS',
521 appl_short_name => 'SQLGL',
522 key_flex_code => 'GL#',
523 structure_number=> coa_id,
524 concat_segments => x_account_num,
525 displayable => 'GL_ACCOUNT',
526 validation_date => NULL,
527 allow_nulls => TRUE,
528 allow_orphans => TRUE)) THEN
529 x_jgzz_recon_flag := fnd_flex_keyval.qualifier_value('RECONCILIATION FLAG');
530 ELSE
531 x_jgzz_recon_flag := 'Y';
532 END IF;
533 END IF;
534 END init_acct_dependencies;
535
536 PROCEDURE get_tax_defaults(x_ledger_id NUMBER,
537 x_org_id NUMBER,
538 x_account_value VARCHAR2,
539 x_tax_type_code VARCHAR2,
540 x_default_rounding_code IN OUT NOCOPY VARCHAR2,
541 x_default_incl_tax_flag IN OUT NOCOPY VARCHAR2
542 ) IS
543
544 BEGIN
545
546 x_default_rounding_code := default_rounding_rule(
547 'I', x_ledger_id,
548 x_org_id, x_tax_type_code);
549
550 x_default_incl_tax_flag := default_includes_tax(
551 'I', x_ledger_id,
552 x_org_id, x_account_value, x_tax_type_code);
553
554 END get_tax_defaults;
555
556
557 FUNCTION default_tax_type(output_type IN VARCHAR2,
558 x_ledger_id IN NUMBER,
559 x_org_id IN NUMBER,
560 x_account_value IN VARCHAR2,
561 x_account_type IN VARCHAR2
562 ) RETURN VARCHAR2 IS
563
564 num_defaults NUMBER;
565 default_tax_type VARCHAR2(80) := '';
566 default_tax_type_code VARCHAR2(1) := '';
567 le_id NUMBER;
568 p_date DATE;
569 return_status VARCHAR2(30);
570 msg_count NUMBER;
571 msg_data VARCHAR2(2000);
572 BEGIN
573 default_tax_type := '';
574 default_tax_type_code := '';
575
576 le_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(x_org_id);
577 ZX_API_PUB.set_tax_security_context(1.0, null, null, null,
578 return_status, msg_count, msg_data, x_org_id, le_id,
579 sysdate, NULL, NULL, p_date);
580
581 SELECT count(*), max(decode(rates.tax_class, 'INPUT', 'I', 'OUTPUT', 'O', NULL, 'T'))
582 INTO num_defaults, default_tax_type_code
583 FROM zx_account_rates rates
584 WHERE rates.ledger_id = x_ledger_id
585 AND rates.account_segment_value = x_account_value
586 AND nvl(rates.tax_class,'T') <> 'NON_TAXABLE';
587
588 -- If there are two or more default rows, then don't
589 -- default
590 IF (num_defaults >= 2) THEN
591 default_tax_type := '';
592 default_tax_type_code := '';
593
594 -- If there are no default rows, then default based
595 -- upon the account type
596 ELSIF (num_defaults = 0) THEN
597 default_tax_type_code :='T';
598 END IF;
599
600 IF ( (default_tax_type_code IS NOT NULL)
601 AND (output_type = 'V')
602 ) THEN
603 SELECT l.meaning
604 INTO default_tax_type
605 FROM gl_lookups l
606 WHERE l.lookup_type = 'TAX_TYPE'
607 AND l.lookup_code = default_tax_type_code;
608 END IF;
609
610 IF (output_type = 'I') THEN
611 RETURN(default_tax_type_code);
612 ELSE
613 RETURN(default_tax_type);
614 END IF;
615
616 END default_tax_type;
617
618 FUNCTION default_tax_code(output_type IN VARCHAR2,
619 x_ledger_id IN NUMBER,
620 x_org_id IN NUMBER,
621 x_account_value IN VARCHAR2,
622 x_acct_type IN VARCHAR2,
623 x_eff_date IN DATE
624 ) RETURN VARCHAR2 IS
625
626 temp_regime_code varchar2(30);
627 temp_tax varchar2(50);
628 temp_status varchar2(30);
629 default_tax_code varchar2(50);
630 default_tax_code_id NUMBER(15);
631 temp_rounding_rule varchar2(30);
632 temp_includes_tax_flag varchar2(1);
633 temp_return_status varchar2(1);
634 err_msg varchar2(2000);
635 le_id NUMBER;
636 BEGIN
637 le_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(x_org_id);
638
639 zx_gl_tax_options_pkg.get_default_values(
640 1.0, x_ledger_id, x_org_id, le_id,
641 x_account_value, x_acct_type,
642 x_eff_date,
643 temp_regime_code,
644 temp_tax,
645 temp_status,
646 default_tax_code,
647 default_tax_code_id,
648 temp_rounding_rule,
649 temp_includes_tax_flag,
650 temp_return_status, err_msg);
651
652 IF (temp_return_status = 'E') THEN
653 FND_MESSAGE.Set_Name('ZX', err_msg);
654 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_tax_code');
655 --APP_EXCEPTION.Raise_Exception;
656 ELSIF (temp_return_status = 'U') THEN
657 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
658 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_tax_code');
659 APP_EXCEPTION.Raise_Exception;
660 END IF;
661
662 IF (output_type = 'I') THEN
663 RETURN(to_char(default_tax_code_id));
664 ELSE
665 RETURN(default_tax_code);
666 END IF;
667 END default_tax_code;
668
669
670 FUNCTION default_rounding_rule(output_type IN VARCHAR2,
671 x_ledger_id IN NUMBER,
672 x_org_id IN NUMBER,
673 x_tax_type IN VARCHAR2
674 ) RETURN VARCHAR2 IS
675
676 default_rounding_rule VARCHAR2(80);
677 default_rounding_rule_code VARCHAR2(1);
678 temp_return_status VARCHAR2(1);
679 err_msg VARCHAR2(2000);
680 le_id NUMBER;
681 BEGIN
682 default_rounding_rule := null;
683 default_rounding_rule_code := null;
684 le_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(x_org_id);
685
686 zx_gl_tax_options_pkg.get_rounding_rule_code
687 (
688 1.0,
689 x_ledger_id ,
690 x_org_id ,
691 le_id,
692 x_tax_type,
693 default_rounding_rule_code,
694 temp_return_status,
695 err_msg
696 );
697
698 IF (temp_return_status = 'E') THEN
699 FND_MESSAGE.Set_Name('ZX', err_msg);
700 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_rounding_rule');
701 --APP_EXCEPTION.Raise_Exception;
702 ELSIF (temp_return_status = 'U') THEN
703 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
704 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_rounding_rule');
705 APP_EXCEPTION.Raise_Exception;
706 END IF;
707
708 IF default_rounding_rule_code IS NOT NULL THEN
709 SELECT l.lookup_code, l.meaning
710 INTO default_rounding_rule_code,
711 default_rounding_rule
712 FROM gl_lookups l
713 WHERE l.lookup_type = 'TAX_ROUNDING_RULE'
714 AND l.lookup_code = default_rounding_rule_code;
715 END IF;
716
717 IF (output_type = 'I') THEN
718 RETURN(default_rounding_rule_code);
719 ELSE
720 RETURN(default_rounding_rule);
721 END IF;
722
723 END default_rounding_rule;
724
725
726 FUNCTION default_includes_tax(output_type IN VARCHAR2,
727 x_ledger_id IN NUMBER,
728 x_org_id IN NUMBER,
729 x_account_value IN VARCHAR2,
730 x_tax_type IN VARCHAR2
731 ) RETURN VARCHAR2 IS
732
733 default_includes_tax VARCHAR2(80);
734 default_includes_tax_flag VARCHAR2(1);
735
736 temp_regime_code varchar2(30);
737 temp_tax varchar2(50);
738 temp_status varchar2(30);
739 default_tax_code varchar2(50);
740 default_tax_code_id NUMBER(15);
741 temp_rounding_rule varchar2(30);
742 temp_includes_tax_flag varchar2(1);
743 temp_return_status varchar2(1);
744 err_msg varchar2(2000);
745 le_id NUMBER(15);
746 BEGIN
747 default_includes_tax := null;
748 default_includes_tax_flag := null;
749 le_id := XLE_UTILITIES_GRP.Get_DefaultLegalContext_OU(x_org_id);
750
751 zx_gl_tax_options_pkg.get_default_tax_include_flag
752 (
753 1.0,
754 x_ledger_id,
755 x_org_id,
756 le_id,
757 x_account_value,
758 x_tax_type,
759 temp_includes_tax_flag,
760 temp_return_status,
761 err_msg);
762
763 IF (temp_return_status = 'E') THEN
764 FND_MESSAGE.Set_Name('ZX', err_msg);
765 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_includes_tax');
766 -- APP_EXCEPTION.Raise_Exception;
767 ELSIF (temp_return_status = 'U') THEN
768 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
769 fnd_message.set_token('FUNCTION', 'gl_je_lines_pkg.default_includes_tax');
770 APP_EXCEPTION.Raise_Exception;
771 END IF;
772
773 IF temp_includes_tax_flag IS NOT NULL THEN
774 SELECT l.lookup_code, l.meaning
775 INTO default_includes_tax_flag,
776 default_includes_tax
777 FROM gl_lookups l
778 WHERE l.lookup_type = 'YES/NO'
779 AND l.lookup_code = temp_includes_tax_flag;
780 END IF;
781
782 IF (output_type = 'I') THEN
783 RETURN(default_includes_tax_flag);
784 ELSE
785 RETURN(default_includes_tax);
786 END IF;
787
788 END default_includes_tax;
789
790 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
791 X_Je_Header_Id IN OUT NOCOPY NUMBER,
792 X_Je_Line_Num NUMBER,
793 X_Last_Update_Date DATE,
794 X_Last_Updated_By NUMBER,
795 X_Ledger_id NUMBER,
796 X_Code_Combination_Id NUMBER,
797 X_Period_Name VARCHAR2,
798 X_Effective_Date DATE,
799 X_Status VARCHAR2,
800 X_Creation_Date DATE,
801 X_Created_By NUMBER,
802 X_Last_Update_Login NUMBER,
803 X_Entered_Dr NUMBER,
804 X_Entered_Cr NUMBER,
805 X_Accounted_Dr NUMBER,
806 X_Accounted_Cr NUMBER,
807 X_Description VARCHAR2,
808 X_Reference_1 VARCHAR2,
809 X_Reference_2 VARCHAR2,
810 X_Reference_3 VARCHAR2,
811 X_Reference_4 VARCHAR2,
812 X_Reference_5 VARCHAR2,
813 X_Attribute1 VARCHAR2,
814 X_Attribute2 VARCHAR2,
815 X_Attribute3 VARCHAR2,
816 X_Attribute4 VARCHAR2,
817 X_Attribute5 VARCHAR2,
818 X_Attribute6 VARCHAR2,
819 X_Attribute7 VARCHAR2,
820 X_Attribute8 VARCHAR2,
821 X_Attribute9 VARCHAR2,
822 X_Attribute10 VARCHAR2,
823 X_Attribute11 VARCHAR2,
824 X_Attribute12 VARCHAR2,
825 X_Attribute13 VARCHAR2,
826 X_Attribute14 VARCHAR2,
827 X_Attribute15 VARCHAR2,
828 X_Attribute16 VARCHAR2,
829 X_Attribute17 VARCHAR2,
830 X_Attribute18 VARCHAR2,
831 X_Attribute19 VARCHAR2,
832 X_Attribute20 VARCHAR2,
833 X_Context VARCHAR2,
834 X_Context2 VARCHAR2,
835 X_Invoice_Date DATE,
836 X_Tax_Code VARCHAR2,
837 X_Invoice_Identifier VARCHAR2,
838 X_Invoice_Amount NUMBER,
839 X_No1 VARCHAR2,
840 X_Stat_Amount NUMBER,
841 X_Ignore_Rate_Flag VARCHAR2,
842 X_Context3 VARCHAR2,
843 X_Ussgl_Transaction_Code VARCHAR2,
844 X_Subledger_Doc_Sequence_Id NUMBER,
845 X_Context4 VARCHAR2,
846 X_Subledger_Doc_Sequence_Value NUMBER,
847 X_Reference_6 VARCHAR2,
848 X_Reference_7 VARCHAR2,
849 X_Reference_8 VARCHAR2,
850 X_Reference_9 VARCHAR2,
851 X_Reference_10 VARCHAR2,
852 X_Recon_On_Flag VARCHAR2,
853 X_Recon_Rowid IN OUT NOCOPY VARCHAR2,
854 X_Jgzz_Recon_Status VARCHAR2,
855 X_Jgzz_Recon_Date DATE,
856 X_Jgzz_Recon_Id NUMBER,
857 X_Jgzz_Recon_Ref VARCHAR2,
858 X_Taxable_Line_Flag VARCHAR2,
859 X_Tax_Type_Code VARCHAR2,
860 X_Tax_Code_Id NUMBER,
861 X_Tax_Rounding_Rule_Code VARCHAR2,
862 X_Amount_Includes_Tax_Flag VARCHAR2,
863 X_Tax_Document_Identifier VARCHAR2,
864 X_Tax_Document_Date DATE,
865 X_Tax_Customer_Name VARCHAR2,
866 X_Tax_Customer_Reference VARCHAR2,
867 X_Tax_Registration_Number VARCHAR2,
868 X_Tax_Line_Flag VARCHAR2,
869 X_Tax_Group_Id NUMBER,
870 X_Third_Party_Id VARCHAR2
871 ) IS
872 CURSOR C IS SELECT rowid FROM GL_JE_LINES
873 WHERE je_header_id = X_Je_Header_Id
874 AND je_line_num = X_Je_Line_Num;
875
876 dummy RowId;
877 BEGIN
878
879 -- Get the header id, if it has not yet been retrieved
880 IF (X_Je_Header_Id IS NULL) THEN
881 X_Je_Header_Id := gl_je_headers_pkg.get_unique_id;
882 END IF;
883
884 -- Add any new segment values
885 gl_je_segment_values_pkg.insert_ccid_segment_values(
886 X_Je_Header_Id,
887 X_Code_Combination_Id,
888 X_Last_Updated_By,
889 X_Last_Update_Login);
890
891 INSERT INTO GL_JE_LINES (
892 je_header_id,
893 je_line_num,
894 last_update_date,
895 last_updated_by,
896 ledger_id,
897 code_combination_id,
898 period_name,
899 effective_date,
900 status,
901 creation_date,
902 created_by,
903 last_update_login,
904 entered_dr,
905 entered_cr,
906 accounted_dr,
907 accounted_cr,
908 description,
909 reference_1,
910 reference_2,
911 reference_3,
912 reference_4,
913 reference_5,
914 attribute1,
915 attribute2,
916 attribute3,
917 attribute4,
918 attribute5,
919 attribute6,
920 attribute7,
921 attribute8,
922 attribute9,
923 attribute10,
924 attribute11,
925 attribute12,
926 attribute13,
927 attribute14,
928 attribute15,
929 attribute16,
930 attribute17,
931 attribute18,
932 attribute19,
933 attribute20,
934 context,
935 context2,
936 invoice_date,
937 tax_code,
938 invoice_identifier,
939 invoice_amount,
940 no1,
941 stat_amount,
942 ignore_rate_flag,
943 context3,
944 ussgl_transaction_code,
945 subledger_doc_sequence_id,
946 context4,
947 subledger_doc_sequence_value,
948 reference_6,
949 reference_7,
950 reference_8,
951 reference_9,
952 reference_10,
953 taxable_line_flag,
954 tax_type_code,
955 tax_code_id,
956 tax_rounding_rule_code,
957 amount_includes_tax_flag,
958 tax_document_identifier,
959 tax_document_date,
960 tax_customer_name,
961 tax_customer_reference,
962 tax_registration_number,
963 tax_line_flag,
964 tax_group_id,
965 co_third_party
966 ) VALUES (
967 X_Je_Header_Id,
968 X_Je_Line_Num,
969 X_Last_Update_Date,
970 X_Last_Updated_By,
971 X_Ledger_id,
972 X_Code_Combination_Id,
973 X_Period_Name,
974 X_Effective_Date,
975 X_Status,
976 X_Creation_Date,
977 X_Created_By,
978 X_Last_Update_Login,
979 X_Entered_Dr,
980 X_Entered_Cr,
981 X_Accounted_Dr,
982 X_Accounted_Cr,
983 X_Description,
984 X_Reference_1,
985 X_Reference_2,
986 X_Reference_3,
987 X_Reference_4,
988 X_Reference_5,
989 X_Attribute1,
990 X_Attribute2,
991 X_Attribute3,
992 X_Attribute4,
993 X_Attribute5,
994 X_Attribute6,
995 X_Attribute7,
996 X_Attribute8,
997 X_Attribute9,
998 X_Attribute10,
999 X_Attribute11,
1000 X_Attribute12,
1001 X_Attribute13,
1002 X_Attribute14,
1003 X_Attribute15,
1004 X_Attribute16,
1005 X_Attribute17,
1006 X_Attribute18,
1007 X_Attribute19,
1008 X_Attribute20,
1009 X_Context,
1010 X_Context2,
1011 X_Invoice_Date,
1012 X_Tax_Code,
1013 X_Invoice_Identifier,
1014 X_Invoice_Amount,
1015 X_No1,
1016 X_Stat_Amount,
1017 X_Ignore_Rate_Flag,
1018 X_Context3,
1019 X_Ussgl_Transaction_Code,
1020 X_Subledger_Doc_Sequence_Id,
1021 X_Context4,
1022 X_Subledger_Doc_Sequence_Value,
1023 X_Reference_6,
1024 X_Reference_7,
1025 X_Reference_8,
1026 X_Reference_9,
1027 X_Reference_10,
1028 X_Taxable_Line_Flag,
1029 X_Tax_Type_Code,
1030 X_Tax_Code_Id,
1031 X_Tax_Rounding_Rule_Code,
1032 X_Amount_Includes_Tax_Flag,
1033 X_Tax_Document_Identifier,
1034 X_Tax_Document_Date,
1035 X_Tax_Customer_Name,
1036 X_Tax_Customer_Reference,
1037 X_Tax_Registration_Number,
1038 X_Tax_Line_Flag,
1039 X_Tax_Group_Id,
1040 X_Third_Party_Id
1041 );
1042
1043 OPEN C;
1044 FETCH C INTO X_Rowid;
1045 if (C%NOTFOUND) then
1046 CLOSE C;
1047 Raise NO_DATA_FOUND;
1048 end if;
1049 CLOSE C;
1050
1051 -- Insert a reconciliation row if reconciliation is on
1052 IF (X_Recon_On_Flag = 'Y') THEN
1053 gl_je_lines_recon_pkg.insert_row(
1054 X_Rowid=>X_Recon_Rowid,
1055 X_Je_Header_Id=>X_Je_Header_id,
1056 X_Je_Line_Num=>X_Je_Line_Num,
1057 X_Ledger_Id=>X_Ledger_Id,
1058 X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
1059 X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
1060 X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
1061 X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
1062 X_Last_Update_Date=>X_Last_Update_Date,
1063 X_Last_Updated_By=>X_Last_Updated_By,
1064 X_Last_Update_Login=>X_Last_Update_Login);
1065 END IF;
1066 END Insert_Row;
1067
1068
1069
1070 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
1071 X_Je_Header_Id NUMBER,
1072 X_Je_Line_Num NUMBER,
1073 X_Ledger_id NUMBER,
1074 X_Code_Combination_Id NUMBER,
1075 X_Period_Name VARCHAR2,
1076 X_Effective_Date DATE,
1077 X_Status VARCHAR2,
1078 X_Entered_Dr NUMBER,
1079 X_Entered_Cr NUMBER,
1080 X_Accounted_Dr NUMBER,
1081 X_Accounted_Cr NUMBER,
1082 X_Description VARCHAR2,
1083 X_Reference_1 VARCHAR2,
1084 X_Reference_2 VARCHAR2,
1085 X_Reference_3 VARCHAR2,
1086 X_Reference_4 VARCHAR2,
1087 X_Reference_5 VARCHAR2,
1088 X_Attribute1 VARCHAR2,
1089 X_Attribute2 VARCHAR2,
1090 X_Attribute3 VARCHAR2,
1091 X_Attribute4 VARCHAR2,
1092 X_Attribute5 VARCHAR2,
1093 X_Attribute6 VARCHAR2,
1094 X_Attribute7 VARCHAR2,
1095 X_Attribute8 VARCHAR2,
1096 X_Attribute9 VARCHAR2,
1097 X_Attribute10 VARCHAR2,
1098 X_Attribute11 VARCHAR2,
1099 X_Attribute12 VARCHAR2,
1100 X_Attribute13 VARCHAR2,
1101 X_Attribute14 VARCHAR2,
1102 X_Attribute15 VARCHAR2,
1103 X_Attribute16 VARCHAR2,
1104 X_Attribute17 VARCHAR2,
1105 X_Attribute18 VARCHAR2,
1106 X_Attribute19 VARCHAR2,
1107 X_Attribute20 VARCHAR2,
1108 X_Context VARCHAR2,
1109 X_Context2 VARCHAR2,
1110 X_Invoice_Date DATE,
1111 X_Tax_Code VARCHAR2,
1112 X_Invoice_Identifier VARCHAR2,
1113 X_Invoice_Amount NUMBER,
1114 X_No1 VARCHAR2,
1115 X_Stat_Amount NUMBER,
1116 X_Ignore_Rate_Flag VARCHAR2,
1117 X_Context3 VARCHAR2,
1118 X_Ussgl_Transaction_Code VARCHAR2,
1119 X_Subledger_Doc_Sequence_Id NUMBER,
1120 X_Context4 VARCHAR2,
1121 X_Subledger_Doc_Sequence_Value NUMBER,
1122 X_Reference_6 VARCHAR2,
1123 X_Reference_7 VARCHAR2,
1124 X_Reference_8 VARCHAR2,
1125 X_Reference_9 VARCHAR2,
1126 X_Reference_10 VARCHAR2,
1127 X_Recon_Rowid VARCHAR2,
1128 X_Jgzz_Recon_Status VARCHAR2,
1129 X_Jgzz_Recon_Date DATE,
1130 X_Jgzz_Recon_Id NUMBER,
1131 X_Jgzz_Recon_Ref VARCHAR2,
1132 X_Taxable_Line_Flag VARCHAR2,
1133 X_Tax_Type_Code VARCHAR2,
1134 X_Tax_Code_Id NUMBER,
1135 X_Tax_Rounding_Rule_Code VARCHAR2,
1136 X_Amount_Includes_Tax_Flag VARCHAR2,
1137 X_Tax_Document_Identifier VARCHAR2,
1138 X_Tax_Document_Date DATE,
1139 X_Tax_Customer_Name VARCHAR2,
1140 X_Tax_Customer_Reference VARCHAR2,
1141 X_Tax_Registration_Number VARCHAR2,
1142 X_Tax_Line_Flag VARCHAR2,
1143 X_Tax_Group_Id NUMBER,
1144 X_Third_Party_Id VARCHAR2
1145 ) IS
1146 CURSOR C IS
1147 SELECT *
1148 FROM GL_JE_LINES
1149 WHERE rowid = X_Rowid
1150 FOR UPDATE of Je_Header_Id NOWAIT;
1151 Recinfo C%ROWTYPE;
1152 BEGIN
1153 OPEN C;
1154 FETCH C INTO Recinfo;
1155 if (C%NOTFOUND) then
1156 CLOSE C;
1157 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1158 APP_EXCEPTION.Raise_Exception;
1159 end if;
1160 CLOSE C;
1161
1162 if (
1163 (Recinfo.je_header_id = X_Je_Header_Id)
1164 AND (Recinfo.je_line_num = X_Je_Line_Num)
1165 AND (Recinfo.ledger_id = X_Ledger_id)
1166 AND (Recinfo.code_combination_id = X_Code_Combination_Id)
1167 AND (Recinfo.period_name = X_Period_Name)
1168 AND (Recinfo.effective_date = X_Effective_Date)
1169 AND (Recinfo.status = X_Status)
1170 AND ( (Recinfo.entered_dr = X_Entered_Dr)
1171 OR ( (Recinfo.entered_dr IS NULL)
1172 AND (X_Entered_Dr IS NULL)))
1173 AND ( (Recinfo.entered_cr = X_Entered_Cr)
1174 OR ( (Recinfo.entered_cr IS NULL)
1175 AND (X_Entered_Cr IS NULL)))
1176 AND ( (Recinfo.accounted_dr = X_Accounted_Dr)
1177 OR ( (Recinfo.accounted_dr IS NULL)
1178 AND (X_Accounted_Dr IS NULL)))
1179 AND ( (Recinfo.accounted_cr = X_Accounted_Cr)
1180 OR ( (Recinfo.accounted_cr IS NULL)
1181 AND (X_Accounted_Cr IS NULL)))
1182 AND ( (Recinfo.description = X_Description)
1183 OR ( (rtrim(Recinfo.description,' ') IS NULL)
1184 AND (X_Description IS NULL)))
1185 AND ( (Recinfo.reference_1 = X_Reference_1)
1186 OR ( (rtrim(Recinfo.reference_1,' ') IS NULL)
1187 AND (X_Reference_1 IS NULL)))
1188 AND ( (Recinfo.reference_2 = X_Reference_2)
1189 OR ( (rtrim(Recinfo.reference_2,' ') IS NULL)
1190 AND (X_Reference_2 IS NULL)))
1191 AND ( (Recinfo.reference_3 = X_Reference_3)
1192 OR ( (rtrim(Recinfo.reference_3,' ') IS NULL)
1193 AND (X_Reference_3 IS NULL)))
1194 AND ( (Recinfo.reference_4 = X_Reference_4)
1195 OR ( (rtrim(Recinfo.reference_4,' ') IS NULL)
1196 AND (X_Reference_4 IS NULL)))
1197 AND ( (Recinfo.reference_5 = X_Reference_5)
1198 OR ( (rtrim(Recinfo.reference_5,' ') IS NULL)
1199 AND (X_Reference_5 IS NULL)))
1200 AND ( (Recinfo.attribute1 = X_Attribute1)
1201 OR ( (rtrim(Recinfo.attribute1,' ') IS NULL)
1202 AND (X_Attribute1 IS NULL)))
1203 AND ( (Recinfo.attribute2 = X_Attribute2)
1204 OR ( (rtrim(Recinfo.attribute2,' ') IS NULL)
1205 AND (X_Attribute2 IS NULL)))
1206 AND ( (Recinfo.attribute3 = X_Attribute3)
1207 OR ( (rtrim(Recinfo.attribute3,' ') IS NULL)
1208 AND (X_Attribute3 IS NULL)))
1209 AND ( (Recinfo.attribute4 = X_Attribute4)
1210 OR ( (rtrim(Recinfo.attribute4,' ') IS NULL)
1211 AND (X_Attribute4 IS NULL)))
1212 AND ( (Recinfo.attribute5 = X_Attribute5)
1213 OR ( (rtrim(Recinfo.attribute5,' ') IS NULL)
1214 AND (X_Attribute5 IS NULL)))
1215 AND ( (Recinfo.attribute6 = X_Attribute6)
1216 OR ( (rtrim(Recinfo.attribute6,' ') IS NULL)
1217 AND (X_Attribute6 IS NULL)))
1218 AND ( (Recinfo.attribute7 = X_Attribute7)
1219 OR ( (rtrim(Recinfo.attribute7,' ') IS NULL)
1220 AND (X_Attribute7 IS NULL)))
1221 AND ( (Recinfo.attribute8 = X_Attribute8)
1222 OR ( (rtrim(Recinfo.attribute8,' ') IS NULL)
1223 AND (X_Attribute8 IS NULL)))
1224 AND ( (Recinfo.attribute9 = X_Attribute9)
1225 OR ( (rtrim(Recinfo.attribute9,' ') IS NULL)
1226 AND (X_Attribute9 IS NULL)))
1227 AND ( (Recinfo.attribute10 = X_Attribute10)
1228 OR ( (rtrim(Recinfo.attribute10,' ') IS NULL)
1229 AND (X_Attribute10 IS NULL)))
1230 AND ( (Recinfo.attribute11 = X_Attribute11)
1231 OR ( (rtrim(Recinfo.attribute11,' ') IS NULL)
1232 AND (X_Attribute11 IS NULL)))
1233 AND ( (Recinfo.attribute12 = X_Attribute12)
1234 OR ( (rtrim(Recinfo.attribute12,' ') IS NULL)
1235 AND (X_Attribute12 IS NULL)))
1236 AND ( (Recinfo.attribute13 = X_Attribute13)
1237 OR ( (rtrim(Recinfo.attribute13,' ') IS NULL)
1238 AND (X_Attribute13 IS NULL)))
1239 AND ( (Recinfo.attribute14 = X_Attribute14)
1240 OR ( (rtrim(Recinfo.attribute14,' ') IS NULL)
1241 AND (X_Attribute14 IS NULL)))
1242 AND ( (Recinfo.attribute15 = X_Attribute15)
1243 OR ( (rtrim(Recinfo.attribute15,' ') IS NULL)
1244 AND (X_Attribute15 IS NULL)))
1245 AND ( (Recinfo.attribute16 = X_Attribute16)
1246 OR ( (rtrim(Recinfo.attribute16,' ') IS NULL)
1247 AND (X_Attribute16 IS NULL)))
1248 AND ( (Recinfo.attribute17 = X_Attribute17)
1249 OR ( (rtrim(Recinfo.attribute17,' ') IS NULL)
1250 AND (X_Attribute17 IS NULL)))
1251 AND ( (Recinfo.attribute18 = X_Attribute18)
1252 OR ( (rtrim(Recinfo.attribute18,' ') IS NULL)
1253 AND (X_Attribute18 IS NULL)))
1254 AND ( (Recinfo.attribute19 = X_Attribute19)
1255 OR ( (rtrim(Recinfo.attribute19,' ') IS NULL)
1256 AND (X_Attribute19 IS NULL)))
1257 AND ( (Recinfo.attribute20 = X_Attribute20)
1258 OR ( (rtrim(Recinfo.attribute20,' ') IS NULL)
1259 AND (X_Attribute20 IS NULL)))
1260 AND ( (Recinfo.context = X_Context)
1261 OR ( (rtrim(Recinfo.context,' ') IS NULL)
1262 AND (X_Context IS NULL)))
1263 AND ( (Recinfo.context2 = X_Context2)
1264 OR ( (rtrim(Recinfo.context2,' ') IS NULL)
1265 AND (X_Context2 IS NULL)))
1266 AND ( (Recinfo.invoice_date = X_Invoice_Date)
1267 OR ( (Recinfo.invoice_date IS NULL)
1268 AND (X_Invoice_Date IS NULL)))
1269 AND ( (Recinfo.tax_code = X_Tax_Code)
1270 OR ( (rtrim(Recinfo.tax_code,' ') IS NULL)
1271 AND (X_Tax_Code IS NULL)))
1272 AND ( (Recinfo.invoice_identifier = X_Invoice_Identifier)
1273 OR ( (rtrim(Recinfo.invoice_identifier,' ') IS NULL)
1274 AND (X_Invoice_Identifier IS NULL)))
1275 AND ( (Recinfo.invoice_amount = X_Invoice_Amount)
1276 OR ( (Recinfo.invoice_amount IS NULL)
1277 AND (X_Invoice_Amount IS NULL)))
1278 AND ( (Recinfo.no1 = X_No1)
1279 OR ( (rtrim(Recinfo.no1,' ') IS NULL)
1280 AND (X_No1 IS NULL)))
1281 AND ( (Recinfo.stat_amount = X_Stat_Amount)
1282 OR ( (Recinfo.stat_amount IS NULL)
1283 AND (X_Stat_Amount IS NULL)))
1284 AND ( (Recinfo.ignore_rate_flag = X_Ignore_Rate_Flag)
1285 OR ( (rtrim(Recinfo.ignore_rate_flag,' ') IS NULL)
1286 AND (X_Ignore_Rate_Flag IS NULL)))
1287 AND ( (Recinfo.context3 = X_Context3)
1288 OR ( (rtrim(Recinfo.context3,' ') IS NULL)
1289 AND (X_Context3 IS NULL)))
1290 AND ( (Recinfo.ussgl_transaction_code = X_Ussgl_Transaction_Code)
1291 OR ( (rtrim(Recinfo.ussgl_transaction_code,' ') IS NULL)
1292 AND (X_Ussgl_Transaction_Code IS NULL)))
1293 AND ( (Recinfo.subledger_doc_sequence_id
1294 = X_Subledger_Doc_Sequence_Id)
1295 OR ( (Recinfo.subledger_doc_sequence_id IS NULL)
1296 AND (X_Subledger_Doc_Sequence_Id IS NULL)))
1297 AND ( (Recinfo.context4 = X_Context4)
1298 OR ( (rtrim(Recinfo.context4,' ') IS NULL)
1299 AND (X_Context4 IS NULL)))
1300 AND ( (Recinfo.subledger_doc_sequence_value
1301 = X_Subledger_Doc_Sequence_Value)
1302 OR ( (Recinfo.subledger_doc_sequence_value IS NULL)
1303 AND (X_Subledger_Doc_Sequence_Value IS NULL)))
1304 AND ( (Recinfo.reference_6 = X_Reference_6)
1305 OR ( (rtrim(Recinfo.reference_6,' ') IS NULL)
1306 AND (X_Reference_6 IS NULL)))
1307 AND ( (Recinfo.reference_7 = X_Reference_7)
1308 OR ( (rtrim(Recinfo.reference_7,' ') IS NULL)
1309 AND (X_Reference_7 IS NULL)))
1310 AND ( (Recinfo.reference_8 = X_Reference_8)
1311 OR ( (rtrim(Recinfo.reference_8,' ') IS NULL)
1312 AND (X_Reference_8 IS NULL)))
1313 AND ( (Recinfo.reference_9 = X_Reference_9)
1314 OR ( (rtrim(Recinfo.reference_9,' ') IS NULL)
1315 AND (X_Reference_9 IS NULL)))
1316 AND ( (Recinfo.reference_10 = X_Reference_10)
1317 OR ( (rtrim(Recinfo.reference_10,' ') IS NULL)
1318 AND (X_Reference_10 IS NULL)))
1319 ) then
1320 if (
1321 ( (Recinfo.taxable_line_flag = X_Taxable_Line_Flag)
1322 OR ( (Recinfo.taxable_line_flag IS NULL)
1323 AND (X_Taxable_Line_Flag IS NULL)))
1324 AND ( (Recinfo.tax_type_code = X_Tax_Type_Code)
1325 OR ( (Recinfo.tax_type_code IS NULL)
1326 AND (X_Tax_Type_Code IS NULL)))
1327 AND ( (Recinfo.tax_code_id = X_Tax_Code_Id)
1328 OR ( (Recinfo.tax_code_id IS NULL)
1329 AND (X_Tax_Code_Id IS NULL)))
1330 AND ( (Recinfo.tax_rounding_rule_code = X_Tax_Rounding_Rule_Code)
1331 OR ( (Recinfo.tax_rounding_rule_code IS NULL)
1332 AND (X_Tax_Rounding_Rule_Code IS NULL)))
1333 AND ( (Recinfo.amount_includes_tax_flag
1334 = X_Amount_Includes_Tax_Flag)
1335 OR ( (Recinfo.amount_includes_tax_flag IS NULL)
1336 AND (X_Amount_Includes_Tax_Flag IS NULL)))
1337 AND ( (Recinfo.tax_document_identifier=X_Tax_Document_Identifier)
1338 OR ( (Recinfo.tax_document_identifier IS NULL)
1339 AND (X_Tax_Document_Identifier IS NULL)))
1340 AND ( (Recinfo.tax_document_date=X_Tax_Document_Date)
1341 OR ( (Recinfo.tax_document_date IS NULL)
1342 AND (X_Tax_Document_Date IS NULL)))
1343 AND ( (Recinfo.tax_customer_name=X_Tax_Customer_Name)
1344 OR ( (Recinfo.tax_customer_name IS NULL)
1345 AND (X_Tax_Customer_Name IS NULL)))
1346 AND ( (Recinfo.tax_customer_reference=X_Tax_Customer_Reference)
1347 OR ( (Recinfo.tax_customer_reference IS NULL)
1348 AND (X_Tax_Customer_Reference IS NULL)))
1349 AND ( (Recinfo.tax_registration_number=X_Tax_Registration_Number)
1350 OR ( (Recinfo.tax_registration_number IS NULL)
1351 AND (X_Tax_Registration_Number IS NULL)))
1352 AND ( (Recinfo.tax_line_flag=X_Tax_Line_Flag)
1353 OR ( (Recinfo.tax_line_flag IS NULL)
1354 AND (X_Tax_Line_Flag IS NULL)))
1355 AND ( (Recinfo.tax_group_id=X_Tax_Group_Id)
1356 OR ( (Recinfo.tax_group_id IS NULL)
1357 AND (X_Tax_Group_Id IS NULL)))
1358 AND ( (Recinfo.co_third_party=X_Third_Party_Id)
1359 OR ( (Recinfo.co_third_party IS NULL)
1360 AND (X_Third_Party_Id IS NULL)))
1361 ) then
1362
1363 IF (X_Recon_Rowid IS NOT NULL) THEN
1364 gl_je_lines_recon_pkg.lock_row(
1365 X_RowId => X_Recon_Rowid,
1366 X_Je_Header_Id=>X_Je_Header_id,
1367 X_Je_Line_Num=>X_Je_Line_Num,
1368 X_Ledger_Id=>X_Ledger_Id,
1369 X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
1370 X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
1371 X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
1372 X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref);
1373 END IF;
1374 else
1375 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1376 APP_EXCEPTION.RAISE_EXCEPTION;
1377 end if;
1378 else
1379 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
1380 APP_EXCEPTION.RAISE_EXCEPTION;
1381 end if;
1382 END Lock_Row;
1383
1384 PROCEDURE Update_Row(X_Rowid VARCHAR2,
1385 X_Je_Header_Id NUMBER,
1386 X_Je_Line_Num NUMBER,
1387 X_Last_Update_Date DATE,
1388 X_Last_Updated_By NUMBER,
1389 X_Ledger_Id NUMBER,
1390 X_Code_Combination_Id NUMBER,
1391 X_Period_Name VARCHAR2,
1392 X_Effective_Date DATE,
1393 X_Status VARCHAR2,
1394 X_Last_Update_Login NUMBER,
1395 X_Entered_Dr NUMBER,
1396 X_Entered_Cr NUMBER,
1397 X_Accounted_Dr NUMBER,
1398 X_Accounted_Cr NUMBER,
1399 X_Description VARCHAR2,
1400 X_Reference_1 VARCHAR2,
1401 X_Reference_2 VARCHAR2,
1402 X_Reference_3 VARCHAR2,
1403 X_Reference_4 VARCHAR2,
1404 X_Reference_5 VARCHAR2,
1405 X_Attribute1 VARCHAR2,
1406 X_Attribute2 VARCHAR2,
1407 X_Attribute3 VARCHAR2,
1408 X_Attribute4 VARCHAR2,
1409 X_Attribute5 VARCHAR2,
1410 X_Attribute6 VARCHAR2,
1411 X_Attribute7 VARCHAR2,
1412 X_Attribute8 VARCHAR2,
1413 X_Attribute9 VARCHAR2,
1414 X_Attribute10 VARCHAR2,
1415 X_Attribute11 VARCHAR2,
1416 X_Attribute12 VARCHAR2,
1417 X_Attribute13 VARCHAR2,
1418 X_Attribute14 VARCHAR2,
1419 X_Attribute15 VARCHAR2,
1420 X_Attribute16 VARCHAR2,
1421 X_Attribute17 VARCHAR2,
1422 X_Attribute18 VARCHAR2,
1423 X_Attribute19 VARCHAR2,
1424 X_Attribute20 VARCHAR2,
1425 X_Context VARCHAR2,
1426 X_Context2 VARCHAR2,
1427 X_Invoice_Date DATE,
1428 X_Tax_Code VARCHAR2,
1429 X_Invoice_Identifier VARCHAR2,
1430 X_Invoice_Amount NUMBER,
1431 X_No1 VARCHAR2,
1432 X_Stat_Amount NUMBER,
1433 X_Ignore_Rate_Flag VARCHAR2,
1434 X_Context3 VARCHAR2,
1435 X_Ussgl_Transaction_Code VARCHAR2,
1436 X_Subledger_Doc_Sequence_Id NUMBER,
1437 X_Context4 VARCHAR2,
1438 X_Subledger_Doc_Sequence_Value NUMBER,
1439 X_Reference_6 VARCHAR2,
1440 X_Reference_7 VARCHAR2,
1441 X_Reference_8 VARCHAR2,
1442 X_Reference_9 VARCHAR2,
1443 X_Reference_10 VARCHAR2,
1444 X_Recon_On_Flag VARCHAR2,
1445 X_Recon_Rowid IN OUT NOCOPY VARCHAR2,
1446 X_Jgzz_Recon_Status VARCHAR2,
1447 X_Jgzz_Recon_Date DATE,
1448 X_Jgzz_Recon_Id NUMBER,
1449 X_Jgzz_Recon_Ref VARCHAR2,
1450 X_Taxable_Line_Flag VARCHAR2,
1451 X_Tax_Type_Code VARCHAR2,
1452 X_Tax_Code_Id NUMBER,
1453 X_Tax_Rounding_Rule_Code VARCHAR2,
1454 X_Amount_Includes_Tax_Flag VARCHAR2,
1455 X_Tax_Document_Identifier VARCHAR2,
1456 X_Tax_Document_Date DATE,
1457 X_Tax_Customer_Name VARCHAR2,
1458 X_Tax_Customer_Reference VARCHAR2,
1459 X_Tax_Registration_Number VARCHAR2,
1460 X_Tax_Line_Flag VARCHAR2,
1461 X_Tax_Group_Id NUMBER,
1462 X_Third_Party_Id VARCHAR2
1463 ) IS
1464 BEGIN
1465 -- Add any new segment values
1466 gl_je_segment_values_pkg.insert_ccid_segment_values(
1467 X_Je_Header_Id,
1468 X_Code_Combination_Id,
1469 X_Last_Updated_By,
1470 X_Last_Update_Login);
1471
1472 UPDATE GL_JE_LINES
1473 SET
1474 je_header_id = X_Je_Header_Id,
1475 je_line_num = X_Je_Line_Num,
1476 last_update_date = X_Last_Update_Date,
1477 last_updated_by = X_Last_Updated_By,
1478 ledger_id = X_Ledger_Id,
1479 code_combination_id = X_Code_Combination_Id,
1480 period_name = X_Period_Name,
1481 effective_date = X_Effective_Date,
1482 status = X_Status,
1483 last_update_login = X_Last_Update_Login,
1484 entered_dr = X_Entered_Dr,
1485 entered_cr = X_Entered_Cr,
1486 accounted_dr = X_Accounted_Dr,
1487 accounted_cr = X_Accounted_Cr,
1488 description = X_Description,
1489 reference_1 = X_Reference_1,
1490 reference_2 = X_Reference_2,
1491 reference_3 = X_Reference_3,
1492 reference_4 = X_Reference_4,
1493 reference_5 = X_Reference_5,
1494 attribute1 = X_Attribute1,
1495 attribute2 = X_Attribute2,
1496 attribute3 = X_Attribute3,
1497 attribute4 = X_Attribute4,
1498 attribute5 = X_Attribute5,
1499 attribute6 = X_Attribute6,
1500 attribute7 = X_Attribute7,
1501 attribute8 = X_Attribute8,
1502 attribute9 = X_Attribute9,
1503 attribute10 = X_Attribute10,
1504 attribute11 = X_Attribute11,
1505 attribute12 = X_Attribute12,
1506 attribute13 = X_Attribute13,
1507 attribute14 = X_Attribute14,
1508 attribute15 = X_Attribute15,
1509 attribute16 = X_Attribute16,
1510 attribute17 = X_Attribute17,
1511 attribute18 = X_Attribute18,
1512 attribute19 = X_Attribute19,
1513 attribute20 = X_Attribute20,
1514 context = X_Context,
1515 context2 = X_Context2,
1516 invoice_date = X_Invoice_Date,
1517 tax_code = X_Tax_Code,
1518 invoice_identifier = X_Invoice_Identifier,
1519 invoice_amount = X_Invoice_Amount,
1520 no1 = X_No1,
1521 stat_amount = X_Stat_Amount,
1522 ignore_rate_flag = X_Ignore_Rate_Flag,
1523 context3 = X_Context3,
1524 ussgl_transaction_code = X_Ussgl_Transaction_Code,
1525 subledger_doc_sequence_id = X_Subledger_Doc_Sequence_Id,
1526 context4 = X_Context4,
1527 subledger_doc_sequence_value = X_Subledger_Doc_Sequence_Value,
1528 reference_6 = X_Reference_6,
1529 reference_7 = X_Reference_7,
1530 reference_8 = X_Reference_8,
1531 reference_9 = X_Reference_9,
1532 reference_10 = X_Reference_10,
1533 taxable_line_flag = X_Taxable_Line_Flag,
1534 tax_type_code = X_Tax_Type_Code,
1535 tax_code_id = X_Tax_Code_Id,
1536 tax_rounding_rule_code = X_Tax_Rounding_Rule_Code,
1537 amount_includes_tax_flag = X_Amount_Includes_Tax_Flag,
1538 tax_document_identifier = X_Tax_Document_Identifier,
1539 tax_document_date = X_Tax_Document_Date,
1540 tax_customer_name = X_Tax_Customer_Name,
1541 tax_customer_reference = X_Tax_Customer_Reference,
1542 tax_registration_number = X_Tax_Registration_Number,
1543 tax_line_flag = X_Tax_Line_Flag,
1544 tax_group_id = X_Tax_Group_Id,
1545 co_third_party = X_Third_Party_Id
1546 WHERE rowid = X_rowid;
1547
1548 IF (SQL%NOTFOUND) THEN
1549 Raise NO_DATA_FOUND;
1550 END IF;
1551
1552 -- If no row exists, insert one if reconciliation is now on
1553 IF (X_Recon_Rowid IS NULL) THEN
1554 IF (X_Recon_On_Flag = 'Y') THEN
1555 gl_je_lines_recon_pkg.insert_row(
1556 X_Rowid=>X_Recon_Rowid,
1557 X_Je_Header_Id=>X_Je_Header_id,
1558 X_Je_Line_Num=>X_Je_Line_Num,
1559 X_Ledger_Id=>X_Ledger_Id,
1560 X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
1561 X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
1562 X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
1563 X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
1564 X_Last_Update_Date=>X_Last_Update_Date,
1565 X_Last_Updated_By=>X_Last_Updated_By,
1566 X_Last_Update_Login=>X_Last_Update_Login);
1567 END IF;
1568
1569 -- If a row exists, update it if reconciliation is on. Delete it if
1570 -- reconciliation is off.
1571 ELSE
1572 IF (X_Recon_On_Flag = 'Y') THEN
1573 gl_je_lines_recon_pkg.update_row(
1574 X_Rowid=>X_Recon_Rowid,
1575 X_Je_Header_Id=>X_Je_Header_id,
1576 X_Je_Line_Num=>X_Je_Line_Num,
1577 X_Ledger_Id=>X_Ledger_Id,
1578 X_Jgzz_Recon_Status=>X_Jgzz_Recon_Status,
1579 X_Jgzz_Recon_Date=>X_Jgzz_Recon_Date,
1580 X_Jgzz_Recon_Id=>X_Jgzz_Recon_Id,
1581 X_Jgzz_Recon_Ref=>X_Jgzz_Recon_Ref,
1582 X_Last_Update_Date=>X_Last_Update_Date,
1583 X_Last_Updated_By=>X_Last_Updated_By,
1584 X_Last_Update_Login=>X_Last_Update_Login);
1585 ELSE
1586 gl_je_lines_recon_pkg.delete_row(
1587 X_Rowid=>X_Recon_Rowid);
1588 X_Recon_Rowid := null;
1589 END IF;
1590 END IF;
1591 END Update_Row;
1592
1593 PROCEDURE Delete_Row(X_Rowid VARCHAR2,
1594 X_Recon_Rowid VARCHAR2) IS
1595 CURSOR c_del_line (lv_row_id VARCHAR2 )IS
1596 SELECT je_line_num,je_header_id
1597 FROM GL_JE_LINES
1598 WHERE rowid = lv_row_id ;
1599
1600 lv_header_id number;
1601 lv_line_num number;
1602 BEGIN
1603 OPEN c_del_line ( X_Rowid);
1604 FETCH c_del_line into lv_line_num , lv_header_id ;
1605 CLOSE c_del_line;
1606
1607 DELETE FROM GL_JE_LINES
1608 WHERE rowid = X_Rowid;
1609
1610 if (SQL%NOTFOUND) then
1611 Raise NO_DATA_FOUND;
1612 end if;
1613
1614 -- To delete the reference lines if any in GL_IMPORT_REFERENCES
1615 GL_IMPORT_REFERENCES_PKG.delete_line (lv_header_id ,lv_line_num );
1616
1617 -- Delete any reconciliation row
1618 IF (X_Recon_Rowid IS NOT NULL) THEN
1619 gl_je_lines_recon_pkg.delete_row(
1620 X_Rowid=>X_Recon_Rowid);
1621 END IF;
1622 END Delete_Row;
1623
1624 END GL_JE_LINES_PKG;