I have the following AND condition below that I will be using as a filter in GL_BALANCES table
The goal is that if the parameter :P_ACCOUNTING_PERIOD is defaulted to ALL in the Data Model it will passed NULL value. Once NULL value was passed, it will then instead get the previous month's period name which will be very useful for scheduling monthly.
However, when I try to pass ALL as parameter to :P_ACCOUNTING_PERIOD error was encountered
"ORA-01843: not a valid month"
Another thing, if the :P_ACCOUNTING_PERIOD was specified to have multiple period for example (Oct-21,Nov-21) it will be captured by the OR condition and it will then allow the query to select multiple periods in the GL_BALANCES table.
However, when I try to pass Sep-21,Oct-21 as parameter to :P_ACCOUNTING_PERIOD error was also encountered
"ORA-00939: too many arguments for function"
Would be grateful if someone can assist on this?
FYI: No error was encountered when this condition was used
(COALESCE(null,:p_Accounting_Period) is null OR gb.period_name in (:p_Accounting_Period))
However, the function to pull the previous month will not be available which I need for this condition.
Thank you.
-------------------------------------------------
and ((COALESCE(null,:p_Accounting_Period) is null AND gb.period_name =
CASE To_char(trunc(add_months(sysdate, -1), 'month'), 'MM')
WHEN '01' THEN 'Jan'
WHEN '02' THEN 'Feb'
WHEN '03' THEN 'Mar'
WHEN '04' THEN 'Apr'
WHEN '05' THEN 'May'
WHEN '06' THEN 'Jun'
WHEN '07' THEN 'Jul'
WHEN '08' THEN 'Aug'
WHEN '09' THEN 'Sep'
WHEN '10' THEN 'Oct'
WHEN '11' THEN 'Nov'
WHEN '12' THEN 'Dec'
END
|| '-'
|| To_char(trunc(add_months(sysdate, -1), 'month'), 'RR')) OR gb.period_name in (:p_Accounting_Period))
-------------------------------------------------------------------------------------------