CN_HCM_PAYROLL_TXN_UE

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 CREDITED_PARTICIPANT_ID PAYEE_EMPLOYEE_NUMBER CREDITED_EMPLOYEE_NUMBER SOURCE_EVENT_DATE PAY_AMT_FUNC_CURR SOURCE_TYPE PARTICIPANT_PAY_ID SOURCE_TRX_NUMBER SOURCE_ORG_ID EARNING_ID ADJUST_STATUS ADJUST_COMMENTS PLAN_COMPONENT_ID SRP_PLAN_ASSIGN_ID ROLE_ID RECOVERABLE_FLAG WAIVE_FLAG CREDIT_TYPE_ID EXPENSE_CCID LIABILITY_CCID PAY_ELEMENT_TYPE_ID TRANSACTION_TYPE CREATED_BY COMM_AMT_CALC_CURR MANUAL_ADJ_REASON_CODE PAY_DATE INCENTIVE_TYPE_CODE PAY_PERIOD_ID ORG_ID PAYRUN_STATUS PAY_PERIOD_START_DATE PAY_PERIOD_END_DATE ASSIGNMENT_NUMBER ASSIGNMENT_ID LEGISLATIVE_DATA_GROUP_ID PERSON_NUMBER PAYRUN_NAME PAY_GROUP_NAME PAY_GROUP_TYPE LAST_UPDATED_BY PLAN_COMPONENT_NAME INCENTIVE_TYPE PAYMENT_GROUP_CODE PARTICIPANT_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

CREDITED_PARTICIPANT_ID

PAYEE_EMPLOYEE_NUMBER

CREDITED_EMPLOYEE_NUMBER

SOURCE_EVENT_DATE

PAY_AMT_FUNC_CURR

SOURCE_TYPE

PARTICIPANT_PAY_ID

SOURCE_TRX_NUMBER

SOURCE_ORG_ID

EARNING_ID

ADJUST_STATUS

ADJUST_COMMENTS

PLAN_COMPONENT_ID

SRP_PLAN_ASSIGN_ID

ROLE_ID

RECOVERABLE_FLAG

WAIVE_FLAG

CREDIT_TYPE_ID

EXPENSE_CCID

LIABILITY_CCID

PAY_ELEMENT_TYPE_ID

TRANSACTION_TYPE

CREATED_BY

COMM_AMT_CALC_CURR

MANUAL_ADJ_REASON_CODE

PAY_DATE

INCENTIVE_TYPE_CODE

PAY_PERIOD_ID

ORG_ID

PAYRUN_STATUS

PAY_PERIOD_START_DATE

PAY_PERIOD_END_DATE

ASSIGNMENT_NUMBER

ASSIGNMENT_ID

LEGISLATIVE_DATA_GROUP_ID

PERSON_NUMBER

PAYRUN_NAME

PAY_GROUP_NAME

PAY_GROUP_TYPE

LAST_UPDATED_BY

PLAN_COMPONENT_NAME

INCENTIVE_TYPE

PAYMENT_GROUP_CODE

PARTICIPANT_NAME

Query

SQL_Statement
SELECT ppa.PAYRUN_ID, ppa.HOLD_FLAG, ppa.SOURCE_EARNING_ID, ppa.PAY_TO_HOME_CURR_CNVRT, ppa.PAY_AMT_PAY_CURR, ppa.PAY_TO_FUNC_CURR_CNVRT, ppa.PAY_AMT_HOME_CURR, ppa.HOME_CURRENCY_CODE, ppa.PAY_CURRENCY_CODE, ppa.COMMISSION_VALUE, ppa.PAYEE_PARTICIPANT_ID, ppa.CREDITED_PARTICIPANT_ID, ppa.PAYEE_EMPLOYEE_NUMBER, ppa.CREDITED_EMPLOYEE_NUMBER, ppa.SOURCE_EVENT_DATE, ppa.PAY_AMT_FUNC_CURR, ppa.SOURCE_TYPE, ppa.PARTICIPANT_PAY_ID, ppa.SOURCE_TRX_NUMBER, ppa.SOURCE_ORG_ID, ppa.EARNING_ID, ppa.ADJUST_STATUS, ppa.ADJUST_COMMENTS, ppa.PLAN_COMPONENT_ID, ppa.SRP_PLAN_ASSIGN_ID, ppa.ROLE_ID, ppa.RECOVERABLE_FLAG, ppa.WAIVE_FLAG, ppa.CREDIT_TYPE_ID, ppa.EXPENSE_CCID, ppa.LIABILITY_CCID, ppa.PAY_ELEMENT_TYPE_ID, ppa.TRANSACTION_TYPE, ppa.CREATED_BY, ppa.COMM_AMT_CALC_CURR, ppa.MANUAL_ADJ_REASON_CODE, pra.PAY_DATE, pra.INCENTIVE_TYPE_CODE, pra.PAY_PERIOD_ID, pra.ORG_ID, pra.OBJECT_STATUS PAYRUN_STATUS, prd.START_DATE PAY_PERIOD_START_DATE, prd.END_DATE PAY_PERIOD_END_DATE, paaf.ASSIGNMENT_NUMBER, paaf.ASSIGNMENT_ID, paaf.LEGISLATIVE_DATA_GROUP_ID, paaf.PERSON_NUMBER, pra.PAYRUN_NAME, PayGroupTLPEO.PAY_GROUP_NAME, PayGroupPEO.PAY_GROUP_TYPE, ppa.LAST_UPDATED_BY, PlanComponentTLPEO.PLAN_COMPONENT_NAME, PlanComponentPEO.INCENTIVE_TYPE, PlanComponentPEO.PAYMENT_GROUP_CODE, s.participant_name FROM CN_TP_PARTICIPANT_PAY_ALL ppa, cn_srp_participant_hdr_v s, CN_TP_PAYRUNS_ALL pra, cn_repositories_all_vl cnr, cn_periods_b prd, CN_PAY_GROUPS_ALL_B PayGroupPEO, CN_PAY_GROUPS_ALL_TL PayGroupTLPEO, CN_PLAN_COMPONENTS_ALL_B PlanComponentPEO, CN_PLAN_COMPONENTS_ALL_TL PlanComponentTLPEO, (SELECT ppa.end_date payrollAssign_end_date, ppa.start_date payrollAssign_start_date, papd.end_date assignedPayR_end_date, papd.start_date assignedPayR_start_date, papf.business_group_id, paaf.assignment_id, paaf.action_code, paaf.effective_end_date eff_end_date, paaf.effective_start_date eff_start_date, paaf.employee_category, paaf.employment_category, paaf.frequency, paaf.grade_id, paaf.job_id, paaf.legal_entity_id, paaf.location_id, paaf.assignment_name, paaf.assignment_number, paaf.organization_id, paaf.position_id, paaf.primary_assignment_flag, paaf.primary_flag assignments_primary_flag, paaf.primary_work_relation_flag, paaf.primary_work_terms_flag, paaf.assignment_status_type, paaf.system_person_type, paaf.assignment_type, ppa.hr_assignment_id, ppos.actual_termination_date, ppos.date_start, ppos.period_of_service_id, ppos.legislation_code, ppos.primary_flag periodService_primary_flag, ppos.worker_number, ppa.hr_term_id, pprd.legislative_data_group_id, ppa.legal_employer_id, papd.payroll_id, ppa.payroll_assignment_id, ppa.payroll_relationship_id, pprd.payroll_relationship_number, ppa.payroll_term_id, papf.effective_end_date, papf.effective_start_date, papf.person_id, papf.person_number, pprd.payroll_stat_unit_id, NVL2(paaf.organization_id,'DEPARTMENT','') organization_id_f, ppos.adjusted_svc_date, ppos.original_date_of_hire, papf.object_version_number, paaf.ASS_ATTRIBUTE1, paaf.ASS_ATTRIBUTE10, paaf.ASS_ATTRIBUTE11, paaf.ASS_ATTRIBUTE12, paaf.ASS_ATTRIBUTE13, paaf.ASS_ATTRIBUTE14, paaf.ASS_ATTRIBUTE15, paaf.ASS_ATTRIBUTE16, paaf.ASS_ATTRIBUTE17, paaf.ASS_ATTRIBUTE18, paaf.ASS_ATTRIBUTE19, paaf.ASS_ATTRIBUTE2, paaf.ASS_ATTRIBUTE20, paaf.ASS_ATTRIBUTE21, paaf.ASS_ATTRIBUTE22, paaf.ASS_ATTRIBUTE23, paaf.ASS_ATTRIBUTE24, paaf.ASS_ATTRIBUTE25, paaf.ASS_ATTRIBUTE26, paaf.ASS_ATTRIBUTE27, paaf.ASS_ATTRIBUTE28, paaf.ASS_ATTRIBUTE29, paaf.ASS_ATTRIBUTE3, paaf.ASS_ATTRIBUTE30, paaf.ASS_ATTRIBUTE5, paaf.ASS_ATTRIBUTE6, paaf.ASS_ATTRIBUTE7, paaf.ASS_ATTRIBUTE8, paaf.ASS_ATTRIBUTE4, paaf.ASS_ATTRIBUTE9, paaf.last_update_date, paaf.REASON_CODE , paaf.created_by, paaf.creation_date, paaf.last_updated_by FROM PER_ALL_PEOPLE_F papf, per_periods_of_service ppos, per_all_assignments_f paaf, pay_payroll_assignments ppa, pay_pay_relationships_dn pprd, (SELECT (SELECT MIN(pdf.date_value) FROM pay_dates pdf, pay_time_definitions ptdf WHERE papd.assigned_payroll_id = pdf.source_id AND ptdf.short_name = 'FSED' AND pdf.source_type ='AP' AND ptdf.time_definition_id = pdf.time_definition_id ) AS start_date, NVL( (SELECT MAX(pdf.date_value) FROM pay_dates pdf, pay_time_definitions ptdf WHERE papd.assigned_payroll_id = pdf.source_id AND ptdf.short_name = 'LSED' AND pdf.source_type ='AP' AND ptdf.time_definition_id = pdf.time_definition_id ),papd.end_date ) AS end_date, papd.payroll_term_id, papd.payroll_id FROM pay_assigned_payrolls_dn papd ) papd WHERE ppos.person_id = papf.person_id AND paaf.person_id = ppos.person_id AND paaf.period_of_service_id = ppos.period_of_service_id AND paaf.assignment_id = ppa.hr_assignment_id AND papd.payroll_term_id(+) = ppa.payroll_term_id AND ppa.payroll_relationship_id = pprd.payroll_relationship_id AND paaf.work_terms_assignment_id = ppa.hr_term_id AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE )paaf WHERE s.participant_id = ppa.payee_participant_id AND s.source_system_id = paaf.person_id(+) AND ppa.PAYRUN_ID = pra.PAYRUN_ID AND pra.org_id = cnr.org_id AND pra.pay_period_id = prd.period_id AND cnr.calendar_id = prd.calendar_id AND PayGroupPEO.PAY_GROUP_ID = pra.PAY_GROUP_ID AND PayGroupPEO.PAY_GROUP_ID = PayGroupTLPEO.PAY_GROUP_ID AND ppa.PLAN_COMPONENT_ID = PlanComponentTLPEO.PLAN_COMPONENT_ID AND PlanComponentPEO.PLAN_COMPONENT_ID = PlanComponentTLPEO.PLAN_COMPONENT_ID AND PlanComponentPEO.ORG_ID = PlanComponentTLPEO.ORG_ID AND PayGroupTLPEO.LANGUAGE = 'US' AND PlanComponentTLPEO.LANGUAGE = 'US'

SQL_Statement

SELECT ppa.PAYRUN_ID,

ppa.HOLD_FLAG,

ppa.SOURCE_EARNING_ID,

ppa.PAY_TO_HOME_CURR_CNVRT,

ppa.PAY_AMT_PAY_CURR,

ppa.PAY_TO_FUNC_CURR_CNVRT,

ppa.PAY_AMT_HOME_CURR,

ppa.HOME_CURRENCY_CODE,

ppa.PAY_CURRENCY_CODE,

ppa.COMMISSION_VALUE,

ppa.PAYEE_PARTICIPANT_ID,

ppa.CREDITED_PARTICIPANT_ID,

ppa.PAYEE_EMPLOYEE_NUMBER,

ppa.CREDITED_EMPLOYEE_NUMBER,

ppa.SOURCE_EVENT_DATE,

ppa.PAY_AMT_FUNC_CURR,

ppa.SOURCE_TYPE,

ppa.PARTICIPANT_PAY_ID,

ppa.SOURCE_TRX_NUMBER,

ppa.SOURCE_ORG_ID,

ppa.EARNING_ID,

ppa.ADJUST_STATUS,

ppa.ADJUST_COMMENTS,

ppa.PLAN_COMPONENT_ID,

ppa.SRP_PLAN_ASSIGN_ID,

ppa.ROLE_ID,

ppa.RECOVERABLE_FLAG,

ppa.WAIVE_FLAG,

ppa.CREDIT_TYPE_ID,

ppa.EXPENSE_CCID,

ppa.LIABILITY_CCID,

ppa.PAY_ELEMENT_TYPE_ID,

ppa.TRANSACTION_TYPE,

ppa.CREATED_BY,

ppa.COMM_AMT_CALC_CURR,

ppa.MANUAL_ADJ_REASON_CODE,

pra.PAY_DATE,

pra.INCENTIVE_TYPE_CODE,

pra.PAY_PERIOD_ID,

pra.ORG_ID,

pra.OBJECT_STATUS PAYRUN_STATUS,

prd.START_DATE PAY_PERIOD_START_DATE,

prd.END_DATE PAY_PERIOD_END_DATE,

paaf.ASSIGNMENT_NUMBER,

paaf.ASSIGNMENT_ID,

paaf.LEGISLATIVE_DATA_GROUP_ID,

paaf.PERSON_NUMBER,

pra.PAYRUN_NAME,

PayGroupTLPEO.PAY_GROUP_NAME,

PayGroupPEO.PAY_GROUP_TYPE,

ppa.LAST_UPDATED_BY,

PlanComponentTLPEO.PLAN_COMPONENT_NAME,

PlanComponentPEO.INCENTIVE_TYPE,

PlanComponentPEO.PAYMENT_GROUP_CODE,

s.participant_name

FROM CN_TP_PARTICIPANT_PAY_ALL ppa,

cn_srp_participant_hdr_v s,

CN_TP_PAYRUNS_ALL pra,

cn_repositories_all_vl cnr,

cn_periods_b prd,

CN_PAY_GROUPS_ALL_B PayGroupPEO,

CN_PAY_GROUPS_ALL_TL PayGroupTLPEO,

CN_PLAN_COMPONENTS_ALL_B PlanComponentPEO,

CN_PLAN_COMPONENTS_ALL_TL PlanComponentTLPEO,

(SELECT ppa.end_date payrollAssign_end_date,

ppa.start_date payrollAssign_start_date,

papd.end_date assignedPayR_end_date,

papd.start_date assignedPayR_start_date,

papf.business_group_id,

paaf.assignment_id,

paaf.action_code,

paaf.effective_end_date eff_end_date,

paaf.effective_start_date eff_start_date,

paaf.employee_category,

paaf.employment_category,

paaf.frequency,

paaf.grade_id,

paaf.job_id,

paaf.legal_entity_id,

paaf.location_id,

paaf.assignment_name,

paaf.assignment_number,

paaf.organization_id,

paaf.position_id,

paaf.primary_assignment_flag,

paaf.primary_flag assignments_primary_flag,

paaf.primary_work_relation_flag,

paaf.primary_work_terms_flag,

paaf.assignment_status_type,

paaf.system_person_type,

paaf.assignment_type,

ppa.hr_assignment_id,

ppos.actual_termination_date,

ppos.date_start,

ppos.period_of_service_id,

ppos.legislation_code,

ppos.primary_flag periodService_primary_flag,

ppos.worker_number,

ppa.hr_term_id,

pprd.legislative_data_group_id,

ppa.legal_employer_id,

papd.payroll_id,

ppa.payroll_assignment_id,

ppa.payroll_relationship_id,

pprd.payroll_relationship_number,

ppa.payroll_term_id,

papf.effective_end_date,

papf.effective_start_date,

papf.person_id,

papf.person_number,

pprd.payroll_stat_unit_id,

NVL2(paaf.organization_id,'DEPARTMENT','') organization_id_f,

ppos.adjusted_svc_date,

ppos.original_date_of_hire,

papf.object_version_number,

paaf.ASS_ATTRIBUTE1,

paaf.ASS_ATTRIBUTE10,

paaf.ASS_ATTRIBUTE11,

paaf.ASS_ATTRIBUTE12,

paaf.ASS_ATTRIBUTE13,

paaf.ASS_ATTRIBUTE14,

paaf.ASS_ATTRIBUTE15,

paaf.ASS_ATTRIBUTE16,

paaf.ASS_ATTRIBUTE17,

paaf.ASS_ATTRIBUTE18,

paaf.ASS_ATTRIBUTE19,

paaf.ASS_ATTRIBUTE2,

paaf.ASS_ATTRIBUTE20,

paaf.ASS_ATTRIBUTE21,

paaf.ASS_ATTRIBUTE22,

paaf.ASS_ATTRIBUTE23,

paaf.ASS_ATTRIBUTE24,

paaf.ASS_ATTRIBUTE25,

paaf.ASS_ATTRIBUTE26,

paaf.ASS_ATTRIBUTE27,

paaf.ASS_ATTRIBUTE28,

paaf.ASS_ATTRIBUTE29,

paaf.ASS_ATTRIBUTE3,

paaf.ASS_ATTRIBUTE30,

paaf.ASS_ATTRIBUTE5,

paaf.ASS_ATTRIBUTE6,

paaf.ASS_ATTRIBUTE7,

paaf.ASS_ATTRIBUTE8,

paaf.ASS_ATTRIBUTE4,

paaf.ASS_ATTRIBUTE9,

paaf.last_update_date,

paaf.REASON_CODE ,

paaf.created_by,

paaf.creation_date,

paaf.last_updated_by

FROM PER_ALL_PEOPLE_F papf,

per_periods_of_service ppos,

per_all_assignments_f paaf,

pay_payroll_assignments ppa,

pay_pay_relationships_dn pprd,

(SELECT

(SELECT MIN(pdf.date_value)

FROM pay_dates pdf,

pay_time_definitions ptdf

WHERE papd.assigned_payroll_id = pdf.source_id

AND ptdf.short_name = 'FSED'

AND pdf.source_type ='AP'

AND ptdf.time_definition_id = pdf.time_definition_id

) AS start_date,

NVL(

(SELECT MAX(pdf.date_value)

FROM pay_dates pdf,

pay_time_definitions ptdf

WHERE papd.assigned_payroll_id = pdf.source_id

AND ptdf.short_name = 'LSED'

AND pdf.source_type ='AP'

AND ptdf.time_definition_id = pdf.time_definition_id

),papd.end_date ) AS end_date,

papd.payroll_term_id,

papd.payroll_id

FROM pay_assigned_payrolls_dn papd

) papd

WHERE ppos.person_id = papf.person_id

AND paaf.person_id = ppos.person_id

AND paaf.period_of_service_id = ppos.period_of_service_id

AND paaf.assignment_id = ppa.hr_assignment_id

AND papd.payroll_term_id(+) = ppa.payroll_term_id

AND ppa.payroll_relationship_id = pprd.payroll_relationship_id

AND paaf.work_terms_assignment_id = ppa.hr_term_id

AND TRUNC(SYSDATE) BETWEEN papf.EFFECTIVE_START_DATE AND papf.EFFECTIVE_END_DATE

)paaf

WHERE s.participant_id = ppa.payee_participant_id

AND s.source_system_id = paaf.person_id(+)

AND ppa.PAYRUN_ID = pra.PAYRUN_ID

AND pra.org_id = cnr.org_id

AND pra.pay_period_id = prd.period_id

AND cnr.calendar_id = prd.calendar_id

AND PayGroupPEO.PAY_GROUP_ID = pra.PAY_GROUP_ID

AND PayGroupPEO.PAY_GROUP_ID = PayGroupTLPEO.PAY_GROUP_ID

AND ppa.PLAN_COMPONENT_ID = PlanComponentTLPEO.PLAN_COMPONENT_ID

AND PlanComponentPEO.PLAN_COMPONENT_ID = PlanComponentTLPEO.PLAN_COMPONENT_ID

AND PlanComponentPEO.ORG_ID = PlanComponentTLPEO.ORG_ID

AND PayGroupTLPEO.LANGUAGE = 'US'

AND PlanComponentTLPEO.LANGUAGE = 'US'