[Home] [Help]
PACKAGE BODY: APPS.ARP_TRX_DEFAULTS_2
Source
1 PACKAGE BODY ARP_TRX_DEFAULTS_2 AS
2 /* $Header: ARTUDF2B.pls 120.5.12010000.1 2008/07/24 16:57:49 appldev ship $ */
3
4 pg_text_dummy varchar2(10);
5 pg_flag_dummy varchar2(10);
6 pg_number_dummy number;
7 pg_date_dummy date;
8
9 pg_base_curr_code gl_sets_of_books.currency_code%type;
10 pg_base_precision fnd_currencies.precision%type;
11 pg_base_min_acc_unit fnd_currencies.minimum_accountable_unit%type;
12 pg_set_of_books_id ar_system_parameters.set_of_books_id%type;
13
14
15
16 /*===========================================================================+
17 | PROCEDURE |
18 | get_source_default |
19 | |
20 | DESCRIPTION |
21 | Checks a potential batch source default to see if it is valid. |
22 | |
23 | SCOPE - PRIVATE |
24 | |
25 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
26 | arp_util.debug |
27 | |
28 | ARGUMENTS : IN: |
29 | p_batch_source_id |
30 | p_ctt_class |
31 | p_trx_date |
32 | p_trx_number |
33 | OUT: |
34 | p_default_batch_source_id |
35 | p_default_batch_source_name |
36 | p_auto_trx_numbering_flag |
37 | p_batch_source_type |
38 | p_default_cust_trx_type_id |
39 | |
40 | RETURNS : NONE |
41 | |
42 | NOTES |
43 | |
44 | MODIFICATION HISTORY |
45 | 04-NOV-95 Charlie Tomberg Created |
46 | |
47 +===========================================================================*/
48
49 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
50
51 PROCEDURE get_source_default(
52 p_batch_source_id IN
53 ra_batch_sources.batch_source_id%type,
54 p_ctt_class IN
55 ra_cust_trx_types.type%type,
56 p_trx_date IN
57 ra_customer_trx.trx_date%type,
58 p_trx_number IN
59 ra_customer_trx.trx_number%type,
60 p_default_batch_source_id OUT NOCOPY
61 ra_batch_sources.batch_source_id%type,
62 p_default_batch_source_name OUT NOCOPY
63 ra_batch_sources.name%type,
64 p_auto_trx_numbering_flag OUT NOCOPY
65 ra_batch_sources.auto_trx_numbering_flag%type,
66 p_batch_source_type OUT NOCOPY
67 ra_batch_sources.batch_source_type%type,
68 p_copy_doc_number_flag OUT NOCOPY
69 ra_batch_sources.copy_doc_number_flag%type,
70 p_default_cust_trx_type_id OUT NOCOPY
71 ra_cust_trx_types.cust_trx_type_id%type
72 ) IS
73
74
75 BEGIN
76
77 IF PG_DEBUG in ('Y', 'C') THEN
78 arp_util.debug('arp_trx_defaults_2.get_source_default()+');
79 END IF;
80
81
82 IF (p_batch_source_id IS NOT NULL)
83 THEN
84
85 BEGIN
86
87 SELECT bs.name source,
88 bs.batch_source_id batch_source_id,
89 bs.auto_trx_numbering_flag auto_trx_numbering_flag,
90 bs.batch_source_type batch_source_type,
91 bs.default_inv_trx_type default_inv_trx_type,
92 bs.copy_doc_number_flag copy_doc_number_flag
93 INTO p_default_batch_source_name,
94 p_default_batch_source_id,
95 p_auto_trx_numbering_flag,
96 p_batch_source_type,
97 p_default_cust_trx_type_id,
98 p_copy_doc_number_flag
99 FROM ra_cust_trx_types ctt,
100 ra_batch_sources bs
101 WHERE bs.batch_source_id = p_batch_source_id
102 AND bs.default_inv_trx_type = ctt.cust_trx_type_id(+)
103 AND NVL(p_ctt_class, '-99') =
104 DECODE(p_ctt_class,
105 null, '-99',
106 ctt.type(+) )
107 AND NVL(p_trx_date,
108 TRUNC(sysdate))
109 BETWEEN NVL(bs.start_date,
110 nvl(p_trx_date, TRUNC(sysdate)))
111 AND NVL(bs.end_date, NVL(p_trx_date, TRUNC(sysdate)))
112 AND NVL(p_trx_date,
113 TRUNC(sysdate))
114 BETWEEN NVL(ctt.start_date(+),
115 NVL(p_trx_date, trunc(sysdate)))
116 AND NVL(ctt.end_date(+), nvl(p_trx_date, trunc(sysdate)))
117 AND (
118 bs.batch_source_type ='INV'
119 OR p_ctt_class = 'CM'
120 )
121 /* do not show 'DM Reversal' and 'Chargeback' */
122 AND bs.batch_source_id not in (11, 12)
123 AND (
124 p_trx_number IS NULL
125 OR bs.auto_trx_numbering_flag = 'N'
126 );
127
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN NULL;
130 WHEN OTHERS THEN RAISE;
131 END;
132
133 END IF;
134
135 IF PG_DEBUG in ('Y', 'C') THEN
136 arp_util.debug('arp_trx_defaults_2.get_source_default()-');
137 END IF;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 IF PG_DEBUG in ('Y', 'C') THEN
142 arp_util.debug('EXCEPTION: arp_trx_defaults_2.get_source_default()');
143 END IF;
144 RAISE;
145
146 END get_source_default;
147
148
149 /*===========================================================================+
150 | PROCEDURE |
151 | get_type_defaults |
152 | |
153 | DESCRIPTION |
154 | Validates a potential transaction type default value and returns |
155 | items that default from the type if the type is valid. |
156 | |
157 | SCOPE - PRIVATE |
158 | |
159 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
160 | arp_util.debug |
161 | |
162 | ARGUMENTS : IN: |
163 | p_cust_trx_type_id |
164 | p_trx_date |
165 | p_ctt_class |
166 | p_row_id |
167 | p_invoicing_rule_id |
168 | p_rev_recog_run_flag |
169 | p_complete_flag |
170 | p_open_receivables_flag |
171 | p_customer_trx_id |
172 | p_security_inv_enter_flag |
173 | p_security_cm_enter_flag |
174 | p_security_dm_enter_flag |
175 | p_security_commit_enter_flag |
176 | OUT: |
177 | p_default_cust_trx_type_id |
178 | p_default_type_name |
179 | p_default_class |
180 | p_deflt_open_receivables_flag |
181 | p_default_post_to_gl_flag |
182 | p_default_allow_freight_flag |
183 | p_default_creation_sign |
184 | p_default_allow_overapp_flag |
185 | p_deflt_natural_app_only_flag |
186 | p_default_tax_calculation_flag |
187 | p_default_status_code |
188 | p_default_status |
189 | p_default_printing_option_code |
190 | p_default_printing_option |
191 | p_default_term_id |
192 | p_default_term_name |
193 | p_number_of_due_dates |
194 | p_term_due_date |
195 | |
196 | RETURNS : NONE |
197 | |
198 | NOTES |
199 | |
200 | MODIFICATION HISTORY |
201 | 04-NOV-95 Charlie Tomberg Created |
202 | |
203 +===========================================================================*/
204
205 PROCEDURE get_type_defaults(
206 p_cust_trx_type_id IN
207 ra_cust_trx_types.cust_trx_type_id%type,
208 p_trx_date IN
209 ra_customer_trx.trx_date%type,
210 p_ctt_class IN
211 ra_cust_trx_types.type%type,
212 p_row_id IN varchar2,
213 p_invoicing_rule_id IN ra_rules.rule_id%type,
214 p_rev_recog_run_flag IN varchar2,
215 p_complete_flag IN
216 ra_customer_trx.complete_flag%type,
217 p_open_receivables_flag IN
218 ra_cust_trx_types.accounting_affect_flag%type,
219 p_customer_trx_id IN
220 ra_customer_trx.customer_trx_id%type,
221 p_default_cust_trx_type_id OUT NOCOPY
222 ra_cust_trx_types.cust_trx_type_id%type,
223 p_default_type_name OUT NOCOPY
224 ra_cust_trx_types.name%type,
225 p_default_class OUT NOCOPY
226 ra_cust_trx_types.type%type,
227 p_deflt_open_receivables_flag OUT NOCOPY
228 ra_cust_trx_types.accounting_affect_flag%type,
229 p_default_post_to_gl_flag OUT NOCOPY
230 ra_cust_trx_types.post_to_gl%type,
231 p_default_allow_freight_flag OUT NOCOPY
232 ra_cust_trx_types.allow_freight_flag%type,
233 p_default_creation_sign OUT NOCOPY
234 ra_cust_trx_types.creation_sign%type,
235 p_default_allow_overapp_flag OUT NOCOPY
236 ra_cust_trx_types.allow_overapplication_flag%type,
237 p_deflt_natural_app_only_flag OUT NOCOPY
238 ra_cust_trx_types.natural_application_only_flag%type,
239 p_default_tax_calculation_flag OUT NOCOPY
240 ra_cust_trx_types.tax_calculation_flag%type,
241 p_default_status_code OUT NOCOPY
242 ar_lookups.lookup_code%type,
243 p_default_status OUT NOCOPY
244 ar_lookups.meaning%type,
245 p_default_printing_option_code OUT NOCOPY
246 ar_lookups.lookup_code%type,
247 p_default_printing_option OUT NOCOPY
248 ar_lookups.meaning%type,
249 p_default_term_id OUT NOCOPY
250 ra_terms.term_id%type,
251 p_default_term_name OUT NOCOPY
252 ra_terms.name%type,
253 p_number_of_due_dates OUT NOCOPY number,
254 p_term_due_date OUT NOCOPY
255 ra_customer_trx.term_due_date%type,
256 p_security_inv_enter_flag IN
257 varchar2 DEFAULT 'Y',
258 p_security_cm_enter_flag IN
259 varchar2 DEFAULT 'Y',
260 p_security_dm_enter_flag IN
261 varchar2 DEFAULT 'Y',
262 p_security_commit_enter_flag IN
263 varchar2 DEFAULT 'Y'
264 ) IS
265
266 l_number_of_due_dates number;
267 l_default_term_id ra_terms.term_id%type;
268 l_term_due_date ra_customer_trx.term_due_date%type;
269
270 BEGIN
271
272 IF PG_DEBUG in ('Y', 'C') THEN
273 arp_util.debug('arp_trx_defaults_2.get_type_defaults()+');
274 END IF;
275
276
277 IF (p_cust_trx_type_id IS NOT NULL )
278 THEN
279
280 SELECT ctt.cust_trx_type_id cust_trx_type_id,
281 ctt.name name,
282 ctt.type class,
283 ctt.accounting_affect_flag open_receivable_flag,
284 ctt.post_to_gl post_to_gl_flag,
285 ctt.allow_freight_flag allow_freight_flag,
286 ctt.creation_sign creation_sign,
287 allow_overapplication_flag allow_overapplication_flag,
288 natural_application_only_flag natural_application_only_flag,
289 ctt.tax_calculation_flag tax_calculation_flag,
290 ctt.default_status default_status_code,
291 arl_status.meaning default_status,
292 ctt.default_printing_option default_printing_option_code,
293 arl_print.meaning default_printing_option,
294 ctt.default_term default_term_id,
295 rat.name default_term
296 INTO
297 p_default_cust_trx_type_id,
298 p_default_type_name,
299 p_default_class,
300 p_deflt_open_receivables_flag,
301 p_default_post_to_gl_flag,
302 p_default_allow_freight_flag,
303 p_default_creation_sign,
304 p_default_allow_overapp_flag,
305 p_deflt_natural_app_only_flag,
306 p_default_tax_calculation_flag,
307 p_default_status_code,
308 p_default_status,
309 p_default_printing_option_code,
310 p_default_printing_option,
311 l_default_term_id,
312 p_default_term_name
313 FROM ar_lookups arl_print,
314 ar_lookups arl_status,
315 ra_terms rat,
316 ra_cust_trx_types ctt
317 WHERE ctt.cust_trx_type_id = p_cust_trx_type_id
318 AND 'INVOICE_PRINT_OPTIONS' = arl_print.lookup_type(+)
319 AND ctt.default_printing_option = arl_print.lookup_code(+)
320 AND 'INVOICE_TRX_STATUS' = arl_status.lookup_type(+)
321 AND ctt.default_status = arl_status.lookup_code(+)
322 AND ctt.default_term = rat.term_id(+)
323 AND 'Y' = arl_print.enabled_flag(+)
324 AND 'Y' = arl_status.enabled_flag(+)
325 AND -- Check effectivity dates
326 NVL(p_trx_date, TRUNC(SYSDATE) )
327 BETWEEN start_date
328 AND NVL(end_date, NVL(p_trx_date, TRUNC(SYSDATE) ) )
329 AND NVL(p_trx_date, TRUNC(SYSDATE))
330 BETWEEN rat.start_date_active(+)
331 AND NVL(rat.end_date_active(+),
332 NVL( p_trx_date, TRUNC(SYSDATE) ) )
333 AND NVL(p_trx_date, TRUNC(SYSDATE))
334 BETWEEN arl_print.start_date_active(+)
335 AND NVL(arl_print.end_date_active(+),
336 NVL( p_trx_date, TRUNC(SYSDATE) ) )
337 AND NVL(p_trx_date, TRUNC(SYSDATE))
338 BETWEEN arl_status.start_date_active(+)
339 AND NVL(arl_status.end_date_active(+),
340 NVL( p_trx_date, TRUNC(SYSDATE) ) )
341 AND -- The transaction must have the same class as is specified
342 -- in the form.
343 -- However, existing Debit Memos may be converted into Invoices
344 -- and Invoices may be converted to Debit Memos
345 (
346 NVL(p_ctt_class, ctt.type) = ctt.type
347 OR
348 (
349 p_ctt_class IN ('DM', 'INV')
350 AND
351 p_row_id IS NOT NULL
352 AND
353 ctt.type IN ('DM', 'INV')
354 )
355 )
356 AND -- Only invoices and credit memos can have rules
357 (
358 p_invoicing_rule_id IS NULL
359 OR
360 ctt.type IN ('INV', 'CM')
361 )
362 AND -- Commitments must be Open Receivable Yes
363 (
364 ctt.type NOT IN ('DEP', 'GUAR')
365 OR
366 ctt.accounting_affect_flag = 'Y'
367 )
368 AND -- Don't allow changes to the Post To GL flag after
369 -- the Revenue Recognition Program has been run on
370 -- this transaction
371 ctt.post_to_gl = DECODE(p_rev_recog_run_flag,
372 'Y', p_rev_recog_run_flag,
373 ctt.post_to_gl )
374 AND -- Don't allow changes to the Open Receivables Flag for
375 -- complete credit memos
376 (
377 p_complete_flag = 'N'
378 OR
379 p_ctt_class <> 'CM'
380 OR
381 (
382 ctt.accounting_affect_flag = p_open_receivables_flag
383 )
384 )
385 AND -- Check allow freight constraint and
386 -- prevent transactions with charges from being changed
387 -- into transactions that do not allow charges.
388 NOT EXISTS ( SELECT 'violates allow freight'
389 FROM ra_customer_trx_lines ctl
390 WHERE ctl.customer_trx_id = p_customer_trx_id
391 AND (
392 (
393 ctt.allow_freight_flag = 'N'
394 AND ctl.line_type = 'FREIGHT'
395 )
396 OR
397 (
398 ctl.line_type = 'CHARGES'
399 AND ctt.type NOT IN ('DM', 'CM')
400 )
401 )
402 )
403 AND -- Check creation sign constraint
404 NOT EXISTS (
405 SELECT 'VIOLATES CREATION SIGN'
406 FROM ra_customer_trx_lines ctl
407 WHERE ctl.customer_trx_id = p_customer_trx_id
408 GROUP BY ctt.creation_sign
409 HAVING DECODE(
410 SIGN( SUM(ctl.extended_amount) ),
411 1, DECODE( ctt.creation_sign,
412 'P', 'Y',
413 'A', 'Y',
414 'N' ),
415 -1, DECODE( ctt.creation_sign,
416 'N', 'Y',
417 'A', 'Y',
418 'N' ),
419 'Y'
420 ) = 'N'
421 )
422 -- Check the functional security rules for transaction entry
423 AND (
424 p_security_inv_enter_flag = 'Y'
425 OR ctt.type <> 'INV'
426 )
427 AND (
428 p_security_cm_enter_flag = 'Y'
429 OR ctt.type <> 'CM'
430 )
431 AND (
432 p_security_dm_enter_flag = 'Y'
433 OR ctt.type <> 'DM'
434 )
435 AND (
436 p_security_commit_enter_flag = 'Y'
437 OR ctt.type NOT IN ('DEP', 'GUAR')
438 );
439
440
441 /*----------------------------------------+
442 | Get the number of term due dates and |
443 | Check an additional term constraint |
444 +----------------------------------------*/
445
446 IF ( l_default_term_id IS NOT NULL )
447 THEN
448
449 SELECT COUNT(*),
450 arpt_sql_func_util.get_First_Due_Date(l_default_term_id,
451 p_trx_date)
452 INTO l_number_of_due_dates,
453 l_term_due_date
454 FROM ra_terms_lines
455 WHERE term_id = l_default_term_id;
456
457 -- Guarantees cannot have split term terms
458
459 IF (
460 p_ctt_class = 'GUAR'
461 AND l_number_of_due_dates > 1
462 )
463 THEN
464 l_default_term_id := NULL;
465 l_number_of_due_dates := NULL;
466 ELSE
467 p_default_term_id := l_default_term_id;
468 p_number_of_due_dates := l_number_of_due_dates;
469 p_term_due_date := l_term_due_date;
470 END IF;
471
472 END IF;
473
474
475 END IF; -- p_cust_trx_type_id IS NOT NULL case
476
477
478 IF PG_DEBUG in ('Y', 'C') THEN
479 arp_util.debug('arp_trx_defaults_2.get_type_defaults()-');
480 END IF;
481
482 EXCEPTION
483 WHEN NO_DATA_FOUND THEN NULL;
484 WHEN OTHERS THEN
485 IF PG_DEBUG in ('Y', 'C') THEN
486 arp_util.debug('EXCEPTION: arp_trx_defaults_2.get_type_defaults()');
487 END IF;
488 RAISE;
489
490 END get_type_defaults;
491
492
493 /*---------------------------------------------+
494 | Package initialization section. |
495 +---------------------------------------------*/
496 PROCEDURE init IS
497 BEGIN
498
499 pg_text_dummy := arp_ct_pkg.get_text_dummy;
500 pg_flag_dummy := arp_ct_pkg.get_flag_dummy;
501 pg_number_dummy := arp_ct_pkg.get_number_dummy;
502 pg_date_dummy := arp_ct_pkg.get_date_dummy;
503
504 pg_base_curr_code := arp_global.functional_currency;
505 pg_base_precision := arp_global.base_precision;
506 pg_base_min_acc_unit := arp_global.base_min_acc_unit;
507 pg_set_of_books_id :=
508 arp_trx_global.system_info.system_parameters.set_of_books_id;
509 END init;
510
511 BEGIN
512 init;
513 END ARP_TRX_DEFAULTS_2;