Tuesday, 2 September 2014

AP Trail Balance Query

/* Formatted on 9/2/2014 2:08:39 PM (QP5 v5.115.810.9015) */
SELECT alb.invoice_id invoice_id,
       inv.invoice_num,
       inv.description inv_description,
       alb.vendor_id vendor_id,
       pv.segment1 vendor#,
       pv.vendor_name,
       alb.code_combination_id code_combination_id,
       SUM (ae_invoice_amount) invoice_amount,
       SUM (alb.accounted_cr) - SUM (alb.accounted_dr) remaining_amount,
       alb.set_of_books_id set_of_books_id,
       alb.org_id org_id,
       gcc.segment1,
       gcc.segment2,
       gcc.segment3,
       inv.invoice_type_lookup_code,
       inv.invoice_date,
       inv.attribute5 status,
       apt.name terms
FROM ap_liability_balance alb,
     po_vendors pv,
     ap_invoices_all inv,
     gl_code_combinations gcc,
     ap_terms apt
WHERE     pv.vendor_id = alb.vendor_id
      AND inv.invoice_id = alb.invoice_id
      AND alb.code_combination_id = gcc.code_combination_id
      AND apt.term_id = inv.terms_id
      AND alb.org_id = :org_id
      AND TRUNC (accounting_date) <= :date1
GROUP BY alb.invoice_id,
         alb.code_combination_id,
         alb.vendor_id,
         alb.set_of_books_id,
         alb.org_id,
         pv.segment1,
         pv.vendor_name,
         inv.invoice_num,
         inv.description,
         gcc.segment1,
         gcc.segment2,
         gcc.segment3,
         inv.invoice_type_lookup_code,
         inv.invoice_date,
         inv.attribute5,
         apt.name
HAVING SUM (accounted_cr) <> SUM (accounted_dr)

No comments:

Post a Comment