Monthly Archives: June 2006

Correcting the Transaction Detail Report (XML Publisher)

Submitting the Transaction Detail Report to Run

  1. Log on as user sysadmin on TEST (illuminat on PROD).
  1. Choose Receivables Superuser responsibility (Receivables Supervisor on PROD)
  1. Choose Requests Submit, and then Single Request.
  1. Enter tr%det% in the Name field and press tab. The report ‘Transaction Detail Report appears in the field.
  1. Enter Transaction Number Low: 10050 Transaction Number High: 10060
  1. Under Upon Completion, click the Options button.
  1. Under Template Name, change FORMAT2 to TEMP. Click OK.
  1. Click Submit.
  1. The report is submitted. Click the Refresh Data button continuously to update the display.

Template Location

The XML template that is being updated is located in the Public folder (project_folder) on x.x.x.x. If you cannot log on to this server, use applmgr/applmgr as the username and password.

Navigate to the ARSetupXML folder. The report that is being modified is the Transaction Detail Report.rtf. This file was copied to TDR1 VS.rtf, which is being modified.

Registering the Template

To register the RTF file as an XML template in Oracle Applications (OA), you must set up a Data Definition and then a Template. To set up a Data Definition:

  1. Log on as user sysadmin on TEST
  1. Choose XML Publisher Administrator
  1. Navigate to Data Definitions.
  1. To list the current data definitions, under Search, enter Receivables in the Application field, and then click Go.
  1. To enter a new Data Definition, click the Create Data Definitions button.
  1. The four mandatory fields are Name, Application, Code and Start Date.
Name Enter any name you choose
Application Receivables
Code Any name you choose
Start Date Today’s Date
  1. On completion, click Apply.

Next, a template must be set up. To create a new template, follow these steps:

  1. Click on Templates, and then click Create Template.
  1. The six mandatory fields are Name, Application, Type, Code, Data Definition and Start Date.
Name Enter any name you choose
Application Receivables
Type RTF
Code Any name you choose
Data Definition The name of the data definition enter in the above steps
Start Date Today’s Date
  1. A Template File can be entered now. All fields are mandatory:
File Click the Browse button and choose ARSetupXML TDR1 VS.rtf from the Public folder
Language English
Territory United States

Updating the Registered Template

  1. In XML Publisher Administrator, click on Templates.
  1. Enter ‘Receivables’ in the Application field.
  1. Click Go.
  1. The list of templates registered under Receivables is displayed. Click on TEMP.
  1. To update the template after any changes were made, click on the Update template icon (the Pencil) under Update.
  1. Click Browse and choose ARSetupXML TDR1 VS.rtf from the Public folder. Then click Apply.

Disabling a Template

To disable a template, set the End Date to a date (such as the Start Date). To restore the template, list all template where ACTIVE = NO. Then remove the End Date.

Troubleshooting Method

When the report was submitted, it completed with a warning. The message in the log stated:

+————- 1) PUBLISH ————-+

Beginning post-processing of request 264770 on node ERPTEST at 29-MAY-2006 10:34:20.

Post-processing of request 264770 failed at 29-MAY-2006 10:34:20 with the error message:

One or more post-processing actions failed. Consult the OPP service log for details.

+————————————–+

To view the OPP service log, OPP must be accessed from the Oracle Applications Manager.

  1. Logon to Oracle Applications Manager in TEST.
  1. Ensure that Application Services is selected, and then click Go.
  1. Click Output Post Processor.
  1. Click Output Post Processor again.
  1. Click Log.

This is the OPP service log. The following was noted:

Caused by: oracle.xdo.parser.v2.XPathException: Unknown expression at EOF: .//for each:G_LINES.

at oracle.xdo.parser.v2.XSLProcessor.reportException(XSLProcessor.java:782)

at oracle.xdo.parser.v2.XSLProcessor.newXSLStylesheet(XSLProcessor.java:564)

… 15 more

From this log, there was an error with G_LINES. It should have been for‑each:G_LINES. Therefore, this would be the starting point to resolve this problem.

To fix this file, a working XML template file, “Revised UTT Invoice V3 (file 1).rtf”, was opened and compared to the “TDR1 VS.rtf” file (file 2). The tags were meticulously compared, together with the XML source, “XML Transaction Detail Report.xml”.

Changes Made

  1. The group tags were checked to ensure that they had ‘for-each’ and not ‘for each’.
  1. The header consisted of an images and text grouped together. This was replaced with a single header image (as file 1 had it).
  1. The text was being shifted in the output report. Tables were used instead of tabs to keep the text formatted.

Refer to the Oracle® XML Publisher User’s Guide, which is located in the Public folder, under the “User Manualsxml” folder in the file XML Publisher.pdf.

Configuring NetMeeting

Step 1:        From the Command Prompt or the Run dialog box, enter conf.

Step 2:        The Windows NetMeeting loads.

Step 3:        Enter the user’s first name, last name and email address. Under the Security tab, check both checkboxes to encrypt incoming and outgoing calls.

Step 4:        To dial in, click the Place Call button. In the Place a Call dialog box, enter the server to call in the To field. This is either oracleserver or oraclesrv.

Step 5:        Enter the following information:

Username: applmgr
Password: applmgr
Domain: domainname

Reconciliation Open Interface

On Test environment:

Prerequisites to Using the Reconciliation Open Interface

  • Check the Use Reconciliation Open Interface check box in the System Parameters window. Setting Up 2 – 35
  • Define Open Interface Float Status and Open Interface Cleared Status in system parameters.
  • Customize the Reconciliation Open Interface package and view. The Reconciliation Open Interface objects include the following view and package, which you would have customized to work in your environment:
  • CE_999_INTERFACE_V (view) – cev999in.sql (physical file)
  • CE_999_PKG (package) – ceab999s.pls (physical file) – ceab999b.pls (physical file)

To enable the Use Reconciliation Open Interface checkbox:

  1. Choose Cash Management Superuser responsibility
  1. From the Navigator, choose Setup > System > System Parameters
  1. Check the Use Reconciliation Open Interface checkbox
  1. Click the Open Interface tab. Under Open Interface Status Code, enter into the following fields:
    • Float Status: Negotiable
    • Clear Status: Reconciled

Most likely, the CE_999_INTERFACE_V view already exists within the system. However, the CE_999_INTERFACE table does NOT exist. It must be created as follows:

CREATE TABLE CE.CE_999_INTERFACE AS SELECT * FROM APPS.CE_999_INTERFACE_V;
CREATE VIEW CE.CE_999_INTERFACE_V AS SELECT * FROM CE.CE_999_INTERFACE;
CREATE SYNONYM APPS.CE_999_INTERFACE FOR CE.CE_999_INTERFACE;
CREATE SYNONYM APPS.CE_999_INTERFACE_V FOR CE.CE_999_INTERFACE_V;

Then grant privileges on the table CE_999_INTERFACE:

Grant select, insert, update, delete, index, references, alter on CE_999_INTERFACE to public;
col grantee format a8
col grantor format a8
select * from dba_tab_privs
where table_name = 'CE_999_INTERFACE';

You may be required to rename OR delete the view in APPS with the same name:

RENAME CE_999_INTERFACE_V TO CE_999_INTERFACE_VS;
DROP VIEW APPS.CE_999_INTERFACE_V;

To validate views with status ‘INVALID’:

ALTER VIEW CE_999_RECONCILED_V COMPILE;
ALTER VIEW CE_999_REVERSAL_V COMPILE;

In order to recreate the original view APPS.CE_999_INTERFACE_V, see the file $CE_TOP/admin/odf/cevw.odf. You may be required to reconstruct the SQL code from that given in this file.

The CE_999_INTERFACE table seems to have been deleted. The script that was used to create the table must be rerun. To find this table, check the Stage11510 folder to locate the file:

mkdir ~/ce
for i in `find /emcb/Stage11510/oraApps/ -iname 'ce*'`; do cp $i ~/ce/; done
  1. Cash Management Superuser
  1. View > Available Transactions
  1. Bank Statements > Manual Clearing > Clearing Transactions
  1. In the Find Transactions form, choose an Account Number for Recurrent Account (1429625Rec).

On Production:

  1. Cash Management Manager
  1. View > Available Transactions
  1. Bank Statements > Manual Clearing > Clear Transactions

The CE_999_INTERFACE_V view should already have been created in the database when the database was set up.

To see the contents of the DBA_OBJECTS view:

Col owner format a6
Col object_name format a30
SELECT
OWNER,
OBJECT_NAME,
OBJECT_TYPE,
CREATED,
STATUS
FROM
DBA_OBJECTS
WHERE
OBJECT_NAME LIKE ‘CE_999%’
;

To see the contents of the DBA_SYNONYMS view:

SET LINESIZE 150
SELECT
OWNER,
SYNONYM_NAME,
TABLE_OWNER,
TABLE_NAME
FROM
DBA_SYNONYMS
WHERE
SYNONYM_NAME LIKE ‘CE_999%’
;

To see the contents of the DBA_VIEWS view:

SET LONG 800
SET LINESIZE 800
SET LONGCHUNKSIZE 800
SET WRAP OFF
SELECT
OWNER,
VIEW_NAME,
TEXT
FROM
DBA_VIEWS
WHERE
VIEW_NAME = ‘CE_999_INTERFACE_V’
;

To troubleshoot any problems viewing data in the application, the CE_999_TRANSACTIONS_V view must be analyzed. Specifically, the WHERE clause must be examined to determine if all criteria are met.

One of the views in the FROM clause is the AP_BANK_ACCOUNTS. The AP_BANK_ACCOUNTS view needs the CLIENT_INFO variable set in SQL*Plus to view the data in this view. To view the value of this variable, enter:

SELECT USERENV (‘CLIENT_INFO’) FROM DUAL;
OR
SELECT SYS_CONTEXT ('USERENV', 'CLIENT_INFO') FROM DUAL;

To set this parameter, use the DBMS_APPLICATION_INFO package:

EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO (‘102’);

By removing all lines from the WHERE clause of then gradually adding them back one at a time in a SQL query, it was determined where the problem occurred. The last line of the WHERE clause was with the Transaction Date and only when this line was added was no rows returned.