Today's fun Saved Search sql:
```case when {revers...
# suiteanalyticsk
KevinJ of Kansas
07/06/2020, 4:26 PMToday's fun Saved Search sql:
Copy code
case when {reversaldate} is null
then case when decode({accounttype},'Operating Revenue',{amount},'Non-Operating Revenue',{amount},'Non-Operating Expense',-{amount},'Operating Expense',-{amount},'Cost Of Goods Sold',-{amount},0) = nvl({custcol_bpc_pending_po_plus_actuals},0)
then 1 else 0 end else
case when trunc(add_months({reversaldate},3),'YYYY') = trunc(add_months({trandate},3),'YYYY')
then
case when 0 = nvl({custcol_bpc_pending_po_plus_actuals},0)
then 1 else 0 end
else case when decode({accounttype},'Operating Revenue',-{amount},'Non-Operating Revenue',-{amount},'Non-Operating Expense',{amount},'Operating Expense',{amount},'Cost Of Goods Sold',{amount},0) = nvl({custcol_bpc_pending_po_plus_actuals},0)
then 1 else 0 end end end👍 1
party furby 1
g
George McMullen
07/06/2020, 9:06 PMIn:
trunc(add_months({reversaldate},3),'YYYY') = trunc(add_months({trandate},3),'YYYY')George McMullen
07/06/2020, 9:07 PMYou're doing the same add_months to both reversaldate and trandate. Probably don't need to do that, just need to do the trunc to see if they occur in the same year.
George McMullen
07/06/2020, 9:08 PMUnless one of them is not supposed to have the add_months() on it.
k
KevinJ of Kansas
07/06/2020, 9:28 PMThey both need the add months - client is on an off fiscal year calendar.
KevinJ of Kansas
07/06/2020, 9:29 PMThe test is to determine if the reversal and the transaction are in the same fiscal year.
👍 1
3 Views