1. Tables and Views for HCM
  2. PAY_NET_PAY_DIST_VL

PAY_NET_PAY_DIST_VL

Details

  • Schema: FUSION

  • Object owner: PAY

  • Object type: VIEW

Columns

Name

XWR_REL_ACTION_ID

PREPAYMENT_REL_ACTION_ID

PAYMENT_REL_ACTION_ID

PAYMENT_METHOD_NAME

BASE_PAYMENT_TYPE

PAYMENT_TYPE

PAYMENT_TYPE_ID

PAYMENT_REFERENCE

BANK_ACCOUNT_ID

ACCOUNT_NUMBER

IBAN

AMOUNT

CURRENCY_CODE

Query

SQL_Statement

SELECT DISTINCT

xmlData.XWR_REL_ACTION_ID,

AAC1.PAYROLL_REL_ACTION_ID PREPAYMENT_REL_ACTION_ID,

PAAC1.PAYROLL_REL_ACTION_ID PAYMENT_REL_ACTION_ID,

PPM.NAME Payment_Method_Name,

PPTLEG.BASE_PAYMENT_TYPE_NAME BASE_PAYMENT_TYPE,

PPTLEGTL.PAYMENT_TYPE_NAME PAYMENT_TYPE,

PPT.PAYMENT_TYPE_ID,

PAAC1.SERIAL_NUMBER PAYMENT_REFERENCE,

PPM.BANK_ACCOUNT_ID BANK_ACCOUNT_ID,

IEBA.MASKED_BANK_ACCOUNT_NUM ACCOUNT_NUMBER,

IEBA.MASKED_IBAN IBAN,

PPP.VALUE AMOUNT,

(select MAX(OPM.CURRENCY_CODE)

from PAY_ORG_PAY_METHODS_F opm

where ppp.ORG_PAYMENT_METHOD_ID=opm.ORG_PAYMENT_METHOD_ID

AND PAC1.effective_date BETWEEN OPM.EFFECTIVE_START_DATE AND OPM.EFFECTIVE_END_DATE) CURRENCY_CODE

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,

PAY_PAYMENT_TYPES PPT,

PAY_PAYROLL_REL_ACTIONS AAC1,

PAY_PAYROLL_ACTIONS PAC1,

PAY_PAYROLL_REL_ACTIONS PAAC1,

PAY_PAYROLL_ACTIONS PPAC1,

PER_LEGISLATIVE_DATA_GROUPS PLDG,

PAY_PAYMENT_TYPES PPTLEG,

PAY_PAYMENT_TYPES_TL PPTLEGTL,

iby_ext_bank_accounts IEBA,

CE_ALL_BANK_BRANCHES_V CABB,

(SELECT x.PAYMENT_REL_ACTION_ID,pra.payroll_rel_action_id XWR_REL_ACTION_ID

FROM per_legislative_data_groups ldg, pay_pay_relationships_dn prd,

pay_payroll_rel_actions pra, pay_file_details fd ,

XMLTABLE('/PAYMENT_DETAILS'

PASSING ( XMLTYPE('<PAYSLIP>'||fd.FILE_FRAGMENT||'</PAYSLIP>').EXTRACT('/PAYSLIP/PAYMENT_RECORD/PAYMENT_DETAILS') )

COLUMNS PAYMENT_REL_ACTION_ID VARCHAR2(100) PATH 'PAYMENT_REL_ACTION_ID') x

where fd.source_id= pra.payroll_rel_action_id

and prd.PAYROLL_RELATIONSHIP_ID=pra.PAYROLL_RELATIONSHIP_ID

and prd.legislative_data_group_id = ldg.legislative_data_group_id

) xmlData

WHERE 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 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 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 PAAC1.PRE_PAYMENT_ID (+) = PPP.PRE_PAYMENT_ID

AND PPP.PAYEE_BANK_ACCOUNT_ID =IEBA.EXT_BANK_ACCOUNT_ID(+)

AND IEBA.BRANCH_ID = CABB.BRANCH_PARTY_ID(+)

and PPP.THIRD_PARTY_PAYEE_ID is null

and PPAC1.action_type IN('H','M','A')

and PAAC1.payroll_rel_action_id=xmlData.PAYMENT_REL_ACTION_ID