Product: | PSB - Public Sector Budgeting (Obsolete) |
---|---|
Description: | |
Implementation/DBA Data: | Not implemented in this database |
SELECT PAS.ROWID
, PAS.POSITION_ASSIGNMENT_ID
, PAS.ELEMENT_VALUE_TYPE
, PAS.DATA_EXTRACT_ID
, PAS.WORKSHEET_ID
, PAS.POSITION_ID
, PAS.ASSIGNMENT_TYPE
, PAS.ATTRIBUTE_ID
, PAS.ATTRIBUTE_VALUE_ID
, PAS.ATTRIBUTE_VALUE
, PAS.PAY_ELEMENT_ID
, PAS.PAY_ELEMENT_OPTION_ID
, PAS.PAY_ELEMENT_RATE_ID
, PAS.EFFECTIVE_START_DATE
, PAS.EFFECTIVE_END_DATE
, PAS.ELEMENT_VALUE
, PAS.GLOBAL_DEFAULT_FLAG
, PAS.ASSIGNMENT_DEFAULT_RULE_ID
, PAS.MODIFY_FLAG
, PAS.PAY_BASIS
, PAS.EMPLOYEE_ID
, PAS.PRIMARY_EMPLOYEE_FLAG
, PAS.CURRENCY_CODE
, PAS.LAST_UPDATE_DATE
, PAS.LAST_UPDATED_BY
, PAS.LAST_UPDATE_LOGIN
, PAS.CREATED_BY
, PAS.CREATION_DATE
FROM PSB_POSITION_ASSIGNMENTS PAS
WHERE (PSB_POSITIONS_PVT.GET_WORKSHEET_FLAG = 'Y'
AND ( ( WORKSHEET_ID =PSB_POSITIONS_PVT.GET_WORKSHEET_ID ) OR ( WORKSHEET_ID IS NULL
AND ASSIGNMENT_TYPE = 'ATTRIBUTE'
AND NOT EXISTS (SELECT 1
FROM PSB_POSITION_ASSIGNMENTS C
WHERE ( ( NVL(C.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE+1) BETWEEN NVL(PAS.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE)
AND NVL(PAS.EFFECTIVE_END_DATE
, NVL(PSB_POSITIONS_PVT.GET_END_DATE
, C.EFFECTIVE_START_DATE ))) OR ( NVL(PAS.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE+1) BETWEEN NVL(C.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE)
AND NVL(C.EFFECTIVE_END_DATE
, NVL(PSB_POSITIONS_PVT.GET_END_DATE
, PAS.EFFECTIVE_START_DATE ))) )
AND C.POSITION_ID = PAS.POSITION_ID
AND C.ATTRIBUTE_ID = PAS.ATTRIBUTE_ID
AND C.WORKSHEET_ID = PSB_POSITIONS_PVT.GET_WORKSHEET_ID) ) OR ( WORKSHEET_ID IS NULL
AND ASSIGNMENT_TYPE = 'ELEMENT'
AND NOT EXISTS (SELECT 1
FROM PSB_POSITION_ASSIGNMENTS C
WHERE ( ( NVL(C.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE+1) BETWEEN NVL(PAS.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE)
AND NVL(PAS.EFFECTIVE_END_DATE
, NVL(PSB_POSITIONS_PVT.GET_END_DATE
, C.EFFECTIVE_START_DATE ))) OR ( NVL(PAS.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE+1) BETWEEN NVL(C.EFFECTIVE_START_DATE
, PSB_POSITIONS_PVT.GET_END_DATE)
AND NVL(C.EFFECTIVE_END_DATE
, NVL(PSB_POSITIONS_PVT.GET_END_DATE
, PAS.EFFECTIVE_START_DATE ))) )
AND C.POSITION_ID = PAS.POSITION_ID
AND C.PAY_ELEMENT_ID = PAS.PAY_ELEMENT_ID
AND C.WORKSHEET_ID =PSB_POSITIONS_PVT.GET_WORKSHEET_ID) ) OR ( WORKSHEET_ID IS NULL
AND ASSIGNMENT_TYPE = 'EMPLOYEE'
AND NOT EXISTS (SELECT 1
FROM PSB_POSITION_ASSIGNMENTS C
WHERE C.POSITION_ID = PAS.POSITION_ID
AND C.PRIMARY_EMPLOYEE_FLAG = 'Y'
AND C.WORKSHEET_ID =PSB_POSITIONS_PVT.GET_WORKSHEET_ID) ) )) OR (PSB_POSITIONS_PVT.GET_WORKSHEET_FLAG = 'N'
AND WORKSHEET_ID IS NULL)