1. Tables and Views for HCM
  2. PAY_PAYMT_SEARCH_RESULTS_VL

PAY_PAYMT_SEARCH_RESULTS_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

SHOW_VOIDED_PAYMENT

ROLL_UP_PAYMENT_ACTION_ID

EMP_REL_ACTION_ID

CANCEL_REL_ACTION_ID

ORI_PAYMENT_REL_ACTION_ID

PAYMENT_REL_ACTION_ID

PRE_PAYMENT_ID

STATUS

STATUS_CODE

OPM

PAYROLL_ACTION_ID

PAYMENT_PAYROLL_ACTION_ID

PROCESS_DATE

PROCESS_START_DATE

PROCESS_END_DATE

PAYMENT_DATE

PAYROLL_NAME

PAYMENT_REFERENCE

PPM_NAME

BANK_ACCOUNT_NUMBER

BANK_ACCOUNT_TYPE

EXT_BANK_ACCOUNT_ID

IBAN

CHECK_DIGITS

SECONDARY_ACCOUNT_REFERENCE

BRANCH_PARTY_ID

BANK_NAME

BANK_NUMBER

BRANCH_NUMBER

BRANCH_NAME

BIC_CODE

BANK_ACCOUNT_ID

BASE_PAYMENT_TYPE_NAME

PAYROLL_REL_ACTION_ID

RELATIONSHIP_ID

REASON

AMOUNT

FLOW_INSTANCE_NAME

SOURCE

PAYMENT_TYPE_ID

LEGISLATION_CODE

PAYMENT_TYPE_NAME

PROCESS_NAME

PROCESS_NAME_CODE

PREPAY_PROC_SUBMIT_DATE

PAYMENT_PROC_SUBMIT_DATE

PRIORITY

CURRENCY_CODE

PREPAY_REL_ACTION_ID

PREPAY_CUM_REL_ACTION_ID

PAYROLL_ID

CALC_BREAKDOWN_ID

PREPAY_PAYMENT_DATE

PREPAY_PROCESS_DATE

PAYEE_TYPE

PAYEE

PAYEE_ID

PAYROLL_RELATIONSHIP_NUMBER

PERSON_NUMBER

PERSON_ID

LEGISLATIVE_DATA_GROUP_ID

PAY_LOCKING_FLAG

PAY_LOCKING_ACTION

MAKE_EXTERNAL_PAYMENT_FLAG

PREVENT_PAYMENT_FLAG

VOID_PAYMENT_FLAG

ROLLBACK_FLAG

ACCOUNT_NUMBER_VISIBLE_FLAG

REASON_VISIBLE_FLAG

PAYMENT_REF_VISIBLE_FLAG

PPM_NAME_VISIBLE_FLAG

PAYEE_VISIBLE_FLAG

EXP_PMT_DT_VISIBLE_FLAG

Query

SQL_Statement

SELECT SHOW_VOIDED_PAYMENT,

ROLL_UP_PAYMENT_ACTION_ID,

EMP_REL_ACTION_ID,

CANCEL_REL_ACTION_ID,

ORI_PAYMENT_REL_ACTION_ID,

PAYMENT_REL_ACTION_ID,

PRE_PAYMENT_ID,

(SELECT MEANING

FROM HCM_LOOKUPS LK1

WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS'

AND LK1.LOOKUP_CODE =STATUS_CODE

) AS STATUS,

STATUS_CODE,

OPM,

DECODE(STATUS_CODE,'V',

( decode(OBJECT_TYPE,'PRE',

(SELECT pa.payroll_action_id

FROM pay_payroll_actions PA,

pay_payroll_rel_actions pra

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID=PAY_LOCKING_FLAG),

(SELECT pa.payroll_action_id

FROM pay_payroll_actions PA,

pay_object_actions pra

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.object_action_id=PAY_LOCKING_FLAG)

)),'MR',

(SELECT pa.payroll_action_id

FROM pay_payroll_actions PA,

pay_payroll_rel_actions PRA

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID=nvl(EMP_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)

),'C',

(SELECT pa.payroll_action_id

FROM pay_payroll_actions PA,

pay_payroll_rel_actions PRA

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID=nvl(CANCEL_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)

),payroll_action_id) AS PAYROLL_ACTION_ID,

PAYMENT_PAYROLL_ACTION_ID,

DECODE(STATUS_CODE,'V',

(

decode(OBJECT_TYPE,'PRE',

(SELECT pa.EFFECTIVE_DATE

FROM pay_payroll_actions PA,

pay_payroll_rel_actions pra

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID=PAY_LOCKING_FLAG),

(SELECT pa.EFFECTIVE_DATE

FROM pay_payroll_actions PA,

pay_object_actions pra

WHERE PA.payroll_action_id =PRA.payroll_action_id

AND PRA.object_action_id=PAY_LOCKING_FLAG)

)

),PROCESS_DATE) AS PROCESS_DATE,

process_start_date,process_end_date,

PAYMENT_DATE,

PAYROLL_NAME,

decode(ORI_PAYMENT_REL_ACTION_ID,null,

decode(SHOW_VOIDED_PAYMENT,'Y',

decode(STATUS_CODE,'MR',nvl((select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1

where p1.payroll_rel_action_id=nvl(EMP_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)),nvl(EMP_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)),

'C',(select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1 where p1.payroll_rel_action_id=CANCEL_REL_ACTION_ID)

,nvl(PAYMENT_REFERENCE,PAYMENT_REL_ACTION_ID)),

decode(STATUS_CODE,'MR',nvl((select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1

where p1.payroll_rel_action_id=nvl(EMP_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)),nvl(EMP_REL_ACTION_ID,PAYMENT_REL_ACTION_ID)),

'C',(select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1 where p1.payroll_rel_action_id=PAYMENT_REL_ACTION_ID)

,nvl(PAYMENT_REFERENCE,PAYMENT_REL_ACTION_ID))),

DECODE(STATUS_CODE,'MR',NVL((select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1 where p1.payroll_rel_action_id=PAYMENT_REL_ACTION_ID),PAYMENT_REL_ACTION_ID),

'C',NVL((select p1.SERIAL_NUMBER from pay_payroll_rel_actions p1 where p1.payroll_rel_action_id=ORI_PAYMENT_REL_ACTION_ID),ORI_PAYMENT_REL_ACTION_ID)

,NVL(PAYMENT_REFERENCE,PAYMENT_REL_ACTION_ID))) AS PAYMENT_REFERENCE,

DECODE(PAYEE_TYPE,'ORGANIZATION',PAYEE,PPM_NAME) AS PPM_NAME,

DECODE(STATUS_CODE,'MR',NULL,BANK_ACCOUNT_NUMBER) AS BANK_ACCOUNT_NUMBER,

DECODE(STATUS_CODE,'MR',NULL,BANK_ACCOUNT_TYPE) AS BANK_ACCOUNT_TYPE,

DECODE(STATUS_CODE,'MR',NULL,EXT_BANK_ACCOUNT_ID) EXT_BANK_ACCOUNT_ID,

DECODE(STATUS_CODE,'MR',NULL,IBAN) IBAN,

DECODE(STATUS_CODE,'MR',NULL,CHECK_DIGITS) CHECK_DIGITS,

DECODE(STATUS_CODE,'MR',NULL,SECONDARY_ACCOUNT_REFERENCE) SECONDARY_ACCOUNT_REFERENCE,

DECODE(STATUS_CODE,'MR',NULL,BRANCH_PARTY_ID) BRANCH_PARTY_ID,

DECODE(STATUS_CODE,'MR',NULL,BANK_NAME) BANK_NAME,

DECODE(STATUS_CODE,'MR',NULL,BANK_NUMBER) BANK_NUMBER,

DECODE(STATUS_CODE,'MR',NULL,BRANCH_NUMBER) BRANCH_NUMBER,

DECODE(STATUS_CODE,'MR',NULL,BRANCH_NAME) BRANCH_NAME,

DECODE(STATUS_CODE,'MR',NULL,BIC_CODE) BIC_CODE,

DECODE(STATUS_CODE,'MR',NULL,BANK_ACCOUNT_ID) BANK_ACCOUNT_ID,

BASE_PAYMENT_TYPE_NAME,

PAYROLL_REL_ACTION_ID,

RELATIONSHIP_ID,

decode(status_code,'V',

(SELECT FLOW_PARAM_VALUE

FROM

PAY_FLOW_PARAMETERS_VL FPP,

PAY_FLOW_PARAM_VALUES FPVP

WHERE FPP.BASE_FLOW_PARAMETER_NAME = 'REASON'

AND FPP.BASE_FLOW_PARAMETER_ID = FPVP.BASE_FLOW_PARAMETER_ID

AND FPVP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PERSON_PROCESS'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PAYMENT_REL_ACTION_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_VOID_PAYMENT')),

'C',

(SELECT FLOW_PARAM_VALUE

FROM

PAY_FLOW_PARAMETERS_VL FPP,

PAY_FLOW_PARAM_VALUES FPVP

WHERE FPP.BASE_FLOW_PARAMETER_NAME = 'REASON'

AND FPP.BASE_FLOW_PARAMETER_ID = FPVP.BASE_FLOW_PARAMETER_ID

AND FPVP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PRE_PAYMENT_ID'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PRE_PAYMENT_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_CANCEL_PAYMENT')),

'MR',

(SELECT FLOW_PARAM_VALUE

FROM

PAY_FLOW_PARAMETERS_VL FPP,

PAY_FLOW_PARAM_VALUES FPVP

WHERE FPP.BASE_FLOW_PARAMETER_NAME = 'REASON'

AND FPP.BASE_FLOW_PARAMETER_ID = FPVP.BASE_FLOW_PARAMETER_ID

AND FPVP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PRE_PAYMENT_ID'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PRE_PAYMENT_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_EXTERNAL_MANUAL_PAYMENT')),

NULL) AS REASON,

AMOUNT,

decode(status_code,'V',

(SELECT INSTANCE_NAME

FROM PAY_FLOW_INSTANCES PFIP

WHERE PFIP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PERSON_PROCESS'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PAYMENT_REL_ACTION_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_VOID_PAYMENT')),

'C',

(SELECT INSTANCE_NAME

FROM PAY_FLOW_INSTANCES PFIP

WHERE PFIP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PRE_PAYMENT_ID'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PRE_PAYMENT_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_CANCEL_PAYMENT')),

'MR',

(SELECT INSTANCE_NAME

FROM PAY_FLOW_INSTANCES PFIP

WHERE PFIP.FLOW_INSTANCE_ID=

(SELECT MAX(FLOW_INSTANCE_ID) FLOW_INSTANCE_ID

FROM PAY_FLOW_PARAM_VALUES FPV ,

PAY_FLOW_PARAMETERS_VL FP, PAY_FLOWS PF

WHERE FP.BASE_FLOW_PARAMETER_NAME = 'PRE_PAYMENT_ID'

AND FP.BASE_FLOW_PARAMETER_ID = FPV.BASE_FLOW_PARAMETER_ID

AND FPV.FLOW_PARAM_VALUE = PRE_PAYMENT_ID

AND PF.FLOW_ID= FP.BASE_FLOW_ID

AND PF.BASE_FLOW_NAME='ORA_EXTERNAL_MANUAL_PAYMENT')),

NULL) FLOW_INSTANCE_NAME,

SOURCE,

PAYMENT_TYPE_ID,

LEGISLATION_CODE,

PAYMENT_TYPE_NAME,

PROCESS_NAME,

PROCESS_NAME_CODE,

PREPAY_PROC_SUBMIT_DATE,

PAYMENT_PROC_SUBMIT_DATE,

PRIORITY,

CURRENCY_CODE,

PREPAY_REL_ACTION_ID,

PREPAY_CUM_REL_ACTION_ID,

PAYROLL_ID,

CALC_BREAKDOWN_ID,

PREPAY_PAYMENT_DATE,

PREPAY_PROCESS_DATE,

PAYEE_TYPE,

PAYEE,

PAYEE_ID,

PAYROLL_RELATIONSHIP_NUMBER,

PERSON_NUMBER,

PERSON_ID,

LEGISLATIVE_DATA_GROUP_ID,

PAY_LOCKING_FLAG,

PAY_LOCKING_ACTION,

(Case when ROLL_UP_PAYMENT_ACTION_ID is not null

then 'N'

else

DECODE(PAY_LOCKING_FLAG, -1,(DECODE(STATUS_CODE,'U','Y','V','Y','N')),DECODE(

(SELECT ACTION_TYPE FROM pay_payroll_actions PA,

pay_payroll_rel_actions PRA WHERE

PA.payroll_action_id=PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID =PAY_LOCKING_FLAG

) ,'D',decode((select max(1)

from

pay_payroll_rel_actions paac2,

pay_payroll_rel_actions paac,

pay_action_interlocks pai

where pai.locking_action_id =PAY_LOCKING_FLAG

and pai.locked_action_id = paac.payroll_rel_action_id

and paac.pre_payment_id = paac2.pre_payment_id

and paac2.action_sequence >

paac.action_sequence),1,'N','Y'),'N'))

end) AS MAKE_EXTERNAL_PAYMENT_FLAG,

(Case when ROLL_UP_PAYMENT_ACTION_ID is not null

then 'N'

else

DECODE(PAY_LOCKING_FLAG, -1,(DECODE(STATUS_CODE,'U','Y','V','Y','N')),DECODE(

(SELECT ACTION_TYPE FROM pay_payroll_actions PA,

pay_payroll_rel_actions PRA WHERE

PA.payroll_action_id=PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID =PAY_LOCKING_FLAG

) ,'D',decode((select max(1)

from

pay_payroll_rel_actions paac2,

pay_payroll_rel_actions paac,

pay_action_interlocks pai

where pai.locking_action_id =PAY_LOCKING_FLAG

and pai.locked_action_id = paac.payroll_rel_action_id

and paac.pre_payment_id = paac2.pre_payment_id

and paac2.action_sequence >

paac.action_sequence),1,'N','Y'),'N'))

end) AS PREVENT_PAYMENT_FLAG,

(Case when ROLL_UP_PAYMENT_ACTION_ID is not null

then 'N'

else

DECODE(STATUS_CODE,'P','Y','T','Y','N')

end) AS VOID_PAYMENT_FLAG,

(Case when ROLL_UP_PAYMENT_ACTION_ID is not null

then 'N'

else

decode(STATUS_CODE,'C','Y',

DECODE(PAY_LOCKING_FLAG,

-1,(DECODE(STATUS_CODE,'U','N','R','N','T','N','Y')), DECODE(

(SELECT ACTION_TYPE FROM pay_payroll_actions PA,

pay_payroll_rel_actions PRA WHERE

PA.payroll_action_id=PRA.payroll_action_id

AND PRA.PAYROLL_REL_ACTION_ID =PAY_LOCKING_FLAG

) ,'D',

decode((select max(1)

from

pay_payroll_rel_actions paac2,

pay_payroll_rel_actions paac,

pay_action_interlocks pai

where pai.locking_action_id =PAY_LOCKING_FLAG

and pai.locked_action_id = paac.payroll_rel_action_id

and paac.pre_payment_id = paac2.pre_payment_id

and paac2.action_sequence >

paac.action_sequence),1,'N','Y')

,'N')))

end) AS ROLLBACK_FLAG,

'Y' AS ACCOUNT_NUMBER_VISIBLE_FLAG,

'Y' AS REASON_VISIBLE_FLAG,

'Y' AS PAYMENT_REF_VISIBLE_FLAG,

DECODE(PAYEE_TYPE,'ORGANIZATION','Y','PERSON',DECODE(STATUS_CODE,'MR','N','Y'),'N')

AS PPM_NAME_VISIBLE_FLAG,

DECODE(PAYEE_TYPE,'ORGANIZATION','Y','N') AS PAYEE_VISIBLE_FLAG, DECODE(STATUS_CODE,'U','Y','N') AS EXP_PMT_DT_VISIBLE_FLAG

FROM(

SELECT decode(EMP_REL_ACTION_ID,null,decode(CANCEL_REL_ACTION_ID,null,'N','Y'),decode(PAY_LOCKING_FLAG,-1,'N','Y')) SHOW_VOIDED_PAYMENT,

HR_PRE_PAY.PAYMENT_STATUS(DECODE(PAYMENT_REL_ACTION_ID,NULL,PRE_PAYMENT_ID,

decode(CANCEL_REL_ACTION_ID,null,PAYMENT_REL_ACTION_ID,CANCEL_REL_ACTION_ID)),

DECODE(PAYMENT_REL_ACTION_ID,NULL,PROCESS_NAME_CODE,

decode(CANCEL_REL_ACTION_ID,null,PAY_PROCESS_NAME_CODE,'E'))) AS STATUS_CODE,

HR_PRE_PAY.GET_PAYMENT_DATE(DECODE(PAYMENT_REL_ACTION_ID,NULL,PRE_PAYMENT_ID,

decode(CANCEL_REL_ACTION_ID,null,PAYMENT_REL_ACTION_ID,CANCEL_REL_ACTION_ID)),

DECODE(PAYMENT_REL_ACTION_ID,NULL,PROCESS_NAME_CODE,

decode(CANCEL_REL_ACTION_ID,null,PAY_PROCESS_NAME_CODE,'E'))) AS PAYMENT_DATE,

OPM,PAYROLL_ACTION_ID,PAYMENT_PAYROLL_ACTION_ID,PROCESS_DATE,process_start_date,process_end_date,PAYROLL_NAME,PAYMENT_REFERENCE,PPM_NAME,BANK_ACCOUNT_NUMBER,

BANK_ACCOUNT_TYPE, EXT_BANK_ACCOUNT_ID, IBAN, CHECK_DIGITS, SECONDARY_ACCOUNT_REFERENCE, BRANCH_PARTY_ID, BANK_NAME,

BANK_NUMBER, BRANCH_NUMBER, BRANCH_NAME, BIC_CODE, BANK_ACCOUNT_ID, BASE_PAYMENT_TYPE_NAME, PAYROLL_REL_ACTION_ID,

RELATIONSHIP_ID, PRE_PAYMENT_ID, AMOUNT, SOURCE, PAYMENT_TYPE_ID, LEGISLATION_CODE, PAYMENT_TYPE_NAME, PROCESS_NAME,

PROCESS_NAME_CODE,PREPAY_PROC_SUBMIT_DATE,

PAYMENT_PROC_SUBMIT_DATE,

PRIORITY, CURRENCY_CODE, PREPAY_REL_ACTION_ID, PREPAY_CUM_REL_ACTION_ID, PAYROLL_ID, CALC_BREAKDOWN_ID, PREPAY_PAYMENT_DATE,

PREPAY_PROCESS_DATE, PAYMENT_REL_ACTION_ID, PAYEE_TYPE, PAYEE, PAYEE_ID , PAYROLL_RELATIONSHIP_NUMBER, PERSON_NUMBER, PERSON_ID,

LEGISLATIVE_DATA_GROUP_ID, EMP_REL_ACTION_ID, CANCEL_REL_ACTION_ID, PAY_LOCKING_FLAG, PAY_LOCKING_ACTION,ORI_PAYMENT_REL_ACTION_ID,

ROLL_UP_PAYMENT_ACTION_ID,OBJECT_TYPE

FROM

( SELECT DISTINCT

OPMTL.ORG_PAYMENT_METHOD_NAME AS OPM,

AAC1.PAYROLL_ACTION_ID,

PAAC1.PAYROLL_ACTION_ID AS PAYMENT_PAYROLL_ACTION_ID,

NVL(PPAC1.EFFECTIVE_DATE,PAC1.EFFECTIVE_DATE) AS PROCESS_DATE,

PPAC1.start_date process_start_date,

PPAC1.end_date process_end_date,

PPAY.PAYROLL_NAME,

PAAC1.SERIAL_NUMBER AS PAYMENT_REFERENCE,

PPM.NAME AS PPM_NAME,

IEBA.MASKED_BANK_ACCOUNT_NUM AS BANK_ACCOUNT_NUMBER,

IEBA.BANK_ACCOUNT_TYPE,

IEBA.EXT_BANK_ACCOUNT_ID,

IEBA.MASKED_IBAN IBAN,

IEBA.CHECK_DIGITS,

IEBA.SECONDARY_ACCOUNT_REFERENCE,

CABB.BRANCH_PARTY_ID,

CABB.BANK_NAME,

CABB.BANK_NUMBER,

CABB.BRANCH_NUMBER,

CABB.BANK_BRANCH_NAME BRANCH_NAME,

CABB.EFT_SWIFT_CODE BIC_CODE,

PPM.BANK_ACCOUNT_ID AS BANK_ACCOUNT_ID,

PPTLEG.BASE_PAYMENT_TYPE_NAME,

PPP.PAYROLL_REL_ACTION_ID,

PPR.PAYROLL_RELATIONSHIP_ID AS RELATIONSHIP_ID,

PPP.PRE_PAYMENT_ID,

PPP.VALUE AMOUNT,

(SELECT ORG_PAYMENT_METHOD_NAME FROM PAY_ORG_PAY_METHODS_VL org

WHERE org.PARENT_ORG_PAY_METHOD_ID=OPM.ORG_PAYMENT_METHOD_ID

AND org.ORG_PAYMENT_METHOD_ID= PPP.PAYER_ORG_PAYMENT_METHOD_ID

AND PAC1.EFFECTIVE_DATE BETWEEN org.effective_start_date AND org.effective_end_date

AND TYPE='PAYER') AS SOURCE,

PPT.PAYMENT_TYPE_ID,

decode(PPTLEG.BASE_PAYMENT_TYPE_NAME,'International Transfer',CABB.BANK_HOME_COUNTRY,PPTLEG.LEGISLATION_CODE) LEGISLATION_CODE,

PPTLEGTL.PAYMENT_TYPE_NAME,

LO2.MEANING AS PROCESS_NAME,

PAC1.ACTION_TYPE AS PROCESS_NAME_CODE,

PPAC1.action_type AS PAY_PROCESS_NAME_CODE,

FTI.CREATION_DATE AS PREPAY_PROC_SUBMIT_DATE,

PFTI.CREATION_DATE AS PAYMENT_PROC_SUBMIT_DATE,

PPM.PRIORITY,

OPM.CURRENCY_CODE,

PPP.PAYROLL_REL_ACTION_ID PREPAY_REL_ACTION_ID,

PPP.PAYROLL_REL_ACTION_ID PREPAY_CUM_REL_ACTION_ID,

PAC1.PAYROLL_ID,

PPP.CALC_BREAKDOWN_ID,

PPP.EFFECTIVE_DATE PREPAY_PAYMENT_DATE,

PAC1.EFFECTIVE_DATE PREPAY_PROCESS_DATE,

PAAC1.PAYROLL_REL_ACTION_ID PAYMENT_REL_ACTION_ID,

DECODE(PPP.THIRD_PARTY_PAYEE_ID,NULL,'PERSON','ORGANIZATION')

PAYEE_TYPE,

DECODE(PPP.THIRD_PARTY_PAYEE_ID,NULL,PEO.FULL_NAME,

(SELECT PARTYEO.PARTY_NAME

FROM HZ_PARTIES PARTYEO,

HZ_PARTY_USG_ASSIGNMENTS PARTYUSAGEASSIGNMENTEO

WHERE PARTYEO.PARTY_ID =

PARTYUSAGEASSIGNMENTEO.PARTY_ID

AND PARTYUSAGEASSIGNMENTEO.PARTY_USAGE_CODE IN

('EXTERNAL_PAYEE','PROFESSIONAL_BODY','PENSION_PROVIDER','PAYMENT_ISSUING_AUTHORITY','DISABILITY_ORGANIZATION','BARGAINING_ASSOCIATION')

AND PPP.THIRD_PARTY_PAYEE_ID =PARTYEO.PARTY_ID

)) PAYEE,

PPP.THIRD_PARTY_PAYEE_ID AS PAYEE_ID ,

PPR.PAYROLL_RELATIONSHIP_NUMBER,

PNM.PERSON_NUMBER,

PNM.PERSON_ID,

PPR.LEGISLATIVE_DATA_GROUP_ID AS LEGISLATIVE_DATA_GROUP_ID,

extcal.pmt_object_action_id ORI_PAYMENT_REL_ACTION_ID,

decode(OPM.ROLLUP_PAYMENT_FLAG,'N',null,

(select PAYROLL_RELATIONSHIP_ACTION_ID from pay_contrib_payments pcp

where pcp.CONTRIBUTING_PRE_PAYMENT_ID=ppp.PRE_PAYMENT_ID

and rownum=1 )) ROLL_UP_PAYMENT_ACTION_ID,

decode(extcal.payment_reason,'ORA_EXTERNAL_PAYMENT',extcal.emp_object_action_id,null) EMP_REL_ACTION_ID,

decode(extcal.payment_reason,'ORA_PREVENT_PAYMENT',extcal.emp_object_action_id,null) CANCEL_REL_ACTION_ID,

decode(PAAC1.object_type,'PRE',

nvl((select pralk.PAYROLL_REL_ACTION_ID

from

pay_payroll_actions ppalk,

pay_action_interlocks plk,

PAY_PAYROLL_REL_ACTIONS pralk

where plk.locked_action_id=PAAC1.PAYROLL_REL_ACTION_ID

and pralk.PAYROLL_REL_ACTION_ID = plk.locking_action_id

and pralk.payroll_action_id = ppalk.payroll_action_id

and ppalk.action_type='D'),-1),

nvl((select pralk.OBJECT_ACTION_ID

from

pay_payroll_actions ppalk,

pay_action_interlocks plk,

pay_object_actions pralk

where plk.locked_action_id=PAAC1.PAYROLL_REL_ACTION_ID

and pralk.object_ACTION_ID = plk.locking_action_id

and pralk.payroll_action_id = ppalk.payroll_action_id

and ppalk.action_type='D'),-1)) PAY_LOCKING_FLAG,

null PAY_LOCKING_ACTION,

PPAC1.payment_reason,

PAAC1.OBJECT_TYPE

FROM PAY_ORG_PAY_METHODS_TL OPMTL,

PAY_PAYMENT_TYPES_TL PPTTL,

PAY_ORG_PAY_METHODS_F OPM,

PAY_PRE_PAYMENTS PPP,

PAY_PERSON_PAY_METHODS_F PPM,

HCM_LOOKUPS LO1,

HCM_LOOKUPS LO2,

PAY_PAYMENT_TYPES PPT,

PAY_PAYROLL_REL_ACTIONS AAC1,

PAY_PAYROLL_ACTIONS PAC1,

(select pra.object_action_id PAYROLL_REL_ACTION_ID, pra.serial_number,

pra.payroll_action_id,pcp.CONTRIBUTING_PRE_PAYMENT_ID PRE_PAYMENT_ID,'POA' object_type

from pay_contrib_payments pcp

,pay_object_actions pra

,pay_payroll_actions ppa

where pcp.pre_payment_id=pra.object_id

and pra.object_type in ('PRE_PAYMENT_ID','PPP')

and pra.action_status='C'

and ppa.action_type <> 'D'

and ppa.payroll_action_id=pra.payroll_action_id

union

select pra.PAYROLL_REL_ACTION_ID, pra.serial_number,

pra.payroll_action_id,pra.PRE_PAYMENT_ID ,'PRE' object_type

from pay_payroll_rel_actions pra) PAAC1,

PAY_PAYROLL_ACTIONS PPAC1,

PAY_ALL_PAYROLLS_F PPAY,

PAY_PAY_RELATIONSHIPS_DN PPR,

PER_PERSON_NAMES_F PEO,

PER_ALL_PEOPLE_F PNM,

PER_LEGISLATIVE_DATA_GROUPS PLDG,

PAY_PAYMENT_TYPES PPTLEG,

PAY_PAYMENT_TYPES_TL PPTLEGTL,

PAY_REQUESTS PRQ,

PAY_FLOW_INSTANCES FTI,

PAY_REQUESTS PPRQ,

PAY_FLOW_INSTANCES PFTI,

iby_ext_bank_accounts IEBA,

CE_ALL_BANK_BRANCHES_V CABB,

(SELECT emp_poa.object_action_id emp_object_action_id,

nvl(emp_ppa.payment_reason,'ORA_EXTERNAL_PAYMENT') payment_reason,

pymt_poa.object_action_id pmt_object_action_id

from (select payroll_rel_action_id object_action_id,

payroll_action_id,

action_status,

pre_payment_id

from pay_payroll_rel_actions pra

) pymt_poa,

pay_action_interlocks int_pymt,

pay_payroll_rel_actions pra_pp,

pay_payroll_actions ppa_pp,

pay_action_interlocks int_pp,

(select payroll_rel_action_id object_action_id,

payroll_action_id,

action_status,

pre_payment_id

from pay_payroll_rel_actions pra

) emp_poa,

pay_payroll_actions emp_ppa

where int_pymt.locking_action_id=pymt_poa.object_action_id

and int_pymt.locked_action_id=pra_pp.payroll_rel_action_id

and pra_pp.payroll_action_id =ppa_pp.payroll_action_id

and ppa_pp.action_type in ('PP','P','U')

and pra_pp.action_status='C'

and pra_pp.payroll_rel_action_id=int_pp.locked_action_id

and int_pp.locking_action_id=emp_poa.object_action_id

and emp_poa.action_status='C'

and emp_poa.payroll_action_id=emp_ppa.payroll_action_id

and emp_ppa.action_type='E'

and emp_poa.pre_payment_id= pymt_poa.pre_payment_id

union all

SELECT emp_poa.object_action_id emp_object_action_id,

emp_ppa.payment_reason,

pymt_poa.object_action_id pmt_object_action_id

from (

select object_action_id,

payroll_action_id,

action_status,

object_id pre_payment_id

from pay_object_actions poa) pymt_poa,

pay_action_interlocks int_pymt,

pay_payroll_rel_actions pra_pp,

pay_payroll_actions ppa_pp,

pay_action_interlocks int_pp,

(select object_action_id,

payroll_action_id,

action_status,

object_id pre_payment_id

from pay_object_actions poa) emp_poa,

pay_payroll_actions emp_ppa

where int_pymt.locking_action_id=pymt_poa.object_action_id

and int_pymt.locked_action_id=pra_pp.payroll_rel_action_id

and pra_pp.payroll_action_id =ppa_pp.payroll_action_id

and ppa_pp.action_type in ('PP','P','U')

and pra_pp.action_status='C'

and pra_pp.payroll_rel_action_id=int_pp.locked_action_id

and int_pp.locking_action_id=emp_poa.object_action_id

and emp_poa.action_status='C'

and emp_poa.payroll_action_id=emp_ppa.payroll_action_id

and emp_ppa.action_type='E'

and emp_poa.pre_payment_id= pymt_poa.pre_payment_id

) extcal

WHERE extcal.emp_object_action_id(+) <>PAAC1.PAYROLL_REL_ACTION_ID

and extcal.pmt_object_action_id(+) =PAAC1.PAYROLL_REL_ACTION_ID

and OPM.ORG_PAYMENT_METHOD_ID = OPMTL.ORG_PAYMENT_METHOD_ID

AND OPMTL.LANGUAGE = USERENV('LANG')

AND PPT.PAYMENT_TYPE_ID = PPTTL.PAYMENT_TYPE_ID

AND PPTLEGTL.LANGUAGE = USERENV('LANG')

AND PPTLEG.LEGISLATION_CODE =PLDG.LEGISLATION_CODE

AND PLDG.LEGISLATIVE_DATA_GROUP_ID=OPM.LEGISLATIVE_DATA_GROUP_ID

AND PPTLEG.BASE_PAYMENT_TYPE_ID =PPT.PAYMENT_TYPE_ID

AND PPTLEGTL.PAYMENT_TYPE_ID =PPTLEG.PAYMENT_TYPE_ID

AND PPAY.PAYROLL_ID = PAC1.PAYROLL_ID

AND PPR.PAYROLL_RELATIONSHIP_ID = AAC1.PAYROLL_RELATIONSHIP_ID

AND PPR.PERSON_ID = PNM.PERSON_ID (+)

AND PPR.PERSON_ID = PEO.PERSON_ID (+)

AND PPP.PAYROLL_REL_ACTION_ID = AAC1.PAYROLL_REL_ACTION_ID

AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID

AND PPAC1.PAYROLL_ACTION_ID(+) = PAAC1.PAYROLL_ACTION_ID

AND LO1.LOOKUP_TYPE = 'PAY_PAYMENT_SOURCE'

AND LO1.LOOKUP_CODE =

DECODE(PAC1.ORG_PAYMENT_METHOD_ID,NULL,DECODE(PPM.PERSONAL_PAYMENT_METHOD_ID,NULL,'D',

'P'), 'O')

AND LO2.LOOKUP_TYPE = 'ACTION_TYPE'

AND LO2.LOOKUP_CODE = PAC1.ACTION_TYPE

AND OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID

AND PAC1.EFFECTIVE_DATE BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE

AND PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID

AND PPP.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID (+)

AND (PPP.PERSONAL_PAYMENT_METHOD_ID IS NULL

OR PAC1.EFFECTIVE_DATE BETWEEN PPM.EFFECTIVE_START_DATE AND

PPM.EFFECTIVE_END_DATE)

AND (PEO.PERSON_ID IS NULL

OR PAC1.EFFECTIVE_DATE BETWEEN PEO.EFFECTIVE_START_DATE AND

PEO.EFFECTIVE_END_DATE)

AND (PNM.PERSON_ID IS NULL

OR PAC1.EFFECTIVE_DATE BETWEEN PNM.EFFECTIVE_START_DATE AND

PNM.EFFECTIVE_END_DATE)

AND PAC1.EFFECTIVE_DATE BETWEEN PPAY.EFFECTIVE_START_DATE AND PPAY.EFFECTIVE_END_DATE

AND PAAC1.PRE_PAYMENT_ID (+) = PPP.PRE_PAYMENT_ID

AND PEO.NAME_TYPE ='GLOBAL'

AND PRQ.PAY_REQUEST_ID =PAC1.PAY_REQUEST_ID

AND FTI.FLOW_INSTANCE_ID = PRQ.FLOW_INSTANCE_ID

AND PPRQ.PAY_REQUEST_ID(+) =PPAC1.PAY_REQUEST_ID

AND PFTI.FLOW_INSTANCE_ID(+) = PPRQ.FLOW_INSTANCE_ID

AND PPP.PAYEE_BANK_ACCOUNT_ID =IEBA.EXT_BANK_ACCOUNT_ID(+)

AND IEBA.BRANCH_ID = CABB.BRANCH_PARTY_ID(+)

)

)

ORDER BY PROCESS_DATE DESC,

PRE_PAYMENT_ID DESC,

PAYMENT_REL_ACTION_ID DESC