创建科目失败解决

路径：应付款系统管理员/发票/发票

问题描述：

在AP发票界面录入一笔发票，验证发票，撤销发票，然后在AP发票界面的“活动”中创建会计科目，发现带出的并发请求“创建会计科目”正常跑完，但是通过“报表/查看会计科目”却无法查看该发票是否生成会计科目。

查询SQL:

SELECT xte.entity_id

      ,xe.event_id

      ,xe.transaction_date

      ,xe.event_status_code

      ,xe.process_status_code

      ,xe.event_type_code

      ,xah.accounting_entry_status_code --F表示还可再次创建会计科目 N表示不能再次创建会计科目

      ,xah.gl_transfer_status_code--N表示尚未传送至GL Y表示已经传送至GL，说明选择“最终过账”会运行传送至GL请求，将子分类帐账务分录传送至GL

FROM   xla.xla_transaction_entities xte

      ,xla.xla_events               xe

      ,xla_ae_headers               xah

      ,ap_invoices_all              aia

WHERE  xe.entity_id = xte.entity_id

AND    xte.application_id = xe.application_id

AND    xe.event_id = xah.event_id

AND    xe.application_id = xah.application_id

AND    nvl(xte.source_id_int_1

          ,-99) = aia.invoice_id

AND    xte.ledger_id = aia.set_of_books_id

AND    xte.entity_code = 'AP_INVOICES'

AND    aia.invoice_num = '';

发现xe.event_status_code和 xe.process_status_code分别为 I 和 U ，并且表xla.xla_ae_headers与xla.xla_ae_lines没有相关数据，所以确认没有生成会计分录，原因不明。

 

解决方法：

一：参照sr提供的方式操作

To implement the solution, please execute the following steps: 
1. Ensure that you have taken a backup of your system before applying the recommended solution. 
2. Run the following scripts in a TEST environment first: 
*********TEST INSTANCE FIRSTLY******** 
Step 1) Download and apply the data fix patch 19637587:R12.AP.A 
**Please ignore the first step in case you have applied this patch before ********** 
Step 2) Execute the selection script : AP_MISC_INVOICE_DIAG.sql 
sqlplus apps/apps 
@$AP_TOP/patch/115/sql/AP_MISC_INVOICE_DIAG.sql 
Parameters : ** invoice date (Format dd/mm/yyyy Eg. 28/01/1999) 
l_start_date 
l_end_date 
This script will create the temp DB table and log file for review. 
a) Driver table AP_TEMP_DATA_DRIVER_9738413 
b) log file 9738413-diag-<DD_MI_SS>.html which displays the affected transactions. 
Log files will be created in the following directory: 
SELECT decode(instr(value,','),0,value,SUBSTR (value,1,instr(value,',') - 1)) FROM v$parameter WHERE name = 'utl_file_dir'; 
Step 3) Update process_flag in each driver table AP_TEMP_DATA_DRIVER_9738413 to 'Y' for any transactions that should be operated on. 
update AP_TEMP_DATA_DRIVER_9738413 set process_flag = 'Y' where invoice_id in ('2007427'); 
commit;

Step 4) Execute the fix script 
Example: sqlplus apps/apps 
$AP_TOP/patch/115/sql/AP_MISC_INVOICE_FIX.sql 
This script expects the following parameters 
-p_user_name : Enter user name that has the required permissions. 
-p_resp_name : Enter responsibility that has required permissions. (Case sensitive) eg. Payables Manager 
-p_proposed_undo_date : New accounting date for undo accounting events. (DD/MM/YYYY) 
-p_email_id: Optional. Enter if you want to receive the generated logs on email id. 
Please verify the user name and responsibility name based on the SQL as below firstly, if return no record, that means the parameter is not correct. 
SELECT *FROM fnd_user WHERE user_name = Upper('&P_User_Name'); 
SELECT * FROM fnd_responsibility_tl WHERE responsibility_name = '&p_resp_name ' AND ROWNUM = 1; 
This script will generate log file 9738413-fix-<DD_MI_SS>.html 
Step 5) Check whether the data fix is working or not. 
-- process_flag : E (Error) ; D (Done / Finished) 
select invoice_id , process_flag from AP_TEMP_DATA_DRIVER_9738413 where invoice_id in ('2007427'); 
3. Once the scripts complete, confirm that the data is corrected. 
4. If you are satisfied with the results, issue a commit. 
5. Confirm that the data is corrected when viewed in the Oracle Applications. 
6. If you are satisfied that the issue is resolved, migrate the solution as appropriate to other environments.

 

二 重新对该发票“创建会计科目”

1 重新创建会计科目，报错如下，xe.event_status_code和 xe.process_status_code分别为 I 和 U ，但是表xla.xla_ae_headers与xla.xla_ae_lines有相关数据

 

2 按照报错提示，在AP职责下单独提交“创建会计科目”并发程序，并将“报表”参数设置为“详细信息”，然后下载“查看输出”文件，搜问题发票编号“D301ZTX2017110001”，

 

检查AP和GL的期间是否打开，发现GL的2017-11期间关闭，则打开GL期间，然后重新创建会计科目，创建成功

 

 

sr网址：

1）https://support.oracle.com/epmos/faces/SrDetail?_afrLoop=172711819808699&srNumber=3-16377513741&srDetailRelativeDateParam=null&queryModeName=Technical&needSrDetailRefresh=true&_afrWindowMode=0&_adf.ctrl-state=gih44hrzi_4

2）https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=174674419612511&parent=SrDetailText&sourceId=3-16377513741&id=1360390.1&_afrWindowMode=0&_adf.ctrl-state=gih44hrzi_21