Hi,
I am currently working in a requirement to get the below column
However, I couldn't find the payment reference, source, and position costing and tax reporting unit in the query.
Can anyone help me with this?
Payroll Statutory Unit | Tax Reporting Unit | Payment Status | Payment Source | Payment Type | Payment Method | Prepayment Action ID | Payroll | Person Number | Payroll Relationship Number | First Name | Last Name | Person Name | Payment Reference | Bank Name | Account Number | Account Name | Account Type | Payment Amount | Payment Date | Currency | Position Costing |
|---|
|
Here is the query I am currently working on,
SELECT DISTINCT
PPP.PRE_PAYMENT_ID PRE_PAYMENT_ACTION_ID
,PPP.PAYROLL_REL_ACTION_ID PAYROLL_REL_ACTION_ID
,OPMTL.ORG_PAYMENT_METHOD_NAME ORG_PAYMENT_METHOD_NAME
,PPTLEGTL.PAYMENT_TYPE_NAME PAYMENT_TYPE_NAME
,OPM.CURRENCY_CODE CURRENCY_CODE
,PPP.VALUE PAYMENT_AMOUNT
,TO_CHAR(PAC1.EFFECTIVE_DATE,'MM/DD/YYYY') PAYMENT_DATE
,IEBC.BANK_ACCOUNT_NAME
,IEBC.BANK_ACCOUNT_NUMBER
,IEBC.BANK_ACCOUNT_TYPE
,IEBC.BANK_NAME
,PAC1.PAYROLL_ID
,PPAY.PAYROLL_NAME
,PPR.PAYROLL_RELATIONSHIP_NUMBER
,PNM.PERSON_NUMBER
,PEO.DISPLAY_NAME AS PERSON_NAME
,PEO.LAST_NAME
,PEO.FIRST_NAME
,PLE.NAME AS PAYROLL_STATUTORY_UNIT --LEGAL_EMPLOYER_NAME
,(SELECT NAME FROM XLE_ETB_PROFILES XLP WHERE XLP.PARTY_ID=HZ_PER.PARTY_ID) TRU
FROM
PAY_ORG_PAY_METHODS_TL OPMTL
,PAY_ORG_PAY_METHODS_F OPM
,PAY_PRE_PAYMENTS PPP
,PAY_PAYMENT_TYPES PPT
,PAY_PAYROLL_REL_ACTIONS AAC1
,PAY_PAYROLL_ACTIONS PAC1
,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
,PER_LEGAL_EMPLOYERS PLE
,PER_ALL_ASSIGNMENTS_M PAAM
,HZ_PARTIES HZ_PER
,IBY_EXT_BANK_ACCOUNTS_V IEBC
WHERE 1=1
AND OPM.ORG_PAYMENT_METHOD_ID =OPMTL.ORG_PAYMENT_METHOD_ID
AND OPMTL.LANGUAGE = USERENV('LANG')
AND PPTLEGTL.LANGUAGE = USERENV('LANG')
AND PPTLEGTL.PAYMENT_TYPE_ID =PPTLEG.PAYMENT_TYPE_ID
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 PPT.PAYMENT_TYPE_ID = OPM.PAYMENT_TYPE_ID
AND OPM.ORG_PAYMENT_METHOD_ID =PPP.ORG_PAYMENT_METHOD_ID
AND PPP.PAYROLL_REL_ACTION_ID =AAC1.PAYROLL_REL_ACTION_ID
AND PPAY.PAYROLL_ID = PAC1.PAYROLL_ID
AND PAC1.PAYROLL_ACTION_ID = AAC1.PAYROLL_ACTION_ID
AND AAC1.PAYROLL_RELATIONSHIP_ID=PPR.PAYROLL_RELATIONSHIP_ID
AND PPR.PERSON_ID = PNM.PERSON_ID --
AND PPR.PERSON_ID = PEO.PERSON_ID --
AND PEO.NAME_TYPE ='GLOBAL'
--AND PNM.PERSON_NUMBER='19121'
--AND PPP.PRE_PAYMENT_ID='869109'
AND PLE.STATUS (+) = 'A'--
AND PNM.PERSON_ID = PAAM.PERSON_ID
AND PLE.ORGANIZATION_ID (+) = PAAM.LEGAL_ENTITY_ID
AND PAAM.PRIMARY_ASSIGNMENT_FLAG = 'Y'
AND PAAM.ASSIGNMENT_TYPE IN ('E','C','N')
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND HZ_PER.ORIG_SYSTEM_REFERENCE = PNM.PERSON_ID
AND HZ_PER.PARTY_ID = IEBC.PRIMARY_ACCT_OWNER_PARTY_ID
ORDER BY PPP.PRE_PAYMENT_ID DESC
Thanks in advance!
