Generate a trace file for a concurrent program

See also:

Metalink Note: 296559.1

Metalink Note: 130182.1

Check the “Enable Trace” flag on the Concurrent Program > Define screen, and then run the program.

Find the trace file on the database server,using the follwoing SQL:

SELECT prog.executable_id
, execname.executable_id
, request_id
, oracle_process_id
, req.enable_trace
, dest.value||’/’||lower(dbnm.value)||’_ora_’||oracle_process_id||’.trc’ AS TRACE_FILE
, prog.user_concurrent_program_name
, execname.execution_file_name
, execname.subroutine_name AS EXEC_FILENAME
, DECODE(phase_code,’R’,’Running’)||’-‘||DECODE(status_code,’R’,’Normal’) AS STATUS
, ses.sid||’,’||ses.serial# AS SID_SERIAL
, ses.module
FROM fnd_concurrent_requests req
, v$session ses
, v$process proc
, v$parameter dest
, v$parameter dbnm
, fnd_concurrent_programs_vl prog
, fnd_executables execname
WHERE req.request_id = :request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.name=’user_dump_dest’
AND dbnm.name=’db_name’
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
–AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

Also:

SELECT value AS TRACE_LOCATION
FROM v$parameter
WHERE name = ‘user_dump_dest’

TKPROF the trc file, to generate readable output (using the 10g version of tkprof – run . db):

tkprof trc_file output_file sort='(prsela,fchela,exeela)’ print=10

Oracle WebADI – Technical Overview

At a very high level, a WebADI application consists of an MS Excel spreadsheet template to store the data to be uploaded to Oracle Applications, and a PL/SQL procedure to validate and upload this data row-by-row.

By convention, the PL/SQL procedure has an input parameter corresponding to each column in the spreadsheet, and has no output parameters.

The procedure has no output parameters (errors are communicated back to the spreadsheet using the standard raise_application_error API, where they appear in the “Messages” column).

Rows in the spreadsheet are processed one-by-one. Each row is processed by its own instance of the PL/SQL procedure. In this sense, the logic in the procedure for a specific row can have no knowledge of previous or subsequent rows in the spreadsheet.

The WebADI spreadsheet is launched from a custom Oracle Applications function, attached to an existing Menu, under a suitable Responsibility.

An Oracle WebADI object consists of the following principal components:

  • An integrator
  • An interface
  • A layout
  • A mapping

These components can, to some extent, be created and maintained using the functions available under the Desktop Integrator responsibility (in R12).

They can also be created, modified and deleted using the functions and procedures in a series of seeded PL/SQL packages, principally BNE_INTEGRATOR_UTILS.

WebADI database objects can be found in the BNE schema in the Applications database.

Taking each WebADI component in turn…

The Integrator component is effectively a container for a specific WebADI application, which can be invoked from within Oracle Applications (usually from a conventional applications Function, attached to a Menu). Properties or attributes of the Integrator determine the behaviour of the (MS Excel) user interface, including the types of content (“None”, “Text File”, etc.) which can be downloaded into it, and the upload screen options (“Validate Before Upload”, etc.).

The Integrator definitions are stored in the tables bne_integrators_b and bne_integrators_tl.

The Interface contains a WebADI “column” corresponding to each input parameter in the PL/SQL procedure which manages the WebADI data upload process. The PL/SQL procedure must therefore exist prior to creating the WebADI Interface. By default, interface column names are identical to the parameter names in the PL/SQL procedure.

The interface definitions are stored in bne_interfaces_b and bne_interfaces_tl.

The Interface Column definitions are stored in bne_interface_cols_b and bne_interface_cols_tl.

The Layout contains a column corresponding to each spreadsheet column. The layout can be based on the columns in a database view (if the WebADI application is being used to download data from Oracle Applications), in which case it is created automatically during the Interface creation process. Alternatively, a default layout can be created, with columns simply numbered from 1 to n.

The layout definitions are stored in bne_layouts_b and bne_layouts_tl.

The Layout Column definitions are stored in bne_layout_cols.

The Mapping provides the link between the columns in the data source, and the Interface columns. In other words, the link between the columns in the Excel spreadsheet and the columns in the database. The Mapping must be created manually, in the Applications, via the Desktop Integrator responsibility.

The mapping definitions are stored in bne_mappings_b, bne_mappings_tl and bne_mapping_lines.

Having defined an entire WebADI application, it can be refined by adding Applications-style Lists of Values to the spreadsheet to allow the lookup/validation of fields such as Asset Category, GL Account Code, etc., using API’s such as BNE_INTEGRATOR_UTILS.CREATE_TABLE_LOV and BNE_INTEGRATOR_UTILS.CREATE_CCID_KFF.

Note: the above changes will not be visible in the WebADI spreadsheet until the Database (not just the Applications) Server is re-started.

SQL: an example of RANK, PARTITION BY, ORDER BY

To reurn the PO information related to a supplier invoice. Since only one row of data is required per invoice, the SELECT statement must show the most recent PO number, the total number of related PO’s (if there are more than one). Similarly for PO lines, shipment lines, receipts.

SELECT CASE WHEN qrslt.po_more > 1 THEN

         qrslt.segment1
         ‘ (1 of ‘po_more‘)’
       ELSE
         qrslt.segment1
       END AS po_number
,      CASE WHEN qrslt.line_more > 1 THEN
         TO_CHAR(qrslt.line_num)
         ‘ (1 of ‘line_more‘)’
       ELSE
         TO_CHAR(qrslt.line_num)
       END AS line_num
,      CASE WHEN qrslt.ship_more > 1 THEN
         TO_CHAR(qrslt.shipment_num)
         ‘ (1 of ‘ship_more‘)’
       ELSE
         TO_CHAR(qrslt.shipment_num)
       END AS ship_num
,      qrslt.full_name AS requestor
,      CASE WHEN qrslt.rcpt_more > 1 THEN
         TO_CHAR(qrslt.receipt_num)
         ‘ (1 of ‘rcpt_more‘)’
       ELSE
         TO_CHAR(qrslt.receipt_num)
       END AS rcpt_num
FROM
( SELECT pha.segment1
, pla.line_num
, plla.shipment_num
, ppx.full_name
, rsh.receipt_num
, pha.creation_date po_date
, rsh.creation_date rcpt_date
, RANK() OVER
(
ORDER BY pha.creation_date ASC
) po_more
, RANK() OVER
(
PARTITION BY pha.segment1
ORDER BY pla.line_num DESC
) line_more
, RANK() OVER
(
PARTITION BY pha.segment1
, pla.line_num
ORDER BY plla.shipment_num DESC
) ship_more
, RANK() OVER
(
PARTITION BY pha.segment1
ORDER BY rsh.creation_date ASC
) rcpt_more
FROM po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
, po_distributions_all pda
, ap_invoice_distributions_all aida
, per_people_x ppx
, rcv_shipment_headers rsh
, rcv_shipment_lines rsl
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND plla.org_id = pla.org_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pda.po_line_id
AND pda.line_location_id = plla.line_location_id
AND pda.org_id = pla.org_id
AND aida.org_id = pda.org_id
AND aida.po_distribution_id = pda.po_distribution_id
AND aida.line_type_lookup_code = ‘ITEM’
AND ppx.person_id(+) = pha.agent_id
AND rsl.po_distribution_id(+) = pda.po_distribution_id
AND rsl.shipment_header_id = rsh.shipment_header_id(+)
AND aida.invoice_id = p_invoice_id
AND aida.org_id = p_org_id
ORDER BY pha.creation_date DESC
, pla.line_num ASC
, plla.shipment_num ASC
, rsh.creation_date DESC
) qrslt
WHERE ROWNUM = 1

Example of a PIPELINED PL/SQL function

To return a data collection (or array – i.e. a record or table)

First define the data structures required:

CREATE OR REPLACE TYPE o_po_info AS OBJECT
(po_num VARCHAR2(20)
,po_line_num NUMBER
,po_ship_num NUMBER
);

…used by the PL/SQL function, and…

CREATE OR REPLACE TYPE t_po_info AS TABLE OF o_po_info;

…used by the query invoking the function.

Alternatively, declare RECORD and TABLE types in the PL/SQL function itself:

TYPE r_po_info IS RECORD (po_num VARCHAR2(20)
 ,po_line_num VARCHAR2(100)
 ,po_ship_num VARCHAR2(100)
 ,agent_name VARCHAR(240)
 ,receipt_num VARCHAR(30)
 );
 TYPE t_po_info IS TABLE OF r_po_info;
 

Then create the function. The following returns PO information for a specific supplier invoice:

CREATE OR REPLACE FUNCTION dt_test
(p_invoice_id IN NUMBER)
RETURN t_po_info
PIPELINED
AS
CURSOR c_po_info
(p_invoice_id IN NUMBER)
IS
SELECT pha.segment1
, pla.line_num
, plla.shipment_num
, COUNT(DISTINCT pha.segment1)
, COUNT(DISTINCT pla.line_num)
, COUNT(DISTINCT plla.shipment_num)
FROM po_headers_all pha
, po_lines_all pla
, po_line_locations_all plla
, po_distributions_all pda
, ap_invoice_distributions_all aida
WHERE pha.po_header_id = pla.po_header_id
AND pha.org_id = pla.org_id
AND plla.po_header_id = pla.po_header_id
AND plla.po_line_id = pla.po_line_id
AND plla.org_id = pla.org_id
AND pda.po_header_id = pla.po_header_id
AND pda.po_line_id = pda.po_line_id
AND pda.org_id = pla.org_id
AND aida.org_id = pda.org_id
AND aida.po_distribution_id = pda.po_distribution_id
AND aida.invoice_id = p_invoice_id
AND ROWNUM = 1
GROUP BY pha.segment1
, pha.creation_date
, pla.line_num
, plla.shipment_num
ORDER BY pha.creation_date DESC
, pla.line_num
, plla.shipment_num
;

v_po_info o_po_info := o_po_info(NULL,NULL,NULL);
v_po_num VARCHAR2(20);
v_po_line_num NUMBER;
v_po_ship_num NUMBER;
v_po_count NUMBER;
v_po_line_count NUMBER;
v_po_ship_count NUMBER;

BEGIN
OPEN c_po_info(p_invoice_id);
FETCH c_po_info
INTO v_po_num
, v_po_line_num
, v_po_ship_num
, v_po_count
, v_po_line_count
, v_po_ship_count
;

IF (c_po_info%NOTFOUND) THEN
  CLOSE c_po_info;
ELSE
  IF (v_po_count = 1) THEN
    v_po_info.po_num := v_po_num;
  ELSIF (v_po_count > 1) THEN
    v_po_info.po_num := v_po_num||' + more';
  END IF;

  IF (v_po_line_count = 1) THEN
    v_po_info.po_line_num := v_po_line_num;
  ELSIF (v_po_line_count > 1) THEN
    v_po_info.po_line_num := v_po_line_num||' + more';
  END IF;

  IF (v_po_ship_count = 1) THEN
    v_po_info.po_ship_num := v_po_ship_num;
  ELSIF (v_po_ship_count > 1) THEN
    v_po_info.po_ship_num := v_po_ship_num||' + more';
  END IF;

  CLOSE c_po_info;
END IF;
PIPE ROW (v_po_info);
RETURN;
EXCEPTION
WHEN OTHERS THEN
RETURN;
END dt_test;

The function can be used in a conventional query:

SELECT table_alias.*
FROM   TABLE(CAST(dt_test(2572423) AS t_po_info)) table_alias

Or, if the custom data TYPEs are declared inside the PL/SQL package or function, the query does not not require the CAST clause:

SELECT table_alias.*
FROM   TABLE(dt_test(2572423)) table_alias

 

Here is another example, to show that table rows are not overwritten by values derived in subsequent loop iterations:

First the data structures

CREATE OR REPLACE TYPE o_user AS OBJECT
(user_name VARCHAR2(50));

CREATE OR REPLACE TYPE t_user AS TABLE OF o_user;

Then create the function. The following returns 3 user names from fnd_user:

CREATE OR REPLACE FUNCTION dt_test
RETURN t_user
PIPELINED
AS

CURSOR c_user
IS
SELECT user_id
FROM   fnd_user
WHERE  user_name IN ('DAVE_TAYLOR','ELLIOTT_JONES','MANOGNA_VADAPALLI')
;

v_user_name    VARCHAR2(50);
v_user  o_user := o_user(NULL);

BEGIN

FOR i_user IN c_user LOOP

  SELECT user_name
  INTO   v_user_name
  FROM   fnd_user
  WHERE  user_id = i_user.user_id
  ;

  v_user.user_name := v_user_name;

  PIPE ROW (v_user);

  dbms_output.put_line('User : '||v_user_name);

END LOOP;

RETURN;

END;

FND Debug Logging

1. In the System Profile Options screen, query all FND: Debug Log profile options and set the following at user-level:

FND: Debug Log Enabled = ‘Yes’
FND: Debug Log Level = ‘Statement’
FND: Debug Log Module = ‘%’   (or limit the string to your conc program name, for example)

2. Save these changes and run your process in Oracle Applications.

3. When the process completes, interrogate the log messages table:

SELECT log_sequence
,      module
,      message_text
FROM   fnd_log_messages
WHERE  user_id = 13334
ORDER BY log_sequence ASC

4. In order to write to the log messages table from a PL/SQL program, use:

fnd_log.STRING (fnd_log.level_statement,MODULE NAME,MESSAGE TEXT);