MPA report

-- MPA Report with Manual MC Adjustment rows 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 PaymentBatch_c, EXTN_ATTRIBUTE_CHAR029 MPAStatus_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' -- Multi-value and Null handling AND (NVL(:p_badge, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR002 IN (:p_badge)) AND (NVL(:P_INC_TYPE, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR022 IN (:P_INC_TYPE)) --AND (NVL(:P_PP_STAT, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR012 IN (:P_PP_STAT)) AND (NVL(:P_PAY_PERIOD, 'NULL') = 'NULL' OR TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001) IN (:P_PAY_PERIOD)) AND (NVL(:P_MPA_STAT, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR029 IN (:P_MPA_STAT)) AND (NVL(:P_PAYBATCH, 'NULL') = 'NULL' OR 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 PaymentBatch_c, EXTN_ATTRIBUTE_CHAR029 MPAStatus_c FROM SVC_REF_ENTITIES zre WHERE zre.ATTRIBUTE_CATEGORY = 'MTQ_ICM_PPH_OBJ_c' AND NVL(EXTN_ATTRIBUTE_NUMBER023, 0) <> 0 -- Multi-value and Null handling AND (NVL(:p_badge, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR002 IN (:p_badge)) AND (NVL(:P_INC_TYPE, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR022 IN (:P_INC_TYPE)) --AND (NVL(:P_PP_STAT, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR012 IN (:P_PP_STAT)) AND (NVL(:P_PAY_PERIOD, 'NULL') = 'NULL' OR TO_CHAR(zre.EXTN_ATTRIBUTE_NUMBER001) IN (:P_PAY_PERIOD)) AND (NVL(:P_MPA_STAT, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR029 IN (:P_MPA_STAT)) AND (NVL(:P_PAYBATCH, 'NULL') = 'NULL' OR zre.EXTN_ATTRIBUTE_CHAR011 IN (:P_PAYBATCH))

Comments

Popular posts from this blog

dev7 from eror

EPA

Unified Currency Report- Krithika