1. Tables and Views for HCM
  2. PAY_PERSON_PAYMT_ANA_VL

PAY_PERSON_PAYMT_ANA_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

ACTION_TYPE

OBJECT_ACTION_ID

PAYMENT_STATUS_CODE

PAYMENT_STATUS

AMOUNT

TOTAL_AMOUNT

CURRENCY_CODE

PAYMENT_ACTION_ID

ORG_PAYMENT_METHOD_NAME

Query

SQL_Statement

select payRes.ACTION_TYPE, payRes.OBJECT_ACTION_ID, payRes.payment_status_code,(select meaning from HCM_LOOKUPS LK

where LK.LOOKUP_TYPE= 'PAY_STATUS'

and LK.LOOKUP_CODE =payRes.payment_status_code) payment_status, payRes.Amount,

payRes.total_amount,

payRes.CURRENCY_CODE, payRes.PAYMENT_ACTION_ID,

payRes.ORG_PAYMENT_METHOD_NAME

from (

select pay.ACTION_TYPE,pay.payment_Action_type,pay.OBJECT_ACTION_ID,

(case when pay.payment_status_code= 'V' then

decode((select max(PAYMENT_REASON)

from PAY_PAYROLL_REL_ACTIONS emp, pay_payroll_actions eppa

where emp.pre_payment_id=pay.pre_payment_id

and eppa.PAYROLL_ACTION_ID=emp.PAYROLL_ACTION_ID

and eppa.action_type='E'

and exists(select max(1) from pay_action_interlocks lk where lk.locked_action_id=pay.OBJECT_ACTION_ID)),'ORA_EXTERNAL_PAYMENT','MR','ORA_PREVENT_PAYMENT','C',pay.payment_status_code)

else pay.payment_status_code end) payment_status_code,

pay.Amount,

sum(pay.Amount) OVER (PARTITION BY OBJECT_ACTION_ID) AS total_amount,

pay.CURRENCY_CODE,

pay.PAYMENT_ACTION_ID ,

pay.ORG_PAYMENT_METHOD_NAME

from (

select PAC1.ACTION_TYPE,PPAC1.action_type payment_Action_type,PPP.PAYROLL_REL_ACTION_ID OBJECT_ACTION_ID,PPP.pre_payment_id,

PYAAC1.PAYROLL_REL_ACTION_ID PAYMENT_ACTION_ID,

(PPP.BASE_CURRENCY_VALUE) Amount,

(case when PYAAC1.PAYROLL_REL_ACTION_ID is not null then

hr_pre_pay.payment_status(PYAAC1.PAYROLL_REL_ACTION_ID,PPAC1.action_type)

else

hr_pre_pay.payment_status(PPP.pre_payment_id,PAC1.ACTION_TYPE)

end) payment_status_code,

(case when PPP.ORG_PAYMENT_METHOD_ID is not null then

(select max(OPM.CURRENCY_CODE) from PAY_ORG_PAY_METHODS_F OPM where OPM.ORG_PAYMENT_METHOD_ID = PPP.ORG_PAYMENT_METHOD_ID)

else

(select max(ldg.DEFAULT_CURRENCY_CODE) from PER_LEGISLATIVE_DATA_GROUPS ldg where ldg.legislative_data_group_id = pac1.legislative_data_group_id)

end)

CURRENCY_CODE,

opm.ORG_PAYMENT_METHOD_NAME

from

PAY_PRE_PAYMENTS PPP,

PAY_PAYROLL_ACTIONS PAC1,

PAY_PAYROLL_REL_ACTIONS PAAC1,

PAY_PAYROLL_ACTIONS PPAC1,

PAY_PAYROLL_REL_ACTIONS PYAAC1,

pay_org_pay_methods_vl opm

where PAC1.PAYROLL_ACTION_ID = PAAC1.PAYROLL_ACTION_ID

AND PAAC1.PAYROLL_REL_ACTION_ID = PPP.PAYROLL_REL_ACTION_ID

and PYAAC1.pre_payment_id(+)=PPP.pre_payment_id

and PYAAC1.PAYROLL_ACTION_ID= PPAC1.PAYROLL_ACTION_ID(+)

and opm.ORG_PAYMENT_METHOD_ID(+)=ppp.ORG_PAYMENT_METHOD_ID

and PAC1.effective_date between EFFECTIVE_START_DATE(+) and EFFECTIVE_END_DATE(+)

and not exists(select 1 from PAY_PAYROLL_ACTIONS EPAC1,

PAY_PAYROLL_REL_ACTIONS EYAAC1

where EYAAC1.PAYROLL_ACTION_ID= EPAC1.PAYROLL_ACTION_ID

and EPAC1.action_type='E'

and PYAAC1.PAYROLL_REL_ACTION_ID= EYAAC1.PAYROLL_REL_ACTION_ID)

)pay) payRes