Will your function have any parameters?

Rather than posting links, which some people (including me) might choose not to open, can you post using text (for DDL and data) and images (showing desired results).

Hyperbole.

But, c'mon man,...

I think you need to ask this question in an Oracle forum, as it makes no sense to me.

In SQL Server terms, I think you'll agree that it's very clear:

"The...

Can you also post DDL and sample INSERT statements (if any) for tbl_probate_case?

50 feet? Is your browser set to Mr Magoo mode?

People will probably ask you to post execution plans (ideally for slow and fast executions) in order to be able to help you.

Here is that query again, in human-readable form:

By 'generated', do you mean 'written'?

Well you could just check the number results in the return and if it's zero hardcode the desired output.

Json is tricky like that because functionally both of those...

Automated e-mails can be generated from SQL Agent jobs, SSIS packages and using various other technologies.

I assume that's not the answer you're after, but it does answer your question, bearing...

Well you could just check the number results in the return and if it's zero hardcode the desired output.

Json is tricky like that because functionally both of those represent...

I can't see a question in your post. Are you blogging?

The code looks broadly compliant to me, though with that many cascading CTEs I would expect poor performance.

This part looks like it will cause issues:

CREATE TABLE probateL1.tmpTblLegalProbateGrantCase
WITH
(
DISTRIBUTION...

solved!

1st friday : (SELECT DATEADD(DAY, DECODE(EXTRACT(DOW FROM START_DATE), 0,5, 1,4, 2,3, 3,2 ,4,1, 6,6, 0), START_DATE) third_friday FROM (SELECT date_trunc('month',current_date) START_DATE))

2nd friday : (SELECT DATEADD(DAY, 7 + DECODE(EXTRACT(DOW FROM START_DATE), 0,5,...

Nothing in the code you posted shows how the proc is called, how the connection is opened or how the connection is closed. It may be helpful to see that.