VIEW: APPS.PER_ASSIGNMENT_STATUS_TYPES_V1

Source

View Text - Preformatted

SELECT ast.ROWID, ast.assignment_status_type_id, ast.business_group_id, ast.legislation_code, nvl(astatl.user_status, asttl.user_status), ast.active_flag, ast.default_flag, ast.primary_flag, ast.pay_system_status, ast.per_system_status, asttl.external_status, ast.last_update_date, ast.last_updated_by, ast.last_update_login, ast.created_by, ast.creation_date FROM per_assignment_status_types ast, per_ass_status_type_amends asta, per_assignment_status_types_tl asttl, per_ass_status_type_amends_tl astatl WHERE ast.assignment_status_type_id = asta.assignment_status_type_id(+) AND ast.assignment_status_type_id = asttl.assignment_status_type_id AND asta.ass_status_type_amend_id = astatl.ass_status_type_amend_id(+) AND( (asta.business_group_id IS NOT NULL AND asta.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')) OR(asta.business_group_id IS NULL AND(ast.business_group_id IS NULL OR ast.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')) ) ) AND nvl(astatl.LANGUAGE, asttl.LANGUAGE) = userenv('LANG') AND ASTTL.language = userenv('LANG') UNION SELECT ast.ROWID, ast.assignment_status_type_id, ast.business_group_id, ast.legislation_code, asttl.user_status, ast.active_flag, ast.default_flag, ast.primary_flag, ast.pay_system_status, ast.per_system_status, asttl.external_status, ast.last_update_date, ast.last_updated_by, ast.last_update_login, ast.created_by, ast.creation_date FROM per_assignment_status_types ast, per_assignment_status_types_tl asttl WHERE ast.assignment_status_type_id =asttl.assignment_status_type_id AND ast.business_group_id IS NULL AND NOT EXISTS (SELECT asta.assignment_status_type_id FROM per_ass_status_type_amends asta WHERE asta.assignment_status_type_id = ast.assignment_status_type_id AND asta.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')) AND asttl.LANGUAGE = userenv('LANG')

View Text - HTML Formatted

SELECT AST.ROWID
, AST.ASSIGNMENT_STATUS_TYPE_ID
, AST.BUSINESS_GROUP_ID
, AST.LEGISLATION_CODE
, NVL(ASTATL.USER_STATUS
, ASTTL.USER_STATUS)
, AST.ACTIVE_FLAG
, AST.DEFAULT_FLAG
, AST.PRIMARY_FLAG
, AST.PAY_SYSTEM_STATUS
, AST.PER_SYSTEM_STATUS
, ASTTL.EXTERNAL_STATUS
, AST.LAST_UPDATE_DATE
, AST.LAST_UPDATED_BY
, AST.LAST_UPDATE_LOGIN
, AST.CREATED_BY
, AST.CREATION_DATE
FROM PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
, PER_ASS_STATUS_TYPE_AMENDS_TL ASTATL
WHERE AST.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID(+)
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND ASTA.ASS_STATUS_TYPE_AMEND_ID = ASTATL.ASS_STATUS_TYPE_AMEND_ID(+) AND( (ASTA.BUSINESS_GROUP_ID IS NOT NULL
AND ASTA.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')) OR(ASTA.BUSINESS_GROUP_ID IS NULL AND(AST.BUSINESS_GROUP_ID IS NULL OR AST.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')) ) )
AND NVL(ASTATL.LANGUAGE
, ASTTL.LANGUAGE) = USERENV('LANG')
AND ASTTL.LANGUAGE = USERENV('LANG') UNION SELECT AST.ROWID
, AST.ASSIGNMENT_STATUS_TYPE_ID
, AST.BUSINESS_GROUP_ID
, AST.LEGISLATION_CODE
, ASTTL.USER_STATUS
, AST.ACTIVE_FLAG
, AST.DEFAULT_FLAG
, AST.PRIMARY_FLAG
, AST.PAY_SYSTEM_STATUS
, AST.PER_SYSTEM_STATUS
, ASTTL.EXTERNAL_STATUS
, AST.LAST_UPDATE_DATE
, AST.LAST_UPDATED_BY
, AST.LAST_UPDATE_LOGIN
, AST.CREATED_BY
, AST.CREATION_DATE
FROM PER_ASSIGNMENT_STATUS_TYPES AST
, PER_ASSIGNMENT_STATUS_TYPES_TL ASTTL
WHERE AST.ASSIGNMENT_STATUS_TYPE_ID =ASTTL.ASSIGNMENT_STATUS_TYPE_ID
AND AST.BUSINESS_GROUP_ID IS NULL
AND NOT EXISTS (SELECT ASTA.ASSIGNMENT_STATUS_TYPE_ID
FROM PER_ASS_STATUS_TYPE_AMENDS ASTA
WHERE ASTA.ASSIGNMENT_STATUS_TYPE_ID = AST.ASSIGNMENT_STATUS_TYPE_ID
AND ASTA.BUSINESS_GROUP_ID = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID'))
AND ASTTL.LANGUAGE = USERENV('LANG')