Untitled

-- DROP FUNCTION public.r_report_daily_cashbank(varchar, int8, int8, int8, varchar, int8, int8, varchar, varchar, varchar);

CREATE OR REPLACE FUNCTION public.r_report_daily_cashbank(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
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;
	pOuId				ALIAS FOR $6;
	pCashbankId			ALIAS FOR $7;
	pDateStart			ALIAS FOR $8;
	pDateEnd			ALIAS FOR $9;
	pStatusDoc			ALIAS FOR $10;
	
	vEmptyId				                bigint;
	vEmpty					                character varying(1);
	vCashBankInType			                character varying(1);
	vCashBankOutType		                character varying(1);
	vStatusReleased			                character varying(1);
	vStatusVoid				                character varying(1);
	vRecordTypeSaldoAwal	                character varying(1);
	vRecordTypeKredit   	                character varying(1);
	vRecordTypeDebet	                    character varying(1);
	vTglAwalCashbankBalance                 character varying(8);
	vDocTypePosShop				            bigint;
	vDocTypeReturnPosShop		            bigint;
	vDocTypePosShopInshop		            bigint;
	vDocTypeReturnPosShopInShop	            bigint;
	vDocTypeCashBankOut			            bigint;
	vDocTypeCashBankInOther		            bigint;
	vDocTypeCashBankInAr		            bigint;
	vDocTypeEdcSettlement		            bigint;
	vDocTypeFollowUpCashAdvanceSettlement	bigint;
	vCount						            bigint;
	vDocTypeDirectCostPayment	            bigint;
	
	vStatusApprovedDoc						character varying;
	vStatusApprovedDocAndOsCbOut			character varying;
	vStatusApprovedDocAndOsCbIn				character varying;	
	vAllStatusDoc							character varying;
	vStatusInProgress						character varying;
	vStatusDraft							character varying;
	vInProgress								character varying;
	vApproved								character varying;
	vDraft									character varying;
	vNewLine								character varying;
	vStatusDocLabel							character varying;
	vRemarkLabel							character varying;
	vDocTypeCGRealization					bigint;
	vStatusAccept							character varying;
	
BEGIN
	
	vEmptyId := -99;
	vEmpty	 := ' ';
	vCashBankInType := 'I';
	vCashBankOutType := 'O';
	vStatusReleased := 'R';
	vStatusVoid := 'V';
	vRecordTypeSaldoAwal := 'A';
	vRecordTypeKredit := 'K';
	vRecordTypeDebet := 'D';
	vTglAwalCashbankBalance := ' ';
	vDocTypePosShop := 401;
	vDocTypeReturnPosShop := 402;
	vDocTypePosShopInShop := 403;
	vDocTypeReturnPosShopInShop := 404;
	vDocTypeCashBankOut := 611;
	vDocTypeCashBankInOther := 623;
	vDocTypeCashBankInAr := 621;
	vDocTypeEdcSettlement := 622;
	vDocTypeFollowUpCashAdvanceSettlement := 641;
	vDocTypeDirectCostPayment := 613;
	
	vStatusApprovedDoc := 'approvedDoc';
	vStatusApprovedDocAndOsCbOut := 'approvedDocAndOutstandingCBOutOnly';
	vStatusApprovedDocAndOsCbIn := 'approvedDocAndOutstandingCBInOnly';	
	vAllStatusDoc := 'allStatusDoc';
	vStatusInProgress := 'I';
	vStatusDraft := 'D';
	vInProgress := 'IN PROGRESS';
	vDraft := 'DRAFT';
	vApproved := 'APPROVED';
	vNewLine := E'\n';
	vStatusDocLabel := 'STATUS DOKUMEN: ';
	vRemarkLabel := 'REMARK: ';
	vDocTypeCGRealization := 625;
	vStatusAccept := 'ACCEPT';
	
	DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
	DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
	
	SELECT MAX(A.cash_bank_date) INTO vTglAwalCashbankBalance
	FROM cb_cashbank_balance A 
	WHERE A.tenant_id = pTenantId 
	AND A.cashbank_id = pCashbankId
	AND A.rec_type = vRecordTypeSaldoAwal
	AND A.cash_bank_date <= pDateStart;

	IF vTglAwalCashbankBalance IS null OR vTglAwalCashbankBalance IN ('', ' ') THEN
		
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, A.cashbank_id, 1, -99, 'Beginning Balance', 'Beginning Balance', pDateStart, 
		' ', ' ', ' ', ' ',
		SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
		FROM cb_cashbank_balance A
		WHERE A.tenant_id = pTenantId 
		AND A.cashbank_id = pCashbankId
		AND A.cash_bank_date < pDateStart
		GROUP BY A.cashbank_id;
			
	ELSE
	
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99, 
		'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(A.amount) AS sum_amount
		FROM cb_cashbank_balance A
		WHERE A.tenant_id = pTenantId 
		AND A.cashbank_id = pCashbankId
		AND A.cash_bank_date = vTglAwalCashbankBalance 
		AND A.rec_type = vRecordTypeSaldoAwal
		GROUP BY A.cashbank_id;
	
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99, 
		'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(CASE WHEN rec_type = vRecordTypeKredit THEN A.amount * -1 ELSE A.amount END) AS sum_amount
		FROM cb_cashbank_balance A
		WHERE A.tenant_id = pTenantId 
		AND A.cashbank_id = pCashbankId
		AND A.cash_bank_date >= vTglAwalCashbankBalance 
		AND A.cash_bank_date < pDateStart
		AND A.rec_type <> vRecordTypeSaldoAwal
		GROUP BY A.cashbank_id;
		
		--ambil nilai untuk CB trf
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99, 
		'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(A.transfer_amount)
		FROM cb_transfer_cashbank A
		WHERE A.tenant_id = pTenantId
		AND A.cashbank_id = pCashbankId
		AND A.status_doc <> vStatusReleased
		AND A.doc_date >= vTglAwalCashbankBalance 
		AND A.doc_date < pDateStart
		GROUP BY A.cashbank_id;
		
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99, 
		'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(B.payment_amount)
		FROM cb_transfer_cashbank A
		RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
		WHERE A.tenant_id = pTenantId
		AND A.cashbank_id = pCashbankId
		AND A.status_doc <> vStatusReleased
		AND A.doc_date >= vTglAwalCashbankBalance 
		AND A.doc_date < pDateStart
		GROUP BY A.cashbank_id;
		
		--ambil nilai untuk CB out
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, B.cashbank_id, -1,
		-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(B.cashbank_amount)
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date >= vTglAwalCashbankBalance
		AND A.doc_date < pDateStart
		AND A.status_doc <> vStatusReleased
		AND A.doc_type_id = vDocTypeCashBankOut
		GROUP BY B.cashbank_id;

		--ambil nilai untuk FU CAS
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, B.cashbank_id, -1,
		-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(B.cashbank_amount)
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date >= vTglAwalCashbankBalance
		AND A.doc_date < pDateStart
		AND A.status_doc <> vStatusReleased
		AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
		AND A.ref_amount > 0
		GROUP BY B.cashbank_id;

		--ambil nilai untuk direct cost payment
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, B.cashbank_id, -1,
		-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		SUM(B.cashbank_amount)
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date >= vTglAwalCashbankBalance
		AND A.doc_date < pDateStart
		AND A.status_doc <> vStatusReleased
		AND A.doc_type_id = vDocTypeDirectCostPayment
		GROUP BY B.cashbank_id;

		--Adrian, Sep 19, 2017, ambil nilai untuk O/S CB out
		IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
			OR (pStatusDoc = vAllStatusDoc) THEN

			--ambil nilai untuk O/S CB trf
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
			'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * A.transfer_amount)
			FROM cb_transfer_cashbank A
			WHERE A.tenant_id = pTenantId
			AND A.cashbank_id = pCashbankId
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			GROUP BY A.cashbank_id;

			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, A.cashbank_id, -1, -99,
			'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * B.payment_amount)
			FROM cb_transfer_cashbank A
			RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND A.cashbank_id = pCashbankId
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			GROUP BY A.cashbank_id;

			--ambil nilai untuk O/S CB out
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * B.cashbank_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeCashBankOut
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S FU CAS
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * B.cashbank_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
			AND A.ref_amount > 0
			GROUP BY B.cashbank_id;

			--ambil nilai untuk Direct Cost Payment
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * B.cashbank_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeDirectCostPayment
			GROUP BY B.cashbank_id;

		END IF;

		--Adrian, Sep 19, 2017, ambil nilai untuk O/S CB in
		IF (pStatusDoc = vStatusApprovedDocAndOsCbIn)
			OR (pStatusDoc = vAllStatusDoc) THEN

			--ambil nilai untuk O/S CB trf
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(A.receive_amount)
			FROM cb_transfer_cashbank_receive A
			INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
			WHERE B.tenant_id = pTenantId
			AND A.cashbank_to_id = pCashbankId
			AND B.status_doc IN (vStatusDraft, vStatusInProgress)
			AND B.doc_date >= vTglAwalCashbankBalance
			AND B.doc_date < pDateStart
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S CB in AR
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(B.cashbank_amount) - SUM(COALESCE(C.cost_amount, 0))
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeCashBankInAr
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S CB in other
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(B.cashbank_amount) - SUM(COALESCE(C.cost_amount, 0))
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeCashBankInOther
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S EDC Settlement
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(B.cashbank_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
			LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeEdcSettlement
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S FU CAS
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(B.cashbank_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
			AND A.ref_amount < 0
			GROUP BY B.cashbank_id;

			--ambil nilai untuk O/S CG Realization
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(B.cheque_giro_amount)
			FROM cb_in_out_cashbank A
			INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeCGRealization
			AND B.realization_status = vStatusAccept
			GROUP BY B.cashbank_id;

			--ambil nilai untuk Cost O/S CG Realization
			WITH tt_in_out_cashbank AS(
				SELECT A.in_out_cashbank_id, B.cashbank_id
				FROM cb_in_out_cashbank A
				INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
				GROUP BY A.in_out_cashbank_id, B.cashbank_id
			)
			INSERT INTO tr_daily_cashbank
			(session_id, tenant_id, cashbank_id, order_type_data,
			doc_type_id, doc_type_desc, doc_no, doc_date,
			mode_payment, bank_payment, no_payment, date_payment,
			amount)
			SELECT pSessionId, pTenantId, B.cashbank_id, -1,
			-99, 'Beginning Balance', 'Beginning Balance', pDateStart,
			' ', ' ', ' ', ' ',
			SUM(-1 * COALESCE(C.cost_amount, 0))
			FROM cb_in_out_cashbank A
			INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
			WHERE A.tenant_id = pTenantId
			AND B.cashbank_id = pCashbankId
			AND A.doc_date >= vTglAwalCashbankBalance
			AND A.doc_date < pDateStart
			AND A.status_doc IN (vStatusDraft, vStatusInProgress)
			AND A.doc_type_id = vDocTypeCGRealization
			--AND B.realization_status = vStatusAccept
			GROUP BY B.cashbank_id;

		END IF;

		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, cashbank_id, 1,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		SUM(amount)
		FROM tr_daily_cashbank
		WHERE session_id = pSessionId
		AND tenant_id = pTenantId
		AND order_type_data = -1
		GROUP BY cashbank_id, order_type_data, doc_type_id, doc_type_desc, doc_no, doc_date, mode_payment, bank_payment, no_payment, date_payment;

		DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId AND order_type_data = -1;

	END IF;

	-- cb trf
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark)
	SELECT pSessionId, pTenantId, pCashbankId, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
	-1 * A.transfer_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark
	FROM cb_transfer_cashbank A
	WHERE A.tenant_id = pTenantId
	AND A.cashbank_id = pCashbankId
	AND A.status_doc = vStatusReleased
	AND A.doc_date BETWEEN pDateStart AND pDateEnd;

	/*
	 * PS, 27 Maret 2015
	 * update amount pada tr_daily_cashbank, dikurangi dengan cost_amount dari cb_transfer_cashbank_cost
	 * memakai RIGHT JOIN karena transaksi di cb_transfer_cashbank_cost bisa kosong untuk sebuah transfer cashbank
	 */
--	UPDATE tr_daily_cashbank C
--	SET amount = C.amount - B.cost_amount
--	FROM cb_transfer_cashbank A
--	RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
--	WHERE A.tenant_id = pTenantId
--	AND A.cashbank_id = pCashbankId
--	AND A.status_doc = vStatusReleased
--	AND A.doc_date BETWEEN pDateStart AND pDateEnd;

	UPDATE tr_daily_cashbank C
	SET amount = C.amount - B.payment_amount
	FROM cb_transfer_cashbank A
	RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
	WHERE A.tenant_id = pTenantId
	AND A.cashbank_id = pCashbankId
	AND A.doc_no = C.doc_no
	AND A.doc_date = C.doc_date
	AND A.doc_type_id = C.doc_type_id
	AND A.status_doc = vStatusReleased
	AND A.doc_date BETWEEN pDateStart AND pDateEnd;

	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark)
	SELECT pSessionId, pTenantId, pCashbankId, 2,
	B.doc_type_id, f_get_doc_desc(B.doc_type_id), B.doc_no, B.doc_date,
	A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
	A.receive_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark
	FROM cb_transfer_cashbank_receive A
	INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
	WHERE B.tenant_id = pTenantId
	AND A.cashbank_to_id = pCashbankId
	AND B.status_doc = vStatusReleased
	AND B.doc_date BETWEEN pDateStart AND pDateEnd;

	-- cash bank out
	/* Note : WTC & TKP 24 Mei 2016
	 * tidak perlu join ke cb_in_out_cashbank_cost
	 * dan untuk join ke cb_trx_cashbank_balance langsung di inner join aja
	 */
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
	(-1 * B.cashbank_amount), vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
	f_get_partner_name(A.partner_id) AS partner_name
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeCashBankOut
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

	-- cash bank in AR
	/* Note : WTC & TKP 24 Mei 2016
	 * tidak perlu join ke cb_trx_cashbank_balance
	 */
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
	B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
	vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeCashBankInAr
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

	-- cash bank in other
	/* Note : WTC & TKP 24 Mei 2016
	 * tidak perlu join ke cb_trx_cashbank_balance
	 *
	 * Note : Didit 24 Agustus 2016
	 * Perhitungkan juga dokumen yang statusnya void karena hasil konversi
	 */
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
	B.cashbank_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
	f_get_partner_name(A.partner_id)
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc IN (vStatusReleased, vStatusVoid)
	AND A.doc_type_id = vDocTypeCashBankInOther
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

	-- edc settlement
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, A.doc_no, A.doc_date,
	B.cashbank_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
	f_get_partner_name(A.partner_id)
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeEdcSettlement
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id;

	-- Added by Citrahadi, 2015-10-01
	-- follow up cash advance settlement
	-- ref_amount minus artinya follow up dgn cashbank in
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
	ref_doc_date, partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
	CASE WHEN A.ref_amount < 0  THEN B.cashbank_amount ELSE (-1 * B.cashbank_amount) END, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
	D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
        B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount;

    --Added by Adrian, Sep 22, 2017 cheque giro realization
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), D.cheque_giro_no, D.cheque_giro_date,
	D.mode_payment, B.bank_payment, B.cheque_giro_no, B.realization_date,
	B.cheque_giro_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || D.remark, A.in_out_cashbank_id, A.doc_type_id, A.doc_no, A.doc_date,
	f_get_partner_name(B.partner_id)
	FROM cb_in_out_cashbank A
	INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeCGRealization
	AND B.realization_status = vStatusAccept
	GROUP BY B.cashbank_id, A.doc_type_id, D.cheque_giro_no, D.cheque_giro_date, D.mode_payment, B.bank_payment, B.cheque_giro_no, B.realization_date, A.type_in_out_cashbank, B.cheque_giro_amount, D.remark, A.in_out_cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.partner_id;

    --Added by Adrian, Sep 22, 2017 cost cheque giro realization
    WITH tt_in_out_cashbank AS(
		SELECT A.in_out_cashbank_id, B.cashbank_id
		FROM cb_in_out_cashbank A
		INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		GROUP BY A.in_out_cashbank_id, B.cashbank_id
	)
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	vEmpty, vEmpty, vEmpty, vEmpty,
	SUM(-1 * COALESCE(C.cost_amount, 0)), vRemarkLabel || C.remark, vEmptyId, vEmptyId, vEmpty, vEmpty,
	vEmpty
	FROM cb_in_out_cashbank A
	INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeCGRealization
	--AND B.realization_status = vStatusAccept
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, A.type_in_out_cashbank, C.cost_amount, C.remark, A.in_out_cashbank_id;

	-- direct cost payment
	/* Note : WTC & TKP 23 Juli 2020
	 * tidak perlu join ke cb_trx_cashbank_balance
	 */
	INSERT INTO tr_daily_cashbank
	(session_id, tenant_id, cashbank_id, order_type_data,
	doc_type_id, doc_type_desc, doc_no, doc_date,
	mode_payment, bank_payment, no_payment, date_payment,
	amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
	partner_name)
	SELECT pSessionId, pTenantId, B.cashbank_id, 2,
	A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
	B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
	(-1 * B.cashbank_amount), vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
	f_get_partner_name(A.partner_id) AS partner_name
	FROM cb_in_out_cashbank A
	INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
	LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
	LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
	WHERE A.tenant_id = pTenantId
	AND B.cashbank_id = pCashbankId
	AND A.doc_date BETWEEN pDateStart AND pDateEnd
	AND A.status_doc = vStatusReleased
	AND A.doc_type_id = vDocTypeDirectCostPayment
	GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.partner_id,
	    A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date;

    -- POS Resto Trf Payment (approved)
    INSERT INTO tr_daily_cashbank
    	(session_id, tenant_id, cashbank_id, order_type_data,
    	doc_type_id, doc_type_desc, doc_no, doc_date,
    	mode_payment, bank_payment, no_payment, date_payment,
    	amount, remark,
    	ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date)
    SELECT pSessionId, pTenantId, pCashbankId, 3,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
        'TT', vEmpty, vEmpty, vEmpty,
        B.payment_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark,
        A.ref_id, A.ref_doc_type_id, vEmpty, vEmpty
	FROM i_trx_pos_with_approval A
    INNER JOIN i_trx_pos_transfer_payment B ON A.trx_pos_with_approval_id = B.trx_pos_id AND
                                                   A.tenant_id = B.tenant_id AND
                                                   A.doc_no = B.process_no
    INNER JOIN m_cashbank_ou E ON B.cashbank_id = E.cashbank_id
    WHERE A.tenant_id = pTenantId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypePosShop) AND
        E.cashbank_id = pCashbankId AND
        A.status_doc = vStatusReleased;

    -- POS Resto Cash Payment (approved)
    INSERT INTO tr_daily_cashbank
        (session_id, tenant_id, cashbank_id, order_type_data,
        doc_type_id, doc_type_desc, doc_no, doc_date,
        mode_payment, bank_payment, no_payment, date_payment,
        amount, remark,
        ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date)
    SELECT pSessionId, pTenantId, pCashbankId, 3,
        A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
        'CASH', vEmpty, vEmpty, vEmpty,
        B.payment_amount, vStatusDocLabel || vApproved || vNewLine || vRemarkLabel || A.remark,
        A.ref_id, A.ref_doc_type_id, vEmpty, vEmpty
    FROM i_trx_pos_with_approval A
    INNER JOIN i_trx_pos_cash_payment B ON A.trx_pos_with_approval_id = B.trx_pos_id AND
                                                   A.tenant_id = B.tenant_id AND
                                                   A.doc_no = B.process_no
    INNER JOIN m_cashbank_ou E ON A.ou_id = E.ou_id
    WHERE A.tenant_id = pTenantId AND
        A.doc_date BETWEEN pDateStart AND pDateEnd AND
        A.doc_type_id IN (vDocTypePosShop) AND
        E.cashbank_id = pCashbankId AND
        A.status_doc = vStatusReleased;



    --Adrian, Sep 19, 2017, O/S cash bank out
	IF (pStatusDoc = vStatusApprovedDocAndOsCbOut)
		OR (pStatusDoc = vAllStatusDoc) THEN

		-- O/S cb trf
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark)
		SELECT pSessionId, pTenantId, pCashbankId, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
		-1 * A.transfer_amount,
		vStatusDocLabel ||
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END
		|| vNewLine || vRemarkLabel || A.remark AS remark
		FROM cb_transfer_cashbank A
		WHERE A.tenant_id = pTenantId
		AND A.cashbank_id = pCashbankId
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_date BETWEEN pDateStart AND pDateEnd;

		UPDATE tr_daily_cashbank C
		SET amount = C.amount - B.payment_amount
		FROM cb_transfer_cashbank A
		RIGHT JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id=B.transfer_cashbank_id
		WHERE A.tenant_id = pTenantId
		AND A.cashbank_id = pCashbankId
		AND A.doc_no = C.doc_no
		AND A.doc_date = C.doc_date
		AND A.doc_type_id = C.doc_type_id
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_date BETWEEN pDateStart AND pDateEnd;

		--O/S cash bank out
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		(-1 * B.cashbank_amount),
		vStatusDocLabel ||
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
		f_get_partner_name(A.partner_id) AS partner_name
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeCashBankOut
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;

		--O/S follow up cash advance settlement
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
		ref_doc_date, partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		(-1 * B.cashbank_amount),
		vStatusDocLabel ||
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
		D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
		AND A.ref_amount > 0
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
	        B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount, A.status_doc;

		--direct cost payment
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date,
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		(-1 * B.cashbank_amount),
		vStatusDocLabel ||
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,
		f_get_partner_name(A.partner_id) AS partner_name
		FROM cb_in_out_cashbank A
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeDirectCostPayment
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		    A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;

	END IF;
	
	--Adrian, Sep 19, 2017, O/S cash bank in
	IF (pStatusDoc = vStatusApprovedDocAndOsCbIn) 
		OR (pStatusDoc = vAllStatusDoc) THEN
		
		--O/S cb trf
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark)
		SELECT pSessionId, pTenantId, pCashbankId, 2, 
		B.doc_type_id, f_get_doc_desc(B.doc_type_id), B.doc_no, B.doc_date,
		A.mode_payment, A.bank_payment, A.no_payment, A.date_payment,
		A.receive_amount, 
		vStatusDocLabel || 
		CASE WHEN (B.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END	
		|| vNewLine || vRemarkLabel || A.remark AS remark
		FROM cb_transfer_cashbank_receive A
		INNER JOIN cb_transfer_cashbank B ON A.transfer_cashbank_id = B.transfer_cashbank_id
		WHERE B.tenant_id = pTenantId
		AND A.cashbank_to_id = pCashbankId
		AND B.status_doc IN (vStatusDraft, vStatusInProgress)
		AND B.doc_date BETWEEN pDateStart AND pDateEnd;

        -- POS Resto Trf Payment (outstanding)
        INSERT INTO tr_daily_cashbank
        	(session_id, tenant_id, cashbank_id, order_type_data,
        	doc_type_id, doc_type_desc, doc_no, doc_date,
        	mode_payment, bank_payment, no_payment, date_payment, amount,
        	remark,
        	ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date)
        SELECT pSessionId, pTenantId, pCashbankId, 3,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
            'TT', vEmpty, vEmpty, vEmpty, B.payment_amount,
            vStatusDocLabel || CASE WHEN (A.status_doc = vStatusDraft) THEN
                    vDraft
                ELSE
                    vInProgress
                END
                || vNewLine || vRemarkLabel || A.remark AS remark,
            A.ref_id, A.ref_doc_type_id, vEmpty, vEmpty
    	FROM i_trx_pos_with_approval A
        INNER JOIN i_trx_pos_transfer_payment B ON A.trx_pos_with_approval_id = B.trx_pos_id AND
                                                       A.tenant_id = B.tenant_id AND
                                                       A.doc_no = B.process_no
        INNER JOIN m_cashbank_ou E ON B.cashbank_id = E.cashbank_id
        WHERE A.tenant_id = pTenantId AND
            A.doc_date BETWEEN pDateStart AND pDateEnd AND
            A.doc_type_id IN (vDocTypePosShop) AND
            E.cashbank_id = pCashbankId AND
            A.status_doc IN (vStatusDraft, vStatusInProgress);

        -- POS Resto Cash Payment (outstanding)
        INSERT INTO tr_daily_cashbank
            (session_id, tenant_id, cashbank_id, order_type_data,
            doc_type_id, doc_type_desc, doc_no, doc_date,
            mode_payment, bank_payment, no_payment, date_payment, amount,
            remark,
            ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date)
        SELECT pSessionId, pTenantId, pCashbankId, 3,
            A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
            'CASH', vEmpty, vEmpty, vEmpty, B.payment_amount,
            vStatusDocLabel || CASE WHEN (A.status_doc = vStatusDraft) THEN
                    vDraft
                ELSE
                    vInProgress
                END
                || vNewLine || vRemarkLabel || A.remark AS remark,
            A.ref_id, A.ref_doc_type_id, vEmpty, vEmpty
        FROM i_trx_pos_with_approval A
        INNER JOIN i_trx_pos_cash_payment B ON A.trx_pos_with_approval_id = B.trx_pos_id AND
                                                       A.tenant_id = B.tenant_id AND
                                                       A.doc_no = B.process_no
        INNER JOIN m_cashbank_ou E ON A.ou_id = E.ou_id
        WHERE A.tenant_id = pTenantId AND
            A.doc_date BETWEEN pDateStart AND pDateEnd AND
            A.doc_type_id IN (vDocTypePosShop) AND
            E.cashbank_id = pCashbankId AND
            A.status_doc IN (vStatusDraft, vStatusInProgress);
		
		--O/S cash bank in AR
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		B.cashbank_amount - SUM(COALESCE(C.cost_amount, 0)),
		vStatusDocLabel || 
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END	
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, f_get_partner_name(A.partner_id)
		FROM cb_in_out_cashbank A 
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id 
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id 
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeCashBankInAr
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
		
		--O/S cash bank in other
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		B.cashbank_amount, 
		vStatusDocLabel || 
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END	
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,	
		f_get_partner_name(A.partner_id)
		FROM cb_in_out_cashbank A 
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id 
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id 
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeCashBankInOther
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
		
		-- edc settlement
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, A.doc_no, A.doc_date,
		B.cashbank_amount, 
		vStatusDocLabel || 
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END	
		|| vNewLine || vRemarkLabel || A.remark AS remark, 
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date,	
		f_get_partner_name(A.partner_id)
		FROM cb_in_out_cashbank A 
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id 
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeEdcSettlement
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank, B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.status_doc;
		
		--O/S follow up cash advance settlement
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no,
		ref_doc_date, partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		B.mode_payment, B.bank_payment, B.no_payment, B.date_payment,
		B.cashbank_amount, 
		vStatusDocLabel || 
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END	
		|| vNewLine || vRemarkLabel || A.remark AS remark,
		A.ref_id, A.ref_doc_type_id, D.payment_doc_no,
		D.payment_doc_date, f_get_partner_name(A.partner_id) AS partner_name
		FROM cb_in_out_cashbank A 
		INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id 
		LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
		LEFT OUTER JOIN cb_trx_cashbank_balance D ON A.ref_doc_type_id = D.doc_type_id AND A.ref_id = D.trx_cashbank_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeFollowUpCashAdvanceSettlement
		AND A.ref_amount < 0
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.mode_payment, B.bank_payment, B.no_payment, B.date_payment, A.type_in_out_cashbank,
	        B.cashbank_amount, A.remark, A.ref_id, A.ref_doc_type_id, D.payment_doc_no, D.payment_doc_date, A.partner_id, A.ref_amount, A.status_doc;
	        
	    --O/S cheque giro realization
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), D.cheque_giro_no, D.cheque_giro_date,
		D.mode_payment, B.bank_payment, B.cheque_giro_no, B.realization_date,
		B.cheque_giro_amount, vStatusDocLabel || 
		CASE WHEN (A.status_doc = vStatusDraft) THEN
			vDraft
		ELSE
			vInProgress
		END 
		|| vNewLine || vRemarkLabel || D.remark, A.in_out_cashbank_id, A.doc_type_id, A.doc_no, A.doc_date,	
		f_get_partner_name(B.partner_id)
		FROM cb_in_out_cashbank A 
		INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		INNER JOIN cb_cheque_giro_balance D ON B.ref_balance_id = D.cheque_giro_balance_id
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeCGRealization
		AND B.realization_status = vStatusAccept
		GROUP BY B.cashbank_id, A.doc_type_id, D.cheque_giro_no, D.cheque_giro_date, D.mode_payment, B.bank_payment, B.cheque_giro_no, B.realization_date, A.type_in_out_cashbank, B.cheque_giro_amount, D.remark, A.in_out_cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, B.partner_id;
		
	    --O/S cost cheque giro realization
	    WITH tt_in_out_cashbank AS(
			SELECT A.in_out_cashbank_id, B.cashbank_id
			FROM cb_in_out_cashbank A
			INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
			GROUP BY A.in_out_cashbank_id, B.cashbank_id
		)
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount, remark, ref_id, ref_doc_type_id, ref_doc_no, ref_doc_date,
		partner_name)
		SELECT pSessionId, pTenantId, B.cashbank_id, 2,
		A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		vEmpty, vEmpty, vEmpty, vEmpty,
		SUM(-1 * COALESCE(C.cost_amount, 0)), vRemarkLabel || C.remark, vEmptyId, vEmptyId, vEmpty, vEmpty,	
		vEmpty
		FROM cb_in_out_cashbank A 
		INNER JOIN tt_in_out_cashbank B ON A.in_out_cashbank_id = B.in_out_cashbank_id
		INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id 
		WHERE A.tenant_id = pTenantId
		AND B.cashbank_id = pCashbankId
		AND A.doc_date BETWEEN pDateStart AND pDateEnd
		AND A.status_doc IN (vStatusDraft, vStatusInProgress)
		AND A.doc_type_id = vDocTypeCGRealization
		--AND B.realization_status = vStatusAccept
		GROUP BY B.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date, A.type_in_out_cashbank, C.cost_amount, C.remark, A.in_out_cashbank_id;
		
	END IF;
        
	/*
	 * ambil data transaksi penjualan pos dan return pos untuk cash payment
	 */	
	INSERT INTO tr_kartu_kas_by_doc_type
	(session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id, 
	doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
	amount_debit, amount_credit)
	SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
		A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
		SUM(B.conversion_amount), 0
	FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
	WHERE A.tenant_id = pTenantId AND
		--A.ou_id = pOuId AND
		A.doc_date BETWEEN pDateStart AND pDateEnd AND
		A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
		A.trx_pos_id = B.trx_pos_id AND
		A.tenant_id = B.tenant_id AND
		A.process_no = B.process_no AND
		B.curr_payment_code = D.curr_code AND
		D.flg_cash_bank = 'C' AND
		D.cashbank_id = E.cashbank_id AND
		A.ou_id = E.ou_id AND
		A.doc_type_id = C.doc_type_id AND
		D.cashbank_id = pCashbankId
    -- exclude data Cash dari POS Resto
        AND NOT EXISTS(
            SELECT 1
            FROM tr_daily_cashbank F
            WHERE A.doc_no = F.doc_no AND
            A.doc_type_id = F.doc_type_id AND
            D.cashbank_id = F.cashbank_id AND
            F.mode_payment = 'CASH'
        )
	GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

	INSERT INTO tr_kartu_kas_by_doc_type
	(session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id, 
	doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
	amount_debit, amount_credit)
	SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
		A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
		SUM(B.conversion_amount), 0
	FROM i_trx_pos A, i_trx_pos_cash_payment B, m_document C, m_cashbank D, m_cashbank_ou E
	WHERE A.tenant_id = pTenantId AND
		--A.ou_id = pOuId AND
		A.doc_date BETWEEN pDateStart AND pDateEnd AND
		A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
		A.trx_pos_id = B.trx_pos_id AND
		A.tenant_id = B.tenant_id AND
		A.process_no = B.process_no AND
		B.curr_payment_code = D.curr_code AND
		D.flg_cash_bank = 'C' AND
		D.cashbank_id = E.cashbank_id AND
		A.ou_id = E.ou_id AND
		A.doc_type_id = C.doc_type_id AND
		D.cashbank_id = pCashbankId
	GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

	/*
	 * ambil data transaksi penjualan pos dan return pos untuk pengembalian cash
	 */	
	INSERT INTO tr_kartu_kas_by_doc_type
	(session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id, 
	doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
	amount_debit, amount_credit)
	SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
		A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
		0, SUM(A.total_refund)
	FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
	WHERE A.tenant_id = pTenantId AND
		--A.ou_id = pOuId AND
		A.doc_date BETWEEN pDateStart AND pDateEnd AND
		A.doc_type_id IN (vDocTypePosShop, vDocTypePosShopInShop) AND
		A.curr_code = D.curr_code AND
		D.flg_cash_bank = 'C' AND
		D.cashbank_id = E.cashbank_id AND
		A.ou_id = E.ou_id AND
		A.doc_type_id = C.doc_type_id AND
		D.cashbank_id = pCashbankId
    -- exclude data Cash dari POS Resto
        AND NOT EXISTS(
            SELECT 1
            FROM tr_daily_cashbank F
            WHERE A.doc_no = F.doc_no AND
            A.doc_type_id = F.doc_type_id AND
            D.cashbank_id = F.cashbank_id AND
            F.mode_payment = 'CASH'
        )
	GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;

	INSERT INTO tr_kartu_kas_by_doc_type
	(session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id, 
	doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
	amount_debit, amount_credit)
	SELECT pSessionId, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
		A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
		0, SUM(A.total_refund)
	FROM i_trx_pos A, m_document C, m_cashbank D, m_cashbank_ou E
	WHERE A.tenant_id = pTenantId AND
		--A.ou_id = pOuId AND
		A.doc_date BETWEEN pDateStart AND pDateEnd AND
		A.doc_type_id IN (vDocTypeReturnPosShop, vDocTypeReturnPosShopInShop) AND
		A.curr_code = D.curr_code AND
		D.flg_cash_bank = 'C' AND
		D.cashbank_id = E.cashbank_id AND
		A.ou_id = E.ou_id AND
		A.doc_type_id = C.doc_type_id AND
		D.cashbank_id = pCashbankId
	GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.curr_code;
	
	INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
	SELECT pSessionId, pTenantId, A.cashbank_id, 2,
		   A.doc_type_id, f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date,
		   'CASH', ' ', ' ', ' ',
		   SUM(A.amount_debit) - SUM(A.amount_credit)
	FROM tr_kartu_kas_by_doc_type A
	WHERE A.session_id = pSessionId
	GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;
	
	INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_id, doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
	SELECT pSessionId, pTenantId, A.cashbank_id, 2,
		   A.doc_type_id, f_get_doc_desc(A.doc_type_id), 'VOID ' || A.doc_no, A.doc_date,
		   'CASH', ' ', ' ', ' ',
		   -1 * (SUM(A.amount_debit) - SUM(A.amount_credit))
	FROM tr_kartu_kas_by_doc_type A	
	WHERE A.session_id = pSessionId
    AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
					WHERE A.tenant_id = B.tenant_id AND
					A.doc_no = B.doc_no AND
					A.doc_date = B.doc_date AND
					A.ou_id = B.ou_id)
	GROUP BY A.cashbank_id, A.doc_type_id, A.doc_no, A.doc_date;

	SELECT COUNT(1) INTO vCount
	FROM tr_daily_cashbank
	WHERE session_id = pSessionId AND order_type_data = 1;

	IF vCount = 0 THEN
		INSERT INTO tr_daily_cashbank
		(session_id, tenant_id, cashbank_id, order_type_data,
		doc_type_desc, doc_no, doc_date, 
		mode_payment, bank_payment, no_payment, date_payment,
		amount)
		SELECT pSessionId, pTenantId, pCashbankId, 1, 'Beginning Balance', 'Beginning Balance', pDateStart,
		' ', ' ', ' ', ' ',
		0;
	END IF;
					
	Open pRefHeader FOR
	SELECT pOuId AS ou_id, f_get_ou_code(pOuId) AS ou_code, f_get_ou_name(pOuId) AS ou_name, pDateStart AS date_start, pDateEnd AS date_end,
		   pDateStart AS date_trx, f_get_username(pUserId) AS username, pDatetime AS datetime,
		   pCashbankId AS cashbank_id, A.cashbank_code AS cashbank_code, A.cashbank_name AS cashbank_name,
		   A.bank_code AS bank_code, A.account_no AS account_no, A.account_name1 AS account_name1, A.account_name2 AS account_name2,
		   A.curr_code AS curr_code
	FROM m_cashbank A WHERE A.cashbank_id = pCashBankId;
		   
	RETURN NEXT pRefHeader;

	Open pRefDetail FOR
	SELECT A.doc_type_desc AS doc_type_desc, A.doc_date AS doc_date, A.doc_no AS doc_no, 
		   A.mode_payment AS mode_payment, A.bank_payment AS bank_payment, 
		   A.ref_doc_date AS ref_doc_date, A.ref_doc_no AS ref_doc_no, 
		   CASE WHEN ((A.no_payment = '-') OR (A.no_payment = 'null')) THEN '' ELSE A.no_payment END AS no_payment,
		   CASE WHEN (A.amount > 0 ) THEN A.amount ELSE 0 END AS debit,
		   CASE WHEN (A.amount < 0 ) THEN -1 * A.amount ELSE 0 END AS credit,
		   SUM(A.amount) OVER (ORDER BY A.order_type_data, A.doc_date, A.amount DESC, A.doc_no, A.daily_cashbank_id) AS amount,
		   A.remark, A.partner_name, 
		   (CASE WHEN length(TRIM(A.remark)) = 0 THEN (CEIL(1/36.0))*21 ELSE ((CEIL(length(SPLIT_PART(TRIM(A.remark), vNewLine, 2))/36.0))*21)+21 END) AS height
	FROM tr_daily_cashbank A
	WHERE A.session_id = pSessionId
	AND A.tenant_id = pTenantId
	ORDER BY A.order_type_data, A.doc_date, A.amount DESC, A.doc_no, A.daily_cashbank_id;
	
	RETURN NEXT pRefDetail;
	
	DELETE FROM tr_daily_cashbank WHERE session_id = pSessionId;
	DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
	
END;
$function$
;