r_limit_detail_mou_for_salesman_csv 20180316

--Adrian, Mar 8, 2018
--! Jika terjadi perubahan, sesuaikan juga r_limit_detail_mou !

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

	pOuId						ALIAS FOR $6;
	pPartnerId					ALIAS FOR $7;
	pPeriodFrom					ALIAS FOR $8;
	pPeriodTo					ALIAS FOR $9;

	vPartnerCodeMou					character varying(50);
	vPartnerNameMou					character varying(100);
	vDocTypeIdSOByBrand				bigint;
	vStatusReleased					character varying(1);
	vStatusFinal					character varying(1);
	vStatusInProgress				character varying(1);
	vStatusDraft					character varying(1);
	vTagKeyMOU					character varying(10);
	vDocTypeIdReturnNote				bigint;
	vDocTypeIdSalesInvoice				bigint;
	vDocTypeIdInvoiceTemp				bigint;
	vDocTypeIdConversionOfExchangeIn		bigint;
	vDocTypeIdConversionOfExchangeOut		bigint;
	vDocTypeIdRRS					bigint;
	vStatusMapping					character varying(1);
	vStatusApproved					character varying(1);
	vRaw						character varying(20);
	vSummary					character varying(20);
	vEmptyId					bigint;
	vOne						integer := 1;
	vEmptyValue					character varying;
	vParamCodeInsentif			character varying := 'INCENTIVE';
	vActivityGLCodeInsentif		character varying;
	vActivityGLInsentif			bigint;

	vFlagYes					character varying(1) :='Y';

	vDocNoMou					character varying(50);
	i							bigint;
	vSeparator					character varying(1);

BEGIN

	vPartnerCodeMou := ' ';
	vPartnerNameMou := ' ';
	vDocTypeIdSOByBrand := 398;
	vStatusReleased	:= 'R';
	vStatusFinal := 'F';
	vStatusInProgress := 'I';
	vStatusDraft := 'D';
	vTagKeyMOU := 'MOU';
	vDocTypeIdReturnNote := 502;
	vDocTypeIdSalesInvoice := 321;
	vDocTypeIdInvoiceTemp := 361;
	vDocTypeIdConversionOfExchangeIn := 394;
	vDocTypeIdConversionOfExchangeOut := 395;
	vDocTypeIdRRS :=381;
	vStatusMapping := 'M';
	vStatusApproved	:= 'A';
	vRaw := 'RAW';
	vSummary := 'SUMMARY';
	vEmptyId := -99;
	vEmptyValue := '';
	vDocNoMou := '';
	i := 1;
	vSeparator := ';';

	SELECT partner_code INTO vPartnerCodeMou
	FROM m_partner
	WHERE partner_id = pPartnerId;

	SELECT partner_name INTO vPartnerNameMou
	FROM m_partner
	WHERE partner_id = pPartnerId;

	-- Get data-data activity gl code incentive dari system config value
	SELECT TRIM(REPLACE(f_get_value_system_config_by_param_code(pTenantId, vParamCodeInsentif), ';', ''',''')) INTO vActivityGLCodeInsentif;

	/*
	--get data activity gl insentif
	SELECT B.parameter_value INTO vActivityGLCodeInsentif
	FROM t_parameter A
		INNER JOIN t_system_config B ON A.parameter_id = B.parameter_id
	WHERE A.parameter_code = vParamCodeInsentif
		AND B.tenant_id = pTenantId;

	--get activity gl
	SELECT activity_gl_id INTO vActivityGLInsentif
	FROM m_activity_gl
	WHERE tenant_id = pTenantId
		AND activity_gl_code = vActivityGLCodeInsentif;
	*/

	--Menghapus semua table temporrer untuk mulai insert data baru
	DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
	DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
	DELETE FROM tt_sl_so WHERE session_id = pSessionId;
	DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
	DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;

	-- Mengambil data MOU sesuai dengan filter nya
	INSERT INTO tt_sl_mou_list(
	  session_id ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
	  curr_code ,plafon_amount ,status_doc ,workflow_status)
	SELECT pSessionId ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
	  curr_code ,plafon_amount ,status_doc ,workflow_status
	FROM sl_mou
	WHERE tenant_id = pTenantId
		AND ou_id = pOuId
		AND partner_id = pPartnerId
		AND period_from BETWEEN pPeriodFrom AND pPeriodTo
		AND status_doc IN (vStatusReleased,vStatusFinal)
		AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, ou_id) = vOne
		AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, partner_id) = vOne;

	-- Mengambil semua data SO By Brand yang tagging ke mou (semua status doc)
	INSERT INTO tt_sl_so(
	  session_id ,so_id,tenant_id ,ou_id ,doc_type_id ,doc_date ,doc_no ,partner_id , mou_id, curr_code ,
	  amount, amount_tax, status_doc ,workflow_status)
	SELECT pSessionId ,A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id, A.curr_code ,
	  SUM(D.nett_brand_amount) AS amount, SUM(D.qty*D.tax_price) AS amount_tax ,A.status_doc ,A.workflow_status
	FROM sl_so A
		INNER JOIN sl_so_info B ON A.so_id = B.so_id
		INNER JOIN tt_sl_mou_list C ON B.mou_id = C.mou_id AND C.session_id = pSessionId
		INNER JOIN sl_so_brand_item D ON A.so_id = D.so_id
	WHERE A.tenant_id = pTenantId
		AND A.ou_id = pOuId
		AND A.doc_type_id = vDocTypeIdSOByBrand
		AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
		AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
		AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
		AND NOT EXISTS (
			SELECT 1 FROM sl_so_balance_invoice Z
			WHERE Z.so_id = A.so_id
			GROUP BY Z.so_id
			HAVING SUM(Z.qty_dlv_so) <= 0
		)
	GROUP BY A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id,A.curr_code ,
	  A.status_doc ,A.workflow_status;

	-- Insert data so by brand ke table tt_sl_outstanding_so yang mana sudah tagging mou dan disubmit , status doc nya (R/F)
	INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id ,amount_so ,amount_tax_so)
	SELECT pSessionId, vRaw, A.so_id, A.amount, A.amount_tax
	FROM tt_sl_so A
		INNER JOIN sl_so_tagging B ON A.so_id = B.so_id AND B.tag_key = vTagKeyMOU
	WHERE A.session_id = pSessionId
		AND A.status_doc IN (vStatusReleased,vStatusFinal);

	-- Insert data invoice temp ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
	INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_temp_id ,amount_inv_temp ,amount_tax_inv_temp)
	SELECT pSessionId, vRaw, B.ref_id,B.invoice_temp_id, B.total_amount, COALESCE(C.tax_amount,0)
	FROM tt_sl_outstanding_so A
		INNER JOIN sl_invoice_temp B ON A.so_id = B.ref_id
		LEFT JOIN sl_invoice_temp_tax C ON B.invoice_temp_id = C.invoice_temp_id
	WHERE A.session_id = pSessionId
		AND B.tenant_id = pTenantId
		AND B.ou_id = pOuId
		AND B.status_doc = vStatusReleased
		AND B.doc_type_id = vDocTypeIdInvoiceTemp
		AND NOT EXISTS (SELECT 1 FROM sl_invoice D
					WHERE D.doc_no = B.inv_doc_no
						AND D.ref_id = B.ref_id
						AND D.doc_type_id = vDocTypeIdSalesInvoice
						AND D.status_doc = vStatusReleased);

	-- Insert data invoice ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
	INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_id ,amount_inv, amount_tax_inv)
	SELECT pSessionId, vRaw, B.ref_id, B.invoice_id, B.total_amount, COALESCE(D.tax_amount,0)
	FROM tt_sl_outstanding_so A
		INNER JOIN sl_invoice B ON A.so_id = B.ref_id
		INNER JOIN sl_invoice_tagging C ON B.invoice_id = C.invoice_id AND C.tag_key = vTagKeyMOU
		LEFT JOIN sl_invoice_tax D ON B.invoice_id = D.invoice_id
	WHERE A.session_id = pSessionId
		AND A.invoice_temp_id = vEmptyId
		AND B.tenant_id = pTenantId
		AND B.ou_id = pOuId
		AND B.status_doc = vStatusReleased
		AND B.doc_type_id = vDocTypeIdSalesInvoice;

	-- Insert ke table tt_sl_outstanding_so untuk menghitung summary (untuk mengetahui outstading amount nya so by brand)
	INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,amount_so, amount_tax_so,
		amount_inv, amount_tax_inv, amount_inv_temp,
		amount_tax_inv_temp, final_so,
		final_tax_so)
	SELECT pSessionId, vSummary, so_id, SUM(amount_so) AS amount_so, SUM(amount_tax_so) AS amount_tax_so,
		SUM(amount_inv) AS amount_inv, SUM(amount_tax_inv) AS amount_tax_inv, SUM(amount_inv_temp) AS amount_inv_temp ,
		SUM(amount_tax_inv_temp) AS amount_tax_inv_temp ,(SUM(amount_so) - SUM(amount_inv) - SUM(amount_inv_temp)) AS final_so,
		(SUM(amount_tax_so) - SUM(amount_tax_inv) - SUM(amount_tax_inv_temp)) AS final_tax_so
	FROM tt_sl_outstanding_so
	WHERE session_id = pSessionId
	GROUP BY so_id;

	/*
	Insert data Invoice yang di mapping(di tarik manual ,sebelum nya SO by brand belum di tagging ke mou)
	dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
	*/
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
		vStatusMapping AS status_doc_invoice, C.workflow_status, A.status_doc AS status_doc_mou,
		COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
		INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
		LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
	WHERE A.session_id = pSessionId
		AND C.doc_type_id = vDocTypeIdSalesInvoice
		AND C.status_doc = vStatusReleased
		AND NOT EXISTS(SELECT 1 FROM tt_sl_so D WHERE D.session_id = pSessionId AND D.so_id = C.ref_id);

	-- Insert data Invoice (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
		vStatusApproved AS status_doc_invoice ,C.workflow_status, A.status_doc AS status_doc_mou,
		COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
		INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
		LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
	WHERE A.session_id = pSessionId
		AND C.doc_type_id = vDocTypeIdSalesInvoice
		AND C.status_doc = vStatusReleased
		AND EXISTS(SELECT 1 FROM tt_sl_outstanding_so D
				WHERE D.session_id = pSessionId
					AND D.data_id = vRaw AND D.invoice_id = C.invoice_id AND D.so_id = C.ref_id);

	/*
	Insert data Invoice temp (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice temp = R
	dan belum di convert ke invoice beneran yang mana status doc invoice beneran nya = R ke table tt_sl_r_limit_detail_mou_list
	*/
	INSERT INTO tt_sl_r_limit_detail_mou_list(
       	 session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
   SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		D.invoice_temp_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, D.total_amount AS usage_amount,
		D.status_doc AS status_doc_invoice_temp ,D.workflow_status, A.status_doc AS status_doc_mou,
		COALESCE(E.tax_amount, 0) AS usage_amount_tax, (D.total_amount + COALESCE(E.tax_amount, 0)) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
		INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vRaw
		INNER JOIN sl_invoice_temp D ON C.invoice_temp_id = D.invoice_temp_id
		LEFT JOIN sl_invoice_temp_tax E ON D.invoice_temp_id = E.invoice_temp_id
	WHERE A.session_id = pSessionId
		AND C.session_id = pSessionId;

	-- Insert data SO By Brand yang sudah di tagging dan status doc( D dan I) ke table tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
		B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
		B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
	WHERE A.session_id = pSessionId
		AND B.status_doc IN (vStatusDraft,vStatusInProgress);

	/* Insert data SO By Brand yang sudah di tagging dan status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
	 * UNTUK SO YG SUDAH SAMPAI DI INVOICE (STATUS R) TIDAK PERLU DIMUNCULKAN
	 */
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
		B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
		B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
	WHERE A.session_id = pSessionId
		AND B.status_doc IN (vStatusReleased,vStatusFinal)
		AND NOT EXISTS(SELECT 1 FROM sl_invoice_temp C WHERE B.so_id = C.ref_id AND C.status_doc = vStatusReleased)
		AND NOT EXISTS(SELECT 1 FROM sl_invoice D WHERE B.so_id = D.ref_id AND D.status_doc = vStatusReleased);

	/*
	 * TIDAK LAGI MENAMPILKAN OUTSTANDING SO -> KARENA SO YG SUDAH DI INVOICE (R) TIDAK PERLU DIMUNCULKAN
	 * KRN di KATAMATA 1 SO 1 INVOICE
	 *
	-- Menghapus data dr table tt_sl_outstanding_so yang mana data_id = RAW
	DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId AND data_id = vRaw;

	-- Insert data SO By Brand yang sudah di tagging ,status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
	   	 status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		D.so_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, C.final_so AS usage_amount,
		D.status_doc AS status_doc_so ,D.workflow_status, A.status_doc AS status_doc_mou,
		C.final_tax_so AS usage_amount_tax, (C.final_so + C.final_tax_so) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
		INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vSummary
		INNER JOIN tt_sl_so D ON C.so_id = D.so_id AND D.session_id = pSessionId
	WHERE A.session_id = pSessionId
		AND C.session_id = pSessionId;
	*/

	--INSERT data return yang sudah di submit (status doc R/F) ke tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
	   	 usage_amount ,status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax,
	     total_usage_amount)
	SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
		SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
		C.workflow_status, A.status_doc AS status_doc_mou,
		SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
		(SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN in_return_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
		INNER JOIN in_inventory C ON B.inventory_id = C.inventory_id
		INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
		INNER JOIN sl_request_return_sales E ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
		INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
			AND f_get_brand_by_product_id(D.product_id) = F.brand_id
		INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
	WHERE A.session_id = pSessionId
		AND C.doc_type_id = vDocTypeIdReturnNote
		AND C.status_doc IN (vStatusReleased,vStatusFinal)
	GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
		A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
		C.status_doc ,C.workflow_status, A.status_doc;

	--INSERT data return yang belum di submit (status doc D/I) ke tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
	   	 doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
	   	 usage_amount ,status_doc ,workflow_status ,status_doc_mou,
	     usage_amount_tax,
	     total_usage_amount)
	SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
		SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
		C.workflow_status, A.status_doc AS status_doc_mou,
		SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
		(SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN tt_sl_r_limit_detail_mou_list B ON A.mou_id = B.mou_id AND B.doc_type_id = vDocTypeIdSOByBrand
		INNER JOIN sl_request_return_sales E ON B.doc_id = E.ref_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand
		INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
		INNER JOIN in_inventory C ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
		INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
		INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
	WHERE A.session_id = pSessionId
		AND B.session_id = pSessionId
		AND C.doc_type_id = vDocTypeIdReturnNote
		AND C.status_doc IN (vStatusDraft,vStatusInProgress)
	GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
		A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
		C.status_doc ,C.workflow_status, A.status_doc;

	-- Insert data MOU yang sama sekali belum digunakan ke table tt_sl_r_limit_detail_mou_list
	INSERT INTO tt_sl_r_limit_detail_mou_list(
        session_id, tenant_id, ou_id, mou_id, doc_date_mou, doc_no_mou,
        period_from, period_to, partner_id_mou, curr_code_mou, plafon_amount,
	    doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, usage_amount,
	    status_doc, workflow_status, status_doc_mou)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		vEmptyId, vEmptyId, vEmptyValue, vEmptyValue, A.partner_id, vEmptyValue, 0 AS usage_amount,
		vEmptyValue, vEmptyValue, A.status_doc AS status_doc_mou
	FROM tt_sl_mou_list A
	WHERE A.session_id = pSessionId
		AND A.mou_id NOT IN (SELECT DISTINCT B.mou_id FROM tt_sl_r_limit_detail_mou_list B WHERE B.session_id = pSessionId);

	/* Insert data CN AR non-insentif yang dimapping ke MOU */
	INSERT INTO tt_sl_r_limit_detail_mou_list(
    	session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
     	period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
   	 	doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
   	 	status_doc ,workflow_status ,status_doc_mou,
     	usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, SUM(D.add_amount)*-1 AS usage_amount,
		vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
		SUM(D.tax_amount)*-1 AS usage_amount_tax, (SUM(D.add_amount) + SUM(D.tax_amount))*-1 AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
		INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
		INNER JOIN fi_invoice_ar_cost D ON C.invoice_ar_id = D.invoice_ar_id
	WHERE A.session_id = pSessionId
		AND C.status_doc IN (vStatusReleased,vStatusFinal)
		AND C.doc_type_id = 251
		AND f_is_invoice_ar_cost_unvalid_by_activity_gl_code(C.invoice_ar_id, vActivityGLCodeInsentif, A.tenant_id) = vFlagYes
	GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date, A.doc_no,
		A.period_from, A.period_to, A.partner_id, A.curr_code, A.plafon_amount,
		C.invoice_ar_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code,
		C.workflow_status, A.status_doc;

	/* Insert data DN AR yang dimapping ke MOU */
	INSERT INTO tt_sl_r_limit_detail_mou_list(
    	session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
     	period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
   	 	doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
   	 	status_doc ,workflow_status ,status_doc_mou,
     	usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
		A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
		C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
		vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
		C.tax_amount AS usage_amount_tax, (C.total_amount + C.tax_amount) AS total_usage_amount
	FROM tt_sl_mou_list A
		INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
		INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
	WHERE A.session_id = pSessionId
		AND C.status_doc IN (vStatusReleased,vStatusFinal)
		AND C.doc_type_id = 241;

	Open pRefHeader FOR
		SELECT 12 AS _COUNT, 'LAPORAN LIMIT CUSTOMER (DETAIL)',
			'', '', '', '', '', '',
			'', '', '', '', '',
			1 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT,
			'', '', '', '', '', '',
			'', '', '', '', '', '',
			2 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT, 'OU', ': ' || f_get_ou_name(pOuId),
			'', '', '', '',
			'', '', '', '',
			'PRINT DATE', ': ' || pDatetime,
			3 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT, 'PERIOD', ': ' || TO_CHAR(TO_DATE(pPeriodFrom,'YYYYMMDD'), 'DD/MM/YYYY') || ' s/d ' || TO_CHAR(TO_DATE(pPeriodTo,'YYYYMMDD'), 'DD/MM/YYYY'),
			'', '', '', '',
			'', '', '', '',
			'USER – ROLE', ': ' || f_get_username(pUserId) || ' - ' || f_get_role_name(pRoleId),
			4 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT, 'CUSTOMER', ': ' || vPartnerCodeMou || ' / ' || vPartnerNameMou,
			'', '', '', '',
			'', '', '', '', '', '',
			5 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT,
			'', '', '', '', '', '',
			'', '', '', '', '', '',
			6 AS ordial,
			vSeparator AS _separator

		UNION

		SELECT 12 AS _COUNT,
			'MOU No', 'MOU Date', 'Start Period', 'End Period',
			'Nilai Paket', 'Doc No', 'Doc Date', 'Currency',
			'Usage Amount', 'Sisa Plafon', 'Owner Code', 'Owner Name',
			7 AS ordial,
			vSeparator AS _separator

		ORDER BY ordial;
	RETURN NEXT pRefHeader;


	FOR vDocNoMou IN (
		SELECT A.doc_no_mou
		FROM tt_sl_r_limit_detail_mou_list A
		GROUP BY A.doc_no_mou
	) LOOP

		PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);

		INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou,
            doc_date_mou, period_from, period_to,
            curr_code_mou, plafon_amount,
            doc_no, doc_date,
            curr_code,
            total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name,
            ordial, line_no)
		SELECT pSessionId, A.doc_no_mou,
			A.doc_date_mou, A.period_from, A.period_to,
			A.curr_code_mou, CAST(A.plafon_amount AS character varying) AS plafon_amount,
			A.doc_no, A.doc_date,
		    A.curr_code,
			CAST((CASE WHEN A.doc_type_id = vDocTypeIdReturnNote THEN (A.total_usage_amount * -1)
				  ELSE A.total_usage_amount
			END) AS character varying) AS total_usage_amount,
			'', B.partner_code AS owner_code, B.partner_name AS owner_name,
			i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
		FROM tt_sl_r_limit_detail_mou_list A
			INNER JOIN m_partner B ON A.partner_id = B.partner_id
		WHERE A.session_id = pSessionId AND
			A.doc_no_mou = vDocNoMou
			--AND A.usage_amount <> 0
		ORDER BY A.doc_no_mou,B.partner_code, A.doc_date_mou, A.doc_no, A.doc_type_id, A.doc_date ASC ;

		INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
            plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
        SELECT pSessionId, '', '', '', '', '',
       		'', 'Sub Total', '',
		    A.curr_code,
			CAST(SUM(A.total_usage_amount::numeric) AS character varying) AS total_usage_amount,
			CAST((A.plafon_amount::numeric - SUM(A.total_usage_amount::numeric)) AS character varying) AS remaining_plafon_amount,
			'', '',
			i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
		FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
		WHERE A.session_id = pSessionId AND
			A.doc_no_mou = vDocNoMou
			--AND A.usage_amount <> 0
		GROUP BY A.doc_no_mou, A.curr_code, A.plafon_amount;

		INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
            plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
		VALUES (pSessionId, '', '', '', '', '',
			'', '', '', '', '',
			'', '', '',
			i, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq'));

		i := i + 1;
	END LOOP;

	PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);

	WITH tt_grouped_limit_mou AS(
		SELECT A.curr_code_mou,
		   	A.plafon_amount::numeric AS plafon_amount,
		    A.curr_code,
			SUM(A.total_usage_amount::numeric) AS total_usage_amount
		FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
		WHERE A.session_id = pSessionId AND
			A.plafon_amount <> '' AND
			A.total_usage_amount <> ''
			--AND A.usage_amount <> 0
		GROUP BY A.curr_code_mou, A.curr_code, A.plafon_amount
	)
	INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
	    session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
	    plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
	    remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
	SELECT pSessionId, 'Grand Total', '', '', '', A.curr_code_mou,
	   	CAST(SUM(A.plafon_amount) AS character varying) AS plafon_amount, '', '',
	    A.curr_code,
		CAST(SUM(A.total_usage_amount) AS character varying) AS total_usage_amount,
		CAST((SUM(A.plafon_amount) - SUM(A.total_usage_amount)) AS character varying) AS remaining_plafon_amount,
		'', '',
		i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
	FROM tt_grouped_limit_mou A
		--AND A.usage_amount <> 0
	GROUP BY A.curr_code_mou, A.curr_code
	ORDER BY A.curr_code_mou, A.curr_code;

	Open pRefDetail FOR

		SELECT 12 AS _COUNT, doc_no_mou, doc_date_mou, period_from, period_to,
			curr_code_mou || ' ' || plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
	    	remaining_plafon_amount, owner_code, owner_name, ordial
	    FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
		WHERE A.session_id = pSessionId
		ORDER BY A.ordial, A.line_no;

	RETURN NEXT pRefDetail;

	--Menghapus semua table temporrer
	DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
	DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
	DELETE FROM tt_sl_so WHERE session_id = pSessionId;
	DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
	DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /