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

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika