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
Post a Comment