eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_PAYWSMEE_ELEMENTS_LOV

Source

View Text - Preformatted


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' )))

View Text - HTML Formatted

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' )))