[Home] [Help]
PACKAGE BODY: APPS.AR_UNACCOUNTED_TRX_SWEEP
Source
4 G_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1 PACKAGE BODY AR_UNACCOUNTED_TRX_SWEEP AS
2 /* $Header: ARUNACCTB.pls 120.2.12020000.6 2013/05/23 11:16:12 kgnanase noship $ */
3
5
6
7 /*============================================================================
8 | FUNCTION - UPDATE_AR_ACCT_DATE
9 |
10 | DESCRIPTION
11 | This function is used to sweep invoice, receipt, adjustment,
12 | bills receivable distributions to an open date in next accounting
13 | period that are unaccounted in the current period.
14 |
15 |
16 | PRAMETERS
17 |
18 |
19 | KNOWN ISSUES:
20 |
21 | NOTES:
22 |
23 | MODIFICATION HISTORY
24 | Date Author Description of Change
25 *===========================================================================*/
26 FUNCTION update_ar_acct_date RETURN BOOLEAN IS
27
28 ln_conc_program_id fnd_concurrent_programs.concurrent_program_id%TYPE;
29 BEGIN
30
31 SELECT concurrent_program_id
32 INTO ln_conc_program_id
33 FROM fnd_concurrent_programs
34 WHERE concurrent_program_name='ARTRXSWP';
35
36
37 <<sweep_gld>>
38 UPDATE ra_cust_trx_line_gl_dist_all
39 SET gl_date = g_sweep_to_date,
40 program_id = ln_conc_program_id,
41 last_update_date = sysdate,
42 last_updated_by = FND_GLOBAL.user_id
43 WHERE cust_trx_line_gl_dist_id in (SELECT gt.cust_trx_line_gl_dist_id
44 FROM ar_period_close_excps_gt gt
45 WHERE gt.document_type = G_SRC_TYP_UNACCT_TRX
46 AND EXISTS
47 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
48 AND posting_control_id = -3;
49
50 arp_standard.debug ('update_ar_acct_date: total records updated in ra_cust_trx_line_gl_dist_all: '||sql%rowcount);
51
52 <<sweep_cm_ra >>
53 UPDATE ar_receivable_applications_all
54 SET gl_date = g_sweep_to_date,
55 program_id = ln_conc_program_id,
56 last_update_date = sysdate,
57 last_updated_by = FND_GLOBAL.user_id
58 WHERE receivable_application_id in (SELECT gt.dist_source_id
59 FROM ar_period_close_excps_gt gt
60 WHERE gt.document_type = G_SRC_TYP_UNACCT_TRX
61 AND gt.dist_source_table = 'RA'
62 AND EXISTS
63 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
64 AND posting_control_id = -3;
65
66 arp_standard.debug ('update_ar_acct_date: total records updated in ar_receivable_applications_all: '||sql%rowcount);
67
68 <<sweep_rct_crh >>
69 UPDATE ar_cash_receipt_history_all
70 SET gl_date = g_sweep_to_date,
71 program_id = ln_conc_program_id,
72 last_update_date = sysdate,
73 last_updated_by = FND_GLOBAL.user_id
74 WHERE cash_receipt_history_id in (SELECT gt.dist_source_id
75 FROM ar_period_close_excps_gt gt
76 WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
77 AND gt.dist_source_table = 'CRH'
78 AND EXISTS
79 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
80 AND posting_control_id = -3;
81
82 arp_standard.debug ('update_ar_acct_date: total records updated in ar_cash_receipt_history_all: '||sql%rowcount);
83
84 <<sweep_rct_ra>>
85 UPDATE ar_receivable_applications_all
86 SET gl_date = g_sweep_to_date,
87 program_id = ln_conc_program_id,
88 last_update_date = sysdate,
89 last_updated_by = FND_GLOBAL.user_id
90 WHERE receivable_application_id in (SELECT gt.dist_source_id
91 FROM ar_period_close_excps_gt gt
92 WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
96 AND posting_control_id = -3;
93 AND gt.dist_source_table = 'RA'
94 AND EXISTS
95 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
97
98 arp_standard.debug ('update_ar_acct_date: total records updated in ar_receivable_applications_all: '||sql%rowcount);
99
100 <<sweep_rct_mcd>>
101 UPDATE ar_misc_cash_distributions_all
102 SET gl_date = g_sweep_to_date,
103 program_id = ln_conc_program_id,
104 last_update_date = sysdate,
105 last_updated_by = FND_GLOBAL.user_id
106 WHERE misc_cash_distribution_id in (SELECT gt.dist_source_id
107 FROM ar_period_close_excps_gt gt
108 WHERE gt.document_type = G_SRC_TYP_UNACCT_RCT
109 AND gt.dist_source_table = 'MCD'
110 AND EXISTS
111 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
112 AND posting_control_id = -3;
113
114 arp_standard.debug ('update_ar_acct_date: total records updated in ar_misc_cash_distributions_all: '||sql%rowcount);
115
116 <<sweep_adj>>
117 UPDATE ar_adjustments_all
118 SET gl_date = g_sweep_to_date,
119 program_id = ln_conc_program_id,
120 last_update_date = sysdate,
121 last_updated_by = FND_GLOBAL.user_id
122 WHERE adjustment_id in (SELECT gt.dist_source_id
123 FROM ar_period_close_excps_gt gt
124 WHERE gt.document_type = G_SRC_TYP_UNACCT_ADJ
125 AND gt.dist_source_table = 'ADJ'
126 AND EXISTS
127 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
128 AND posting_control_id = -3;
129
130 arp_standard.debug ('update_ar_acct_date: total records updated in ar_adjustments_all: '||sql%rowcount);
131
132 <<sweep_br>>
133 UPDATE ar_transaction_history_all
134 SET gl_date = g_sweep_to_date,
135 program_id = ln_conc_program_id,
136 last_update_date = sysdate,
137 last_updated_by = FND_GLOBAL.user_id
138 WHERE transaction_history_id in (SELECT gt.dist_source_id
139 FROM ar_period_close_excps_gt gt
140 WHERE gt.document_type = G_SRC_TYP_UNACCT_BR
141 AND gt.dist_source_table = 'TH'
142 AND EXISTS
143 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
144 AND posting_control_id = -3;
145
146 arp_standard.debug ('update_ar_acct_date: total records updated in ar_transaction_history_all: '||sql%rowcount);
147
148
149 <<update_ps>>
150 -- TRX
151
152 update ar_payment_schedules_all ps
153 set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
154 from ra_cust_trx_line_gl_dist_all
155 where customer_trx_id = ps.customer_trx_id
156 and latest_rec_flag = 'Y'),
157 ps.gl_date_closed = decode(ps.status,
158 'CL', (SELECT MAX(a.gl_date)
159 from (
160 select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
161 from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
162 where ra.status = 'APP'
163 and ra.payment_schedule_id = psi.payment_schedule_id
164 group by psi.payment_schedule_id
165 union all
166 select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
167 from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
168 where ra.status = 'APP'
169 and ra.applied_payment_schedule_id = psi.payment_schedule_id
170 group by psi.payment_schedule_id
171 union all
172 select psi.payment_schedule_id payment_schedule_id, max(adj.gl_date) gl_date
173 from ar_adjustments_all adj, ar_period_close_excps_gt psi
174 where adj.status = 'A'
175 and adj.amount <> 0
176 and adj.payment_schedule_id = psi.payment_schedule_id
177 group by psi.payment_schedule_id
178 ) a, ar_payment_schedules_all ps2
179 where ps2.payment_schedule_id = a.payment_schedule_id
180 and ps2.payment_schedule_id = ps.payment_schedule_id
181 group by a.payment_schedule_id
182 ),
183 ps.gl_date_closed),
184 last_update_date = sysdate
185 where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
186 WHERE EXISTS
187 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
188 and class not in ('PMT', 'BR')
189 and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
190 from ra_cust_trx_line_gl_dist_all
191 where customer_trx_id = ps.customer_trx_id
192 and latest_rec_flag = 'Y'
193 );
194
195 -- Applied trx/cm app/rec app/Adj
199 last_update_date = sysdate
196
197 update ar_payment_schedules_all ps
198 set ps.gl_date_closed = g_sweep_to_date,
200 where payment_schedule_id in (select rai.applied_payment_schedule_id
201 from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
202 where rai.payment_schedule_id = gt.payment_schedule_id
203 and rai.application_type = 'CASH'
204 and rai.applied_payment_schedule_id is not null
205 and rai.applied_payment_schedule_id > 0
206 and gt.document_type = G_SRC_TYP_UNACCT_RCT
207 and gt.dist_source_table = 'RA'
208 AND EXISTS
209 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
210 union
211 select rai.payment_schedule_id
212 from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
213 where rai.payment_schedule_id = gt.payment_schedule_id
214 and rai.application_type = 'CASH'
215 and gt.document_type = G_SRC_TYP_UNACCT_RCT
216 and gt.dist_source_table = 'RA'
217 AND EXISTS
218 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
219 union
220 select rai.payment_schedule_id
221 from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
222 where rai.payment_schedule_id = gt.payment_schedule_id
223 and rai.application_type = 'CM'
224 and rai.payment_schedule_id is not null
225 and gt.document_type = G_SRC_TYP_UNACCT_TRX
226 and gt.dist_source_table = 'RA'
227 AND EXISTS
228 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
229 union
230 select rai.applied_payment_schedule_id
231 from ar_receivable_applications_all rai, ar_period_close_excps_gt gt
232 where rai.payment_schedule_id = gt.payment_schedule_id
233 and rai.application_type = 'CM'
234 and rai.payment_schedule_id is not null
235 and gt.document_type = G_SRC_TYP_UNACCT_TRX
236 and gt.dist_source_table = 'RA'
237 AND EXISTS
238 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id)
239 union
240 select adji.payment_schedule_id
241 from ar_adjustments_all adji, ar_period_close_excps_gt gt
242 where adji.payment_schedule_id = gt.payment_schedule_id
243 and adji.adjustment_id = gt.adjustment_id
244 and gt.document_type = G_SRC_TYP_UNACCT_ADJ
245 and gt.dist_source_table = 'ADJ'
246 AND EXISTS
247 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
248 and ps.status = 'CL'
249 and ps.gl_date_closed < g_sweep_to_date
250 ;
251
252
253 -- PMT
254
255 update ar_payment_schedules_all ps
256 set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
257 from ar_cash_receipt_history_all
258 where cash_receipt_id = ps.cash_receipt_id
259 and first_posted_record_flag = 'Y'),
260 ps.gl_date_closed = decode(ps.status,
261 'CL', (SELECT MAX(a.gl_date)
262 from (
263 select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
264 from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
265 where ra.payment_schedule_id = psi.payment_schedule_id
266 group by psi.payment_schedule_id
267 ) a, ar_payment_schedules_all ps2
268 where ps2.payment_schedule_id = a.payment_schedule_id
269 and ps2.payment_schedule_id = ps.payment_schedule_id
270 group by a.payment_schedule_id
271 ),
272 ps.gl_date_closed),
273 last_update_date = sysdate
274 where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
275 WHERE EXISTS
276 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
277 and class = 'PMT'
278 and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
279 from ar_cash_receipt_history_all
280 where cash_receipt_id = ps.cash_receipt_id
281 and first_posted_record_flag = 'Y'
282 );
283
284 -- BR
285
286 update ar_payment_schedules_all ps
287 set ps.gl_date = ( select nvl(min(gl_date), ps.gl_date)
288 from ar_transaction_history_all
289 where customer_trx_id = ps.customer_trx_id
290 and first_posted_record_flag = 'Y'),
294 select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
291 ps.gl_date_closed = decode(ps.status,
292 'CL', (SELECT MAX(a.gl_date)
293 from (
295 from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
296 where ra.status = 'APP'
297 and ra.payment_schedule_id = psi.payment_schedule_id
298 group by psi.payment_schedule_id
299 union all
300 select psi.payment_schedule_id payment_schedule_id, max(ra.gl_date) gl_date
301 from ar_receivable_applications_all ra, ar_period_close_excps_gt psi
302 where ra.status = 'APP'
303 and ra.applied_payment_schedule_id = psi.payment_schedule_id
304 group by psi.payment_schedule_id
305 union all
306 select psi.payment_schedule_id payment_schedule_id, max(adj.gl_date) gl_date
307 from ar_adjustments_all adj, ar_period_close_excps_gt psi
308 where adj.status = 'A'
309 and adj.amount <> 0
310 and adj.payment_schedule_id = psi.payment_schedule_id
311 group by psi.payment_schedule_id
312 ) a, ar_payment_schedules_all ps2
313 where ps2.payment_schedule_id = a.payment_schedule_id
314 and ps2.payment_schedule_id = ps.payment_schedule_id
315 group by a.payment_schedule_id
316 ),
317 ps.gl_date_closed),
318 last_update_date = sysdate
319 where payment_schedule_id in (select payment_schedule_id from ar_period_close_excps_gt gt
320 WHERE EXISTS
321 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id))
322 and class = 'BR'
323 and trunc(ps.gl_date) <> ( select nvl(min(gl_date), ps.gl_date)
324 from ar_transaction_history_all
325 where customer_trx_id = ps.customer_trx_id
326 and first_posted_record_flag = 'Y'
327 );
328
329
330 return TRUE;
331
332 exception
333 WHEN OTHERS THEN
334 return FALSE;
335 END;
336
337
338 /*============================================================================
339 | PROCEDURE - UPDATE_XLA_EVENTS
340 |
341 | DESCRIPTION
342 | This procedure is used to sweep accounting events from one accounting period
343 | to another.
344 |
345 |
346 | PRAMETERS
347 |
348 | KNOWN ISSUES:
349 |
350 | NOTES:
351 |
352 | MODIFICATION HISTORY
353 | Date Author Description of Change
354 *===========================================================================*/
355
356 FUNCTION update_xla_events RETURN BOOLEAN IS
357
358 TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
359 TYPE t_trans_ids IS TABLE OF NUMBER(18) INDEX BY PLS_INTEGER;
360 TYPE t_trans_nums IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
361 TYPE t_entity_codes IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
362 TYPE t_org_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
363 TYPE t_legal_entity_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
364 TYPE t_ledger_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
365
366
367 l_event_ids t_event_ids;
368 l_trans_ids t_trans_ids;
369 l_trans_nums t_trans_nums;
370 l_entity_codes t_entity_codes;
371 l_org_ids t_org_ids;
372 l_legal_entity_ids t_legal_entity_ids;
373 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
374 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
375
376 l_xla_event XLA_EVENTS.EVENT_ID%TYPE;
377 l_xla_event_status XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
378 l_call_xla_api VARCHAR2(1);
379
380 CURSOR c_events IS
381 SELECT gt.event_id,
382 DECODE (gt.document_type
383 ,G_SRC_TYP_UNACCT_TRX, gt.customer_trx_id
384 ,G_SRC_TYP_UNACCT_RCT, gt.cash_receipt_id
385 ,G_SRC_TYP_UNACCT_ADJ, gt.adjustment_id
386 ,G_SRC_TYP_UNACCT_BR, gt.customer_trx_id
387 ) trans_id,
388 gt.org_id org_id,
389 gt.legal_entity_id legal_entity_id,
390 decode (gt.document_type
391 ,G_SRC_TYP_UNACCT_TRX, gt.trx_number
392 ,G_SRC_TYP_UNACCT_RCT, gt.receipt_number
393 ,G_SRC_TYP_UNACCT_ADJ, gt.adjustment_number
394 ,G_SRC_TYP_UNACCT_BR, gt.trx_number
395 )trans_num,
396 decode(gt.document_type
397 ,G_SRC_TYP_UNACCT_TRX, 'TRANSACTIONS'
398 ,G_SRC_TYP_UNACCT_RCT, 'RECEIPTS'
399 ,G_SRC_TYP_UNACCT_ADJ, 'ADJUSTMENTS'
400 ,G_SRC_TYP_UNACCT_BR, 'BILLS_RECEIVABLE'
401 ) entity_code
402 FROM ar_period_close_excps_gt gt
403 WHERE gt.event_id is NOT NULL
404 AND gt.document_type <> G_SRC_TYP_OTHER_EXCPS
405 AND EXISTS
406 ( SELECT 'X' FROM AR_SYSTEM_PARAMETERS where org_id = gt.org_id);
407
408 begin
409
413 LOOP
410 arp_standard.debug ('begin update_xla_events: Bulk fetch cursor c_events');
411
412 OPEN c_events;
414 FETCH c_events
415 BULK COLLECT INTO
416 l_event_ids,
417 l_trans_ids,
418 l_org_ids,
419 l_legal_entity_ids,
420 l_trans_nums,
421 l_entity_codes
422 LIMIT g_fetch_limit;
423
424 arp_standard.debug ('update_xla_events: l_event_ids.count='||l_event_ids.count );
425
426 EXIT WHEN
427 l_event_ids.count = 0;
428
429 FOR i IN 1 .. l_event_ids.count LOOP
430
431 BEGIN
432
433 SELECT event_id, event_status_code
434 INTO l_xla_event, l_xla_event_status
435 FROM xla_events
436 WHERE event_id = l_event_ids(i)
437 AND application_id = 222;
438
439 IF l_xla_event_status = 'P' THEN
440 l_call_xla_api := 'N';
441 ELSE
442 l_call_xla_api := 'Y';
443 END IF;
444
445 EXCEPTION
446
447 WHEN NO_DATA_FOUND THEN
448 l_call_xla_api := 'N';
449
450 END;
451
452 IF l_call_xla_api = 'Y' THEN
453
454 l_event_source_info.application_id := G_AR_APPLICATION_ID;
455 l_event_source_info.legal_entity_id := l_legal_entity_ids(i);
456 l_event_source_info.ledger_id := g_ledger_id;
457 l_event_source_info.entity_type_code := l_entity_codes(i);
458 l_event_source_info.transaction_number := l_trans_nums(i);
459 l_event_source_info.source_id_int_1 := l_trans_ids(i);
460
461 l_event_security_context.security_id_int_1 := l_org_ids(i);
462
463 XLA_EVENTS_PUB_PKG.UPDATE_EVENT
464 ( p_event_source_info => l_event_source_info,
465 p_event_id => l_event_ids(i),
466 p_event_type_code => NULL,
467 p_event_date => g_sweep_to_date,
468 p_event_status_code => NULL,
469 p_valuation_method => NULL,
470 p_security_context => l_event_security_context
471 );
472
473 END IF;
474
475 END LOOP;
476
477 forall i in l_event_ids.first..l_event_ids.last
478 UPDATE xla_ae_headers aeh
479 SET aeh.accounting_date = g_sweep_to_date,
480 aeh.period_name = g_sweep_period_name,
481 last_update_date = SYSDATE,
482 last_updated_by = FND_GLOBAL.user_id
483 WHERE aeh.event_id = l_event_ids(i)
484 AND application_id = 200
485 AND gl_transfer_status_code <> 'Y'
486 AND accounting_entry_status_code <> 'F';
487
488 forall i in l_event_ids.first..l_event_ids.last
489 UPDATE xla_ae_lines ael
490 SET ael.accounting_date = g_sweep_to_date,
491 last_update_date = sysdate,
492 last_updated_by = FND_GLOBAL.user_id
493 WHERE ael.ae_header_id in (
494 SELECT aeh.ae_header_id
495 FROM xla_ae_headers aeh
496 WHERE aeh.event_id = l_event_ids(i)
497 AND aeh.application_id = 200
498 AND aeh.gl_transfer_status_code <> 'Y'
499 AND aeh.accounting_entry_status_code <> 'F');
500
501 END LOOP;
502 CLOSE c_events;
503
504 arp_standard.debug ('end update_xla_events');
505
506 return TRUE;
507
508 EXCEPTION
509 WHEN OTHERS THEN
510
511 IF (c_events%ISOPEN) THEN
512 CLOSE c_events;
513 END IF;
514 arp_standard.debug ('EXCEPTION: update_xla_events: '|| sqlerrm);
515 return FALSE;
516
517 END;
518
519
520 /*============================================================================
521 | FUNCTION - SWEEP_TRANSACTIONS
522 |
523 | DESCRIPTION
524 | This function is used to sweep receivables transations from one
525 | accounting period to another.
526 |
527 | PARAMETERS
528 |
529 |
530 |
531 | KNOWN ISSUES:
532 |
533 | NOTES:
534 |
535 | MODIFICATION HISTORY
536 | Date Author Description of Change
537 *===========================================================================*/
538 FUNCTION sweep_transactions
539 RETURN BOOLEAN
540 IS
541
542 l_success BOOLEAN;
543 BEGIN
544
545 l_success := update_xla_events;
546
547 if (l_success <> TRUE) then
548 arp_standard.debug ('Failure in update_xla_events while updating XLA unaccounted events');
549 return FALSE;
550 end if;
551
552 l_success := FALSE;
553
554 -- do we need to update_ebtax_dists ?
555
556 l_success := update_ar_acct_date;
557
558 if (l_success <> TRUE) then
559 arp_standard.debug ('Failure in update_ar_acct_date while updating payables invoices and payments');
560 end if;
561
562 return l_success;
563
564 END;
565
566
567 /*------------------------------------------------------------------------------------------------------------------------*/
568 --
569 -- get_unposted_transactions
570 -- contains logic to derive unposted (exceptional) invoice distributions, lines and
571 -- payment related transaction.
572 --
573 /*------------------------------------------------------------------------------------------------------------------------*/
574 function get_unposted_transactions
575 return varchar2
576 is
577
578 l_rowcount number;
579
583 -- TRANSACTIONS --
580 begin
581
582 --------------
584 --------------
585
586 <<gld_processing>>
587
588 -- insert statement will populate all un-posted invoice distributions
589 --
590 insert into ar_period_close_excps_gt
591 ( document_type
592 , customer_trx_id
593 , trx_number
594 , cash_receipt_id
595 , receipt_number
596 , adjustment_id
597 , adjustment_number
598 , transaction_history_id
599 , cust_trx_line_gl_dist_id
600 , account_class
601 , dist_line_id
602 , dist_source_id
603 , dist_source_table
604 , dist_source_type
605 , event_id
606 , gl_date
607 , amount_dr
608 , amount_cr
609 , acctd_amount_dr
610 , acctd_amount_cr
611 , org_id
612 , legal_entity_id
613 , currency_code
614 , customer_id
615 , payment_schedule_id
616 , applied_payment_schedule_id
617 )
618 select G_SRC_TYP_UNACCT_TRX
619 , ct.customer_trx_id
620 , ct.trx_number
621 , null
622 , null
623 , null
624 , null
625 , null
626 , gld.cust_trx_line_gl_dist_id
627 , gld.account_class
628 , null
629 , null
630 , null
631 , null
632 , gld.event_id
633 , gld.gl_date
634 , DECODE(gld.account_class, 'REC',decode(sign(gld.amount), -1 , 0 ,gld.amount),
635 decode(sign(gld.amount), -1, abs(gld.amount),0)
636 ) amount_dr
637 , DECODE(gld.account_class, 'REC',decode(sign(gld.amount), -1 ,abs(gld.amount),0),
638 decode(sign(gld.amount), -1, 0,gld.amount)
639 ) amount_cr
640 , DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 , 0 ,gld.acctd_amount),
641 decode(sign(gld.acctd_amount), -1, abs(gld.acctd_amount),0)
642 ) acctd_amount_dr
643 , DECODE(gld.account_class, 'REC',decode(sign(gld.acctd_amount), -1 ,abs(gld.acctd_amount),0),
644 decode(sign(gld.acctd_amount), -1, 0,gld.acctd_amount)
645 ) acctd_amount_cr
646 , gld.org_id
647 , ct.legal_entity_id
648 , ct.invoice_currency_code
649 , ct.bill_to_customer_id
650 , ps.payment_schedule_id
651 , NULL
652 from
653 ra_customer_trx_all ct
654 ,ra_cust_trx_line_gl_dist_all gld
655 ,xla_events xle
656 ,ar_payment_schedules_all ps
657 WHERE ct.complete_flag = 'Y'
658 and ct.customer_trx_id = gld.customer_trx_id
659 and gld.account_set_flag = 'N'
660 and gld.gl_date between g_period_start_date and g_period_end_date
661 and gld.posting_control_id = -3
662 and gld.set_of_books_id = g_ledger_id
663 and ct.customer_trx_id = ps.customer_trx_id
664 and xle.event_id = gld.event_id
665 and xle.event_status_code in ('U')
666 and xle.process_status_code IN ('I','R');
667
668
669 l_rowcount := sql%rowcount;
670
671 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_TRX||' is:'||l_rowcount);
672
673 l_rowcount := NULL;
674
675 <<ra_processing>>
676
677 -- insert statement will populate all un-posted invoice distributions
678 --
679 insert into ar_period_close_excps_gt
680 ( document_type
681 , customer_trx_id
682 , trx_number
683 , cash_receipt_id
684 , receipt_number
685 , adjustment_id
686 , adjustment_number
687 , transaction_history_id
688 , cust_trx_line_gl_dist_id
689 , account_class
690 , dist_line_id
691 , dist_source_id
692 , dist_source_table
693 , dist_source_type
694 , event_id
695 , gl_date
696 , amount_dr
697 , amount_cr
698 , acctd_amount_dr
699 , acctd_amount_cr
700 , org_id
701 , legal_entity_id
702 , currency_code
703 , customer_id
704 , payment_schedule_id
705 , applied_payment_schedule_id
706 )
707 select G_SRC_TYP_UNACCT_TRX
708 , ct.customer_trx_id
709 , ct.trx_number
710 , null
711 , null
712 , null
713 , null
714 , null
715 , null
716 , null
717 , ard.line_id
718 , ard.source_id
719 , ard.source_table
723 , ard.amount_dr
720 , ard.source_type
721 , ra.event_id
722 , ra.gl_date
724 , ard.amount_cr
725 , ard.acctd_amount_dr
726 , ard.acctd_amount_cr
727 , ra.org_id
728 , ct.legal_entity_id
729 , ct.invoice_currency_code
730 , ct.bill_to_customer_id
731 , ps.payment_schedule_id
732 , ra.applied_payment_schedule_id
733 from ra_customer_trx_all ct
734 , ar_receivable_applications_all ra
735 , xla_events xle
736 , ar_distributions_all ard
737 , ar_payment_schedules_all ps
738 WHERE ct.complete_flag = 'Y'
739 and ct.customer_trx_id = ra.customer_trx_id
740 and ra.gl_date between g_period_start_date and g_period_end_date
741 and ra.posting_control_id = -3
742 and NVL(ra.postable, 'Y') = 'Y'
743 and ra.set_of_books_id = g_ledger_id
744 and ra.receivable_application_id = ard.source_id
745 and ard.source_table = 'RA'
746 and ct.customer_trx_id = ps.customer_trx_id
747 and xle.event_id = ra.event_id
748 and xle.event_status_code in ('U')
749 and xle.process_status_code IN ('I','R');
750
751
752 l_rowcount := sql%rowcount;
753
754 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_TRX||' is:'||l_rowcount);
755
756 l_rowcount := NULL;
757
758 --------------
759 -- RECEIPTS --
760 --------------
761
762 <<crh_processing>>
763
764 insert into ar_period_close_excps_gt
765 ( document_type
766 , customer_trx_id
767 , trx_number
768 , cash_receipt_id
769 , receipt_number
770 , adjustment_id
771 , adjustment_number
772 , transaction_history_id
773 , cust_trx_line_gl_dist_id
774 , account_class
775 , dist_line_id
776 , dist_source_id
777 , dist_source_table
778 , dist_source_type
779 , event_id
780 , gl_date
781 , amount_dr
782 , amount_cr
783 , acctd_amount_dr
784 , acctd_amount_cr
785 , org_id
786 , currency_code
787 , customer_id
788 , payment_schedule_id
789 , applied_payment_schedule_id
790 )
791 select G_SRC_TYP_UNACCT_RCT
792 , null
793 , null
794 , cr.cash_receipt_id
795 , cr.receipt_number
796 , null
797 , null
798 , null
799 , null
800 , null
801 , ard.line_id
802 , ard.source_id
803 , ard.source_table
804 , ard.source_type
805 , crh.event_id
806 , crh.gl_date
807 , ard.amount_dr
808 , ard.amount_cr
809 , ard.acctd_amount_dr
810 , ard.acctd_amount_cr
811 , crh.org_id
812 , cr.currency_code
813 , cr.pay_from_customer
814 , ps.payment_schedule_id
815 , null
816 FROM ar_cash_receipts_all cr
817 , ar_cash_receipt_history_all crh
818 , xla_events xle
819 , ar_distributions_all ard
820 , ar_payment_schedules_all ps
821 WHERE cr.cash_receipt_id = crh.cash_receipt_id
822 and cr.set_of_books_id = g_ledger_id
823 and crh.gl_date between g_period_start_date and g_period_end_date
824 and crh.posting_control_id = -3
825 and NVL(crh.postable_flag, 'Y') = 'Y'
826 and crh.cash_receipt_history_id = ard.source_id
827 and ard.source_table = 'CRH'
828 and cr.cash_receipt_id = ps.cash_receipt_id
829 and xle.event_id = crh.event_id
830 and xle.event_status_code in ('U')
831 and xle.process_status_code IN ('I','R');
832
833
834 l_rowcount := sql%rowcount;
835
836 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for cash receipts source_type='||G_SRC_TYP_UNACCT_RCT||' for table= CRH is:'||l_rowcount);
837
838 l_rowcount := NULL;
839
840 <<crh_processing_mcd>>
841
842 insert into ar_period_close_excps_gt
843 ( document_type
844 , customer_trx_id
845 , trx_number
846 , cash_receipt_id
847 , receipt_number
848 , adjustment_id
849 , adjustment_number
850 , transaction_history_id
851 , cust_trx_line_gl_dist_id
852 , account_class
853 , dist_line_id
854 , dist_source_id
855 , dist_source_table
856 , dist_source_type
857 , event_id
858 , gl_date
859 , amount_dr
860 , amount_cr
861 , acctd_amount_dr
862 , acctd_amount_cr
863 , org_id
864 , currency_code
865 , customer_id
866 , payment_schedule_id
867 , applied_payment_schedule_id
868 )
869 select G_SRC_TYP_UNACCT_RCT
870 , null
871 , null
872 , cr.cash_receipt_id
873 , cr.receipt_number
874 , null
875 , null
876 , null
877 , null
878 , null
879 , ard.line_id
883 , crh.event_id
880 , ard.source_id
881 , ard.source_table
882 , ard.source_type
884 , crh.gl_date
885 , ard.amount_dr
886 , ard.amount_cr
887 , ard.acctd_amount_dr
888 , ard.acctd_amount_cr
889 , crh.org_id
890 , cr.currency_code
891 , cr.pay_from_customer
892 , null
893 , null
894 FROM ar_cash_receipts_all cr
895 , ar_cash_receipt_history_all crh
896 , xla_events xle
897 , ar_distributions_all ard
898 WHERE cr.type = 'MISC'
899 and cr.cash_receipt_id = crh.cash_receipt_id
900 and cr.set_of_books_id = g_ledger_id
901 and crh.gl_date between g_period_start_date and g_period_end_date
902 and crh.posting_control_id = -3
903 and NVL(crh.postable_flag, 'Y') = 'Y'
904 and crh.cash_receipt_history_id = ard.source_id
905 and ard.source_table = 'CRH'
906 and xle.event_id = crh.event_id
907 and xle.event_status_code in ('U')
908 and xle.process_status_code IN ('I','R');
909
910
911 l_rowcount := sql%rowcount;
912
913 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for MCD receipts source_type='||G_SRC_TYP_UNACCT_RCT||' for table= CRH is:'||l_rowcount);
914
915 l_rowcount := NULL;
916
917 <<ra_processing>>
918
919 insert into ar_period_close_excps_gt
920 ( document_type
921 , customer_trx_id
922 , trx_number
923 , cash_receipt_id
924 , receipt_number
925 , adjustment_id
926 , adjustment_number
927 , transaction_history_id
928 , cust_trx_line_gl_dist_id
929 , account_class
930 , dist_line_id
931 , dist_source_id
932 , dist_source_table
933 , dist_source_type
934 , event_id
935 , gl_date
936 , amount_dr
937 , amount_cr
938 , acctd_amount_dr
939 , acctd_amount_cr
940 , org_id
941 , currency_code
942 , customer_id
943 , payment_schedule_id
944 , applied_payment_schedule_id
945 )
946 select G_SRC_TYP_UNACCT_RCT
947 , null
948 , null
949 , cr.cash_receipt_id
950 , cr.receipt_number
951 , null
952 , null
953 , null
954 , null
955 , null
956 , ard.line_id
957 , ard.source_id
958 , ard.source_table
959 , ard.source_type
960 , ra.event_id
961 , ra.gl_date
962 , ard.amount_dr
963 , ard.amount_cr
964 , ard.acctd_amount_dr
965 , ard.acctd_amount_cr
966 , ra.org_id
967 , cr.currency_code
968 , cr.pay_from_customer
969 , ps.payment_schedule_id
970 , ra.applied_payment_schedule_id
971 FROM ar_cash_receipts_all cr
972 , ar_receivable_applications_all ra
973 , xla_events xle
974 , ar_distributions_all ard
975 , ar_payment_schedules_all ps
976 WHERE cr.cash_receipt_id = ra.cash_receipt_id
977 and cr.set_of_books_id = g_ledger_id
978 and ra.gl_date between g_period_start_date and g_period_end_date
979 and ra.posting_control_id = -3
980 and NVL(ra.postable, 'Y') = 'Y'
981 and ra.receivable_application_id = ard.source_id
982 and ard.source_table = 'RA'
983 and cr.cash_receipt_id = ps.cash_receipt_id
984 and xle.event_id = ra.event_id
985 and xle.event_status_code in ('U')
986 and xle.process_status_code IN ('I','R');
987
988
989
990 l_rowcount := sql%rowcount;
991
992 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_RCT||' for table= RA is:'||l_rowcount);
993
994 l_rowcount := NULL;
995
996 <<mcd_processing>>
997
998 insert into ar_period_close_excps_gt
999 ( document_type
1000 , customer_trx_id
1001 , trx_number
1002 , cash_receipt_id
1003 , receipt_number
1004 , adjustment_id
1005 , adjustment_number
1006 , transaction_history_id
1007 , cust_trx_line_gl_dist_id
1008 , account_class
1009 , dist_line_id
1010 , dist_source_id
1011 , dist_source_table
1012 , dist_source_type
1013 , event_id
1014 , gl_date
1015 , amount_dr
1016 , amount_cr
1017 , acctd_amount_dr
1018 , acctd_amount_cr
1019 , org_id
1020 , currency_code
1021 , customer_id
1022 , payment_schedule_id
1023 , applied_payment_schedule_id
1024 )
1025 select G_SRC_TYP_UNACCT_RCT
1026 , null
1027 , null
1028 , cr.cash_receipt_id
1029 , cr.receipt_number
1030 , null
1031 , null
1032 , null
1033 , null
1034 , null
1035 , ard.line_id
1036 , ard.source_id
1037 , ard.source_table
1038 , ard.source_type
1039 , mcd.event_id
1040 , mcd.gl_date
1044 , ard.acctd_amount_cr
1041 , ard.amount_dr
1042 , ard.amount_cr
1043 , ard.acctd_amount_dr
1045 , mcd.org_id
1046 , cr.currency_code
1047 , cr.pay_from_customer
1048 , null
1049 , null
1050 FROM ar_cash_receipts_all cr
1051 , ar_misc_cash_distributions_all mcd
1052 , xla_events xle
1053 , ar_distributions_all ard
1054 WHERE cr.cash_receipt_id = mcd.cash_receipt_id
1055 and cr.set_of_books_id = g_ledger_id
1056 and mcd.gl_date between g_period_start_date and g_period_end_date
1057 and mcd.posting_control_id = -3
1058 and mcd.misc_cash_distribution_id = ard.source_id
1059 and ard.source_table = 'MCD'
1060 and xle.event_id = mcd.event_id
1061 and xle.event_status_code in ('U')
1062 and xle.process_status_code IN ('I','R');
1063
1064
1065
1066 l_rowcount := sql%rowcount;
1067
1068 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_RCT||' for table= MCD is:'||l_rowcount);
1069
1070 l_rowcount := NULL;
1071
1072 --------------
1073 -- ADJUSTMENTS --
1074 --------------
1075
1076 <<adj_processing>>
1077
1078 insert into ar_period_close_excps_gt
1079 ( document_type
1080 , customer_trx_id
1081 , trx_number
1082 , cash_receipt_id
1083 , receipt_number
1084 , adjustment_id
1085 , adjustment_number
1086 , transaction_history_id
1087 , cust_trx_line_gl_dist_id
1088 , account_class
1089 , dist_line_id
1090 , dist_source_id
1091 , dist_source_table
1092 , dist_source_type
1093 , event_id
1094 , gl_date
1095 , amount_dr
1096 , amount_cr
1097 , acctd_amount_dr
1098 , acctd_amount_cr
1099 , org_id
1100 , currency_code
1101 , customer_id
1102 , payment_schedule_id
1103 , applied_payment_schedule_id
1104 )
1105 select G_SRC_TYP_UNACCT_ADJ
1106 , ct.customer_trx_id
1107 , ct.trx_number
1108 , null
1109 , null
1110 , adj.adjustment_id
1111 , adj.adjustment_number
1112 , null
1113 , null
1114 , null
1115 , ard.line_id
1116 , ard.source_id
1117 , ard.source_table
1118 , ard.source_type
1119 , adj.event_id
1120 , adj.gl_date
1121 , ard.amount_dr
1122 , ard.amount_cr
1123 , ard.acctd_amount_dr
1124 , ard.acctd_amount_cr
1125 , adj.org_id
1126 , ct.invoice_currency_code
1127 , ct.bill_to_customer_id
1128 , ps.payment_schedule_id
1129 , null
1130 FROM ar_adjustments_all adj
1131 , ar_distributions_all ard
1132 , xla_events xle
1133 , ra_customer_trx_all ct
1134 , ar_payment_schedules_all ps
1135 WHERE adj.set_of_books_id = g_ledger_id
1136 and adj.gl_date between g_period_start_date and g_period_end_date
1137 and adj.posting_control_id = -3
1138 and NVL(postable, 'Y') = 'Y'
1139 and adj.adjustment_id = ard.source_id
1140 and ard.source_table = 'ADJ'
1141 and adj.customer_trx_id = ct.customer_trx_id
1142 and ct.customer_trx_id = ps.customer_trx_id
1143 and xle.event_id = adj.event_id
1144 and xle.event_status_code in ('U')
1145 and xle.process_status_code IN ('I','R');
1146
1147
1148
1149 l_rowcount := sql%rowcount;
1150
1151 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_ADJ||' is:'||l_rowcount);
1152
1153 l_rowcount := NULL;
1154
1155 --------------
1156 -- BILLS RECEIVABLE --
1157 --------------
1158
1159 <<th_processing>>
1160
1161 insert into ar_period_close_excps_gt
1162 ( document_type
1163 , customer_trx_id
1164 , trx_number
1165 , cash_receipt_id
1166 , receipt_number
1167 , adjustment_id
1168 , adjustment_number
1169 , transaction_history_id
1170 , cust_trx_line_gl_dist_id
1171 , account_class
1172 , dist_line_id
1173 , dist_source_id
1174 , dist_source_table
1175 , dist_source_type
1176 , event_id
1177 , gl_date
1178 , amount_dr
1179 , amount_cr
1180 , acctd_amount_dr
1181 , acctd_amount_cr
1182 , org_id
1183 , currency_code
1184 , customer_id
1185 , payment_schedule_id
1186 , applied_payment_schedule_id
1187 )
1188 select G_SRC_TYP_UNACCT_BR
1189 , ct.customer_trx_id
1190 , ct.trx_number
1191 , null
1192 , null
1193 , null
1194 , null
1195 , null
1196 , null
1197 , null
1198 , ard.line_id
1199 , ard.source_id
1200 , ard.source_table
1201 , ard.source_type
1202 , th.event_id
1203 , th.gl_date
1204 , ard.amount_dr
1205 , ard.amount_cr
1206 , ard.acctd_amount_dr
1210 , ct.drawee_id
1207 , ard.acctd_amount_cr
1208 , th.org_id
1209 , ct.invoice_currency_code
1211 , ps.payment_schedule_id
1212 , null
1213 FROM ar_transaction_history_all th
1214 , xla_events xle
1215 , ar_distributions_all ard
1216 , ra_customer_trx_all ct
1217 , ar_payment_schedules_all ps
1218 WHERE th.gl_date between g_period_start_date and g_period_end_date
1219 and th.posting_control_id = -3
1220 and NVL(th.postable_flag, 'Y') = 'Y'
1221 and th.transaction_history_id = ard.source_id
1222 and ard.source_table = 'TH'
1223 and th.customer_trx_id = ct.customer_trx_id
1224 and ct.set_of_books_id = g_ledger_id
1225 and ct.customer_trx_id = ps.customer_trx_id
1226 and xle.event_id = th.event_id
1227 and xle.event_status_code in ('U')
1228 and xle.process_status_code IN ('I','R');
1229
1230
1231
1232 l_rowcount := sql%rowcount;
1233
1234 arp_standard.debug ('Total records inserted in ar_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_BR||' is:'||l_rowcount);
1235
1236 l_rowcount := NULL;
1237
1238
1239 -- If an invoice / cm has posted applications against it with gl_date greater than REC gl_date and less than sweep_to_date
1240 -- then mark the invoice distributions as 'OTHER_EXCEPTIONS' as these cant be sweeped
1241 <<update_gld_other_exceptions>>
1242 UPDATE ar_period_close_excps_gt
1243 SET document_type = G_SRC_TYP_OTHER_EXCPS
1244 WHERE customer_trx_id IN (SELECT pce.customer_trx_id
1245 FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
1246 WHERE pce.document_type = G_SRC_TYP_UNACCT_TRX
1247 AND pce.account_class = 'REC'
1248 AND pce.customer_trx_id = ra.customer_trx_id
1249 AND ra.gl_date between pce.gl_date and g_period_end_date
1250 AND ra.posting_control_id <> -3
1251 UNION
1252 SELECT pce.customer_trx_id
1253 FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
1254 WHERE pce.document_type = G_SRC_TYP_UNACCT_TRX
1255 AND pce.account_class = 'REC'
1256 AND pce.customer_trx_id = ra.applied_customer_trx_id
1257 AND ra.gl_date between pce.gl_date and g_period_end_date
1258 AND ra.posting_control_id <> -3
1259 );
1260
1261 -- If a RECEIPT has posted applications/CRH activities against it with gl_date greater than gl_date being sweeped and less than sweep_to_date
1262 -- then mark the CRH/RA records as 'OTHER_EXCEPTIONS' as these cant be sweeped
1263 <<update_rct_other_exceptions>>
1264 UPDATE ar_period_close_excps_gt
1265 SET document_type = G_SRC_TYP_OTHER_EXCPS
1266 WHERE cash_receipt_id IN (SELECT pce.customer_trx_id
1267 FROM ar_period_close_excps_gt pce, ar_receivable_applications_all ra
1268 WHERE pce.document_type = G_SRC_TYP_UNACCT_RCT
1269 AND ra.receivable_application_id = pce.dist_source_id
1270 AND pce.dist_source_table = 'RA'
1271 AND ra.gl_date between pce.gl_date and g_period_end_date
1272 AND ra.posting_control_id <> -3
1273 UNION
1274 SELECT pce.customer_trx_id
1275 FROM ar_period_close_excps_gt pce, ar_cash_receipt_history_all crh
1276 WHERE pce.document_type = G_SRC_TYP_UNACCT_RCT
1277 AND crh.cash_receipt_history_id = pce.dist_source_id
1278 AND pce.dist_source_table = 'CRH'
1279 AND crh.gl_date between pce.gl_date and g_period_end_date
1280 AND crh.posting_control_id <> -3
1281 );
1282
1283 return null;
1284
1285 end get_unposted_transactions;
1286
1287
1288 /*------------------------------------------------------------------------------------------------------------------------*/
1289 procedure validate_parameters
1290 ( p_validation_flag out nocopy varchar2
1291 ,p_validation_message out nocopy varchar2
1292 )
1293 is
1294
1295 CURSOR c_get_period_dates (p_period_name gl_period_statuses.period_name%type default g_period_name
1296 ,p_include_adj_period gl_period_statuses.adjustment_period_flag%type default null
1297 )
1298 IS
1299 SELECT start_date, end_date, closing_status
1300 FROM gl_period_statuses
1301 WHERE period_name = p_period_name
1302 AND application_id = G_AR_APPLICATION_ID
1303 AND set_of_books_id = g_ledger_id
1304 AND (p_include_adj_period is null or (nvl(adjustment_period_flag,'N') = p_include_adj_period));
1305
1306 ld_period_start_date gl_period_statuses.start_date%type;
1307 ld_period_end_date gl_period_statuses.end_date%type;
1308 lv_closing_status gl_period_statuses.closing_status%type;
1309 ld_sweep_to_end_date gl_period_statuses.end_date%type;
1310
1311 begin
1312
1313 if g_period_name is not null then
1314 open c_get_period_dates;
1318 close c_get_period_dates;
1315 fetch c_get_period_dates into ld_period_start_date
1316 , ld_period_end_date
1317 , lv_closing_status;
1319
1320 arp_standard.debug ('cursor c_get_period_dates: ld_period_start_date='||ld_period_start_date
1321 ||'; ld_period_end_date='||ld_period_end_date
1322 ||'; lv_closing_status='||lv_closing_status
1323 );
1324
1325 g_period_start_date := ld_period_start_date;
1326 g_period_end_date := ld_period_end_date;
1327
1328 end if;
1329
1330 if lv_closing_status not in ('O','W') then
1331 p_validation_flag := 'EE';
1332 p_validation_message := 'AR_ALL_NOT_OPEN_PERIOD';
1333 return;
1334 end if;
1335
1336
1337 if (g_sweep_now = 'Y') then
1338
1339 -- Validation: To SWEEP, paramter sweep_to_period must be given
1340
1341 if g_sweep_period_name is null then
1342 p_validation_flag := 'EE';
1343 p_validation_message := 'AR_SWEEP_TO_PERIOD_REQ';
1344 return;
1345 end if;
1346
1347 lv_closing_status :=null;
1348
1349 open c_get_period_dates (p_period_name => g_sweep_period_name
1350 ,p_include_adj_period => 'N'
1351 );
1352 fetch c_get_period_dates into g_sweep_to_date
1353 ,ld_sweep_to_end_date
1354 ,lv_closing_status;
1355 close c_get_period_dates;
1356
1357 arp_standard.debug ('cursor c_get_period_dates (p_period_name=>'||g_sweep_period_name||',p_include_adj_period=N');
1358 arp_standard.debug ('cursor c_get_period_dates: g_sweep_to_date='||g_sweep_to_date
1359 ||'; ld_sweep_to_end_date='||ld_sweep_to_end_date
1360 ||'; lv_closing_status='||lv_closing_status
1361 );
1362
1363 --
1364 -- Check that sweep to date is valid
1365 -- Sweep to date is invalid if
1366 -- 1. It is NULL
1367 -- 2. It is prior to the start date of the current period (the period being closed/swept)
1368 -- 3. If it is in closed period
1369 --
1370
1371 if g_sweep_to_date is null
1372 or g_sweep_to_date <= g_period_end_date
1373 or lv_closing_status not in ('O','F')
1374 then
1375
1376 p_validation_flag := 'EE';
1377 p_validation_message := 'AR_INVALID_SWEEP_PERIOD';
1378 end if;
1379
1380 end if;
1381
1382 if p_validation_flag <> 'EE' then
1383 p_validation_flag := 'SS';
1384 p_validation_message := '';
1385 end if;
1386
1387 end validate_parameters;
1388
1389
1390 PROCEDURE process_period(
1391 p_reporting_level IN number,
1392 p_reporting_entity_id IN NUMBER,
1393 p_period_name IN VARCHAR2,
1394 p_sweep_period_name IN VARCHAR2,
1395 p_sweep_now IN VARCHAR2,
1396 p_process_flag out nocopy varchar2,
1397 p_process_message out nocopy varchar2
1398 )
1399 IS
1400
1401 lv_dummy varchar2(3);
1402
1403 BEGIN
1404 IF G_DEBUG in ('Y', 'C') THEN
1405 arp_standard.debug( 'AR_SWEEP_UNACCOUNTED_TRX.sweep()+');
1406 arp_standard.debug( 'p_reporting_level '|| p_reporting_level);
1407 arp_standard.debug( 'p_reporting_entity_id '|| p_reporting_entity_id);
1408 arp_standard.debug( 'p_period_name '|| p_period_name);
1409 arp_standard.debug( 'p_sweep_period_name '|| p_sweep_period_name);
1410 arp_standard.debug( 'p_sweep_now '|| p_sweep_now);
1411 END IF;
1412
1413 g_reporting_level := p_reporting_level;
1414 g_reporting_entity_id := p_reporting_entity_id;
1415 g_period_name := p_period_name;
1416 g_sweep_period_name := p_sweep_period_name;
1417 g_sweep_now := p_sweep_now;
1418
1419
1420 IF (p_reporting_level = '1000') THEN
1421 g_ledger_id := p_reporting_entity_id;
1422
1423 ELSIF (p_reporting_level = '3000') THEN
1424
1425 SELECT set_of_books_id
1426 INTO g_ledger_id
1427 FROM ar_system_parameters_all
1428 WHERE org_id = p_reporting_entity_id;
1429
1430 g_org_id := p_reporting_entity_id;
1431
1432 END IF;
1433
1434 --
1435 -- validate the input paramters and also performs the initialization
1436 --
1437 validate_parameters
1438 (p_validation_flag => p_process_flag
1439 ,p_validation_message => p_process_message
1440 );
1441
1442 arp_standard.debug ('validate_parameters: flag='||p_process_flag ||'; message='|| p_process_message);
1443 if (p_process_flag <> 'SS') then
1444 -- parameters are not proper hence should avoid processing further
1445 return;
1446 end if;
1447
1448 --
1449 -- Get Unposted Transactions
1450 --
1451 lv_dummy := get_unposted_transactions;
1452 arp_standard.debug ('get_unposted_transaction: return value='||lv_dummy);
1453
1454 --
1455 -- Sweep unposted Transactions
1456 --
1457 if g_sweep_now = 'Y' then
1458
1459 arp_standard.debug ('begin sweep_transactions: current timestamp is= '||current_timestamp);
1460
1461 if NOT sweep_transactions then -- perform the SWEEP logic
1462 p_process_flag := 'EE';
1463 p_process_message := 'AR_SWEEP_FAILED';
1464 return;
1465 end if;
1466
1467 arp_standard.debug ('sweep_transactions: flag='||p_process_flag||'; message='||p_process_message);
1468 arp_standard.debug ('end sweep_transactions: current timestamp is= '||current_timestamp);
1469
1470 end if;
1471
1472 arp_standard.debug ('end process period: current timestamp is= '||current_timestamp);
1473 p_process_flag := 'SS';
1474
1475 EXCEPTION WHEN OTHERS THEN
1476 p_process_flag := 'UE';
1477 p_process_message:='ERROR: process_period:' || sqlerrm;
1478 arp_standard.debug ('EXCEPTION: process_period: '||sqlerrm);
1479 END process_period;
1480
1481
1482 FUNCTION before_report_artrxswp RETURN BOOLEAN IS
1483 lv_process_flag varchar2 (2);
1484 lv_process_message varchar2 (2000);
1485
1486 BEGIN
1487
1488 arp_standard.debug ('begin before_report_artrxswp: current timestamp:' || current_timestamp);
1489
1490 process_period(p_reporting_level => P_REPORTING_LEVEL,
1491 p_reporting_entity_id => P_REPORTING_ENTITY_ID,
1492 p_period_name => P_PERIOD_NAME,
1493 p_sweep_period_name => P_SWEEP_TO_PERIOD,
1494 p_sweep_now => P_SWEEP_NOW
1495 ,p_process_flag => lv_process_flag
1496 ,p_process_message => lv_process_message
1497 );
1498
1499 arp_standard.debug ('end before_report_artrxswp: current timestamp: '|| current_timestamp);
1500 IF lv_process_flag = 'SS' THEN
1501 RETURN TRUE;
1502 ELSE
1503 RETURN FALSE;
1504 END IF;
1505
1506 EXCEPTION WHEN OTHERS THEN
1507 arp_standard.debug ('EXCEPTION: Before Report: '||sqlerrm);
1508 RAISE;
1509 END;
1510
1511 FUNCTION get_ledger_name RETURN varchar2
1512 IS
1513 l_ledger varchar2(100);
1514 begin
1515 select name into l_ledger from GL_SETS_OF_BOOKS
1516 where set_of_books_id=g_ledger_id ;
1517
1518 return l_ledger;
1519 EXCEPTION WHEN OTHERS THEN
1520 arp_standard.debug ('EXCEPTION: get_ledger_name: '||sqlerrm);
1521 END get_ledger_name;
1522
1523
1524 FUNCTION get_acct_start_date RETURN varchar2 IS
1525 begin
1526 return to_char(g_period_start_date);
1527 END get_acct_start_date;
1528
1529 FUNCTION get_acct_end_date RETURN varchar2 IS
1530 begin
1531 return to_char(g_period_end_date);
1532 END get_acct_end_date;
1533 End;
1534
1535