VIEW: APPS.PAY_US_EARNINGS_AMOUNTS_V

Source

View Text - Preformatted

SELECT /*+ ORDERED USE_NL(PAA,PPA, PEC,PET,PETTL,PBT) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA PAY_PAYROLL_ACTIONS_PK) INDEX(PEC PAY_ELEMENT_CLASSIFICATION_UK2) INDEX(PET PAY_ELEMENT_TYPES_F_FK1) INDEX(PETTL PAY_ELEMENT_TYPES_F_TL_PK) INDEX(PBT PAY_BALANCE_TYPES_PK) */ PAA.ASSIGNMENT_ID , PAA.TAX_UNIT_ID , PAA.ASSIGNMENT_ACTION_ID , PAA.ACTION_STATUS , PAA.PAYROLL_ACTION_ID , PPA.ACTION_TYPE , PPA.EFFECTIVE_DATE , PETTL.ELEMENT_NAME , PETTL.ELEMENT_TYPE_ID , PET.ELEMENT_INFORMATION1 TAX_CATEGORY_CODE, PEC.CLASSIFICATION_NAME , PET.PROCESSING_PRIORITY , PET.EFFECTIVE_START_DATE , PET.EFFECTIVE_END_DATE , PET.BUSINESS_GROUP_ID , PET.LEGISLATION_CODE , PETTL.REPORTING_NAME , NVL(PETTL.REPORTING_NAME, PETTL.ELEMENT_NAME) REPORTING_NAME_ALT , HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID) BALANCE_NAME , PBT.BALANCE_TYPE_ID , TO_NUMBER(PET.ELEMENT_INFORMATION12) HOURS_BALANCE_TYPE_ID , HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)) HOURS_BALANCE_NAME, PBT.BALANCE_UOM , PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE , PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'CURRENT', PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) CURRENT_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'ASG_GRE_RUN' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) RUN_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'ASG_GRE_PYDATE', PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) PYDATE_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'ASG_GRE_MONTH', PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) MONTH_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'ASG_GRE_QTD' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) QTD_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID)), 'ASG_GRE_YTD' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) YTD_VAL, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12))), 'CURRENT', PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) HOURS_CURRENT_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12))), 'ASG_GRE_RUN' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) HOURS_RUN_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12))), 'ASG_GRE_QTD' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) HOURS_QTD_VAL , PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12))), 'ASG_GRE_YTD' , PAA.ASSIGNMENT_ACTION_ID, PAA.ASSIGNMENT_ID, PPA.DATE_EARNED, PAA.TAX_UNIT_ID, PET.BUSINESS_GROUP_ID, NULL) HOURS_YTD_VAL FROM PAY_ASSIGNMENT_ACTIONS PAA, PAY_PAYROLL_ACTIONS PPA, PAY_ELEMENT_CLASSIFICATIONS PEC, PAY_ELEMENT_TYPES_F PET, PAY_ELEMENT_TYPES_F_TL PETTL, PAY_BALANCE_TYPES PBT WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID AND PPA.ACTION_TYPE IN ('Q', 'R', 'V', 'B', 'I') AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID AND PETTL.LANGUAGE = USERENV('LANG') AND PEC.CLASSIFICATION_NAME IN ('Earnings','Alien/Expat Earnings','Supplemental Earnings','Imputed Earnings','Non-payroll Payments') AND PETTL.ELEMENT_NAME NOT LIKE '%Special Features' AND PETTL.ELEMENT_NAME NOT LIKE '%Special Inputs' AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID AND PEC.LEGISLATION_CODE = 'US' AND PET.ELEMENT_INFORMATION10 = PBT.BALANCE_TYPE_ID AND NVL(PPA.DATE_EARNED, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND EXISTS ( SELECT 'x' FROM PAY_PAYROLL_ACTIONS PACT, PAY_ASSIGNMENT_ACTIONS ASG, PAY_RUN_RESULTS RR WHERE RR.ELEMENT_TYPE_ID + 0 = PET.ELEMENT_TYPE_ID AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE,'YEAR') AND PPA.EFFECTIVE_DATE AND PACT.ACTION_TYPE IN ('R', 'Q','B','I','V') AND rownum < 2 )

View Text - HTML Formatted

SELECT /*+ ORDERED USE_NL(PAA
, PPA
, PEC
, PET
, PETTL
, PBT) INDEX(PAA PAY_ASSIGNMENT_ACTIONS_PK) INDEX(PPA PAY_PAYROLL_ACTIONS_PK) INDEX(PEC PAY_ELEMENT_CLASSIFICATION_UK2) INDEX(PET PAY_ELEMENT_TYPES_F_FK1) INDEX(PETTL PAY_ELEMENT_TYPES_F_TL_PK) INDEX(PBT PAY_BALANCE_TYPES_PK) */ PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, PETTL.ELEMENT_NAME
, PETTL.ELEMENT_TYPE_ID
, PET.ELEMENT_INFORMATION1 TAX_CATEGORY_CODE
, PEC.CLASSIFICATION_NAME
, PET.PROCESSING_PRIORITY
, PET.EFFECTIVE_START_DATE
, PET.EFFECTIVE_END_DATE
, PET.BUSINESS_GROUP_ID
, PET.LEGISLATION_CODE
, PETTL.REPORTING_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORTING_NAME_ALT
, HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID) BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, TO_NUMBER(PET.ELEMENT_INFORMATION12) HOURS_BALANCE_TYPE_ID
, HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)) HOURS_BALANCE_NAME
, PBT.BALANCE_UOM
, PAY_US_BALANCE_VIEW_PKG.GET_VIEW_MODE VIEW_MODE
, PAY_US_BALANCE_VIEW_PKG.GET_CALC_ALL_TIMETYPES_FLAG CALC_ALL_TIMETYPES
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'ASG_GRE_PYDATE'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'ASG_GRE_MONTH'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'ASG_GRE_QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(PBT.BALANCE_TYPE_ID))
, 'ASG_GRE_YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) YTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)))
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) HOURS_CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)))
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) HOURS_RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)))
, 'ASG_GRE_QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) HOURS_QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM (UPPER(HR_BALANCES.DECODE_BALANCE(TO_NUMBER(PET.ELEMENT_INFORMATION12)))
, 'ASG_GRE_YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, PET.BUSINESS_GROUP_ID
, NULL) HOURS_YTD_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_BALANCE_TYPES PBT
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_NAME IN ('EARNINGS'
, 'ALIEN/EXPAT EARNINGS'
, 'SUPPLEMENTAL EARNINGS'
, 'IMPUTED EARNINGS'
, 'NON-PAYROLL PAYMENTS')
AND PETTL.ELEMENT_NAME NOT LIKE '%SPECIAL FEATURES'
AND PETTL.ELEMENT_NAME NOT LIKE '%SPECIAL INPUTS'
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PEC.LEGISLATION_CODE = 'US'
AND PET.ELEMENT_INFORMATION10 = PBT.BALANCE_TYPE_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND EXISTS ( SELECT 'X'
FROM PAY_PAYROLL_ACTIONS PACT
, PAY_ASSIGNMENT_ACTIONS ASG
, PAY_RUN_RESULTS RR
WHERE RR.ELEMENT_TYPE_ID + 0 = PET.ELEMENT_TYPE_ID
AND RR.ASSIGNMENT_ACTION_ID = ASG.ASSIGNMENT_ACTION_ID
AND ASG.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND ASG.TAX_UNIT_ID = PAA.TAX_UNIT_ID
AND ASG.PAYROLL_ACTION_ID = PACT.PAYROLL_ACTION_ID
AND PACT.EFFECTIVE_DATE BETWEEN TRUNC(PPA.EFFECTIVE_DATE
, 'YEAR')
AND PPA.EFFECTIVE_DATE
AND PACT.ACTION_TYPE IN ('R'
, 'Q'
, 'B'
, 'I'
, 'V')
AND ROWNUM < 2 )