ERPstuff - Oracle EBS Community

Code: Select all

SELECT  v.vendor_name,
        cc1.segment1, 
        cc1.segment2,
        cc1.segment3,
        cc1.segment4,        
        ael.ae_line_type_code c_type, 
        aip.accounting_date c_gl_date,
        ck.check_number c_check_number, 
        ck.check_number c_check_number1,
        i.invoice_currency_code c_currency, 
        i.invoice_num c_transaction_type,
         DECODE (ael.ae_line_type_code, 'GAIN', SUM(NVL(ael.accounted_cr, 0)- NVL (ael.accounted_dr, 0)), NULL) c_gain_amount,
         DECODE (ael.ae_line_type_code, 'LOSS', -1 * SUM(NVL(ael.accounted_dr,0)-NVL(ael.accounted_cr, 0)),NULL) c_loss_amount
    FROM ap_checks_all ck,
         ap_invoices_all i,
         po_vendors v,
         ap_invoice_payments_all aip,
         ap_ae_lines_all ael,
         ap_ae_headers_all aeh,
         gl_code_combinations cc1,
         gl_code_combinations cc2
   WHERE cc2.code_combination_id(+) = aip.gain_code_combination_id
     AND cc1.code_combination_id(+) = aip.loss_code_combination_id
     AND aip.set_of_books_id = <your set_of_books_id>
     AND aip.check_id = ck.check_id
     AND aip.invoice_id = i.invoice_id
     AND aip.accounting_event_id = aeh.accounting_event_id
     AND aip.invoice_payment_id = ael.source_id
     AND aeh.gl_transfer_flag = 'Y'
     AND aeh.ae_header_id = ael.ae_header_id
     AND v.vendor_id = i.vendor_id
     AND ael.ae_line_type_code IN ('GAIN', 'LOSS')
     AND (NVL (ael.accounted_dr, 0) <> 0 OR NVL (ael.accounted_cr, 0) <> 0)
     AND EXISTS (SELECT 'X'
              FROM ap_ae_headers_all aeh1,
                   ap_ae_lines_all ael1,
                   ap_invoice_payments_all aip1
             WHERE aip1.accounting_event_id = aeh1.accounting_event_id
               AND aeh1.ae_header_id = ael1.ae_header_id
               AND aip1.check_id = aip.check_id
               AND ael1.ae_line_type_code =
                      DECODE (ael.ae_line_type_code,
                              'GAIN', 'LOSS',
                              'LOSS', 'GAIN'
                             )
               AND (   NVL (ael1.accounted_dr, 0) <> 0
                    OR NVL (ael1.accounted_cr, 0) <> 0
                   ))
     AND aip.accounting_date >= '01-JAN-08'
     AND aip.accounting_date <= '30-NOV-08'
GROUP BY v.vendor_name,
         aip.accounting_date,
         cc1.segment1,
         cc1.segment2,
         cc1.segment3,
         cc1.segment4,
         aip.gain_code_combination_id,
         aip.loss_code_combination_id,
         ael.ae_line_type_code,
         ck.check_number,
         i.invoice_currency_code,
         i.invoice_num
ORDER BY 2 ASC, 1 ASC, 5 ASC, aip.accounting_date