View: PAYBV_CHECK_PAYMENTS

Product:	PAY - Payroll
Description:
Implementation/DBA Data:	APPS.PAYBV_CHECK_PAYMENTS

View Text

SELECT FULL_NAME
, ASSIGNMENT_NUMBER
, PAYROLL_NAME
, PROCESS_DATE
, CHEQUE_DATE
, CHEQUE_NUMBER
, PAYMENT_METHOD
, VALUE
, DECODE(VOID_DATE
, NULL
, DECODE(REVERSAL_EXIST
, 'Y'
, 'RWV'
, 'P')
, DECODE(REVERSAL_EXIST
, 'Y'
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL)
, 'EMP'
, 'C'
, 'RWMP')
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL)
, 'CW'
, 'R'
, 'EMP'
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_CHEQUE_NUMBER
, NULL)
, NULL
, 'DMP'
, 'MR')
, 'V' ))) STATUS_CODE
, HR_BIS.BIS_DECODE_LOOKUP('PAY_CHECK_STATUS'
, DECODE(VOID_DATE
, NULL
, DECODE(REVERSAL_EXIST
, 'Y'
, 'RWV'
, 'P')
, DECODE(REVERSAL_EXIST
, 'Y'
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL)
, 'EMP'
, 'C'
, 'RWMP')
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL)
, 'CW'
, 'R'
, 'EMP'
, DECODE(DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_CHEQUE_NUMBER
, NULL)
, NULL
, 'DMP'
, 'MR')
, 'V' )))) STATUS
, VOID_DATE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(REVERSAL_EXIST
, 'Y'
, 'Y'
, 'N'))
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, DECODE(REPLACEMENT_EXIST
, 'Y'
, 'Y'
, 'N'))
, DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_CHEQUE_DATE
, NULL) REPLACEMENT_CHEQUE_DATE
, DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_CHEQUE_NUMBER
, NULL) REPLACEMENT_CHEQUE_NUMBER
, DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL) REPLACEMENT_METHOD_CODE
, HR_BIS.BIS_DECODE_LOOKUP('PAY_CHECK_METHOD'
, DECODE(REPLACEMENT_EXIST
, 'Y'
, REPLACEMENT_METHOD_CODE
, NULL) ) REPLACEMENT_METHOD
, ORIGINAL_METHOD_CODE
, HR_BIS.BIS_DECODE_LOOKUP('PAY_CHECK_METHOD'
, ORIGINAL_METHOD_CODE ) ORIGINAL_METHOD
, PERSON_ID
, ASSIGNMENT_ID
, PAYROLL_ID
, ORG_PAY_METHOD_ID
, DATE_EARNED
, OVERRIDING_DATE
, PAYROLL_ACTION_ID
, ASSIGNMENT_ACTION_ID
, PRE_PAYMENT_ID
, BUSINESS_GROUP_ID
, CASE WHEN (BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, BUSINESS_GROUP_ID)) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM ( SELECT PEO.FULL_NAME
, ASG.ASSIGNMENT_NUMBER
, PPF.PAYROLL_NAME
, CHQ_PPA.EFFECTIVE_DATE PROCESS_DATE
, CHQ_PPA.DATE_EARNED
, CHQ_PPA.OVERRIDING_DD_DATE OVERRIDING_DATE
, CHQ_PPA.PAYROLL_ACTION_ID
, CHQ_AA.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID
, HR_PRE_PAY.GET_TRX_DATE(CHQ_PPA.BUSINESS_GROUP_ID
, CHQ_PPA.PAYROLL_ACTION_ID
, CHQ_AA.ASSIGNMENT_ACTION_ID
, PPF.PAYROLL_ID
, PPF.CONSOLIDATION_SET_ID
, PPP.ORG_PAYMENT_METHOD_ID
, CHQ_PPA.EFFECTIVE_DATE
, CHQ_PPA.DATE_EARNED
, CHQ_PPA.OVERRIDING_DD_DATE
, PPP.PRE_PAYMENT_ID) CHEQUE_DATE
, CHQ_AA.SERIAL_NUMBER CHEQUE_NUMBER
, ORG.ORG_PAYMENT_METHOD_NAME PAYMENT_METHOD
, PPP.VALUE
, PPP.BASE_CURRENCY_VALUE
, (SELECT VOID_PPA.EFFECTIVE_DATE
FROM PAY_ASSIGNMENT_ACTIONS VOID_AA
, PAY_PAYROLL_ACTIONS VOID_PPA
, PAY_ACTION_INTERLOCKS VOID_INT
WHERE VOID_PPA.ACTION_TYPE='D'
AND VOID_PPA.PAYROLL_ACTION_ID=VOID_AA.PAYROLL_ACTION_ID
AND VOID_AA.ASSIGNMENT_ACTION_ID = VOID_INT.LOCKING_ACTION_ID
AND VOID_INT.LOCKED_ACTION_ID = CHQ_AA.ASSIGNMENT_ACTION_ID) VOID_DATE
, (SELECT DISTINCT 'Y'
FROM PAY_ASSIGNMENT_ACTIONS RR_ASG
, PAY_ACTION_INTERLOCKS PP_INT
, PAY_PAYROLL_ACTIONS RR_PPA
WHERE PPP.ASSIGNMENT_ACTION_ID=PP_INT.LOCKING_ACTION_ID
AND PP_INT.LOCKED_ACTION_ID=RR_ASG.ASSIGNMENT_ACTION_ID
AND RR_ASG.PAYROLL_ACTION_ID=RR_PPA.PAYROLL_ACTION_ID
AND RR_PPA.ACTION_TYPE IN ('Q'
, 'R')
AND ((EXISTS (SELECT NULL
FROM PAY_ASSIGNMENT_ACTIONS REV_AA
, PAY_PAYROLL_ACTIONS REV_PPA
, PAY_ACTION_INTERLOCKS REV_INT
WHERE REV_PPA.ACTION_TYPE='V'
AND REV_PPA.PAYROLL_ACTION_ID=REV_AA.PAYROLL_ACTION_ID
AND REV_AA.ASSIGNMENT_ACTION_ID=REV_INT.LOCKING_ACTION_ID
AND REV_INT.LOCKED_ACTION_ID=RR_ASG.ASSIGNMENT_ACTION_ID
AND RR_ASG.SOURCE_ACTION_ID IS NULL)) OR (EXISTS (SELECT NULL
FROM PAY_ASSIGNMENT_ACTIONS REV_AA
, PAY_PAYROLL_ACTIONS REV_PPA
, PAY_ACTION_INTERLOCKS REV_INT
, PAY_ASSIGNMENT_ACTIONS RR2_ASG
WHERE REV_PPA.ACTION_TYPE='V'
AND REV_PPA.PAYROLL_ACTION_ID=REV_AA.PAYROLL_ACTION_ID
AND REV_AA.ASSIGNMENT_ACTION_ID=REV_INT.LOCKING_ACTION_ID
AND REV_INT.LOCKED_ACTION_ID=RR2_ASG.ASSIGNMENT_ACTION_ID
AND RR2_ASG.SOURCE_ACTION_ID =RR_ASG.ASSIGNMENT_ACTION_ID )))) REVERSAL_EXIST
, DECODE(EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
, CHQ_AA.ASSIGNMENT_ACTION_ID
, 'N'
, 'Y') REPLACEMENT_EXIST
, DECODE(EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
, CHQ_AA.ASSIGNMENT_ACTION_ID
, TO_DATE(NULL)
, HR_PRE_PAY.GET_TRX_DATE(EXT_CHQ_PPA.BUSINESS_GROUP_ID
, EXT_CHQ_PPA.PAYROLL_ACTION_ID
, EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
, PPF.PAYROLL_ID
, PPF.CONSOLIDATION_SET_ID
, PPP.ORG_PAYMENT_METHOD_ID
, EXT_CHQ_PPA.EFFECTIVE_DATE
, EXT_CHQ_PPA.DATE_EARNED
, EXT_CHQ_PPA.OVERRIDING_DD_DATE
, PPP.PRE_PAYMENT_ID)) REPLACEMENT_CHEQUE_DATE
, EXT_CHQ_AA.SERIAL_NUMBER REPLACEMENT_CHEQUE_NUMBER
, DECODE(EXT_CHQ_PPA.ACTION_TYPE
, 'H'
, 'CW'
, 'EMP') REPLACEMENT_METHOD_CODE
, DECODE(CHQ_PPA.ACTION_TYPE
, 'H'
, 'CW'
, 'EMP') ORIGINAL_METHOD_CODE
, PEO.PERSON_ID
, ASG.ASSIGNMENT_ID
, PPF.PAYROLL_ID
, ORG.ORG_PAYMENT_METHOD_ID ORG_PAY_METHOD_ID
, PPF.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
FROM PAY_ACTION_INTERLOCKS CHQ_INT
, PAY_ASSIGNMENT_ACTIONS CHQ_AA
, PAY_PAYROLL_ACTIONS CHQ_PPA
, PAY_ACTION_INTERLOCKS EXT_CHQ_INT
, PAY_ASSIGNMENT_ACTIONS EXT_CHQ_AA
, PAY_PAYROLL_ACTIONS EXT_CHQ_PPA
, PER_ASSIGNMENTS_F ASG
, PER_PEOPLE_F PEO
, PAY_PAYROLLS_F PPF
, PAY_PRE_PAYMENTS PPP
, PAY_ORG_PAYMENT_METHODS_F ORG
WHERE CHQ_PPA.ACTION_TYPE IN ('H'
, 'E')
AND ((CHQ_PPA.ACTION_TYPE = 'E'
AND CHQ_AA.SERIAL_NUMBER NOT IN (-1
, -2)) OR (CHQ_PPA.ACTION_TYPE = 'H'))
AND CHQ_AA.PAYROLL_ACTION_ID = CHQ_PPA.PAYROLL_ACTION_ID
AND CHQ_AA.ASSIGNMENT_ACTION_ID=CHQ_INT.LOCKING_ACTION_ID
AND CHQ_AA.PRE_PAYMENT_ID=PPP.PRE_PAYMENT_ID
AND CHQ_AA.SERIAL_NUMBER IS NOT NULL
AND ORG.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID
AND CHQ_PPA.EFFECTIVE_DATE BETWEEN ORG.EFFECTIVE_START_DATE
AND ORG.EFFECTIVE_END_DATE
AND CHQ_INT.LOCKED_ACTION_ID=EXT_CHQ_INT.LOCKED_ACTION_ID
AND CHQ_AA.PRE_PAYMENT_ID = EXT_CHQ_AA.PRE_PAYMENT_ID
AND EXT_CHQ_INT.LOCKING_ACTION_ID=EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
AND EXT_CHQ_AA.PAYROLL_ACTION_ID=EXT_CHQ_PPA.PAYROLL_ACTION_ID
AND EXT_CHQ_PPA.ACTION_TYPE IN ('H'
, 'E')
AND (CHQ_PPA.ACTION_TYPE = 'H' OR (CHQ_PPA.ACTION_TYPE = 'E'
AND EXT_CHQ_PPA.ACTION_TYPE = 'E'))
AND NOT EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS CHQ_INT1
, PAY_ASSIGNMENT_ACTIONS CHQ_AA1
, PAY_PAYROLL_ACTIONS CHQ_PPA1
WHERE CHQ_INT1.LOCKED_ACTION_ID=CHQ_INT.LOCKED_ACTION_ID
AND CHQ_AA.PRE_PAYMENT_ID = CHQ_AA1.PRE_PAYMENT_ID
AND CHQ_PPA.ACTION_TYPE = 'H'
AND ((EXT_CHQ_AA.ASSIGNMENT_ACTION_ID = CHQ_AA.ASSIGNMENT_ACTION_ID) OR (EXT_CHQ_AA.ASSIGNMENT_ACTION_ID <> CHQ_AA.ASSIGNMENT_ACTION_ID
AND EXISTS (SELECT NULL
FROM PAY_ACTION_INTERLOCKS EXT_CHQ_VINT
, PAY_ASSIGNMENT_ACTIONS EXT_CHQ_VAA
, PAY_PAYROLL_ACTIONS EXT_CHQ_VPPA
WHERE EXT_CHQ_VINT.LOCKED_ACTION_ID=EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
AND EXT_CHQ_VINT.LOCKING_ACTION_ID=EXT_CHQ_VAA.ASSIGNMENT_ACTION_ID
AND EXT_CHQ_VAA.PAYROLL_ACTION_ID=EXT_CHQ_VPPA.PAYROLL_ACTION_ID
AND EXT_CHQ_VPPA.ACTION_TYPE = 'D')))
AND CHQ_INT1.LOCKING_ACTION_ID=CHQ_AA1.ASSIGNMENT_ACTION_ID
AND CHQ_AA1.PAYROLL_ACTION_ID=CHQ_PPA1.PAYROLL_ACTION_ID
AND CHQ_AA1.ASSIGNMENT_ACTION_ID <> EXT_CHQ_AA.ASSIGNMENT_ACTION_ID
AND CHQ_PPA1.ACTION_TYPE IN ('H'
, 'E'))
AND ASG.ASSIGNMENT_ID = CHQ_AA.ASSIGNMENT_ID
AND CHQ_PPA.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.PERSON_ID = PEO.PERSON_ID
AND CHQ_PPA.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE
AND PEO.EFFECTIVE_END_DATE
AND ASG.PAYROLL_ID = PPF.PAYROLL_ID
AND CHQ_PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE ) WITH READ ONLY

Columns