I got a SQL query as follows:
SELECT <blah>, <blah>, ....
FROM    ap_invoices_all                a
       ,ap_invoice_distributions_all   b
       ,ap_suppliers                   i
       ,ap_supplier_sites_all          assa  
       ,ap_payment_schedules_all       k
       ,ap_terms_tl                    m    
       ,ap_invoice_lines_all           aila 
       ,per_all_people_f               papf 
 WHERE  a.invoice_id          = b.invoice_id
   AND  a.invoice_id          = k.invoice_id(+)
   AND  i.vendor_id           = a.vendor_id
   and  a.vendor_site_id      = assa.vendor_site_id 
   AND  m.term_id             = a.terms_id
   AND  b.invoice_id          = aila.invoice_id
   AND  b.invoice_line_number = aila.line_number
   AND  a.requester_id        = papf.person_id (+)             
   AND  trunc(a.last_update_date) between papf.effective_start_date  (+) and papf.effective_end_date (+)  
   AND  k.payment_num = 1
   AND  (a.cancelled_date is NULL)
   AND  a.payment_status_flag BETWEEN 'N' AND 'P'
order by a.invoice_id, b.invoice_distribution_id;
Here the joins are in the form of comma separated tables and Oracle (+) operator which means LEFT OUTER JOIN. I have to convert those comma separated tables in FROM clause to proper JOIN statements.
I have tried this:
SELECT <blah>, <blah>, ...
  FROM  ap_invoices_all                     a
        JOIN ap_invoice_distributions_all   b       ON   a.invoice_id        = b.invoice_id
        JOIN ap_supplier_sites_all          assa    ON   a.vendor_site_id    = assa.vendor_site_id
        LEFT OUTER JOIN ap_payment_schedules_all k  ON   a.invoice_id        = k.invoice_id
        LEFT OUTER JOIN per_all_people_f papf       ON   a.requester_id      = papf.person_id
        JOIN ap_terms_tl                    m       ON   m.term_id           = a.terms_id
        JOIN ap_suppliers                   i       ON   i.vendor_id           = a.vendor_id
        JOIN ap_invoice_lines_all           aila    ON b.invoice_id          = aila.invoice_id 
                                                   AND b.invoice_line_number = aila.line_number
 WHERE    k.payment_num = 1
   AND  ISNULL(a.Cancelled_Date)
   AND  a.payment_status_flag BETWEEN 'N' AND 'P'           
   AND  DATE_TRUNC("DAY", a.last_update_date)  between papf.effective_start_date  and papf.effective_end_date   
order by a.invoice_id, b.invoice_distribution_id;
But my converted query provides different result from the original query. Could anyone please help me understand where I am wrong and what should be the proper join statements?
Thanks, Soubhik
