r_print_debet_credit_ar LAMA

CREATE OR REPLACE FUNCTION r_print_debet_credit_ar(character varying, bigint, bigint, bigint, character varying, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader				REFCURSOR := 'refHeader';
	pRefDetail				REFCURSOR := 'refDetail';
	pSessionId				ALIAS FOR $1;
	pTenantId				ALIAS FOR $2;
	pUserId					ALIAS FOR $3;
	pRoleId			 		ALIAS FOR $4;
	pDatetime				ALIAS FOR $5;
	pInvoiceArId				ALIAS FOR $6;

	vDocTypeDebetAr				bigint;
	vTotal					numeric;
	vtotalTax				numeric;
	vGrandTotal				numeric;

BEGIN
	vDocTypeDebetAr := '241';

	SELECT SUM(A.add_amount) INTO vTotal
	FROM fi_invoice_ar_cost A
	INNER JOIN fi_invoice_ar B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_ar_id
	INNER JOIN m_activity_gl C ON A.tenant_id = C.tenant_id AND A.activity_gl_id = C.activity_gl_id
	WHERE A.tenant_id = pTenantId
	AND A.invoice_ar_id = pInvoiceArId;

	SELECT SUM(A.tax_amount) INTO vTotalTax
	FROM fi_invoice_ar_cost A
	INNER JOIN fi_invoice_ar B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_ar_id
	INNER JOIN m_activity_gl C ON A.tenant_id = C.tenant_id AND A.activity_gl_id = C.activity_gl_id
	WHERE A.tenant_id = pTenantId
	AND A.invoice_ar_id = pInvoiceArId;

	vGrandTotal = vTotal + vTotalTax;

	Open pRefHeader FOR
	SELECT f_get_ou_name(A.ou_id) AS main_ou, COALESCE(C.address1 || ' ' || C.address2 || ' ' || C.address3, '-') AS address1,
		   (CASE WHEN (C.city || ', ' || C.state_or_province || ', ' || C.zip_code) = ', , '
			THEN ' - '
			ELSE C.city || ', ' || C.state_or_province || ', ' || C.zip_code
			END) AS address2,
		   COALESCE(C.npwp_no, '-') AS npwp_no, A.doc_no AS doc_no, A.doc_date AS doc_date,
		   A.ext_doc_no AS customer_doc_no, A.ext_doc_date AS customer_doc_date, A.curr_code AS currency,
		   A.remark AS remark, f_get_username(pUserId) AS username, f_get_partner_name(A.partner_id) AS customer_name,
		   (CASE WHEN A.doc_type_id = vDocTypeDebetAr THEN 'D' ELSE 'C' END) AS flg_debet_credit,
		   vGrandTotal AS grand_total, vTotal AS total, vTotalTax AS total_tax,
		   CONCAT(terbilang(vGrandTotal), (CASE WHEN A.curr_code = 'IDR' THEN 'RUPIAH' ELSE a.curr_code END)) AS terbilang,
		   COALESCE(COALESCE(E.address1, D.address1) || ' ' || COALESCE(E.address2, D.address2) || ' ' ||
			COALESCE(E.address3, D.address3), '-') AS customer_address1,
		   (CASE WHEN (COALESCE(E.city, D.city) || ', ' || D.state_or_province )= ', '
			THEN ' - '
			ELSE COALESCE(E.city, D.city) || ', ' || COALESCE(D.state_or_province, ' ')
			END) AS customer_address2
	FROM fi_invoice_ar A
	  INNER JOIN t_ou B ON A.ou_id = B.ou_id AND A.tenant_id = B.tenant_id
	  LEFT OUTER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
	  LEFT OUTER JOIN m_partner_address D ON A.partner_id = D.partner_id AND D.flg_official = 'Y'
	  LEFT OUTER JOIN m_partner_npwp E ON A.partner_id = E.partner_id AND E.active = 'Y'
	WHERE A.invoice_ar_id = pInvoiceArId AND A.tenant_id = pTenantId;

	RETURN NEXT pRefHeader;

	Open pRefDetail FOR
	SELECT A.invoice_ar_cost_id, A.activity_gl_id, C.activity_gl_code, C.activity_gl_name,
           A.curr_code, A.add_amount, A.tax_id, A.tax_percentage, A.tax_amount
	FROM fi_invoice_ar_cost A
	  INNER JOIN fi_invoice_ar B ON A.tenant_id = B.tenant_id AND A.invoice_ar_id = B.invoice_ar_id
	  INNER JOIN m_activity_gl C ON A.tenant_id = C.tenant_id AND A.activity_gl_id = C.activity_gl_id
	WHERE A.tenant_id = pTenantId
	 AND A.invoice_ar_id = pInvoiceArId;

	RETURN NEXT pRefDetail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
  /