abirama62

query_get_list_ar

Jul 5th, 2021
932
251 days
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH daftar_ar AS(
  2.     SELECT distinct(B.invoice_ar_balance_id), 'document_header' AS type_record, f_get_ou_name(B.ou_id) as ou_name, f_get_doc_desc(B.doc_type_id),
  3.         B.doc_no, B.doc_date, B.ext_doc_no, B.ext_doc_date,
  4.         B.amount, 0 AS tax_amount, B.payment_amount, B.remark
  5.     -- SELECT B.invoice_ar_balance_id, A.*
  6.     from fi_summary_monthly_ar A
  7.     INNER JOIN fi_invoice_ar_balance B ON A.doc_type_id = B.doc_type_id AND A.invoice_id = B.invoice_ar_balance_id
  8.     where left(date_year_month,4) = '2021'
  9.     and balance_amount <> trunc(balance_amount)
  10.     --ORDER BY B.doc_date, B.doc_no
  11.     UNION ALL
  12.     SELECT distinct(B.invoice_tax_ar_balance_id), 'document_tax' as type_record, f_get_ou_name(B.ou_id) as ou_name, f_get_doc_desc(B.doc_type_id),
  13.         C.doc_no, C.doc_date, C.ext_doc_no, C.ext_doc_date,
  14.         0 AS amount, B.tax_amount, B.payment_amount, B.remark
  15.     -- SELECT B.*
  16.     from fi_summary_monthly_ar A
  17.     INNER JOIN fi_invoice_tax_ar_balance B ON A.doc_type_id = B.doc_type_id AND A.invoice_id = B.invoice_tax_ar_balance_id
  18.     INNER JOIN fi_invoice_ar_balance C ON C.invoice_ar_balance_id = B.invoice_ar_balance_id
  19.     where left(date_year_month,4) = '2021'
  20.     and balance_amount <> trunc(balance_amount)
  21.     --ORDER BY C.doc_date, C.doc_no ASC
  22. ) SELECT *
  23. FROM daftar_ar
  24. ORDER BY doc_date ASC, doc_no ASC, type_record
RAW Paste Data