Using dynamic date filter causes data not to return
Okay, figured it out, leaving this here in case anyone else comes across a similar issue.

For some reason Oracle will not compare a DATE data type to a SYSDATE when it has been place through a function such as:

SYSDATE - (TO_CHAR(SYSDATE,'d')-1)


To overcome this limitation, I used the NEXT_DAY() and TO_DATE() functions to build the following query:

TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')


Using this method, the below filter returns a result set, and has a low cardinality only slightly higher than a static filter.

      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-7,'Monday'),'DD/MON/YY')
  OR 
      PIP.DTDATE = TO_DATE(NEXT_DAY(SYSDATE-371,'Monday'),'DD/MON/YY')


If anyone has any comments about WHY oracle requires you to use TO_DATE(), please extrapolate, because this confused the daylights outta me XD