--------------Acceptd ----
select x.oidd||','||x.trans_dt ||','||x.status||','||x.NEW_SALE_VALUE||','||y.brkup||':'||x.NEW_SALE_VALUE||','||x.curr
from (
with twt as
  (select *
  from tw_orders_vw  two
  where 1=1
  ), sdo as
  ( select distinct twt.order_id ,twt.platform_id,twt.tx_click_dttm,twt.cst_click_dttm,
     sdo.tracking_code,sdo.marketing_program_code
  from twt
  join  book_base_fact sdo
  ON twt.order_id=sdo.order_id
  and twt.platform_id=sdo.platform_id
  where 1=1
    )
select
       twt.transaction_id,
       twt.order_id as oidd,
       to_char(twt.transaction_dttm,'DD/MM/YYYY HH:MM:SS')as trans_dt,
       'change' as status,
        ,NEW_SALE_VALUE 
        ,curr       
  from twt
    left join  booked_orderline_dnorm bod
      on twt.order_id=bod.order_id
      and twt.platform_id=bod.platform_id
    left join  curr_tbl cur  
      on trunc(twt.transaction_dttm)=cur.effective_date
      and twt.sale_currency=cur.to_currency_code
      and cur.from_currency_code='USD'
    left join  curr_tbl cur1  
      on trunc(twt.transaction_dttm)=cur1.effective_date
    left join sdo
      on twt.order_id=sdo.order_id
      and twt.platform_id=sdo.platform_id
 where 1=1
 and  not regexp_like (twt.order_id,'[^[:digit:]]')
 group by twt.transaction_id,twt.tw_account_id , twt.order_id ,twt.sale_amt,twt.transaction_dttm
  )X,
  (
    SELECT tw_transaction_id ,LISTAGG (PT.COMMISSIONGROUPCode ,':')
    WITHIN GROUP (ORDER BY PT.COMMISSIONGROUPcode DESC)brkup
    FROM  tw_trans_parts pt
    GROUP BY tw_transaction_id
)Y
where y.tw_transaction_id =x.transaction_id;
 ------------------------------------------------------------------------
 Deleted Record Query
 
 with twt as
  (select *
  from tw_orders_vw  two
  where 1=1
  ), sdo as
  ( select distinct twt.order_id ,twt.platform_id,twt.tx_click_dttm,twt.cst_click_dttm,
     sdo.tracking_code,sdo.marketing_program_code
  from twt
  join  sbook_base_fact sdo
  ON twt.order_id=sdo.order_id
  and twt.platform_id=sdo.platform_id
  where 1=1
    )
 select
       twt.order_id||','||
       to_char(twt.transaction_dttm,'DD/MM/YYYY HH:MM:SS')||','||
       case
        when regexp_like (twt.order_id,'[^[:digit:]]')OR
             max(sdo.order_id) is null OR
             sum(sdo.order_base_price) = 0 OR          
          max(smdo.order_id) is not null THEN 'Declined'
        end||','|| 
        case
             when sum(sdo.order_base_price) = 0 then 'Order Returned'
             when max(sdo.order_id) is not null then 'Duplication / Sale Accredited to Another Source'
        end
		from twt
    left join  sbook_base_fact sdo
      on twt.order_id=sdo.order_id
      and twt.platform_id=sdo.platform_id
    left join  curr_tbl cur  
      on trunc(twt.transaction_dttm)=cur.effective_date
      and twt.sale_currency=cur.to_currency_code
      and cur.from_currency_code='USD'
    left join  curr_tbl cur1  
      on trunc(twt.transaction_dttm)=cur1.effective_date
    left join sdo
      on twt.order_id=sdo.order_id
      and twt.platform_id=sdo.platform_id
 where 1=1
 and  not regexp_like (twt.order_id,'[^[:digit:]]')
---------------------Accepted Query--------------
with twt as
  (select *
  from tw_orders_vw  two
  where 1=1
  ), sdo as
  ( select distinct twt.order_id ,twt.platform_id,twt.tx_click_dttm,twt.cst_click_dttm,
     sdo.tracking_code,sdo.marketing_program_code
  from twt
  join  sbook_base_fact sdo
  ON twt.order_id=sdo.order_id
  and twt.platform_id=sdo.platform_id
  where 1=1
    )
	select
       twt.order_id||','||
      to_char(twt.transaction_dttm,'DD/MM/YYYY HH:MM:SS')||','||
       case
          when twt.SALE_AMT = SUM(sdo.order_base_price)  then 'Accepted'
          when  ROUND(SUM(sdo.order_base_price)*(max(cur.curr_rate)/max(cur1.EXCHANGE_RATE_NUM)),2)
          then 'Accepted' 
           else 'Accepted manually' 
        end     
	from twt
    left join  sbook_base_fact sdo
      on twt.order_id=sdo.order_id
      left join  curr_tbl ces_pcc  
      on trunc(twt.transaction_dttm)=ces_pcc.effective_date
      and twt.sale_currency=ces_pcc.to_currency_code
      and ces_pcc.from_currency_code='USD'
    left join  curr_tbl ces_txcc  
      on trunc(twt.transaction_dttm)=ces_txcc.effective_date
    left join sdo
      on twt.order_id=sdo.order_id
     
 where 1=1
 and  not regexp_like (twt.order_id,'[^[:digit:]]')