select Opportunity__r.Country_of_Ownership__c,CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c)Months,CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c)years,
sum(Transaction_Allocation__c.Amount__c) amount,Opportunity__r.CurrencyIsoCode
from Transaction_Allocation__c
where
Transaction_Allocation__c.GL_Code__c IN ('50140','50180','50181','50270','50260','510141')
AND
Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged','Posted','Refunded','Failed','Refund','Reversal')
AND
Opportunity__r.RecordTypeId NOT IN ('012w00000006jGy','0122X000000ie1A')
AND
((Transaction_Allocation__c.Stage__c IN ('Posted','Refunded','Failed','Refund','Reversal')
AND
Transaction_Allocation__c.Close_Date__c > 2022-03-31 AND Transaction_Allocation__c.Close_Date__c < NEXT_N_DAYS:1
AND
(Opportunity__r.Pledge_Date__c > 2022-03-31
OR
Opportunity__r.Pledge_Date__c =null))
OR
(Transaction_Allocation__c.Stage__c IN ('Ask Made', 'Identify','Research','Plan','Engage','Prepare Ask','Pledged')
AND
Opportunity__r.Pledge_Date__c > 2022-03-31))
Group by
Opportunity__r.Country_of_Ownership__c, CALENDAR_YEAR(Transaction_Allocation__c.Close_Date__c),CALENDAR_MONTH(Transaction_Allocation__c.Close_Date__c),Opportunity__r.CurrencyIsoCode
Comments