VIEW: APPS.PSB_POSITION_ASSIGN_SALARY_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.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.currency_code, pas.employee_id, pas.last_update_date, pas.last_updated_by, pas.last_update_login, pas.created_by, pas.creation_date, pe.overwrite_flag, pe.salary_type, pe.option_flag, pe.name, peo.name, peo.grade_step, pe.salary_flag, peo.sequence_number from psb_position_assignments pas, psb_pay_elements pe, psb_pay_element_options peo where pas.pay_element_id = pe.pay_element_id and pas.pay_element_option_id = peo.pay_element_option_id(+) and pe.salary_flag='Y' and ( ( PSB_POSITIONS_PVT.get_worksheet_flag = 'Y' AND ( ( worksheet_id =PSB_POSITIONS_PVT.GET_WORKSHEET_ID ) or ( worksheet_id is null and not exists (select 1 from psb_position_assignments c ,psb_pay_elements pe2 where c.position_id = pas.position_id and c.pay_element_id = pe2.pay_element_id and pe2.salary_flag = 'Y' and ( ( 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.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.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.CURRENCY_CODE
, PAS.EMPLOYEE_ID
, PAS.LAST_UPDATE_DATE
, PAS.LAST_UPDATED_BY
, PAS.LAST_UPDATE_LOGIN
, PAS.CREATED_BY
, PAS.CREATION_DATE
, PE.OVERWRITE_FLAG
, PE.SALARY_TYPE
, PE.OPTION_FLAG
, PE.NAME
, PEO.NAME
, PEO.GRADE_STEP
, PE.SALARY_FLAG
, PEO.SEQUENCE_NUMBER
FROM PSB_POSITION_ASSIGNMENTS PAS
, PSB_PAY_ELEMENTS PE
, PSB_PAY_ELEMENT_OPTIONS PEO
WHERE PAS.PAY_ELEMENT_ID = PE.PAY_ELEMENT_ID
AND PAS.PAY_ELEMENT_OPTION_ID = PEO.PAY_ELEMENT_OPTION_ID(+)
AND PE.SALARY_FLAG='Y'
AND ( ( PSB_POSITIONS_PVT.GET_WORKSHEET_FLAG = 'Y'
AND ( ( WORKSHEET_ID =PSB_POSITIONS_PVT.GET_WORKSHEET_ID ) OR ( WORKSHEET_ID IS NULL
AND NOT EXISTS (SELECT 1
FROM PSB_POSITION_ASSIGNMENTS C
, PSB_PAY_ELEMENTS PE2
WHERE C.POSITION_ID = PAS.POSITION_ID
AND C.PAY_ELEMENT_ID = PE2.PAY_ELEMENT_ID
AND PE2.SALARY_FLAG = 'Y'
AND ( ( 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.WORKSHEET_ID =PSB_POSITIONS_PVT.GET_WORKSHEET_ID) ) ) ) OR (PSB_POSITIONS_PVT.GET_WORKSHEET_FLAG = 'N'
AND WORKSHEET_ID IS NULL))