Select DISTINCT A.position_nbr From Sysadm.Ps_Position_Data A Where
A.Effdt = (Select Max(A1.Effdt) From Sysadm.Ps_Position_Data A1 Where A.Position_Nbr =A1.Position_Nbr)
And A.Eff_Status = 'A'
And A.Position_Nbr Not In ( Select Distinct Position_Nbr From Sysadm.Ps_Current_Job)) Aa Where Aa.Position_Nbr Not In
(Select distinct position_nbr From Sysadm.Ps_Job Where Effdt Between Sysdate-180 And Sysdate
and position_nbr in (Select position_nbr From Sysadm.Ps_Position_Data A Where
A.Effdt = (Select Max(A1.Effdt) From Sysadm.Ps_Position_Data A1 Where A.Position_Nbr =A1.Position_Nbr)
And A.Eff_Status = 'A'
And A.Position_Nbr Not In ( Select Distinct Position_Nbr From Sysadm.Ps_Current_Job)));

Continue reading
    Share:
select A.EMPLID,A.ACCOUNT_ID,A.ACCOUNT_NAME,A.EFF_STATUS,A.ACCOUNT_TYPE_PYE,A.COUNTRY_CD,A.BANK_CD,A.BRANCH_EC_CD,A.ACCOUNT_EC_ID,CHECK_DIGIT,
A.CURRENCY_CD,A.IAT_IND,A.ACCOUNT_NAME_AC from sysadm.PS_PYE_BANKACCT A WHERE A.EMPLID IN
(SELECT DISTINCT B.EMPLID FROM SYSADM.PS_JOB B WHERE B.REG_REGION = 'TWN') ORDER BY 1;
Continue reading
    Share:
Disciplinary Actions by Reg_region,employee type

SELECT 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.COMPANY FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND
A.EMPLID = JB.EMPLID)COMPANY,
(SELECT JB.BUSINESS_UNIT FROM SYSADM.PS_CURRENT_JOB JB WHERE EMPL_RCD =0 AND A.EMPLID = JB.EMPLID)BUSINESS_UNIT,

(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 A ORDER BY 1,2,3 ASC)X2 WHERE X2.DISCIPLINARY_TYPE = X1.DISCIPLINARY_TYPE
AND X2.REG_REGION = X1.REG_REGION
AND X1.EFFDT =(SELECT MAX(X3.EFFDT) FROM SYSADM.PS_DISCIP_TYPE_TBL X3 WHERE X1.DISCIPLINARY_TYPE = X3.DISCIPLINARY_TYPE
AND
X3.REG_REGION = X1.REG_REGION AND X3.EFFDT < SYSDATE) AND
X2.REG_REGION IN ('AUS','CHN','IND','IDN','NZL','ZAF','VNM','TWN','THA','PHL','JPN');
Continue reading
    Share: