VIEW: APPS.PSB_POSITION_ASSIGNMENTS_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)