SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
EMP_NUMBER saw_0,
ASSIGNMENT_NUMBER saw_1,
EMP_NAME saw_2,
HIRE_DATE saw_3,
GRADE saw_4,
JOB saw_5,
POSITION_NAME saw_6,
DEPT_NAME1 saw_7,
sector_2 saw_8,
BU_NAME1 saw_9,
PAYROLL_PERIOD_NAME saw_10,
PERIOD_NAME saw_11,
DATE_EARNED saw_12,
RUN_TYPE saw_13,
ELEMENT_CLASSI_NAME saw_14,
ELEMENT_NAME saw_15,
AMOUNT saw_16,
CASE WHEN ELEMENT_CLASSI_NAME in ('Involuntary Deductions','Social Insurance Deductions','Voluntary Deductions') THEN (AMOUNT*(-1)) ELSE AMOUNT END saw_17,
PROCESS_FLOW_NAME saw_18
FROM (SELECT
"Payroll - Payroll Run Results Real Time"."Business Unit"."Business Unit Name" BU_NAME1,
"Payroll - Payroll Run Results Real Time"."Department"."Department Name" DEPT_NAME1,
"Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" ELEMENT_CLASSI_NAME,
"Payroll - Payroll Run Results Real Time"."Element"."Element Name" ELEMENT_NAME,
"Payroll - Payroll Run Results Real Time"."Grade"."Grade Name" GRADE,
"Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Name" INPUT_VALUE_NAME,
"Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure" INPUT_VALUE_UOM,
"Payroll - Payroll Run Results Real Time"."Job"."Job Name" JOB,
"Payroll - Payroll Run Results Real Time"."Worker"."Enterprise Hire Date" HIRE_DATE,
"Payroll - Payroll Run Results Real Time"."Payroll Period"."Period Name" PERIOD_NAME,
"Payroll - Payroll Run Results Real Time"."Payroll Run Result Details"."Date Earned" DATE_EARNED,
"Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Run Result Value" RUN_RESULT_VALUE,
"Payroll - Payroll Run Results Real Time"."Payroll Statutory Unit"."Payroll Statutory Unit Name" Payroll_Statutory_Unit_Name,
"Payroll - Payroll Run Results Real Time"."Payroll"."Payroll Period Name" PAYROLL_PERIOD_NAME,
"Payroll - Payroll Run Results Real Time"."Position"."Position Name" POSITION_NAME,
"Payroll - Payroll Run Results Real Time"."Run Type"."Run Type Name" RUN_TYPE,
"Payroll - Payroll Run Results Real Time"."Worker"."Assignment Number" ASSIGNMENT_NUMBER,
"Payroll - Payroll Run Results Real Time"."Worker"."Assignment Status Code" ASSIGN_STATUS_CODE,
"Payroll - Payroll Run Results Real Time"."Worker"."Employee Display Name" EMP_NAME,
"Payroll - Payroll Run Results Real Time"."Worker"."Person Number" EMP_NUMBER,
"Payroll - Payroll Run Results Real Time"."Payroll Flows"."Flow Instance Name" PROCESS_FLOW_NAME,
"Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Amount" AMOUNT
FROM "Payroll - Payroll Run Results Real Time"
WHERE
((DESCRIPTOR_IDOF("Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure") = 'M') AND ("Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" in ('Involuntary Deductions','Social Insurance Deductions','Standard Earnings','Supplemental Earnings','Voluntary Deductions','Information') )AND ("Input Value"."Input Value Name" = 'Pay Value'))
) TABLE_A LEFT OUTER JOIN (
SELECT
"Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,
"Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))
) TABLE_B ON TABLE_A.EMP_NUMBER = TABLE_B.person_number2
WHERE
(EMP_NUMBER IN ('ABC')) AND (DATE_EARNED >= ANY
(
SELECT saw_0 FROM (SELECT
"Payroll Period"."Default Pay Date" saw_0
FROM "Payroll - Payroll Run Results Real Time"
WHERE
"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')
) nqw_1
)) AND (DATE_EARNED <= ANY
(
SELECT saw_0 FROM (SELECT
"Payroll Period"."Default Pay Date" saw_0
FROM "Payroll - Payroll Balances Real Time"
WHERE
"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')
) nqw_1
))
ORDER BY saw_12 DESC
No comments:
Post a Comment