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