AR Period Cannot Be Closed Even The Unposted Items Report Is Empty

Oracle Receivables 11.5.10.2

ARXSUMPS - Open/Close Accounting Periods
fact: System Option: Accounting Method "Cash Basis"
symptom: APP-AR-11332 You must post all transactions in this period before you close it
symptom: Period cannot be closed in AR
symptom: There are no unposted items on the Unposted Items report.
cause: This could be Bug 2189383:

If you are using "Cash Basis" accounting deletion of zero amount
receipts causes orphan records in table AR_MISC_CASH_DISTRIBUTIONS.
These records remain unposted and you are not able to close the period.

Please run following script to verify this issue:

select org_id,cash_receipt_id,amount,gl_date,posting_control_id pid
from ar_misc_cash_distributions_all dist
where not exists
(select 'x' from ar_cash_receipts_all cash
where cash.cash_receipt_id=dist.cash_receipt_id)
/
fix:

This is fixed in ARREMT2B.pls-115.8 included in Minipack AR.J (2182030).

There are two standalone patches available:
2202643 can be applied on AR.D.
2200713 can be applied on AR.H.

To obtain a datafix please log an iTar with Oracle Support including the output
of the above script.

 

For this issue, I submit a SR:7529606.992 Cann't close the period Feb-09 in Receivables module

(1) Oracle support provide sql script to analyse this issue

 

i) select org_id,adjustment_id,gl_date,posting_control_id,postable,
status from ar_adjustments_all
where gl_date between '&start' and '&end'
and status = 'A'
and posting_control_id = -3;

ii)
select cash_receipt_id,gl_date,status,amount_applied,
application_type from ar_receivable_applications_all
where gl_date between '&start' and '&end'
and application_type = 'CASH' and posting_control_id= -3;----return some records

iii)
select org_id,gl_date,status,amount_applied,application_type,
customer_trx_id from ar_receivable_applications_all
where gl_date between '&start' and '&end'
and application_type = 'CM' and posting_control_id = -3
and postable ^= 'N' order by customer_trx_id,org_id;
iv)
select
org_id,cash_receipt_id,cash_receipt_history_id,status,amount,
gl_date,reversal_gl_date,posting_control_id,
reversal_posting_control_id from ar_cash_receipt_history_all
where posting_control_id = -3
and gl_date between '&start' and '&end';

v)select org_id,cash_receipt_id,amount,gl_date,posting_control_id
from ar_misc_cash_distributions_all
where gl_date between '&start' and '&end'
and posting_control_id = -3;

vi)
select a.customer_trx_id,a.amount,a.gl_date,a.account_class,a.
latest_rec_flag,account_set_flag
from ra_cust_trx_line_gl_dist_all a, ra_customer_trx_all b,
ra_cust_trx_types_all c
where a.gl_date between '&start' and '&end''
and a.posting_control_id = -3
and ((a.account_class = 'REC'
and a.latest_rec_flag = 'Y')
or
(a.account_class != 'REC' and
a.account_set_flag = 'N'))
and a.customer_trx_id = b.customer_trx_id
and a.org_id = b.org_id
and b.complete_flag = 'Y'
and b.cust_trx_type_id = c.cust_trx_type_id
and a.org_id = b.org_id
and c.POST_TO_GL = 'Y'
order by a.customer_trx_id;

(2)It looks like you have few Receipts which are not posted

 

(3)So, this script will fix the issue with two application rows having issue in Cash Receipt Id
10708 / Receipt Number 7740.

1)On the TEST instance where the script was executed with Read Only Mode : Y , Please execute the script
"create_dists_for_ra.sql" with below parameters :
Read Only Mode : N
l_gl_date_low date : 01-FEB-2009
l_gl_date_high date :28-FEB-2009

2)Issue a commit.
3)Please execute AR to GL posting for Feb 2009 and upload the output and Log file.
4)From the same instance, Please also upload the latest receipt diagnostics of :
Cash Receipt Id 10708 , Cash Receipt Id 10739

(4)The receipt Cash Receipt Id 10708 /Receipt Number 7740 is now posted successfully to GL.
Please provide result of below query :
select * from AR_RECEIVABLE_APPLICATIONS_ALL where CASH_RECEIPT_ID ='10739' and POSTING_CONTROL_ID='-3';

(5)Please provide result of below query :

select cash_receipt_id, receivable_application_id
from ar_receivable_applications_all
where gl_date between l_gl_date_low and l_gl_date_high
and nvl(postable,'Y') = 'Y'
and not exists (select line_id
from ar_distributions_all
where source_id = receivable_application_id
and source_table ='RA');

(6)Please go ahead and execute the script for Read Only Mode : N and then run AR to GL transfer program.

(7)I can close period 'Feb-09' successfully in TEST Server now.

NOTE:Lucy's solution [I have created four records on table ar_distributions_all on our test server for missing records I mentioned before, then transferred them to GL, and now AR period Mar-07 has been closed successfully. ]

 

附上create_dists_for_ra.sql

set serverout on size 1000000
set lines 200
PROMPT
PROMPT Choose the directory from utl_file_dir where debug information should be written.
PROMPT
SELECT value utl_file_dir FROM v$parameter WHERE UPPER(name)='UTL_FILE_DIR' ;
define out_dir_usr = '&Out_Dir_name'
PROMPT
PROMPT Enter the output file name
PROMPT
define  out_file = '&Out_File'
PROMPT

DECLARE
  l_out_file                    varchar2(512):= ('&out_file');
  l_out_dir                     varchar2(512) ;
  l_out_dir_usr                 varchar2(512) := ('&out_dir_usr');
  l_org_id number := &org_id;
  l_posted_count    number;
  l_read_only_mode  varchar2(1) := '&read_only_mode';
  l_gl_date_low date := to_date('&gl_date_low','DD-MON-YYYY');
  l_gl_date_high date := to_date('&gl_date_high','DD-MON-YYYY');

  cursor c1 is
  select cash_receipt_id, receivable_application_id
  from ar_receivable_applications
  where gl_date between l_gl_date_low and l_gl_date_high
    and nvl(postable,'Y') = 'Y'
    and not exists (select line_id
                    from ar_distributions
                    where source_id = receivable_application_id
                     and  source_table ='RA');
BEGIN

  dbms_output.put_line('Start processing...');

    fnd_client_info.set_org_context(l_org_id);

   select value
   into l_out_dir
   from v$parameter
   where upper(name) ='UTL_FILE_DIR';

   IF (instr(l_out_dir,l_out_dir_usr) = 0 AND l_out_dir_usr IS NOT NULL )
     OR l_out_dir_usr IS NULL THEN
     l_out_dir_usr := substr(l_out_dir,1,instr(l_out_dir,',')-1);
     dbms_output.put_line('The entered directory can not be used');
     dbms_output.put_line('The output will be written to '||l_out_dir_usr);
     dbms_output.put_line('                            ');
   END IF;
   IF l_out_file is null then
     l_out_file := '5721575.out';
     dbms_output.put_line('The output is available in 5721575.out file ');
     dbms_output.put_line('                            ');
   END IF;

   arp_standard.enable_debug;
   arp_standard.enable_file_debug(l_out_dir_usr,l_out_file);

   FOR i in C1 loop

   --Make sure all the records in the distributions are unposted
        select count(*)
        into l_posted_count
        from ar_receivable_applications
        where receivable_application_id = i.receivable_application_id
        and posting_control_id <> -3;

        --If there are no posted records then we can update the distributions

        IF l_posted_count = 0
 THEN
             dbms_output.put_line('Processing receipt_application: '||i.receivable_application_id);

            IF upper(nvl(l_read_only_mode,'Y')) = 'N' THEN
       arp_acct_main.create_Acct_entry('RECEIPT', null, 'ONE', 'RA',
                               i.receivable_application_id, null, null, 'Y', 'C');

       dbms_output.put_line('Fixed the receipt application : '||i.receivable_application_id);

             ELSE
                 dbms_output.put_line('Script can fix : '||i.receivable_application_id);
             END IF;
     ELSE
           dbms_output.put_line('Posted record exists, cannot fix it : '||i.receivable_application_id);
  END IF;
   END LOOP;
END;
/