SELECT (A.EMPLID)"GPID",(SELECT PED.ALTER_EMPLID FROM SYSADM.PS_PERS_DATA_EFFDT PED WHERE PED.EMPLID = A.EMPLID ANDPED.EFFDT = (SELECT MAX(PED1.EFFDT) FROM SYSADM.PS_PERS_DATA_EFFDT PED1 WHERE PED1.EMPLID =PED.EMPLID)) "GID",(A.DT_OF_DEATH)"DATE OF DEATH",B.HR_STATUS,B.EMPL_RCD,B.EFFDT,B.EFFSEQ,B.ACTION,B.ACTION_REASON,B.REG_REGION FROM SYSADM.PS_CURRENT_JOB B, SYSADM.PS_PERSON A WHERE A.EMPLID = B.EMPLID AND ...
April 19, 2016
--The number of users connected on the environment at the moment select count(Nr) USERS_NUMBER FROM ( SELECT count(OPRID) as Nr FROM PSACCESSLOG where to_date(sysdate)-to_date(LOGINDTTM)>=0 and to_date(LOGOUTDTTM)-to_date(sysdate)>=0 group by OPRID) --The users connected on the environment at the moment select DISTINCT OPRID, LOGIPADDRESS, TO_CHAR(LOGINDTTM ,'YYYY-MM-DD:hh:mi:ss') LOGINTIME,TO_CHAR(LOGOUTDTTM ...
April 19, 2016
-- Disciplinary Actions by Reg_region,employee typeSELECT X2.*,X1.DESCR FROM SYSADM.PS_DISCIP_TYPE_TBL X1,(SELECT(SELECT JB.REG_REGION FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)REG_REGION,(SELECT JB.EMPL_TYPE FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)EMPLOYEE_TYPE,A.DISCIPLINE_DT,A.EMPLID,A.PURGE_DT, A.DISCIPLINARY_TYPE FROM SYSADM.PS_DISCIPLIN_ACTN ...
April 19, 2016
Below SQL will return all the records that have PER_ORG_ASGN as parent record in record properties in Application Designer. select RECNAME from PSRECDEFN where PARENTRECNAME = 'PER_ORG_ASGN' Apart from the above direct child records, we will also be interested to know indirect child records. For example PER_ORG_ASGN is parent of JOB and JOB is parent of JOB_JR. To understand the complete hierarchy (i.e) ...
April 19, 2016
create database link "database_link_name"connect to usernameidentified by "password"using 'connection_name';select distinct db_link, created from dba_db_links where db_link='DB LINK NAME';These views are helpful for working with DB links in Oracle:DBA_DB_LINKS - All DB links defined in the databaseALL_DB_LINKS - All DB links the current user has access toUSER_DB_LINKS - All DB links owned by current user ...
