CN_HCM_PAYROLL_PAYSHEET_UE

Details

Schema: FUSION
Object owner: CN
Object type: VIEW

Columns

Name
PAYRUN_NAME PAYRUN_ID PARTICIPANT_ID PERSON_ID PAY_PERIOD_ID PAY_PERIOD_START_DATE PAY_PERIOD_END_DATE PAY_DATE PAYRUN_STATUS WKSHT_STATUS PMT_AMOUNT_CALC PMT_AMOUNT_TOTAL EARNING_DIFF PAY_CURRENCY_CODE PARTICIPANT_NAME ANALYST_ID PAYSHEET_ID ORG_ID ISPAYEE WAIVE_RECOVERY_AMT MAN_ADJ_AMT ATTACHMENTENTITYNAME DIST_HMCURR_CODE_COUNT ASSIGNMENT_NUMBER ASSIGNMENT_ID LEGISLATIVE_DATA_GROUP_ID PERSON_NUMBER

Name

PAYRUN_NAME

PAYRUN_ID

PARTICIPANT_ID

PERSON_ID

PAY_PERIOD_ID

PAY_PERIOD_START_DATE

PAY_PERIOD_END_DATE

PAY_DATE

PAYRUN_STATUS

WKSHT_STATUS

PMT_AMOUNT_CALC

PMT_AMOUNT_TOTAL

EARNING_DIFF

PAY_CURRENCY_CODE

PARTICIPANT_NAME

ANALYST_ID

PAYSHEET_ID

ORG_ID

ISPAYEE

WAIVE_RECOVERY_AMT

MAN_ADJ_AMT

ATTACHMENTENTITYNAME

DIST_HMCURR_CODE_COUNT

ASSIGNMENT_NUMBER

ASSIGNMENT_ID

LEGISLATIVE_DATA_GROUP_ID

PERSON_NUMBER

Query

SQL_Statement
select cnp.payrun_name PAYRUN_NAME, cnp.payrun_id PAYRUN_ID, cnp.participant_id PARTICIPANT_ID, s.SOURCE_SYSTEM_ID PERSON_ID, cnp.pay_period_id PAY_PERIOD_ID, prd.start_date PAY_PERIOD_START_DATE, prd.end_date PAY_PERIOD_END_DATE, cnp.pay_date PAY_DATE, cnp.payrun_status PAYRUN_STATUS, cnp.paysheet_status WKSHT_STATUS, cnp.pmt_amount_calc PMT_AMOUNT_CALC, nvl(cnp.pmt_amount_calc, 0) + nvl(cnp.pay_amt_adj_pay_curr, 0) + nvl(cnp.pmt_amount_adj_rec, 0) + nvl(cnp.pmt_amount_adj_nrec, 0) + nvl(cnp.pmt_amount_recovery, 0) PMT_AMOUNT_TOTAL, nvl(cnp.pay_amt_adj_pay_curr, 0) + nvl(cnp.pmt_amount_adj_rec, 0) + nvl(cnp.pmt_amount_adj_nrec, 0) + nvl(cnp.pmt_amount_recovery, 0) EARNING_DIFF, cnp.pay_currency_code PAY_CURRENCY_CODE, s.participant_name PARTICIPANT_NAME, s.analyst_id ANALYST_ID, cnp.paysheet_id PAYSHEET_ID, cnp.org_id ORG_ID, cn_tp_payment_util.is_payee(cnp.pay_period_id, cnp.participant_id, cnp.org_id) ISPAYEE, cn_tp_payment_util.get_waive_recovery_amount(cnp.payrun_id,cnp.participant_id) WAIVE_RECOVERY_AMT, cn_tp_payment_util.get_man_adj_amount(cnp.payrun_id,cnp.participant_id) MAN_ADJ_AMT, 'CN_TP_PAYSHEETS_ALL' ATTACHMENTENTITYNAME, (select count(distinct(home_currency_code)) from cn_tp_participant_pay_all partpay where partpay.credited_participant_id=cnp.participant_id and partpay.payrun_id=cnp.payrun_id ) DIST_HMCURR_CODE_COUNT, paaf.assignment_number ASSIGNMENT_NUMBER, paaf.assignment_id ASSIGNMENT_ID, paaf.legislative_data_group_id LEGISLATIVE_DATA_GROUP_ID, paaf.person_number PERSON_NUMBER FROM cn_srp_participant_hdr_v s, ( SELECT pr.payrun_name, pr.payrun_id, pr.pay_date, pr.pay_period_id, pr.object_status payrun_status, w.paysheet_id, w.participant_id, w.org_id, w.last_updated_by, w.created_by, w.comm_due_bb, w.comm_ptd, w.bonus_ptd, w.held_amount, w.pmt_amount_recovery, w.pmt_amount_calc, w.object_status paysheet_status, w.object_version_number, w.current_earnings_due, pmt_amount_adj_rec, w.pmt_amount_adj_nrec, w.pay_amt_adj_pay_curr, decode(nvl(w.pay_currency_code,'0'),'0', decode(rep.process_currency,'FUNCTIONAL_CURRENCY',rep.functional_currency,srpdet.currency_code), w.pay_currency_code) as pay_currency_code, SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) - nvl(srp.balance2_bbc, 0)) - (nvl(srp.balance4_bbd, 0) - nvl(srp.balance4_bbc, 0) + nvl(srp.balance4_dtd, 0) - nvl(srp.balance4_ctd, 0))) balance FROM cn_srp_subledger_all srp, cn_tp_paysheets_all w, cn_tp_payruns_all pr, cn_repositories_all_vl rep, cn_srp_participant_details_all srpdet WHERE srp.org_id(+) = w.org_id AND srp.participant_id(+) = w.participant_id AND srp.earning_type_id(+) = -1000 AND srp.plan_component_id(+) IS NULL AND w.quota_id IS NULL AND pr.payrun_id = w.payrun_id AND w.org_id=rep.org_id AND w.participant_id=srpdet.participant_id AND ( ( EXISTS ( select 1 from cn_srp_participant_details_all dtexist where w.participant_id=dtexist.participant_id and pr.pay_date between dtexist.start_date and nvl(dtexist.end_date,pr.pay_date) ) AND ( pr.pay_date between srpdet.start_date and nvl(srpdet.end_date,pr.pay_date) ) ) OR ( srpdet.end_date=(select max(srpdet.end_date) from cn_srp_participant_details_all dnottexist where w.participant_id=dnottexist.participant_id) ) ) group by pr.payrun_name, pr.payrun_id, pr.pay_date, pr.pay_period_id, pr.object_status, w.paysheet_id, w.participant_id, w.org_id, w.last_updated_by, w.created_by, w.comm_due_bb, w.comm_ptd, w.bonus_ptd, w.held_amount, w.pmt_amount_recovery, w.pmt_amount_calc, w.object_status, w.object_version_number, w.current_earnings_due, pmt_amount_adj_rec, w.pmt_amount_adj_nrec, w.pay_amt_adj_pay_curr, decode(nvl(w.pay_currency_code,'0'),'0', decode(rep.process_currency,'FUNCTIONAL_CURRENCY',rep.functional_currency,srpdet.currency_code), w.pay_currency_code) ) cnp, cn_period_statuses_v cps, ( /* to get the assignment_id */ 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, cn_repositories_all_vl cnr, cn_periods_b prd WHERE s.participant_id = cnp.participant_id and s.source_system_id = paaf.person_id(+) AND cnp.org_id = s.org_id AND cnp.pay_period_id = cps.period_id AND cnp.org_id = cps.org_id AND cnp.org_id = cnr.org_id AND cnp.pay_period_id = prd.period_id AND cnr.calendar_id = prd.calendar_id

SQL_Statement

select

cnp.payrun_name PAYRUN_NAME,

cnp.payrun_id PAYRUN_ID,

cnp.participant_id PARTICIPANT_ID,

s.SOURCE_SYSTEM_ID PERSON_ID,

cnp.pay_period_id PAY_PERIOD_ID,

prd.start_date PAY_PERIOD_START_DATE,

prd.end_date PAY_PERIOD_END_DATE,

cnp.pay_date PAY_DATE,

cnp.payrun_status PAYRUN_STATUS,

cnp.paysheet_status WKSHT_STATUS,

cnp.pmt_amount_calc PMT_AMOUNT_CALC,

nvl(cnp.pmt_amount_calc, 0) + nvl(cnp.pay_amt_adj_pay_curr, 0) + nvl(cnp.pmt_amount_adj_rec, 0) + nvl(cnp.pmt_amount_adj_nrec, 0) + nvl(cnp.pmt_amount_recovery, 0) PMT_AMOUNT_TOTAL,

nvl(cnp.pay_amt_adj_pay_curr, 0) + nvl(cnp.pmt_amount_adj_rec, 0) + nvl(cnp.pmt_amount_adj_nrec, 0) + nvl(cnp.pmt_amount_recovery, 0) EARNING_DIFF,

cnp.pay_currency_code PAY_CURRENCY_CODE,

s.participant_name PARTICIPANT_NAME,

s.analyst_id ANALYST_ID,

cnp.paysheet_id PAYSHEET_ID,

cnp.org_id ORG_ID,

cn_tp_payment_util.is_payee(cnp.pay_period_id, cnp.participant_id, cnp.org_id) ISPAYEE,

cn_tp_payment_util.get_waive_recovery_amount(cnp.payrun_id,cnp.participant_id) WAIVE_RECOVERY_AMT,

cn_tp_payment_util.get_man_adj_amount(cnp.payrun_id,cnp.participant_id) MAN_ADJ_AMT,

'CN_TP_PAYSHEETS_ALL' ATTACHMENTENTITYNAME,

(select count(distinct(home_currency_code)) from cn_tp_participant_pay_all partpay where partpay.credited_participant_id=cnp.participant_id and partpay.payrun_id=cnp.payrun_id ) DIST_HMCURR_CODE_COUNT,

paaf.assignment_number ASSIGNMENT_NUMBER,

paaf.assignment_id ASSIGNMENT_ID,

paaf.legislative_data_group_id LEGISLATIVE_DATA_GROUP_ID,

paaf.person_number PERSON_NUMBER

FROM cn_srp_participant_hdr_v s,

( SELECT

pr.payrun_name,

pr.payrun_id,

pr.pay_date,

pr.pay_period_id,

pr.object_status payrun_status,

w.paysheet_id,

w.participant_id,

w.org_id,

w.last_updated_by,

w.created_by,

w.comm_due_bb,

w.comm_ptd,

w.bonus_ptd,

w.held_amount,

w.pmt_amount_recovery,

w.pmt_amount_calc,

w.object_status paysheet_status,

w.object_version_number,

w.current_earnings_due,

pmt_amount_adj_rec,

w.pmt_amount_adj_nrec,

w.pay_amt_adj_pay_curr,

decode(nvl(w.pay_currency_code,'0'),'0',

decode(rep.process_currency,'FUNCTIONAL_CURRENCY',rep.functional_currency,srpdet.currency_code), w.pay_currency_code) as pay_currency_code,

SUM((nvl(srp.balance2_dtd, 0) - nvl(srp.balance2_ctd, 0) + nvl(srp.balance2_bbd, 0) -

nvl(srp.balance2_bbc, 0)) - (nvl(srp.balance4_bbd, 0) - nvl(srp.balance4_bbc, 0) +

nvl(srp.balance4_dtd, 0) - nvl(srp.balance4_ctd, 0))) balance

FROM cn_srp_subledger_all srp,

cn_tp_paysheets_all w,

cn_tp_payruns_all pr,

cn_repositories_all_vl rep,

cn_srp_participant_details_all srpdet

WHERE srp.org_id(+) = w.org_id

AND srp.participant_id(+) = w.participant_id

AND srp.earning_type_id(+) = -1000

AND srp.plan_component_id(+) IS NULL

AND w.quota_id IS NULL

AND pr.payrun_id = w.payrun_id

AND w.org_id=rep.org_id

AND w.participant_id=srpdet.participant_id

AND (

(

EXISTS ( select 1 from cn_srp_participant_details_all dtexist where w.participant_id=dtexist.participant_id and pr.pay_date between dtexist.start_date and nvl(dtexist.end_date,pr.pay_date) )

AND

( pr.pay_date between srpdet.start_date and nvl(srpdet.end_date,pr.pay_date) )

)

OR

(

srpdet.end_date=(select max(srpdet.end_date) from cn_srp_participant_details_all dnottexist where w.participant_id=dnottexist.participant_id)

)

group by pr.payrun_name, pr.payrun_id, pr.pay_date, pr.pay_period_id, pr.object_status, w.paysheet_id,

w.participant_id, w.org_id, w.last_updated_by, w.created_by, w.comm_due_bb, w.comm_ptd, w.bonus_ptd,

w.held_amount, w.pmt_amount_recovery, w.pmt_amount_calc, w.object_status, w.object_version_number,

w.current_earnings_due, pmt_amount_adj_rec, w.pmt_amount_adj_nrec, w.pay_amt_adj_pay_curr, decode(nvl(w.pay_currency_code,'0'),'0',

decode(rep.process_currency,'FUNCTIONAL_CURRENCY',rep.functional_currency,srpdet.currency_code), w.pay_currency_code)

) cnp,

cn_period_statuses_v cps,

( /* to get the assignment_id */

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,

cn_repositories_all_vl cnr,

cn_periods_b prd

WHERE s.participant_id = cnp.participant_id

and s.source_system_id = paaf.person_id(+)

AND cnp.org_id = s.org_id

AND cnp.pay_period_id = cps.period_id

AND cnp.org_id = cps.org_id

AND cnp.org_id = cnr.org_id

AND cnp.pay_period_id = prd.period_id

AND cnr.calendar_id = prd.calendar_id