Find Applied Patch Set Information Query
select
fav.application_name app_name
, fav.application_short_name app_s_name
, decode(fpi.status, 'I', 'Yes',
'S', 'Shared',
'N', 'No', fpi.status) inst_status
, nvl(fpi.patch_level, 'Not Available') patchset
, fav.application_id app_id
from apps.fnd_application_vl fav, apps.fnd_product_installations fpi
where fav.application_id = fpi.application_id
--and fav.application_name like '%Secu%Enterprise%'
and fav.application_short_name = 'PER'
order by 3;
############################################################
##########################################################3
Remove Junk Characters Function
CREATE OR REPLACE FUNCTION xxx_ascii_only_vipul (p_txt IN VARCHAR2)
RETURN VARCHAR2
IS
v_tmp VARCHAR2 (32767);
v_clean VARCHAR2 (32767);
v_char VARCHAR2 (3 BYTE);
BEGIN
FOR i IN 1 .. LENGTH (p_txt) LOOP
v_char := SUBSTR (p_txt, i, 1);
IF (ASCII (v_char) BETWEEN 32 AND 127)
OR (ASCII (v_char) IN (9, 10, 13)) THEN
v_clean := v_clean || v_char;
END IF;
END LOOP;
IF LENGTH (v_clean) != LENGTH (p_txt) THEN
DBMS_OUTPUT.put_line ('removed '||TO_CHAR(LENGTH(p_txt) - LENGTH(v_clean))||' characters');
END IF;
RETURN v_clean;
END;
/
##############################################################
####################################################3
Query for Ledger, OU, Legal Enity, balancing segment
SELECT DISTINCT hrl.country,
hroutl_bg.NAME bg,
hroutl_bg.organization_id,
lep.legal_entity_id,
lep.NAME legal_entity,
hroutl_ou.NAME ou_name,
hroutl_ou.organization_id org_id,
hrl.location_id,
hrl.location_code,
glev.FLEX_SEGMENT_VALUE
FROM xle_entity_profiles lep,
xle_registrations reg,
hr_locations_all hrl,
hz_parties hzp,
fnd_territories_vl ter,
hr_operating_units hro,
hr_all_organization_units_tl hroutl_bg,
hr_all_organization_units_tl hroutl_ou,
hr_organization_units hou,
gl_legal_entities_bsvs glev
WHERE lep.transacting_entity_flag = 'Y'
AND lep.party_id = hzp.party_id
AND lep.legal_entity_id = reg.source_id
AND reg.source_table = 'XLE_ENTITY_PROFILES'
AND hrl.location_id = reg.location_id
AND reg.identifying_flag = 'Y'
AND ter.territory_code = hrl.country
AND lep.legal_entity_id = hro.default_legal_context_id
AND hou.organization_id = hro.organization_id
AND hroutl_bg.organization_id = hro.business_group_id
AND hroutl_ou.organization_id = hro.organization_id
AND glev.legal_entity_id = lep.legal_entity_id
AND hou.organization_id =241
#################################################################
###################################################################
API to get DEPENDENT OBEJCTS
API to get DEPENDENT OBEJCTS:
DBMS_UTILITY.GET_DEPENDENCY
type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2);
SELECT * FROM ALL_DEPENDENCIES
Using above method, we can find out the Dependent Objects.
###############################################################
###################################################3
Find Locks on Packages/Tables and Release them:
Find Locks on Packages/Tables and Release them:
SELECT l.session_id||','||v.serial# sid_serial,
l.ORACLE_USERNAME ora_user,
o.object_name,
o.object_type,
DECODE(l.locked_mode,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive',
TO_CHAR(l.locked_mode)
) lock_mode,
o.status,
to_char(o.last_ddl_time,'dd.mm.yy') last_ddl
FROM dba_objects o, gv$locked_object l, v$session v
WHERE o.object_id = l.object_id
and l.SESSION_ID=v.sid
order by 2,3;
SELECT * FROM DBA_DDL_LOCKS
WHERE NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')
SELECT * FROM DBA_OBJECTS WHERE OBJECT_NAME IN ('XXX_OBJECT_NAME' ,'XXX_OBJECT_NAME')
SELECT * FROM SYSTEM.V$SESSION
WHERE SID = 2883 OR ROW_WAIT_OBJ# IN (374295,373803,382481,382490)
ALTER SESSION SET CURRENT_SCHEMA=SYS
ALTER SYSTEM KILL SESSION '1586,395'
ALTER SYSTEM FLUSH BUFFER_CACHE
ALTER SYSTEM FLUSH SHARED_POOL
ALTER SESSION SET CURRENT_SCHEMA=APPS
###############################################################
###############################################################DFF Deletion API:
DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEX_CONTEXT_CODE, DESCRIPTIVE_FLEXFIELD_NAME, APPLICATION_ID
FROM FND_DESCR_FLEX_CONTEXTS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_CONTEXTS_PKG.DELETE_ROW (
V1.APPLICATION_ID,
V1.DESCRIPTIVE_FLEXFIELD_NAME,
V1.DESCRIPTIVE_FLEX_CONTEXT_CODE);
END LOOP;
END;
-----------
DECLARE
CURSOR C1 IS
SELECT APPLICATION_COLUMN_NAME,DESCRIPTIVE_FLEX_CONTEXT_CODE,DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCR_FLEX_COLUMN_USAGES
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCR_FLEX_COL_USAGE_PKG.DELETE_ROW (
V1.APPLICATION_ID,
V1.DESCRIPTIVE_FLEXFIELD_NAME,
V1.DESCRIPTIVE_FLEX_CONTEXT_CODE,
V1.APPLICATION_COLUMN_NAME);
END LOOP;
END;
-----------
DECLARE
CURSOR C1 IS
SELECT DESCRIPTIVE_FLEXFIELD_NAME,APPLICATION_ID
FROM FND_DESCRIPTIVE_FLEXS
WHERE DESCRIPTIVE_FLEXFIELD_NAME = 'XXXXXXXXXXXXXX';
BEGIN
FOR V1 IN C1
LOOP
FND_DESCRIPTIVE_FLEXS_PKG.DELETE_ROW (
V1.APPLICATION_ID,
V1.DESCRIPTIVE_FLEXFIELD_NAME);
END LOOP;
END;
#######################################################3
##########################################################
No comments:
Post a Comment