Query
-- MPA Report with Manual MC Adjustment rows
SELECT
record_number as RecordNumber,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID, -- EXTN_ATTRIBUTE_CHAR032(dev6)
'Auto Approved' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c, --EXTN_ATTRIBUTE_CHAR002
'N' AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
EXTN_ATTRIBUTE_NUMBER037 AS AmountInLocalCurrency_c, -- MPA Adjusted--EXTN_ATTRIBUTE_NUMBER035
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,--EXTN_ATTRIBUTE_CHAR020
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id, --EXTN_ATTRIBUTE_CHAR003
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c, --EXTN_ATTRIBUTE_CHAR015
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
'MAV_DIR_MPA_CODE_1012' AS ReasonCodeLOV_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
and NVL(zre.EXTN_ATTRIBUTE_NUMBER037, 0) <> 0
and NVL(zre.EXTN_ATTRIBUTE_CHAR040, 'N') = 'Y' --need to check from Srini
-- Parameterized filters (unchanged logic)
AND (
:p_badge IS NULL
OR TRIM(:p_badge) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR002)), '') = TRIM(:p_badge)
) -- EXTN_ATTRIBUTE_CHAR003(dev6)
AND (
:P_INC_TYPE IS NULL
OR TRIM(:P_INC_TYPE) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR022)), '') = TRIM(:P_INC_TYPE)
) -- EXTN_ATTRIBUTE_CHAR020(dev6)
AND (
:P_PP_STAT IS NULL
OR TRIM(:P_PP_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR012)), '') = TRIM(:P_PP_STAT)
) --EXTN_ATTRIBUTE_CHAR011(dev6)
AND (
:P_PAY_PERIOD IS NULL
OR TRIM(:P_PAY_PERIOD) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001)), '') = TRIM(:P_PAY_PERIOD)
) -- EXTN_ATTRIBUTE_NUMBER001(dev6)
AND (
:P_MPA_STAT IS NULL
OR TRIM(:P_MPA_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR029)), '') = TRIM(:P_MPA_STAT) -- EXTN_ATTRIBUTE_CHAR031(dev6)
)
AND (
:P_PAYBATCH IS NULL
OR TRIM(:P_PAYBATCH) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR011)), '') = TRIM(:P_PAYBATCH)
) -- EXTN_ATTRIBUTE_CHAR010(dev6)
UNION ALL
SELECT
record_number as RecordNumber,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID, --- EXTN_ATTRIBUTE_CHAR032(dev6)
EXTN_ATTRIBUTE_CHAR029 AS MPA_Status, -- EXTN_ATTRIBUTE_CHAR031(dev6)
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c, -- EXTN_ATTRIBUTE_CHAR002
EXTN_ATTRIBUTE_CHAR013 AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
nvl(EXTN_ATTRIBUTE_NUMBER037,0) AS AmountInLocalCurrency_c,
EXTN_ATTRIBUTE_CHAR022 AS IncentiveTypeCode_c,
EXTN_ATTRIBUTE_CHAR002 AS Badge_Id,
EXTN_ATTRIBUTE_NUMBER001 AS PeriodId_c,
EXTN_ATTRIBUTE_CHAR005 AS ParticipantPayId_c,
EXTN_ATTRIBUTE_CHAR043 AS ParticipantPayIdMC,
OWNER_RESOURCE_ID OraZcxOwner_Id_c,
EXTN_ATTRIBUTE_NUMBER002 AS CreditedParticipantId_c,
EXTN_ATTRIBUTE_CHAR001 AS PayrunId_c,
ID AS RECORD_ID,
(SELECT bu_id FROM fun_all_business_units_v WHERE bu_name = 'ICM_DIRECT_INCTV') AS OrgId_c,
EXTN_ATTRIBUTE_CHAR023 AS ReasonCodeLOV_c -- EXTN_ATTRIBUTE_CHAR041 (dev6)
FROM SVC_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_CHAR002)), '') = TRIM(:p_badge)
)
AND (
:P_INC_TYPE IS NULL
OR TRIM(:P_INC_TYPE) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR022)), '') = TRIM(:P_INC_TYPE)
)
AND (
:P_PP_STAT IS NULL
OR TRIM(:P_PP_STAT) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR012)), '') = 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_CHAR029)), '') = TRIM(:P_MPA_STAT)
)
AND (
:P_PAYBATCH IS NULL
OR TRIM(:P_PAYBATCH) = ''
OR NVL(TRIM(TO_CHAR(zre.EXTN_ATTRIBUTE_CHAR011)), '') = TRIM(:P_PAYBATCH)
)
Comments
Post a Comment