--Organization through Organization Hierarchy
SELECT NAME, TYPE, organization_id
FROM (SELECT hou.NAME, TYPE, organization_id
FROM hr_all_organization_units hou
WHERE organization_id IN (
SELECT organization_id_child
organization_id
FROM (SELECT organization_id_child,
organization_id_parent,
org_structure_version_id
FROM per_org_structure_elements
WHERE org_structure_version_id IN (
SELECT posv.org_structure_version_id
FROM per_organization_structures pos,
per_org_structure_versions posv
WHERE primary_structure_flag =
'Y'
AND pos.organization_structure_id =
posv.organization_structure_id
AND pos.NAME =
:p_x_org_hierarchy))
CONNECT BY organization_id_child =
PRIOR organization_id_parent
START WITH organization_id_child =
:x_organization_id)
AND UPPER (hou.TYPE) = :x_organization_type)
WHERE ROWNUM = 1;
--Find Organization Manager Query
SELECT NVL
((SELECT NVL (papf.person_id, 0)
FROM hr_all_organization_units org,
hr_all_organization_units_tl org_tl,
hr_organization_information org_info2,
per_all_people_f papf
WHERE org_info2.organization_id =
org.organization_id
AND org_info2.org_information_context =
'Organization Name Alias'
AND org_info2.org_information2 = TO_CHAR (papf.person_id(+))
AND org.organization_id = org_tl.organization_id
AND org_tl.LANGUAGE = USERENV ('LANG')
AND org_tl.NAME = :x_org_name
AND papf.current_employee_flag = 'Y'
AND TRUNC (:x_effective_date)
BETWEEN papf.effective_start_date
AND papf.effective_end_date),
0
) val
FROM DUAL;
--Find the List Users that have a particular Responsibility
select rg.user_id,fu.user_name,ppf.employee_number,ppf.full_name,rt.responsibility_name
from fnd_responsibility_tl rt,FND_USER_RESP_GROUPS_DIRECT rg,fnd_user fu, per_all_people_f ppf
where upper(RESPONSIBILITY_NAME) like '%APPROVALS MANAGEMENT BUSINESS ANALYST'
and rt.RESPONSIBILITY_ID = rg.RESPONSIBILITY_ID
and rg.user_id = fu.user_id
and fu.employee_id = ppf.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
--Find total Active employee in the company
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where ppf.current_employee_flag = 'Y'
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number is not null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)
--Get Month Wise Hired Employees Head Count
select to_char(ORIGINAL_DATE_OF_HIRE,'MON') MM, COUNT(PERSON_ID) TOTAL from per_people_x
WHERE to_char(ORIGINAL_DATE_OF_HIRE,'RRRR') = '2017'
group by to_char(ORIGINAL_DATE_OF_HIRE,'MON')
--Provide a List of Active Employees along with their Supervisor’s Name and email address
select ppf.employee_number,ppf.full_name,ppf.email_address ,paaf.supervisor_id,sup.full_name,sup.email_address
from per_all_people_f ppf,per_all_assignments_f paaf,per_people_x sup
where ppf.current_employee_flag = 'Y'
and paaf.supervisor_id (+) = sup.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppf.person_id = paaf.person_id
and ppf.employee_number is not null
--d ppf.employee_number = '39988'
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)
--Get the Employee Salary Increase Summary
select ppf.employee_number,ppf.full_name,ppp.proposed_salary_n,ppp.change_date
from per_pay_proposals ppp,per_all_people_f ppf,per_all_assignments_f paaf
where ppf.person_id = paaf.person_id
and paaf.assignment_id = ppp.assignment_id
and ppf.employee_number = ‘603167’
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and ppp.approved = ‘Y’
and change_date = (select min(change_date) from per_pay_proposals where assignment_id = ppp.assignment_id and proposed_salary_n = ppp.proposed_salary_n)
order by ppp.change_date desc
--Get the History for An Employee’s Transfers in the company
--Get the list of employee who were terminated and have been rehired
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and person_id in
( select person_id
from (
select count(1),person_id
from per_periods_of_service
group by person_id
having count(1) > 1
)
)
--Get the List of Terminated Employees
select ppf.employee_number,ppf.full_name,ppf.email_address
from per_all_people_f ppf
where trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and current_employee_flag is null
and ppf.person_type_id = ( select person_type_id
from per_person_types
where user_person_type = 'Ex-employee'
and business_group_id = ppf.BUSINESS_GROUP_ID
)
--Get an Employees Leave History
select a.date_start,a.date_end,a.absence_days,
(select name from per_ABSENCE_ATTENDANCE_types
where ABSENCE_ATTENDANCE_TYPE_ID = a.ABSENCE_ATTENDANCE_TYPE_ID
and business_group_id =a.business_group_id) leave_type
from per_absence_attendances a
where person_id = (select person_id from per_all_people_f where employee_number = '1000063' and employee_number is not null)
order by date_start desc
--Get EIT Based information for employees
select ppf.employee_number,ppf.full_name, ppei.* from per_all_people_f ppf, per_people_extra_info ppei
where ppf.person_id = ppei.person_id
and trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
and ppf.employee_number = '1000063'
--and ppei.information_type = 'XX_OTHER_DETAILS'
--Get SIT Based information for employees
select * from per_person_analyses ppa,per_analysis_criteria pac
where ppa.ANALYSIS_CRITERIA_ID = pac.ANALYSIS_CRITERIA_ID
and person_id = 115043
and pac.ID_FLEX_NUM =
(
select fis.ID_FLEX_NUM
from FND_ID_FLEXS fif, FND_ID_FLEX_STRUCTURES fis
where fif.ID_FLEX_CODE = fis.ID_FLEX_CODE
and id_flex_structure_code = ‘XX_PROBATION_EVALUATION’
)
--Get the List of all Organization which are below an organization in Org Hierarchy
--Get the List of all Organization which are Above an organization in Org Hierarchy
--Parent and Child Organizations List
select hp.name parent_org, hc.name child_organization
from PER_ORG_STRUCTURE_ELEMENTS pose, PER_ORG_STRUCTURE_VERSIONS posv, hr_all_organization_units hc, hr_all_organization_units hp
where 1=1
and pose.BUSINESS_GROUP_ID = posv.BUSINESS_GROUP_ID
and pose.ORGANIZATION_ID_CHILD = hc.organization_id
and pose.ORGANIZATION_ID_PARENT = hp.organization_id
--Get the Oracle Balance Values
SELECT pdb.defined_balance_id,dim.dimension_name
FROM pay_defined_balances pdb,
pay_balance_types typ,
pay_balance_dimensions dim
WHERE pdb.balance_type_id = typ.balance_type_id
AND pdb.balance_dimension_id = dim.balance_dimension_id
AND dim.legislation_code = ( SELECT legislation_code FROM per_business_groups WHERE business_group_id = 81)
AND UPPER (typ.balance_name) = UPPER ('Indemnity Days')
AND UPPER (dim.dimension_name) = UPPER ('Assignment Run');
select pay_balance_pkg.get_value (P_Balance_ID, P_Assignment_Action_ID, trunc(sysdate))
from dual
--Trunc(Sysdate) will return you balance value as of Date, you can use any old date as per your requirement.
--Disable Responsibilities for Users
--Normally such requirement occurs when you want that no user should be able to logged in during any particular time like some upgrade project or any critical migration activity. You can disable the users by following query. Al though direct updates are not allowed but since this table does not contain object version id column so we used queries mentioned below. Please consult Oracle Support if you want to double check the impact of using this update statement.
update fnd_responsibility a
set a.END_DATE = to_date(’05-APR-2016?)
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)
--In order to reset the responsibilities back to original state
update fnd_responsibility a
set a.END_DATE = NULL
where a.RESPONSIBILITY_KEY in
( SELECT distinct b.RESPONSIBILITY_KEY
FROM FND_USER_RESP_GROUPS A,
FND_RESPONSIBILITY_VL B,
FND_USER C,
PER_ALL_PEOPLE_F D
WHERE A.responsibility_id = B.responsibility_id AND
C.user_id = A.user_id AND
(to_char(A.end_date) IS NULL OR A.end_date > sysdate)
AND C.user_name = D.EMPLOYEE_NUMBER
and (b.RESPONSIBILITY_KEY like ‘%LSG%’)
and C.EMPLOYEE_ID is not null
)
and end_date IS not NULL
and end_date = ’05-APR-2016?
--Provide the List of Employees current and Previous Assignment Details
--(shows Employee Organization Change history. Like wise we can change the query to show the history of Grade, Payroll, Organization, Location, Position, Job change summary)
select
ppf.employee_number
,paaf.assignment_number
,ppf.full_name
,paaf.organization_id current_org_id
,paaf.effective_start_date Curr_org_start_date
,(select name from hr_all_organization_units where organization_id = paaf.organization_id) current_org_name
,paaf_prev.effective_start_date prev_org_start_date
,paaf_prev.organization_id prev_org_id
,(select name from hr_all_organization_units where organization_id = paaf_prev.organization_id) prev_org
from per_all_assignments_f paaf,per_all_assignments_f paaf_prev,pay_people_groups ppg,pay_people_groups ppg_prev,per_all_people_f ppf
where paaf_prev.effective_end_date + 1= paaf.effective_start_date
and paaf_prev.assignment_id = paaf.assignment_id
and paaf_prev.assignment_type = 'E'
and ppf.employee_number = '1000063'
and paaf.assignment_type = 'E'
and paaf.organization_id <> paaf_prev.organization_id
and paaf.PEOPLE_GROUP_ID = ppg.people_group_id
and paaf_prev.PEOPLE_GROUP_ID = ppg_prev.people_group_id
and paaf.effective_start_date between ppf.effective_start_date and ppf.effective_end_date
and paaf.person_id = ppf.person_id
order by paaf_prev.effective_start_date desc
--Employee Short Leave Details and Hours Calculation
select paa.person_id,paa.date_start, paa.date_end,time_start,time_end
,round ( ( ((substr(time_end,1,2) -substr(time_start,1,2)) * 60
+ (substr(time_end,4,2) -substr(time_start,4,2))) / 60 ) ,2) Hours
from per_absence_attendances paa,per_absence_Attendance_types paat
where paa.person_id = 68567
and paa.absence_attendance_type_id = paat.absence_attendance_type_id
and paat.name = 'Short Leave'
and paa.date_start between :P_Period_Start_date and :P_Period_End_date
and paa.date_end between :P_Period_Start_date and :P_Period_End_date
Thursday, June 13, 2019
Concurrent Request Statistics
--- Concurrent Request ran in 24 Hours
SELECT fcr.request_id "Request ID",
fcp.user_concurrent_program_name "Program",
-- fcr.argument_text " Parameters",
fu.user_name "Username",
fr.responsibility_name "Responsbility",
fcr.actual_start_date "Starting Time",
fcr.actual_completion_date "Ending Time",
TRUNC((fcr.actual_completion_date - fcr.actual_start_date) * 1440, 2) "Hours Minutes"
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_vl fcp,
fnd_responsibility_vl fr,
fnd_user fu
WHERE actual_start_date LIKE SYSDATE-1
AND fu.user_id = fcr.requested_by
--AND (SYSDATE - actual_start_date) * 24 > 1
AND status_code = 'C'
AND phase_code = 'C'
--AND fu.user_name='MJUBARA'
and fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
and fu.user_id = fcr.requested_by
and fr.responsibility_id = fcr.responsibility_id
order by "Hours Minutes" DESC
--- CONCURRENT REQUESTS COMPLETED WITH ERROR
SELECT requestor, a.request_id request_id
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'DD/MON/YYYY HH:MI') start_time
, TO_CHAR(a.actual_completion_date,'DD/MON/YYYY HH:MI') end_time
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
, argument_text Parameters
, completion_text Completion_Error
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE -1)
AND a.status_code IN ('E')
ORDER BY actual_start_date
---Concurrent Requests Completed With Warning
SELECT requestor, a.request_id request_id
, SUBSTR(a.user_concurrent_program_name,1,50) name
, TO_CHAR(a.actual_start_date,'DD/MON/YYYY HH:MI') start_time
, TO_CHAR(a.actual_completion_date,'DD/MON/YYYY HH:MI') end_time
, DECODE(a.phase_code, 'R'
,'Running', 'P'
,'Inactive', 'C'
,'Completed', a.phase_code) phase_code
, DECODE(a.status_code, 'E'
,'Error', 'C'
,'Normal', 'X'
,'Terminated', 'Q'
,'On Hold', 'D'
,'Cancelled', 'G'
,'Warning', 'R'
,'Normal', 'W'
,'Paused', a.status_code) status_code
, argument_text Parameters
, completion_text Completion_Error
FROM apps.fnd_conc_req_summary_v a
WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE -1)
AND a.status_code IN ('G')
ORDER BY actual_start_date
---Concurrent Statistics 24 Hours
SELECT count (fcr.request_id) as "Request Count",
fcp.user_concurrent_program_name "Program",
fu.user_name "Username",
TRUNC((fcr.actual_completion_date - fcr.actual_start_date) * 1440, 2) "Minutes"
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs_vl fcp,
fnd_user fu
WHERE actual_start_date LIKE SYSDATE-1
AND fu.user_id = fcr.requested_by
--AND (SYSDATE - actual_start_date) * 24 > 1
AND status_code = 'C'
AND phase_code = 'C'
and fcr.CONCURRENT_PROGRAM_ID = fcp.CONCURRENT_PROGRAM_ID
and fu.user_id = fcr.requested_by
group by fcp.user_concurrent_program_name, fu.user_name ,fcr.actual_completion_date,fcr.actual_start_date
Concurrent Program Statestics
select CONC.USER_CONCURRENT_PROGRAM_NAME,
CONC.CONCURRENT_PROGRAM_NAME,
CONC.ENABLED_FLAG,
stat.LAST_RUN_DATE,
stat.LAST_RUN_REQUEST_ID,
stat.OUTCOME,
stat.SUCCESSFUL_COMPLETION,
stat.WARNING_COMPLETION,
stat.ERROR_COMPLETION,
stat.MIN_RUN_TIME,
stat.MAX_RUN_TIME,
stat.AVG_RUN_TIME,
stat.ON_FAILURE_LOG_LEVEL,
(select meaning
from fnd_lookups FL
where FL.lookup_code = stat.OUTCOME
AND FL.LOOKUP_TYPE = 'CP_STATUS_CODE') MEANING,
stat.SHELF_LIFE,
stat.CONNSTR1,
stat.NODE_NAME1
from FND_CONCURRENT_PROGRAMS_VL CONC,
FND_APPLICATION_VL APP,
FND_CONC_PROG_ONSITE_INFO stat
where CONC.APPLICATION_ID = APP.APPLICATION_ID
AND STAT.CONCURRENT_PROGRAM_ID = CONC.CONCURRENT_PROGRAM_ID
AND CONC.APPLICATION_ID = STAT.PROGRAM_APPLICATION_ID
AND CONC.ENABLED_FLAG = 'Y'
CONC.CONCURRENT_PROGRAM_NAME,
CONC.ENABLED_FLAG,
stat.LAST_RUN_DATE,
stat.LAST_RUN_REQUEST_ID,
stat.OUTCOME,
stat.SUCCESSFUL_COMPLETION,
stat.WARNING_COMPLETION,
stat.ERROR_COMPLETION,
stat.MIN_RUN_TIME,
stat.MAX_RUN_TIME,
stat.AVG_RUN_TIME,
stat.ON_FAILURE_LOG_LEVEL,
(select meaning
from fnd_lookups FL
where FL.lookup_code = stat.OUTCOME
AND FL.LOOKUP_TYPE = 'CP_STATUS_CODE') MEANING,
stat.SHELF_LIFE,
stat.CONNSTR1,
stat.NODE_NAME1
from FND_CONCURRENT_PROGRAMS_VL CONC,
FND_APPLICATION_VL APP,
FND_CONC_PROG_ONSITE_INFO stat
where CONC.APPLICATION_ID = APP.APPLICATION_ID
AND STAT.CONCURRENT_PROGRAM_ID = CONC.CONCURRENT_PROGRAM_ID
AND CONC.APPLICATION_ID = STAT.PROGRAM_APPLICATION_ID
AND CONC.ENABLED_FLAG = 'Y'
Concurrent Managers Status
/* Checking concurrent programs running currently with Details of Processed time and Start Date */
SELECT DISTINCT
c.USER_CONCURRENT_PROGRAM_NAME,
ROUND ( ( (SYSDATE - a.actual_start_date) * 24 * 60 * 60 / 60), 2)
AS Process_time,
a.request_id,
a.parent_request_id,
a.request_date,
a.actual_start_date,
a.actual_completion_date,
(a.actual_completion_date - a.request_date) * 24 * 60 * 60
AS end_to_end,
(a.actual_start_date - a.request_date) * 24 * 60 * 60 AS lag_time,
d.user_name,
a.phase_code,
a.status_code,
a.argument_text,
a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id = b.concurrent_program_id
AND b.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = d.user_id
AND status_code = 'R'
ORDER BY Process_time DESC;
/* For checking the locks in concurrent jobs */
SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess,
inst_id,
id1,
id2,
lmode,
request,
TYPE
FROM gV$LOCK
WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE
FROM gV$LOCK
WHERE request > 0)
ORDER BY id1, request;
SELECT DISTINCT
c.USER_CONCURRENT_PROGRAM_NAME,
ROUND ( ( (SYSDATE - a.actual_start_date) * 24 * 60 * 60 / 60), 2)
AS Process_time,
a.request_id,
a.parent_request_id,
a.request_date,
a.actual_start_date,
a.actual_completion_date,
(a.actual_completion_date - a.request_date) * 24 * 60 * 60
AS end_to_end,
(a.actual_start_date - a.request_date) * 24 * 60 * 60 AS lag_time,
d.user_name,
a.phase_code,
a.status_code,
a.argument_text,
a.priority
FROM apps.fnd_concurrent_requests a,
apps.fnd_concurrent_programs b,
apps.FND_CONCURRENT_PROGRAMS_TL c,
apps.fnd_user d
WHERE a.concurrent_program_id = b.concurrent_program_id
AND b.concurrent_program_id = c.concurrent_program_id
AND a.requested_by = d.user_id
AND status_code = 'R'
ORDER BY Process_time DESC;
/* For checking the locks in concurrent jobs */
SELECT DECODE (request, 0, 'Holder: ', 'Waiter: ') || sid sess,
inst_id,
id1,
id2,
lmode,
request,
TYPE
FROM gV$LOCK
WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE
FROM gV$LOCK
WHERE request > 0)
ORDER BY id1, request;
Compile Forms command line
TEST
f60gen module=/u5/applupg2/uatappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u5/applupg2/uatappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special
PROD
f60gen module=/u3/applprd/prdappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u3/applprd/prdappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special
f60gen module=/u5/applupg2/uatappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u5/applupg2/uatappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special
PROD
f60gen module=/u3/applprd/prdappl/au/11.5.0/forms/US/APXINWKB.fmb userid=APPS/APPS
output_file=/u3/applprd/prdappl/ap/11.5.0/forms/US/APXINWKB.fmx
module_type=form
batch=no
compile_all=special
Concurrent Manager Cleanup Script
====================================================================
REM
REM FILENAME
REM cmclean.sql
REM DESCRIPTION
REM Clean out the concurrent manager tables
REM NOTES
REM Usage: sqlplus <apps_user/apps_passwd> @cmclean
REM
REM
REM $Id: cmclean.sql,v 1.4 2001/04/07 15:55:07 pferguso Exp $
REM
REM
REM +======================================================================+
set verify off;
set head off;
set timing off
set pagesize 1000
column manager format a20 heading 'Manager short name'
column pid heading 'Process id'
column pscode format a12 heading 'Status code'
column ccode format a12 heading 'Control code'
column request heading 'Request ID'
column pcode format a6 heading 'Phase'
column scode format a6 heading 'Status'
WHENEVER SQLERROR EXIT ROLLBACK;
DOCUMENT
WARNING : Do not run this script without explicit instructions
from Oracle Support
*** Make sure that the managers are shut down ***
*** before running this script ***
*** If the concurrent managers are NOT shut down, ***
*** exit this script now !! ***
#
accept answer prompt 'If you wish to continue type the word ''dual'': '
set feed off
select null from &answer;
set feed on
REM Update process status codes to TERMINATED
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid process status codes in FND_CONCURRENT_PROCESSES
set feedback off
set head on
break on manager
SELECT concurrent_queue_name manager,
concurrent_process_id pid,
process_status_code pscode
FROM fnd_concurrent_queues fcq, fnd_concurrent_processes fcp
WHERE process_status_code not in ('K', 'S')
AND fcq.concurrent_queue_id = fcp.concurrent_queue_id
AND fcq.application_id = fcp.queue_application_id;
set head off
set feedback on
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');
REM Set all managers to 0 processes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating running processes in FND_CONCURRENT_QUEUES
prompt -- Setting running_processes = 0 and max_processes = 0 for all managers
UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;
REM Reset control codes
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating invalid control_codes in FND_CONCURRENT_QUEUES
set feedback off
set head on
SELECT concurrent_queue_name manager,
control_code ccode
FROM fnd_concurrent_queues
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
set feedback on
set head off
UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;
REM Also null out target_node for all managers
UPDATE fnd_concurrent_queues
SET target_node = null;
REM Set all 'Terminating' requests to Completed/Error
REM Also set Running requests to completed, since the managers are down
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Running or Terminating requests to Completed/Error
set feedback off
set head on
SELECT request_id request,
phase_code pcode,
status_code scode
FROM fnd_concurrent_requests
WHERE status_code = 'T' OR phase_code = 'R'
ORDER BY request_id;
set feedback on
set head off
UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R';
REM Set all Runalone flags to 'N'
REM This has to be done differently for Release 10
prompt
prompt ------------------------------------------------------------------------
prompt -- Updating any Runalone flags to 'N'
prompt
set serveroutput on
set feedback off
declare
c pls_integer := dbms_sql.open_cursor;
upd_rows pls_integer;
vers varchar2(50);
tbl varchar2(50);
col varchar2(50);
statement varchar2(255);
begin
select substr(release_name, 1, 2)
into vers
from fnd_product_groups;
if vers >= 11 then
tbl := 'fnd_conflicts_domain';
col := 'runalone_flag';
else
tbl := 'fnd_concurrent_conflict_sets';
col := 'run_alone_flag';
end if;
statement := 'update ' || tbl || ' set ' || col || '=''N'' where ' || col || ' = ''Y''';
dbms_sql.parse(c, statement, dbms_sql.native);
upd_rows := dbms_sql.execute(c);
dbms_sql.close_cursor(c);
dbms_output.put_line('Updated ' || upd_rows || ' rows of ' || col || ' in ' || tbl || ' to ''N''');
end;
/
prompt
prompt ------------------------------------------------------------------------
prompt Updates complete.
prompt Type commit now to commit these updates, or rollback to cancel.
prompt ------------------------------------------------------------------------
prompt
set feedback on
REM <= Last REM statment -----------------------------------------------------
Check Materialized view Refresh
SELECT owner, mview_name, last_refresh_date
FROM all_mviews
where mview_name like 'MATERIALIZED_VIEW_NAME'
Subscribe to:
Posts (Atom)
Some Tips About FNDLOAD
Data Synchronization Data Synchronization is a process in which some setup data would be synchronized, and this would be more important w...
-
Check out this job at Tabadul: Oracle SOA / BPM / Fusion Middleware Consultant https://www.linkedin.com/jobs2/view/197437126
-
How to customize Java Virtual Machine Settings in Oracle WebLogic Server To achieve the best performance of the application and avoid perfor...
-
This index provides a single, easy-to-navigate comprehensive library of Oracle Database resources. Quickly and easily access the latest aler...