MPA query

-- Report MPA Report Enhancement Hight level - Report is to be called by integration to pull data from Prepaid Hub " Where payment batch status = Unpaid" and add a new line for Manual adjustment for adjustments happening due to Multi Currency scenarios. Condition - New record to be created whenever column MC Adjustment is not Null and not equal to 0. -- MPA Report Enhancement Hight level Report is to be called by integration to pull data from Prepaid Hub " Where payment batch status = Unpaid" and add a new line for Manual adjustment for adjustments happening due to Multi Currency scenarios. Condition to Create new Manual Record - New record to be created whenever column MC Adjustment is not Null and not equal to 0. Technical One sql query will create the manual adjustment record for Actual MPA We will have one more manual adjustment record for MPA requried because of Multi Currency adjustment. BOth queries will work based on Union ALl. Open Question Parameters to be used to call the report and complete end to end cycle. SELECT EXTN_ATTRIBUTE_CHAR032 Paysheet_ID , 'Auto Approved' MPA_Status , EXTN_ATTRIBUTE_CHAR002 Plan_component_Id , 'N' Recoverable_flag , CURRENCY_CODE Home_currency_code , EXTN_ATTRIBUTE_NUMBER035 Home_currency_amount --MPA Adjusted , EXTN_ATTRIBUTE_CHAR020 Incentive_type , EXTN_ATTRIBUTE_CHAR003 Badge_Id , EXTN_ATTRIBUTE_NUMBER001 Period_ID , EXTN_ATTRIBUTE_CHAR011 PrePayHub_Status , OWNER_RESOURCE_ID , EXTN_ATTRIBUTE_NUMBER002 PARTICIPANT_ID , EXTN_ATTRIBUTE_CHAR001 PAYRUN_ID , ID RECORD_ID , (select bu_id from fun_all_business_units_v where bu_name = 'ICM_DIRECT_INCTV') BU_ID , EXTN_ATTRIBUTE_CHAR041 MPA_REASON_CODE FROM ZCA_REF_ENTITIES zre where zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c' AND ( zre.EXTN_ATTRIBUTE_NUMBER035 is not null OR EXTN_ATTRIBUTE_NUMBER035 <> 0 ) 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 Paysheet_ID ,EXTN_ATTRIBUTE_CHAR031 MPA_Status , EXTN_ATTRIBUTE_CHAR002 Plan_component_Id , EXTN_ATTRIBUTE_CHAR025 Recoverable_flag , CURRENCY_CODE Home_currency_code , EXTN_ATTRIBUTE_NUMBER019 Home_currency_amount , EXTN_ATTRIBUTE_CHAR020 Incentive_type , EXTN_ATTRIBUTE_CHAR003 Badge_Id , EXTN_ATTRIBUTE_NUMBER001 Period_ID , EXTN_ATTRIBUTE_CHAR011 PrePayHub_Status , OWNER_RESOURCE_ID , EXTN_ATTRIBUTE_NUMBER002 PARTICIPANT_ID , EXTN_ATTRIBUTE_CHAR001 PAYRUN_ID , ID RECORD_ID , (select bu_id from fun_all_business_units_v where bu_name = 'ICM_DIRECT_INCTV') BU_ID , EXTN_ATTRIBUTE_CHAR041 MPA_REASON_CODE FROM ZCA_REF_ENTITIES zre where zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c' 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) ) == /* Payment batch status - Unpad payment pbatch name - Pay period id - */

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika