獲取財務系統設置審批鏈相關信息SQL

SELECT DISTINCT PS.POSITION_STRUCTURE_ID 審批鏈ID,
PS.NAME 審批鏈名稱,
PE.POS_STRUCTURE_ELEMENT_ID POS_STRUCTURE_ELEMENT_ID,
HP.POSITION_ID 職位ID,
HP.NAME 職位名,
PPF.PERSON_ID 員工ID,
PPF.LAST_NAME 用戶名,
PE.ORDER_FLAG ORDER_FLAG
FROM PER_POS_STRUCTURE_VERSIONS PV,
PER_POSITION_STRUCTURES PS,
(SELECT POS_STRUCTURE_VERSION_ID,
POS_STRUCTURE_ELEMENT_ID,
PARENT_POSITION_ID,
ORDER_FLAG
FROM (SELECT PE.POS_STRUCTURE_VERSION_ID,
PE.POS_STRUCTURE_ELEMENT_ID,
PE.PARENT_POSITION_ID,
'1' ORDER_FLAG
FROM PER_POS_STRUCTURE_ELEMENTS PE
UNION ALL
SELECT PE.POS_STRUCTURE_VERSION_ID,
PE.POS_STRUCTURE_ELEMENT_ID,
PE.SUBORDINATE_POSITION_ID PARENT_POSITION_ID,
'2' ORDER_FLAG
FROM PER_POS_STRUCTURE_ELEMENTS PE
WHERE NOT EXISTS (SELECT 1
FROM PER_POS_STRUCTURE_ELEMENTS PE2
WHERE PE2.POS_STRUCTURE_VERSION_ID =
PE.POS_STRUCTURE_VERSION_ID
AND PE.SUBORDINATE_POSITION_ID =
PE2.PARENT_POSITION_ID))) PE,
PER_PEOPLE_F PPF,
PER_ALL_ASSIGNMENTS_F PAF,
HR_POSITIONS_F HP
WHERE PV.POS_STRUCTURE_VERSION_ID = PE.POS_STRUCTURE_VERSION_ID
AND PV.POSITION_STRUCTURE_ID = PS.POSITION_STRUCTURE_ID
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PE.PARENT_POSITION_ID = HP.POSITION_ID
AND HP.POSITION_ID = PAF.POSITION_ID
AND (PAF.EFFECTIVE_END_DATE IS NULL OR PAF.EFFECTIVE_END_DATE > SYSDATE)
AND (HP.EFFECTIVE_END_DATE IS NULL OR HP.EFFECTIVE_END_DATE > SYSDATE)
AND (PPF.EFFECTIVE_END_DATE IS NULL OR PPF.EFFECTIVE_END_DATE > SYSDATE)
AND PAF.EFFECTIVE_END_DATE >= SYSDATE
–AND PS.NAME = '000100預算申請審批層次'
ORDER BY PS.POSITION_STRUCTURE_ID,
PE.POS_STRUCTURE_ELEMENT_ID,
PE.ORDER_FLAG;