How to generate point in time snapshot table from a transaction fact table?

I haven't done Oracle for a while but you're after two components:

1. The current snapshot
2. A fixed historical snapshot

This generates a snapshot for a given hardcoded date. I don't have Oracle to check how variables work so you'll have to do the date variable part yourself.

Note:

• I'm assuming that a Cust_ID can only have one state at a time
• real world data is more complicated than this and there are always edge cases
• If there is no current state for the Cust, there will be no row
• Just noticed you have overlapping dates. That is a problem because a customer is in two states at the same time

You could join to a calendar table to run this for all dates but that can be very performance intensive, and ongoing you usually just want to generate every day to add to an existing table.

Below is the code copied from a fiddle

Setup code

CREATE TABLE t
    (FactID int, Cust_ID int, Status varchar2(1), EffectiveDate DATE, EndDate DATE)
;

INSERT ALL 
    INTO t (FactID, Cust_ID, Status, EffectiveDate, EndDate)
         VALUES (1, 1, 'A', TIMESTAMP'2021-05-20 08:52:29.000', TIMESTAMP'2021-05-21 03:08:22.000')
    INTO t (FactID, Cust_ID, Status, EffectiveDate, EndDate)
         VALUES (2, 1, 'B', TIMESTAMP'2021-05-21 03:08:22.000', TIMESTAMP'2021-05-24 02:47:28.000')
    INTO t (FactID, Cust_ID, Status, EffectiveDate, EndDate)
         VALUES (3, 1, 'C', TIMESTAMP'2021-05-24 02:47:28.000', TIMESTAMP'2021-05-24 04:15:45.000')
    INTO t (FactID, Cust_ID, Status, EffectiveDate, EndDate)
         VALUES (4, 1, 'A', TIMESTAMP'2021-05-24 04:15:45.000', TIMESTAMP'2021-05-24 08:05:09.000')
    INTO t (FactID, Cust_ID, Status, EffectiveDate, EndDate)
         VALUES (5, 1, 'D', TIMESTAMP'2021-05-24 08:05:09.000', TIMESTAMP'9000-12-31 00:00:00.000')         

SELECT * FROM dual
;

Query

SELECT
T.Cust_ID, DATE '2021-05-25' ReportDate, T.Status, T.EffectiveDate,T.EndDate,
H.A_SDATE, H.A_EDATE, H.B_SDATE, H.B_EDATE, H.C_SDATE, H.C_EDATE
FROM
(
    -- Todays snapshot
    SELECT Cust_ID,Status, EffectiveDate,EndDate
    FROM t 
    WHERE DATE '2021-05-25' BETWEEN EffectiveDate AND EndDate 
) T
LEFT OUTER JOIN
(
-- Static capture of all states
    SELECT Cust_ID, 
    MIN(CASE WHEN Status = 'A' THEN EffectiveDate ELSE NULL END) A_SDATE, 
    MAX(CASE WHEN Status = 'A' THEN LEAST(DATE '2021-07-10',EndDate) ELSE NULL END) A_EDATE,
    MIN(CASE WHEN Status = 'B' THEN EffectiveDate ELSE NULL END) B_SDATE, 
    MAX(CASE WHEN Status = 'B' THEN LEAST(DATE '2021-05-25',EndDate) ELSE NULL END) B_EDATE,
    MIN(CASE WHEN Status = 'C' THEN EffectiveDate ELSE NULL END) C_SDATE, 
    MAX(CASE WHEN Status = 'C' THEN LEAST(DATE '2021-05-25',EndDate) ELSE NULL END) C_EDATE

    FROM t 
    -- Exclude state changes after the process date
    WHERE EffectiveDate < DATE '2021-05-25'
    GROUP BY Cust_ID
) H
ON T.Cust_ID = H.Cust_ID

First of all my sincere thanks to all the people who tried to help me. I somehow managed to nail this near-impossible task with some convoluted logic (but it works). I tried providing inline comments to explain the derivations. A special mention to @Wernfried Domscheit who has written the PIVOT logic and deleted the answer and it helped me to a great extent.

WITH
    date_ranges
-- Generate dates 
    AS
        (SELECT ROWNUM, TO_DATE ('21-05-2021', 'dd-mm-yyyy') + ROWNUM - 1.00001 reportdate
           FROM all_objects
          WHERE ROWNUM <= 6),
-- Mock up source records
    transactions (factid, cust_id,status,effectivedate,enddate)
    AS
        (SELECT 1,1,'A',
                TO_DATE ('20/05/2021 8:52:29 PM', 'DD/MM/YYYY HH12:MI:SS AM'),
                TO_DATE ('21/05/2021 3:08:22 PM', 'DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 2,1,'B',
                TO_DATE ('21/05/2021 3:08:22 PM', 'DD/MM/YYYY HH12:MI:SS AM'),
                TO_DATE ('24/05/2021 2:47:28 PM', 'DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 3,1,'C',
                TO_DATE ('24/05/2021 2:47:28 PM', 'DD/MM/YYYY HH12:MI:SS AM'),
                TO_DATE ('24/05/2021 4:15:45 PM', 'DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 4,1,'A',
                TO_DATE ('24/05/2021 4:15:45 PM', 'DD/MM/YYYY HH12:MI:SS AM'),
                TO_DATE ('24/05/2021 8:05:09 PM', 'DD/MM/YYYY HH12:MI:SS AM') FROM DUAL
         UNION ALL
         SELECT 5,1,'D',
                TO_DATE ('24/05/2021 8:05:09 PM', 'DD/MM/YYYY HH12:MI:SS AM'),
                TO_DATE ('31/12/9000', 'DD/MM/YYYY') FROM DUAL),
    dataset
-- Apply cross join to get report date into transactions
-- Could've been much better; time crunched
    AS
        (SELECT DISTINCT reportdate,cust_id,status     AS eodstatus,effectivedate,enddate
           FROM transactions CROSS JOIN date_ranges),
    dataset1
-- Ignore start and end dates if they are older than the reporting date
    AS
        (  SELECT reportdate,
                  cust_id,
                  eodstatus,
                  CASE
                      WHEN reportdate > effectivedate THEN effectivedate
                      ELSE TO_DATE ('31/12/9000', 'DD/MM/YYYY')
                  END    AS effectivedate,
                  CASE
                      WHEN reportdate > enddate THEN enddate
                      ELSE TO_DATE ('31/12/9000', 'DD/MM/YYYY')
                  END    AS enddate
             FROM dataset
            WHERE reportdate > effectivedate),
    dataset2
-- Grab the min of start and max of end for all reporting days
    AS
        (  SELECT reportdate,
                  cust_id,
                  eodstatus,
                  eodstatus               AS status,
                  MIN (effectivedate)     effectivedate,
                  MAX (enddate)           enddate
             FROM dataset1
         GROUP BY reportdate, cust_id, eodstatus),
    dataset_new
-- Apply PIVOT to capture the start and end date per known statues and replacing NULLs with high open end dates
    AS
        (  SELECT reportdate,
                  cust_id,
                  eodstatus,
                  COALESCE ('A','B','C','D')                           AS status,
                  NVL (a_sdate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    a_sdate,
                  NVL (a_edate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    a_edate,
                  NVL (b_sdate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    b_sdate,
                  NVL (b_edate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    b_edate,
                  NVL (c_sdate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    c_sdate,
                  NVL (c_edate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    c_edate,
                  NVL (d_sdate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    d_sdate,
                  NVL (d_edate, TO_DATE ('31/12/9000', 'DD/MM/YYYY'))    d_edate
             FROM dataset2
                  PIVOT (MIN (effectivedate) AS "SDATE", MAX (enddate) AS "EDATE"
                        FOR status
                        IN ('A' AS "A", 'B' AS "B", 'C' AS "C", 'D' AS "D"))
         ORDER BY reportdate),
    date_manipulations
-- Merging multiple entries into one record a day
    AS
        (  SELECT reportdate,
                  cust_id,
                  MIN (a_sdate)     a_sdate,
                  MIN (a_edate)     a_edate,
                  MIN (b_sdate)     b_sdate,
                  MIN (b_edate)     b_edate,
                  MIN (c_sdate)     c_sdate,
                  MIN (c_edate)     c_edate,
                  MIN (d_sdate)     d_sdate,
                  MIN (d_edate)     d_edate
             FROM dataset_new
         GROUP BY reportdate, cust_id
         ORDER BY 1)
-- JOIN with transaction to report the original status 
SELECT a.*, b.status
  FROM date_manipulations a JOIN transactions b ON reportdate BETWEEN effectivedate AND enddate;