MPA report generated
-- MPA Report with Manual MC Adjustment rows
SELECT
EXTN_ATTRIBUTE_CHAR032 AS Paysheet_ID,
'Auto Approved' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR002 AS Plan_component_Id,
'N' AS Recoverable_flag,
CURRENCY_CODE AS Home_currency_code,
EXTN_ATTRIBUTE_NUMBER035 AS Home_currency_amount, -- MPA Adjusted
EXTN_ATTRIBUTE_CHAR020 AS Incentive_type,
EXTN_ATTRIBUTE_CHAR003 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS Period_ID,
EXTN_ATTRIBUTE_CHAR011 AS PrePayHub_Status,
OWNER_RESOURCE_ID,
EXTN_ATTRIBUTE_NUMBER002 AS PARTICIPANT_ID,
EXTN_ATTRIBUTE_CHAR001 AS PAYRUN_ID,
ID AS RECORD_ID,
(SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AS BU_ID,
EXTN_ATTRIBUTE_CHAR041 AS MPA_REASON_CODE
FROM ZCA_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
-- Parameterized filters (unchanged logic)
AND (
:p_badge IS NULL
OR TRIM(:p_badge) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR003)), '') = TRIM(:p_badge)
)
AND (
:P_INC_TYPE IS NULL
OR TRIM(:P_INC_TYPE) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR020)), '') = TRIM(:P_INC_TYPE)
)
AND (
:P_PP_STAT IS NULL
OR TRIM(:P_PP_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR011)), '') = TRIM(:P_PP_STAT)
)
AND (
:P_PAY_PERIOD IS NULL
OR TRIM(:P_PAY_PERIOD) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001)), '') = TRIM(:P_PAY_PERIOD)
)
AND (
:P_MPA_STAT IS NULL
OR TRIM(:P_MPA_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR031)), '') = TRIM(:P_MPA_STAT)
)
AND (
:P_PAYBATCH IS NULL
OR TRIM(:P_PAYBATCH) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR010)), '') = TRIM(:P_PAYBATCH)
)
UNION ALL
SELECT
RECORD_NUMBER AS Paysheet_ID,
EXTN_ATTRIBUTE_CHAR031 AS MPA_Status,
EXTN_ATTRIBUTE_CHAR002 AS Plan_component_Id,
EXTN_ATTRIBUTE_CHAR025 AS Recoverable_flag,
CURRENCY_CODE AS Home_currency_code,
EXTN_ATTRIBUTE_NUMBER019 AS Home_currency_amount,
EXTN_ATTRIBUTE_CHAR020 AS Incentive_type,
EXTN_ATTRIBUTE_CHAR003 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS Period_ID,
EXTN_ATTRIBUTE_CHAR011 AS PrePayHub_Status,
OWNER_RESOURCE_ID,
EXTN_ATTRIBUTE_NUMBER002 AS PARTICIPANT_ID,
EXTN_ATTRIBUTE_CHAR001 AS PAYRUN_ID,
ID AS RECORD_ID,
(SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AS BU_ID,
EXTN_ATTRIBUTE_CHAR041 AS MPA_REASON_CODE
FROM ZCA_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
-- same parameter filters
AND (
:p_badge IS NULL
OR TRIM(:p_badge) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR003)), '') = TRIM(:p_badge)
)
AND (
:P_INC_TYPE IS NULL
OR TRIM(:P_INC_TYPE) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR020)), '') = TRIM(:P_INC_TYPE)
)
AND (
:P_PP_STAT IS NULL
OR TRIM(:P_PP_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR011)), '') = TRIM(:P_PP_STAT)
)
AND (
:P_PAY_PERIOD IS NULL
OR TRIM(:P_PAY_PERIOD) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001)), '') = TRIM(:P_PAY_PERIOD)
)
AND (
:P_MPA_STAT IS NULL
OR TRIM(:P_MPA_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR031)), '') = TRIM(:P_MPA_STAT)
)
AND (
:P_PAYBATCH IS NULL
OR TRIM(:P_PAYBATCH) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR010)), '') = TRIM(:P_PAYBATCH)
)
UNION ALL
-- Manual MC Adjustment rows: created only when MC adjustment column exists and is not zero
SELECT
EXTN_ATTRIBUTE_CHAR032 AS Paysheet_ID,
'Manual Adjustment' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR002 AS Plan_component_Id,
'N' AS Recoverable_flag,
CURRENCY_CODE AS Home_currency_code,
EXTN_ATTRIBUTE_NUMBER035 AS Home_currency_amount, -- MC adjustment amount
EXTN_ATTRIBUTE_CHAR020 AS Incentive_type,
EXTN_ATTRIBUTE_CHAR003 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS Period_ID,
EXTN_ATTRIBUTE_CHAR011 AS PrePayHub_Status,
OWNER_RESOURCE_ID,
EXTN_ATTRIBUTE_NUMBER002 AS PARTICIPANT_ID,
EXTN_ATTRIBUTE_CHAR001 AS PAYRUN_ID,
ID AS RECORD_ID,
(SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AS BU_ID,
'MC_ADJUSTMENT' AS MPA_REASON_CODE
FROM ZCA_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER035, 0) <> 0 -- create new manual record only when MC Adjustment exists and not 0
-- keep same parameter filters to restrict to the same result set
AND (
:p_badge IS NULL
OR TRIM(:p_badge) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR003)), '') = TRIM(:p_badge)
)
AND (
:P_INC_TYPE IS NULL
OR TRIM(:P_INC_TYPE) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR020)), '') = TRIM(:P_INC_TYPE)
)
AND (
:P_PP_STAT IS NULL
OR TRIM(:P_PP_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR011)), '') = TRIM(:P_PP_STAT)
)
AND (
:P_PAY_PERIOD IS NULL
OR TRIM(:P_PAY_PERIOD) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001)), '') = TRIM(:P_PAY_PERIOD)
)
AND (
:P_MPA_STAT IS NULL
OR TRIM(:P_MPA_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR031)), '') = TRIM(:P_MPA_STAT)
)
AND (
:P_PAYBATCH IS NULL
OR TRIM(:P_PAYBATCH) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR010)), '') = TRIM(:P_PAYBATCH)
)
;
Comments
Post a Comment