PAY_PROC_REL_ACT_KEY_INFO

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
AMOUNT PAYMENT_STATUS DEFAULT_CURRENCY_CODE AMOUNT_DEBITED AMOUNT_CREDITED PAYROLL_ACTION_ID PAYROLL_REL_ACTION_ID

Name

AMOUNT

PAYMENT_STATUS

DEFAULT_CURRENCY_CODE

AMOUNT_DEBITED

AMOUNT_CREDITED

PAYROLL_ACTION_ID

PAYROLL_REL_ACTION_ID

Query

SQL_Statement
select zzz.AMOUNT,zzz.PAYMENT_STATUS,zzz.DEFAULT_CURRENCY_CODE,zzz.Amount_Debited,zzz.Amount_Credited,zzz.payroll_action_id,zzz.payroll_rel_action_id FROM (select DECODE(pra.action_status,'U',NULL,'E',NULL, (case when ppa.ACTION_TYPE =('CTG') then null when ppa.ACTION_TYPE in('B' ,'I') then (select sum(decode( (select i.position from pay_bal_grp_usage_items i, pay_balance_dimensions dim where i.bal_grp_usage_id in (select bgu.BAL_GRP_USAGE_ID from pay_bal_grp_usages bgu where base_group_usage_name='SOE Summary Gross to Net Statement of Earnings') and i.source_type='BD' and nvl(i.legislation_code,nvl(ldg.legislation_code,' nvl(ldg.legislation_code,' and dim.balance_dimension_id=i.source_id and dim.period_type='RUN' and rownum=1),1,mat.value1,2,mat.value2,3,mat.value3,mat.value1 )) as Net_Pay from table(pay_balance_view_pkg.get_balance_matrix( p_balance_group_usage_id => (select bgu.BAL_GRP_USAGE_ID from pay_bal_grp_usages bgu where base_group_usage_name='SOE Summary Gross to Net Statement of Earnings'), p_payroll_rel_action_id => pra.payroll_rel_action_id) ) mat, pay_balance_types bal where mat.balance_type_id=bal.balance_type_id and bal.remuneration_flag='Y') when (select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'SOE_RESULTS%')=1 then pay_balance_view_pkg.get_net_pay_bal_agg_ovr(pra.payroll_rel_action_id,ldg.legislation_code) when (select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PREPAY_RESULTS%')=1 then (SELECT (SUM(PPP.BASE_CURRENCY_VALUE)) FROM PAY_PRE_PAYMENTS PPP, PAY_ORG_PAY_METHODS_F opm, pay_legislative_data_groups ldg WHERE PPP.PAYROLL_REL_ACTION_ID = pra.PAYROLL_REL_ACTION_ID and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID and opm.legislative_Data_group_id=ldg.legislative_Data_group_id and ppa.effective_date between opm.effective_start_date and opm.effective_end_date GROUP BY PPP.PAYROLL_REL_ACTION_ID) when (select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PAYMENT_RESULTS%')=1 then (SELECT (sum(ppp.value)) FROM PAY_PRE_PAYMENTS PPP where pra.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID) else null end)) AMOUNT, /* DECODE(pra.action_status,'U',NULL,'E',NULL,DECODE((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PAYMENT_RESULTS%'),1, ( case when HR_PRE_PAY.PAYMENT_STATUS(pra.PAYROLL_REL_ACTION_ID,ppa.action_type)= 'V' then decode((select emp_ppa.payment_reason from (select payroll_rel_action_id object_action_id, payroll_action_id, action_status, pre_payment_id from pay_payroll_rel_actions pra union all 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 payroll_rel_action_id object_action_id, payroll_action_id, action_status, pre_payment_id from pay_payroll_rel_actions pra union all 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 pymt_poa.object_action_id= pra.PAYROLL_REL_ACTION_ID and 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.object_action_id != pra.PAYROLL_REL_ACTION_ID 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),'ORA_PREVENT_PAYMENT', (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS' and LK1.LOOKUP_CODE='C'), 'ORA_EXTERNAL_PAYMENT', (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS' and LK1.LOOKUP_CODE='MR'), (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS' and LK1.LOOKUP_CODE='V')) else (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS' AND LK1.LOOKUP_CODE=HR_PRE_PAY.PAYMENT_STATUS(pra.PAYROLL_REL_ACTION_ID,ppa.action_type)) end ) ,null)) */ null PAYMENT_STATUS, decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1,( case when (select count(1) from (select sum(value) from pay_payment_costs where payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID and debit_or_credit='D' group by CURRENCY_CODE)) <= 1 then (select sum(decode(ppc.debit_or_credit,'D',ppc.value)) from pay_payment_costs ppc where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID group by ppc.payroll_rel_action_id) else null end),null) Amount_Debited, decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1,( case when (select count(1) from (select sum(value) from pay_payment_costs where payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID and debit_or_credit='C' group by CURRENCY_CODE)) <= 1 then (select sum(decode(ppc.debit_or_credit,'C',ppc.value)) from pay_payment_costs ppc where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID group by ppc.payroll_rel_action_id) else null end) ,null) Amount_Credited, decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1, (select max(ppc.currency_code) from pay_payment_costs ppc where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID), decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PAYMENT_RESULTS%'),1, (SELECT opm.CURRENCY_CODE FROM PAY_PRE_PAYMENTS PPP, PAY_ORG_PAY_METHODS_F opm, pay_legislative_data_groups ldg WHERE PPP.PRE_PAYMENT_ID = pra.PRE_PAYMENT_ID and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID and opm.legislative_Data_group_id=ldg.legislative_Data_group_id and ppa.effective_date between opm.effective_start_date and opm.effective_end_date), decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PREPAY_RESULTS%'),1, ldg.default_currency_code, decode((select 1 from pay_action_classifications cls where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'SOE_RESULTS%'),1, ldg.default_currency_code,null))))default_currency_code, PRA.PAYROLL_REL_ACTION_ID,PRA.PAYROLL_ACTION_ID from pay_payroll_rel_actions pra,pay_payroll_actions ppa,per_legislative_data_groups_vl ldg where pra.payroll_action_id=ppa.payroll_action_id AND ppa.legislative_data_group_id = ldg.legislative_data_group_id (+) ) zzz

SQL_Statement

select

zzz.AMOUNT,zzz.PAYMENT_STATUS,zzz.DEFAULT_CURRENCY_CODE,zzz.Amount_Debited,zzz.Amount_Credited,zzz.payroll_action_id,zzz.payroll_rel_action_id

FROM (select

DECODE(pra.action_status,'U',NULL,'E',NULL,

(case when ppa.ACTION_TYPE =('CTG') then null when ppa.ACTION_TYPE in('B' ,'I') then

(select sum(decode(

(select i.position from

pay_bal_grp_usage_items i,

pay_balance_dimensions dim

where i.bal_grp_usage_id in

(select bgu.BAL_GRP_USAGE_ID

from pay_bal_grp_usages bgu where base_group_usage_name='SOE Summary Gross to Net Statement of Earnings')

and i.source_type='BD'

and nvl(i.legislation_code,nvl(ldg.legislation_code,'

nvl(ldg.legislation_code,'

and dim.balance_dimension_id=i.source_id

and dim.period_type='RUN'

and rownum=1),1,mat.value1,2,mat.value2,3,mat.value3,mat.value1

)) as Net_Pay

from

table(pay_balance_view_pkg.get_balance_matrix(

p_balance_group_usage_id => (select bgu.BAL_GRP_USAGE_ID

from pay_bal_grp_usages bgu where base_group_usage_name='SOE Summary Gross to Net Statement of Earnings'),

p_payroll_rel_action_id => pra.payroll_rel_action_id)

) mat, pay_balance_types bal

where mat.balance_type_id=bal.balance_type_id

and bal.remuneration_flag='Y')

when (select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like

'SOE_RESULTS%')=1

then

pay_balance_view_pkg.get_net_pay_bal_agg_ovr(pra.payroll_rel_action_id,ldg.legislation_code)

when (select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like

'PREPAY_RESULTS%')=1

then

(SELECT

(SUM(PPP.BASE_CURRENCY_VALUE))

FROM PAY_PRE_PAYMENTS PPP,

PAY_ORG_PAY_METHODS_F opm,

pay_legislative_data_groups ldg

WHERE PPP.PAYROLL_REL_ACTION_ID = pra.PAYROLL_REL_ACTION_ID

and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID

and opm.legislative_Data_group_id=ldg.legislative_Data_group_id

and ppa.effective_date between opm.effective_start_date and opm.effective_end_date

GROUP BY PPP.PAYROLL_REL_ACTION_ID) when (select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like

'PAYMENT_RESULTS%')=1

then (SELECT (sum(ppp.value))

FROM PAY_PRE_PAYMENTS PPP

where pra.PRE_PAYMENT_ID = PPP.PRE_PAYMENT_ID) else null end)) AMOUNT,

/* DECODE(pra.action_status,'U',NULL,'E',NULL,DECODE((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PAYMENT_RESULTS%'),1,

(

case when

HR_PRE_PAY.PAYMENT_STATUS(pra.PAYROLL_REL_ACTION_ID,ppa.action_type)= 'V'

then

decode((select emp_ppa.payment_reason from (select payroll_rel_action_id object_action_id,

payroll_action_id,

action_status,

pre_payment_id

from pay_payroll_rel_actions pra

union all

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 payroll_rel_action_id object_action_id,

payroll_action_id,

action_status,

pre_payment_id

from pay_payroll_rel_actions pra

union all

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 pymt_poa.object_action_id= pra.PAYROLL_REL_ACTION_ID and 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.object_action_id != pra.PAYROLL_REL_ACTION_ID 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),'ORA_PREVENT_PAYMENT',

(SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS'

and LK1.LOOKUP_CODE='C'),

'ORA_EXTERNAL_PAYMENT',

(SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS'

and LK1.LOOKUP_CODE='MR'),

(SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS'

and LK1.LOOKUP_CODE='V'))

else (SELECT MEANING FROM HCM_LOOKUPS LK1 WHERE LK1.LOOKUP_TYPE= 'PAY_STATUS'

AND

LK1.LOOKUP_CODE=HR_PRE_PAY.PAYMENT_STATUS(pra.PAYROLL_REL_ACTION_ID,ppa.action_type))

end

)

,null)) */

null PAYMENT_STATUS,

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1,( case when (select count(1) from

(select sum(value)

from pay_payment_costs where payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID

and debit_or_credit='D'

group by CURRENCY_CODE)) <= 1

then (select sum(decode(ppc.debit_or_credit,'D',ppc.value))

from pay_payment_costs ppc

where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID

group by ppc.payroll_rel_action_id)

else null end),null) Amount_Debited,

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1,( case when (select count(1) from

(select sum(value)

from pay_payment_costs where payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID

and debit_or_credit='C'

group by CURRENCY_CODE)) <= 1

then (select sum(decode(ppc.debit_or_credit,'C',ppc.value))

from pay_payment_costs ppc

where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID

group by ppc.payroll_rel_action_id)

else null end) ,null) Amount_Credited,

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'COST_PAY_RESULTS%'),1,

(select max(ppc.currency_code)

from pay_payment_costs ppc

where ppc.payroll_rel_action_id=pra.PAYROLL_REL_ACTION_ID),

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PAYMENT_RESULTS%'),1,

(SELECT opm.CURRENCY_CODE

FROM PAY_PRE_PAYMENTS PPP,

PAY_ORG_PAY_METHODS_F opm,

pay_legislative_data_groups ldg

WHERE PPP.PRE_PAYMENT_ID = pra.PRE_PAYMENT_ID

and ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID

and opm.legislative_Data_group_id=ldg.legislative_Data_group_id

and ppa.effective_date between opm.effective_start_date and opm.effective_end_date),

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'PREPAY_RESULTS%'),1,

ldg.default_currency_code,

decode((select 1 from pay_action_classifications cls

where cls.ACTION_TYPE=ppa.ACTION_TYPE and CLASSIFICATION_NAME like 'SOE_RESULTS%'),1,

ldg.default_currency_code,null))))default_currency_code,

PRA.PAYROLL_REL_ACTION_ID,PRA.PAYROLL_ACTION_ID

from pay_payroll_rel_actions pra,pay_payroll_actions ppa,per_legislative_data_groups_vl ldg

where pra.payroll_action_id=ppa.payroll_action_id

AND ppa.legislative_data_group_id = ldg.legislative_data_group_id (+)

) zzz