case as
/* ======================================================
MPA Report – Fusion SaaS BIP (LOV Multi-Select Safe)
====================================================== */
SELECT
record_number || '-MC' AS RecordNumber,
record_number || '-MC' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
'Auto Approved' AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
'N' AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
EXTN_ATTRIBUTE_NUMBER037 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,
'MAV_DIR_MPA_CODE_1012' AS ReasonCodeLOV_c,
EXTN_ATTRIBUTE_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER037,0) <> 0
/* ===== LOV Multi-Select Filters ===== */
AND case
when :p_badge is null then
zre.EXTN_ATTRIBUTE_CHAR002 = zre.EXTN_ATTRIBUTE_CHAR002
else zre.EXTN_ATTRIBUTE_CHAR002 in (:p_badge)
AND zre.EXTN_ATTRIBUTE_CHAR022 IN (:P_INC_TYPE)
AND to_char(zre.EXTN_ATTRIBUTE_NUMBER001) IN (:P_PAY_PERIOD)
AND zre.EXTN_ATTRIBUTE_CHAR029 IN (:P_MPA_STAT)
AND zre.EXTN_ATTRIBUTE_CHAR011 IN (:P_PAYBATCH)
UNION ALL
SELECT
record_number || '-MPA' AS RecordNumber,
record_number || '-MPA' AS RecordName,
EXTN_ATTRIBUTE_CHAR039 AS Paysheet_ID,
EXTN_ATTRIBUTE_CHAR029 AS MPA_Status,
EXTN_ATTRIBUTE_CHAR004 AS PlanComponentId_c,
EXTN_ATTRIBUTE_CHAR013 AS Recoverable_c,
CURRENCY_CODE AS HomeCurrencyCode_c,
NVL(EXTN_ATTRIBUTE_NUMBER023,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_CHAR011 AS PaymentBatch_c,
EXTN_ATTRIBUTE_CHAR029 AS MPAStatus_c
FROM SVC_REF_ENTITIES zre
WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c'
AND NVL(zre.EXTN_ATTRIBUTE_NUMBER023,0) <> 0
/* ===== SAME FILTERS AGAIN ===== */
AND zre.EXTN_ATTRIBUTE_CHAR002 IN (:p_badge)
AND zre.EXTN_ATTRIBUTE_CHAR022 IN (:P_INC_TYPE)
AND to_char(zre.EXTN_ATTRIBUTE_NUMBER001) IN (:P_PAY_PERIOD)
AND zre.EXTN_ATTRIBUTE_CHAR029 IN (:P_MPA_STAT)
AND zre.EXTN_ATTRIBUTE_CHAR011 IN (:P_PAYBATCH)
Comments
Post a Comment