final_query
SELECT
rules.RecordName,
rules.RecordNumber,
rules.PaysheetId_c,
rules.PayPeriodID_c,
rules.PayDate_c,
rules.BadgeIDWorkerNumber_c,
rules.ParticipantId_c,
rules.RoleStartDate_c,
rules.RoleEndDate_c,
rules.PaymentBatch_c,
rules.PayGroup_c,
NVL(TO_CHAR(rules.TotalPaysheetAmount_c, 'FM9999999990.0000'), '0') AS TotalPaysheetAmount_c,
rules.CurrencyCode,
rules.PlanComponentId_c,
NVL(TO_CHAR(rules.ProratedTI_c, 'FM9999999990.0000'), '0') AS ProratedTI_c,
TO_CHAR(rules.PTDUSD_c) AS PTDUSD_c,
TO_CHAR(rules.BBUSD_c) AS BBUSD_c,
rules.MultiCurrency_c,
rules.PaysheetAutomatedRefreshStartCST_c,
rules.PaysheetAutomatedRefreshEndCST_c,
TO_CHAR(rules.TotalPaysheetAmountUSD_c) AS TotalPaysheetAmountUSD_c,
rules.ConversionRateHomeToUSD_c,
rules.PlanComponentName_c,
rules.OwnerEmailId_c,
rules.HRStatus_c,
rules.EmploymentCountry_c,
rules.RoleName_c,
NVL(TO_CHAR(rules.BeginningBalance_c, 'FM9999999990.0000'), '0') AS BeginningBalance_c,
TO_CHAR(rules.CED_c) AS CED_c,
TO_CHAR(rules.CEDUSD_c) AS CEDUSD_c,
NVL(TO_CHAR(rules.HRTI_c, 'FM9999999990.0000'), '0') AS HRTI_c,
rules.BBOfHRTI_c,
rules.IncentiveType_c,
rules.SalesRP_c,
rules.ActionCode_c,
NVL(TO_CHAR(rules.PTDEarnings_c, 'FM9999999990.0000'), '0') AS PTDEarnings_c,
-- rules.PayrollSubmissionEndCST_c,
-- rules.MPACutoffStartCST_c,
-- rules.MPACutoffEndCST_c,
-- BBAction_c
DECODE(Rule_Number,
1, 'MAV_DIR_BB_ACTION_1000',
2, 'MAV_DIR_BB_ACTION_1003',
3, 'MAV_DIR_BB_ACTION_1001',
4, 'MAV_DIR_BB_ACTION_1003',
5, 'MAV_DIR_BB_ACTION_1003',
6, 'MAV_DIR_BB_ACTION_1003',
7, 'MAV_DIR_BB_ACTION_1003',
8, 'MAV_DIR_BB_ACTION_1003',
9, 'MAV_DIR_BB_ACTION_1003',
10, 'MAV_DIR_BB_ACTION_1003',
99, 'MAV_DIR_BB_ACTION_1002',
11, 'MAV_DIR_BB_ACTION_1051',
NULL
) AS BBAction_c,
-- BBReasonCode_c
DECODE(Rule_Number,
1, 'MVA_DIR_BB_REASON_1018',
2, 'MVA_DIR_BB_REASON_1001',
3, 'MVA_DIR_BB_REASON_1018',
4, 'MVA_DIR_BB_REASON_1001',
5, 'MVA_DIR_BB_REASON_1001',
6, 'MVA_DIR_BB_REASON_1001',
7, 'MVA_DIR_BB_REASON_1001',
8, 'MVA_DIR_BB_REASON_1001',
9, 'MVA_DIR_BB_REASON_1001',
10, 'MVA_DIR_BB_REASON_1001',
99, 'MVA_DIR_BB_REASON_1001',
11, 'MVA_DIR_BB_REASON_1027',
NULL
) AS BBReasonCode_c,
-- BBMPAComment_c (NEW BB/MPA Comment)
DECODE(Rule_Number,
1, 'Full BB is pre-approved by system as the rep is active sale rep within the defined threshold limits (BB % HR TI ≥ -5% and ≤ 5%). Pre-approved not applicable for Austria country.',
2, 'Full CED put on Hold by system as the rep is on LOA with positive or negative CED amount. PENDING analyst to review and action.',
3, 'Full CED is pre-approved by system as the rep is terminated within the defined threshold limits of CED amount (CED ≥ 0 and ≤ 10 USD). Pre-approved not applicable for Austria country.',
4, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED ≥ 0 and ≤ 10 USD). PENDING analyst to review and action.',
5, 'Full CED put on Hold by system as the rep is terminated within the defined threshold limits of CED amount (CED > 10 USD). PENDING analyst to review and action.',
6, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
7, 'Full CED put on Hold by system as the rep is terminated with negative CED amount. PENDING analyst to review and action. Consider Payroll collection, if negative CED amount is final.',
8, 'Full CED put on Hold by system as the rep moved to non-sales role with positive CED amount. PENDING analyst to review and action.',
9, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Payroll collection not applicable for Romania country.',
10, 'Full CED put on Hold by system as the rep moved to non-sales role with negative CED amount. PENDING analyst to review and action. Consider Payroll collection if negative CED amount is final. Rep''s consent NEEDS to be attached for such case.',
99, 'Full BB put on Hold by system as the rep is active sale rep OVER the defined pre-approved BB threshold limits (BB % HR TI ≥ -5% and ≤ 5%) or is Austria rep. PENDING analyst to review and action.',
11, 'Zero BB is pre-approved by system for active sale rep',
NULL
) AS BBMPAComment_c,
-- MPAReasonCode_c (MPA reason code)
DECODE(Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'MAV_DIR_MPA_CODE_1008'
) AS MPAReasonCode_c,
-- MPAGenerated_c (MPA Auto prepopulate)
DECODE(Rule_Number,
1, NULL,
2, (-1) * rules.CED_c,
3, NULL,
4, (-1) * rules.CED_c,
5, (-1) * rules.CED_c,
6, (-1) * rules.CED_c,
7, (-1) * rules.CED_c,
8, (-1) * rules.CED_c,
9, (-1) * rules.CED_c,
10, (-1) * rules.CED_c,
11 , NULL,
99, (-1) * rules.BeginningBalance_c,
NULL
) AS MPAGenerated_c,
-- ExpectedPaymentAmount_c (Expected Payout Amount)
DECODE(Rule_Number,
1, NULL,
3, NULL,
11, NULL,
99, rules.PTDEarnings_c,
NULL
) AS ExpectedPaymentAmount_c,
-- MPAStatus_c (SCP/SPIF manager's approval)
DECODE(Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'Auto approved'
) AS MPAStatus_c,
-- RecoverableFlagV1_c (Recoverable MPA? Y/N)
DECODE(Rule_Number,
1, NULL,
3, NULL,
11, NULL,
'Y'
) AS RecoverableFlagV1_c,
rules.Owner_Id_c OraZcxOwner_Id_c,
rules.PayrunID_c,
-- rules.PaymentCycle_c,
rules.ParticipantName_c,
rules.BadgeIdOfSCPAnalyst_c
FROM (
SELECT base.*,
-- Evaluate complex conditions ONLY ONCE
CASE
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.HRTI_c != 0
AND base.BBOfHRTI_c BETWEEN -0.05 AND 0.05
AND base.BeginningBalance_c != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 1
WHEN base.HRStatus_c = 'L'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CED_c != 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 2
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'AT'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CEDUSD_c BETWEEN 0 AND 10
AND base.IncentiveType_c = 'COMMISSION'
THEN 3
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CEDUSD_c BETWEEN 0 AND 10
AND base.IncentiveType_c = 'BONUS'
THEN 4
WHEN BASE.HRStatus_c = 'T'
AND UPPER(BASE.SalesRP_c) = 'Y'
AND TO_NUMBER(BASE.CEDUSD_c) BETWEEN 0 AND 10
AND BASE.IncentiveType_c = 'COMMISSION'
AND BASE.EmploymentCountry_c = 'AT'
THEN 4
WHEN base.HRStatus_c = 'T'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CEDUSD_c > 10
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 5
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c = 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CED_c < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 6
WHEN base.HRStatus_c = 'T'
AND base.EmploymentCountry_c != 'RO'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.CED_c < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 7
WHEN base.HRStatus_c = 'A'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND base.CED_c > 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 8
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c = 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND base.CED_c < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 9
WHEN base.HRStatus_c = 'A'
AND base.EmploymentCountry_c != 'RO'
AND (UPPER(base.SalesRP_c) = 'N' OR base.SalesRP_c IS NULL)
AND base.CED_c < 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 10
WHEN base.HRStatus_c = 'A'
AND UPPER(base.SalesRP_c) = 'Y'
AND base.BeginningBalance_c = 0
AND base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 11
WHEN base.IncentiveType_c IN ('COMMISSION', 'BONUS')
THEN 99
ELSE 0
END AS Rule_Number
FROM (
WITH
-- Optimization: Defined Org ID once to avoid repeated subqueries in CTEs
BUS_UNIT AS (
SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV'
),
CN_ROLE_DETAILS AS (
SELECT
PART_ROLE.PARTICIPANT_ID,
PERIOD.ORG_ID,
PERIOD.PERIOD_ID,
CN_ROLE.ROLE_ID,
CN_ROLE.ROLE_NAME,
PART_ROLE.START_DATE_ACTIVE,
PART_ROLE.END_DATE_ACTIVE,
ROW_NUMBER() OVER (
PARTITION BY PART_ROLE.PARTICIPANT_ID, PERIOD.ORG_ID, PERIOD.PERIOD_ID
ORDER BY NVL(PART_ROLE.END_DATE_ACTIVE, '9999-12-31') DESC
) AS LATEST_ROLE_ORDER
FROM CN_PARTICIPANT_ROLES PART_ROLE
JOIN CN_ROLES_TL CN_ROLE
ON PART_ROLE.ROLE_ID = CN_ROLE.ROLE_ID
AND CN_ROLE.LANGUAGE = 'US'
JOIN CN_SRP_PARTICIPANTS_ALL PH
ON PH.PARTICIPANT_ID = PART_ROLE.PARTICIPANT_ID
JOIN BUS_UNIT BU
ON PH.ORG_ID = BU.bu_id
JOIN CN_PERIOD_DIMS_ALL_TL PERIOD
ON PERIOD.ORG_ID = PH.ORG_ID
AND PERIOD.PERIOD_TYPE = 'Monthly'
AND PERIOD.PERIOD_ID IN (:P_PERIOD_ID)
WHERE (PH.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (PH.PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
AND EXISTS (
SELECT 1 FROM CN_ROLE_PLANS_ALL RP WHERE RP.ROLE_ID = PART_ROLE.ROLE_ID
)
AND (
PERIOD.END_DATE BETWEEN PART_ROLE.START_DATE_ACTIVE AND NVL(PART_ROLE.END_DATE_ACTIVE, '9999-12-31')
OR
NVL(PART_ROLE.END_DATE_ACTIVE, '9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE
)
) ,
PRORATED_MONTHLY_TI AS (
SELECT SPFM.PARTICIPANT_ID "PARTICIPANT_ID",
SPFM.ORG_ID "ORG_ID",
SPFM.PERIOD_ID "PERIOD_ID",
SRP_CP.START_DATE "START_DATE",
SRP_CP.END_DATE "END_DATE",
SPFM.OUTPUT_ACHIEVED_ITD Prorated_MonthlyTI,
ROW_NUMBER() OVER (PARTITION BY SPFM.PARTICIPANT_ID, SPFM.ORG_ID, SPFM.PERIOD_ID ORDER BY NVL(SRP_CP.END_DATE,'9999-12-31') DESC) AS LATEST_TI_RECORD
FROM CN_SRP_PER_FORM_METRICS_ALL SPFM
JOIN CN_FORMULAS_ALL_B Monthly_TI
ON Monthly_TI.FORMULA_ID = SPFM.FORMULA_ID
AND Monthly_TI.ATTRIBUTE9 = 'EmployeeEvents'
AND Monthly_TI.ATTRIBUTE10 = 'MonthlyTI'
JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_CP.PARTICIPANT_ID = SPFM.PARTICIPANT_ID
AND SPFM.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SPFM.ORG_ID = SRP_CP.ORG_ID
JOIN BUS_UNIT BU
ON SPFM.ORG_ID = BU.bu_id
JOIN CN_PERIOD_DIMS_ALL_TL PERIOD
ON PERIOD.ORG_ID = SPFM.ORG_ID
AND PERIOD.PERIOD_TYPE = 'Monthly'
AND PERIOD.PERIOD_ID = SPFM.PERIOD_ID
AND PERIOD.PERIOD_ID IN (:P_PERIOD_ID)
WHERE (
PERIOD.END_DATE BETWEEN SRP_CP.START_DATE AND NVL(SRP_CP.END_DATE,'9999-12-31')
OR
NVL(SRP_CP.END_DATE,'9999-12-31') BETWEEN PERIOD.START_DATE AND PERIOD.END_DATE
)
and
(SPFM.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (SPFM.PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
),
LATEST_PLAN_COMPONENT AS (
SELECT /*+ MATERIALIZE */
DISTINCT PART_PAY.CREDITED_PARTICIPANT_ID "PARTICIPANT_ID",
PART_PAY.ORG_ID "ORG_ID",
PB.PAYRUN_ID PAYRUN_ID,
PB.PAY_PERIOD_ID "PERIOD_ID",
PART_PAY.PLAN_COMPONENT_ID PLAN_COMPONENT_ID,
PC.PLAN_COMPONENT_NAME PLAN_COMPONENT_NAME,
PC.REPORT_GROUP INCENTIVE_TYPE,
DENSE_RANK()
OVER (PARTITION BY
PART_PAY.CREDITED_PARTICIPANT_ID
, PART_PAY.ORG_ID
, PB.PAY_PERIOD_ID
, PB.PAYRUN_ID ORDER BY PART_PAY.SOURCE_EVENT_DATE DESC) AS LATEST_PC_RECORD
FROM CN_TP_PARTICIPANT_PAY_ALL PART_PAY
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = PART_PAY.PAYRUN_ID
AND PB.ORG_ID = PART_PAY.ORG_ID
AND PART_PAY.SOURCE_EVENT_DATE <= PB.PAY_DATE
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
JOIN BUS_UNIT BU
ON PART_PAY.ORG_ID = BU.bu_id
JOIN CN_PLAN_COMPONENTS_ALL_VL PC
ON PART_PAY.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
JOIN CN_SRP_PLAN_COMPONENTS_ALL SRP_PC
ON SRP_PC.PLAN_COMPONENT_ID = PC.PLAN_COMPONENT_ID
AND SRP_PC.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
JOIN CN_SRP_COMP_PLANS_ALL SRP_CP
ON SRP_PC.SRP_COMP_PLAN_ID = SRP_CP.SRP_COMP_PLAN_ID
AND SRP_CP.PARTICIPANT_ID = PART_PAY.CREDITED_PARTICIPANT_ID
WHERE
-- Payrun and Period Filters
PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID, 'ALL') = 'ALL')
-- Participant / Badge Filters
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (PART_PAY.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
),
CO_PAYRUN_CALENDAR AS (
SELECT UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR010) "PAYMENT_BATCH_NAME",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR009) "PAYGROUP_NAME",
PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 "PERIOD_ID",
PAYRUN_CAL.CURRENCY_CODE "CURRENCY_CODE",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR008) "INCENTIVE_TYPE",
UPPER(PAYRUN_CAL.EXTN_ATTRIBUTE_CHAR007) COUNTRY,
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP004 "MPA_CUTOFF_START_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP003 "MPA_CUTOFF_END_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP011 "PAYSHEET_CUTOFF_START_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP010 "PAYSHEET_CUTOFF_DT",
PAYRUN_CAL.EXTN_ATTRIBUTE_TIMESTAMP008 "PAYROLL_SUBMISSION_END_CST"
FROM MOO_REF_ENTITIES PAYRUN_CAL
WHERE ATTRIBUTE_CATEGORY = 'MTQ_ICM_PC_OBJ_c'
AND PAYRUN_CAL.EXTN_ATTRIBUTE_NUMBER001 IN (:P_PERIOD_ID)
),
CN_CURR_CONV_RATES AS (
SELECT CR.FROM_CURRENCY "FROM_CURRENCY",
CR.TO_CURRENCY "TO_CURRENCY",
CR.CONVERSION_START_DATE "CONVERSION_START_DATE",
CR.CONVERSION_END_DATE "CONVERSION_END_DATE",
CR.CONVERSION_RATE "CONVERSION_RATE"
FROM CN_CONVERSION_RATES CR
JOIN CN_CONVERSION_TYPES_TL CT
ON CT.CONVERSION_TYPE_ID = CR.CONVERSION_TYPE_ID
AND CT.CONVERSION_TYPE_ID = '-1001'
AND CT.LANGUAGE='US'
),
CN_SRP_PYMT_DETAILS AS (
SELECT PS.PAYRUN_ID,
PS.PARTICIPANT_ID "CREDITED_PARTICIPANT_ID",
PS.ORG_ID "ORG_ID",
PB.PAYRUN_NAME "PAYRUN_NAME",
PB.PAY_PERIOD_ID "PAY_PERIOD_ID",
PB.PAY_DATE "PAY_DATE",
PG.PAY_GROUP_ID "PAY_GROUP_ID",
PG.PAY_GROUP_NAME "PAY_GROUP_NAME",
NVL(PB.REPORT_GROUP,'ALL') "INCENTIVE_TYPE",
PS.PAYSHEET_ID "PAYSHEET_ID",
LK.MEANING AS "PAYSHEET_STATUS",
SUM(PS.TOTAL_AMT_HOME_CURRENCY) "TOTAL_AMT"
FROM CN_TP_PAYSHEETS_ALL PS
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = PS.PAYRUN_ID
AND PB.ORG_ID = PS.ORG_ID
-- AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
-- AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
JOIN CN_PAY_GROUPS_ALL_VL PG
ON PG.PAY_GROUP_ID = PB.PAY_GROUP_ID
AND PG.ORG_ID = PS.ORG_ID
LEFT JOIN CN_LOOKUPS LK
ON LK.LOOKUP_CODE = PS.OBJECT_STATUS
AND LK.LOOKUP_TYPE = 'CN_PAYSHEET_STATUS'
AND LK.ENABLED_FLAG = 'Y'
AND LK.LOOKUP_CODE != 'REVIEWED'
JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON PS.PARTICIPANT_ID = PD.PARTICIPANT_ID
AND PD.ORG_ID = PS.ORG_ID
AND PB.PAY_DATE BETWEEN PD.START_DATE AND NVL(PD.END_DATE,PB.PAY_DATE)
WHERE PS.QUOTA_ID IS NULL
AND PS.ORG_ID = (SELECT bu_id FROM BUS_UNIT)
AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID, 'ALL') = 'ALL')
AND (PS.PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (PS.PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
GROUP BY PS.PAYRUN_ID,
PS.PARTICIPANT_ID,
PS.ORG_ID,
PB.PAYRUN_NAME,
PB.PAY_PERIOD_ID,
PB.PAY_DATE,
PG.PAY_GROUP_ID,
PG.PAY_GROUP_NAME,
PB.REPORT_GROUP,
PS.PAYSHEET_ID,
PS.OBJECT_STATUS,
LK.MEANING
),
BB_AMT AS (
SELECT P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID",
P.ORG_ID "ORG_ID",
PB.PAY_PERIOD_ID "PAY_PERIOD_ID",
SUM(P.PAY_AMT_PAY_CURR) B_BALANCE
FROM CN_TP_PARTICIPANT_PAY_ALL P
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
-- AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
-- AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
WHERE 1 = 1
AND P.ORG_ID = (SELECT bu_id FROM BUS_UNIT)
AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
-- Added Participant Filters for consistency and speed
AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
-- Logic for Active Date Range
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)))
AND P.PAY_PERIOD_ID <> PB.PAY_PERIOD_ID
GROUP BY P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
PTD_EARNINGS_AMT AS (
SELECT /*+ MATERIALIZE LEADING(PB P) USE_NL(P) */
P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID "CREDITED_PARTICIPANT_ID",
P.ORG_ID "ORG_ID",
PB.PAY_PERIOD_ID "PAY_PERIOD_ID",
SUM(P.PAY_AMT_PAY_CURR) PTD_EARNINGS
FROM CN_TP_PARTICIPANT_PAY_ALL P
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = P.PAYRUN_ID
AND PB.ORG_ID = P.ORG_ID
AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID
-- AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
--AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
JOIN CN_SRP_PARTICIPANT_DETAILS_ALL PD
ON P.CREDITED_PARTICIPANT_ID = PD.PARTICIPANT_ID
AND P.ORG_ID = PD.ORG_ID
WHERE 1= 1
AND PB.ORG_ID = (SELECT bu_id FROM BUS_UNIT)
AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN')
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)))
AND P.PAY_PERIOD_ID = PB.PAY_PERIOD_ID
-- AND P.INCENTIVE_TYPE_CODE NOT IN ('MANUAL_PAY_ADJ', 'PMTPLN')
GROUP BY P.PAYRUN_ID,
P.CREDITED_PARTICIPANT_ID,
P.ORG_ID,
PB.PAY_PERIOD_ID
),
CN_PART_DETAILS AS (
SELECT /*+ MATERIALIZE LEADING(PART_HDR PART_DTLS) */
PART_DTLS.PARTICIPANT_ID "PARTICIPANT_ID",
PART_HDR.PARTICIPANT_NAME PARTICIPANT_NAME,
PEOPLE.PERSON_NUMBER AS "HR_PRIMARY_WORKER_NUMBER",
/* (SELECT DISTINCT papf.person_number
FROM hz_person_profiles hpp
INNER JOIN PER_ALL_PEOPLE_F papf
ON papf.person_id = hpp.hr_person_id
WHERE hpp.party_id = PART_HDR.party_id) AS "HR_PRIMARY_WORKER_NUMBER",*/
/*(SELECT DISTINCT hpp.person_name
FROM hz_person_profiles hpp
INNER JOIN PER_ALL_PEOPLE_F papf
ON papf.person_id = hpp.hr_person_id
WHERE hpp.party_id = PART_HDR.party_id) AS "PARTICIPANT_NAME",*/
PART_DTLS.PARTICIPANT_DETAIL_ID "PARTICIPANT_DETAIL_ID",
PART_DTLS.ORG_ID "ORG_ID",
PART_DTLS.START_DATE "START_DATE",
PART_DTLS.END_DATE "END_DATE",
PART_DTLS.COUNTRY_CODE HR_Country,
PART_DTLS.ATTRIBUTE_CHAR40 Employment_Country,
ANL.PARTY_ID AS "ANALYST_PARTY_ID",
/*(SELECT DISTINCT PARTY_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID
) AS "ANALYST_PARTY_ID",*/
ANL.HR_PRIMARY_WORKER_NUMBER AS "ANALYST_BADGE_ID",
/* (SELECT DISTINCT HR_PRIMARY_WORKER_NUMBER
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID) AS "ANALYST_BADGE_ID",*/
ANL_PROF.RESOURCE_PROFILE_ID AS "ANALYST_RESOURCE_PROFILE_ID",
/* (SELECT DISTINCT B.RESOURCE_PROFILE_ID
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID
) AS "ANALYST_RESOURCE_PROFILE_ID",*/
ANL_PROF.EMAIL_ADDRESS AS "OWNER_EMAIL_ID",
/* (SELECT DISTINCT B.EMAIL_ADDRESS
FROM CN_SRP_PARTICIPANTS_ALL ANALYST
JOIN JTF_RS_RESOURCE_PROFILES B ON ANALYST.PARTY_ID = B.PARTY_ID
WHERE ANALYST.ORG_ID = PART_HDR.ORG_ID
AND ANALYST.PARTICIPANT_ID = PART_HDR.ANALYST_ID
) AS "OWNER_EMAIL_ID",*/
PART_DTLS.ATTRIBUTE_NUMBER1 "HR_TI",
PART_DTLS.CURRENCY_CODE "HOME_CURRENCY",
PART_DTLS.ATTRIBUTE_CHAR2 HR_Status,
PART_DTLS.ATTRIBUTE_CHAR17 SalesRep,
PART_DTLS.ATTRIBUTE_CHAR33 ActionCode
FROM CN_SRP_PARTICIPANT_DETAILS_ALL PART_DTLS
JOIN CN_SRP_PARTICIPANTS_ALL PART_HDR
ON PART_DTLS.PARTICIPANT_ID = PART_HDR.PARTICIPANT_ID
AND PART_DTLS.ORG_ID = PART_HDR.ORG_ID
-- 1. Optimized HR Person Number lookup
LEFT JOIN (
SELECT hpp.party_id, MAX(papf.person_number) as person_number
FROM hz_person_profiles hpp
INNER JOIN PER_ALL_PEOPLE_F papf ON papf.person_id = hpp.hr_person_id
GROUP BY hpp.party_id
) PEOPLE ON PEOPLE.party_id = PART_HDR.party_id
-- 2. Optimized Analyst Header Info
LEFT JOIN CN_SRP_PARTICIPANTS_ALL ANL
ON ANL.PARTICIPANT_ID = PART_HDR.ANALYST_ID
AND ANL.ORG_ID = PART_HDR.ORG_ID
-- 3. Optimized Analyst Profile Info (Email/Resource ID)
LEFT JOIN JTF_RS_RESOURCE_PROFILES ANL_PROF
ON ANL_PROF.PARTY_ID = ANL.PARTY_ID
WHERE PART_HDR.ORG_ID = (SELECT bu_id FROM BUS_UNIT)
),
MULTI_CURR_YN AS (
SELECT /*+ MATERIALIZE */
MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID,
CASE WHEN COUNT( distinct MULTI_CURR.HOME_CURRENCY_CODE)>1 THEN 'Y' ELSE 'N' END AS MULTI_CURR_YN
FROM (
SELECT p.CREDITED_PARTICIPANT_ID "PARTICIPANT_ID",
p.HOME_CURRENCY_CODE "HOME_CURRENCY_CODE",
p.PAYRUN_ID "PAYRUN_ID",
PB.ORG_ID,
PB.PAY_PERIOD_ID "PERIOD_ID",
SUM(p.PAY_AMT_PAY_CURR) "TotalAmt"
FROM CN_TP_PARTICIPANT_PAY_ALL p
JOIN CN_TP_PAYRUNS_ALL PB
ON PB.PAYRUN_ID = p.PAYRUN_ID
AND PB.ORG_ID = p.ORG_ID
-- AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
-- AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
WHERE 1 =1
AND PB.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (PB.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
/* Push participant filters deep to avoid scanning the whole table */
AND (P.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE, 'ALL') = 'ALL')
AND (P.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT, 'ALL') = 'ALL')
---AND PART_PAY.PAY_AMT_PAY_CURR <> 0
AND P.ORG_ID = (SELECT bu_id FROM BUS_UNIT)
GROUP BY p.CREDITED_PARTICIPANT_ID,
p.HOME_CURRENCY_CODE,
p.PAYRUN_ID,
PB.ORG_ID,
PB.PAY_PERIOD_ID
HAVING SUM(p.PAY_AMT_PAY_CURR) <> 0
) MULTI_CURR
GROUP BY MULTI_CURR.PARTICIPANT_ID,
MULTI_CURR.PAYRUN_ID,
MULTI_CURR.ORG_ID,
MULTI_CURR.PERIOD_ID
)
SELECT
TO_CHAR(
SPD.PAY_PERIOD_ID
|| '-' ||
SUBSTR(SPD.PAYRUN_NAME,1+INSTR(SPD.PAYRUN_NAME,'_',-1,2),(INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1))
|| '-' ||
PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER
|| '-' ||
DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))
) RecordName,
TO_CHAR(PD_PAY_DT.ANALYST_RESOURCE_PROFILE_ID) Owner_Id_c,
NVL(MC_YN.MULTI_CURR_YN,'N') MultiCurrency_c,
TO_CHAR(
SPD.PAY_PERIOD_ID
|| '-' ||
SUBSTR(SPD.PAYRUN_NAME,1+INSTR(SPD.PAYRUN_NAME,'_',-1,2),(INSTR(SPD.PAYRUN_NAME,'_',-1,1)-INSTR(SPD.PAYRUN_NAME,'_',-1,2)-1))
|| '-' ||
PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER
|| '-' ||
DECODE(UPPER(SPD.INCENTIVE_TYPE),'COMMISSION','COM','BONUS','SPIF',UPPER(SPD.INCENTIVE_TYPE))
) RecordNumber,
PD_PAY_DT.HOME_CURRENCY CurrencyCode,
TO_CHAR(SPD.PAYRUN_ID) PayrunID_c,
'Regular cycle' PaymentCycle_c,
TO_CHAR(SPD.PAY_PERIOD_ID) PayPeriodID_c,
TO_CHAR(SPD.PAY_DATE,'MM/DD/YYYY') PayDate_c,
PD_PAY_DT.HR_PRIMARY_WORKER_NUMBER BadgeIDWorkerNumber_c,
TO_CHAR(SPD.CREDITED_PARTICIPANT_ID) ParticipantId_c,
PD_PAY_DT.PARTICIPANT_NAME ParticipantName_c,
PRD.ROLE_NAME RoleName_c,
NVL(PD_PYMT_DT.HR_Status,PD_PAY_DT.HR_Status) HRStatus_c,
TO_CHAR(PRD.START_DATE_ACTIVE,'MM/DD/YYYY') RoleStartDate_c,
TO_CHAR(PRD.END_DATE_ACTIVE,'MM/DD/YYYY') RoleEndDate_c,
SPD.PAYRUN_NAME PaymentBatch_c,
SPD.PAY_GROUP_NAME PayGroup_c,
ROUND(NVL(BB.B_BALANCE,0),4) BeginningBalance_c,
ROUND(NVL(CUR_PAY.PTD_EARNINGS,0),4) PTDEarnings_c,
TO_CHAR(LPC.PLAN_COMPONENT_ID) PlanComponentId_c,
ROUND(NVL(PD_PAY_DT.HR_TI,0),4) HRTI_c,
CASE WHEN NVL(PD_PAY_DT.HR_TI,0)=0 THEN 0 ELSE ROUND((NVL(BB.B_BALANCE,0)/PD_PAY_DT.HR_TI),4) END BBOfHRTI_c,
ROUND(NVL(SPD.TOTAL_AMT,0),4) TotalPaysheetAmount_c,
ROUND(NVL(PM_TI.Prorated_MonthlyTI,0),4) ProratedTI_c,
NVL(PD_PYMT_DT.SalesRep,PD_PAY_DT.SalesRep) SalesRP_c,
ROUND((NVL(BB.B_BALANCE,0) + NVL(CUR_PAY.PTD_EARNINGS,0)),4) CED_c,
ROUND(((NVL(BB.B_BALANCE,0) + NVL(CUR_PAY.PTD_EARNINGS,0)) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) CEDUSD_c,
ROUND((NVL(BB.B_BALANCE,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) BBUSD_c,
ROUND((NVL(CUR_PAY.PTD_EARNINGS,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) PTDUSD_c,
SPD.INCENTIVE_TYPE IncentiveType_c,
PD_PAY_DT.HR_Country EmploymentCountry_c,
TO_CHAR(PD_PAY_DT.OWNER_EMAIL_ID) OwnerEmailId_c,
TO_CHAR(PD_PAY_DT.ANALYST_BADGE_ID) BadgeIdOfSCPAnalyst_c,
ROUND(NVL(CC_TO_USD.CONVERSION_RATE,1),4) ConversionRateHomeToUSD_c,
ROUND((NVL(SPD.TOTAL_AMT,0) * NVL(CC_TO_USD.CONVERSION_RATE,1)),4) TotalPaysheetAmountUSD_c,
LPC.PLAN_COMPONENT_NAME PlanComponentName_c,
TO_CHAR(PAY_CAL.PAYROLL_SUBMISSION_END_CST, 'MM/DD/YYYY HH:MI:SS PM') PayrollSubmissionEndCST_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS PM') MPACutoffStartCST_c,
TO_CHAR(PAY_CAL.MPA_CUTOFF_END_DT, 'MM/DD/YYYY HH:MI:SS PM') MPACutoffEndCST_c,
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_DT, 'MM/DD/YYYY HH:MI:SS PM') PaysheetAutomatedRefreshEndCST_c,
TO_CHAR(PAY_CAL.PAYSHEET_CUTOFF_START_DT, 'MM/DD/YYYY HH:MI:SS PM') PaysheetAutomatedRefreshStartCST_c,
SPD.PAYSHEET_ID PaysheetId_c,
PD_PYMT_DT.ActionCode ActionCode_c
FROM CN_SRP_PYMT_DETAILS SPD
JOIN CN_PART_DETAILS PD_PAY_DT
ON TRUNC(SPD.PAY_DATE) BETWEEN PD_PAY_DT.START_DATE AND NVL(PD_PAY_DT.END_DATE,TO_DATE('9999-12-31','YYYY-MM-DD'))
AND SPD.CREDITED_PARTICIPANT_ID = PD_PAY_DT.PARTICIPANT_ID
AND SPD.ORG_ID = PD_PAY_DT.ORG_ID
LEFT JOIN CN_ROLE_DETAILS PRD
ON PRD.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND PRD.ORG_ID = SPD.ORG_ID
AND PRD.PERIOD_ID = SPD.PAY_PERIOD_ID
AND PRD.LATEST_ROLE_ORDER = 1
LEFT JOIN PRORATED_MONTHLY_TI PM_TI
ON PM_TI.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND PM_TI.ORG_ID = SPD.ORG_ID
AND PM_TI.PERIOD_ID = SPD.PAY_PERIOD_ID
AND PM_TI.LATEST_TI_RECORD = 1
LEFT JOIN LATEST_PLAN_COMPONENT LPC
ON LPC.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND LPC.ORG_ID = SPD.ORG_ID
AND LPC.PERIOD_ID = SPD.PAY_PERIOD_ID
AND LPC.PAYRUN_ID = SPD.PAYRUN_ID
AND LPC.INCENTIVE_TYPE = SPD.INCENTIVE_TYPE
AND LPC.LATEST_PC_RECORD = 1
LEFT JOIN CO_PAYRUN_CALENDAR PAY_CAL
ON PAY_CAL.PAYMENT_BATCH_NAME = UPPER(SPD.PAYRUN_NAME)
AND PAY_CAL.PAYGROUP_NAME = UPPER(SPD.PAY_GROUP_NAME)
AND PAY_CAL.PERIOD_ID = SPD.PAY_PERIOD_ID
AND PAY_CAL.CURRENCY_CODE = PD_PAY_DT.HOME_CURRENCY
AND UPPER(PAY_CAL.COUNTRY) = UPPER(PD_PAY_DT.HR_Country)
AND TO_CHAR(PAY_CAL.INCENTIVE_TYPE) = UPPER(TO_CHAR(SPD.INCENTIVE_TYPE))
LEFT JOIN CN_PART_DETAILS PD_PYMT_DT
ON TRUNC(PAY_CAL.PAYSHEET_CUTOFF_DT) BETWEEN PD_PYMT_DT.START_DATE AND NVL(PD_PYMT_DT.END_DATE,TO_DATE('9999-12-31', 'YYYY-MM-DD'))
AND PD_PYMT_DT.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND PD_PYMT_DT.ORG_ID = SPD.ORG_ID
LEFT JOIN CN_CURR_CONV_RATES CC_TO_USD
ON TRUNC(SPD.PAY_DATE) BETWEEN CC_TO_USD.CONVERSION_START_DATE AND CC_TO_USD.CONVERSION_END_DATE
AND CC_TO_USD.TO_CURRENCY = 'USD'
AND CC_TO_USD.FROM_CURRENCY = PD_PAY_DT.HOME_CURRENCY
LEFT JOIN MULTI_CURR_YN MC_YN
ON MC_YN.PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND MC_YN.PAYRUN_ID = SPD.PAYRUN_ID
AND MC_YN.ORG_ID = SPD.ORG_ID
AND MC_YN.PERIOD_ID = SPD.PAY_PERIOD_ID
LEFT JOIN PTD_EARNINGS_AMT CUR_PAY
ON CUR_PAY.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND CUR_PAY.ORG_ID = SPD.ORG_ID
AND CUR_PAY.PAYRUN_ID = SPD.PAYRUN_ID
AND CUR_PAY.PAY_PERIOD_ID = SPD.PAY_PERIOD_ID
LEFT JOIN BB_AMT BB
ON BB.CREDITED_PARTICIPANT_ID = SPD.CREDITED_PARTICIPANT_ID
AND BB.ORG_ID = SPD.ORG_ID
AND BB.PAYRUN_ID = SPD.PAYRUN_ID
AND BB.PAY_PERIOD_ID = SPD.PAY_PERIOD_ID
WHERE NVL(MC_YN.MULTI_CURR_YN,'N') = 'N'
AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_BADGE) OR COALESCE(:P_BADGE,'ALL') = 'ALL')
AND (SPD.PAYRUN_ID IN (:P_PAYRUN_ID) OR COALESCE(:P_PAYRUN_ID,'ALL') = 'ALL')
AND SPD.ORG_ID = (SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV')
AND SPD.PAY_PERIOD_ID IN (:P_PERIOD_ID)
AND (SPD.CREDITED_PARTICIPANT_ID IN (:P_PARTICIPANT_ID_FT) OR COALESCE(:P_PARTICIPANT_ID_FT,'ALL') = 'ALL')
) base
) rules
Comments
Post a Comment