CREATE TABLE  TRANS_PARTS 
(
  TRANS_ID NUMBER(38, 0) NOT NULL 
, PARTS_AMT NUMBER(38, 8) NOT NULL 
, GP_ID NUMBER(38, 8) NOT NULL 
, SRC_POSTING  DATE NOT NULL , 
,  tCODE VARCHAR2(64 BYTE) 
,  tNAME VARCHAR2(64 BYTE) 
) 
 
Insert into TRANS_PARTS values (29580,3749.99,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (29580,266.64,126003,sysdate,'PERIPHERALS','peripherique');
Insert into TRANS_PARTS values (1329,2941.65,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (1329,149.99,126003,sysdate,'PERIPHERALS','peripherique');
Insert into TRANS_PARTS values (6842,1316.66,126005,sysdate,'SYSTEMS','System product');
Insert into TRANS_PARTS values (6842,312.49,126009,sysdate,'OSVR','osvr product');
Insert into TRANS_PARTS values (6842,24.96,123727,sysdate,'DEFAULT','Default');
Insert into TRANS_PARTS values (3577,299.96,126003,sysdate,'PERIPHERALS','peripherique');

WITH wt AS
  (SELECT *
  FROM test_ve  wo
  WHERE 1 =1
  AND wo.active_ind  = 1
 ),
 smdo AS
  ( SELECT DISTINCT OID,pid    FROM wt  JOIN demand_tbl smdo
  ON wt.OID  = smdo.OID  AND wt.pid = smdo.pid
  WHERE 1  =1
  )
SELECT
       wt.user_account  ,
       wt.OID,
       CASE
          WHEN  MAX(wt.sale ) = MAX(bod.tx_curr)
                AND wt.sale = sum(bod.tx_ne_price)
          THEN  1
          WHEN  MAX(wt.sale_currency) <> MAX(bod.tx_curr)
                AND round(wt.sale_amt,2) = round(sum(bod.tx_ne_price),2)
          THEN 2
           ELSE 3
        END 
      
FROM wt
LEFT JOIN booked_tbl bod
ON wt.OID  = bod.OID
AND wt.pid = bod.pid
LEFT JOIN smdo
ON wt.OID = smdo.OID
AND wt.pid = smdo.pid
WHERE 1=1
GROUP BY wt.user_account  , wt.OID ,wt.sale