krishnamraju

Helpful Queries --Alter Date format to display date + time alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'; --Alter session for sysadm access alter session set current_schema=sysadm; --Any batch process (Finance) Select a.RUNSTATUS, a.* from psprcsrqst a where jobinstance = '4111574' order by prcsjobseq desc; Select a.RUNSTATUS, a.* from psprcsrqst a where PRCSNAME='VCH_EMAIL’; --PIA Operator details SELECT * FROM psoprdefn WHERE oprid = ' '; --LDAP directory setup Select * from ps_DIRECTORYATTRIB; Select * from ps_DIRECTORYSETUP; --To see the current accesses to database Select * from v$session where type <> 'BACKGROUND'; Select * from v$process; --To see the indexes and other characteristics of columns Select * FROM dba_ind_columns Where table_name='PS_PROJ_RESOURCE'; Select * from PSINDEXDEFN where recname = 'PROJ_RESOURCE'; --To see object modification details Select * from dba_objec

select trunc((to_number(to_char(sysdate,'yyyymmdd'))-to_number(to_char(BIRTHDATE,'yyyymmdd')))/10000) AGE, BIRTHDATE from PS_DEP_BEN WHERE EMPLID = '6508';

INSERT INTO %Table(IIE_OE_TMP)(  SELECT DISTINCT %ProcessInstance  , PER.EMPLID  , PER.DEPENDENT_BENEF  , %DateOut(PLN.COVERAGE_BEGIN_DT )  ,PLN.COVERAGE_ELECT  ,CASE WHEN JOB.EMPL_STATUS IN ('A'  ,'P'  ,'S')    AND JOB.FULL_PART_TIME = 'F' THEN 'FT' WHEN JOB.EMPL_STATUS IN ('A'    ,'P'    ,'S')    AND JOB.FULL_PART_TIME = 'P' THEN 'PT' WHEN JOB.EMPL_STATUS = 'L' THEN 'L1' END    , (CASE PER.RELATIONSHIP WHEN ' ' THEN '18' WHEN 'SP' THEN '01' WHEN 'C' THEN '19' WHEN 'NA' THEN '53' ELSE ' ' END)    , PER.NATIONAL_ID    , PER.LAST_NAME    , PER.FIRST_NAME    , PER.MIDDLE_NAME    , PER.NAME_PREFIX    , PER.NAME_SUFFIX    , PER.NATIONAL_ID    , PER.ADDRESS1    , PER.ADDRESS2    , PER.CITY    , PER.STATE    , PER.POSTAL    , PER.COUNTRY    , %DateOut(PER.BIRTHDATE )    , PER.SEX    ,PER.DISABL

The below SQL will give all the records for a given component. SELECT DISTINCT (recname) FROM psrecdefn WHERE recname IN (SELECT DISTINCT (recname) FROM pspnlfield WHERE pnlname IN (SELECT DISTINCT (b.pnlname) FROM pspnlgroup a, pspnlfield b WHERE (   a.pnlname = b.pnlname OR a.pnlname =b.subpnlname) AND a.pnlgrpname = 'JOB_DATA'                 -- specify your component name) AND recname <> ' ') UNION SELECT DISTINCT (recname) FROM pspnlfield WHERE pnlname IN (SELECT DISTINCT (b.subpnlname) FROM pspnlgroup a,pspnlfield b WHERE (a.pnlname = b.pnlname OR a.pnlname = b.subpnlname ) AND a.pnlgrpname = 'JOB_DATA')               -- specify your component name) AND recname <> ' ') AND rectype = '0'                                                 -- specify record type order by recname asc;

SQL: SELECT REC.RECNAME,REC.FIELDNAME,CASE WHEN FLD.FIELDTYPE = 0  THEN 'Char'  WHEN FLD.FIELDTYPE = 1  THEN 'Long Char' WHEN FLD.FIELDTYPE = 2  THEN 'Num' WHEN FLD.FIELDTYPE = 3  THEN 'Sign Num' WHEN FLD.FIELDTYPE = 4  THEN 'Date' WHEN FLD.FIELDTYPE = 5  THEN 'Time' WHEN FLD.FIELDTYPE = 6  THEN 'Date Time' WHEN FLD.FIELDTYPE = 8  THEN 'Image' WHEN FLD.FIELDTYPE = 9  THEN 'Image Ref' END ,FLD.LENGTH,REC.EDITTABLE FROM PSRECFIELD REC,PSDBFIELD FLD  WHERE REC.FIELDNAME = FLD.FIELDNAME AND RECNAME = 'IIE_INT_MAP_TBL' ORDER BY FIELDNUM

The Below SQL is useful to calculate the employee age who are having greater than 26 years:  AND (PER.RELATIONSHIP IN('X','XN')     OR ((TRUNC((to_number(TO_CHAR(%CurrentDateIn ,'yyyymmdd'))-to_number(TO_CHAR(PER.BIRTHDATE ,'yyyymmdd')))/10000)) > 26    AND PER.DISABLED ='N' AND PER.RELATIONSHIP ='C' ) )  AND (    PER.RELATIONSHIP IN('X','XN')  OR    ((TRUNC((to_number(TO_CHAR(%CurrentDateIn ,'yyyymmdd'))-to_number(TO_CHAR(PER.BIRTHDATE ,'yyyymmdd')))/10000)) > 26    AND PER.DISABLED ='N'   AND PER.RELATIONSHIP ='C' )     OR (PLN.COVERAGE_ELECT='T'  AND PLN.COVERAGE_BEGIN_DT <= %Bind(IIE_WEEK_RUN_DATE)) ) AND (    PER.RELATIONSHIP IN('X','XN')   OR ( ( (TRUNC((to_number(TO_CHAR(%CurrentDateIn ,'yyyymmdd'))-to_number(TO_CHAR(PER.BIRTHDATE ,'yyyymmdd')))/10000)) > 26    AND PER.DISABLED ='N'    AND