The following is the Sample Code to Submit the Concurrent Programfrom the backend.

Note:- This is the Concurrent Program, not the RequestSet. To Submit the Request Set from the backend, We have different API.


DECLARE
l_success NUMBER;
BEGIN
BEGIN

fnd_global.apps_initialize( user_id=> 2572694, resp_id => 50407, resp_appl_id => 20003);

-- If you are directlyrunning from the database using the TOAD, SQL-NAVIGATOR or SQL*PLUS etc. Then you need to Initialize the Apps.
-- In thiscase use the above API to Initialize the APPS. If you are using same code insome procedure and running directly fromapplication then you don't need to initalize.
-- Thenyou can comment the above API.

l_success:=
fnd_request.submit_request
('XXAPP',-- Application Short name of the Concurrent Program.
'XXPRO_RPT', --Program Short Name.
'Program For testing the backendReport', -- Descriptionof the Program.
SYSDATE, --Submitted date. Always give the SYSDATE.
FALSE, --Always give the FLASE.
'1234' --Passing the Value to the First Parameter of the report.
);
COMMIT;

--Note:- In theabove request Run, I have created the Report, which has one parameter.

IFl_success = 0
THEN
-- fnd_file.put_line(fnd_file.LOG, 'Request submission For this store FAILED' );
DBMS_OUTPUT.PUT_LINE('Request submission For this store FAILED' );
ELSE
-- fnd_file.put_line(fnd_file.LOG, 'Request submission for this store SUCCESSFUL');
DBMS_OUTPUT.PUT_LINE('Request submission For this store SUCCESSFUL' );
ENDIF;

--Note:- If you arerunning directly from database, use DBMS API to display.
--If you are running directly fromApplication, then Use the fnd_file API to write themessage in the log file.

END;

PROCEDURE write(p_type IN VARCHAR2, p_message IN VARCHAR2)IS

/************************************************************************

Purpose : Procedure writes to the log file or output file based on type.

O=Output File, L=Log File

*************************************************************************/

BEGIN

IF p_type = 'L' THEN

fnd_file.put_line (fnd_file.log, p_message);

ELSIF p_type = 'O' THEN

fnd_file.put_line (fnd_file.output, p_message);

END IF;

END write;

The above write procedure can be used in other Procedure/Function in the package to write any information in the Log or Out files.

PROCEDURE main(errbuf OUT VARCHAR2, retcode OUT NUMBER, p_par1 IN NUMBER)

IS

v_errbuf VARCHAR2(1000) := NULL;

v_retcode NUMBER := 0;

v_file_name VARCHAR2(100);

BEGIN

v_retcode := 0;

v_file_name := fnd_profile.value('XYZ');

IF v_file_name IS NULL THEN

write('O','Profile XYZ is not defined or the value is not set');

retcode := 2;

RETURN;

END IF;

END;

Note:- In the above Procedure, I am using the write Procedure and returning 2 for the retcode (0 - Complete, 1- Warning and 2 will be for Error).

This is one time process and you will realise, how much helpful it will be when ever you have to right something in log or out file.

AutoInvoice is a program which is used to import and validate transaction data

from other financial systems from which one can create

• INVOICES
• DEBIT MEMOS
• CREDIT MEMOS
• ON-ACCOUNT CREDITS

Features of Auto Invoice Program

Supports Oracle & Non-Oracle Systems
Import Large Amount of Data
Calculate or Import Tax
Group Lines & Invoices
Online Error Correction
Lines Validation
Derive GL Date
Import Flex fields

AutoInvoice is a program set consists of 3 main programs. Each program will have unique nature of work to do and they are called internally except Purge program whose execution is derived on the setup otherwise ready to execute stand alone.

Master (RAXMTR)
Import (RAXTRX)
Purge (RAXDEL)

Auto Invoice Master program RAXMTR

Selects and marks records in the interface tables to be processed based on the parameters the user entered and then calls the AutoInvoice Import program. Auto Invoice Master program has no report output.

• Gathers statistics, it means it gathers the stats on interface tables and set the stats on certain indices on interface tables
• Marks interface records for processing by marking request_id
• Submits multiple workers for Parallel Processing by creating instances for request.

Auto Invoice Import Program

Validates the selected record and creates transaction if it passes validation. Any record that fails validation is left in the interface table with an error code. Depending on the setup, related records may be rejected as well. This program has an output file called Auto Invoice Execution report, which we can view by clicking the View Report button in the Requests window. Working of Auto invoice , Validates data, Inserts records, Deletes interface data Only when system option purge set to ‘Y’

Auto Invoice Purge Program

Deletes records from the interface tables. If you set the Purge Interface Table system option to No in Define System Option window, Auto Invoice does not delete processed records from the interface tables after each run, and we must submit Auto Invoice Purge Program periodically to clean up the interface tables. This program only deletes transaction lines that have been successfully imported. Deletes all rows where interface_status =‘P’

• Ra_interface_lines
• Ra_interface_distributions
• Ra_interface_salescredits

Oracle Receivable’s Auto Invoice program will be used to import and validate Invoices. A custom feeder program is required to transfer data
from the Advantage extract files and populate the Auto Invoice interface tables (RA_INTERFACE_LINES_ALL and RA_INTERFACE_DISTRIBUTIONS_ALL).
If there is need to run populate sales credit into RA_INTERFACE_SALESCREDITS_ALL table. When run, AutoInvoice produces the AutoInvoice Execution Report and the AutoInvoice Validation Report. Any entries which failed validation can be reviewed in Oracle Receivables’ AutoInvoice Interface Exceptions window. Depending on the error, changes may need to be made in Receivables, the feeder program or the imported records in the interface tables.

How Autoinvoice Execution works

Normally, Auto Invoice can be divided into three major phases,

Pre-grouping : here the validates all of the line level data takes place
Grouping : groups lines based on the grouping rules and validates header level data
Transfer : validates information that exists in Receivables tables

What happen when AutoInvoice run?

Once the Auto invoice Program gets called, the following activity takes place is part of execution process. This can be analyzed by debug options. Line, accounting, and sales credit information for each line populates three interface tablesLines are ordered and groupedTax is calculatedGL date is determinedGL accounts are assigned using Auto AccountingTax, freight, commitments, and credit memos are linked to transaction linesAll transactions are batchedValidated lines are used to create the transaction.

Flow of Data

Select, insert and update and delete take place on certain tables once it is logged out.

Selects
– RA_INTERFACE_LINES_ALL
– RA_INTERFACE_DISTRIBUTIONS_ALL
– RA_INTERFACE_SALESCREDITS_ALL

Updates/Insert
– RA_INTERFACE_ERRORS_ALL
– RA_CUSTOMER_TRX_ALL
– RA_CUSTOMER_TRX_LINES_ALL
– AR_PAYMENT_SCHEDULES_ALL
– AR_RECEIVABLE_APPLICATIONS_ALL

Inserts
– RA_INTERFACE_ERRORS_ALL

AutoInvoice Exception Handling

Records that fail validation are called ‘Exceptions’. Exceptions stay in Interface Tables which is RA_INTERFACE_ERRORS_ALL. Errors can be corrected in the Exception Handling window. Once corrections are made, Auto invoice must be resubmitted. Records that pass validation get transferred to Receivables tables

AutoInvoice Exception Handling Windows

-Interface Exception window displays exception messages associated with all invalid records
-Interface Lines window displays records that fail validation, provides an error message and can be used to correct the errors
-The Line Errors windows displays errors associated with a specific line, and can only be opened from Interface Lines window
-Interface Exceptions window displays Interface Id, Exception Type, Error Message and Invalid Value associated to the error
-Data cannot be edited in this window, but error can be viewed and corrected by clicking the Details button
-Error Message and Column name with invalid data are displayed in the Message column, and the invalid value that needs to be corrected is displayed in the Invalid Value column

Step 1 - Copy the jsp file to $COMMON_TOP/webapps/oacore/html
(this location translates to $OA_HTML).

Placing the file under $OA_HTML enables end users to access the file
using the url http://hostname.domain:port/OA_HTML/[jspfile.jsp]
Step 2 - export PATH=$PATH:$FND_TOP/patch/115/bin
Step 3 - ojspCompile.pl -compile -s ‘[jspfile.jsp]‘

There is no need to bounce apache server after compiling the file to access the new content.

Below is the command line help for ojspCompile.pl command
ojspCompile.jsp command help
syntax: ./ojspCompile.pl COMMAND {ARGS}COMMAND

– compile update dependency, compile delta

– create rebuild entire dependency file

– delta.out update dependency, list delta to file

– dep.out update dependency, output heirarchy to file

ARGS -s matching condition for JSPs filenames

– p number of parallel compilations- log to override logfile from ojspCompile.conf
It is recommended to set the log file location outside of any network file system shared (NFS) area/drive.

– conf to override ojspCompile.conf– retry retry previously failed compilation attempts

– flush forces recompilation of all parent JSPs

– quiet do not provide an actively running progress meter

– fast instantly fail jsps that are *possibly* invalid

example1 : ojspCompile.pl –compile -s ‘jtf%’ -p 20 –retry
example2 : ojspCompile.pl –compile -s ‘jtflogin.jsp,jtfavald.jsp’ –flush
example3 : ojspCompile.pl –compile –fast –quiet

AD_APPL_TOPS
This table holds the various APPL-TOP’s in the Oracle Applications installation that have ever been patched.

AD_APPLIED_PATCHES
AD_APPLIED_PATCHES holds information about the “distinct” Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. “merged” patches), then they are considered distinct and this table will therefore hold 2 records.

AD_BUGS
AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation.


AD_PATCH_DRIVERS
This table holds information about the patch drivers that comprise a patch.


AD_FILE_VERSIONS
This table holds the various versions of Oracle Applications files (real files, not “pseudo-files”), that have ever been patched or executed in the Oracle Applications installation.

AD_FILES
AD_FILES is the “files repository”. It contains information about the various files that have been patched in the Oracle Applications installation. Some entries are “pseudo-files” and not real files, (eg. directories) in which case some of the columns are not applicable and would then hold the value “DUMMY”

AD_PATCH_DRIVER_LANGS
NLS patches (or more specifically, NLS patch drivers) pertain to a language or multiple languages. This table holds that language (or multiple languages).

AD_PATCH_DRIVER_MINIPKS
This table holds information about the various Mini Packs contained in a patch (driver)

AD_PATCH_RUN_BUG_ACTIONS

holds the various actions present in “applied” bug (fix).
If Autopatch determined not to apply a bug (fix), then this table will not hold any records for that “unapplied” bug fix.

AD_PATCH_RUN_BUG_ACTIONS
Even though a patch may have been applied on an Oracle Applications installation, some actions in some of its included bugs (fixes) may not have got executed if the “Autopatch” utility determined that it was not necessary to execute those actions. In such cases, EXECUTED_FLAG is set to N.

AD_PATCH_RUN_BUGS
This table holds information about the bugs fixed in a specific run of Autopatch.
AD_PATCH_RUN_BUGS holds information about the various bugs fixed in a specific run of Autopatch.
Even though a patch may have been applied on an Oracle Applications installation, some bugs (fixes) contained in it may not get applied due to some reason. In such cases, the REASON_NOT_APPLIED column holds the reason.

AD_PATCH_RUNS
AD_PATCH_RUNS holds information about the various invocations of Autopatch for applying Oracle Applications patches to a specific release of an Oracle Applications installation.


If multiple drivers are run in one invocation of Autopatch, they result in multiple records in this table. These multiple records will all have the same SESSION_ID (because they arose from one Autopatch invocation), but different TASK_NUMBER’s. The TASK_NUMBER’s in this case will be numbered sequentially as 1, 2, 3, etc.


Note that when the database driver of a Maintenance Pack is applied, it bumps up the release version by creating a new record in AD_RELEASES, which is then pointed to by the UPDATED_TO_RELEASE_ID column of the old record.

AD_RELEASES
AD_RELEASES holds the various Oracle Applications releases that an installation of Oracle Applications has gone through in its entire life cycle.

It should be noted that START_DATE_ACTIVE, END_DATE_ACTIVE and BASE_RELEASE_FLAG are loosely-maintained informational columns and are not accurately maintained,
and therefore should not be relied upon heavily.

AD_PATCH_COMMON_ACTIONS
This table holds distinct information about the various actions that are (often repeatedly) performed by Autopatch as part of applying patches.

Some times we need to diagnose the issue or error coming in forms. For such situation we need to get more information about the issue we are facing in forms. One of the best way to get such information is using tracing.

Many types of tracing are available in forms. We will discuss couple of methods for enabling the tracing and see how that can help us in diagnosing the issue.

Enabling SQL Tracing
First we see how to enable a normal SQL tracing. In my case I have a forms where I am entering few values and then saving the form. This will have some insert and updates command in the backend which will get executed when we save the form. SQL tracing is used to track those DML queries to debug the issue. When we open the form we can see on the menu option

Help -> Diagnostic -> Trace ->

Here we have many options available (default is ‘No Trace’). We can enable tracing by selecing on of the options from here. ‘Regular Trace’ gives the least information and ‘Trace with Binds and Waits’ (level 12) gives maximum information. Be careful while enabling SQL trace with wait and binds as this will make the trace file huge.

Also we can start the tracing in the middle of forms sessions also. Example in my case I wanted to check the last insert/update DML statements that gets fired when we save the form. So after we fill all the values in the fields, we can start the tracing so that the initial select statement does not come in trace file.

When we enable the trace it will give the trace file location (This location will be the location of USER_DUMP_DESTINATION parameter of database).
After you save the form you can stop tracing by selecting ‘No Trace’ again from
Help -> Diagnostic -> Trace -> No Trace

Use tkprof to covert trace file in readable format.
-bash-2.05b$ tkprof md9ys210_ora_20412_MFG.trcoutput = trace_sql.txt
TKPROF: Release 10.2.0.4.0 - Production on Mon Jul 28 23:54:31 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
-bash-2.05b$

Enabling Forms Tracing
Another way for debugging is to enable tracing at forms level for a particular user. This method is also explained in metalink note ID 373548.1. By default, it will trace errors only. To utilize the full flexibility of Forms Trace, the record=forms parameter must be used in conjunction with the tracegroup parameter.
Applications Development has created several recommended levels of tracing, which are predefined in the file
$ORA_CONFIG_HOME/10.1.2/forms/server/ftrace.cfg

· light (0-71,192): Records user actions, error messages and form service events.
· medium (0-97,192,194,195): Includes events in the light tracegroup also captures built-in and user-exit events.
· full (0-98,100-199): Captures all information that is currently available through Forms Tracing.
· dbsql (32,64,98,100,101,136,137,192): All events related to the database and forms interaction with it
· network (64,32,128,129,131,130,132,133,134,192): All events related to communications between client tier and Forms server.

In my case I am enabling the tracing for SQLs.
Please follow below steps to enable form level tracing.
1. Sign on to Oracle Applications as a user with the System Administrator responsibility.
2. Select the System Administrator responsibility.
3. Select the Profile -> System function (this will launch Forms).
4. Search for the Applications user and the profile option Forms Runtime Parameters.
5. Add the required parameters, using the syntax: record=forms tracegroup=dbsqlYou can use any other value also for tracegroup depending on your requirement. Examples:
record=forms tracegroup=medium
Note the space between the parameter/value pairs.
6. Commit the changes and log out of Applications.
7. Restart the Forms OC4J group using either adformsctl.sh (servlet) or adformsrvctl.sh (socket).
8. Log in to Applications as the user whose Forms Runtime Parameters profile option was amended.
Before doing this, metalink note ID also mention to append “record=forms” at the end of ICX: Forms Launcher profile for that user. Example
http://rafi.oracle.com:8080/forms/frmservlet?record=forms

But when I tried that I got following error.
FRM-90926: Duplicate Parameter on command line.
So I removed record=forms and I was able to generate the trace files without any issue. This may be some issue with my instance.

Once you set the profile “Forms Runtime Parameters”, bounce the forms and try to accecss forms, it will generate a trace file at the location defined by FORMS_TRACE_DIR. After you finish the navigation in form and exit, your trace file will get created FORMS_TRACE_DIR location.
This file will be in binary format and cannot be read. You need to convert this into .txt or .html format using following command
java -cp $ORACLE_HOME/forms/java/frmxlate.jar oracle.forms.diagnostics.Xlate datafile=forms_8842.trc outputfile=forms_trace1.html outputclass=WriteOutHTML

Here,
datafile=forms_8842.trc is the name of trace file that got created due to activities on forms.
outputfile=forms_trace1.html is the name of output file that will get created. You can use html or .txt outputclass=WriteOutHTML parameter decides if the output file should be HTML or TXT.

If you use just WriteOut then output file will be TXT.
If you use WriteOutHTML then output file will be HTML.