[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_BR_HEADER
Source
1 PACKAGE BODY ARP_PROCESS_BR_HEADER AS
2 /* $Header: ARTEBRHB.pls 120.4.12010000.3 2008/10/31 05:36:19 spdixit ship $ */
3
4 SUBTYPE ae_doc_rec_type IS arp_acct_main.ae_doc_rec_type;
5
6 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
7
8 PROCEDURE delete_transaction(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE);
9 PROCEDURE delete_transaction_dist(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE);
10
11 /*===========================================================================+
12 | PROCEDURE |
13 | |
14 | |
15 | DESCRIPTION |
16 | Inserts a record into ra_customer_trx for bills receivable transaction |
17 | |
18 | SCOPE - PUBLIC |
19 | |
20 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
21 | arp_util.debug |
22 | |
23 | ARGUMENTS : IN: p_trx_rec |
24 | OUT: p_trx_number |
25 | p_customer_trx_id |
26 | IN/ OUT: |
27 | |
28 | RETURNS : NONE |
29 | |
30 | MODIFICATION HISTORY |
31 | 28-MAR-2000 Jani Rautiainen Created |
32 | |
33 +===========================================================================*/
34
35 PROCEDURE insert_header(p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
36 p_gl_date IN DATE,
37 p_trx_number OUT NOCOPY ra_customer_trx.trx_number%type,
38 p_customer_trx_id OUT NOCOPY ra_customer_trx.customer_trx_id%type) IS
39
40 l_trh_rec ar_transaction_history%ROWTYPE;
41 l_transaction_history_id ar_transaction_history.transaction_history_id%TYPE;
42 BEGIN
43
44 IF PG_DEBUG in ('Y', 'C') THEN
45 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.insert_header()+');
46 END IF;
47
48 /*------------------------------------------------+
49 | All the validation and defaulting is done in |
50 | the BR API, so none is done in the entity |
51 | handler. |
52 +------------------------------------------------*/
53
54 /*----------------------+
55 | call table-handler |
56 +----------------------*/
57
58 arp_ct_pkg.insert_p(p_trx_rec, p_trx_number, p_customer_trx_id);
59
60 /*------------------------------------------------+
61 | Create the firs transaction history record |
62 | with status of 'INCOMPLETE' |
63 +------------------------------------------------*/
64
65 l_trh_rec.customer_trx_id := p_customer_trx_id;
66 l_trh_rec.status := 'INCOMPLETE';
67 l_trh_rec.event := 'INCOMPLETE';
68 l_trh_rec.batch_id := p_trx_rec.batch_id;
69 l_trh_rec.trx_date := p_trx_rec.trx_date;
70 l_trh_rec.gl_date := p_gl_date;
71 l_trh_rec.maturity_date := p_trx_rec.term_due_date;
72 l_trh_rec.current_record_flag := 'Y';
73 l_trh_rec.current_accounted_flag := 'N';
74 l_trh_rec.postable_flag := 'N';
75 l_trh_rec.first_posted_record_flag := 'N';
76 l_trh_rec.posting_control_id := -3;
77 l_trh_rec.gl_posted_date := NULL;
78 l_trh_rec.created_from := 'ARTEBRHB';
79 l_trh_rec.comments := p_trx_rec.comments;
80 l_trh_rec.org_id := p_trx_rec.org_id;
81
82 /*----------------------------------------------------------------------------------+
83 | Following columns are defaulted by the table handler: |
84 | program_application_id, program_id, program_update_date, request_id, |
85 | creation_date, created_by, last_update_login, last_update_date, last_updated_by |
86 +----------------------------------------------------------------------------------*/
87
88 ARP_PROC_TRANSACTION_HISTORY.insert_transaction_history(l_trh_rec, l_transaction_history_id);
89
90 /*----------------------------------------+
91 | BR has no Sales Credit, terms or tax |
92 +----------------------------------------*/
93
94 /*---------------------------------------------------------------------------+
95 | All accounting is done in the accounting engine called by transaction |
96 | history entity handler |
97 +---------------------------------------------------------------------------*/
98
99 IF PG_DEBUG in ('Y', 'C') THEN
100 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.insert_header()-');
101 END IF;
102
103 EXCEPTION
104 WHEN OTHERS THEN
105 IF PG_DEBUG in ('Y', 'C') THEN
106 arp_util.debug('delete_transaction: ' || 'EXCEPTION: arp_process_br_header.insert_header()');
107 END IF;
108 RAISE;
109
110 END insert_header;
111
112 /*===========================================================================+
113 | PROCEDURE |
114 | update_header |
115 | |
116 | DESCRIPTION |
117 | Updates a record into ra_customer_trx for bills receivable transaction.|
118 | |
119 | SCOPE - PUBLIC |
120 | |
121 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
122 | arp_util.debug |
123 | |
124 | ARGUMENTS : IN: |
125 | OUT: |
126 | IN/ OUT: p_trx_rec |
127 | |
128 | RETURNS : NONE |
129 | |
130 | MODIFICATION HISTORY |
131 | 28-MAR-2000 Jani Rautiainen Created |
132 | |
133 +===========================================================================*/
134
135 PROCEDURE update_header(p_trx_rec IN OUT NOCOPY ra_customer_trx%rowtype,
136 p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) IS
137 BEGIN
138
139 IF PG_DEBUG in ('Y', 'C') THEN
140 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.update_header()+');
141 END IF;
142
143 /*------------------------------------------------+
144 | All the validation and defaulting is done in |
145 | the BR API, so none is done in the entity |
146 | handler. |
147 +------------------------------------------------*/
148
149 /*----------------------------------------------------------------+
150 | Lock rows in other tables that reference this customer_trx_id |
151 +----------------------------------------------------------------*/
152 arp_process_br_header.lock_transaction(p_trx_rec.customer_trx_id);
153
154 /*----------------------------------------------------------------------+
155 | BR does not have Tax tax itself, deferred tax exists but it is taken |
156 | care of with the transaction history. |
157 +----------------------------------------------------------------------*/
158
159 /*----------------------+
160 | call table-handler |
161 +----------------------*/
162
163 arp_ct_pkg.update_p(p_trx_rec, p_customer_trx_id);
164
165 /*----------------------------------------------------------------------+
166 | Disputing is handled by updating the PS using PS entity handlers |
167 +----------------------------------------------------------------------*/
168
169 IF PG_DEBUG in ('Y', 'C') THEN
170 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.update_header()-');
171 END IF;
172
173 EXCEPTION
174 WHEN OTHERS THEN
175 IF PG_DEBUG in ('Y', 'C') THEN
176 arp_util.debug('delete_transaction: ' || 'EXCEPTION: arp_process_br_header.update_header()');
177 END IF;
178 RAISE;
179
180 END update_header;
181
182 /*===========================================================================+
183 | PROCEDURE |
184 | delete_header |
185 | |
186 | DESCRIPTION |
187 | Deletes row from ra_customer_trx for Bills Receivable Transaction. |
188 | Also deletes all child rows. |
189 | |
190 | SCOPE - PUBLIC |
191 | |
192 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
193 | arp_util.debug |
194 | |
195 | ARGUMENTS : IN: p_customer_trx_id |
196 | OUT: |
197 | IN/ OUT: |
198 | |
199 | RETURNS : NONE |
200 | |
201 | MODIFICATION HISTORY |
202 | 28-MAR-2000 Jani Rautiainen Created |
203 | |
204 +===========================================================================*/
205 PROCEDURE delete_header(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) IS
206 l_transaction_history_id NUMBER;
207 BEGIN
208
209 IF PG_DEBUG in ('Y', 'C') THEN
210 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.delete_header()+');
211 END IF;
212
213 /*----------------------------------------------------------------+
214 | Lock rows in other tables that reference this customer_trx_id |
215 +----------------------------------------------------------------*/
216 arp_process_br_header.lock_transaction(p_customer_trx_id);
217
218 /*-------------------------+
219 | delete the transaction |
220 +-------------------------*/
221 /*Bug7484811, Called ARP_XLA_EVENTS.delete_event to delete XLA record. */
222 BEGIN
223 SELECT transaction_history_id
224 INTO l_transaction_history_id
225 FROM ar_transaction_history
226 WHERE customer_trx_id = p_customer_trx_id
227 AND current_record_flag = 'Y';
228
229 EXCEPTION
230 WHEN OTHERS THEN
231 IF PG_DEBUG in ('Y', 'C') THEN
232 arp_util.debug('EXCEPTION: getting trh_id in arp_process_br_header.delete_header() '|| sqlerrm);
233 END IF;
234 RAISE;
235 END;
236
237 ARP_XLA_EVENTS.delete_event( p_document_id => l_transaction_history_id,
238 p_doc_table => 'TRH');
239
240 arp_process_br_header.delete_transaction(p_customer_trx_id);
241
242 IF PG_DEBUG in ('Y', 'C') THEN
243 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.delete_header()-');
244 END IF;
245
246 EXCEPTION
247 WHEN OTHERS THEN
248 IF PG_DEBUG in ('Y', 'C') THEN
249 arp_util.debug('delete_transaction: ' || 'EXCEPTION: arp_process_br_header.delete_header()');
250 END IF;
251 RAISE;
252
253 END delete_header;
254
255 /*===========================================================================+
256 | PROCEDURE |
257 | delete_transaction |
258 | |
259 | DESCRIPTION |
260 | Deletes all records in all tables associated with a particular |
261 | bills receivable transcation. |
262 | |
263 | SCOPE - PRIVATE |
264 | |
265 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
266 | arp_util.debug |
267 | |
268 | ARGUMENTS : IN: p_customer_trx_id |
269 | OUT: None |
270 | |
271 | RETURNS : NONE |
272 | |
273 | MODIFICATION HISTORY |
274 | 28-MAR-2000 Jani Rautiainen Created
275 | |
276 +===========================================================================*/
277 PROCEDURE delete_transaction(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) IS
278
279 BEGIN
280
281 IF PG_DEBUG in ('Y', 'C') THEN
282 arp_util.debug('arp_process_br_header.delete_transaction()+');
283 END IF;
284
285 savepoint ar_br_delete_transaction_1;
286
287 /*------------------------------------------------------------------+
288 | Delete rows in other tables that reference this customer_trx_id |
289 +------------------------------------------------------------------*/
290 arp_ct_pkg.delete_p(p_customer_trx_id);
291 arp_ctl_pkg.delete_f_ct_id(p_customer_trx_id);
292 arp_ps_pkg.delete_f_ct_id(p_customer_trx_id);
293 arp_process_br_header.delete_transaction_dist(p_customer_trx_id);
294 arp_transaction_history_pkg.delete_p(p_trx_id => p_customer_trx_id);
295
296 IF PG_DEBUG in ('Y', 'C') THEN
297 arp_util.debug('arp_process_br_header.delete_transaction()-');
298 END IF;
299
300 EXCEPTION
301 WHEN OTHERS THEN
302 IF PG_DEBUG in ('Y', 'C') THEN
303 arp_util.debug('EXCEPTION: arp_process_br_header.delete_transaction()');
304 END IF;
305 rollback to savepoint ar_br_delete_transaction_1;
306 RAISE;
307
308 END delete_transaction;
309
310 /*===========================================================================+
311 | PROCEDURE |
312 | lock_transaction |
313 | |
314 | DESCRIPTION |
315 | Locks all records in all tables associated with a particular |
316 | bills receivable transcation. |
317 | |
318 | SCOPE - PRIVATE |
319 | |
320 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
321 | dbms_sql.bind_variable |
322 | arp_util.debug |
323 | |
324 | ARGUMENTS : IN: p_customer_trx_id |
325 | OUT: None |
326 | |
327 | RETURNS : NONE |
328 | |
329 | MODIFICATION HISTORY |
330 | 28-MAR-2000 Jani Rautiainen Created
331 | |
332 +===========================================================================*/
333 PROCEDURE lock_transaction(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) IS
334 BEGIN
335 IF PG_DEBUG in ('Y', 'C') THEN
336 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.lock_transaction()+');
337 END IF;
338
339 savepoint ar_br_lock_transaction_1;
340
341 /*----------------------------------------------------------------+
342 | Lock rows in other tables that reference this customer_trx_id |
343 +----------------------------------------------------------------*/
344 arp_ct_pkg.lock_p(p_customer_trx_id);
345 arp_ctl_pkg.lock_f_ct_id(p_customer_trx_id);
346 arp_ps_pkg.lock_f_ct_id(p_customer_trx_id);
347 arp_adjustments_pkg.lock_f_ct_id(p_customer_trx_id);
348 arp_transaction_history_pkg.lock_f_trx_id(p_customer_trx_id);
349
350 IF PG_DEBUG in ('Y', 'C') THEN
351 arp_util.debug('delete_transaction: ' || 'arp_process_br_header.lock_transaction()-');
352 END IF;
353
354 EXCEPTION
355 WHEN OTHERS THEN
356 IF PG_DEBUG in ('Y', 'C') THEN
357 arp_util.debug('delete_transaction: ' || 'EXCEPTION: arp_process_br_header.lock_transaction');
358 END IF;
359 rollback to savepoint ar_br_lock_transaction_1;
360 RAISE;
361
362 END lock_transaction;
363
364 /*===========================================================================+
365 | PROCEDURE |
366 | delete_transaction_dist |
367 | |
368 | DESCRIPTION |
369 | Deletes distribution rows from ar_distributions for given transaction |
370 | |
371 | SCOPE - PUBLIC |
372 | |
373 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
374 | arp_util.debug |
375 | |
376 | ARGUMENTS : IN: p_customer_trx_id |
377 | OUT: |
378 | IN/ OUT: |
379 | |
380 | RETURNS : NONE |
381 | |
382 | NOTES |
383 | |
384 | MODIFICATION HISTORY |
385 | 28-MAR-2000 Jani Rautiainen Created |
386 | |
387 +===========================================================================*/
388 PROCEDURE delete_transaction_dist(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE) IS
389
390 /*----------------------------------------------------------------+
391 | Cursor for all transaction history rows that have accounting |
392 +----------------------------------------------------------------*/
393 CURSOR transaction_history_cur IS
394 select th.transaction_history_id
395 from ar_transaction_history th
396 where customer_trx_id = p_customer_trx_id
397 and postable_flag = 'Y';
398
399 transaction_history_rec transaction_history_cur%ROWTYPE;
400
401 BEGIN
402
403 IF PG_DEBUG in ('Y', 'C') THEN
404 arp_util.debug('arp_process_br_header.delete_transaction_dist()+');
405 END IF;
406
407 /*------------------------------------------------------------------+
408 | loop through all transaction history rows that have accounting |
409 +------------------------------------------------------------------*/
410
411 FOR transaction_history_rec IN transaction_history_cur LOOP
412
413 /*-------------------------------------------------------+
414 | delete the accounting related to transaction history |
415 +-------------------------------------------------------*/
416
417 arp_proc_transaction_history.delete_transaction_hist_dist(transaction_history_rec.transaction_history_id);
418
419 END LOOP;
420
421 IF PG_DEBUG in ('Y', 'C') THEN
422 arp_util.debug('arp_process_br_header.delete_transaction_dist()-');
423 END IF;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 IF PG_DEBUG in ('Y', 'C') THEN
428 arp_util.debug('EXCEPTION: arp_process_br_header.delete_transaction_dist()');
429 END IF;
430 RAISE;
431
432 END delete_transaction_dist;
433
434 /*===========================================================================+
435 | PROCEDURE |
436 | move_deferred_tax |
437 | |
438 | DESCRIPTION |
439 | This procedure is used to deduct whether deferred tax needs to be |
440 | moved for a Bills Receivable transaction. |
441 | SCOPE - PUBLIC |
442 | |
443 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
444 | arp_util.debug |
445 | |
446 | ARGUMENTS : IN: p_customer_trx_id - ID of the BR to be checked |
447 | OUT: p_required - returns whether to move tax or not |
448 | IN/ OUT: |
449 | |
450 | RETURNS : NONE |
451 | |
452 | NOTES |
453 | |
454 | MODIFICATION HISTORY |
455 | 31-AUG-2000 Jani Rautiainen Created |
456 | |
457 +===========================================================================*/
458 PROCEDURE move_deferred_tax(p_customer_trx_id IN ra_customer_trx.customer_trx_id%TYPE,
459 p_required OUT NOCOPY BOOLEAN) IS
460
461 CURSOR move_deferred_tax_cur IS
462 select 'Y' deferred_tax_moved
463 from dual
464 where exists (select 'x'
465 from ra_customer_trx ct
466 where ct.customer_trx_id IN (select distinct ctl.br_ref_customer_trx_id
467 from ra_customer_trx_lines ctl
468 start with ctl.customer_trx_id = p_customer_trx_id
469 connect by prior ctl.br_ref_customer_trx_id = ctl.customer_trx_id
470 )
471 and ct.drawee_site_use_id IS NULL
472 and exists (select 'x'
473 from ra_cust_trx_line_gl_dist gld
474 where gld.account_class = 'TAX'
475 and gld.customer_trx_id = ct.customer_trx_id
476 and gld.collected_tax_ccid IS NOT NULL
477 )
478 );
479
480 move_deferred_tax_rec move_deferred_tax_cur%ROWTYPE;
481
482 BEGIN
483 IF PG_DEBUG in ('Y', 'C') THEN
484 arp_util.debug('arp_process_br_header.move_deferred_tax()+');
485 END IF;
486
487 OPEN move_deferred_tax_cur;
488 FETCH move_deferred_tax_cur INTO move_deferred_tax_rec;
489 CLOSE move_deferred_tax_cur;
490
491 IF NVL(move_deferred_tax_rec.deferred_tax_moved,'N') = 'Y' THEN
492
493 p_required := TRUE;
494
495 ELSE
496
497 p_required := FALSE;
498
499 END IF;
500
501 IF PG_DEBUG in ('Y', 'C') THEN
502 arp_util.debug('arp_process_br_header.move_deferred_tax()-');
503 END IF;
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 IF PG_DEBUG in ('Y', 'C') THEN
508 arp_util.debug('EXCEPTION: arp_process_br_header.move_deferred_tax()');
509 END IF;
510 p_required := FALSE; --Tax is not deferred processing not required
511
512 WHEN OTHERS THEN
513 IF PG_DEBUG in ('Y', 'C') THEN
514 arp_util.debug('EXCEPTION: arp_process_br_header.move_deferred_tax()');
515 END IF;
516 p_required := FALSE; --Tax is not deferred processing not required
517
518 END move_deferred_tax;
519
520
521 END ARP_PROCESS_BR_HEADER;