CN_TP_PARTICIPANT_PAY_EXP

Details

Schema: FUSION
Object owner: CN
Object type: VIEW

Columns

Name
PAYRUN_ID HOLD_FLAG SOURCE_EARNING_ID PAY_TO_HOME_CURR_CNVRT PAY_AMT_PAY_CURR PAY_TO_FUNC_CURR_CNVRT PAY_AMT_HOME_CURR HOME_CURRENCY_CODE PAY_CURRENCY_CODE COMMISSION_VALUE PAYEE_PARTICIPANT_ID OBJECT_VERSION_NUMBER CREDITED_PARTICIPANT_ID PAYEE_EMPLOYEE_NUMBER CREDITED_EMPLOYEE_NUMBER SOURCE_EVENT_DATE PAY_PERIOD_ID PAY_AMT_FUNC_CURR SOURCE_TYPE PARTICIPANT_PAY_ID SOURCE_TRX_NUMBER SOURCE_ORG_ID EARNING_ID OBJECT_STATUS ADJUST_STATUS ADJUST_COMMENTS ORG_ID PLAN_COMPONENT_ID SRP_PLAN_ASSIGN_ID ROLE_ID RECOVERABLE_FLAG WAIVE_FLAG INCENTIVE_TYPE_CODE CREDIT_TYPE_ID EXPENSE_CCID LIABILITY_CCID PAY_ELEMENT_TYPE_ID TRANSACTION_TYPE CREATED_BY COMM_AMT_CALC_CURR CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN PAYMENT_ATTRIBUTE_CATEGORY PAYMENT_ATTRIBUTE1 PAYMENT_ATTRIBUTE2 PAYMENT_ATTRIBUTE3 PAYMENT_ATTRIBUTE4 PAYMENT_ATTRIBUTE5 PAYMENT_ATTRIBUTE6 PAYMENT_ATTRIBUTE7 PAYMENT_ATTRIBUTE8 PAYMENT_ATTRIBUTE9 PAYMENT_ATTRIBUTE10 PAYMENT_ATTRIBUTE11 PAYMENT_ATTRIBUTE12 PAYMENT_ATTRIBUTE13 PAYMENT_ATTRIBUTE14 PAYMENT_ATTRIBUTE15 PERSON_NUMBER HR_PRIMARY_WORKER_NUMBER PARTY_NUMBER PAYEE_ONLY PARTICIPANT_TYPE PARTY_ID SOURCE_SYSTEM SOURCE_SYSTEM_ID COMPENSATION_END_DATE START_DATE END_DATE ACTIVE_FLAG HOLD_PAYMENT_FLAG COST_CENTER COUNTRY_CODE SRP_DTL_ATTRIBUTE_CATEGORY SRP_DTL_ATTRIBUTE_NUMBER1 SRP_DTL_ATTRIBUTE_NUMBER2 SRP_DTL_ATTRIBUTE_NUMBER3 SRP_DTL_ATTRIBUTE_NUMBER4 SRP_DTL_ATTRIBUTE_NUMBER5 SRP_DTL_ATTRIBUTE_NUMBER6 SRP_DTL_ATTRIBUTE_NUMBER7 SRP_DTL_ATTRIBUTE_NUMBER8 SRP_DTL_ATTRIBUTE_NUMBER9 SRP_DTL_ATTRIBUTE_NUMBER10 SRP_DTL_ATTRIBUTE_NUMBER11 SRP_DTL_ATTRIBUTE_NUMBER12 SRP_DTL_ATTRIBUTE_NUMBER13 SRP_DTL_ATTRIBUTE_NUMBER14 SRP_DTL_ATTRIBUTE_NUMBER15 SRP_DTL_ATTRIBUTE_NUMBER16 SRP_DTL_ATTRIBUTE_NUMBER17 SRP_DTL_ATTRIBUTE_NUMBER18 SRP_DTL_ATTRIBUTE_NUMBER19 SRP_DTL_ATTRIBUTE_NUMBER20 SRP_DTL_ATTRIBUTE_CHAR1 SRP_DTL_ATTRIBUTE_CHAR2 SRP_DTL_ATTRIBUTE_CHAR3 SRP_DTL_ATTRIBUTE_CHAR4 SRP_DTL_ATTRIBUTE_CHAR5 SRP_DTL_ATTRIBUTE_CHAR6 SRP_DTL_ATTRIBUTE_CHAR7 SRP_DTL_ATTRIBUTE_CHAR8 SRP_DTL_ATTRIBUTE_CHAR9 SRP_DTL_ATTRIBUTE_CHAR10 SRP_DTL_ATTRIBUTE_CHAR11 SRP_DTL_ATTRIBUTE_CHAR12 SRP_DTL_ATTRIBUTE_CHAR13 SRP_DTL_ATTRIBUTE_CHAR14 SRP_DTL_ATTRIBUTE_CHAR15 SRP_DTL_ATTRIBUTE_CHAR16 SRP_DTL_ATTRIBUTE_CHAR17 SRP_DTL_ATTRIBUTE_CHAR18 SRP_DTL_ATTRIBUTE_CHAR19 SRP_DTL_ATTRIBUTE_CHAR20 SRP_DTL_ATTRIBUTE_DATE1 SRP_DTL_ATTRIBUTE_DATE2 SRP_DTL_ATTRIBUTE_DATE3 SRP_DTL_ATTRIBUTE_DATE4 SRP_DTL_ATTRIBUTE_DATE5 SRP_DTL_ATTRIBUTE_DATE6 SRP_DTL_ATTRIBUTE_DATE7 SRP_DTL_ATTRIBUTE_DATE8 SRP_DTL_ATTRIBUTE_DATE9 SRP_DTL_ATTRIBUTE_DATE10 PARTY_NAME PLAN_COMPONENT_NAME PAYMENT_GROUP_CODE EARNING_TYPE_ID AP_LIAB_ID AP_EXP_ID REPORT_GROUP PLAN_ATTRIBUTE_CATEGORY PLAN_COMP_ATTRIBUTE1 PLAN_COMP_ATTRIBUTE2 PLAN_COMP_ATTRIBUTE3 PLAN_COMP_ATTRIBUTE4 PLAN_COMP_ATTRIBUTE5 PLAN_COMP_ATTRIBUTE6 PLAN_COMP_ATTRIBUTE7 PLAN_COMP_ATTRIBUTE8 PLAN_COMP_ATTRIBUTE9 PLAN_COMP_ATTRIBUTE10 PLAN_COMP_ATTRIBUTE11 PLAN_COMP_ATTRIBUTE12 PLAN_COMP_ATTRIBUTE13 PLAN_COMP_ATTRIBUTE14 PLAN_COMP_ATTRIBUTE15 BUSINESS_UNIT_NAME PAYMENT_BATCH_NAME PAYMENT_BATCH_STATUS PAY_DATE PAYMENT_BATCH_PERIOD PAYGROUP_NAME PAYGROUP_TYPE PAYSHEET_STATUS ANALYST_NAME PLAN_NAME

Name

PAYRUN_ID

HOLD_FLAG

SOURCE_EARNING_ID

PAY_TO_HOME_CURR_CNVRT

PAY_AMT_PAY_CURR

PAY_TO_FUNC_CURR_CNVRT

PAY_AMT_HOME_CURR

HOME_CURRENCY_CODE

PAY_CURRENCY_CODE

COMMISSION_VALUE

PAYEE_PARTICIPANT_ID

OBJECT_VERSION_NUMBER

CREDITED_PARTICIPANT_ID

PAYEE_EMPLOYEE_NUMBER

CREDITED_EMPLOYEE_NUMBER

SOURCE_EVENT_DATE

PAY_PERIOD_ID

PAY_AMT_FUNC_CURR

SOURCE_TYPE

PARTICIPANT_PAY_ID

SOURCE_TRX_NUMBER

SOURCE_ORG_ID

EARNING_ID

OBJECT_STATUS

ADJUST_STATUS

ADJUST_COMMENTS

ORG_ID

PLAN_COMPONENT_ID

SRP_PLAN_ASSIGN_ID

ROLE_ID

RECOVERABLE_FLAG

WAIVE_FLAG

INCENTIVE_TYPE_CODE

CREDIT_TYPE_ID

EXPENSE_CCID

LIABILITY_CCID

PAY_ELEMENT_TYPE_ID

TRANSACTION_TYPE

CREATED_BY

COMM_AMT_CALC_CURR

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

PAYMENT_ATTRIBUTE_CATEGORY

PAYMENT_ATTRIBUTE1

PAYMENT_ATTRIBUTE2

PAYMENT_ATTRIBUTE3

PAYMENT_ATTRIBUTE4

PAYMENT_ATTRIBUTE5

PAYMENT_ATTRIBUTE6

PAYMENT_ATTRIBUTE7

PAYMENT_ATTRIBUTE8

PAYMENT_ATTRIBUTE9

PAYMENT_ATTRIBUTE10

PAYMENT_ATTRIBUTE11

PAYMENT_ATTRIBUTE12

PAYMENT_ATTRIBUTE13

PAYMENT_ATTRIBUTE14

PAYMENT_ATTRIBUTE15

PERSON_NUMBER

HR_PRIMARY_WORKER_NUMBER

PARTY_NUMBER

PAYEE_ONLY

PARTICIPANT_TYPE

PARTY_ID

SOURCE_SYSTEM

SOURCE_SYSTEM_ID

COMPENSATION_END_DATE

START_DATE

END_DATE

ACTIVE_FLAG

HOLD_PAYMENT_FLAG

COST_CENTER

COUNTRY_CODE

SRP_DTL_ATTRIBUTE_CATEGORY

SRP_DTL_ATTRIBUTE_NUMBER1

SRP_DTL_ATTRIBUTE_NUMBER2

SRP_DTL_ATTRIBUTE_NUMBER3

SRP_DTL_ATTRIBUTE_NUMBER4

SRP_DTL_ATTRIBUTE_NUMBER5

SRP_DTL_ATTRIBUTE_NUMBER6

SRP_DTL_ATTRIBUTE_NUMBER7

SRP_DTL_ATTRIBUTE_NUMBER8

SRP_DTL_ATTRIBUTE_NUMBER9

SRP_DTL_ATTRIBUTE_NUMBER10

SRP_DTL_ATTRIBUTE_NUMBER11

SRP_DTL_ATTRIBUTE_NUMBER12

SRP_DTL_ATTRIBUTE_NUMBER13

SRP_DTL_ATTRIBUTE_NUMBER14

SRP_DTL_ATTRIBUTE_NUMBER15

SRP_DTL_ATTRIBUTE_NUMBER16

SRP_DTL_ATTRIBUTE_NUMBER17

SRP_DTL_ATTRIBUTE_NUMBER18

SRP_DTL_ATTRIBUTE_NUMBER19

SRP_DTL_ATTRIBUTE_NUMBER20

SRP_DTL_ATTRIBUTE_CHAR1

SRP_DTL_ATTRIBUTE_CHAR2

SRP_DTL_ATTRIBUTE_CHAR3

SRP_DTL_ATTRIBUTE_CHAR4

SRP_DTL_ATTRIBUTE_CHAR5

SRP_DTL_ATTRIBUTE_CHAR6

SRP_DTL_ATTRIBUTE_CHAR7

SRP_DTL_ATTRIBUTE_CHAR8

SRP_DTL_ATTRIBUTE_CHAR9

SRP_DTL_ATTRIBUTE_CHAR10

SRP_DTL_ATTRIBUTE_CHAR11

SRP_DTL_ATTRIBUTE_CHAR12

SRP_DTL_ATTRIBUTE_CHAR13

SRP_DTL_ATTRIBUTE_CHAR14

SRP_DTL_ATTRIBUTE_CHAR15

SRP_DTL_ATTRIBUTE_CHAR16

SRP_DTL_ATTRIBUTE_CHAR17

SRP_DTL_ATTRIBUTE_CHAR18

SRP_DTL_ATTRIBUTE_CHAR19

SRP_DTL_ATTRIBUTE_CHAR20

SRP_DTL_ATTRIBUTE_DATE1

SRP_DTL_ATTRIBUTE_DATE2

SRP_DTL_ATTRIBUTE_DATE3

SRP_DTL_ATTRIBUTE_DATE4

SRP_DTL_ATTRIBUTE_DATE5

SRP_DTL_ATTRIBUTE_DATE6

SRP_DTL_ATTRIBUTE_DATE7

SRP_DTL_ATTRIBUTE_DATE8

SRP_DTL_ATTRIBUTE_DATE9

SRP_DTL_ATTRIBUTE_DATE10

PARTY_NAME

PLAN_COMPONENT_NAME

PAYMENT_GROUP_CODE

EARNING_TYPE_ID

AP_LIAB_ID

AP_EXP_ID

REPORT_GROUP

PLAN_ATTRIBUTE_CATEGORY

PLAN_COMP_ATTRIBUTE1

PLAN_COMP_ATTRIBUTE2

PLAN_COMP_ATTRIBUTE3

PLAN_COMP_ATTRIBUTE4

PLAN_COMP_ATTRIBUTE5

PLAN_COMP_ATTRIBUTE6

PLAN_COMP_ATTRIBUTE7

PLAN_COMP_ATTRIBUTE8

PLAN_COMP_ATTRIBUTE9

PLAN_COMP_ATTRIBUTE10

PLAN_COMP_ATTRIBUTE11

PLAN_COMP_ATTRIBUTE12

PLAN_COMP_ATTRIBUTE13

PLAN_COMP_ATTRIBUTE14

PLAN_COMP_ATTRIBUTE15

BUSINESS_UNIT_NAME

PAYMENT_BATCH_NAME

PAYMENT_BATCH_STATUS

PAY_DATE

PAYMENT_BATCH_PERIOD

PAYGROUP_NAME

PAYGROUP_TYPE

PAYSHEET_STATUS

ANALYST_NAME

PLAN_NAME

Query

SQL_Statement
SELECT P.PAYRUN_ID, P.HOLD_FLAG, P.SOURCE_EARNING_ID, P.PAY_TO_HOME_CURR_CNVRT, P.PAY_AMT_PAY_CURR, P.PAY_TO_FUNC_CURR_CNVRT, P.PAY_AMT_HOME_CURR, P.HOME_CURRENCY_CODE, P.PAY_CURRENCY_CODE, P.COMMISSION_VALUE, P.PAYEE_PARTICIPANT_ID, P.OBJECT_VERSION_NUMBER, P.CREDITED_PARTICIPANT_ID, P.PAYEE_EMPLOYEE_NUMBER, P.CREDITED_EMPLOYEE_NUMBER, P.SOURCE_EVENT_DATE, P.PAY_PERIOD_ID, P.PAY_AMT_FUNC_CURR, P.SOURCE_TYPE, P.PARTICIPANT_PAY_ID, P.SOURCE_TRX_NUMBER, P.SOURCE_ORG_ID, P.EARNING_ID, P.OBJECT_STATUS, P.ADJUST_STATUS, P.ADJUST_COMMENTS, P.ORG_ID, P.PLAN_COMPONENT_ID, P.SRP_PLAN_ASSIGN_ID, P.ROLE_ID, P.RECOVERABLE_FLAG, P.WAIVE_FLAG, P.INCENTIVE_TYPE_CODE, P.CREDIT_TYPE_ID, P.EXPENSE_CCID, P.LIABILITY_CCID, P.PAY_ELEMENT_TYPE_ID, P.TRANSACTION_TYPE, P.CREATED_BY, P.COMM_AMT_CALC_CURR, P.CREATION_DATE, P.LAST_UPDATED_BY, P.LAST_UPDATE_DATE, P.LAST_UPDATE_LOGIN, P.ATTRIBUTE_CATEGORY AS PAYMENT_ATTRIBUTE_CATEGORY, P.ATTRIBUTE1 AS PAYMENT_ATTRIBUTE1, P.ATTRIBUTE2 AS PAYMENT_ATTRIBUTE2, P.ATTRIBUTE3 AS PAYMENT_ATTRIBUTE3, P.ATTRIBUTE4 AS PAYMENT_ATTRIBUTE4, P.ATTRIBUTE5 AS PAYMENT_ATTRIBUTE5, P.ATTRIBUTE6 AS PAYMENT_ATTRIBUTE6, P.ATTRIBUTE7 AS PAYMENT_ATTRIBUTE7, P.ATTRIBUTE8 AS PAYMENT_ATTRIBUTE8, P.ATTRIBUTE9 AS PAYMENT_ATTRIBUTE9, P.ATTRIBUTE10 AS PAYMENT_ATTRIBUTE10, P.ATTRIBUTE11 AS PAYMENT_ATTRIBUTE11, P.ATTRIBUTE12 AS PAYMENT_ATTRIBUTE12, P.ATTRIBUTE13 AS PAYMENT_ATTRIBUTE13, P.ATTRIBUTE14 AS PAYMENT_ATTRIBUTE14, P.ATTRIBUTE15 AS PAYMENT_ATTRIBUTE15, (SELECT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE SRP.SOURCE_SYSTEM_ID = PER.PERSON_ID AND SRP.SOURCE_SYSTEM = 'FUSION_HCM' AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE)PERSON_NUMBER, SRP.HR_PRIMARY_WORKER_NUMBER, SRP.PARTY_NUMBER, SRP.PAYEE_ONLY, SRP.PARTICIPANT_TYPE, SRP.PARTY_ID, SRP.SOURCE_SYSTEM, SRP.SOURCE_SYSTEM_ID, SRP.COMPENSATION_END_DATE, SRP.START_DATE, SRP.END_DATE, SRP.ACTIVE_FLAG, SRP.HOLD_PAYMENT_FLAG, PD.COST_CENTER, PD.COUNTRY_CODE, PD.ATTRIBUTE_CATEGORY AS SRP_DTL_ATTRIBUTE_CATEGORY, PD.ATTRIBUTE_NUMBER1 AS SRP_DTL_ATTRIBUTE_NUMBER1, PD.ATTRIBUTE_NUMBER2 AS SRP_DTL_ATTRIBUTE_NUMBER2, PD.ATTRIBUTE_NUMBER3 AS SRP_DTL_ATTRIBUTE_NUMBER3, PD.ATTRIBUTE_NUMBER4 AS SRP_DTL_ATTRIBUTE_NUMBER4, PD.ATTRIBUTE_NUMBER5 AS SRP_DTL_ATTRIBUTE_NUMBER5, PD.ATTRIBUTE_NUMBER6 AS SRP_DTL_ATTRIBUTE_NUMBER6, PD.ATTRIBUTE_NUMBER7 AS SRP_DTL_ATTRIBUTE_NUMBER7, PD.ATTRIBUTE_NUMBER8 AS SRP_DTL_ATTRIBUTE_NUMBER8, PD.ATTRIBUTE_NUMBER9 AS SRP_DTL_ATTRIBUTE_NUMBER9, PD.ATTRIBUTE_NUMBER10 AS SRP_DTL_ATTRIBUTE_NUMBER10, PD.ATTRIBUTE_NUMBER11 AS SRP_DTL_ATTRIBUTE_NUMBER11, PD.ATTRIBUTE_NUMBER12 AS SRP_DTL_ATTRIBUTE_NUMBER12, PD.ATTRIBUTE_NUMBER13 AS SRP_DTL_ATTRIBUTE_NUMBER13, PD.ATTRIBUTE_NUMBER14 AS SRP_DTL_ATTRIBUTE_NUMBER14, PD.ATTRIBUTE_NUMBER15 AS SRP_DTL_ATTRIBUTE_NUMBER15, PD.ATTRIBUTE_NUMBER16 AS SRP_DTL_ATTRIBUTE_NUMBER16, PD.ATTRIBUTE_NUMBER17 AS SRP_DTL_ATTRIBUTE_NUMBER17, PD.ATTRIBUTE_NUMBER18 AS SRP_DTL_ATTRIBUTE_NUMBER18, PD.ATTRIBUTE_NUMBER19 AS SRP_DTL_ATTRIBUTE_NUMBER19, PD.ATTRIBUTE_NUMBER20 AS SRP_DTL_ATTRIBUTE_NUMBER20, PD.ATTRIBUTE_CHAR1 AS SRP_DTL_ATTRIBUTE_CHAR1, PD.ATTRIBUTE_CHAR2 AS SRP_DTL_ATTRIBUTE_CHAR2, PD.ATTRIBUTE_CHAR3 AS SRP_DTL_ATTRIBUTE_CHAR3, PD.ATTRIBUTE_CHAR4 AS SRP_DTL_ATTRIBUTE_CHAR4, PD.ATTRIBUTE_CHAR5 AS SRP_DTL_ATTRIBUTE_CHAR5, PD.ATTRIBUTE_CHAR6 AS SRP_DTL_ATTRIBUTE_CHAR6, PD.ATTRIBUTE_CHAR7 AS SRP_DTL_ATTRIBUTE_CHAR7, PD.ATTRIBUTE_CHAR8 AS SRP_DTL_ATTRIBUTE_CHAR8, PD.ATTRIBUTE_CHAR9 AS SRP_DTL_ATTRIBUTE_CHAR9, PD.ATTRIBUTE_CHAR10 AS SRP_DTL_ATTRIBUTE_CHAR10, PD.ATTRIBUTE_CHAR11 AS SRP_DTL_ATTRIBUTE_CHAR11, PD.ATTRIBUTE_CHAR12 AS SRP_DTL_ATTRIBUTE_CHAR12, PD.ATTRIBUTE_CHAR13 AS SRP_DTL_ATTRIBUTE_CHAR13, PD.ATTRIBUTE_CHAR14 AS SRP_DTL_ATTRIBUTE_CHAR14, PD.ATTRIBUTE_CHAR15 AS SRP_DTL_ATTRIBUTE_CHAR15, PD.ATTRIBUTE_CHAR16 AS SRP_DTL_ATTRIBUTE_CHAR16, PD.ATTRIBUTE_CHAR17 AS SRP_DTL_ATTRIBUTE_CHAR17, PD.ATTRIBUTE_CHAR18 AS SRP_DTL_ATTRIBUTE_CHAR18, PD.ATTRIBUTE_CHAR19 AS SRP_DTL_ATTRIBUTE_CHAR19, PD.ATTRIBUTE_CHAR20 AS SRP_DTL_ATTRIBUTE_CHAR20, PD.ATTRIBUTE_DATE1 AS SRP_DTL_ATTRIBUTE_DATE1, PD.ATTRIBUTE_DATE2 AS SRP_DTL_ATTRIBUTE_DATE2, PD.ATTRIBUTE_DATE3 AS SRP_DTL_ATTRIBUTE_DATE3, PD.ATTRIBUTE_DATE4 AS SRP_DTL_ATTRIBUTE_DATE4, PD.ATTRIBUTE_DATE5 AS SRP_DTL_ATTRIBUTE_DATE5, PD.ATTRIBUTE_DATE6 AS SRP_DTL_ATTRIBUTE_DATE6, PD.ATTRIBUTE_DATE7 AS SRP_DTL_ATTRIBUTE_DATE7, PD.ATTRIBUTE_DATE8 AS SRP_DTL_ATTRIBUTE_DATE8, PD.ATTRIBUTE_DATE9 AS SRP_DTL_ATTRIBUTE_DATE9, PD.ATTRIBUTE_DATE10 AS SRP_DTL_ATTRIBUTE_DATE10, HZ.PARTY_NAME, PC.DISPLAY_NAME PLAN_COMPONENT_NAME, PC.PAYMENT_GROUP_CODE, PC.EARNING_TYPE_ID, PC.AP_LIAB_ID, PC.AP_EXP_ID, PC.REPORT_GROUP, PC.ATTRIBUTE_CATEGORY AS PLAN_ATTRIBUTE_CATEGORY, PC.ATTRIBUTE1 AS PLAN_COMP_ATTRIBUTE1, PC.ATTRIBUTE2 AS PLAN_COMP_ATTRIBUTE2, PC.ATTRIBUTE3 AS PLAN_COMP_ATTRIBUTE3, PC.ATTRIBUTE4 AS PLAN_COMP_ATTRIBUTE4, PC.ATTRIBUTE5 AS PLAN_COMP_ATTRIBUTE5, PC.ATTRIBUTE6 AS PLAN_COMP_ATTRIBUTE6, PC.ATTRIBUTE7 AS PLAN_COMP_ATTRIBUTE7, PC.ATTRIBUTE8 AS PLAN_COMP_ATTRIBUTE8, PC.ATTRIBUTE9 AS PLAN_COMP_ATTRIBUTE9, PC.ATTRIBUTE10 AS PLAN_COMP_ATTRIBUTE10, PC.ATTRIBUTE11 AS PLAN_COMP_ATTRIBUTE11, PC.ATTRIBUTE12 AS PLAN_COMP_ATTRIBUTE12, PC.ATTRIBUTE13 AS PLAN_COMP_ATTRIBUTE13, PC.ATTRIBUTE14 AS PLAN_COMP_ATTRIBUTE14, PC.ATTRIBUTE15 AS PLAN_COMP_ATTRIBUTE15, REP.ORG_NAME AS BUSINESS_UNIT_NAME, PB.PAYRUN_NAME AS PAYMENT_BATCH_NAME, (select meaning from cn_lookups where lookup_type like 'CN_PAYRUN_STATUS' and lookup_code = PB.OBJECT_STATUS) AS PAYMENT_BATCH_STATUS, PB.PAY_DATE AS PAY_DATE, PB.PAY_PERIOD_ID AS PAYMENT_BATCH_PERIOD, PG.PAY_GROUP_NAME AS PAYGROUP_NAME, (select meaning from cn_lookups where lookup_type like 'CN_PAY_GROUP_TYPE' and lookup_code = PG.PAY_GROUP_TYPE) AS PAYGROUP_TYPE, (select meaning from cn_lookups where lookup_type like 'CN_PAYSHEET_STATUS' and lookup_code = PS.OBJECT_STATUS and enabled_flag = 'Y' and lookup_code != 'REVIEWED') AS PAYSHEET_STATUS, (select participant_name from CN_SRP_PARTICIPANT_HDR_RO_V party where party.participant_id = SRP.ANALYST_ID) AS ANALYST_NAME, (select CP.DISPLAY_NAME from CN_COMP_PLANS_ALL_VL CP ,CN_SRP_COMP_PLANS_ALL SCP where CP.COMP_PLAN_ID = SCP.COMP_PLAN_ID AND P.SRP_PLAN_ASSIGN_ID = SCP.SRP_COMP_PLAN_ID) AS PLAN_NAME FROM CN_TP_PARTICIPANT_PAY_ALL P, CN_TP_PAYRUNS_ALL PB, CN_PAY_GROUPS_ALL_VL PG, CN_TP_PAYSHEETS_ALL PS, CN_SRP_PARTICIPANT_HDR_RO_V SRP, HZ_PARTIES HZ, CN_PLAN_COMPONENTS_ALL_VL PC, CN_REPOSITORIES_ALL_VL REP, CN_SRP_PARTICIPANT_DETAILS_ALL PD WHERE P.CREDITED_PARTICIPANT_ID = SRP.PARTICIPANT_ID AND P.PAYRUN_ID = PB.PAYRUN_ID AND PB.PAY_GROUP_ID = PG.PAY_GROUP_ID AND P.PAYRUN_ID = PS.PAYRUN_ID AND P.CREDITED_PARTICIPANT_ID = PS.PARTICIPANT_ID AND PB.PAYRUN_ID = PS.PAYRUN_ID AND PS.QUOTA_ID IS NULL AND SRP.PARTY_ID = HZ.PARTY_ID AND P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID AND P.ORG_ID = REP.ORG_ID AND PD.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID AND ( ( P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,P.SOURCE_EVENT_DATE) ) OR ( P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PB.PAY_DATE)) )

SQL_Statement

SELECT

P.PAYRUN_ID,

P.HOLD_FLAG,

P.SOURCE_EARNING_ID,

P.PAY_TO_HOME_CURR_CNVRT,

P.PAY_AMT_PAY_CURR,

P.PAY_TO_FUNC_CURR_CNVRT,

P.PAY_AMT_HOME_CURR,

P.HOME_CURRENCY_CODE,

P.PAY_CURRENCY_CODE,

P.COMMISSION_VALUE,

P.PAYEE_PARTICIPANT_ID,

P.OBJECT_VERSION_NUMBER,

P.CREDITED_PARTICIPANT_ID,

P.PAYEE_EMPLOYEE_NUMBER,

P.CREDITED_EMPLOYEE_NUMBER,

P.SOURCE_EVENT_DATE,

P.PAY_PERIOD_ID,

P.PAY_AMT_FUNC_CURR,

P.SOURCE_TYPE,

P.PARTICIPANT_PAY_ID,

P.SOURCE_TRX_NUMBER,

P.SOURCE_ORG_ID,

P.EARNING_ID,

P.OBJECT_STATUS,

P.ADJUST_STATUS,

P.ADJUST_COMMENTS,

P.ORG_ID,

P.PLAN_COMPONENT_ID,

P.SRP_PLAN_ASSIGN_ID,

P.ROLE_ID,

P.RECOVERABLE_FLAG,

P.WAIVE_FLAG,

P.INCENTIVE_TYPE_CODE,

P.CREDIT_TYPE_ID,

P.EXPENSE_CCID,

P.LIABILITY_CCID,

P.PAY_ELEMENT_TYPE_ID,

P.TRANSACTION_TYPE,

P.CREATED_BY,

P.COMM_AMT_CALC_CURR,

P.CREATION_DATE,

P.LAST_UPDATED_BY,

P.LAST_UPDATE_DATE,

P.LAST_UPDATE_LOGIN,

P.ATTRIBUTE_CATEGORY AS PAYMENT_ATTRIBUTE_CATEGORY,

P.ATTRIBUTE1 AS PAYMENT_ATTRIBUTE1,

P.ATTRIBUTE2 AS PAYMENT_ATTRIBUTE2,

P.ATTRIBUTE3 AS PAYMENT_ATTRIBUTE3,

P.ATTRIBUTE4 AS PAYMENT_ATTRIBUTE4,

P.ATTRIBUTE5 AS PAYMENT_ATTRIBUTE5,

P.ATTRIBUTE6 AS PAYMENT_ATTRIBUTE6,

P.ATTRIBUTE7 AS PAYMENT_ATTRIBUTE7,

P.ATTRIBUTE8 AS PAYMENT_ATTRIBUTE8,

P.ATTRIBUTE9 AS PAYMENT_ATTRIBUTE9,

P.ATTRIBUTE10 AS PAYMENT_ATTRIBUTE10,

P.ATTRIBUTE11 AS PAYMENT_ATTRIBUTE11,

P.ATTRIBUTE12 AS PAYMENT_ATTRIBUTE12,

P.ATTRIBUTE13 AS PAYMENT_ATTRIBUTE13,

P.ATTRIBUTE14 AS PAYMENT_ATTRIBUTE14,

P.ATTRIBUTE15 AS PAYMENT_ATTRIBUTE15,

(SELECT PERSON_NUMBER FROM PER_ALL_PEOPLE_F PER WHERE SRP.SOURCE_SYSTEM_ID = PER.PERSON_ID AND SRP.SOURCE_SYSTEM = 'FUSION_HCM' AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE)PERSON_NUMBER,

SRP.HR_PRIMARY_WORKER_NUMBER,

SRP.PARTY_NUMBER,

SRP.PAYEE_ONLY,

SRP.PARTICIPANT_TYPE,

SRP.PARTY_ID,

SRP.SOURCE_SYSTEM,

SRP.SOURCE_SYSTEM_ID,

SRP.COMPENSATION_END_DATE,

SRP.START_DATE,

SRP.END_DATE,

SRP.ACTIVE_FLAG,

SRP.HOLD_PAYMENT_FLAG,

PD.COST_CENTER,

PD.COUNTRY_CODE,

PD.ATTRIBUTE_CATEGORY AS SRP_DTL_ATTRIBUTE_CATEGORY,

PD.ATTRIBUTE_NUMBER1 AS SRP_DTL_ATTRIBUTE_NUMBER1,

PD.ATTRIBUTE_NUMBER2 AS SRP_DTL_ATTRIBUTE_NUMBER2,

PD.ATTRIBUTE_NUMBER3 AS SRP_DTL_ATTRIBUTE_NUMBER3,

PD.ATTRIBUTE_NUMBER4 AS SRP_DTL_ATTRIBUTE_NUMBER4,

PD.ATTRIBUTE_NUMBER5 AS SRP_DTL_ATTRIBUTE_NUMBER5,

PD.ATTRIBUTE_NUMBER6 AS SRP_DTL_ATTRIBUTE_NUMBER6,

PD.ATTRIBUTE_NUMBER7 AS SRP_DTL_ATTRIBUTE_NUMBER7,

PD.ATTRIBUTE_NUMBER8 AS SRP_DTL_ATTRIBUTE_NUMBER8,

PD.ATTRIBUTE_NUMBER9 AS SRP_DTL_ATTRIBUTE_NUMBER9,

PD.ATTRIBUTE_NUMBER10 AS SRP_DTL_ATTRIBUTE_NUMBER10,

PD.ATTRIBUTE_NUMBER11 AS SRP_DTL_ATTRIBUTE_NUMBER11,

PD.ATTRIBUTE_NUMBER12 AS SRP_DTL_ATTRIBUTE_NUMBER12,

PD.ATTRIBUTE_NUMBER13 AS SRP_DTL_ATTRIBUTE_NUMBER13,

PD.ATTRIBUTE_NUMBER14 AS SRP_DTL_ATTRIBUTE_NUMBER14,

PD.ATTRIBUTE_NUMBER15 AS SRP_DTL_ATTRIBUTE_NUMBER15,

PD.ATTRIBUTE_NUMBER16 AS SRP_DTL_ATTRIBUTE_NUMBER16,

PD.ATTRIBUTE_NUMBER17 AS SRP_DTL_ATTRIBUTE_NUMBER17,

PD.ATTRIBUTE_NUMBER18 AS SRP_DTL_ATTRIBUTE_NUMBER18,

PD.ATTRIBUTE_NUMBER19 AS SRP_DTL_ATTRIBUTE_NUMBER19,

PD.ATTRIBUTE_NUMBER20 AS SRP_DTL_ATTRIBUTE_NUMBER20,

PD.ATTRIBUTE_CHAR1 AS SRP_DTL_ATTRIBUTE_CHAR1,

PD.ATTRIBUTE_CHAR2 AS SRP_DTL_ATTRIBUTE_CHAR2,

PD.ATTRIBUTE_CHAR3 AS SRP_DTL_ATTRIBUTE_CHAR3,

PD.ATTRIBUTE_CHAR4 AS SRP_DTL_ATTRIBUTE_CHAR4,

PD.ATTRIBUTE_CHAR5 AS SRP_DTL_ATTRIBUTE_CHAR5,

PD.ATTRIBUTE_CHAR6 AS SRP_DTL_ATTRIBUTE_CHAR6,

PD.ATTRIBUTE_CHAR7 AS SRP_DTL_ATTRIBUTE_CHAR7,

PD.ATTRIBUTE_CHAR8 AS SRP_DTL_ATTRIBUTE_CHAR8,

PD.ATTRIBUTE_CHAR9 AS SRP_DTL_ATTRIBUTE_CHAR9,

PD.ATTRIBUTE_CHAR10 AS SRP_DTL_ATTRIBUTE_CHAR10,

PD.ATTRIBUTE_CHAR11 AS SRP_DTL_ATTRIBUTE_CHAR11,

PD.ATTRIBUTE_CHAR12 AS SRP_DTL_ATTRIBUTE_CHAR12,

PD.ATTRIBUTE_CHAR13 AS SRP_DTL_ATTRIBUTE_CHAR13,

PD.ATTRIBUTE_CHAR14 AS SRP_DTL_ATTRIBUTE_CHAR14,

PD.ATTRIBUTE_CHAR15 AS SRP_DTL_ATTRIBUTE_CHAR15,

PD.ATTRIBUTE_CHAR16 AS SRP_DTL_ATTRIBUTE_CHAR16,

PD.ATTRIBUTE_CHAR17 AS SRP_DTL_ATTRIBUTE_CHAR17,

PD.ATTRIBUTE_CHAR18 AS SRP_DTL_ATTRIBUTE_CHAR18,

PD.ATTRIBUTE_CHAR19 AS SRP_DTL_ATTRIBUTE_CHAR19,

PD.ATTRIBUTE_CHAR20 AS SRP_DTL_ATTRIBUTE_CHAR20,

PD.ATTRIBUTE_DATE1 AS SRP_DTL_ATTRIBUTE_DATE1,

PD.ATTRIBUTE_DATE2 AS SRP_DTL_ATTRIBUTE_DATE2,

PD.ATTRIBUTE_DATE3 AS SRP_DTL_ATTRIBUTE_DATE3,

PD.ATTRIBUTE_DATE4 AS SRP_DTL_ATTRIBUTE_DATE4,

PD.ATTRIBUTE_DATE5 AS SRP_DTL_ATTRIBUTE_DATE5,

PD.ATTRIBUTE_DATE6 AS SRP_DTL_ATTRIBUTE_DATE6,

PD.ATTRIBUTE_DATE7 AS SRP_DTL_ATTRIBUTE_DATE7,

PD.ATTRIBUTE_DATE8 AS SRP_DTL_ATTRIBUTE_DATE8,

PD.ATTRIBUTE_DATE9 AS SRP_DTL_ATTRIBUTE_DATE9,

PD.ATTRIBUTE_DATE10 AS SRP_DTL_ATTRIBUTE_DATE10,

HZ.PARTY_NAME,

PC.DISPLAY_NAME PLAN_COMPONENT_NAME,

PC.PAYMENT_GROUP_CODE,

PC.EARNING_TYPE_ID,

PC.AP_LIAB_ID,

PC.AP_EXP_ID,

PC.REPORT_GROUP,

PC.ATTRIBUTE_CATEGORY AS PLAN_ATTRIBUTE_CATEGORY,

PC.ATTRIBUTE1 AS PLAN_COMP_ATTRIBUTE1,

PC.ATTRIBUTE2 AS PLAN_COMP_ATTRIBUTE2,

PC.ATTRIBUTE3 AS PLAN_COMP_ATTRIBUTE3,

PC.ATTRIBUTE4 AS PLAN_COMP_ATTRIBUTE4,

PC.ATTRIBUTE5 AS PLAN_COMP_ATTRIBUTE5,

PC.ATTRIBUTE6 AS PLAN_COMP_ATTRIBUTE6,

PC.ATTRIBUTE7 AS PLAN_COMP_ATTRIBUTE7,

PC.ATTRIBUTE8 AS PLAN_COMP_ATTRIBUTE8,

PC.ATTRIBUTE9 AS PLAN_COMP_ATTRIBUTE9,

PC.ATTRIBUTE10 AS PLAN_COMP_ATTRIBUTE10,

PC.ATTRIBUTE11 AS PLAN_COMP_ATTRIBUTE11,

PC.ATTRIBUTE12 AS PLAN_COMP_ATTRIBUTE12,

PC.ATTRIBUTE13 AS PLAN_COMP_ATTRIBUTE13,

PC.ATTRIBUTE14 AS PLAN_COMP_ATTRIBUTE14,

PC.ATTRIBUTE15 AS PLAN_COMP_ATTRIBUTE15,

REP.ORG_NAME AS BUSINESS_UNIT_NAME,

PB.PAYRUN_NAME AS PAYMENT_BATCH_NAME,

(select meaning from cn_lookups where lookup_type like 'CN_PAYRUN_STATUS' and lookup_code = PB.OBJECT_STATUS) AS PAYMENT_BATCH_STATUS,

PB.PAY_DATE AS PAY_DATE,

PB.PAY_PERIOD_ID AS PAYMENT_BATCH_PERIOD,

PG.PAY_GROUP_NAME AS PAYGROUP_NAME,

(select meaning from cn_lookups where lookup_type like 'CN_PAY_GROUP_TYPE' and lookup_code = PG.PAY_GROUP_TYPE) AS PAYGROUP_TYPE,

(select meaning from cn_lookups where lookup_type like 'CN_PAYSHEET_STATUS' and lookup_code = PS.OBJECT_STATUS and enabled_flag = 'Y' and lookup_code != 'REVIEWED') AS PAYSHEET_STATUS,

(select participant_name from CN_SRP_PARTICIPANT_HDR_RO_V party where party.participant_id = SRP.ANALYST_ID) AS ANALYST_NAME,

(select CP.DISPLAY_NAME

from CN_COMP_PLANS_ALL_VL CP

,CN_SRP_COMP_PLANS_ALL SCP

where CP.COMP_PLAN_ID = SCP.COMP_PLAN_ID

AND P.SRP_PLAN_ASSIGN_ID = SCP.SRP_COMP_PLAN_ID) AS PLAN_NAME

FROM

CN_TP_PARTICIPANT_PAY_ALL P,

CN_TP_PAYRUNS_ALL PB,

CN_PAY_GROUPS_ALL_VL PG,

CN_TP_PAYSHEETS_ALL PS,

CN_SRP_PARTICIPANT_HDR_RO_V SRP,

HZ_PARTIES HZ,

CN_PLAN_COMPONENTS_ALL_VL PC,

CN_REPOSITORIES_ALL_VL REP,

CN_SRP_PARTICIPANT_DETAILS_ALL PD

WHERE

P.CREDITED_PARTICIPANT_ID = SRP.PARTICIPANT_ID

AND P.PAYRUN_ID = PB.PAYRUN_ID

AND PB.PAY_GROUP_ID = PG.PAY_GROUP_ID

AND P.PAYRUN_ID = PS.PAYRUN_ID

AND P.CREDITED_PARTICIPANT_ID = PS.PARTICIPANT_ID

AND PB.PAYRUN_ID = PS.PAYRUN_ID

AND PS.QUOTA_ID IS NULL

AND SRP.PARTY_ID = HZ.PARTY_ID

AND P.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID

AND P.ORG_ID = REP.ORG_ID

AND PD.PARTICIPANT_ID = P.CREDITED_PARTICIPANT_ID

AND ( ( P.SOURCE_EVENT_DATE IS NOT NULL AND P.SOURCE_EVENT_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,P.SOURCE_EVENT_DATE) )

OR ( P.SOURCE_EVENT_DATE IS NULL AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PB.PAY_DATE)) )