How to adjust the dates and distinct records based on a column

The rules are quite extensive, and your might get better results and performance with PL/SQL code, as that can use variables while iterating over a cursor.

Still, I think the following query may do what you need:

select  reg_id, 
        event_type_cd,
        event_date,
        package_desc,
        case product_type when 'GOODWILL' then coalesce(prev_product_type, 'TRIAL')
            else product_type
        end as product_type,
        case event_type_cd when 'EXPIRATION' then first_term_start_date
            else term_start_date
        end as term_start_date,
        case next_product_type when 'GOODWILL' then next_term_end_date
            else term_end_date
        end as term_end_date
from    (select reg_id, 
                event_type_cd,
                event_date,
                package_desc,
                product_type,
                term_start_date,
                term_end_date,
                first_value(term_start_date) over (
                    partition by reg_id, package_desc 
                    order by event_date, term_end_date, event_type_cd desc) as first_term_start_date,
                lead(term_end_date, 1) over (
                    partition by reg_id, package_desc 
                    order by event_date, term_end_date, event_type_cd desc) as next_term_end_date,
                lag(product_type, 1) over (
                    partition by reg_id, package_desc 
                    order by event_date, term_end_date, event_type_cd desc) as prev_product_type,
                lead(product_type, 1) over (
                    partition by reg_id, package_desc 
                    order by event_date, term_end_date, event_type_cd desc) as next_product_type
        from    (select reg_id, 
                        event_type_cd,
                        event_date,
                        package_desc,
                        product_type,
                        term_start_date,
                        term_end_date,
                        lead(product_type, 1, '-') over (
                            partition by reg_id, package_desc 
                            order by event_date, term_end_date, event_type_cd desc) as next_product_type
                from    mytable)
                where   not (event_type_cd = 'CANCELLATION' and next_product_type <> '-')
                and     not (product_type = 'GOODWILL' and next_product_type = 'GOODWILL')
                )
where    not (product_type = 'GOODWILL' and event_type_cd <> 'EXPIRATION' and prev_product_type is not null)
order by reg_id, package_desc, event_date, term_end_date, event_type_cd desc

The query has a two-level nested sub query.

The inner-most query only serves to get the product_type of the next record within the cycle (i.e. within the same partition of reg_id and package_desc).

The middle query uses that information to eliminate:

• 'CANCELLATION' records, unless they are the very last record of their cycle;
• Consecutive 'GOODWILL' records, leaving only the last in place of every sequence -- this is last one is temporary, but is at this stage still needed;

The middle query also re-fetches the product_type of the next record in the cycle, as it might now have changed due to the eliminated records. Additionally, it determines:

• the term_start_date of the first record in the cycle;
• the term_end_date of the next record in the cycle;
• the product_type of the previous record in the cycle;

Finally, the outer query uses this information to:

• set the product_type to that of the previous record (or 'TRIAL' if there is no previous) if it concerns a 'GOODWILL' record;
• set the term_start_date to the that of the first record in the cycle, if it concerns an 'EXPIRATION' record;
• set the term_end_date to that of the next record in the cycle, if the next record concerns a 'GOODWILL' record.

The 'GOODWILL' records (before the change in the first bullet above) are excluded from the result, except when they relate to a first record in their cycle, or correspond to an 'EXPIRATION' record.

The order by clause uses the order as you mentioned in comments, with an additional event_type_cd desc to make sure "The EVENT_TYPE_CD's for Cancellation or Expiration will always follow the New Subscription or UPSELL for a particular REG_ID, PACKAGE_DESC". This is because by fortune both 'NEW SUBSCRIPTION' and 'UPSELL' come both later in the alphabetical order than 'CANCELLATION' and 'EXPIRATION', so in descending order we get them sorted right.