View: PAY_US_DEDUCTIONS_V

Product:	PAY - Payroll
Description:	This view retrieves deductions information for a given assignment.
Implementation/DBA Data:	APPS.PAY_US_DEDUCTIONS_V

View Text

SELECT DISTINCT PAA.ASSIGNMENT_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ACTION_STATUS
, PAA.PAYROLL_ACTION_ID
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, DEDUCT.ELEMENT_NAME
, DEDUCT.ELEMENT_TYPE_ID
, DEDUCT.CLASSIFICATION_NAME
, DEDUCT.BUSINESS_GROUP_ID
, DEDUCT.EFFECTIVE_START_DATE
, DEDUCT.EFFECTIVE_END_DATE
, DEDUCT.LEGISLATION_CODE
, DEDUCT.ELEMENT_INFORMATION10
, DEDUCT.PROCESSING_PRIORITY
, DEDUCT.REPORTING_NAME
, DEDUCT.REPORTING_NAME_ALT
, PBTTL.BALANCE_NAME
, PBT.BALANCE_TYPE_ID
, 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(PBTTL.BALANCE_NAME)
, 'CURRENT'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) CURRENT_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBTTL.BALANCE_NAME)
, 'ASG_GRE_RUN'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) RUN_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBTTL.BALANCE_NAME)
, 'ASG_GRE_PYDATE'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) PYDATE_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBTTL.BALANCE_NAME)
, 'ASG_GRE_MONTH'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) MONTH_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBTTL.BALANCE_NAME)
, 'ASG_GRE_QTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) QTD_VAL
, PAY_US_TAXBAL_VIEW_PKG.US_NAMED_BALANCE_VM( UPPER(PBTTL.BALANCE_NAME)
, 'ASG_GRE_YTD'
, PAA.ASSIGNMENT_ACTION_ID
, PAA.ASSIGNMENT_ID
, PPA.DATE_EARNED
, PAA.TAX_UNIT_ID
, DEDUCT.BUSINESS_GROUP_ID
, NULL) YTD_VAL
FROM PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_BALANCE_TYPES_TL PBTTL
, PAY_BALANCE_TYPES PBT
, PAY_US_DEDUCTION_TYPES_V DEDUCT
WHERE PBT.BALANCE_TYPE_ID = PBTTL.BALANCE_TYPE_ID
AND PBTTL.LANGUAGE = USERENV('LANG')
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND DEDUCT.ELEMENT_INFORMATION10 IS NOT NULL
AND DEDUCT.ELEMENT_INFORMATION10 = PBT.BALANCE_TYPE_ID
AND PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'V'
, 'B'
, 'I')
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN DEDUCT.EFFECTIVE_START_DATE
AND DEDUCT.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 = DEDUCT.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') )

Columns