Payment Source and positon costing Column Query

Sudhan

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!

Sumanth V -Oracle

@Sudhan -

This community focuses specifically on Oracle Analytics Server (OAS), Oracle Business Intelligence Enterprise Edition (OBIEE), and Oracle Analytics Publisher (OAP) queries.

For questions related to Fusion Applications, kindly post them in the Fusion Applications Community linked below for appropriate assistance:

https://community.oracle.com/customerconnect/

Feel free to reach out here for any OAS/OBIEE/OAP-related discussions!