My Findings ...

06 February 2015

Sample code for dbms_scheduler.create_job

Sample code for dbms_scheduler.create_job

ln_job_id NUMBER;

lv_job VARCHAR2(100);

lv_job_action VARCHAR2(150) := 'BEGIN XXPACKAGE_NAME.procedure(''' ||
pv_rice_id || ''',' || pn_run_id || '); END;';
BEGIN
ln_job_id := xxaud_soa_inbound_seq.nextval;
lv_job := pv_rice_id || '_' || pn_run_id || '_' || ln_job_id;

dbms_scheduler.create_job(job_name => lv_job,
job_type => 'PLSQL_BLOCK',
job_action => lv_job_action,
number_of_arguments => 0,
enabled => TRUE,
comments => 'SOA Invoke Program');

END;

30 January 2015

GOVERNMENT INTRODUCED ONLINE SERVICES 🔘

06 January 2015

SQL to fetch the SMTP server details from Workflow Setup

SQL to fetch the SMTP server details from Workflow Setup

select fscpv.parameter_value smtp_server_name,fscpt.display_name
from fnd_svc_comp_params_tl fscpt
, fnd_svc_comp_param_vals fscpv
, fnd_svc_components fsc
where fscpt.parameter_id = fscpv.parameter_id
and fscpv.component_id = fsc.component_id
and fscpt.display_name = 'Outbound Server Name'
and fsc.component_name = 'Workflow Notification Mailer'

09 October 2014

How to delete XML Templete in oracle R12

Use the following code to delete a Template Code

declare
l_templateCode varchar2 (100) := ; -- Template Code
begin
for r in (select t1.application_short_name template_app_name,
t1.data_source_code,
t1.application_short_name def_app_name
from xdo_templates_b t1
where t1.template_code = l_templateCode)
loop

xdo_templates_pkg.delete_row (r.template_app_name, l_templateCode);

delete from xdo_lobs
where lob_code = l_templateCode
and application_short_name = r.template_app_name
and lob_type in ('TEMPLATE_SOURCE', 'TEMPLATE');

delete from xdo_config_values
where application_short_name = r.template_app_name
and template_code = l_templateCode
and data_source_code = r.data_source_code
and config_level = 50;

end loop;
end;

18 September 2014

How do I re-enable PST usage on an Outlook Client

How do I re-enable PST usage on an Outlook Client

1) Click Start, and then click Run.
2) In the Open box, type regedit, and then click OK.
3) Locate one of the following keys in the registry, as applicable:

For Outlook 98:
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\8.0\Outlook\

For Outlook 2000:
HKEY_LOCAL_MACHINE\Software\Microsoft\Office\9.0\Outlook\

On the Edit menu, click Add Value, and then add the following registry value:
Value Name: DisablePst
Data Type: REG_DWORD
Value: 1 -- To disable/0 - enable

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q258277

19 August 2014

Oracle dbms_scheduler

Starting with 10g offers a brand new job-scheduling facility, known as The Scheduler, controlled via the new package dbms_scheduler. This package replaces the dbms_job (but that one is still available). The new scheduler offers more functionality over the dbms_job package.

CREATE TABLE KTEST

(

X NUMBER,

Y NUMBER

);

CREATE SEQUENCE ktest_s

START WITH 1;

CREATE OR REPLACE PROCEDURE KPRO (PX IN NUMBER, PY NUMBER)

BEGIN

INSERT INTO KTEST

VALUES (PX, PY);

DBMS_LOCK.SLEEP (60);

COMMIT;

END KPRO;

BEGIN

DBMS_SCHEDULER.create_program (

program_name => 'TEST_STORED_PROCEDURE_PROG',

program_type => 'STORED_PROCEDURE',

program_action => 'KPRO',

number_of_arguments => 2,

enabled => FALSE,

comments => 'SOA Invoke Program');

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

program_name => 'TEST_STORED_PROCEDURE_PROG',

argument_position => 1,

argument_type => 'NUMBER',

DEFAULT_VALUE => '1');

DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT (

program_name => 'TEST_STORED_PROCEDURE_PROG',

argument_position => 2,

argument_type => 'NUMBER',

DEFAULT_VALUE => '2');

DBMS_SCHEDULER.enable ('TEST_STORED_PROCEDURE_PROG');

END;

CREATE OR REPLACE PROCEDURE SOA_INVOKE (px IN VARCHAR2, py IN VARCHAR2)

ljob_id NUMBER;

ljob VARCHAR2 (100);

BEGIN

ljob_id := ktest_s.NEXTVAL;

ljob := 'RequestID_' || ljob_id;

DBMS_SCHEDULER.create_job (ljob,

program_name => 'TEST_STORED_PROCEDURE_PROG');

DBMS_SCHEDULER.set_job_argument_value (ljob, 1, px);

DBMS_SCHEDULER.set_job_argument_value (ljob, 2, px);

DBMS_SCHEDULER.enable (ljob);

DBMS_OUTPUT.put_line ('Job ID : ' || ljob);

END;

EXECUTE SOA_INVOKE(1,2);

SELECT *

FROM ALL_SCHEDULER_RUNNING_JOBS

WHERE job_name = :1

References

http://www.pafumi.net/Scheduler_DBMS_SCHEDULER.html

http://dba.stackexchange.com/questions/42119/passing-parameters-to-a-procedure-executed-by-dbms-scheduler

04 August 2014

Wait for concurrent request using fnd_concurrent.wait_for_request

Most of the times while submitting concurrent request one has to wait for its completion to perform sequence of steps. This can be achieved through fnd_concurrent.wait_for_request in oracle apps. It returns the status of the previously submitted concurrent program upon interval set.

This is a conjunction to the SUBMIT_REQUEST. Follow ARTICLE on submitting concurrent program and use below call upon completed. There are some parameter that has to be declared.

lb_complete BOOLEAN;
lc_phase VARCHAR2 (100);
lc_status VARCHAR2 (100);
lc_dev_phase VARCHAR2 (100);
lc_dev_status VARCHAR2 (100);
lc_message VARCHAR2 (100);

ln_request_id is return variable for concurrent_request_id from above article. If ln_request_id > 0 (means request submitted successfully) then wait_for_request.

Arguments (input)
request_id - Request ID to wait on
interval - time b/w checks. Number of seconds to sleep (default 60 seconds)
max_wait - Max amount of time to wait (in seconds) for request's completion
Arguments (output)
User version of phase and status
Developer version of phase and status
Completion text if any
phase - Request phase ( from meaning in fnd_lookups )
status - Request status( for display purposes )
dev_phase - Request phase as a constant string so that it can be used for comparisons
dev_status - Request status as a constatnt string
message - Completion message if request has completed

IF ln_request_id > 0
THEN
lb_complete :=
fnd_concurrent.wait_for_request (request_id => ln_request_id
,interval => 2
,max_wait => 60
-- out arguments
,phase => lc_phase
,status => lc_status
,dev_phase => lc_dev_phase
,dev_status => lc_dev_status
,message => lc_message
);
COMMIT;

IF UPPER (lc_dev_phase) IN ('COMPLETE')
THEN
dbms_output.put_line('Concurrent request completed successfully');
END IF;
END IF;

My Findings ...

Search This Blog

06 February 2015

Sample code for dbms_scheduler.create_job

30 January 2015

GOVERNMENT INTRODUCED ONLINE SERVICES 🔘

06 January 2015

SQL to fetch the SMTP server details from Workflow Setup

09 October 2014

How to delete XML Templete in oracle R12

18 September 2014

How do I re-enable PST usage on an Outlook Client

19 August 2014

Oracle dbms_scheduler

04 August 2014

Wait for concurrent request using fnd_concurrent.wait_for_request

About Me

Labels

Blog Archive

Friends Blogs

Telugu News

Followers