r_cash_projection

CREATE OR REPLACE FUNCTION r_cash_projection(bigint, bigint, bigint, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
	pRefHeader			REFCURSOR := 'refHeader';
	pRefDetail			REFCURSOR := 'refDetail';
	pRefDetail2			REFCURSOR := 'refDetail2';
	pRefDetail3			REFCURSOR := 'refDetail3';
	pTenantId				ALIAS FOR $1;
	pOuId					ALIAS FOR $2;
	pUserId					ALIAS FOR $3;
	pDatetime				ALIAS FOR $4;
	pSessionId				ALIAS FOR $5;
	pFlgDueDateAr			ALIAS FOR $6; -- new due date AR : N , original due date AR : O

	vFlagYes				character varying(1);
	vFlagNo					character varying(1);
	vResult					numeric;
	vEmptyId				numeric;
	vCurrIdr				character varying(3);
	vCurrUsd				character varying(3);
	vAmount					numeric;
	vTax					numeric;
BEGIN
	vFlagNo := 'N';
	vFlagYes := 'Y';
	vCurrIdr := 'IDR';
	vCurrUsd := 'USD';
	vAmount := 0;
	vTax := 0;
	vResult := 0;
	vEmptyId := -99;

	/**
	*1. Ambil cash position idr & usd, letakkan di current, simpan di tt_report_cash_projection
	*2. Jumlahkan di total cash
	*3. Ambil data untuk projected cash in : Outstanding AR, Accr AR, outstanding Cash Bank In
	*4. Ambil data untuk projected cash out : Outstanding AP, Accr AP, outstanding Payment Order AP,
	*   outstanding Payment Order Cash Bank
	**/



	-- 1. Ambil cash position idr & usd, letakkan di current, simpan di tt_report_cash_projection
	-- cash position idr
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Cash Position' AS projected_transaction_type,
	A.cashbank_name AS projected_transaction_name,
	0 AS overdue_idr,
	0 AS overdue_usd,
	f_get_cashbank_balance(A.cashbank_id, pDatetime) AS current_idr,
	0 AS current_usd,
	0 AS not_yet_due_idr,
	0 AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd
	FROM m_cashbank A
	INNER JOIN m_cashbank_ou B ON A.cashbank_id = B.cashbank_id
	WHERE A.curr_code = vCurrIdr
	AND B.ou_id = pOuId;

	-- cash position usd
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Cash Position' AS projected_transaction_type,
	A.cashbank_name AS projected_transaction_name,
	0 AS overdue_idr,
	0 AS overdue_usd,
	0 AS current_idr,
	f_get_cashbank_balance(A.cashbank_id, pDatetime) AS current_usd,
	0 AS not_yet_due_idr,
	0 AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd
	FROM m_cashbank A
	INNER JOIN m_cashbank_ou B ON A.cashbank_id = B.cashbank_id
	WHERE A.curr_code = vCurrUsd
	AND B.ou_id = pOuId;

	--3. Ambil data untuk projected cash in : Outstanding AR, Accr AR
	--Outstanding AR
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash In' AS projected_transaction_type,
	'Outstanding AR' AS projected_transaction_name,
	f_get_overdue_outstanding_ar_amount(pDatetime, vCurrIdr, pOuId, pFlgDueDateAr) AS overdue_idr,
	f_get_overdue_outstanding_ar_amount(pDatetime, vCurrUsd, pOuId, pFlgDueDateAr) AS overdue_usd,
	f_get_current_due_outstanding_ar_amount(pDatetime, vCurrIdr, pOuId, pFlgDueDateAr) AS current_idr,
	f_get_current_due_outstanding_ar_amount(pDatetime, vCurrUsd, pOuId, pFlgDueDateAr) AS current_usd,
	f_get_not_yet_due_outstanding_ar_amount(pDatetime, vCurrIdr, pOuId, pFlgDueDateAr) AS not_yet_due_idr,
	f_get_not_yet_due_outstanding_ar_amount(pDatetime, vCurrUsd, pOuId, pFlgDueDateAr) AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd;

	--Accr AR
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash In' AS projected_transaction_type,
	'Accr AR' AS projected_transaction_name,
	0 AS overdue_idr,
	0 AS overdue_usd,
	0 AS current_idr,
	0 AS current_usd,
	f_get_not_yet_due_accr_ar_amount(pDatetime, vCurrIdr, pOuId) AS not_yet_due_idr,
	f_get_not_yet_due_accr_ar_amount(pDatetime, vCurrUsd, pOuId) AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd;

	--Accr POS Shop in Shop
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash In' AS projected_transaction_type,
	'Accr POS Shop in Shop' AS projected_transaction_name,
	0 AS overdue_idr,
	0 AS overdue_usd,
	0 AS current_idr,
	0 AS current_usd,
	f_get_not_yet_due_accr_pos_shop_in_shop_amount(pDatetime, vCurrIdr, pOuId) AS not_yet_due_idr,
	f_get_not_yet_due_accr_pos_shop_in_shop_amount(pDatetime, vCurrUsd, pOuId) AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd;


	--outstanding CBI
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash In' AS projected_transaction_type,
	'Outstanding Cash Bank In' AS projected_transaction_name,
	f_get_overdue_outstanding_cash_bank_in_amount(pDatetime, vCurrIdr, pOuId) *(-1),
	f_get_overdue_outstanding_cash_bank_in_amount(pDatetime, vCurrUsd, pOuId) *(-1),
	f_get_current_due_outstanding_cash_bank_in_amount(pDatetime, vCurrIdr, pOuId) *(-1),
	f_get_current_due_outstanding_cash_bank_in_amount(pDatetime, vCurrUsd, pOuId) *(-1),
	f_get_not_yet_due_outstanding_cash_bank_in_amount(pDatetime, vCurrIdr, pOuId) *(-1),
	f_get_not_yet_due_outstanding_cash_bank_in_amount(pDatetime, vCurrUsd, pOuId) *(-1),
	0,
	0;

	--
	--4. Ambil data untuk projected cash out : Outstanding AP, Accr AP, outstanding Payment Order AP,
	--Outstanding AP
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash Out' AS projected_transaction_type,
	'Outstanding AP' AS projected_transaction_name,
	f_get_overdue_outstanding_ap_amount(pDatetime, vCurrIdr, pOuId) AS overdue_idr,
	f_get_overdue_outstanding_ap_amount(pDatetime, vCurrUsd, pOuId) AS overdue_usd,
	f_get_current_due_outstanding_ap_amount(pDatetime, vCurrIdr, pOuId) AS current_idr,
	f_get_current_due_outstanding_ap_amount(pDatetime, vCurrUsd, pOuId) AS current_usd,
	f_get_not_yet_due_outstanding_ap_amount(pDatetime, vCurrIdr, pOuId) AS not_yet_due_idr,
	f_get_not_yet_due_outstanding_ap_amount(pDatetime, vCurrUsd, pOuId) AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd;

	--Accr AP
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash Out' AS projected_transaction_type,
	'Accr AP' AS projected_transaction_name,
	0 AS overdue_idr,
	0 AS overdue_usd,
	0 AS current_idr,
	0 AS current_usd,
	f_get_not_yet_due_accr_ap_amount(pDatetime, vCurrIdr, pOuId) AS not_yet_due_idr,
	f_get_not_yet_due_accr_ap_amount(pDatetime, vCurrUsd, pOuId) AS not_yet_due_usd,
	0 AS total_idr,
	0 AS total_usd;

	--outstanding payment order AP
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash Out' AS projected_transaction_type,
	'Outstanding Payment Order AP' AS projected_transaction_name,
	f_get_overdue_outstanding_payment_order_ap_amount(pDatetime, vCurrIdr, pOuId),
	f_get_overdue_outstanding_payment_order_ap_amount(pDatetime, vCurrUsd, pOuId),
	f_get_current_due_outstanding_payment_order_ap_amount(pDatetime, vCurrIdr, pOuId),
	f_get_current_due_outstanding_payment_order_ap_amount(pDatetime, vCurrUsd, pOuId),
	f_get_not_yet_due_outstanding_payment_order_ap_amount(pDatetime, vCurrIdr, pOuId),
	f_get_not_yet_due_outstanding_payment_order_ap_amount(pDatetime, vCurrUsd, pOuId),
	0 AS total_idr,
	0 AS total_usd;

	--outstanding payment order cashbank
	INSERT INTO tt_report_cash_projection(session_id, projected_transaction_type, projected_transaction_name,
	overdue_idr, overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd)
	SELECT pSessionId AS session_id, 'Projected Cash Out' AS projected_transaction_type,
	'Outstanding Payment Order Cash/Bank' AS projected_transaction_name,
	f_get_overdue_outstanding_payment_order_cashbank_amount(pDatetime, vCurrIdr, pOuId),
	f_get_overdue_outstanding_payment_order_cashbank_amount(pDatetime, vCurrUsd, pOuId),
	f_get_current_due_outstanding_payment_order_cashbank_amount(pDatetime, vCurrIdr, pOuId),
	f_get_current_due_outstanding_payment_order_cashbank_amount(pDatetime, vCurrUsd, pOuId),
	f_get_not_yet_due_outstanding_payment_order_cashbank_amount(pDatetime, vCurrIdr, pOuId),
	f_get_not_yet_due_outstanding_payment_order_cashbank_amount(pDatetime, vCurrUsd, pOuId),
	0 AS total_idr,
	0 AS total_usd;

	--2. Jumlahkan di total cash
	UPDATE tt_report_cash_projection SET total_idr = overdue_idr + current_idr + not_yet_due_idr;
	UPDATE tt_report_cash_projection SET total_usd = overdue_usd + current_usd + not_yet_due_usd;

	Open pRefHeader FOR
		SELECT f_get_ou_name(pOuId) AS ou_name,
		f_get_username(pUserId) AS username,
		pDatetime AS datetime;
	RETURN NEXT pRefHeader;


	Open pRefDetail FOR
	SELECT session_id, projected_transaction_type, projected_transaction_name, overdue_idr,
	overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd
	FROM tt_report_cash_projection WHERE projected_transaction_type = 'Cash Position'
	AND session_id = pSessionId;
	RETURN NEXT pRefDetail ;

	Open pRefDetail2 FOR
	SELECT session_id, projected_transaction_type, projected_transaction_name, overdue_idr,
	overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd
	FROM tt_report_cash_projection WHERE projected_transaction_type = 'Projected Cash In'
	AND session_id = pSessionId;
	RETURN NEXT pRefDetail2 ;

	Open pRefDetail3 FOR
	SELECT session_id, projected_transaction_type, projected_transaction_name, overdue_idr,
	overdue_usd, current_idr, current_usd, not_yet_due_idr, not_yet_due_usd, total_idr, total_usd
	FROM tt_report_cash_projection WHERE projected_transaction_type = 'Projected Cash Out'
	AND session_id = pSessionId;
	RETURN NEXT pRefDetail3 ;

--kosongkan temp table tt_report_cash_projection
DELETE FROM tt_report_cash_projection WHERE session_id = pSessionId;

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