[Home] [Help]
PACKAGE BODY: APPS.AP_APXTRSWP_XMLP_PKG
Source
1 PACKAGE BODY AP_APXTRSWP_XMLP_PKG AS
2 /* $Header: APXTRSWPB.pls 120.0 2007/12/27 08:41:34 vjaganat noship $ */
3
4 USER_EXIT_FAILURE EXCEPTION;
5
6 FUNCTION get_base_curr_data RETURN BOOLEAN IS
7
8 base_curr ap_system_parameters.base_currency_code%TYPE; cash_acct_flag VARCHAR2(1);
9 prec fnd_currencies.precision%TYPE; min_au fnd_currencies.minimum_accountable_unit%TYPE; descr fnd_currencies.description%TYPE;
10 BEGIN
11
12 base_curr := '';
13 cash_acct_flag := 'N';
14 prec := 0;
15 min_au := 0;
16 descr := '';
17
18
19 begin
20 SELECT nvl(sob.sla_ledger_cash_basis_flag, 'N')
21 INTO cash_acct_flag
22 FROM gl_sets_of_books sob
23 WHERE sob.set_of_books_id = p_set_of_books_id;
24 exception
25 when no_data_found then
26 null;
27 when others then
28 null;
29
30 end;
31
32 C_BASE_CURRENCY_CODE := base_curr;
33 C_BASE_PRECISION := prec;
34 C_BASE_MIN_ACCT_UNIT := min_au;
35 C_BASE_DESCRIPTION := descr;
36
37 IF (cash_acct_flag = 'Y') THEN
38 P_ACCT_METHOD := 'C';
39 ELSE
40 P_ACCT_METHOD := 'A';
41 END IF;
42
43 RETURN (TRUE);
44
45 RETURN NULL; EXCEPTION
46
47 WHEN OTHERS THEN
48 RETURN (FALSE);
49
50 END;
51
52 FUNCTION custom_init RETURN BOOLEAN IS
53
54 BEGIN
55
56
57 RETURN (TRUE);
58
59 RETURN NULL; EXCEPTION
60
61 WHEN OTHERS THEN
62 RETURN (FALSE);
63
64 END;
65
66 FUNCTION get_cover_page_values RETURN BOOLEAN IS
67
68 BEGIN
69
70 RETURN(TRUE);
71
72 RETURN NULL; EXCEPTION
73 WHEN OTHERS THEN
74 RETURN(FALSE);
75
76 END;
77
78 FUNCTION get_nls_strings RETURN BOOLEAN IS
79 nls_void ap_lookup_codes.displayed_field%TYPE; nls_na ap_lookup_codes.displayed_field%TYPE; nls_all ap_lookup_codes.displayed_field%TYPE; nls_yes fnd_lookups.meaning%TYPE; nls_no fnd_lookups.meaning%TYPE;
80 BEGIN
81
82 SELECT ly.meaning,
83 ln.meaning,
84 l1.displayed_field,
85 l2.displayed_field,
86 l3.displayed_field
87 INTO nls_yes,
88 nls_no,
89 nls_all,
90 nls_void,
91 nls_na
92 FROM fnd_lookups ly,
93 fnd_lookups ln,
94 ap_lookup_codes l1,
95 ap_lookup_codes l2,
96 ap_lookup_codes l3
97 WHERE ly.lookup_type = 'YES_NO'
98 AND ly.lookup_code = 'Y'
99 AND ln.lookup_type = 'YES_NO'
100 AND ln.lookup_code = 'N'
101 AND l1.lookup_type = 'NLS REPORT PARAMETER'
102 AND l1.lookup_code = 'ALL'
103 AND l2.lookup_type = 'NLS TRANSLATION'
104 AND l2.lookup_code = 'VOID'
105 AND l3.lookup_type = 'NLS REPORT PARAMETER'
106 AND l3.lookup_code = 'NA';
107
108 C_NLS_YES := nls_yes;
109 C_NLS_NO := nls_no;
110 C_NLS_ALL := nls_all;
111 C_NLS_VOID := nls_void;
112 C_NLS_NA := nls_na;
113
114
115
116 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_APPRVL_NO_DATA"');*/null;
117
118 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_NO_DATA_EXISTS"');*/null;
119
120 /*srw.user_exit('FND MESSAGE_NAME APPL="SQLAP" NAME="AP_ALL_END_OF_REPORT"');*/null;
121
122 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_NLS_END_OF_REPORT"');*/null;
123
124
125
126
127 /*srw.user_exit('FND MESSAGE_NAME APPL="FND" NAME="FND_MO_RPT_PARTIAL_LEDGER"');*/null;
128
129 /*srw.user_exit('FND MESSAGE_GET OUTPUT_FIELD=":C_LEDGER_PARTIAL_OU"');*/null;
130
131
132
133 RETURN (TRUE);
134
135 RETURN NULL; EXCEPTION
136 WHEN OTHERS THEN
137 RETURN (FALSE);
138 END;
139
140 function BeforeReport return boolean is
141 begin
142
143
144
145 DECLARE
146
147 init_failure EXCEPTION;
148
149 BEGIN
150 LEDGER_PART:=mo_utils.check_ledger_in_sp(p_set_of_books_id );
151 if P_SWEEP_NOW is not null
152 then
153 P_SWEEP_NOW_1 := P_SWEEP_NOW;
154 end if;
155 C_REPORT_START_DATE := sysdate;
156
157
158
159
160 IF p_sweep_now IS NULL
161 THEN
162 p_unacct_rpt := 'Y';
163
164 p_sweep_now_1 := 'N';
165 ELSE
166 p_unacct_rpt := 'N';
167
168 END IF;
169
170 /*SRW.USER_EXIT('FND SRWINIT');*/null;
171
172 IF (p_debug_switch in ('y','Y')) THEN
173 /*SRW.MESSAGE('1','After SRWINIT');*/null;
174
175 END IF;
176
177
178 IF (p_trace_switch in ('y','Y')) THEN
179 /*SRW.DO_SQL('alter session set sql_trace TRUE');*/null;
180
181 END IF;
182
183
184 IF (get_company_name() <> TRUE) THEN RAISE init_failure;
185 END IF;
186 IF (p_debug_switch in ('y','Y')) THEN
187 /*SRW.MESSAGE('2','After Get_Company_Name');*/null;
188
189 END IF;
190
191
192 IF (get_nls_strings() <> TRUE) THEN RAISE init_failure;
193 END IF;
194 IF (p_debug_switch in ('y','Y')) THEN
195 /*SRW.MESSAGE('3','After Get_NLS_Strings');*/null;
196
197 END IF;
198
199
200 IF (get_base_curr_data() <> TRUE) THEN RAISE init_failure;
201 END IF;
202 IF (p_debug_switch in ('y','Y')) THEN
203 /*SRW.MESSAGE('4','After Get_Base_Curr_Data');*/null;
204
205 END IF;
206
207
208
209 IF p_period_name IS NOT NULL
210 THEN SELECT start_date, end_date
211 INTO p_start_date, p_end_date
212 FROM gl_period_statuses
213 WHERE period_name = p_period_name
214 AND application_id = 201
215 AND set_of_books_id = p_set_of_books_id;
216 ELSE IF P_FROM_ACCTG_DATE IS NOT NULL AND P_TO_ACCTG_DATE IS NOT NULL THEN
217 p_start_date := P_FROM_ACCTG_DATE ;
218 p_end_date := P_TO_ACCTG_DATE ;
219 ELSIF P_FROM_ACCTG_DATE IS NOT NULL AND P_TO_ACCTG_DATE IS NULL THEN
220 p_start_date := P_FROM_ACCTG_DATE ;
221 p_end_date := sysdate + 75000 ;
222 ELSIF P_FROM_ACCTG_DATE IS NULL AND P_TO_ACCTG_DATE IS NOT NULL THEN
223 p_start_date := sysdate - 75000 ;
224 p_end_date := P_TO_ACCTG_DATE ;
225 ELSIF P_FROM_ACCTG_DATE IS NULL AND P_TO_ACCTG_DATE IS NULL THEN
226 p_start_date := sysdate - 75000 ;
227 p_end_date := sysdate + 75000 ;
228 else
229 p_start_date := ' ';
230 p_end_date := ' ';
231 END IF ;
232 END IF;
233
234 IF (p_debug_switch in ('y','Y')) THEN
235 /*SRW.MESSAGE('4','After Getting the Start and End date');*/null;
236
237 END IF;
238
239 IF (p_debug_switch in ('y','Y')) THEN
240 /*SRW.MESSAGE('5','After Get Sort by Alternate');*/null;
241
242 END IF;
243
244
245 IF (get_org_placeholders() <> TRUE) THEN
246 RAISE init_failure;
247 END IF;
248 IF (p_debug_switch in ('y','Y')) THEN
249 /*SRW.MESSAGE('6','After Get_Org_Placeholders');*/null;
250
251 END IF;
252
253 IF (get_acctg_date() <> TRUE) THEN
254 RAISE init_failure;
255 END IF;
256 IF (p_debug_switch in ('y','Y')) THEN
257 /*SRW.MESSAGE('7','After Get_Acctg_Date');*/null;
258
259 END IF;
260 IF (get_filtered_dates <> TRUE) THEN
261 RAISE init_failure;
262 END IF;
263 IF (p_debug_switch in ('y','Y')) THEN
264 /*SRW.MESSAGE('71','After Get_Filtered_Dates');*/null;
265
266 END IF;
267
268 IF (p_sweep_now_1 = 'Y') THEN
269 c_sweep_now := c_nls_yes;
270 ELSE
271 c_sweep_now := c_nls_no;
272 END IF;
273
274 IF (p_debug_switch in ('y','Y')) THEN
275 /*SRW.MESSAGE('8','After setting C_Sweep_Now');*/null;
276
277 END IF;
278 IF (p_debug_switch in ('y','Y')) THEN
279 /*SRW.BREAK;*/null;
280
281 END IF;
282
283
284
285 RETURN (TRUE);
286 EXCEPTION
287
288 WHEN OTHERS THEN
289 /*RAISE SRW.PROGRAM_ABORT;*/RAISE_APPLICATION_ERROR(-20101,null);
290 null;
291 END;
292 return (TRUE);
293 end;
294
295 function AfterReport return boolean is
296 begin
297
298 DECLARE
299 init_failure EXCEPTION;
300 BEGIN
301 /*SRW.USER_EXIT('FND SRWEXIT');*/null;
302
303 if (p_sweep_now_1 = 'Y') then
304 if (update_acctg_dates() <> TRUE) then
305 RAISE init_failure;
306 end if;
307 if (P_DEBUG_SWITCH = 'Y') THEN
308 /*SRW.MESSAGE('20','After updating invoices and payments');*/null;
309
310 end if;
311 end if;
312 EXCEPTION
313 WHEN OTHERS THEN
314 RAISE_application_error(-20101,null);/*SRW.PROGRAM_ABORT;*/null;
315
316 END; return (TRUE);
317 end;
318
319 FUNCTION get_company_name RETURN BOOLEAN IS
320 l_chart_of_accounts_id gl_sets_of_books.chart_of_accounts_id%TYPE;
321 l_name gl_sets_of_books.name%TYPE;
322 l_sob_id number;
323 BEGIN
324
325 if P_SET_OF_BOOKS_ID is not null then
326 l_sob_id := p_set_of_books_id;
327 SELECT name,
328 chart_of_accounts_id
329 INTO l_name,
330 l_chart_of_accounts_id
331 FROM gl_sets_of_books
332 WHERE set_of_books_id = l_sob_id;
333
334 c_company_name_header := l_name;
335 c_chart_of_accounts_id := l_chart_of_accounts_id;
336
337 end if;
338
339 RETURN (TRUE);
340
341 RETURN NULL; EXCEPTION
342
343 WHEN OTHERS THEN
344 RETURN (FALSE);
345
346 END;
347 function M_G_3_UNACCT_PAY_GRPFRFormatTr(co_org_id number) return varchar2 is
348 cup_counter number := 0;
349
350 BEGIN
351
352 SELECT COUNT(*)
353 INTO cup_counter
354 FROM ap_invoice_payments_all
355 WHERE accounting_date BETWEEN p_start_date AND p_end_date
356 AND posted_flag IN ('N','S') --Bug3476167
357 AND org_id = co_org_id
358 AND rownum = 1;
359
360 IF cup_counter = 0
361 THEN RETURN('FALSE');
362 ELSE RETURN('TRUE');
363 END IF;
364
365 END;
366 function M_G_2_UNACCT_INV_GRPFRFormatTr(co_org_id number) return varchar2 is
367
368 cui_counter NUMBER := 0;
369
370 BEGIN
371 SELECT COUNT(*)
372 INTO cui_counter
373 FROM ap_invoice_distributions_all
374 WHERE accounting_date BETWEEN p_start_date AND p_end_date
375 AND accrual_posted_flag = 'N'
376 AND p_acct_method = 'A'
377 AND org_id = CO_org_id
378 AND rownum = 1;
379
380 IF cui_counter = 0
381 THEN RETURN('FALSE');
382 ELSE RETURN('TRUE');
383 END IF;
384
385 END;
386 function M_G_4_FUTURE_PAY_GRPFRFormatTr(co_org_id number) return varchar2 is
387 cfp_counter number := 0;
388
389 BEGIN
390
391 SELECT COUNT(*)
392 INTO cfp_counter
393 FROM ap_checks_all
394 WHERE future_pay_due_date IS NOT NULL
395 AND status_lookup_code = 'ISSUED'
396 AND future_pay_due_date BETWEEN p_start_date AND p_end_date
397 AND org_id = co_org_id
398 AND rownum = 1;
399
400 IF cfp_counter = 0
401 THEN RETURN('FALSE');
402 ELSE RETURN('TRUE');
403 END IF;
404
405 END;
406 function M_G_5_PAY_BATCH_GRPFRFormatTri(co_org_id number) return varchar2 is
407 cpb_counter number := 0;
408
409 BEGIN
410
411 SELECT COUNT(*)
412 INTO cpb_counter
413 FROM ap_inv_selection_criteria_all
414 WHERE check_date BETWEEN p_start_date AND p_end_date
415 AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
416 AND org_id = co_org_id
417 AND rownum = 1;
418
419 IF cpb_counter = 0
420 THEN RETURN('FALSE');
421 ELSE RETURN('TRUE');
422 END IF;
423
424 END;
425 function M_G_6_UNTRANS_ACCT_GRPFRFormat(co_org_id number) return varchar2 is
426 cut_counter number := 0;
427 BEGIN
428
429 -- Bug 3739324 - Replaced AP table with XLA table
430 SELECT COUNT(*)
431 INTO cut_counter
432 FROM xla_ae_headers xah, xla_transaction_entities xte
433 WHERE xah.accounting_date BETWEEN p_start_date AND p_end_date
434 AND xah.gl_transfer_status_code = 'N'
435 AND xah.entity_id = xte.entity_id
436 AND xte.security_id_int_1 = co_org_id
437 AND rownum = 1;
438
439 IF cut_counter = 0
440 THEN RETURN('FALSE');
441 ELSE RETURN('TRUE');
442 END IF;
443
444 END;
445 FUNCTION get_flexdata RETURN BOOLEAN IS
446
447 BEGIN
448
449 if C_CHART_OF_ACCOUNTS_ID is not null then
450
451 null;
452 return (TRUE);
453 else
454 /*SRW.MESSAGE('999','Cannot use flex API without a chart of accounts ID.');*/null;
455
456 return(FALSE);
457 end if;
458
459 RETURN NULL; EXCEPTION
460 WHEN OTHERS THEN
461 RETURN(FALSE);
462 END;
463
464 FUNCTION GET_ORG_PLACEHOLDERS RETURN BOOLEAN IS
465 multi_org_installation fnd_product_groups.multi_org_flag%TYPE ;
466 BEGIN
467 SELECT multi_org_flag
468 INTO multi_org_installation
469 FROM fnd_product_groups
470 WHERE product_group_id = 1;
471
472
473 IF multi_org_installation = 'Y' THEN
474
475 c_inv_multi_org_where :=
476 ' AND aid.org_id = oi.organization_id
477 AND oi.org_information_context = ''Operating Unit Information''
478 AND DECODE(LTRIM(oi.org_information3,''0123456789''), NULL
479 , TO_NUMBER(oi.org_information3)
480 , NULL ) = '||p_set_of_books_id||'
481 AND DECODE(LTRIM(oi.org_information2,''0123456789''), NULL
482 , TO_NUMBER(oi.org_information2)
483 , NULL ) = le.organization_id
484 AND ou.organization_id = oi.organization_id
485 AND ou.language = USERENV(''LANG'')
486 AND le.language = USERENV(''LANG'') ' ;
487
488 c_aip_multi_org_where :=
489 ' AND aip.org_id = oi.organization_id
490 AND oi.org_information_context = ''Operating Unit Information''
491 AND DECODE(LTRIM(oi.org_information3,''0123456789''), NULL
492 , TO_NUMBER(oi.org_information3)
493 , NULL ) = '||p_set_of_books_id||'
494 AND DECODE(LTRIM(oi.org_information2,''0123456789''), NULL
495 , TO_NUMBER(oi.org_information2)
496 , NULL ) = le.organization_id
497 AND ou.organization_id = oi.organization_id
498 AND ou.language = USERENV(''LANG'')
499 AND le.language = USERENV(''LANG'') ' ;
500
501 c_aph_multi_org_where :=
502 ' AND aph.org_id = oi.organization_id
503 AND oi.org_information_context = ''Operating Unit Information''
504 AND DECODE(LTRIM(oi.org_information3,''0123456789''), NULL
505 , TO_NUMBER(oi.org_information3)
506 , NULL ) = '||p_set_of_books_id||'
507 AND DECODE(LTRIM(oi.org_information2,''0123456789''), NULL
508 , TO_NUMBER(oi.org_information2)
509 , NULL ) = le.organization_id
510 AND ou.organization_id = oi.organization_id
511 AND ou.language = USERENV(''LANG'')
512 AND le.language = USERENV(''LANG'') ' ;
513
514
515
516
517
518 c_select_le := 'le.name ';
519 c_select_ou := 'ou.name ';
520 c_org_from_tables := 'HR_ORGANIZATION_INFORMATION OI,
521 HR_ALL_ORGANIZATION_UNITS_TL LE,
522 HR_ALL_ORGANIZATION_UNITS_TL OU' ;
523
524 ELSE
525 c_inv_multi_org_where := 'AND 1=1';
526 c_aip_multi_org_where := 'AND 1 = 1';
527 c_aph_multi_org_where := ' AND 1=1';
528 c_pmts_multi_org_where := 'AND 1=1';
529 c_select_le := '''Legal Entity''';
530 c_select_ou := '''Operating Unit''';
531 c_org_from_tables := 'sys.dual';
532 END IF;
533
534 RETURN (TRUE);
535
536 EXCEPTION
537 WHEN OTHERS THEN
538 RETURN (FALSE);
539 END;
540
541 FUNCTION UPDATE_PO_CLOSE_DATE RETURN BOOLEAN IS
542
543
544
545 CURSOR PO_LIST IS
546 SELECT DISTINCT PLL.LINE_LOCATION_ID,
547 PLL.CLOSED_DATE
548 FROM PO_LINE_LOCATIONS_ALL PLL,
549 PO_DISTRIBUTIONS_ALL PD,
550 AP_INVOICE_DISTRIBUTIONS_ALL AID
551 WHERE PLL.LINE_LOCATION_ID = PD.LINE_LOCATION_ID
552 AND PLL.CLOSED_DATE IS NOT NULL
553 AND PD.PO_DISTRIBUTION_ID = AID.PO_DISTRIBUTION_ID
554 AND AID.POSTED_FLAG = 'N'
555 AND NVL(AID.ORG_ID,-99) = NVL(PD.ORG_ID,-99)
556 AND NVL(AID.ORG_ID,-99) IN
557 (SELECT NVL(ASP.ORG_ID,-99)
558 FROM HR_ORGANIZATION_INFORMATION OI,
559 HR_ALL_ORGANIZATION_UNITS_TL LE,
560 HR_ALL_ORGANIZATION_UNITS_TL OU,
561 AP_SYSTEM_PARAMETERS_ALL ASP,
562 GL_SETS_OF_BOOKS SOB
563 WHERE nvl(SOB.SLA_LEDGER_CASH_BASIS_FLAG, 'N') <> 'Y'
564 AND SOB.SET_OF_BOOKS_ID = ASP.SET_OF_BOOKS_ID
565 AND ASP.ORG_ID = OI.ORGANIZATION_ID
566 AND OU.ORGANIZATION_ID = OI.ORGANIZATION_ID
567 AND OI.ORG_INFORMATION_CONTEXT = 'Operating Unit Information'
568 AND DECODE(LTRIM(OI.ORG_INFORMATION3,'0123456789'), NULL ,
569 TO_NUMBER(OI.ORG_INFORMATION3), NULL ) = P_SET_OF_BOOKS_ID
570 AND DECODE(LTRIM(OI.ORG_INFORMATION2,'0123456789'), NULL ,
571 TO_NUMBER(OI.ORG_INFORMATION2), NULL )
572 = LE.ORGANIZATION_ID
573 AND OU.ORGANIZATION_ID = OI.ORGANIZATION_ID
574 AND OU.LANGUAGE = USERENV('LANG')
575 AND LE.LANGUAGE = USERENV('LANG')
576 )
577 AND ( (P_PERIOD_NAME IS NULL AND AID.ACCOUNTING_DATE BETWEEN
578 P_FROM_ACCTG_DATE and P_TO_ACCTG_DATE)
579 OR (P_PERIOD_NAME IS NOT NULL AND AID.PERIOD_NAME = P_PERIOD_NAME ))
580 AND AID.PO_DISTRIBUTION_ID IS NOT NULL
581 GROUP BY PLL.LINE_LOCATION_ID, PLL.CLOSED_DATE, AID.PO_DISTRIBUTION_ID
582 HAVING SUM(AID.AMOUNT) > 0;
583
584
585 l_ship_close_date DATE;
586 l_header_close_date DATE;
587 l_header_id NUMBER;
588 l_line_loc_id NUMBER;
589 BEGIN
590
591 OPEN PO_LIST;
592
593 LOOP
594
595 FETCH PO_LIST INTO l_line_loc_id,
596 l_ship_close_date;
597
598 EXIT WHEN PO_LIST%NOTFOUND OR PO_LIST%NOTFOUND IS NULL;
599
600 if (l_ship_close_date is not null and
601 l_ship_close_date < c_sweep_to_date) then
602
603 update po_line_locations_all
604 set closed_date = c_sweep_to_date
605 where line_location_id = l_line_loc_id;
606
607 select distinct POH.po_header_id,
608 POH.closed_date
609 into l_header_id,
610 l_header_close_date
611 from po_headers_all POH,
612 po_line_locations_all PLL
613 where POH.po_header_id = PLL.po_header_id
614 and PLL.line_location_id = l_line_loc_id;
615
616 if (l_header_close_date is not null and
617 l_header_close_date < c_sweep_to_date) then
618 update po_headers
619 set closed_date = c_sweep_to_date
620 where po_header_id = l_header_id;
621 end if;
622 end if;
623
624 END LOOP;
625
626
627 RETURN(TRUE);
628
629 RETURN NULL; exception
630 WHEN OTHERS THEN
631 RETURN (FALSE);
632 END;
633
634 FUNCTION UPDATE_ACCTG_DATES RETURN BOOLEAN IS
635
636 CURSOR DIST_ORGS IS
637 SELECT aid.invoice_id, aid.invoice_distribution_id
638 FROM ap_invoice_distributions_all aid
639 WHERE aid.accrual_posted_flag = 'N'
640 AND ((p_period_name is null and aid.accounting_date between p_from_acctg_date
641 and p_to_acctg_date)
642 OR (p_period_name is not null and aid.period_name = p_period_name))
643 AND nvl(aid.org_id, -99) IN
644 (select nvl(asp.org_id, -99)
645 from hr_organization_information oi,
646 hr_all_organization_units_tl le,
647 hr_all_organization_units_tl ou,
648 ap_system_parameters_all asp
649 where asp.accounting_method_option = 'Accrual'
650 and asp.org_id = oi.organization_id
651 and ou.organization_id = oi.organization_id
652 and oi.org_information_context =
653 'Operating Unit Information'
654 and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
655 , TO_NUMBER(oi.org_information3) , NULL ) =
656 p_set_of_books_id
657 and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
658 , TO_NUMBER(oi.org_information2), NULL) =
659 le.organization_id
660 and ou.organization_id = oi.organization_id
661 and ou.language = USERENV('LANG')
662 and le.language = USERENV('LANG'));
663
664 CURSOR DIST_ORG IS
665 SELECT aid.invoice_id, aid.invoice_distribution_id
666 FROM ap_invoice_distributions_all aid,
667 ap_system_parameters_all asp
668 WHERE aid.accrual_posted_flag = 'N'
669 AND asp.accounting_method_option = 'Accrual'
670 AND ((p_period_name is null and aid.accounting_date between p_from_acctg_date
671 and p_to_acctg_date)
672 OR (p_period_name is not null and aid.period_name = p_period_name));
673
674
675 v_no_orgs NUMBER(5);
676 l_invoice_id NUMBER(15);
677 l_invoice_distribution_id NUMBER(15);
678
679 BEGIN
680 /*SRW.MESSAGE(0, 'UPDATE_ACCTG_DATES');*/null;
681
682 if (update_po_close_date() <> TRUE) then
683 return(FALSE);
684 end if;
685
686
687 select count(*)
688 into v_no_orgs
689 from ap_system_parameters_all;
690
691
692
693
694 IF v_no_orgs > 1 THEN
695
696 /*srw.message('10', 'Updating invoice distributions....');*/null;
697
698
699 OPEN DIST_ORGS;
700
701 LOOP
702
703 FETCH DIST_ORGS INTO l_invoice_id,
704 l_invoice_distribution_id;
705
706 EXIT WHEN DIST_ORGS%NOTFOUND OR DIST_ORGS%NOTFOUND IS NULL;
707
708 UPDATE ap_invoice_distributions_all aid
709 SET accounting_date = c_sweep_to_date,
710 period_name = p_to_period,
711 last_update_date = sysdate,
712 last_updated_by = 5
713 WHERE aid.invoice_distribution_id = l_invoice_distribution_id;
714
715 AP_DBI_PKG.Maintain_DBI_Summary
716 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
717 p_operation => 'U',
718 p_key_value1 => l_invoice_id,
719 p_key_value2 => l_invoice_distribution_id,
720 p_calling_sequence => 'APXTRSWP');
721
722
723 END LOOP;
724
725 /*srw.message('11', 'Done updating invoice distributions.');*/null;
726
727 /*srw.message('12', 'Updating invoice payments....');*/null;
728
729
730 UPDATE ap_invoice_payments_all aip
731 SET accounting_date = c_sweep_to_date,
732 period_name = p_to_period,
733 last_update_date = sysdate,
734 last_updated_by = 5
735 WHERE posted_flag IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
736 and p_to_acctg_date)
737 OR (p_period_name is not null and period_name = p_period_name))
738 AND nvl(aip.org_id, -99) IN
739 (select nvl(asp.org_id, -99)
740 from hr_organization_information oi,
741 hr_all_organization_units_tl le,
742 hr_all_organization_units_tl ou,
743 ap_system_parameters_all asp
744
745 where
746 asp.org_id = oi.organization_id
747 and ou.organization_id = oi.organization_id
748 and oi.org_information_context =
749 'Operating Unit Information'
750 and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
751 , TO_NUMBER(oi.org_information3) , NULL ) =
752 p_set_of_books_id
753 and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
754 , TO_NUMBER(oi.org_information2), NULL) =
755 le.organization_id
756 and ou.organization_id = oi.organization_id
757 and ou.language = USERENV('LANG')
758 and le.language = USERENV('LANG'));
759
760 /*srw.message('13', 'Done updating invoice payments.');*/null;
761
762 /*srw.message('14', 'Updating payment history records ....');*/null;
763
764
765 UPDATE ap_payment_history_all aph
766 SET accounting_date = c_sweep_to_date,
767 last_update_date = sysdate,
768 last_updated_by = 5
769 WHERE nvl(aph.posted_flag, 'N') IN ('N','S')
770
771
772 AND ((p_period_name is null and accounting_date between p_from_acctg_date
773 and p_to_acctg_date)
774 OR
775 (p_period_name is not null and accounting_date between c_from_acctg_date
776 and c_to_acctg_date))
777 AND nvl(aph.org_id, -99) IN
778 (select nvl(asp.org_id, -99)
779 from hr_organization_information oi,
780 hr_all_organization_units_tl le,
781 hr_all_organization_units_tl ou,
782 ap_system_parameters_all asp
783 where
784 asp.org_id = oi.organization_id
785 and ou.organization_id = oi.organization_id
786 and oi.org_information_context =
787 'Operating Unit Information'
788 and DECODE(LTRIM(oi.org_information3,'0123456789'), NULL
789 , TO_NUMBER(oi.org_information3) , NULL ) =
790 p_set_of_books_id
791 and DECODE(LTRIM(oi.org_information2,'0123456789'), NULL
792 , TO_NUMBER(oi.org_information2), NULL) =
793 le.organization_id
794 and ou.organization_id = oi.organization_id
795 and ou.language = USERENV('LANG')
796 and le.language = USERENV('LANG'));
797
798 /*srw.message('15', 'Done updating payment history records.');*/null;
799
800
801
802 AP_ACCOUNTING_EVENTS_PKG.MULTI_ORG_EVENTS_SWEEP
803 (
804 p_ledger_id => p_set_of_books_id,
805 p_period_name => p_period_name,
806 p_from_date => p_from_acctg_date,
807 p_to_date => p_to_acctg_date,
808 p_sweep_to_date => c_sweep_to_date,
809 p_calling_sequence => 'APXTRSWP.rdf (update_acctg_dates() )'
810 );
811
812 /*srw.message('16', 'Done updating accoutning events records.');*/null;
813
814
815 else
816
817 /*srw.message('10', 'Updating invoice distributions....');*/null;
818
819
820 OPEN DIST_ORG;
821
822 LOOP
823
824 FETCH DIST_ORG INTO l_invoice_id,
825 l_invoice_distribution_id;
826
827 EXIT WHEN DIST_ORG%NOTFOUND OR DIST_ORG%NOTFOUND IS NULL;
828
829 UPDATE ap_invoice_distributions_all aid
830 SET accounting_date = c_sweep_to_date,
831 period_name = p_to_period,
832 last_update_date = sysdate,
833 last_updated_by = 5
834 WHERE aid.invoice_distribution_id = l_invoice_distribution_id;
835
836 AP_DBI_PKG.Maintain_DBI_Summary
837 (p_table_name => 'AP_INVOICE_DISTRIBUTIONS',
838 p_operation => 'U',
839 p_key_value1 => l_invoice_id,
840 p_key_value2 => l_invoice_distribution_id,
841 p_calling_sequence => 'APXTRSWP');
842
843
844 END LOOP;
845
846 /*srw.message('11', 'Done updating invoice distributions.');*/null;
847
848 /*srw.message('12', 'Updating invoice payments....');*/null;
849
850
851 UPDATE ap_invoice_payments_all aip
852 SET accounting_date = c_sweep_to_date,
853 period_name = p_to_period,
854 last_update_date = sysdate,
855 last_updated_by = 5
856 WHERE posted_flag IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
857 and p_to_acctg_date)
858 OR (p_period_name is not null and period_name = p_period_name));
859
860 /*srw.message('13', 'Done updating invoice payments.');*/null;
861
862 /*srw.message('14', 'Updating payment history records ....');*/null;
863
864
865 UPDATE ap_payment_history_all aph
866 SET accounting_date = c_sweep_to_date,
867 last_update_date = sysdate,
868 last_updated_by = 5
869 WHERE nvl(aph.posted_flag, 'N') IN ('N','S') AND ((p_period_name is null and accounting_date between p_from_acctg_date
870 and p_to_acctg_date)
871 OR
872 (p_period_name is not null and accounting_date between c_from_acctg_date
873 and c_to_acctg_date));
874
875
876 AP_ACCOUNTING_EVENTS_PKG.SINGLE_ORG_EVENTS_SWEEP
877 (
878 p_period_name => p_period_name,
879 p_from_date => p_from_acctg_date,
880 p_to_date => p_to_acctg_date,
881 p_sweep_to_date => c_sweep_to_date,
882 p_calling_sequence => 'APXTRSWP.rdf (update_acctg_dates() )'
883 );
884
885 end if;
886
887 RETURN (TRUE);
888
889
890 Exception
891 WHEN OTHERS THEN
892 RETURN (FALSE);
893 END;
894
895 FUNCTION GET_ACCTG_DATE RETURN BOOLEAN IS
896 l_to_acctg_date date;
897 l_start_date date;
898 l_end_date date;
899 BEGIN
900
901 if (p_sweep_now_1 = 'Y') then
902
903
904 /*srw.message('1', 'Sweep now is: '||p_sweep_now);*/null;
905
906 /*srw.message('2', 'Sweep to Period is: '||p_to_period);*/null;
907
908 SELECT start_date
909 INTO l_to_acctg_date
910 FROM gl_period_statuses
911 WHERE period_name = p_to_period
912 AND application_id = 200
913 AND set_of_books_id = p_set_of_books_id
914 AND nvl(adjustment_period_flag, 'N') = 'N';
915
916 c_sweep_to_date := l_to_acctg_date;
917 /*srw.message('3', 'sweep to date is: '||c_sweep_to_date);*/null;
918
919
920
921 end if;
922
923
924
925 if (p_period_name is not null) then
926 SELECT start_date,
927 end_date
928 INTO l_start_date,
929 l_end_date
930 FROM gl_period_statuses
931 WHERE period_name = p_period_name
932 AND application_id = 200
933 AND set_of_books_id = p_set_of_books_id
934 AND nvl(adjustment_period_flag, 'N') = 'N';
935
936 c_from_acctg_date := l_start_date;
937 c_to_acctg_date := l_end_date;
938 end if;
939
940 RETURN (TRUE);
941
942 RETURN NULL; EXCEPTION
943
944 WHEN OTHERS THEN
945 RETURN (FALSE);
946
947 END;
948
949 function AfterPForm return boolean is
950 begin
951 XLA_MO_REPORTING_API.Initialize(p_reporting_level,p_reporting_entity_id,'AUTO');
952 p_level_name := XLA_MO_REPORTING_API.Get_Reporting_level_name;
953 p_entity_name := XLA_MO_REPORTING_API.Get_Reporting_entity_name;
954 p_ac_org_where := XLA_MO_REPORTING_API.Get_Predicate('ac', null);
955 p_aid_org_where := XLA_MO_REPORTING_API.Get_Predicate('aid', null);
956 p_aip_org_where := XLA_MO_REPORTING_API.Get_Predicate('aip', null);
957 p_aph_org_where := XLA_MO_REPORTING_API.Get_Predicate('aph', null);
958 return (TRUE);
959 end;
960
961 FUNCTION GET_ACCRUAL_BASIS RETURN BOOLEAN IS
962 l_cash_basis_flag varchar2(1);
963 BEGIN
964
965 SELECT nvl(sla_ledger_cash_basis_flag, 'N')
966 INTO l_cash_basis_flag
967 FROM gl_sets_of_books
968 WHERE set_of_books_id = p_set_of_books_id;
969
970 if (l_cash_basis_flag = 'N') then
971 c_accrual_basis_in_use := 'Y';
972 else
973 c_accrual_basis_in_use := 'N';
974 end if;
975 END;
976
977 FUNCTION get_filtered_dates RETURN BOOLEAN IS
978 BEGIN
979 IF P_period_name IS NOT NULL THEN
980
981 C_date_filter_inv := ' and aid.period_name = ''' || p_period_name || '''';
982 C_date_filter_pay := ' and aip.period_name = ''' || p_period_name || '''';
983 C_date_filter_payhist := ' and aph.accounting_date between to_date('''
984 || fnd_date.date_to_canonical(C_from_acctg_date) ||''', ''YYYY/MM/DD HH24:MI:SS'')'
985 || ' and to_date(''' || fnd_date.date_to_canonical(C_to_acctg_date)
986 ||''', ''YYYY/MM/DD HH24:MI:SS'')';
987
988 ELSIF (P_from_acctg_date is NOT NULL and P_to_acctg_date is NOT NULL) THEN
989
990 C_date_filter_inv :=
991 ' and aid.accounting_date between to_date(''' || fnd_date.date_to_canonical(P_from_acctg_date)
992 || ''', ''YYYY/MM/DD HH24:MI:SS'') and to_date('''
993 || fnd_date.date_to_canonical(P_to_acctg_date) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
994
995 C_date_filter_pay :=
996 ' and aip.accounting_date between to_date(''' || fnd_date.date_to_canonical(P_from_acctg_date)
997 || ''', ''YYYY/MM/DD HH24:MI:SS'') and to_date('''
998 || fnd_date.date_to_canonical(P_to_acctg_date) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
999
1000 C_date_filter_payhist :=
1001 ' and aph.accounting_date between to_date(''' || fnd_date.date_to_canonical(P_from_acctg_date)
1002 || ''', ''YYYY/MM/DD HH24:MI:SS'') and to_date('''
1003 || fnd_date.date_to_canonical(P_to_acctg_date) || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1004
1005 ELSIF (P_to_acctg_date is NOT NULL and P_from_acctg_date is NULL) THEN
1006
1007 C_date_filter_inv := ' and aid.accounting_date <= to_date('''
1008 || fnd_date.date_to_canonical(P_to_acctg_date)
1009 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1010 C_date_filter_pay := ' and aip.accounting_date <= to_date('''
1011 || fnd_date.date_to_canonical(P_to_acctg_date)
1012 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1013 C_date_filter_payhist := ' and aph.accounting_date <= to_date('''
1014 || fnd_date.date_to_canonical(P_to_acctg_date)
1015 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1016
1017 ELSIF (P_to_acctg_date is NULL and P_from_acctg_date is NOT NULL) THEN
1018
1019 C_date_filter_inv := ' and aid.accounting_date >= to_date('''
1020 || fnd_date.date_to_canonical(P_from_acctg_date)
1021 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1022 C_date_filter_pay := ' and aip.accounting_date >= to_date('''
1023 || fnd_date.date_to_canonical(P_from_acctg_date)
1024 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1025 C_date_filter_payhist := ' and aph.accounting_date >= to_date('''
1026 || fnd_date.date_to_canonical(P_from_acctg_date)
1027 || ''', ''YYYY/MM/DD HH24:MI:SS'')';
1028 else
1029 C_DATE_FILTER_PAY := 'AND 1 = 1 ';
1030 C_DATE_FILTER_PAYHIST := 'AND 1 = 1 ';
1031 C_DATE_FILTER_INV := 'AND 1 = 1 ';
1032
1033 END IF;
1034
1035
1036 RETURN (TRUE);
1037
1038 RETURN NULL; EXCEPTION
1039
1040 WHEN OTHERS THEN
1041 RETURN (FALSE);
1042
1043
1044
1045
1046 END;
1047
1048 function g_1_orgsgroupfilter(CO_org_id in number) return boolean is
1049 cui_counter number := 0;
1050 cup_counter number := 0;
1051 cut_counter number := 0;
1052 cfp_counter number := 0;
1053 cpb_counter number := 0;
1054
1055 BEGIN
1056 IF p_sweep_now_1 = 'Y' OR p_unacct_rpt = 'Y' THEN RETURN(FALSE);
1057 ELSE SELECT COUNT(*)
1058 INTO cui_counter
1059 FROM ap_invoice_distributions_all
1060 WHERE accounting_date BETWEEN p_start_date AND p_end_date
1061 AND accrual_posted_flag = 'N'
1062 AND p_acct_method = 'A'
1063 AND org_id = CO_org_id
1064 AND rownum = 1;
1065
1066 SELECT COUNT(*)
1067 INTO cup_counter
1068 FROM ap_invoice_payments_all
1069 WHERE accounting_date BETWEEN p_start_date AND p_end_date
1070 AND posted_flag IN ('N','S') AND org_id = co_org_id
1071 AND rownum = 1;
1072
1073 SELECT COUNT(*)
1074 INTO cut_counter
1075 FROM xla_ae_headers xah, xla_transaction_entities xte
1076 WHERE xah.accounting_date BETWEEN p_start_date AND p_end_date
1077 AND xah.gl_transfer_status_code = 'N'
1078 AND xah.entity_id = xte.entity_id
1079 AND xte.security_id_int_1 = co_org_id
1080 AND rownum = 1;
1081
1082 SELECT COUNT(*)
1083 INTO cfp_counter
1084 FROM ap_checks_all
1085 WHERE future_pay_due_date IS NOT NULL
1086 AND status_lookup_code = 'ISSUED'
1087 AND future_pay_due_date BETWEEN p_start_date AND p_end_date
1088 AND org_id = co_org_id
1089 AND rownum = 1;
1090
1091 SELECT COUNT(*)
1092 INTO cpb_counter
1093 FROM ap_inv_selection_criteria_all
1094 WHERE check_date BETWEEN p_start_date AND p_end_date
1095 AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
1096 AND org_id = co_org_id
1097 AND rownum = 1;
1098
1099 IF cui_counter + cup_counter + cut_counter + cfp_counter + cpb_counter = 0
1100 THEN RETURN(FALSE);
1101 ELSE RETURN(TRUE);
1102 END IF;
1103 END IF;
1104
1105 END;
1106
1107 --Functions to refer Oracle report placeholders--
1108
1109 Function C_NLS_YES_p return varchar2 is
1110 Begin
1111 return C_NLS_YES;
1112 END;
1113 Function C_NLS_NO_p return varchar2 is
1114 Begin
1115 return C_NLS_NO;
1116 END;
1117 Function C_NLS_ALL_p return varchar2 is
1118 Begin
1119 return C_NLS_ALL;
1120 END;
1121 Function C_NLS_NO_DATA_EXISTS_p return varchar2 is
1122 Begin
1123 return C_NLS_NO_DATA_EXISTS;
1124 END;
1125 Function C_NLS_VOID_p return varchar2 is
1126 Begin
1127 return C_NLS_VOID;
1128 END;
1129 Function C_NLS_NA_p return varchar2 is
1130 Begin
1131 return C_NLS_NA;
1132 END;
1133 Function C_NLS_END_OF_REPORT_p return varchar2 is
1134 Begin
1135 return C_NLS_END_OF_REPORT;
1136 END;
1137 Function C_REPORT_START_DATE_p return date is
1138 Begin
1139 return C_REPORT_START_DATE;
1140 END;
1141 Function C_COMPANY_NAME_HEADER_p return varchar2 is
1142 Begin
1143 return C_COMPANY_NAME_HEADER;
1144 END;
1145 Function C_BASE_CURRENCY_CODE_p return varchar2 is
1146 Begin
1147 return C_BASE_CURRENCY_CODE;
1148 END;
1149 Function C_BASE_PRECISION_p return number is
1150 Begin
1151 return C_BASE_PRECISION;
1152 END;
1153 Function C_BASE_MIN_ACCT_UNIT_p return number is
1154 Begin
1155 return C_BASE_MIN_ACCT_UNIT;
1156 END;
1157 Function C_BASE_DESCRIPTION_p return varchar2 is
1158 Begin
1159 return C_BASE_DESCRIPTION;
1160 END;
1161 Function C_CHART_OF_ACCOUNTS_ID_p return number is
1162 Begin
1163 return C_CHART_OF_ACCOUNTS_ID;
1164 END;
1165 --Function Applications Template Report_p return varchar2 is
1166 Function Applications_Template_Report_p return varchar2 is
1167 Begin
1168 --return Applications Template Report;
1169 return Applications_Template_Report;
1170 END;
1171 Function C_AIP_MULTI_ORG_WHERE_p return varchar2 is
1172 Begin
1173 return C_AIP_MULTI_ORG_WHERE;
1174 END;
1175 Function C_APH_MULTI_ORG_WHERE_p return varchar2 is
1176 Begin
1177 return C_APH_MULTI_ORG_WHERE;
1178 END;
1179 Function C_PMTS_MULTI_ORG_WHERE_p return varchar2 is
1180 Begin
1181 return C_PMTS_MULTI_ORG_WHERE;
1182 END;
1183 Function C_INV_MULTI_ORG_WHERE_p return varchar2 is
1184 Begin
1185 return C_INV_MULTI_ORG_WHERE;
1186 END;
1187 Function C_ORG_FROM_TABLES_p return varchar2 is
1188 Begin
1189 return C_ORG_FROM_TABLES;
1190 END;
1191 Function C_SELECT_LE_p return varchar2 is
1192 Begin
1193 return C_SELECT_LE;
1194 END;
1195 Function C_SELECT_OU_p return varchar2 is
1196 Begin
1197 return C_SELECT_OU;
1198 END;
1199 Function C_REP_TITLE_p return varchar2 is
1200 Begin
1201 return C_REP_TITLE;
1202 END;
1203 Function C_SWEEP_TO_DATE_p return date is
1204 Begin
1205 return C_SWEEP_TO_DATE;
1206 END;
1207 Function C_ACCRUAL_BASIS_IN_USE_p return varchar2 is
1208 Begin
1209 return C_ACCRUAL_BASIS_IN_USE;
1210 END;
1211 Function C_FROM_ACCTG_DATE_p return date is
1212 Begin
1213 return C_FROM_ACCTG_DATE;
1214 END;
1215 Function C_TO_ACCTG_DATE_p return date is
1216 Begin
1217 return C_TO_ACCTG_DATE;
1218 END;
1219 Function C_APH_ACCTG_DATE_p return varchar2 is
1220 Begin
1221 return C_APH_ACCTG_DATE;
1222 END;
1223 Function C_SWEEP_NOW_p return varchar2 is
1224 Begin
1225 return C_SWEEP_NOW;
1226 END;
1227 Function C_DATE_FILTER_INV_p return varchar2 is
1228 Begin
1229 return C_DATE_FILTER_INV;
1230 END;
1231 Function C_DATE_FILTER_PAY_p return varchar2 is
1232 Begin
1233 return C_DATE_FILTER_PAY;
1234 END;
1235 Function C_DATE_FILTER_PAYHIST_p return varchar2 is
1236 Begin
1237 return C_DATE_FILTER_PAYHIST;
1238 END;
1239 Function C_LEDGER_PARTIAL_OU_p return varchar2 is
1240 Begin
1241 return C_LEDGER_PARTIAL_OU;
1242 END;
1243 function F_END_OF_REPORT1FormatTrigger return number is
1244 cui_counter number := 0;
1245 cup_counter number := 0;
1246 cut_counter number := 0;
1247 cfp_counter number := 0;
1248 cpb_counter number := 0;
1249
1250 BEGIN
1251 IF P_SWEEP_NOW_1 <> 'Y' then
1252 SELECT COUNT(*)
1253 INTO cui_counter
1254 FROM ap_invoice_distributions_all
1255 WHERE accounting_date BETWEEN p_start_date AND p_end_date
1256 AND accrual_posted_flag = 'N'
1257 AND p_acct_method = 'A'
1258 AND set_of_books_id = p_set_of_books_id
1259 AND rownum = 1;
1260
1261 SELECT COUNT(*)
1262 INTO cup_counter
1263 FROM ap_invoice_payments_all
1264 WHERE accounting_date BETWEEN p_start_date AND p_end_date
1265 AND posted_flag IN ('N','S') --Bug 3476167
1266 AND set_of_books_id = p_set_of_books_id
1267 AND rownum = 1;
1268
1269 SELECT COUNT(*)
1270 INTO cut_counter
1271 FROM xla_ae_headers --Bug 3739324
1272 WHERE accounting_date BETWEEN p_start_date AND p_end_date
1273 AND gl_transfer_status_code = 'N'
1274 AND ledger_id = p_set_of_books_id
1275 AND rownum = 1;
1276
1277 SELECT COUNT(*)
1278 INTO cfp_counter
1279 FROM ap_checks_all AC, hr_operating_units HOU
1280 WHERE AC.org_id = HOU.organization_id
1281 AND AC.future_pay_due_date IS NOT NULL
1282 AND AC.status_lookup_code = 'ISSUED'
1283 AND AC.future_pay_due_date BETWEEN p_start_date AND p_end_date
1284 AND HOU.set_of_books_id = to_char(p_set_of_books_id) --Bug 2986690
1285 AND rownum = 1;
1286
1287 SELECT COUNT(*)
1288 INTO cpb_counter
1289 FROM ap_inv_selection_criteria_all AIS, hr_operating_units HOU
1290 WHERE AIS.org_id = HOU.organization_id
1291 AND AIS.check_date BETWEEN p_start_date AND p_end_date
1292 AND status NOT IN ('CONFIRMED', 'CANCELED', 'QUICKCHECK')
1293 AND HOU.set_of_books_id = to_char(p_set_of_books_id) --Bug 2986690
1294 AND rownum = 1;
1295
1296
1297 RETURN(cui_counter + cup_counter + cut_counter + cfp_counter + cpb_counter);
1298
1299
1300 END IF;
1301
1302 END;
1303 END AP_APXTRSWP_XMLP_PKG ;
1304
1305