query

SELECT everything.id,
       actual_event_id,
       expected_event_id,
       everything.policy_reference,
       provider_order_number,
       requirement_name,
       status_event_type_code,
       status_event_name,
       status_event_detail,
       flex_status,
       everything.tracking,
       status_event_at,
       late_at,
       requested_schedule_at,
       everything.created_at,
       expected_event_flex_type,
       actual_event_flex_type,
       product_name
FROM (SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Credit Card Failure Day ' || 4
       AS status_event_name,
       name || ', '::text || media_name || ' '::text || cc || ' exp '::text || exp || ', Payment Amt: '::text || amount AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       trans_at::timestamp AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN trust_commerce_payment_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE count = '4'

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Credit Card Failure Day ' || 6
       AS status_event_name,
       name || ', '::text || media_name || ' '::text || cc || ' exp '::text || exp || ', Payment Amt: '::text || amount AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       trans_at::timestamp AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN trust_commerce_payment_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE count = '6'

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Credit Card Failure Day ' || 10
       AS status_event_name,
       name || ', '::text || media_name || ' '::text || cc || ' exp '::text || exp || ', Payment Amt: '::text || amount AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       trans_at::timestamp AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN trust_commerce_payment_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE count = '10'

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Credit Card Failure Day ' || 15
       AS status_event_name,
       name || ', '::text || media_name || ' '::text || cc || ' exp '::text || exp || ', Payment Amt: '::text || amount AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       trans_at::timestamp AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN trust_commerce_payment_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE count = '15'

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Credit Card Failure Day ' || 20
       AS status_event_name,
       name || ', '::text || media_name || ' '::text || cc || ' exp '::text || exp || ', Payment Amt: '::text || amount AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       trans_at::timestamp AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN trust_commerce_payment_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE count = '20'

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'EFT Payment Failure' AS status_event_name,
       CASE WHEN payment_mode <> previous_payment_mode
            THEN 'Changed from: '::text || previous_payment_mode || ', Payment Amt: '::text || previous_payment_amount
            ELSE 'Payment Amt: '::text || previous_payment_amount
       END AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       transaction_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN eft_payment_failure_actual_events AS e ON a.id = e.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Payment'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Final Credit Card Failure' AS status_event_name,
       CASE WHEN payment_mode <> previous_payment_mode
            THEN 'Changed from: '::text || previous_payment_mode || ', Payment Amt: '::text || previous_payment_amount
            ELSE 'Payment Amt: '::text || previous_payment_amount
       END AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       transaction_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN final_credit_card_failure_actual_events AS c ON a.id = c.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference

 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'New Application'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Submitted to New Business' AS status_event_name,
       ''::text AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       flash_timestamp_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN flash_app_submitted_actual_events AS f ON a.id = f.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference

 UNION
SELECT id,
       actual_event_id,
       expected_event_id,
       policy_reference,
       provider_order_number,
       requirement_name,
       status_event_type_code,
       status_event_name,
       status_event_detail,
       flex_status,
       tracking,
       status_event_at,
       late_at,
       requested_schedule_at,
       created_at,
       expected_event_flex_type,
       actual_event_flex_type,
       product_name
FROM (/* select the expected events that are Pending but not Leftover */
      SELECT e.id,
             NULL::integer AS actual_event_id,
             e.id AS expected_event_id,
             e.policy_reference,
             e.provider_order_number,
             CASE WHEN e.flex_type = 'FLEX::Schedule::Events::ImagesReceivedExpected' THEN 'Agent Ordered Paramed'
             ELSE requirement_type_code_to_name(e.requirement_type_code)
             END AS requirement_name,
             NULL AS status_event_type_code,
             d.description AS status_event_name,
             NULL AS status_event_detail,
             CASE WHEN e.late_at IS NOT NULL THEN 'Late'
             ELSE 'Pending'
             END AS flex_status,
             p.tracking,
             e.expected_at AS status_event_at,
             e.late_at,
             e.requested_schedule_at,
             e.created_at,
             e.flex_type AS expected_event_flex_type,
             NULL AS actual_event_flex_type,
             COALESCE(p.product_name, '') AS product_name
      FROM expected_events AS e
      INNER JOIN expected_event_descriptions AS d ON e.flex_type = d.flex_type
      LEFT OUTER JOIN policies AS p ON p.policy_reference = e.policy_reference
      WHERE e.clearing_event_id IS NULL
        AND e.fulfilling_event_id IS NULL
        AND e.leftover_marking_event_id IS NULL
      UNION
      /* select the exam one actual events that are neither clearing nor fulfilling and are not OrderConfirmed */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             e.provider_order_number,
             requirement_type_code_to_name(e.requirement_type_code) AS requirement_name,
             e.status_event_type_code,
             r.status_event_name,
             e.status_event_detail,
             a.flex_type AS flex_status,
             p.tracking,
             e.status_event_at,
             NULL AS late_at,
             e.requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN exam_one_actual_events AS e ON e.id = a.id
      INNER JOIN rules AS r ON r.actual_event_class = a.flex_type
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE a.id NOT IN (SELECT DISTINCT clearing_event_id
                         FROM expected_events
                         WHERE clearing_event_id IS NOT NULL
                         UNION
                         SELECT DISTINCT fulfilling_event_id
                         FROM expected_events
                         WHERE fulfilling_event_id IS NOT NULL)
        AND a.flex_type <> 'FLEX::Events::OrderConfirmed'
      UNION
      /* select the OrderConfirmed events that have a peer that is fulfilling */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             e.provider_order_number,
             requirement_type_code_to_name(e.requirement_type_code) AS requirement_name,
             e.status_event_type_code,
             r.status_event_name,
             e.status_event_detail,
             'Fulfilled' AS flex_status,
             p.tracking,
             e.status_event_at,
             NULL AS late_at,
             e.requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN exam_one_actual_events AS e ON e.id = a.id
      INNER JOIN rules AS r ON r.actual_event_class = a.flex_type
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE a.id NOT IN (SELECT DISTINCT clearing_event_id
                         FROM expected_events
                         WHERE clearing_event_id IS NOT NULL
                         UNION
                         SELECT DISTINCT fulfilling_event_id
                         FROM expected_events
                         WHERE fulfilling_event_id IS NOT NULL)
        AND a.flex_type = 'FLEX::Events::OrderConfirmed'
        AND EXISTS (SELECT ex.id
                    FROM expected_events AS ex
                    INNER JOIN actual_events AS a2 ON a2.id = ex.fulfilling_event_id
                    INNER JOIN exam_one_actual_events AS e2 ON a2.id = e2.id
                    WHERE ex.policy_reference = a.policy_reference
                      AND a2.flex_type = 'FLEX::Events::OrderConfirmed'
                      AND ex.flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'
                      AND ex.flex_status = 'Fulfilled'
                      AND ex.requirement_type_code <> e.requirement_type_code
                      AND ex.realtime_app_requirement_id = e.realtime_app_requirement_id)
      UNION
      /* select the OrderConfirmed events that do not have a peer that is fulfilling */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             e.provider_order_number,
             requirement_type_code_to_name(e.requirement_type_code) AS requirement_name,
             e.status_event_type_code,
             r.status_event_name,
             e.status_event_detail,
             '' AS flex_status,
             p.tracking,
             e.status_event_at,
             NULL AS late_at,
             e.requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN exam_one_actual_events AS e ON e.id = a.id
      INNER JOIN rules AS r ON r.actual_event_class = a.flex_type
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE a.id NOT IN (SELECT DISTINCT clearing_event_id
                         FROM expected_events
                         WHERE clearing_event_id IS NOT NULL
                         UNION
                         SELECT DISTINCT fulfilling_event_id
                         FROM expected_events
                         WHERE fulfilling_event_id IS NOT NULL)
        AND a.flex_type = 'FLEX::Events::OrderConfirmed'
        AND NOT EXISTS (SELECT ex.id
                        FROM expected_events AS ex
                        INNER JOIN actual_events AS a2 ON a2.id = ex.fulfilling_event_id
                        INNER JOIN exam_one_actual_events AS e2 ON a2.id = e2.id
                        WHERE ex.policy_reference = a.policy_reference
                          AND a2.flex_type = 'FLEX::Events::OrderConfirmed'
                          AND ex.flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'
                          AND ex.flex_status = 'Fulfilled'
                          AND ex.requirement_type_code <> e.requirement_type_code
                          AND ex.realtime_app_requirement_id = e.realtime_app_requirement_id)
      UNION
      /* select the exam-related realtime actual events that are neither clearing nor fulfilling */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             NULL AS provider_order_number,
             'Agent Ordered Paramed' AS requirement_name,
             '' AS status_event_type_code,
             CASE WHEN a.flex_type = 'FLEX::Events::ImagesReceived' THEN 'Images Received in RT'
             WHEN a.flex_type = 'FLEX::Events::ExamRequirementDeleted' THEN 'Exam Requirement Deleted'
             ELSE 'Exam Requirement Added'
             END AS status_event_name,
             NULL AS status_event_detail,
             a.flex_type AS flex_status,
             p.tracking,
             r.last_updated_at AS status_event_at,
             NULL AS late_at,
             NULL AS requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN realtime_actual_events AS r ON r.id = a.id
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE r.event_category = 'Exam Related'
        AND a.id NOT IN (SELECT DISTINCT clearing_event_id
                         FROM expected_events
                         WHERE clearing_event_id IS NOT NULL
                         UNION
                         SELECT DISTINCT fulfilling_event_id
                         FROM expected_events
                         WHERE fulfilling_event_id IS NOT NULL)
      UNION
      /* select the expected events inner joined with related Exam One clearing or fulfilling events */
      SELECT a.id,
             a.id AS actual_event_id,
             e.id AS expected_event_id,
             a.policy_reference,
             o.provider_order_number,
             requirement_type_code_to_name(o.requirement_type_code) AS requirement_name,
             o.status_event_type_code,
             r.status_event_name,
             o.status_event_detail,
             CAST(e.flex_status AS TEXT),
             p.tracking,
             o.status_event_at,
             e.late_at,
             o.requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM expected_events AS e
      INNER JOIN actual_events AS a ON (a.id = e.clearing_event_id OR a.id = e.fulfilling_event_id)
      INNER JOIN exam_one_actual_events AS o ON o.id = a.id
      INNER JOIN rules AS r ON r.actual_event_class = a.flex_type
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      UNION
      /* select Realtime ImagesReceived events that have fulfilled one or more expected events  */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             ( SELECT DISTINCT e.provider_order_number
               FROM expected_events AS e
               WHERE e.fulfilling_event_id = a.id
               LIMIT 1 ) AS provider_order_number,
             'Agent Ordered Paramed' AS requirement_name,
             NULL AS status_event_type_code,
             'Images Received in RT' AS status_event_name,
             NULL AS status_event_detail,
             'Fulfilled' AS flex_status,
             p.tracking,
             r.last_updated_at AS status_event_at,
             NULL AS late_at,    /* Current query does not detect late_at -- might have multiple values */
             NULL AS requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN realtime_actual_events AS r ON r.id = a.id
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE a.flex_type = 'FLEX::Events::ImagesReceived'
        AND a.id = ( SELECT DISTINCT fulfilling_event_id
                     FROM expected_events
                     WHERE flex_type = 'FLEX::Schedule::Events::ImagesReceivedExpected'
                       AND flex_status = 'Fulfilled'
                       AND policy_reference = a.policy_reference
                       AND realtime_app_requirement_id = r.app_requirement_id )
      UNION
      /* select Realtime ExamRequirementDeleted events that have cleared an OrderConfirmedExpected  */
      SELECT a.id,
             a.id AS actual_event_id,
             NULL AS expected_event_id,
             a.policy_reference,
             NULL AS provider_order_number,
             'Agent Ordered Paramed' AS requirement_name,
             NULL AS status_event_type_code,
             'Exam Requirement Deleted' AS status_event_name,
             NULL AS status_event_detail,
             NULL AS flex_status,
             p.tracking,
             r.last_updated_at AS status_event_at,
             NULL AS late_at,    /* Current query does not detect late_at -- might have multiple values */
             NULL AS requested_schedule_at,
             a.created_at,
             NULL AS expected_event_flex_type,
             a.flex_type AS actual_event_flex_type,
             COALESCE(p.product_name, a.product) AS product_name
      FROM actual_events AS a
      INNER JOIN realtime_actual_events AS r ON r.id = a.id
      LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
      WHERE a.flex_type = 'FLEX::Events::ExamRequirementDeleted'
        AND a.id = ( SELECT DISTINCT clearing_event_id
                     FROM expected_events
                     WHERE flex_type = 'FLEX::Schedule::Events::OrderConfirmedExpected'
                       AND flex_status = 'Cleared'
                       AND policy_reference = a.policy_reference
                       AND realtime_app_requirement_id = r.app_requirement_id ))
AS exam_events


 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Underwriting'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       pps.detail AS status_event_name,
       NULL::text AS status_event_detail,
       CASE WHEN pps.is_update = true
       THEN 'Updated'
       ELSE NULL::text
       END AS flex_status,
       p.tracking,
       pps.status_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN pending_policy_status_actual_events AS pps ON a.id = pps.id
LEFT OUTER JOIN policies AS p ON a.policy_reference = p.policy_reference


 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       CASE WHEN a.flex_type = 'FLEX::Events::AttendingPhysicianStatementRequested' THEN 'Underwriting'
            WHEN a.flex_type = 'FLEX::Events::AttendingPhysicianStatementReviewed'  THEN 'Underwriting'
            WHEN a.flex_type = 'FLEX::Events::SpecialAuthorizationRequested'  THEN 'Underwriting'
            WHEN a.flex_type = 'FLEX::Events::SpecialAuthorizationReviewed'  THEN 'Underwriting'
            ELSE 'Activation'
       END AS requirement_name,
       NULL::text AS status_event_type_code,
       CASE WHEN a.flex_type = 'FLEX::Events::AttendingPhysicianStatementRequested' THEN 'Attending Physician Statement Requested'
            WHEN a.flex_type = 'FLEX::Events::AttendingPhysicianStatementReviewed'  THEN 'Attending Physician Statement Reviewed'
            WHEN a.flex_type = 'FLEX::Events::InitialPremiumReviewed' THEN 'Initial Premium Reviewed'
            WHEN a.flex_type = 'FLEX::Events::SpecialAuthorizationRequested' THEN 'Special Authorization/HIPAA Requested'
            WHEN a.flex_type = 'FLEX::Events::SpecialAuthorizationReviewed' THEN 'Special Authorization/HIPAA Reviewed'
            ELSE 'Initial Premium Requested'
       END AS status_event_name,
       CASE WHEN a.flex_type = 'FLEX::Events::InitialPremiumReviewed' THEN r.requirement_comment
            WHEN r.requirement_id = 'COD' THEN 'Initial Premium payment via COD'
            WHEN r.requirement_id = 'IPCCPAYMENT' THEN 'Initial Premium payment via CC'
            WHEN r.requirement_id = 'IPEFTPAYMENT' THEN 'Initial Premium payment via EFT'
            WHEN r.requirement_id = '1495' THEN r.requirement_comment
            WHEN r.requirement_id = '1496' THEN r.requirement_comment
            WHEN r.requirement_id = '1497' THEN r.requirement_comment
            WHEN r.requirement_id = '1111' THEN r.requirement_comment
            ELSE NULL::text
       END AS status_event_detail,
       NULL::text AS flex_status,
       p.tracking,
       last_updated_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN realtime_actual_events AS r ON a.id = r.id
LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference
WHERE r.event_category = 'Pending Policy'


 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Inforce Policy Update'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Policy Status Change: '::text || policy_status AS status_event_name,
       'Change from: '::text || previous_policy_status AS status_event_detail,
       'Updated'::text AS flex_status,
       p.tracking,
       transaction_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN policy_status_actual_events AS b ON a.id = b.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference


 UNION
  SELECT a.id,
         a.id AS actual_event_id,
         NULL::integer AS expected_event_id,
         a.policy_reference,
         NULL::text AS provider_order_number,
         'Inforce Policy Update'::text AS requirement_name,
         NULL::text AS status_event_type_code,
         'Billing Method Change: '::text || billing_method AS status_event_name,
         'Change from: '::text || previous_billing_method AS status_event_detail,
         'Updated'::text AS flex_status,
         p.tracking,
         transaction_at AS status_event_at,
         NULL::timestamp AS late_at,
         NULL::timestamp AS requested_schedule_at,
         a.created_at,
         NULL::text AS expected_event_flex_type,
         a.flex_type AS actual_event_flex_type,
         COALESCE(p.product_name, a.product) AS product_name
  FROM actual_events AS a
  INNER JOIN billing_method_actual_events AS b ON a.id = b.id
  LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference


 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Inforce Policy Update'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       'Payment Mode Change: '::text || payment_mode AS status_event_name,
       'Change from: '::text || previous_payment_mode AS status_event_detail,
       'Updated'::text AS flex_status,
       p.tracking,
       transaction_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN payment_mode_actual_events AS pm ON a.id = pm.id
LEFT OUTER JOIN policies AS p ON p.policy_reference = a.policy_reference


 UNION
SELECT a.id,
       a.id AS actual_event_id,
       NULL::integer AS expected_event_id,
       a.policy_reference,
       NULL::text AS provider_order_number,
       'Inforce at TPA'::text AS requirement_name,
       NULL::text AS status_event_type_code,
       policy_status AS status_event_name,
       NULL::text AS status_event_detail,
       ''::text AS flex_status,
       p.tracking,
       transaction_at AS status_event_at,
       NULL::timestamp AS late_at,
       NULL::timestamp AS requested_schedule_at,
       a.created_at,
       NULL::text AS expected_event_flex_type,
       a.flex_type AS actual_event_flex_type,
       COALESCE(p.product_name, a.product) AS product_name
FROM actual_events AS a
INNER JOIN inforce_at_tpa_actual_events AS b ON a.id = b.id
INNER JOIN policies AS p ON p.policy_reference = a.policy_reference
)
AS everything
WHERE product_name IN ('Accidental Death 50')
ORDER BY policy_reference, status_event_at DESC, requirement_name