PAY_PAYMENT_RESULTS

Details

Schema: FUSION
Object owner: PAY
Object type: VIEW

Columns

Name
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

Name

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

Query

SQL_Statement
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.PAYROLL_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','ORGANIZATION') 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_PROVIDER','PAYMENT_ISSUING_AUTH 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 PPR.PERSON_ID = PEO.PERSON_ID (+) 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)

SQL_Statement

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.PAYROLL_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','ORGANIZATION') 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_PROVIDER','PAYMENT_ISSUING_AUTH

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 PPR.PERSON_ID = PEO.PERSON_ID (+)

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)