[Home] [Help]
PACKAGE BODY: APPS.AP_PERIOD_CLOSE_PKG
Source
1 package body ap_period_close_pkg as
2 /* $Header: apprdclb.pls 120.30.12020000.3 2012/10/30 21:44:58 vinaik ship $ */
3
4
5 cursor c_get_period_dates (cp_period_name gl_period_statuses.period_name%type default g_period_name
6 ,cp_include_adj_period gl_period_statuses.adjustment_period_flag%type default null
7 )
8 is
9 SELECT start_date, end_date, closing_status
10 FROM gl_period_statuses
11 WHERE period_name = cp_period_name
12 AND application_id = G_AP_APPLICATION_ID
13 AND set_of_books_id = g_ledger_id
14 and (cp_include_adj_period is null or (nvl(adjustment_period_flag,'N') = cp_include_adj_period));
15
16 cursor c_ledger_attribs
17 is
18 /* -- changed the sql for the bug 10126216 */
19 select name, (SELECT DECODE(count(1), 0, 'Y', 'N')
20 FROM xla_ledger_relationships_v xlr,
21 gl_ledgers gl
22 WHERE xlr.primary_ledger_id = gsob.set_of_books_id
23 AND xlr.relationship_enabled_flag = 'Y'
24 AND gl.sla_ledger_cash_basis_flag <> 'Y'
25 AND xlr.ledger_id = gl.ledger_id
26 AND EXISTS (SELECT 1
27 FROM xla_ledger_options xlo
28 WHERE application_id = 200
29 AND DECODE(xlr.ledger_category_code
30 ,'ALC',xlr.ledger_id
31 ,xlo.ledger_id) = xlr.ledger_id
32 AND DECODE(xlr.ledger_category_code
33 ,'SECONDARY',xlo.capture_event_flag
34 ,'N') = 'N'
35 AND DECODE(xlr.ledger_category_code
36 ,'ALC','Y'
37 ,xlo.enabled_flag) = 'Y'
38 )
39 ) sla_ledger_cash_basis_flag
40 from gl_sets_of_books gsob
41 where set_of_books_id = g_ledger_id;
42
43 /*------------------------------------------------------------------------------------------------------------------------*/
44 PROCEDURE Print
45 (
46 p_string IN VARCHAR2
47 )
48 IS
49 lv_stemp VARCHAR2(80);
50 ln_length NUMBER := 1;
51 BEGIN
52
53 WHILE(length(P_string) >= ln_length)
54 LOOP
55
56 lv_stemp := substrb(P_string, ln_length, 80);
57 fnd_file.put_line(FND_FILE.LOG, lv_stemp);
58 ln_length := (ln_length + 80);
59
60 END LOOP;
61
62 EXCEPTION
63 WHEN OTHERS THEN
64 IF (SQLCODE <> -20001) THEN
65 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
66 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
67 END IF;
68 APP_EXCEPTION.RAISE_EXCEPTION;
69
70 END Print;
71
72 /******************************************************************************/
73 procedure debug (p_debug_msg in varchar2)
74 is
75 begin
76 print (p_debug_msg);
77 end debug;
78
79 /******************************************************************************/
80 /*Bug 9813947 added function get_period_status*/
81 FUNCTION get_period_status(
82 g_period_start_date IN DATE,
83 g_period_end_date IN DATE)
84 RETURN VARCHAR2
85 IS
86 g_period_status VARCHAR2(1);
87 BEGIN
88 SELECT closing_status
89 INTO g_period_status
90 FROM gl_period_statuses
91 WHERE g_period_start_date BETWEEN start_date AND end_date
92 AND g_period_end_date BETWEEN start_date AND end_date
93 AND application_id = G_AP_APPLICATION_ID
94 AND set_of_books_id = g_ledger_id;
95
96 RETURN g_period_status;
97 EXCEPTION
98 WHEN OTHERS THEN
99 debug ('EXCEPTION: In get_period_status: '||sqlerrm);
100 return null;
101 END;
102 /******************************************************************************/
103
104 procedure populate_orgs (p_ledger_id number
105 ,p_process_flag out nocopy varchar2
106 ,p_process_message out nocopy varchar2
107 )
108 is
109 ln_org_cnt number :=0;
110 begin
111 --
112 -- we are fetching all the operating units defined under ledger and populating
113 -- a GTT
114 --
115 for r_org in c_get_all_orgs
116 loop
117
118 insert into ap_org_attributes_gt
119 (org_name
120 ,org_id
121 ,recon_accounting_flag
122 ,when_to_account_pmt
123 ,set_of_books_id
124 )
125 values (r_org.operating_unit_name
126 ,r_org.org_id
127 ,r_org.recon_accounting_flag
128 ,r_org.when_to_account_pmt
129 ,r_org.set_of_books_id
130 );
131 ln_org_cnt := ln_org_cnt + 1;
132
133
134 end loop;
135
136 if ln_org_cnt = 0 then
137 p_process_flag := 'EE';
138 p_process_message := 'AP_INVALID_LEDGER';
139 return;
140 end if;
141
142 debug ('populate_orgs: total orgs populated in ap_org_attributes_gt= '||ln_org_cnt);
143
144 p_process_flag := 'SS';
145 p_process_message := null;
146
147 end populate_orgs;
148
149 /*------------------------------------------------------------------------------------------------------------------------*/
150 --
151 -- get_unposted_transactions
152 -- contains logic to derive unposted (exceptional) invoice distributions, lines and
153 -- payment related transaction. It operate in two different mode.
154 -- if action = PERIOD_CLOSE, it will populate only one row to check for existance of
155 -- such exceptions and returns immediately if any.
156 -- For action other than PERIOD_CLOSE it actually poupulates all the rows
157 --
158 /*------------------------------------------------------------------------------------------------------------------------*/
159 function get_unposted_transactions
160 return varchar2
161 is
162
163 /*Bug#7649020: Defining table collection type to hold exception
164 data reported by SLA cursors.
165 For bugs related to events not picked by SLA cursors log a
166 bug against SLA team.
167 */
168 type xla_period_close_header_tab is table of XLA_PERIOD_CLOSE_EXP_PKG.period_close_hdr_date_cur%rowtype;
169 xla_headers_untransfered xla_period_close_header_tab := xla_period_close_header_tab();
170
171 type xla_period_close_evt_tab is table of XLA_PERIOD_CLOSE_EXP_PKG.period_close_evt_date_cur%rowtype;
172 xla_events_unacct xla_period_close_evt_tab := xla_period_close_evt_tab();
173
174 l_rowcount NUMBER := NULL; -- bug 9509700
175
176 begin
177
178 --
179 -- Invoice processing is not required If ledger is set to CASH based accounting
180 --
181
182
183 debug ('g_cash_basis_flag='||g_cash_basis_flag);
184 --TODO Need to figure out from XLA about how to derive the accounting method - Cash/Accrual.
185 if g_cash_basis_flag <> 'Y' then
186
187 --------------
188 -- INVOICES --
189 --------------
190
191 <<invoice_processing>>
192
193 -- insert statement will populate all un-posted invoice distributions
194 -- if action is PERIOD_CLOSE only one row will be fetched to check
195 -- existance unposted transactions
196 --
197 -- bug9553092, interest invoices would be picked up for sweep and display
198 -- in the GT table only when the corresponding payment event is a problem
199 -- for the period close processing
200 --
201 insert into ap_period_close_excps_gt
202 ( invoice_id
203 ,invoice_distribution_id
204 ,invoice_payment_id -- 7318763
205 ,accounting_event_id
206 ,accounting_date
207 ,org_id
208 ,invoice_num
209 ,invoice_currency_code
210 ,party_id
211 ,vendor_id
212 ,doc_sequence_value
213 ,voucher_num
214 ,invoice_date
215 ,invoice_amount
216 ,cancelled_date
217 ,match_status_flag
218 ,legal_entity_id
219 ,po_distribution_id
220 ,amount
221 ,detail_tax_dist_id
222 ,invoice_line_number
223 ,source_type
224 ,source_table_name
225 )
226 select ai.invoice_id
227 ,aid.invoice_distribution_id
228 ,aid.awt_invoice_payment_id -- 7318763
229 ,aid.accounting_event_id
230 ,aid.accounting_date
231 ,aid.org_id
232 ,ai.invoice_num
233 ,ai.invoice_currency_code
234 ,ai.party_id
235 ,ai.vendor_id
236 ,ai.doc_sequence_value
237 ,ai.voucher_num
238 ,ai.invoice_date
239 ,ai.invoice_amount
240 ,ai.cancelled_date
241 ,aid.match_status_flag
242 ,ai.legal_entity_id
243 ,aid.po_distribution_id
244 ,aid.amount
245 ,aid.detail_tax_dist_id
246 ,aid.invoice_line_number
247 ,G_SRC_TYP_UNACCT_DISTS
248 ,G_SRC_TAB_AP_INV_DISTS_ALL
249 from
250 ap_invoices_all ai
251 ,ap_invoice_distributions_all aid
252 ,ap_org_attributes_gt org_gtt
253 where
254 ai.invoice_id = aid.invoice_id
255 and ( aid.accounting_date between g_period_start_date and g_period_end_date)
256 and aid.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
257 and aid.set_of_books_id = g_ledger_id
258 and aid.org_id = org_gtt.org_id
259 and ( g_action <> G_ACTION_PERIOD_CLOSE
260 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
261 )
262 and ai.approval_ready_flag <> 'S' --bug 9224843
263 and not exists
264 (select 1
265 from ap_payment_history_all aph
266 where aph.accounting_event_id = aid.accounting_event_id
267 and ai.invoice_type_lookup_code = 'INTEREST'
268 and nvl(org_gtt.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY'
269 and aph.transaction_type not in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING'))
270 UNION
271 -- added for the bug11881258
272 select ai.invoice_id
273 ,aid.invoice_distribution_id
274 ,aid.awt_invoice_payment_id -- 7318763
275 ,aid.bc_event_id
276 ,aid.accounting_date
277 ,aid.org_id
278 ,ai.invoice_num
279 ,ai.invoice_currency_code
280 ,ai.party_id
281 ,ai.vendor_id
282 ,ai.doc_sequence_value
283 ,ai.voucher_num
284 ,ai.invoice_date
285 ,ai.invoice_amount
286 ,ai.cancelled_date
287 ,aid.match_status_flag
288 ,ai.legal_entity_id
289 ,aid.po_distribution_id
290 ,aid.amount
291 ,aid.detail_tax_dist_id
292 ,aid.invoice_line_number
293 ,G_SRC_TYP_UNACCT_DISTS
294 ,G_SRC_TAB_AP_INV_DISTS_ALL
295 from ap_invoices_all ai
296 ,ap_invoice_distributions_all aid
297 ,ap_org_attributes_gt org_gtt
298 where ai.invoice_id = aid.invoice_id
299 and ( aid.accounting_date between g_period_start_date and g_period_end_date)
300 and aid.posted_flag in ('N' , 'S', 'P')
301 and aid.bc_event_id is not null
302 and nvl(aid.encumbered_flag, 'N') in ('N', 'H', 'P')
303 and aid.set_of_books_id = g_ledger_id
304 and aid.org_id = org_gtt.org_id
305 and ( g_action <> G_ACTION_PERIOD_CLOSE
306 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 )
307 )
308 and ai.approval_ready_flag <> 'S'
309 and not exists
310 (select 1
311 from ap_payment_history_all aph
312 where aph.accounting_event_id = aid.accounting_event_id
313 and ai.invoice_type_lookup_code = 'INTEREST'
314 and nvl(org_gtt.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY'
315 and aph.transaction_type not in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING'))
316 ;
317
318
319 l_rowcount := sql%rowcount; -- bug 9509700
320
321 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||' is:'||l_rowcount);
322
323 if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
324 -- current action is PERIOD_CLOSE and there are unposted invoices
325 -- and we cannot allow to close period hence return
326 return 'Y';
327 end if;
328
329 l_rowcount := NULL;
330
331 insert into ap_period_close_excps_gt
332 ( invoice_id
333 ,invoice_distribution_id
334 ,accounting_event_id
335 ,accounting_date
336 ,org_id
337 ,invoice_num
338 ,invoice_currency_code
339 ,party_id
340 ,vendor_id
341 ,doc_sequence_value
342 ,voucher_num
343 ,invoice_date
344 ,invoice_amount
345 ,cancelled_date
346 ,match_status_flag
347 ,legal_entity_id
348 ,po_distribution_id
349 ,amount
350 ,detail_tax_dist_id
351 ,source_type
352 ,source_table_name
353 )
354 select ai.invoice_id
355 ,astd.invoice_distribution_id
356 ,astd.accounting_event_id
357 ,astd.accounting_date
358 ,astd.org_id
359 ,ai.invoice_num
360 ,ai.invoice_currency_code
361 ,ai.party_id
362 ,ai.vendor_id
363 ,ai.doc_sequence_value
364 ,ai.voucher_num
365 ,ai.invoice_date
366 ,ai.invoice_amount
367 ,ai.cancelled_date
368 ,astd.match_status_flag
369 ,ai.legal_entity_id
370 ,astd.po_distribution_id
371 ,astd.amount
372 ,astd.detail_tax_dist_id
373 ,G_SRC_TYP_UNACCT_DISTS
374 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
375 from
376 ap_invoices_all ai
377 ,ap_self_assessed_tax_dist_all astd
378 ,ap_org_attributes_gt org_gtt
379 where
380 ai.invoice_id = astd.invoice_id
381 and (astd.accounting_date between g_period_start_date and g_period_end_date )
382 and astd.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
383 and astd.set_of_books_id = g_ledger_id
384 and astd.org_id = org_gtt.org_id
385 and ( g_action <> G_ACTION_PERIOD_CLOSE
386 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
387 )
388 and ai.approval_ready_flag <> 'S'
389 union
390 -- added for the bug11881258
391 select ai.invoice_id
392 ,astd.invoice_distribution_id
393 ,astd.bc_event_id
394 ,astd.accounting_date
395 ,astd.org_id
396 ,ai.invoice_num
397 ,ai.invoice_currency_code
398 ,ai.party_id
399 ,ai.vendor_id
400 ,ai.doc_sequence_value
401 ,ai.voucher_num
402 ,ai.invoice_date
403 ,ai.invoice_amount
404 ,ai.cancelled_date
405 ,astd.match_status_flag
406 ,ai.legal_entity_id
407 ,astd.po_distribution_id
408 ,astd.amount
409 ,astd.detail_tax_dist_id
410 ,G_SRC_TYP_UNACCT_DISTS
411 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
412 from
413 ap_invoices_all ai
414 ,ap_self_assessed_tax_dist_all astd
415 ,ap_org_attributes_gt org_gtt
416 where
417 ai.invoice_id = astd.invoice_id
418 and (astd.accounting_date between g_period_start_date and g_period_end_date )
419 and astd.posted_flag in ('N' , 'S', 'P')
420 and astd.bc_event_id is not null
421 and nvl(astd.encumbered_flag, 'N') in ('N', 'H', 'P')
422 and astd.set_of_books_id = g_ledger_id
423 and astd.org_id = org_gtt.org_id
424 and ( g_action <> G_ACTION_PERIOD_CLOSE
425 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 )
426 )
427 and ai.approval_ready_flag <> 'S';
428
429
430 l_rowcount := sql%rowcount; -- bug 9509700
431
432 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_DISTS||'for table='||G_SRC_TAB_AP_SELF_TAX_DIST_ALL
433 || ' is:'||l_rowcount);
434
435 if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
436 -- current action is PERIOD_CLOSE and there are unposted self assessed tax dists
437 -- and we cannot allow to close period hence return
438 return 'Y';
439 end if;
440
441 l_rowcount := NULL;
442 /* Removed the Hint Bug#8870730 */
443
444 /* bug 11702640 Made changes to the select statement below */
445 insert into ap_period_close_excps_gt
446 ( invoice_id
447 ,invoice_line_number
448 ,accounting_date
449 ,org_id
450 ,invoice_num
451 ,invoice_currency_code
452 ,party_id
453 ,vendor_id
454 ,doc_sequence_value
455 ,voucher_num
456 ,invoice_date
457 ,invoice_amount
458 ,cancelled_date
459 ,source_type
460 ,source_table_name
461 )
462 select /*+ leading(org_gtt,ail,aid,ai) */ ai.invoice_id
463 ,ail.line_number
464 ,ail.accounting_date
465 ,ail.org_id
466 ,ai.invoice_num
467 ,ai.invoice_currency_code
468 ,ai.party_id
469 ,ai.vendor_id
470 ,ai.doc_sequence_value
471 ,ai.voucher_num
472 ,ai.invoice_date
473 ,ai.invoice_amount
474 ,ai.cancelled_date
475 ,G_SRC_TYP_LINES_WITHOUT_DISTS
476 ,G_SRC_TAB_AP_INV_LINES_ALL
477 from
478 ap_invoices_all ai
479 ,ap_invoice_lines_all ail
480 ,ap_org_attributes_gt org_gtt
481 where
482 ai.invoice_id = ail.invoice_id
483 and (ail.accounting_date between g_period_start_date and g_period_end_date)
484 and not exists (select /*+ nl_aj */ 1 --> lines without distributions
485 from ap_invoice_distributions_all aid
486 where aid.invoice_id = ail.invoice_id
487 and aid.invoice_line_number = ail.line_number
488 and aid.org_id = org_gtt.org_id
489 )
490 --Bug 7242216 Excluding invoices having discarded lines with
491 --no distributions
492 and ail.discarded_flag <> 'Y'
493 /* Bug 14660916 */
494 and ( (ail.amount <> 0 )
495 OR
496 (ail.amount = 0
497 and (ail.default_dist_ccid is not NULL
498 or ail.distribution_set_id is not null ))) /* Bug 14660916 */
499 and ai.cancelled_date is null
500 and ail.set_of_books_id = g_ledger_id
501 and ail.org_id = org_gtt.org_id
502 and ( g_action <> G_ACTION_PERIOD_CLOSE
503 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
504 )
505 and ai.approval_ready_flag <> 'S'; --bug 9224843
506
507 l_rowcount := sql%rowcount; -- bug 9509700
508
509 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_LINES_WITHOUT_DISTS||' is:'||l_rowcount);
510
511 if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
512 -- current action is PERIOD_CLOSE and there are lines without any distributions
513 -- so we cannot allow to close perio, hence return
514 return 'Y';
515 end if;
516
517 l_rowcount := NULL;
518
519 -- gagrawal
520 insert into ap_period_close_excps_gt
521 ( invoice_id
522 ,accounting_event_id
523 ,accounting_date
524 ,org_id
525 ,invoice_num
526 ,invoice_currency_code
527 ,party_id
528 ,vendor_id
529 ,doc_sequence_value
530 ,voucher_num
531 ,invoice_date
532 ,invoice_amount
533 ,cancelled_date
534 ,legal_entity_id
535 ,source_type
536 ,source_table_name
537 )
538 select ai.invoice_id
539 ,apph.accounting_event_id
540 ,apph.accounting_date
541 ,ai.org_id
542 ,ai.invoice_num
543 ,ai.invoice_currency_code
544 ,ai.party_id
545 ,ai.vendor_id
546 ,ai.doc_sequence_value
547 ,ai.voucher_num
548 ,ai.invoice_date
549 ,ai.invoice_amount
550 ,ai.cancelled_date
551 ,ai.legal_entity_id
552 ,G_SRC_TYP_UNACCT_PREPAY_HIST
553 ,G_SRC_TAB_AP_PREPAY_HIST
554 from ap_invoices_all ai
555 ,ap_prepay_history_all apph
556 ,ap_org_attributes_gt org_gtt
557 where
558 ai.invoice_id = apph.invoice_id
559 and ( apph.accounting_date between g_period_start_date and g_period_end_date)
560 and apph.posted_flag in ('N' , 'S', 'P') -- N=Not Accounted, S=Selected for Accounting, P=Partially Accounted for CASH based accounting
561 and apph.accounting_event_id IS NOT NULL
562 and ai.set_of_books_id = g_ledger_id
563 -- and ai.org_id = org_gtt.org_id commented for 13416897
564 and apph.org_id = org_gtt.org_id -- new condition for 13416897
565 and ( g_action <> G_ACTION_PERIOD_CLOSE
566 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
567 );
568
569 l_rowcount := sql%rowcount; -- bug 9509700
570
571
572 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PREPAY_HIST||' is:'||l_rowcount);
573
574 if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
575 -- current action is PERIOD_CLOSE and there are lines without any distributions
576 -- so we cannot allow to close perio, hence return
577 return 'Y';
578 end if;
579
580 l_rowcount := NULL;
581
582 end if; --> g_cash_basis_flag <> 'Y'
583
584
585 ---------------
586 -- PAYMENTS --
587 ----------------
588 <<payment_processing>>
589
590 INSERT INTO AP_PERIOD_CLOSE_EXCPS_GT
591 (payment_history_id
592 ,accounting_event_id
593 ,accounting_date
594 ,check_id
595 ,transaction_type
596 ,org_id
597 ,recon_accounting_flag
598 ,check_number
599 ,exchange_rate
600 ,check_date
601 ,legal_entity_id
602 ,vendor_name
603 ,bank_account_name
604 ,check_amount
605 ,currency_code
606 ,party_id
607 ,vendor_id
608 ,source_type
609 ,source_table_name
610 )
611 SELECT aph.payment_history_id,
612 aph.accounting_event_id,
613 aph.accounting_date,
614 aph.check_id,
615 aph.transaction_type,
616 aph.org_id,
617 orgs.recon_accounting_flag,
618 ac.check_number,
619 ac.exchange_rate,
620 ac.check_date,
621 ac.legal_entity_id,
622 ac.vendor_name,
623 ac.bank_account_name,
624 --ac.amount, --bug 7416004
625 decode(aph.transaction_type,'PAYMENT CANCELLED',(-1*ac.amount),
626 'REFUND CANCELLED',(-1*ac.amount),
627 ac.amount),
628 ac.currency_code,
629 ac.party_id,
630 ac.vendor_id
631 ,G_SRC_TYP_UNACCT_PMT_HISTORY
632 ,G_SRC_TAB_AP_PMT_HISTORY
633 FROM ap_payment_history_all aph,
634 ap_checks_all ac,
635 ap_org_attributes_gt orgs
636 WHERE aph.posted_flag IN ('N','S')
637 AND ac.check_id = aph.check_id
638 and (aph.accounting_date between g_period_start_date and g_period_end_date)
639 AND aph.org_id = orgs.org_id
640 AND ( NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' or
641 (NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY' and
642 aph.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING')))
643 and ( g_action <> G_ACTION_PERIOD_CLOSE
644 OR (g_action = G_ACTION_PERIOD_CLOSE and ROWNUM = 1 ) -- for period close we just need check if any such record exists
645 );
646
647 l_rowcount := sql%rowcount; -- bug 9509700
648
649 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_PMT_HISTORY||' is:'||l_rowcount);
650
651 if g_action = G_ACTION_PERIOD_CLOSE and l_rowcount > 0 then
652 -- current action is PERIOD_CLOSE and there are lines without any distributions
653 -- so we cannot allow to close period, hence return
654 return 'Y';
655 end if;
656
657 l_rowcount := NULL;
658
659 if g_action = G_ACTION_SWEEP then -- populate GT ONLY when sweeping
660 -- get unaccounted invoice payments
661 insert into ap_period_close_excps_gt
662 (invoice_payment_id
663 ,accounting_event_id
664 ,accounting_date
665 ,check_id
666 ,payment_amount
667 ,org_id
668 ,recon_accounting_flag
669 ,check_number
670 ,exchange_rate
671 ,check_date
672 ,legal_entity_id
673 ,vendor_name
674 ,bank_account_name
675 ,check_amount
676 ,currency_code
677 ,status_lookup_code
678 ,party_id
679 ,vendor_id
680 ,source_type
681 ,source_table_name
682 )
683 SELECT aip.invoice_payment_id,
684 aip.accounting_event_id,
685 aip.accounting_date,
686 aip.check_id,
687 aip.amount,
688 aip.org_id,
689 orgs.recon_accounting_flag,
690 ac.check_number,
691 ac.exchange_rate,
692 ac.check_date,
693 ac.legal_entity_id,
694 ac.vendor_name,
695 ac.bank_account_name,
696 ac.amount,
697 ac.currency_code,
698 ac.status_lookup_code,
699 ac.party_id,
700 ac.vendor_id
701 ,G_SRC_TYP_UNACCT_INV_PMTS
702 ,G_SRC_TAB_AP_INV_PAYMENTS
703 FROM ap_invoice_payments_all aip,
704 ap_checks_All ac,
705 ap_org_attributes_gt orgs
706 WHERE aip.posted_flag IN ('N','S')
707 and (aip.accounting_date between g_period_start_date and g_period_end_date)
708 AND aip.org_id = orgs.org_id
709 AND ac.check_id = aip.check_id
710 AND NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS';
711
712 l_rowcount := sql%rowcount; -- bug 9509700
713
714 debug ('Total records inserted in ap_period_close_excps_gt for source_type='||G_SRC_TYP_UNACCT_INV_PMTS||' is:'||l_rowcount);
715
716 l_rowcount := NULL;
717 end if;
718
719 /*Bug#7649020: Fetching data from SLA cursors and inserting into GT tables.
720 * If action is period closure returing after check of one record.
721 * If action is other than period closure (UTR,PCER,SWEEP), inserting
722 * data fetched by cursor into GT table.
723 *
724 *Bug#8240910: SLA cursor modified to fetch data over a date range instead
725 * of period name as the reports can be submitted over any date range and
726 * not specifically over a period. Modified the call to SLA cursor to pass
727 * start date and end date instead of passing period name
728 */
729 IF g_action = G_ACTION_PERIOD_CLOSE THEN
730 OPEN xla_period_close_exp_pkg.period_close_hdr_date_cur(200,g_ledger_id,g_period_start_date,g_period_end_date);
731 xla_headers_untransfered.EXTEND();
732 FETCH xla_period_close_exp_pkg.period_close_hdr_date_cur INTO xla_headers_untransfered(1);
733 CLOSE xla_period_close_exp_pkg.period_close_hdr_date_cur;
734 IF xla_headers_untransfered(1).event_id IS NOT NULL THEN
735 RETURN 'Y';
736 END IF;
737
738 OPEN xla_period_close_exp_pkg.period_close_evt_date_cur(200,g_ledger_id,g_period_start_date,g_period_end_date);
739 xla_events_unacct.EXTEND();
740 FETCH xla_period_close_exp_pkg.period_close_evt_date_cur INTO xla_events_unacct(1);
741 CLOSE xla_period_close_exp_pkg.period_close_evt_date_cur;
742 IF xla_events_unacct(1).event_id IS NOT NULL THEN
743 RETURN 'Y';
744 END IF;
745
746 ELSE
747
748 IF g_action = G_ACTION_PCER THEN
749 -- Insert untransferred headers to GT table only if action is PCER
750 OPEN xla_period_close_exp_pkg.period_close_hdr_date_cur(200,g_ledger_id,g_period_start_date,g_period_end_date);
751 FETCH xla_period_close_exp_pkg.period_close_hdr_date_cur
752 BULK COLLECT INTO xla_headers_untransfered;
753 CLOSE xla_period_close_exp_pkg.period_close_hdr_date_cur;
754
755 IF xla_headers_untransfered.COUNT> 0 THEN
756 FOR i IN xla_headers_untransfered.FIRST..xla_headers_untransfered.LAST
757 LOOP
758
759 -- Bug 8887052: Modified decode on invoices to consider MANUAL events
760 -- untransferred headers as well.
761
762 INSERT INTO ap_period_close_excps_gt
763 (accounting_event_id
764 ,accounting_date
765 ,org_id
766 ,legal_entity_id
767 ,invoice_num
768 ,invoice_id
769 ,invoice_date
770 ,check_number
771 ,check_id
772 ,check_date
773 ,event_type_code
774 ,entity_code
775 ,source_type
776 ,source_table_name
777 ,party_id /*Bug 9721897*/
778 ,vendor_id /*Bug 9721897*/
779 ) values
780 (xla_headers_untransfered(i).event_id
781 ,xla_headers_untransfered(i).event_date
782 ,xla_headers_untransfered(i).security_id_int_1
783 ,xla_headers_untransfered(i).legal_entity_id
784 ,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
785 THEN xla_headers_untransfered(i).transaction_number
786 ELSE NULL END
787 ,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
788 THEN xla_headers_untransfered(i).source_id_int_1
789 ELSE NULL END
790 ,CASE WHEN xla_headers_untransfered(i).entity_code IN ('AP_INVOICES','MANUAL')
791 THEN xla_headers_untransfered(i).transaction_date
792 ELSE NULL END
793 ,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).transaction_number,NULL)
794 ,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).source_id_int_1,NULL)
795 ,decode(xla_headers_untransfered(i).entity_code,'AP_PAYMENTS',xla_headers_untransfered(i).transaction_date,NULL)
796 ,xla_headers_untransfered(i).event_type_code
797 ,xla_headers_untransfered(i).entity_code
798 ,G_SRC_TYP_UNTRANSFERED_HEADERS
799 ,G_SRC_TAB_XLA_AE_HEADERS
800 ,(select party_id from ap_invoices_all
801 where invoice_id = xla_headers_untransfered(i).source_id_int_1
802 and 'AP_INVOICES' = xla_headers_untransfered(i).entity_code
803 union
804 select party_id from ap_checks_all
805 where check_id = xla_headers_untransfered(i).source_id_int_1
806 and 'AP_PAYMENTS' = xla_headers_untransfered(i).entity_code) /*Bug 9721897*/
807 ,(select vendor_id from ap_invoices_all
808 where invoice_id = xla_headers_untransfered(i).source_id_int_1
809 and 'AP_INVOICES' = xla_headers_untransfered(i).entity_code
810 union
811 select vendor_id from ap_checks_all
812 where check_id = xla_headers_untransfered(i).source_id_int_1
813 and 'AP_PAYMENTS' = xla_headers_untransfered(i).entity_code) /*Bug 9721897*/
814 );
815
816 END LOOP;
817 END IF;
818 END IF;
819
820 OPEN xla_period_close_exp_pkg.period_close_evt_date_cur(200,g_ledger_id,g_period_start_date,g_period_end_date);
821 FETCH xla_period_close_exp_pkg.period_close_evt_date_cur
822 BULK COLLECT INTO xla_events_unacct;
823 CLOSE xla_period_close_exp_pkg.period_close_evt_date_cur;
824
825 IF xla_events_unacct.COUNT> 0 THEN
826 FOR i IN xla_events_unacct.FIRST..xla_events_unacct.LAST
827 LOOP
828 INSERT WHEN NOT EXISTS (SELECT accounting_event_id
829 FROM ap_period_close_excps_gt
830 WHERE accounting_event_id = xla_events_unacct(i).event_id)
831 AND xla_events_unacct(i).entity_code='AP_INVOICES' THEN
832 INTO ap_period_close_excps_gt
833 ( invoice_id
834 ,invoice_distribution_id
835 ,invoice_payment_id -- 7318763
836 ,accounting_event_id
837 ,accounting_date
838 ,org_id
839 ,invoice_num
840 ,invoice_currency_code
841 ,party_id
842 ,vendor_id
843 ,doc_sequence_value
844 ,voucher_num
845 ,invoice_date
846 ,invoice_amount
847 ,cancelled_date
848 ,match_status_flag
849 ,legal_entity_id
850 ,po_distribution_id
851 ,amount
852 ,detail_tax_dist_id
853 ,invoice_line_number
854 ,event_type_code
855 ,entity_code
856 ,source_type
857 ,source_table_name
858 )
859 SELECT xla_events_unacct(i).source_id_int_1
860 ,aid.invoice_distribution_id
861 ,aid.awt_invoice_payment_id -- 7318763
862 ,aid.accounting_event_id
863 ,aid.accounting_date
864 ,aid.org_id
865 ,xla_events_unacct(i).transaction_number
866 ,ai.invoice_currency_code
867 ,ai.party_id
868 ,ai.vendor_id
869 ,ai.doc_sequence_value
870 ,ai.voucher_num
871 ,xla_events_unacct(i).transaction_date
872 ,ai.invoice_amount
873 ,ai.cancelled_date
874 ,aid.match_status_flag
875 ,xla_events_unacct(i).legal_entity_id
876 ,aid.po_distribution_id
877 ,aid.amount
878 ,aid.detail_tax_dist_id
879 ,aid.invoice_line_number
880 ,xla_events_unacct(i).event_type_code
881 ,xla_events_unacct(i).entity_code
882 ,G_SRC_TYP_OTHER_EXCPS
883 ,G_SRC_TAB_AP_INV_DISTS_ALL
884 FROM ap_invoices_all ai
885 ,ap_invoice_distributions_all aid
886 ,ap_org_attributes_gt org_gtt
887 WHERE aid.invoice_id = ai.invoice_id(+)
888 AND aid.set_of_books_id = g_ledger_id
889 AND aid.org_id = org_gtt.org_id
890 AND aid.accounting_event_id = xla_events_unacct(i).event_id
891 UNION ALL
892 SELECT xla_events_unacct(i).source_id_int_1
893 ,astd.invoice_distribution_id
894 ,NULL invoice_payment_id
895 ,astd.accounting_event_id
896 ,astd.accounting_date
897 ,astd.org_id
898 ,xla_events_unacct(i).transaction_number
899 ,ai.invoice_currency_code
900 ,ai.party_id
901 ,ai.vendor_id
902 ,ai.doc_sequence_value
903 ,ai.voucher_num
904 ,xla_events_unacct(i).transaction_date
905 ,ai.invoice_amount
906 ,ai.cancelled_date
907 ,astd.match_status_flag
908 ,xla_events_unacct(i).legal_entity_id
909 ,astd.po_distribution_id
910 ,astd.amount
911 ,astd.detail_tax_dist_id
912 ,NULL invoice_line_number
913 ,xla_events_unacct(i).event_type_code
914 ,xla_events_unacct(i).entity_code
915 ,G_SRC_TYP_OTHER_EXCPS
916 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL
917 FROM ap_invoices_all ai
918 ,ap_self_assessed_tax_dist_all astd
919 ,ap_org_attributes_gt org_gtt
920 WHERE astd.invoice_id = ai.invoice_id(+)
921 AND astd.set_of_books_id = g_ledger_id
922 AND astd.org_id = org_gtt.org_id
923 AND astd.accounting_event_id = xla_events_unacct(i).event_id
924 UNION ALL
925 SELECT xla_events_unacct(i).source_id_int_1
926 ,NULL invoice_distribution_id
927 ,NULL invoice_payment_id
928 ,apph.accounting_event_id
929 ,apph.accounting_date
930 ,ai.org_id
931 ,xla_events_unacct(i).transaction_number
932 ,ai.invoice_currency_code
933 ,ai.party_id
934 ,ai.vendor_id
935 ,ai.doc_sequence_value
936 ,ai.voucher_num
937 ,xla_events_unacct(i).transaction_date
938 ,ai.invoice_amount
939 ,ai.cancelled_date
940 ,NULL match_status_flag
941 ,NULL po_distribution_id
942 ,NULL amount
943 ,NULL detail_tax_dist_id
944 ,NULL invoice_line_number
945 ,xla_events_unacct(i).legal_entity_id
946 ,xla_events_unacct(i).event_type_code
947 ,xla_events_unacct(i).entity_code
948 ,G_SRC_TYP_OTHER_EXCPS
949 ,G_SRC_TAB_AP_PREPAY_HIST
950 FROM ap_invoices_all ai
951 ,ap_prepay_history_all apph
952 ,ap_org_attributes_gt org_gtt
953 WHERE apph.invoice_id = ai.invoice_id(+)
954 AND apph.accounting_event_id IS NOT NULL
955 AND ai.set_of_books_id = g_ledger_id
956 AND apph.org_id = org_gtt.org_id
957 AND apph.accounting_event_id = xla_events_unacct(i).event_id;
958 END LOOP;
959
960 -- bug 12629621
961 --changed ai.org_id = org_gtt.org_id to apph.org_id = org_gtt.org_id in above
962
963 FOR i IN xla_events_unacct.FIRST..xla_events_unacct.LAST
964 LOOP
965 INSERT WHEN NOT EXISTS (SELECT accounting_event_id
966 FROM ap_period_close_excps_gt
967 WHERE accounting_event_id = xla_events_unacct(i).event_id)
968 AND xla_events_unacct(i).entity_code='AP_PAYMENTS' then
969 INTO AP_PERIOD_CLOSE_EXCPS_GT
970 (payment_history_id
971 ,accounting_event_id
972 ,accounting_date
973 ,check_id
974 ,transaction_type
975 ,org_id
976 ,recon_accounting_flag
977 ,check_number
978 ,exchange_rate
979 ,check_date
980 ,legal_entity_id
981 ,vendor_name
982 ,bank_account_name
983 ,check_amount
984 ,currency_code
985 ,party_id
986 ,vendor_id
987 ,event_type_code
988 ,entity_code
989 ,source_type
990 ,source_table_name
991 )
992 SELECT aph.payment_history_id,
993 aph.accounting_event_id,
994 aph.accounting_date,
995 xla_events_unacct(i).source_id_int_1,
996 aph.transaction_type,
997 aph.org_id,
998 orgs.recon_accounting_flag,
999 xla_events_unacct(i).transaction_number,
1000 ac.exchange_rate,
1001 xla_events_unacct(i).transaction_date,
1002 xla_events_unacct(i).legal_entity_id,
1003 ac.vendor_name,
1004 ac.bank_account_name,
1005 ac.amount,
1006 ac.currency_code,
1007 ac.party_id,
1008 ac.vendor_id
1009 ,xla_events_unacct(i).event_type_code
1010 ,xla_events_unacct(i).entity_code
1011 ,G_SRC_TYP_OTHER_EXCPS
1012 ,G_SRC_TAB_AP_PMT_HISTORY
1013 FROM ap_payment_history_all aph,
1014 ap_checks_all ac,
1015 ap_org_attributes_gt orgs
1016 WHERE aph.check_id = ac.check_id(+)
1017 AND aph.org_id = orgs.org_id
1018 AND ( NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'ALWAYS' or
1019 (NVL(orgs.when_to_account_pmt, 'ALWAYS') = 'CLEARING ONLY' and
1020 aph.transaction_type in ('PAYMENT CLEARING', 'PAYMENT UNCLEARING')))
1021 AND aph.accounting_event_id = xla_events_unacct(i).event_id;
1022
1023 END LOOP;
1024
1025
1026 FOR i IN xla_events_unacct.FIRST..xla_events_unacct.LAST
1027 LOOP
1028
1029 -- Bug 8887052: Modified decode on invoices to consider
1030 -- unaccounted MANUAL events as well
1031
1032 INSERT WHEN NOT EXISTS (SELECT accounting_event_id
1033 FROM ap_period_close_excps_gt
1034 WHERE accounting_event_id = xla_events_unacct(i).event_id)
1035 THEN
1036 INTO ap_period_close_excps_gt
1037 (accounting_event_id
1038 ,accounting_date
1039 ,org_id
1040 ,legal_entity_id
1041 ,invoice_num
1042 ,invoice_id
1043 ,invoice_date
1044 ,check_number
1045 ,check_id
1046 ,check_date
1047 ,event_type_code
1048 ,entity_code
1049 ,source_type
1050 ,source_table_name
1051 ,party_id /*Bug 9721897*/
1052 ,vendor_id /*Bug 9721897*/
1053 )
1054 SELECT xla_events_unacct(i).event_id
1055 ,xla_events_unacct(i).event_date
1056 ,xla_events_unacct(i).security_id_int_1
1057 ,xla_events_unacct(i).legal_entity_id
1058 ,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
1059 THEN xla_events_unacct(i).transaction_number
1060 ELSE NULL END
1061 ,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
1062 THEN xla_events_unacct(i).source_id_int_1
1063 ELSE NULL END
1064 ,CASE WHEN xla_events_unacct(i).entity_code IN ('AP_INVOICES','MANUAL')
1065 THEN xla_events_unacct(i).transaction_date
1066 ELSE NULL END
1067 ,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).transaction_number,NULL)
1068 ,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).source_id_int_1,NULL)
1069 ,decode(xla_events_unacct(i).entity_code,'AP_PAYMENTS',xla_events_unacct(i).transaction_date,NULL)
1070 ,xla_events_unacct(i).event_type_code
1071 ,xla_events_unacct(i).entity_code
1072 ,G_SRC_TYP_OTHER_EXCPS
1073 ,'ORPHAN_EVENTS'
1074 ,(select party_id from ap_invoices_all
1075 where invoice_id = xla_events_unacct(i).source_id_int_1
1076 and 'AP_INVOICES' = xla_events_unacct(i).entity_code
1077 union
1078 select party_id from ap_checks_all
1079 where check_id = xla_events_unacct(i).source_id_int_1
1080 and 'AP_PAYMENTS' = xla_events_unacct(i).entity_code) party_id /*Bug 9721897*/
1081 ,(select vendor_id from ap_invoices_all
1082 where invoice_id = xla_events_unacct(i).source_id_int_1
1083 and 'AP_INVOICES' = xla_events_unacct(i).entity_code
1084 union
1085 select vendor_id from ap_checks_all
1086 where check_id = xla_events_unacct(i).source_id_int_1
1087 and 'AP_PAYMENTS' = xla_events_unacct(i).entity_code) vendor_id /*Bug 9721897*/
1088 FROM DUAL
1089 where xla_events_unacct(i).security_id_int_1 in (Select org_id
1090 from ap_org_attributes_gt orgs) ; /*Bug 14596406 */
1091
1092 END LOOP;
1093
1094 end if;
1095 -- Bug# 8240910: Since other exceptions are not swept anymore, removed the
1096 -- code which populates records from ap_invocie_payments_all to GT table.
1097
1098 --END Bug#7649020: Fetching data from SLA cursors
1099 END IF;
1100
1101 return null;
1102
1103 end get_unposted_transactions;
1104
1105 /*------------------------------------------------------------------------------------------------------------------------*/
1106 function get_reporting_level_name
1107 return varchar2
1108 is
1109
1110 lv_name varchar2(100);
1111
1112 begin
1113 SELECT meaning
1114 into lv_name
1115 FROM FND_LOOKUPS
1116 WHERE LOOKUP_TYPE = 'FND_MO_REPORTING_LEVEL'
1117 and lookup_code = g_reporting_level;
1118
1119 debug ('get_reporting_level_name: lv_name='||lv_name);
1120 return lv_name;
1121 exception
1122 when others then
1123
1124 debug ('EXCEPTION: get_reporting_level_name: '||sqlerrm);
1125 return null;
1126 end;
1127
1128 /*------------------------------------------------------------------------------------------------------------------------*/
1129 function get_reporting_context
1130 return varchar2
1131 is
1132 cursor c_org_name
1133 is
1134 select org_name
1135 from ap_org_attributes_gt
1136 where org_id = g_org_id;
1137
1138 lv_name varchar2(100);
1139
1140 begin
1141
1142 if (G_ACTION = G_ACTION_PCER or G_ACTION = G_ACTION_SWEEP
1143 or (G_ACTION = G_ACTION_UTR and G_REPORTING_LEVEL = 1000)) then
1144 lv_name := g_ledger_name;
1145
1146 elsif (G_ACTION = G_ACTION_UTR and G_REPORTING_LEVEL = 3000) then
1147 open c_org_name;
1148 fetch c_org_name into lv_name;
1149 close c_org_name;
1150 end if;
1151
1152 debug ('get_reporting_context: lv_name='||lv_name);
1153 return lv_name;
1154 exception
1155 when others then
1156
1157 debug ('EXCEPTION: get_reporting_context: '||sqlerrm);
1158 return null;
1159 end;
1160
1161 /*------------------------------------------------------------------------------------------------------------------------*/
1162 procedure validate_sweep
1163 (p_validation_flag out nocopy varchar2
1164 ,p_validation_message out nocopy varchar2
1165 )
1166 is
1167
1168 ln_cnt number;
1169
1170 cursor c_cnt_org_access
1171 is
1172 select count(1)
1173 from ap_org_attributes_gt all_orgs
1174 where org_id not in (select org_id from ap_system_parameters);
1175
1176
1177 begin
1178 --
1179 -- Validation: SWEEP can be done only if all operating units defifned under the given ledger are accessible.
1180 --
1181 -- we have a all valid orgs in ap_org_attributes_gt. If a particular org_id is present in ap_org_attributes_gt
1182 -- but not in ap_system_paramter it means we don't have access to that org
1183 --
1184
1185 ln_cnt := 0;
1186 open c_cnt_org_access;
1187 fetch c_cnt_org_access into ln_cnt;
1188 close c_cnt_org_access;
1189
1190 if ln_cnt > 0 then -- there are some orgs which are no accessible
1191 --
1192 -- You must have access to all the operating units defined for a ledger
1193 --
1194 p_validation_flag := 'EE';
1195 p_validation_message := 'AP_SWEEP_ACCESS_ERROR';
1196
1197 debug ('Number of orgs which are not accessible='||ln_cnt);
1198
1199 return;
1200 end if;
1201
1202 if p_validation_flag <> 'EE' then
1203 p_validation_flag := 'SS';
1204 p_validation_message := '';
1205 end if;
1206
1207 end validate_sweep;
1208 /*------------------------------------------------------------------------------------------------------------------------*/
1209 procedure validate_period_close
1210 (p_validation_flag out nocopy varchar2
1211 ,p_validation_message out nocopy varchar2
1212 )
1213 is
1214 -- check if any unconfirmed payment batches
1215 cursor c_uncnf_pmt_batch_exists is
1216 SELECT 'Y'
1217 FROM ap_inv_selection_criteria_all AISC,
1218 iby_pay_service_requests IPSR ,
1219 ap_selected_invoices_all ASI
1220 WHERE IPSR.call_app_pay_service_req_code (+) = AISC.checkrun_name
1221 AND trunc(aisc.check_date) between g_period_start_date and g_period_end_date
1222 AND DECODE(IPSR.payment_service_request_id, NULL,
1223 AISC.status,
1224 AP_PAYMENT_UTIL_PKG.get_psr_status(IPSR.payment_service_request_id,
1225 IPSR.payment_service_request_status) )
1226 NOT IN ('CONFIRMED','CANCELED','QUICKCHECK', 'CANCELLED NO PAYMENTS', 'TERMINATED')
1227 AND aisc.checkrun_id = asi.checkrun_id
1228 AND asi.org_id in (select org_id org_id from ap_org_attributes_gt org_gtt)
1229 AND rownum = 1;
1230
1231 -- check if any unmatured future payments exists
1232 cursor c_unmat_fut_pmts_exists
1233 is
1234 select 'Y'
1235 from ap_checks_all c
1236 where c.future_pay_due_date is not null
1237 and c.status_lookup_code = 'ISSUED'
1238 and c.future_pay_due_date between g_period_start_date
1239 and g_period_end_date
1240 and c.org_id in (select org_id org_id from ap_org_attributes_gt org_gtt)
1241 and rownum = 1;
1242
1243 lv_exists varchar2 (1);
1244
1245 procedure set_expected_error (p_msg varchar2)
1246 is
1247 begin
1248 p_validation_flag := 'EE';
1249 p_validation_message := 'AP_SET_CANNOT_CLOSED_PERIOD';
1250 print(p_msg);
1251 end set_expected_error;
1252
1253
1254 begin
1255
1256 -- check if unconfirmed payment batch exists
1257 lv_exists := 'N';
1258 open c_uncnf_pmt_batch_exists;
1259 fetch c_uncnf_pmt_batch_exists into lv_exists;
1260 close c_uncnf_pmt_batch_exists;
1261
1262 debug ('cursor c_uncnf_pmt_batch_exists: lv_exists='||lv_exists);
1263
1264 if lv_exists = 'Y' then
1265 set_expected_error ('AP_UNCNF_PMT_BATCH_EXISTS ' || '- Unconfirmed Payment Batches');
1266 return;
1267 end if;
1268
1269 -- check if unmatured future payment exists
1270 lv_exists := 'N';
1271 open c_unmat_fut_pmts_exists;
1272 fetch c_unmat_fut_pmts_exists into lv_exists;
1273 close c_unmat_fut_pmts_exists;
1274
1275 debug ('cursor c_unmat_fut_pmts_exists: lv_exists='||lv_exists);
1276
1277 if lv_exists = 'Y' then
1278 set_expected_error ('AP_UNMAT_FUT_PMTS_EXISTS ' || '- Unmatured Future Payments');
1279 return;
1280 end if;
1281
1282 -- check transfer to GL
1283 -- Bug#7649020: Commented call to xla package to make codepath
1284 -- for period close and PCER as close as possible
1285 /*xla_events_pub_pkg.period_close(P_API_VERSION => 1
1286 , X_RETURN_STATUS => p_validation_flag
1287 , P_APPLICATION_ID => G_AP_APPLICATION_ID
1288 , P_LEDGER_ID => g_ledger_id
1289 , P_PERIOD_NAME => g_period_name);
1290
1291 debug ('xla_events_pub_pkg.period_close: p_validation_flag='||p_validation_flag);
1292
1293 if (p_validation_flag <> 'S') then
1294 set_expected_error ('AP_UNTRNF_EVENTS_IN_XLA ' ||' - Untransferred XLA events');
1295 return;
1296 end if;*/
1297
1298 -- check if unposted invoices or unposted payment exists
1299 lv_exists := 'N';
1300 lv_exists := get_unposted_transactions ;
1301
1302 debug ('get_unposted_transactions: return value: lv_exists='||lv_exists);
1303
1304 if lv_exists = 'Y' then
1305 set_expected_error ('AP_UNACCT_TRXS_EXISTS '|| '- Unaccounted Invoices and/or payments');
1306 return;
1307 end if;
1308
1309 if p_validation_flag <> 'EE' then
1310 p_validation_flag := 'SS';
1311 p_validation_message := '';
1312 end if;
1313
1314 end validate_period_close;
1315
1316 /*------------------------------------------------------------------------------------------------------------------------*/
1317 procedure validate_parameters
1318 ( p_validation_flag out nocopy varchar2
1319 ,p_validation_message out nocopy varchar2
1320 )
1321 is
1322
1323 cursor c_get_ledger_from_org
1324 is
1325 select set_of_books_id ledger_id
1326 from ap_system_parameters_all
1327 where org_id = g_org_id;
1328
1329 lv_closing_status gl_period_statuses.closing_status%type;
1330 ld_period_start_date gl_period_statuses.start_date%type;
1331 ld_period_end_date gl_period_statuses.end_date%type;
1332 ld_sweep_to_end_date gl_period_statuses.end_date%type;
1333
1334 l_min_date gl_period_statuses.start_date%type;
1335 l_max_date gl_period_statuses.end_date%type;
1336
1337 begin
1338
1339 if g_ledger_id is null
1340 and g_org_id is null then
1341 p_validation_flag := 'EE';
1342 p_validation_message := 'AP_LEDGER_OR_OU_REQ';
1343 return;
1344 -- elsif g_ledger_id is null then
1345 /*
1346 * veramach bug 7412634. g_ledger_id is passed as -9999 when reporting context is set to a OU. But,
1347 * earlier the condition was being checked as g_ledger_id is null. So, when running for an OU,
1348 g_ledger_id was never getting set. So, c_get_all_orgs cursor was failing in populate_orgs method.
1349 */
1350 elsif NVL(g_ledger_id,-9999) = -9999 THEN
1351
1352 --
1353 -- we will derive ledger_id based on the the org_id
1354 --
1355 open c_get_ledger_from_org;
1356 fetch c_get_ledger_from_org into g_ledger_id;
1357 close c_get_ledger_from_org;
1358
1359 debug ('cursor c_get_ledger_from_org: g_ledger_id='||g_ledger_id);
1360
1361 end if;
1362
1363 --
1364 -- Get ledger attributes
1365 --
1366
1367 open c_ledger_attribs;
1368 fetch c_ledger_attribs into g_ledger_name, g_cash_basis_flag;
1369 close c_ledger_attribs;
1370
1371 debug ('cursor c_ledger_attribs: g_ledger_name='||g_ledger_name||'; g_cash_basis_flag='||g_cash_basis_flag);
1372
1373 if g_period_name is null
1374 and (g_period_start_date is null or g_period_end_date is null)
1375 then
1376 --7649020: If action is UTR, dates should default if no dates are given.
1377 if g_action = G_ACTION_UTR then
1378
1379 SELECT min(start_date), max(end_date)
1380 INTO l_min_date,l_max_date
1381 FROM gl_period_statuses
1382 WHERE application_id = G_AP_APPLICATION_ID
1383 AND set_of_books_id = g_ledger_id
1384 AND closing_status in ('C','O','F');
1385
1386 if g_period_start_date is null then
1387 g_period_start_date := l_min_date;
1388 end if;
1389
1390 if g_period_end_date is null then
1391 g_period_end_date := l_max_date;
1392 end if;
1393 else
1394 p_validation_flag := 'EE';
1395 p_validation_message := 'AP_PERIOD_OR_DATE_REQ';
1396 return;
1397 end if;
1398 end if;
1399
1400 if g_period_name is not null then
1401 open c_get_period_dates;
1402 fetch c_get_period_dates into ld_period_start_date
1403 , ld_period_end_date
1404 , lv_closing_status;
1405
1406 close c_get_period_dates;
1407
1408 debug ('cursor c_get_period_dates: ld_period_start_date='||ld_period_start_date
1409 ||'; ld_period_end_date='||ld_period_end_date
1410 ||'; lv_closing_status='||lv_closing_status
1411 );
1412
1413 g_period_start_date := ld_period_start_date;
1414 g_period_end_date := ld_period_end_date;
1415
1416 else
1417 /*Bug 9813947*/
1418 lv_closing_status := get_period_status(g_period_start_date ,
1419 g_period_end_date);
1420 debug ('function get_period_status: g_period_start_date='||g_period_start_date
1421 ||'; g_period_end_date='||g_period_end_date
1422 ||'; lv_closing_status='||lv_closing_status
1423 );
1424 end if;
1425
1426 if lv_closing_status <> 'O' then
1427 p_validation_flag := 'EE';
1428 p_validation_message := 'AP_ALL_NOT_OPEN_PERIOD';
1429 return;
1430 end if;
1431
1432
1433 if (g_action in (G_ACTION_SWEEP, G_ACTION_PERIOD_CLOSE)
1434 and (g_ledger_id is null or g_period_name is null )
1435 ) then
1436
1437 -- We cannot perform PERIOD_CLOSE/SWEEP without a valid ledger and period name
1438 p_validation_flag := 'EE';
1439 p_validation_message := 'AP_LEDGER_PERIOD_REQ';
1440 return;
1441 end if;
1442
1443 if (g_action = G_ACTION_SWEEP) then
1444
1445 -- Validation: To SWEEP, paramter sweep_to_period must be given
1446
1447 if g_sweep_to_period is null then
1448 p_validation_flag := 'EE';
1449 p_validation_message := 'AP_SWEEP_TO_PERIOD_REQ';
1450 return;
1451 end if;
1452
1453
1454 lv_closing_status :=null;
1455
1456 open c_get_period_dates (cp_period_name => g_sweep_to_period
1457 ,cp_include_adj_period => 'N'
1458 );
1459 fetch c_get_period_dates into g_sweep_to_date
1460 ,ld_sweep_to_end_date
1461 ,lv_closing_status;
1462 close c_get_period_dates;
1463
1464 debug ('cursor c_get_period_dates (cp_period_name=>'||g_sweep_to_period||',cp_include_adj_period=N');
1465 debug ('cursor c_get_period_dates: g_sweep_to_date='||g_sweep_to_date
1466 ||'; ld_sweep_to_end_date='||ld_sweep_to_end_date
1467 ||'; lv_closing_status='||lv_closing_status
1468 );
1469
1470 --
1471 -- Check that sweep to date is valid
1472 -- Sweep to date is invalid if
1473 -- 1. It is NULL
1474 -- 2. It is prior to the start date of the current period (the period being closed/swept)
1475 -- 3. If it is in closed period
1476 --
1477
1478 if g_sweep_to_date is null
1479 or g_sweep_to_date <= g_period_end_date
1480 or lv_closing_status not in ('O','F')
1481 then
1482
1483 p_validation_flag := 'EE';
1484 p_validation_message := 'AP_INVALID_SWEEP_PERIOD';
1485 end if;
1486
1487 end if;
1488
1489 if p_validation_flag <> 'EE' then
1490 p_validation_flag := 'SS';
1491 p_validation_message := '';
1492 end if;
1493
1494 end validate_parameters;
1495
1496
1497 /*------------------------------------------------------------------------------------------------------------------------*/
1498 --Bug#7649020: removed the call to validate_action
1499 --and this code handled in process_period
1500 /*procedure validate_action
1501 (p_action in varchar2
1502 ,p_validation_flag out nocopy varchar2
1503 ,p_validation_message out nocopy varchar2
1504 )
1505 is
1506 l_msg_count NUMBER;
1507 begin
1508
1509 if p_action = G_ACTION_PERIOD_CLOSE then
1510
1511 validate_period_close
1512 (p_validation_flag => p_validation_flag
1513 ,p_validation_message => p_validation_message
1514 );
1515
1516 end if;
1517
1518 if p_action = G_ACTION_SWEEP then
1519
1520 PSA_AP_BC_PVT.delete_events(
1521 p_init_msg_list => 'F',
1522 p_ledger_id => g_ledger_id,
1523 p_start_date => g_period_start_date,
1524 p_end_date => g_period_end_date,
1525 p_calling_sequence => 'ap_period_close_pkg.validate_action',
1526 x_return_status => p_validation_flag,
1527 x_msg_count =>l_msg_count,
1528 x_msg_data => p_validation_message
1529 );
1530
1531 if p_validation_flag <> 'S' then
1532 p_validation_flag := 'EE';
1533 print ('l_msg_count = ' || l_msg_count || ' error msg - ' || p_validation_message);
1534 else
1535 p_validation_flag := 'SS';
1536 p_validation_message := '';
1537 end if;
1538
1539 end if;
1540
1541 exception
1542 when others then
1543 p_validation_flag := 'UE';
1544 p_validation_message := 'ERROR: validate_action :'|| sqlerrm;
1545 debug ('EXCEPTION: validate_action: '||sqlerrm);
1546 end validate_action;*/
1547
1548 /*============================================================================
1549 | FUNCTION - GET_EVENT_SECURITY_CONTEXT(PRIVATE)
1550 |
1551 | DESCRIPTION
1552 | This function is used to get the event security context.
1553 |
1554 | PRAMETERS:
1555 | p_org_id: Organization ID
1556 | p_calling_sequence: Debug information
1557 |
1558 | RETURN: XLA_EVENTS_PUB_PKG.T_SECURITY
1559 |
1560 | KNOWN ISSUES:
1561 |
1562 | NOTES:
1563 |
1564 | MODIFICATION HISTORY
1565 | Date Author Description of Change
1566 | 14-MAR-08 PRANPAUL New
1567 *===========================================================================*/
1568 FUNCTION get_event_security_context(
1569 p_org_id IN NUMBER,
1570 p_calling_sequence IN VARCHAR2)
1571 RETURN XLA_EVENTS_PUB_PKG.T_SECURITY
1572 IS
1573
1574 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
1575
1576 BEGIN
1577
1578 l_event_security_context.security_id_int_1 := p_org_id;
1579
1580 RETURN l_event_security_context;
1581
1582 END get_event_security_context;
1583
1584
1585 /*============================================================================
1586 | FUNCTION - GET_EVENT_SOURCE_INFO(PRIVATE)
1587 |
1588 | DESCRIPTION
1589 | This function is used to get invoice/payment event source information
1590 |
1591 | PRAMETERS:
1592 | p_legal_entity_id: Legal entity ID
1593 | p_ledger_id: Ledger ID
1594 | p_trans_id: Invoice ID / Check ID
1595 | p_calling_sequence: Debug information
1596 |
1597 | RETURN: XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
1598 |
1599 | KNOWN ISSUES:
1600 |
1601 | NOTES:
1602 |
1603 | MODIFICATION HISTORY
1604 | Date Author Description of Change
1605 | 14-MAR-08 PRANPAUL New
1606 *===========================================================================*/
1607 FUNCTION get_event_source_info(
1608 p_legal_entity_id IN NUMBER,
1609 p_ledger_id IN NUMBER,
1610 p_trans_id IN NUMBER,
1611 p_event_id IN NUMBER,
1612 p_inv_payment_id IN NUMBER, -- 7318763
1613 p_trans_num IN VARCHAR2,
1614 p_context IN VARCHAR2,
1615 p_calling_sequence IN VARCHAR2)
1616 RETURN XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO
1617 IS
1618 /* Modified the procedure for bug 7137359, related to AWT event creation */
1619 l_invoice_num VARCHAR2(50);
1620 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
1621 l_count NUMBER(15);
1622 l_check_id AP_CHECKS_ALL.Check_Id%TYPE;
1623 l_check_number AP_CHECKS_ALL.Check_Number%TYPE;
1624
1625 BEGIN
1626
1627 l_event_source_info.application_id := G_AP_APPLICATION_ID;
1628 l_event_source_info.legal_entity_id := p_legal_entity_id;
1629 l_event_source_info.ledger_id := p_ledger_id;
1630
1631 if p_context = 'INV' then
1632 /* select count(*) --commented this peice of code 7318763
1633 into l_count
1634 from ap_invoice_distributions_all
1635 where accounting_event_id = p_event_id
1636 and invoice_id = p_trans_id
1637 and awt_invoice_payment_id is not null; */
1638
1639 if (nvl(p_inv_payment_id ,-1) > 0) then -- 7318763
1640
1641 BEGIN
1642
1643 select DISTINCT ac.check_id, --bug9649978
1644 ac.check_number
1645 into l_check_id, l_check_number
1646 from ap_invoice_payments_all aip,
1647 ap_checks_all ac
1648 where aip.check_id=ac.check_id
1649 and aip.accounting_event_id = p_event_id
1650 and aip.invoice_id= p_trans_id;
1651
1652 l_event_source_info.entity_type_code := 'AP_PAYMENTS';
1653 l_event_source_info.transaction_number := l_check_number;
1654 l_event_source_info.source_id_int_1 := l_check_id;
1655
1656 EXCEPTION
1657 WHEN OTHERS THEN
1658 NULL;
1659 END;
1660 else
1661 l_event_source_info.entity_type_code := 'AP_INVOICES';
1662 l_event_source_info.transaction_number := p_trans_num;
1663 l_event_source_info.source_id_int_1 := p_trans_id;
1664 end if;
1665
1666 else
1667 l_event_source_info.entity_type_code := 'AP_PAYMENTS';
1668 l_event_source_info.transaction_number := p_trans_num;
1669 l_event_source_info.source_id_int_1 := p_trans_id;
1670
1671 end if;
1672
1673
1674 RETURN l_event_source_info;
1675
1676 END;
1677
1678
1679
1680
1681 /*============================================================================
1682 | FUNCTION - UPDATE_PO_CLOSE_DATE
1683 |
1684 | DESCRIPTION
1685 | This function is used to sweep closed date of PO Shipment and Headers
1686 | to an open date in next accounting period for unaccounted invoice
1687 | distributions matched to these shipments.
1688 |
1689 |
1690 | PRAMETERS
1691 |
1692 |
1693 | KNOWN ISSUES:
1694 |
1695 | NOTES:
1696 |
1697 | MODIFICATION HISTORY
1698 | Date Author Description of Change
1699 | 14-MAR-08 PRANPAUL New
1700 *===========================================================================*/
1701 FUNCTION update_po_close_date RETURN BOOLEAN IS
1702
1703 BEGIN
1704
1705 UPDATE po_headers_all POH
1706 SET POH.closed_date = g_sweep_to_date
1707 WHERE po_header_id in (SELECT PLL.PO_HEADER_ID
1708 FROM PO_LINE_LOCATIONS_ALL PLL,
1709 PO_DISTRIBUTIONS_ALL PD,
1710 AP_PERIOD_CLOSE_EXCPS_GT GT
1711 WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1712 AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1713 AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1714 AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1715 G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
1716 AND ( PLL.CLOSED_DATE IS NOT NULL
1717 AND PLL.CLOSED_DATE < g_sweep_to_date )
1718 GROUP BY PLL.PO_HEADER_ID, GT.PO_DISTRIBUTION_ID
1719 HAVING SUM(GT.AMOUNT) > 0)
1720 AND ( POH.CLOSED_DATE IS NOT NULL
1721 AND POH.CLOSED_DATE < g_sweep_to_date );
1722
1723 debug ('update_po_close_date: total records updated in po_headers_all:'||sql%rowcount);
1724
1725
1726 UPDATE po_line_locations_all
1727 SET closed_date = g_sweep_to_date
1728 WHERE line_location_id in (SELECT PLL.LINE_LOCATION_ID
1729 FROM PO_LINE_LOCATIONS_ALL PLL,
1730 PO_DISTRIBUTIONS_ALL PD,
1731 AP_PERIOD_CLOSE_EXCPS_GT GT
1732 WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
1733 AND PD.PO_DISTRIBUTION_ID = GT.PO_DISTRIBUTION_ID
1734 AND GT.SOURCE_TYPE = G_SRC_TYP_UNACCT_DISTS
1735 AND GT.SOURCE_TABLE_NAME in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1736 G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
1737 AND ( PLL.CLOSED_DATE IS NOT NULL
1738 AND PLL.CLOSED_DATE < g_sweep_to_date )
1739 GROUP BY PLL.LINE_LOCATION_ID, GT.PO_DISTRIBUTION_ID
1740 HAVING SUM(GT.AMOUNT) > 0);
1741
1742 debug ('update_po_close_date: total records updated in po_line_locations_all:'||sql%rowcount);
1743
1744 return TRUE;
1745
1746 exception
1747 WHEN OTHERS THEN
1748 debug ('EXCEPTION: update_po_close_date: '||sqlerrm);
1749 return FALSE;
1750
1751 END;
1752
1753
1754 /*============================================================================
1755 | FUNCTION - UPDATE_EBTAX_DISTS
1756 |
1757 | DESCRIPTION
1758 | This function is used to sweep all eBtax distributions to
1759 | to an open date in next accounting period for unaccounted tax
1760 | distributions generated by eBtax.
1761 |
1762 |
1763 | PRAMETERS
1764 |
1765 |
1766 | KNOWN ISSUES:
1767 |
1768 | NOTES:
1769 |
1770 | MODIFICATION HISTORY
1771 | Date Author Description of Change
1772 | 14-MAR-08 PRANPAUL New
1773 *===========================================================================*/
1774 FUNCTION update_ebtax_dists RETURN BOOLEAN IS
1775
1776 l_return_status varchar2(20);
1777 l_msg_count number;
1778 l_msg_data varchar2(2000);
1779 BEGIN
1780
1781 INSERT into ZX_TAX_DIST_ID_GT
1782 (SELECT detail_tax_dist_id
1783 FROM ap_period_close_excps_gt
1784 WHERE detail_tax_dist_id is not null
1785 AND source_type = G_SRC_TYP_UNACCT_DISTS
1786 AND source_table_name in ( G_SRC_TAB_AP_INV_DISTS_ALL,
1787 G_SRC_TAB_AP_SELF_TAX_DIST_ALL));
1788
1789 debug ('update_ebtax_dists: total records inserted in ZX_TAX_DIST_ID_GT: '||sql%rowcount);
1790
1791 if sql%rowcount > 0 then
1792
1793 ZX_API_PUB.Update_Tax_dist_gl_date (
1794 1.0,
1795 FND_API.G_TRUE,
1796 FND_API.G_FALSE,
1797 FND_API.G_VALID_LEVEL_FULL,
1798 l_return_status,
1799 l_msg_count,
1800 l_msg_data,
1801 g_sweep_to_date );
1802
1803 debug ('update_ebtax_dists: l_return_status='||l_return_status||';l_msg_data='||l_msg_data||';l_msg_count='||l_msg_count );
1804
1805 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1806 print (l_msg_data);
1807 return FALSE;
1808 end if;
1809 end if;
1810 return TRUE;
1811
1812 exception
1813 WHEN OTHERS THEN
1814 debug ('EXCEPTION: update_ebtax_dists: '||sqlerrm);
1815 return FALSE;
1816 END;
1817
1818
1819 /*============================================================================
1820 | PROCEDURE - UPDATE_XLA_EVENTS
1821 |
1822 | DESCRIPTION
1823 | This procedure is used to sweep accounting events from one accounting period
1824 | to another.
1825 |
1826 |
1827 | PRAMETERS
1828 |
1829 | p_sweep_to_date: The new event date
1830 | p_calling_sequence: Debug information
1831 |
1832 | KNOWN ISSUES:
1833 |
1834 | NOTES:
1835 |
1836 | MODIFICATION HISTORY
1837 | Date Author Description of Change
1838 | 14-MAR-08 PRANPAUL New
1839 *===========================================================================*/
1840
1841 PROCEDURE update_xla_events (
1842 p_calling_sequence IN VARCHAR2,
1843 p_success OUT NOCOPY BOOLEAN)
1844 IS
1845
1846 TYPE t_event_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
1847 TYPE t_trans_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
1848 TYPE t_inv_payment_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER; -- 7318763
1849 TYPE t_trans_nums IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
1850 TYPE t_source IS TABLE OF VARCHAR2(30) INDEX BY PLS_INTEGER;
1851 TYPE t_org_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
1852 TYPE t_legal_entity_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
1853 TYPE t_ledger_ids IS TABLE OF NUMBER(15) INDEX BY PLS_INTEGER;
1854
1855
1856 l_event_ids t_event_ids;
1857 l_inv_payment_ids t_inv_payment_ids; -- 7318763
1858 l_trans_ids t_trans_ids;
1859 l_trans_nums t_trans_nums;
1860 l_org_ids t_org_ids;
1861 l_legal_entity_ids t_legal_entity_ids;
1862 --l_ledger_ids t_ledger_ids;
1863 l_sources t_source;
1864 l_event_security_context XLA_EVENTS_PUB_PKG.T_SECURITY;
1865 l_event_source_info XLA_EVENTS_PUB_PKG.T_EVENT_SOURCE_INFO;
1866 l_curr_calling_sequence VARCHAR2(200);
1867
1868 -- Bug 7137359
1869 l_xla_event XLA_EVENTS.EVENT_ID%TYPE;
1870 l_xla_event_status XLA_EVENTS.EVENT_STATUS_CODE%TYPE;
1871 l_call_xla_api VARCHAR2(1);
1872
1873 --Bug#8240910 Reverted back changes done on cursor to handle other exceptions
1874 -- reported by SLA as they are not swept now.
1875 -- Bug9553092 added the exists clause below to exclude the records in the
1876 -- Period Close Exceptions GT which have the source type as INVOICE DISTS
1877 -- but are stamped with payment events. The only two cases where this could
1878 -- happen in Payables is the Payment Time AWT records or Interest Invoice
1879 -- Records. It should be noted that excluding those records does not harm
1880 -- in any way because the events would be swept while updating the eventsa
1881 -- corresponding to the Payment History or Invoice Payment Records
1882 --
1883 CURSOR c_events IS
1884 SELECT gt.accounting_event_id accounting_event_id,
1885 decode (gt.source_table_name
1886 ,G_SRC_TAB_AP_INV_DISTS_ALL, gt.invoice_id
1887 ,G_SRC_TAB_AP_PREPAY_HIST, gt.invoice_id
1888 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, gt.invoice_id
1889 ,G_SRC_TAB_AP_PMT_HISTORY, gt.check_id
1890 ) trans_id,
1891 gt.org_id org_id,
1892 gt.legal_entity_id legal_entity_id,
1893 decode (gt.source_table_name
1894 ,G_SRC_TAB_AP_INV_DISTS_ALL, gt.invoice_num
1895 ,G_SRC_TAB_AP_PREPAY_HIST, gt.invoice_num
1896 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, gt.invoice_num
1897 ,G_SRC_TAB_AP_PMT_HISTORY, gt.check_number
1898 )trans_num,
1899 decode(gt.source_table_name
1900 ,G_SRC_TAB_AP_INV_DISTS_ALL, 'INV'
1901 ,G_SRC_TAB_AP_PREPAY_HIST, 'INV'
1902 ,G_SRC_TAB_AP_SELF_TAX_DIST_ALL, 'INV'
1903 ,G_SRC_TAB_AP_PMT_HISTORY,'PMT'
1904 ) source
1905 ,invoice_payment_id -- 7318763
1906 FROM ap_period_close_excps_gt gt
1907 WHERE gt.source_type in (G_SRC_TYP_UNACCT_DISTS, G_SRC_TYP_UNACCT_PMT_HISTORY,
1908 G_SRC_TYP_UNACCT_PREPAY_HIST)
1909 AND gt.source_table_name in (G_SRC_TAB_AP_INV_DISTS_ALL, G_SRC_TAB_AP_PMT_HISTORY,
1910 G_SRC_TAB_AP_SELF_TAX_DIST_ALL, G_SRC_TAB_AP_PREPAY_HIST)
1911 AND gt.accounting_event_id is NOT NULL
1912 AND NOT EXISTS
1913 (SELECT 'check if invoice dist has payment event'
1914 FROM AP_Payment_History_ALL APH
1915 WHERE APH.Accounting_Event_ID = GT.Accounting_Event_ID
1916 AND GT.Accounting_Event_ID IS NOT NULL
1917 AND GT.Source_Type = G_SRC_TYP_UNACCT_DISTS);
1918
1919 begin
1920
1921 l_curr_calling_sequence := p_calling_sequence;
1922 debug ('begin update_xla_events: Bulk fetch cursor c_events');
1923
1924 OPEN c_events;
1925 LOOP
1926 FETCH c_events
1927 BULK COLLECT INTO
1928 l_event_ids,
1929 l_trans_ids,
1930 l_org_ids,
1931 l_legal_entity_ids,
1932 l_trans_nums,
1933 l_sources,
1934 l_inv_payment_ids -- 7318763
1935 LIMIT g_fetch_limit;
1936
1937 debug ('update_xla_events: l_event_ids.count='||l_event_ids.count );
1938
1939 EXIT WHEN
1940 l_event_ids.count = 0;
1941
1942 FOR i IN 1 .. l_event_ids.count LOOP
1943
1944 /** Bug 7137359 */
1945 BEGIN
1946
1947 SELECT event_id, event_status_code
1948 INTO l_xla_event, l_xla_event_status
1949 FROM xla_events
1950 WHERE event_id = l_event_ids(i)
1951 AND application_id = 200;
1952
1953 IF l_xla_event_status = 'P' THEN
1954 l_call_xla_api := 'N';
1955 ELSE
1956 l_call_xla_api := 'Y';
1957 END IF;
1958
1959 EXCEPTION
1960
1961 WHEN NO_DATA_FOUND THEN
1962 l_call_xla_api := 'N';
1963
1964 END;
1965
1966 IF l_call_xla_api = 'Y' THEN
1967
1968 l_event_security_context :=
1969 get_event_security_context
1970 ( p_org_id => l_org_ids(i),
1971 p_calling_sequence => l_curr_calling_sequence
1972 );
1973
1974
1975 l_event_source_info :=
1976 get_event_source_info
1977 ( p_legal_entity_id => l_legal_entity_ids(i),
1978 p_ledger_id => g_ledger_id, -- l_ledger_ids(i),
1979 p_trans_id => l_trans_ids(i),
1980 p_event_id => l_event_ids(i),
1981 p_trans_num => l_trans_nums(i),
1982 p_inv_payment_id => l_inv_payment_ids(i), -- 7318763
1983 p_context => l_sources(i),
1984 p_calling_sequence => l_curr_calling_sequence
1985 );
1986
1987 AP_XLA_EVENTS_PKG.UPDATE_EVENT
1988 ( p_event_source_info => l_event_source_info,
1989 p_event_id => l_event_ids(i),
1990 p_event_type_code => NULL,
1991 p_event_date => g_sweep_to_date,
1992 p_event_status_code => NULL,
1993 p_valuation_method => NULL,
1994 p_security_context => l_event_security_context,
1995 p_calling_sequence => l_curr_calling_sequence
1996 );
1997
1998 END IF;
1999
2000 END LOOP;
2001
2002 forall i in l_event_ids.first..l_event_ids.last
2003 UPDATE xla_ae_headers aeh
2004 SET aeh.accounting_date = g_sweep_to_date,
2005 aeh.period_name = g_sweep_to_period,
2006 last_update_date = SYSDATE,
2007 last_updated_by = FND_GLOBAL.user_id
2008 WHERE aeh.event_id = l_event_ids(i)
2009 AND application_id = 200
2010 AND gl_transfer_status_code <> 'Y'
2011 AND accounting_entry_status_code <> 'F';
2012
2013 forall i in l_event_ids.first..l_event_ids.last
2014 UPDATE xla_ae_lines ael
2015 SET ael.accounting_date = g_sweep_to_date,
2016 last_update_date = sysdate,
2017 last_updated_by = FND_GLOBAL.user_id
2018 WHERE ael.ae_header_id in (
2019 SELECT aeh.ae_header_id
2020 FROM xla_ae_headers aeh
2021 WHERE aeh.event_id = l_event_ids(i)
2022 AND aeh.application_id = 200
2023 AND aeh.gl_transfer_status_code <> 'Y'
2024 AND aeh.accounting_entry_status_code <> 'F');
2025
2026 END LOOP;
2027 CLOSE c_events;
2028
2029 debug ('end update_xla_events');
2030
2031 p_success := TRUE;
2032
2033 EXCEPTION
2034 WHEN OTHERS THEN
2035
2036 IF (c_events%ISOPEN) THEN
2037 CLOSE c_events;
2038 END IF;
2039 debug ('EXCEPTION: update_xla_events: '|| sqlerrm);
2040 p_success := FALSE;
2041
2042 END update_xla_events;
2043
2044 /*============================================================================
2045 | FUNCTION - UPDATE_AP_ACCT_DATE
2046 |
2047 | DESCRIPTION
2048 | This function is used to sweep invoice distributions, lines and
2049 | payment, payment history records to an open date in next accounting
2050 | period that are unaccounted in the current period.
2051 |
2052 |
2053 | PRAMETERS
2054 |
2055 |
2056 | KNOWN ISSUES:
2057 |
2058 | NOTES:
2059 |
2060 | MODIFICATION HISTORY
2061 | Date Author Description of Change
2062 | 14-MAR-08 PRANPAUL New
2063 *===========================================================================*/
2064 FUNCTION update_ap_acct_date RETURN BOOLEAN IS
2065
2066
2067 type typ_number_tab is table of number (15) index by binary_integer;
2068
2069 ltab_id typ_number_tab;
2070 ltab_line_num typ_number_tab;
2071 Itab_event_id typ_number_tab; --Bug 9045217
2072
2073 l_dbi_key_value_list ap_dbi_pkg.r_dbi_key_value_arr;
2074
2075 BEGIN
2076 --Bug#8240910 Reverted changes done on UPDATES to handle other exceptions reported
2077 -- by SLA as they are not swept now.
2078 UPDATE ap_invoice_distributions_all aid
2079 SET accounting_date = g_sweep_to_date,
2080 period_name = g_sweep_to_period,
2081 last_update_date = sysdate,
2082 last_updated_by = 5
2083 WHERE aid.invoice_distribution_id in (SELECT gt.invoice_distribution_id
2084 FROM ap_period_close_excps_gt gt
2085 WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
2086 AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL)
2087 AND aid.posted_flag in ('N','S','P') --Bug 9045217
2088 returning invoice_distribution_id bulk collect into l_dbi_key_value_list;
2089
2090 debug ('update_ap_acct_date: total records updated in ap_invoice_distributions_all: '||sql%rowcount);
2091
2092 forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
2093 update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
2094 set process_status_flag = 'Y'
2095 where invoice_distribution_id = l_dbi_key_value_list(i)
2096 AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2097 AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL; -- 7318763
2098
2099 AP_DBI_PKG.Maintain_DBI_Summary
2100 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
2101 p_operation => 'U',
2102 p_key_value_list => l_dbi_key_value_list,
2103 p_calling_sequence => 'AP_PERIOD_CLOSE_PKG.update_ap_acct_date');
2104
2105 debug ('update_ap_acct_date: total distributions processed in ap_period_close_excps_gt: '||l_dbi_key_value_list.count);
2106
2107 forall i in l_dbi_key_value_list.first .. l_dbi_key_value_list.last
2108 UPDATE ap_invoice_lines_all ail
2109 SET accounting_date = g_sweep_to_date,
2110 period_name = g_sweep_to_period,
2111 last_update_date = sysdate,
2112 last_updated_by = 5
2113 WHERE (ail.invoice_id, ail.line_number)
2114 in (SELECT /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ gt.invoice_id, gt.invoice_line_number --Bug 9045217
2115 FROM ap_period_close_excps_gt gt
2116 WHERE gt.invoice_distribution_id = l_dbi_key_value_list(i)
2117 AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2118 AND gt.source_table_name = G_SRC_TAB_AP_INV_DISTS_ALL); -- 7318763
2119
2120 debug ('update_ap_acct_date: total lines processed in ap_invoice_lines_all: '||l_dbi_key_value_list.count);
2121
2122 l_dbi_key_value_list.delete;
2123
2124
2125 UPDATE ap_self_assessed_tax_dist_all astd
2126 SET accounting_date = g_sweep_to_date,
2127 period_name = g_sweep_to_period,
2128 last_update_date = sysdate,
2129 last_updated_by = 5
2130 WHERE astd.invoice_distribution_id
2131 in (SELECT gt.invoice_distribution_id
2132 FROM ap_period_close_excps_gt gt
2133 WHERE gt.source_type = G_SRC_TYP_UNACCT_DISTS
2134 AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL)
2135 AND astd.posted_flag <> 'Y'
2136 returning invoice_distribution_id bulk collect into ltab_id;
2137
2138 debug ('update_ap_acct_date: total records updated in ap_self_assessed_tax_dist_all: '||sql%rowcount);
2139
2140 forall i in ltab_id.first .. ltab_id.last
2141 update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N3)*/ ap_period_close_excps_gt gt --Bug 9045217
2142 set process_status_flag = 'Y'
2143 where invoice_distribution_id = ltab_id(i)
2144 AND gt.source_type = G_SRC_TYP_UNACCT_DISTS
2145 AND gt.source_table_name = G_SRC_TAB_AP_SELF_TAX_DIST_ALL; -- 7318763
2146
2147 debug ('update_ap_acct_date: total self assessed tax distributions processed in ap_period_close_excps_gt: '||ltab_id.count);
2148
2149 ltab_id.delete;
2150
2151
2152 UPDATE ap_invoice_lines_all ail
2153 SET accounting_date = g_sweep_to_date,
2154 period_name = g_sweep_to_period,
2155 last_update_date = sysdate,
2156 last_updated_by = 5
2157 WHERE (ail.invoice_id,ail.line_number) in
2158 (SELECT gt.invoice_id, gt.invoice_line_number
2159 FROM ap_period_close_excps_gt gt
2160 WHERE gt.source_type = G_SRC_TYP_LINES_WITHOUT_DISTS
2161 AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL)
2162 returning ail.invoice_id, ail.line_number bulk collect into ltab_id, ltab_line_num;
2163 debug ('update_ap_acct_date: total records updated in ap_invoice_lines_all: '||sql%rowcount);
2164
2165 forall i in ltab_id.first..ltab_id.last
2166 update /*+index (gt AP_PERIOD_CLOSE_EXCPS_GT_N4)*/ap_period_close_excps_gt gt --Bug 9045217
2167 set process_status_flag = 'Y'
2168 where invoice_id = ltab_id(i)
2169 and invoice_line_number = ltab_line_num(i)
2170 AND gt.source_type =G_SRC_TYP_LINES_WITHOUT_DISTS
2171 AND gt.source_table_name = G_SRC_TAB_AP_INV_LINES_ALL; -- 7318763
2172
2173 debug ('update_ap_acct_date: total invoice lines processed in ap_period_close_excps_gt: '||ltab_id.count );
2174
2175 ltab_id.delete;
2176
2177 UPDATE ap_invoice_payments_all aip
2178 SET accounting_date = g_sweep_to_date,
2179 period_name = g_sweep_to_period,
2180 last_update_date = sysdate,
2181 last_updated_by = 5
2182 WHERE aip.invoice_payment_id in (SELECT gt.invoice_payment_id
2183 FROM ap_period_close_excps_gt gt
2184 WHERE gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
2185 AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS)
2186 AND aip.posted_flag <> 'Y'
2187 returning invoice_payment_id, accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
2188
2189 debug ('update_ap_acct_date: total records updated in ap_invoice_payments_all: '||sql%rowcount);
2190
2191 forall i in ltab_id.first .. ltab_id.last
2192 update ap_period_close_excps_gt gt
2193 set process_status_flag = 'Y'
2194 where invoice_payment_id = ltab_id(i)
2195 AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2196 AND gt.source_type = G_SRC_TYP_UNACCT_INV_PMTS
2197 AND gt.source_table_name = G_SRC_TAB_AP_INV_PAYMENTS; -- 7318763
2198
2199 debug ('update_ap_acct_date: total invoice payments processed in ap_period_close_excps_gt: '||ltab_id.count );
2200
2201 ltab_id.delete;
2202 Itab_event_id.delete; --Bug 9045217
2203
2204 UPDATE ap_payment_history_all aph
2205 SET accounting_date = g_sweep_to_date,
2206 last_update_date = sysdate,
2207 last_updated_by = 5
2208 WHERE aph.payment_history_id in (SELECT gt.payment_history_id
2209 FROM ap_period_close_excps_gt gt
2210 WHERE gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
2211 AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY)
2212 AND aph.posted_flag <> 'Y'
2213 returning aph.payment_history_id,aph.accounting_event_id bulk collect into ltab_id,Itab_event_id; --Bug 9045217
2214
2215 debug ('update_ap_acct_date: total records updated in ap_payment_history_all: '||sql%rowcount);
2216
2217 forall i in ltab_id.first .. ltab_id.last
2218 update ap_period_close_excps_gt gt
2219 set process_status_flag = 'Y'
2220 where payment_history_id = ltab_id(i)
2221 AND gt.accounting_event_id = Itab_event_id(i) --Bug 9045217
2222 AND gt.source_type = G_SRC_TYP_UNACCT_PMT_HISTORY
2223 AND gt.source_table_name = G_SRC_TAB_AP_PMT_HISTORY; -- 7318763
2224 debug ('update_ap_acct_date: total payment history processed in ap_period_close_excps_gt: '||ltab_id.count );
2225
2226 ltab_id.delete;
2227 Itab_event_id.delete; --Bug 9045217
2228
2229 -- gagrawal
2230
2231 UPDATE ap_prepay_history_all apph
2232 SET accounting_date = g_sweep_to_date,
2233 last_update_date = sysdate,
2234 last_updated_by = 5
2235 WHERE apph.accounting_event_id in (SELECT gt.accounting_event_id
2236 FROM ap_period_close_excps_gt gt
2237 WHERE gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
2238 AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST
2239 AND gt.accounting_event_id IS NOT NULL)
2240 AND apph.posted_flag <> 'Y'
2241 returning apph.accounting_event_id bulk collect into ltab_id;
2242
2243 debug ('update_ap_acct_date: total records updated in ap_prepay_history_all: '||sql%rowcount);
2244
2245
2246 forall i in ltab_id.first .. ltab_id.last
2247 update ap_period_close_excps_gt gt
2248 set process_status_flag = 'Y'
2249 where accounting_event_id = ltab_id(i)
2250 AND gt.source_type = G_SRC_TYP_UNACCT_PREPAY_HIST
2251 AND gt.source_table_name = G_SRC_TAB_AP_PREPAY_HIST;
2252
2253 debug ('update_ap_acct_date: total prepay history processed in ap_period_close_excps_gt: '||ltab_id.count );
2254
2255 ltab_id.delete;
2256
2257 return TRUE;
2258
2259 exception
2260 WHEN OTHERS THEN
2261 return FALSE;
2262 END;
2263
2264
2265 --Deletion of orphan events handled as GDF
2266
2267 /*============================================================================
2268 | FUNCTION - SWEEP_TRANSACTIONS
2269 |
2270 | DESCRIPTION
2271 | This function is used to sweep payables transations from one
2272 | accounting period to another. This includes sweeping the following
2273 | transactions -:
2274 | 1. PO Shipments
2275 | 2. XLA Invoice and Payment Accounting events
2276 | 3. Invoice Distributions
2277 | 4. Invoice Lines
2278 | 5. Invoice Payments
2279 | 6. Payment History
2280 |
2281 | PARAMETERS
2282 |
2283 |
2284 |
2285 | KNOWN ISSUES:
2286 |
2287 | NOTES:
2288 |
2289 | MODIFICATION HISTORY
2290 | Date Author Description of Change
2291 | 14-MAR-08 PRANPAUL New
2292 *===========================================================================*/
2293 FUNCTION sweep_transactions
2294 RETURN BOOLEAN
2295 IS
2296
2297 l_success BOOLEAN;
2298 BEGIN
2299
2300 l_success := update_po_close_date;
2301
2302 if (l_success <> TRUE) then
2303 print ('Failure in update_po_close_date while updating PO shipments');
2304 return FALSE;
2305 end if;
2306
2307
2308 update_xla_events('AP_PERIOD_CLOSE_EXCP_PKG.DO_SWEEP',
2309 l_success);
2310
2311 if (l_success <> TRUE) then
2312 print ('Failure in update_xla_events while updating XLA unaccounted events');
2313 return FALSE;
2314 end if;
2315
2316 l_success := update_ebtax_dists;
2317
2318 if (l_success <> TRUE) then
2319 print ('Failure in update_ebtax_dists while updating tax distributions in eBtax');
2320 return FALSE;
2321 end if;
2322
2323 l_success := update_ap_acct_date;
2324
2325
2326 if (l_success <> TRUE) then
2327 print ('Failure in update_ap_acct_date while updating payables invoices and payments');
2328 end if;
2329
2330 return l_success;
2331
2332 END;
2333
2334
2335
2336 /*------------------------------------------------------------------------------------------------------------------------*/
2337 procedure process_period
2338 ( p_ledger_id in number default null
2339 ,p_org_id in number default null
2340 ,p_period_name in varchar2 default null
2341 ,p_period_start_date in date default null
2342 ,p_period_end_date in date default null
2343 ,p_sweep_to_period in varchar2 default null
2344 ,p_action in varchar2
2345 ,p_debug in varchar2 default 'N'
2346 ,p_process_flag out nocopy varchar2
2347 ,p_process_message out nocopy varchar2
2348 )
2349 is
2350
2351 lv_dummy varchar2(3);
2352 lv_closing_status gl_period_statuses.closing_status%type;
2353 ld_sweep_to_end_date gl_period_statuses.end_date%type;
2354
2355 l_msg_count NUMBER;
2356
2357 begin
2358
2359 g_debug := nvl(p_debug,'N');
2360
2361 debug('begin process_period. Current time stamp is= '|| current_timestamp);
2362 debug('Parameters: p_ledger_id='||p_ledger_id||'; p_org_id='||p_org_id||'; p_period_name='||p_period_name
2363 ||'; p_period_start_date='||p_period_start_date||'; p_period_end_date='||p_period_end_date
2364 ||'; p_sweep_to_period='||p_sweep_to_period||'; p_action='||p_action
2365 );
2366
2367 g_ledger_id := p_ledger_id;
2368 g_org_id := p_org_id;
2369 g_period_name := p_period_name;
2370 g_period_start_date := p_period_start_date;
2371 g_period_end_date := p_period_end_date;
2372 g_action := p_action;
2373 g_sweep_to_period := p_sweep_to_period;
2374
2375 debug ('Global variables initialized');
2376
2377 -- validate the input paramters and also performs the initialization
2378 validate_parameters
2379 (p_validation_flag => p_process_flag
2380 ,p_validation_message => p_process_message
2381 );
2382
2383 debug ('validate_parameters: flag='||p_process_flag ||'; message='|| p_process_message);
2384 if (p_process_flag <> 'SS') then
2385 -- parameters are not proper hence should avoid processing further
2386 return;
2387 end if;
2388
2389 -- Populate all the orgs for a ledger
2390
2391 populate_orgs
2392 (p_ledger_id => g_ledger_id
2393 ,p_process_flag => p_process_flag
2394 ,p_process_message => p_process_message
2395 );
2396 debug ('populate_orgs: flag='||p_process_flag ||'; message='|| p_process_message);
2397 if (p_process_flag <> 'SS') then
2398 -- There is problem in populating org GTT hence should avoid processing further
2399 return;
2400 end if;
2401
2402
2403 --Bug#7649020: removed the call to validate_action
2404 --and this code handled in process_period
2405 --Deletion of orphan events handled as GDF
2406
2407 if p_action = G_ACTION_PERIOD_CLOSE then
2408 --
2409 -- User is trying to close the period. We are returning unconditionally because
2410 -- we have already validated the user action. validate_period_close has set the flag
2411 -- and message beased on the validation outcome and if any error, form will take care to
2412 -- display the message. For success, form can continue to close the period
2413 --
2414 validate_period_close
2415 (p_validation_flag => p_process_flag
2416 ,p_validation_message => p_process_message
2417 );
2418 return;
2419
2420 end if;
2421
2422 --
2423 -- We reach here only if the action is one of the following
2424 -- 1. SWEEP
2425 -- 2. Run Un-Accounted Transaction Report (UTR)
2426 -- 3. Run Period Close Exception Report (PCER)
2427 --
2428 -- All of the above three action refers data populated by
2429 -- procedure get_unposted_transactions in global temp table AP_PERIOD_CLOSE_EXCP_GT.
2430 --
2431
2432 lv_dummy := get_unposted_transactions;
2433 debug ('get_unposted_transaction: return value='||lv_dummy);
2434
2435 --Bug#7649020: removed the call to validate_action
2436 --and this code handled in process_period
2437 if g_action = G_ACTION_SWEEP then
2438
2439 PSA_AP_BC_PVT.delete_events(
2440 p_init_msg_list => 'F',
2441 p_ledger_id => g_ledger_id,
2442 p_start_date => g_period_start_date,
2443 p_end_date => g_period_end_date,
2444 p_calling_sequence => 'ap_period_close_pkg.process_period',
2445 x_return_status => p_process_flag,
2446 x_msg_count =>l_msg_count,
2447 x_msg_data => p_process_message
2448 );
2449
2450 if p_process_flag <> 'S' then
2451 p_process_flag := 'EE';
2452 print ('l_msg_count = ' || l_msg_count || ' error msg - ' || p_process_message);
2453 -- there is either expected or un-expected error
2454 return; --app_exception.raise_exception ('AP',-20001,p_process_message);
2455 end if;
2456
2457 debug ('begin sweep_transactions: current timestamp is= '||current_timestamp);
2458
2459 if NOT sweep_transactions then -- perform the SWEEP logic
2460 p_process_flag := 'EE';
2461 p_process_message := 'AP_SWEEP_FAILED';
2462 return;
2463 end if;
2464
2465 debug ('sweep_transactions: flag='||p_process_flag||'; message='||p_process_message);
2466 debug ('end sweep_transactions: current timestamp is= '||current_timestamp);
2467
2468 end if;
2469 debug ('end process period: current timestamp is= '||current_timestamp);
2470 p_process_flag := 'SS';
2471 exception
2472 when others then
2473 p_process_flag := 'UE';
2474 p_process_message:='ERROR: process_period:' || sqlerrm;
2475 debug ('EXCEPTION: process_period: '||sqlerrm);
2476 end process_period;
2477
2478 /*------------------------------------------------------------------------------------------------------------------------*/
2479
2480 function before_report_apxpcer
2481 return boolean
2482 is
2483 lv_process_flag varchar2 (2);
2484 lv_process_message varchar2 (2000);
2485 begin
2486
2487 g_period_start_date := fnd_date.canonical_to_date (g_start_date);
2488 g_period_end_date := fnd_date.canonical_to_date (g_end_date);
2489
2490 debug ('Begin process_period: current timestamp:'|| current_timestamp);
2491
2492 process_period
2493 (p_ledger_id => G_ledger_id
2494 ,p_period_start_date => g_period_start_date
2495 ,p_period_end_date => g_period_end_date
2496 ,p_period_name => g_period_name
2497 ,p_action => G_ACTION_PCER
2498 ,p_debug => g_debug
2499 ,p_process_flag => lv_process_flag
2500 ,p_process_message => lv_process_message
2501 );
2502 debug ('End process_period: current timestamp:'||current_timestamp);
2503
2504 if lv_process_flag <> 'SS' then
2505 print ('before_report_apxpcer: flag='|| lv_process_flag ||'; message='||lv_process_message);
2506 return (false);
2507 end if;
2508
2509 return (true);
2510
2511 exception
2512 when others then
2513 print ('EXCEPTION: before_report_apxpcer: '|| sqlerrm);
2514 return (false);
2515 end before_report_apxpcer;
2516
2517 /*------------------------------------------------------------------------------------------------------------------------*/
2518
2519 /*============================================================================
2520 | PROCEDURE - PROCESS_APTRNSWP
2521 |
2522 | DESCRIPTION
2523 | This procedure is used as wrapper call to process_period procedure
2524 | for PL/SQL stored procedure executable for Payables Transaction
2525 | Sweep concurrent program.
2526 |
2527 |
2528 | PARAMETERS
2529 |
2530 |
2531 |
2532 | KNOWN ISSUES:
2533 |
2534 | NOTES:
2535 |
2536 | MODIFICATION HISTORY
2537 | Date Author Description of Change
2538 | 14-MAR-08 PRANPAUL New
2539 *===========================================================================*/
2540
2541 PROCEDURE process_aptrnswp ( ErrCode OUT NOCOPY NUMBER,
2542 ErrMesg OUT NOCOPY VARCHAR2,
2543 P_REPORTING_LEVEL IN VARCHAR2,
2544 P_REPORTING_ENTITY_ID IN VARCHAR2,
2545 P_SET_OF_BOOKS_ID IN NUMBER,
2546 P_FROM_ACCTG_DATE IN DATE,
2547 P_TO_ACCTG_DATE IN DATE,
2548 P_PERIOD_NAME IN VARCHAR2,
2549 P_SWEEP_NOW IN VARCHAR2,
2550 P_TO_PERIOD IN VARCHAR2,
2551 P_DEBUG_SWITCH IN VARCHAR2,
2552 P_TRACE_SWITCH IN VARCHAR2 )
2553
2554 is
2555 lv_process_flag varchar2 (2);
2556 lv_process_message varchar2 (2000);
2557 begin
2558 debug ('begin process_aptrnswp: current timestamp:'||current_timestamp);
2559 process_period
2560 (p_ledger_id => P_SET_OF_BOOKS_ID
2561 ,p_period_name => P_PERIOD_NAME
2562 ,p_sweep_to_period => P_TO_PERIOD
2563 ,p_action => G_ACTION_SWEEP
2564 ,p_process_flag => lv_process_flag
2565 ,p_process_message => lv_process_message
2566 );
2567 debug ('end process_aptrnswp: current timestamp:'||current_timestamp);
2568
2569 end process_aptrnswp;
2570
2571 /*============================================================================
2572 | FUNCTION - BEFORE_REPORT_APXUATR
2573 |
2574 | DESCRIPTION
2575 | This function is used as a wrapper for Unaccounted Transactions report
2576 | and Payables Sweep program. This function is directky called from XML
2577 | Pub report.
2578 |
2579 | PARAMETERS
2580 |
2581 |
2582 |
2583 | KNOWN ISSUES:
2584 |
2585 | NOTES:
2586 |
2587 | MODIFICATION HISTORY
2588 | Date Author Description of Change
2589 | 20-MAR-08 PRANPAUL New
2590 *===========================================================================*/
2591
2592 function before_report_apxuatr
2593 return boolean
2594 is
2595 lv_process_flag varchar2 (2);
2596 lv_process_message varchar2 (2000);
2597 l_action varchar2 (100);
2598 begin
2599
2600 g_period_start_date := fnd_date.canonical_to_date (g_start_date);
2601 g_period_end_date := fnd_date.canonical_to_date (g_end_date);
2602
2603
2604 debug ('begin before_report_apxuatr: current timestamp:' || current_timestamp);
2605 debug ('g_reporting_level='||g_reporting_level);
2606
2607 if g_reporting_level = 1000 then
2608 g_ledger_id := g_reporting_entity_id;
2609 elsif g_reporting_level = 3000 then
2610 g_org_id := g_reporting_entity_id;
2611 end if;
2612
2613 if g_sweep_now = 'Y' then
2614 l_action := G_ACTION_SWEEP;
2615 else
2616 l_action := G_ACTION_UTR;
2617 end if;
2618
2619 process_period
2620 (p_ledger_id => g_ledger_id
2621 ,p_org_id => g_org_id
2622 ,p_period_start_date => g_period_start_date
2623 ,p_period_end_date => g_period_end_date
2624 ,p_period_name => g_period_name
2625 ,p_action => l_action
2626 ,p_sweep_to_period => g_sweep_to_period
2627 ,p_debug => g_debug
2628 ,p_process_flag => lv_process_flag
2629 ,p_process_message => lv_process_message
2630 );
2631
2632 debug ('end before_report_apxuatr: current timestamp: '|| current_timestamp);
2633
2634 if lv_process_flag <> 'SS' then
2635 print ('before_report_apxuatr: flag='|| lv_process_flag ||'; message='||lv_process_message);
2636 return (false);
2637 end if;
2638
2639 return (true);
2640
2641 end before_report_apxuatr;
2642
2643 /*------------------------------------------------------------------------------------------------------------------------*/
2644
2645 procedure check_orgs_for_ledger
2646 (p_ledger_id in number
2647 ,p_process_flag out nocopy varchar2
2648 ,p_process_message out nocopy varchar2
2649 )
2650 is
2651 begin
2652
2653 --
2654 -- This procedure is called from forms to check if SWEEP can be performed
2655 -- Hence first populate the org GTT and call validate_sweep to check if sweep
2656 -- action is valid
2657 --
2658
2659 populate_orgs
2660 (p_ledger_id => p_ledger_id
2661 ,p_process_flag => p_process_flag
2662 ,p_process_message => p_process_message
2663 );
2664
2665 if (p_process_flag <> 'SS') then
2666 -- There is problem in populating org GTT hence should avoid processing further
2667 return;
2668 end if;
2669
2670 validate_sweep (p_validation_flag => p_process_flag
2671 ,p_validation_message => p_process_message
2672 );
2673
2674 end check_orgs_for_ledger;
2675
2676
2677
2678 end ap_period_close_pkg;