Thursday, October 14, 2010

Helpful View for Concurrent Request Details

Select * from apps.fnd_amp_requests_v where request_id = XXXXX

Use this View to get helpful info on the status of a concurrent request, who submitted and what responsibility it was run under. Very help View!

Wednesday, June 30, 2010

EBS PL/SQL multithreading example

Here are some good examples of multi-threading Concurrent Program from within a PL/SQL Program.

Example1:
http://sbllc3.solutionbeacon.net/pls/a159vis2/fndgfm/fnd_help.get/US@PSA_US/fnd/@concglob

OR

Example2:

PROCEDURE CONC_MULTI_THREAD_PRG (
Errbuf OUT NOCOPY VARCHAR2
,Retcode OUT NOCOPY VARCHAR2
)
IS
ln_thread_count NUMBER;
ls_smtp_server VARCHAR2(240);
ln_smtp_server_port PLS_INTEGER;
ls_from_name VARCHAR2(240);
ls_subject VARCHAR2(240);
ls_message VARCHAR2(240);
ls_message_html VARCHAR2(240);
ln_conc_request_id NUMBER := NULL;
ls_req_data VARCHAR2(240);
ln_request_id NUMBER; -- parent request id
cnt_warnings INTEGER := 0;
cnt_errors INTEGER := 0;
request_status BOOLEAN;
BEGIN
ls_req_data := fnd_conc_global.request_data;
ln_request_id := fnd_global.conc_request_id;

IF ls_req_data IS NOT NULL THEN
put_log_line( ' Back at beginning after spawing ' ls_req_data ' threads.');
ln_thread_count := ls_req_data;

IF ln_thread_count > 0 THEN
put_log_line ( 'Checking child threads...');

-- Check all child requests to see how they finished...
FOR child_request_rec IN (SELECT request_id, status_code
FROM fnd_concurrent_requests
WHERE parent_request_id = ln_request_id)
LOOP
check_child_request(child_request_rec.request_id);
IF ( child_request_rec.status_code = 'G' OR child_request_rec.status_code = 'X'
OR child_request_rec.status_code ='D' OR child_request_rec.status_code ='T' ) THEN
cnt_warnings := cnt_warnings + 1;
ELSIF ( child_request_rec.status_code = 'E' ) THEN
cnt_errors := cnt_errors + 1;
END IF;
END LOOP; -- FOR child_request_rec

IF cnt_errors > 0 THEN
put_log_line( 'Setting completion status to ERROR.');
request_status := fnd_concurrent.set_completion_status('ERROR', '');
ELSIF cnt_warnings > 0 THEN
put_log_line( 'Setting completion status to WARNING.');
request_status := fnd_concurrent.set_completion_status('WARNING', '');
ELSE
put_log_line( 'Setting completion status to NORMAL.');
request_status := fnd_concurrent.set_completion_status('NORMAL', '');
END IF;
END IF;

RETURN; -- end of parent
END IF;

get_translations('AR_EBL_EMAIL_CONFIG','RESEND',ls_smtp_server, ln_smtp_server_port, ls_from_name, ls_subject, ls_message, ls_message_html);
get_translation('AR_EBL_CONFIG','TRANSMIT_EMAIL','N_THREADS',ln_thread_count);

put_log_line('spawning ' ln_thread_count ' thread(s)');

FOR i IN 1..ln_thread_count LOOP
put_log_line('thread: ' i);
-- TRANSMIT_EMAIL_C(Errbuf,Retcode, i, ln_thread_count, ls_smtp_server, ln_smtp_server_port, ls_from_name);

n_conc_request_id :=
FND_REQUEST.submit_request
( application => 'XXFIN' -- application short name
,program => 'XX_AR_EBL_TRANSMIT_EMAIL_C' -- concurrent program name
,sub_request => TRUE -- is this a sub-request?
,argument1 => i -- thread_id
,argument2 => ln_thread_count
,argument3 => ls_smtp_server
,argument4 => ln_smtp_server_port
,argument5 => ls_from_name);

-- ===========================================================================
-- if request was successful
-- ===========================================================================
IF (n_conc_request_id > 0) THEN
-- ===========================================================================
-- if a child request, then update it for concurrent mgr to process
-- ===========================================================================
/* -- Instead of doing the following Update, use FND_CONC_GLOBAL.SET_REQ_GLOBALS(conc_status => 'PAUSED', request_data => to_char(ln_thread_count)) -- See below
-- This program will then restart when the child programs are done, so if fnd_conc_global.request_data is NOT NULL at start of proc, check child statuses & end.
-- If either this Update, or the set_req_globals approach, is not done, the child programs will hang in Invalid, No Manager status.

UPDATE fnd_concurrent_requests
SET phase_code = 'P',
status_code = 'I'
WHERE request_id = n_conc_request_id;
*/
-- ===========================================================================
-- must commit work so that the concurrent manager polls the request
-- ===========================================================================
COMMIT;

put_log_line( ' Concurrent Request ID: ' n_conc_request_id '.' );

-- ===========================================================================
-- else errors have occured for request
-- ===========================================================================
ELSE
-- ===========================================================================
-- retrieve and raise any errors
-- ===========================================================================
FND_MESSAGE.raise_error;
END IF;

END LOOP;

FND_CONC_GLOBAL.SET_REQ_GLOBALS(conc_status => 'PAUSED', request_data => to_char(ln_thread_count));

END CONC_MULTI_THREAD_PRG;

Friday, November 02, 2007

Documentation on Bursting Engine

Here is a great tutorial for anyone interested in understanding the basics of using the Java bursting engine to distribute document files.

  • E Business Suite Bursting


  • Also, A very fine white paper with additional information on the process.

  • Documentation on Bursting Engine
  • Friday, July 06, 2007

    fnd_request.submit_request handy tip.

    Here is a HANDY TIP that could save you some time bugging why the fnd_request.submit_request procedure is returning a request_id of 0. If you are creating and running a package to run outside Oracle Applications, you may be wondering why the procedure will not be executing your concurrent program being passed into into the fnd_request.submit_request procedure.

    The first make sure that you are passing in the correct parameters into the procedure. Once you are confident that you have the correct parameters make sure you call the FND_GLOBAL.APPS_INITIALIZE procedure prior to calling the submit_request procedure. Since you are executing the package outside of the application you need to intialize your user_id, responsibility_id and Applicaiton_id within your executing procedure.

    It sounds like common sense but it is easy to over look!

    Below is a snipit of code for submitting the gl journal import program from a Procedure:

    BEGIN

    ...

    SELECT user_id
    INTO x_user_id
    FROM FND_USER
    WHERE user_name = ;


    SELECT application_id
    INTO x_appl_id
    FROM FND_APPLICATION
    WHERE application_short_name = 'SQLGL';


    SELECT responsibility_id
    INTO x_resp_id
    FROM FND_APPLICATION fa, FND_RESPONSIBILITY_TL fr
    WHERE fa.application_short_name = 'SQLGL' AND
    fa.application_id = fr.application_id AND
    fr.responsibility_name = 'General Ledger Super User';

    FND_GLOBAL.APPS_INITIALIZE(x_user_id, x_resp_id, x_appl_id);

    x_conc_id := fnd_request.submit_request(application => 'SQLGL'
    ,program => 'GLLEZL'
    ,description => NULL
    ,start_time => SYSDATE
    ,sub_request => FALSE
    ,argument1 => to_char(p_interface_run_id)
    ,argument2 => to_char(p_sob_id)
    ,argument3 => 'N'
    ,argument4 => NULL
    ,argument5 => NULL
    ,argument6 => 'N'
    ,argument7 => 'W');

    ...
    END;

    Friday, February 16, 2007

    Oracle upgrade War Stories!

    Recently, A consultant friend of mine was bragging about how successful a recent upgrade from 11.5.9 for 11.5.10 (AP,GL,AR,CE,PO,FA) was for his project. It was a huge success he claimed! This was an upgrade with no new functionality or application being added. The entire upgrade was completed in only nine months he told me!

    Success?

    Nine months to go one release version up didn't seem that successful to me. Please share your view points and war stories. I need to put my friends success into perspective.

    Tuesday, February 06, 2007

    File Version Help!

    Need help determining a file verison in Oracle Apps. Here is a handy query that will assist you in detemining the current version a file.


    SELECT afl.filename, max(afv.version),APP_SHORT_NAME, SUBDIR
    FROM applsys.ad_file_versions afv, applsys.ad_files afl
    WHERE afv.file_id = afl.file_ID
    AND afl.filename in
    ('APXINWKB.fmb','APXHRUPD.rdf','CUSTOM.pll') -- insert your file names here
    GROUP BY afl.filename, APP_SHORT_NAME,SUBDIR

    Unix users:

    If the file is not in the above tables, use the following commands to determine the version number of a file.

    Change to the directory where file is:
    cd $AP_TOP/reports/US

    Run following command with file name:
    strings -a APXHRUPD.rdf | grep '$Header'

    Monday, February 05, 2007

    US Daylight Saving Change (DST)

    Direct from ATG SUPPORT NEWSLETTER:

    What is the US Daylight Saving Change (DST) for 2007?

    Beginning in 2007, daylight saving time in the U.S. will begin on the second Sunday in March and end the first Sunday in November rather than beginning on the first Sunday in April and ending the last Sunday in October, as it did in the past. This change affects Canada as well. Mexico is still using the old DST rules.


    403311.1
    United States Time Zone Changes 2007: E-Business Suite (EBS).

    Have you looked at our Knowledge Browser page on Metalink? See Daylight Savings Time (DST) USA 2007

    You can also look at this external Blog about Clarifying E-Business Suite Daylight Saving Time 2007 Patching Requirements - http://blogs.oracle.com/schan/2007/01/19#a1149