VIEW: APPS.PAYBV_CHECK_PAYMENTS

Source

View Text - Preformatted

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

View Text - HTML Formatted

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