View: PAY_PAYWSMEE_ELEMENTS_LOV

Product:	PAY - Payroll
Description:	List of element types for which an assignment is eligible.
Implementation/DBA Data:	APPS.PAY_PAYWSMEE_ELEMENTS_LOV

View Text

SELECT ASGT.ASSIGNMENT_ID
, ELEMENT.ELEMENT_TYPE_ID
, LINK.ELEMENT_LINK_ID
, ELEMENTTL.ELEMENT_NAME
, ELEMENT.BUSINESS_GROUP_ID
, ELEMENT.LEGISLATION_CODE
, SUBSTR (ELEMENT.PROCESSING_TYPE
, 1
, 1)
, HR_GENERAL.DECODE_LOOKUP ('PROCESSING_TYPE'
, ELEMENT.PROCESSING_TYPE)
, SUBSTR (ELEMENT.CLOSED_FOR_ENTRY_FLAG
, 1
, 1)
, SUBSTR (ELEMENT.PROCESS_IN_RUN_FLAG
, 1
, 1)
, SUBSTR (ELEMENT.ADDITIONAL_ENTRY_ALLOWED_FLAG
, 1
, 1)
, SUBSTR (ELEMENT.MULTIPLE_ENTRIES_ALLOWED_FLAG
, 1
, 1)
, ELEMENT.CLASSIFICATION_ID
, SUBSTR (LINK.COSTABLE_TYPE
, 1
, 1)
, ELEMENT.INPUT_CURRENCY_CODE
, SUBSTR (PAY_PAYWSMEE_PKG.OVERRIDDEN (LINK.ELEMENT_LINK_ID
, ASGT.ASSIGNMENT_ID
, SESH.EFFECTIVE_DATE)
, 1
, 1)
, SUBSTR (PAY_PAYWSMEE_PKG.ADJUSTED (LINK.ELEMENT_LINK_ID
, ASGT.ASSIGNMENT_ID
, SESH.EFFECTIVE_DATE)
, 1
, 1)
, SUBSTR (PAY_PAYWSMEE_PKG.ADDITIONAL_EXISTS (LINK.ELEMENT_LINK_ID
, ASGT.ASSIGNMENT_ID
, SESH.EFFECTIVE_DATE)
, 1
, 1)
, SUBSTR (PAY_PAYWSMEE_PKG.NORMAL_EXISTS (LINK.ELEMENT_LINK_ID
, ASGT.ASSIGNMENT_ID
, SESH.EFFECTIVE_DATE)
, 1
, 1)
, ELEMENT.PROCESSING_PRIORITY
, BENEFIT.CONTRIBUTIONS_USED
, ELEMENT.THIRD_PARTY_PAY_ONLY_FLAG
, ELEMENTTL.DESCRIPTION
, CLASSIFICATIONTL.CLASSIFICATION_NAME
FROM PAY_ELEMENT_TYPES_F_TL ELEMENTTL
, PAY_ELEMENT_TYPES_F ELEMENT
, PAY_ELEMENT_CLASSIFICATIONS_TL CLASSIFICATIONTL
, PAY_ELEMENT_CLASSIFICATIONS CLASSIFICATION
, BEN_BENEFIT_CLASSIFICATIONS BENEFIT
, PAY_ELEMENT_LINKS_F LINK
, PER_ALL_ASSIGNMENTS_F ASGT
, FND_SESSIONS SESH
, PER_PERIODS_OF_SERVICE SERVICE_PERIOD
WHERE ELEMENT.ELEMENT_TYPE_ID = ELEMENTTL.ELEMENT_TYPE_ID
AND ELEMENTTL.LANGUAGE = USERENV('LANG')
AND CLASSIFICATION.CLASSIFICATION_ID = CLASSIFICATIONTL.CLASSIFICATION_ID (+)
AND DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID
, NULL
, '1'
, CLASSIFICATIONTL.LANGUAGE) = DECODE(CLASSIFICATIONTL.CLASSIFICATION_ID
, NULL
, '1'
, USERENV('LANG'))
AND USERENV ('SESSIONID') = SESH.SESSION_ID /* BUGFIX 1887362 DO NOT WANT THE CBO TO CONSIDER THE JOIN FOR ASG TO EL USING THE INDEX PAY_ELEMENT_LINKS_F_FK1 AS THIS WILL RETURN VIRTUALLY ALL ROWS IN THE EL TABLE */
AND ASGT.BUSINESS_GROUP_ID = LINK.BUSINESS_GROUP_ID + 0
AND ELEMENT.ELEMENT_TYPE_ID = LINK.ELEMENT_TYPE_ID
AND ELEMENT.BENEFIT_CLASSIFICATION_ID = BENEFIT.BENEFIT_CLASSIFICATION_ID (+)
AND ELEMENT.CLASSIFICATION_ID = CLASSIFICATION.CLASSIFICATION_ID /* RESTRICT DATE-EFFECTIVE TABLES TO SESSION DATE */
AND SESH.EFFECTIVE_DATE BETWEEN ELEMENT.EFFECTIVE_START_DATE
AND ELEMENT.EFFECTIVE_END_DATE
AND SESH.EFFECTIVE_DATE BETWEEN ASGT.EFFECTIVE_START_DATE
AND ASGT.EFFECTIVE_END_DATE
AND SESH.EFFECTIVE_DATE BETWEEN LINK.EFFECTIVE_START_DATE
AND LINK.EFFECTIVE_END_DATE /* DO NOT RETRIEVE INDIRECT ONLY ELEMENTS */
AND ELEMENT.INDIRECT_ONLY_FLAG = 'N' /* DO NOT RETRIEVE US TAX ELEMENTS */
AND UPPER (ELEMENT.ELEMENT_NAME) != 'VERTEX' /* DO NOT RETRIEVE ADVANCE PAY INTERNAL ELEMENTS */
AND NOT EXISTS (SELECT 1
FROM HR_ORGANIZATION_INFORMATION HOI
, PAY_LEGISLATION_RULES PLR
WHERE PLR.RULE_TYPE IN ('ADVANCE'
, 'ADVANCE_INDICATOR'
, 'ADV_DEDUCTION'
, 'PAY_ADVANCE_INDICATOR'
, 'ADV_CLEARUP'
, 'DEFER_PAY')
AND PLR.RULE_MODE = TO_CHAR(ELEMENT.ELEMENT_TYPE_ID)
AND PLR.LEGISLATION_CODE = HOI.ORG_INFORMATION9
AND HOI.ORGANIZATION_ID = ASGT.ORGANIZATION_ID ) /* THE ELEMENT MUST NOT BE CLOSED FOR ENTRY */
AND ELEMENT.CLOSED_FOR_ENTRY_FLAG = 'N' /* THE ELEMENT MUST NOT BE ADJUSTMENT ONLY */
AND ELEMENT.ADJUSTMENT_ONLY_FLAG = 'N' /* THE ASSIGNMENT MUST BE ELIGIBLE FOR THE ELEMENT */
AND ((LINK.PAYROLL_ID IS NOT NULL
AND LINK.PAYROLL_ID = ASGT.PAYROLL_ID) OR (LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'Y'
AND ASGT.PAYROLL_ID IS NOT NULL) OR (LINK.PAYROLL_ID IS NULL
AND LINK.LINK_TO_ALL_PAYROLLS_FLAG = 'N'))
AND (LINK.ORGANIZATION_ID = ASGT.ORGANIZATION_ID OR LINK.ORGANIZATION_ID IS NULL)
AND (LINK.POSITION_ID = ASGT.POSITION_ID OR LINK.POSITION_ID IS NULL)
AND (LINK.JOB_ID = ASGT.JOB_ID OR LINK.JOB_ID IS NULL)
AND (LINK.GRADE_ID = ASGT.GRADE_ID OR LINK.GRADE_ID IS NULL)
AND (LINK.LOCATION_ID = ASGT.LOCATION_ID OR LINK.LOCATION_ID IS NULL)
AND (LINK.PAY_BASIS_ID = ASGT.PAY_BASIS_ID OR LINK.PAY_BASIS_ID IS NULL)
AND (LINK.EMPLOYMENT_CATEGORY = ASGT.EMPLOYMENT_CATEGORY OR LINK.EMPLOYMENT_CATEGORY IS NULL)
AND (LINK.PEOPLE_GROUP_ID IS NULL OR EXISTS ( SELECT 1
FROM PAY_ASSIGNMENT_LINK_USAGES_F USAGE
WHERE USAGE.ASSIGNMENT_ID = ASGT.ASSIGNMENT_ID
AND USAGE.ELEMENT_LINK_ID = LINK.ELEMENT_LINK_ID
AND SESH.EFFECTIVE_DATE BETWEEN USAGE.EFFECTIVE_START_DATE
AND USAGE.EFFECTIVE_END_DATE)) /* NON-RECURRING ELEMENTS MUST BE FOR AN ASSIGNMENT ON A PAYROLL */
AND (ELEMENT.PROCESSING_TYPE = 'R' OR ASGT.PAYROLL_ID IS NOT NULL) /* OUTER JOIN TO PERIOD OF SERVICE AS NULL FOR BENEFIT ASSIGNMENT */
AND SERVICE_PERIOD.PERIOD_OF_SERVICE_ID (+) = ASGT.PERIOD_OF_SERVICE_ID /* THE ELEMENT TERMINATION RULE MUST BE SATISFIED FOR NON-BENEFIT ASSIGNMENTS */
AND SESH.EFFECTIVE_DATE <= DECODE(ELEMENT.POST_TERMINATION_RULE
, 'L'
, NVL(SERVICE_PERIOD.LAST_STANDARD_PROCESS_DATE
, TO_DATE ('4712/12/31'
, 'YYYY/MM/DD'))
, 'F'
, NVL(SERVICE_PERIOD.FINAL_PROCESS_DATE
, TO_DATE ('4712/12/31'
, 'YYYY/MM/DD'))
, NVL(SERVICE_PERIOD.ACTUAL_TERMINATION_DATE
, TO_DATE ('4712/12/31'
, 'YYYY/MM/DD')))
AND ((ASGT.ASSIGNMENT_TYPE NOT IN ( 'B'
, 'C')
AND (NVL(SERVICE_PERIOD.PERIOD_OF_SERVICE_ID
, -1)<>-1)) OR (/* CONTIGENT WOKER ONLY */ ASGT.ASSIGNMENT_TYPE = 'C'
AND CLASSIFICATION.CLASSIFICATION_NAME = 'INFORMATION'
AND ELEMENT.PROCESSING_TYPE = 'R') OR (/* BENEFITS ASSIGNMENTS ONLY */ SERVICE_PERIOD.PERIOD_OF_SERVICE_ID IS NULL
AND ASGT.ASSIGNMENT_TYPE= 'B'
AND NOT EXISTS ( SELECT 1
FROM BEN_ACTY_BASE_RT_F ABR
WHERE ELEMENT.ELEMENT_TYPE_ID = ABR.ELEMENT_TYPE_ID
AND SESH.EFFECTIVE_DATE BETWEEN ABR.EFFECTIVE_START_DATE
AND ABR.EFFECTIVE_END_DATE
AND ACTY_BASE_RT_STAT_CD ='A' )))

Columns