R12AppsQueriesSQL

Tuesday, 11 September 2018

Query to get Oracle Scheduled Concurrent Program Information

select * from
(
SELECT DISTINCT
fcr.concurrent_program_id
,fcpl.user_concurrent_program_name Concurrent_Program
,frl.responsibility_name
,DECODE(fcrc.class_type,'P','Periodic : '
||SUBSTR(fcrc.class_info,1,INSTR(fcrc.class_info,':',1,1)-1)
||' : '||DECODE(SUBSTR(fcrc.class_info,-3,1)
,'M','Month(s)','W','Week(s)','D','Day(s)'
,'H','Hour(s)','N','Minute(s)','')
||' : '||DECODE(SUBSTR(fcrc.class_info,-1),'C'
,'From Completion of Prior Run','S'
,'From Start of Prior Run',''),'')
||DECODE(fcrc.class_type,'S','Specific : '
||DECODE(SUBSTR(fcrc.class_info,1,1),'0','',':1:')
||DECODE(SUBSTR(fcrc.class_info,2,1),'0','',':2:')
||DECODE(SUBSTR(fcrc.class_info,3,1),'0','',':3:')
||DECODE(SUBSTR(fcrc.class_info,4,1),'0','',':4:')
||DECODE(SUBSTR(fcrc.class_info,5,1),'0','',':5:')
||DECODE(SUBSTR(fcrc.class_info,6,1),'0','',':6:')
||DECODE(SUBSTR(fcrc.class_info,7,1),'0','',':7:')
||DECODE(SUBSTR(fcrc.class_info,8,1),'0','',':8:')
||DECODE(SUBSTR(fcrc.class_info,9,1),'0','',':9:')
||DECODE(SUBSTR(fcrc.class_info,10,1),'0','',':10:')
||DECODE(SUBSTR(fcrc.class_info,11,1),'0','',':11:')
||DECODE(SUBSTR(fcrc.class_info,12,1),'0','',':12:')
||DECODE(SUBSTR(fcrc.class_info,13,1),'0','',':13:')
||DECODE(SUBSTR(fcrc.class_info,14,1),'0','',':14:')
||DECODE(SUBSTR(fcrc.class_info,15,1),'0','',':15:')
||DECODE(SUBSTR(fcrc.class_info,16,1),'0','',':16:')
||DECODE(SUBSTR(fcrc.class_info,17,1),'0','',':17:')
||DECODE(SUBSTR(fcrc.class_info,18,1),'0','',':18:')
||DECODE(SUBSTR(fcrc.class_info,19,1),'0','',':19:')
||DECODE(SUBSTR(fcrc.class_info,20,1),'0','',':20:')
||DECODE(SUBSTR(fcrc.class_info,21,1),'0','',':21:')
||DECODE(SUBSTR(fcrc.class_info,22,1),'0','',':22:')
||DECODE(SUBSTR(fcrc.class_info,23,1),'0','',':23:')
||DECODE(SUBSTR(fcrc.class_info,24,1),'0','',':24:')
||DECODE(SUBSTR(fcrc.class_info,25,1),'0','',':25:')
||DECODE(SUBSTR(fcrc.class_info,26,1),'0','',':26:')
||DECODE(SUBSTR(fcrc.class_info,27,1),'0','',':27:')
||DECODE(SUBSTR(fcrc.class_info,28,1),'0','',':28:')
||DECODE(SUBSTR(fcrc.class_info,29,1),'0','',':29:')
||DECODE(SUBSTR(fcrc.class_info,30,1),'0','',':30:')
||DECODE(SUBSTR(fcrc.class_info,31,1),'0','',':31:')
||DECODE(SUBSTR(fcrc.class_info,32,1),'0','',':Last Day:')
||DECODE(SUBSTR(fcrc.class_info,33,1),'0','',':Sunday:')
||DECODE(SUBSTR(fcrc.class_info,34,1),'0','',':Monday:')
||DECODE(SUBSTR(fcrc.class_info,35,1),'0','',':Tuesday:')
||DECODE(SUBSTR(fcrc.class_info,36,1),'0','',':Wednesday:')
||DECODE(SUBSTR(fcrc.class_info,37,1),'0','',':Thursday:')
||DECODE(SUBSTR(fcrc.class_info,38,1),'0','',':Friday:')
||DECODE(SUBSTR(fcrc.class_info,39,1),'0','',':Saturday:')
, '') scheduling_information
FROM apps.fnd_executables fe
, apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpl
, apps.fnd_concurrent_requests fcr
, apps.fnd_lookups fl
, apps.fnd_responsibility_tl frl
, apps.fnd_user fu
, apps.fnd_conc_req_summary_v fcrs
, apps.fnd_conc_release_classes fcrc
WHERE fe.executable_id = fcp.executable_id
AND fcr.responsibility_application_id = frl.application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND frl.responsibility_id = fcr.responsibility_id
AND fu.user_id = fcr.requested_by
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fl.lookup_code = fe.execution_method_code
AND fcrs.concurrent_program_id = fcr.concurrent_program_id
AND fcrs.request_id = fcr.request_id
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND frl.language = 'US'
AND fcpl.language = 'US'
AND fcr.phase_code IN ('P','R')
AND nvl(fcr.hold_flag,'N') <> 'Y')
Where scheduling_information is not null;

Query/Script to check Oracle Concurrent Program Status

set serveroutput on
declare
l_ret_status boolean;
l_request_id number:=12345678;
l_phase varchar2(200);
l_status varchar2(200);
l_dev_phase varchar2(200);
l_dev_status varchar2(200);
l_message varchar2(200);
l_app varchar2(30) :='FND';
l_conc_prog varchar2(30):='FNDOAMCOL';
begin
l_ret_status := FND_CONCURRENT.GET_REQUEST_STATUS
(request_id =>l_request_id,
appl_shortname => l_app,
program => l_conc_prog,
phase => l_phase,
status => l_status,
dev_phase => l_dev_phase,
dev_status => l_dev_status,
message => l_message);
dbms_output.put_line('Program : '||l_app||':'||l_conc_prog);
dbms_output.put_line('l_request_id : '||l_request_id);
dbms_output.put_line('l_phase : '||l_phase);
dbms_output.put_line('l_dev_phase : '||l_dev_phase);
dbms_output.put_line('l_dev_status : '||l_dev_status);
dbms_output.put_line('l_message : '||l_message);
end;

Tuesday, 27 March 2018

unable to locate jvm.dll while opening jdeveloper

When you run the Jdevloper for first time you may see the below error

ERROR:
Unable to launch the Java Virtual Machine "Located at path:..\..\jdk\jre\bin\client\jvm.dll"

Try the following:
1. go to Control Panel->System->Advanced System Settings->Environmental Variables
2. Update the 'Path' setting to include the following directory: '<jdev home>\jdevbin\jdk\bin'
3. Reopen the jDeveloper.

Thursday, 4 September 2014

Workflow Notification Details

----------------------------------------------
--Workflow Notification Details
----------------------------------------------
select wn.notification_id nid,
        wn.context,
        wn.group_id,
        wn.status,
        wn.mail_status,
        wn.message_type,
        wn.message_name,
        wn.access_key,
        wn.priority,
        wn.begin_date,
        wn.end_date,
        wn.due_date,
        wn.callback,
        wn.recipient_role,
        wn.responder,
        wn.original_recipient,
        wn.from_user,
        wn.to_user,
        wn.subject
from    wf_notifications wn, wf_item_activity_statuses wias
where wn.group_id = wias.notification_id
and wias.item_type = 'APEXP'
and wias.item_key = 'APX123SD';

Workflow: SQL Query to get workflow notification errors within a certain period

--***********************************************************************************

--Workflow: SQL Query to get workflow notification errors within a certain period

--***********************************************************************************
SELECT   IAS.BEGIN_DATE, IAS.ITEM_KEY, AC.NAME ACTIVITY,
         IAS.ACTIVITY_RESULT_CODE RESULT, IAS.ERROR_NAME ERROR_NAME,
         IAS.ERROR_MESSAGE ERROR_MESSAGE
    FROM WF_ITEM_ACTIVITY_STATUSES IAS,
         WF_PROCESS_ACTIVITIES PA,
         WF_ACTIVITIES AC,
         WF_ACTIVITIES AP,
         WF_ITEMS I
   WHERE IAS.ITEM_TYPE = I.ITEM_TYPE
     AND IAS.ACTIVITY_STATUS = 'ERROR'
     AND IAS.PROCESS_ACTIVITY = PA.INSTANCE_ID
     AND PA.ACTIVITY_NAME = AC.NAME
     AND PA.ACTIVITY_ITEM_TYPE = AC.ITEM_TYPE
     AND PA.PROCESS_NAME = AP.NAME
     AND PA.PROCESS_ITEM_TYPE = AP.ITEM_TYPE
     AND PA.PROCESS_VERSION = AP.VERSION
     AND I.ITEM_TYPE = 'APEXP'
     AND I.ITEM_KEY = IAS.ITEM_KEY
     AND I.BEGIN_DATE >= AC.BEGIN_DATE
     AND I.BEGIN_DATE < NVL (AC.END_DATE, I.BEGIN_DATE + 1)
ORDER BY IAS.BEGIN_DATE DESC;

Thursday, 2 May 2013

Procedure : Waiting for child concurrent program to complete

PROCEDURE wait_for_program_completion (p_req_id IN OUT NUMBER) IS
l_success BOOLEAN;
l_phase VARCHAR2 (80); -- phase displyed on screen
l_status VARCHAR2 (80); -- status displayed on screen
l_dev_phase VARCHAR2 (80); -- phase available for developer
l_dev_status VARCHAR2 (80); -- status available for developer
l_err_msg VARCHAR2 (255);
l_req_submitted BOOLEAN;
BEGIN
IF p_req_id <> 0 THEN
l_req_submitted :=
fnd_concurrent.get_request_status
(p_req_id -- Request ID
, NULL
, NULL
, l_phase--Phase displyed on screen
, l_status--Status displayed on screen
, l_dev_phase--Phase for developer
, l_dev_status--Status for developer
, l_err_msg
); -- Execution Message

-- note check Request Status --
WHILE (l_dev_phase != 'COMPLETE') LOOP
l_success :=
fnd_concurrent.wait_for_request
(p_req_id -- Request ID
, 20 --0.5 Mins -- Time Interval
, 1800 --30 Mins -- Total Time to wait
, l_phase -- Phase displyed on screen
, l_status -- Status displayed on screen
, l_dev_phase -- Phase for developer
, l_dev_status -- Status for developer
, l_err_msg
); -- Execution Message
EXIT WHEN (l_dev_status = 'COMPLETE' OR l_success = FALSE);
END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END wait_for_program_completion;

Thursday, 14 March 2013

Query : Concurrent Program Request Status - For Specific Time Duration

SELECT main_prog.description Module
, fcr.hold_flag hold
, fcr.phase_code phase
, fcr.status_code status
, fcr.request_id
, fcr.parent_request_id parent_req_id
, TRUNC( ( NVL( fcr.actual_completion_date, SYSDATE ) - fcr.actual_start_date ) * 24 * 60, 2 ) time_in_mins
, fu.user_name
, fcpl.user_concurrent_program_name conc_program_name
, frl.responsibility_name
, fcr.requested_start_date req_start_date
, TO_CHAR( fcr.actual_start_date, 'DD-MON-YYYY HH:MI AM' ) start_date
, TO_CHAR( fcr.actual_completion_date, 'DD-MON-YYYY HH:MI AM' ) completion_date
, TRUNC( fcr.actual_start_date - fcr.requested_start_date, 2 ) kick_off_time
, fcrs.completion_text
, fcr.argument_text
, fcr.printer
, fcp.concurrent_program_id||'-'||fcp.concurrent_program_name conc_prog_id_code
, fcp.executable_id ||'-' ||fe.execution_method_code||'-'||fe.executable_name ID_CODE_NAME
, fe.execution_file_name
, fl.meaning execution_type
, fe.execution_file_path
, fcr.has_sub_request
, fcr.resubmitted
, fcr.resubmit_interval
, fu.description user_full_name
, fcr.outfile_name
, fcr.logfile_name
FROM apps.fnd_executables fe
, apps.fnd_concurrent_programs fcp
, apps.fnd_concurrent_programs_tl fcpl
, apps.fnd_concurrent_requests fcr
, apps.fnd_lookups fl
, apps.fnd_responsibility_tl frl
, apps.fnd_user fu
, apps.fnd_conc_req_summary_v fcrs
, apps.fnd_lookups main_prog
WHERE fe.executable_id = fcp.executable_id
AND fcp.execution_method_code = fe.execution_method_code
AND fcr.responsibility_application_id = frl.application_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id
AND frl.responsibility_id = fcr.responsibility_id
AND fu.user_id = fcr.requested_by
AND fl.lookup_code = fe.execution_method_code
AND fcrs.concurrent_program_id = fcr.concurrent_program_id
AND fcrs.request_id = fcr.request_id
AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'
AND frl.language = 'US'
AND fcpl.language = 'US'
AND fcp.concurrent_program_name = main_prog.meaning(+)
AND main_prog.lookup_type(+) like 'REPORTERR'
--and fu.user_name = 'USER-IF-REQUIRED'
--and fcr.argument_text like '%Argument Content%'
--AND fcpl.user_concurrent_program_name like '%Any Program Name if Any%'
and fcr.actual_start_date <= to_date('08-Mar-2013 09:40:00','DD-MON-YYYY HH24:MI:SS')--To Time
and nvl(fcr.actual_completion_date,sysdate) >= to_date('08-Mar-2013 09:30:00','DD-MON-YYYY HH24:MI:SS') --From Time
ORDER BY fcr.actual_start_date desc,request_id desc