RA Data Fix

RA Data Fix

SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY');
 
 
  SELECT *--event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 3 22 652 rows data returns
    
    
      SELECT event_id
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;  -- No data return
   
 
  --3rd
  
  SELECT event_id
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3  -- 9 rows return
    
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
    
     select * from  ra_cust_trx_line_gl_dist_all
     --SET posting_control_id = -9999,
         --gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-OCT-1816','DD-MON-YYYY') TO_DATE('01-OCT-2016','DD-MON-YYYY') AND TO_DATE('31-OCT-2016','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
    
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY');
 
 
 
     SELECT event_id
  FROM xla_events
WHERE 1 = 1
  AND application_id = 222
  AND event_status_code NOT IN('P','N','Z')
  AND event_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
  -- No data
 

 SELECT event_id
       FROM ar_receivable_applications_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
  -- 38 rows
 
 
  SELECT event_id,gl_posted_date
       FROM ar_cash_receipt_history_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- no data
    
    
     SELECT event_id,posting_control_id,gl_posted_date
      FROM ra_cust_trx_line_gl_dist_all
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
     -- 7 rows
    
    
     ---- update
    
      UPDATE ar_receivable_applications_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
  WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;
    
     UPDATE ra_cust_trx_line_gl_dist_all
     SET posting_control_id = -9999,
         gl_posted_date = TO_DATE('01-OCT-1816','DD-MON-YYYY')
     WHERE 1 = 1
     AND gl_date BETWEEN TO_DATE('01-JUL-2015','DD-MON-YYYY') AND TO_DATE('31-JUL-2015','DD-MON-YYYY')
     AND posting_control_id = -3;