q- parameter use to pass the parameter condition at next heirarchical level

 

OrganizationCode =28192 and StructureName='Primary' and ItemNumber='F6-TD-' and Component.EndDateTime =''

Note :In oracle Notes found that the q parameters filtering will not works at components level

So need to use filtering logic at the OIC level .

I have used a IF condition at OIC mapping level 

((string-length (nsmpr10:EndDateTime ) = 0) or (nsmpr10:EndDateTime > fn:current-dateTime()))

SQL to Get dates missing in a table for given date range

 WITH all_dates AS (

    SELECT DATE '2025-01-01' + LEVEL - 1 AS dt

    FROM dual

    CONNECT BY LEVEL <= (DATE '2026-01-01' - DATE '2025-01-01') + 1

),

existing_dates AS (

    SELECT TRUNC(creation_date) AS dt

    FROM  XXCUST.CUSTOM_TL -- your table name

    WHERE creation_date BETWEEN DATE '2025-01-01' AND DATE '2026-01-01'

)

SELECT a.dt AS missing_date

FROM all_dates a

LEFT JOIN existing_dates e

       ON a.dt = e.dt

WHERE e.dt IS NULL

ORDER BY a.dt;