Query Pre Payment details Oracle Fusion Cloud

Query Pre Payment details Oracle Fusion Cloud

1. SELECT PRE_PAYMENT_ID, PAYROLL_REL_ACTION_ID,
ORG_PAYMENT_METHOD_NAME, PAYMENT_METHOD_PRIORITY,
PAYMENT_TYPE_NAME, PAYMENT_TYPE_ID,
PAY_PAYMENT_SOURCE,
CURRENCY_CODE, PAYMENT_AMOUNT, PAYMENT_STATUS,
PREPAY_REL_ACTION_ID, PREPAY_CUM_REL_ACTION_ID,
PAYROLL_ID, CALC_BREAKDOWN_ID, PREPAY_PAYMENT_DATE,
PREPAY_PROCESS_DATE, PAYMENT_REL_ACTION_ID,
PAYROLL_NAME, PAYEE_TYPE, PAYEE_NAME,
PAYROLL_RELATIONSHIP_NUMBER, PERSON_NUMBER,
PAYMENT_REFERENCE, LEGISLATIVE_DATA_GROUP_ID
FROM (
SELECT DISTINCT PPP.PRE_PAYMENT_ID PRE_PAYMENT_ID,
PPP.PAYROLL_REL_ACTION_ID PAYROLL_REL_ACTION_ID,
OPMTL.ORG_PAYMENT_METHOD_NAME
ORG_PAYMENT_METHOD_NAME,
PPM.PRIORITY PAYMENT_METHOD_PRIORITY,
PPTLEGTL.PAYMENT_TYPE_NAME PAYMENT_TYPE_NAME,
PPT.PAYMENT_TYPE_ID PAYMENT_TYPE_ID,
LO1.MEANING PAY_PAYMENT_SOURCE,
OPM.CURRENCY_CODE CURRENCY_CODE,
PPP.VALUE PAYMENT_AMOUNT,
PAY_PROCESS_FLOW_UTILS.GET_PAYMENT_STATUS(AAC1.PAY
ROLL_REL_ACTION_ID,PPP.PRE_PAYMENT_ID)
PAYMENT_STATUS,
PPP.PAYROLL_REL_ACTION_ID PREPAY_REL_ACTION_ID,
PPP.PAYROLL_REL_ACTION_ID PREPAY_CUM_REL_ACTION_ID,
PAC1.PAYROLL_ID,
PPP.CALC_BREAKDOWN_ID,
PPP.EFFECTIVE_DATE PREPAY_PAYMENT_DATE,
PAC1.EFFECTIVE_DATE PREPAY_PROCESS_DATE,
PAAC1.PAYROLL_REL_ACTION_ID PAYMENT_REL_ACTION_ID,
PPAY.PAYROLL_NAME,
DECODE(PPP.THIRD_PARTY_PAYEE_ID,NULL,'PERSON','ORGANIZ
ATION') PAYEE_TYPE,
DECODE(PPP.THIRD_PARTY_PAYEE_ID,NULL,PEO.FULL_NAME,
(SELECT PARTYEO.PARTY_NAME
FROM HZ_PARTIES PARTYEO,
HZ_PARTY_USG_ASSIGNMENTS PARTYUSAGEASSIGNMENTEO
WHERE PARTYEO.PARTY_ID =
PARTYUSAGEASSIGNMENTEO.PARTY_ID
AND PARTYUSAGEASSIGNMENTEO.PARTY_USAGE_CODE IN
('EXTERNAL_PAYEE','PROFESSIONAL_BODY','PENSION_PROVIDE
R','PAYMENT_ISSUING_AUTH

2. ORITY','DISABILITY_ORGANIZATION','BARGAINING_ASSOCIATION')
AND PPP.THIRD_PARTY_PAYEE_ID =PARTYEO.PARTY_ID
)) PAYEE_NAME,
PPR.PAYROLL_RELATIONSHIP_NUMBER ,
PNM.PERSON_NUMBER,
PAAC1.SERIAL_NUMBER PAYMENT_REFERENCE,
PPR.LEGISLATIVE_DATA_GROUP_ID AS
LEGISLATIVE_DATA_GROUP_ID
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_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
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 PPAY.PAYROLL_ID = PAC1.PAYROLL_ID
AND PPR.PAYROLL_RELATIONSHIP_ID =
AAC1.PAYROLL_RELATIONSHIP_ID
AND PPR.PERSON_ID = PNM.PERSON_ID (+)
--AND PAC1.EFFECTIVE_DATE BETWEEN
PNM.EFFECTIVE_START_DATE AND PNM.EFFECTIVE_END_DATE
AND PPR.PERSON_ID = PEO.PERSON_ID (+)
--AND PAC1.EFFECTIVE_DATE BETWEEN

3. PEO.EFFECTIVE_START_DATE AND PEO.EFFECTIVE_END_DATE
AND PPP.PAYROLL_REL_ACTION_ID =
AAC1.PAYROLL_REL_ACTION_ID
AND PAC1.PAYROLL_ACTION_ID = AAC1.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 (PEO.PERSON_ID IS NULL OR PAC1.EFFECTIVE_DATE
BETWEEN PEO.EFFECTIVE_START_DATE AND
PEO.EFFECTIVE_END_DATE)
AND (PNM.PERSON_ID IS NULL OR PAC1.EFFECTIVE_DATE
BETWEEN PNM.EFFECTIVE_START_DATE AND
PNM.EFFECTIVE_END_DATE)
AND PAC1.EFFECTIVE_DATE BETWEEN
PPAY.EFFECTIVE_START_DATE AND
PPAY.EFFECTIVE_END_DATE
AND PAAC1.PRE_PAYMENT_ID (+) = PPP.PRE_PAYMENT_ID
AND PEO.NAME_TYPE ='GLOBAL'
ORDER BY PPP.PRE_PAYMENT_ID DESC)