function_pos_message

CREATE OR REPLACE FUNCTION r_kas_harian(bigint, bigint, bigint, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader              REFCURSOR := 'refHeader';
    pRefPenerimaanCash          REFCURSOR := 'refPenerimaanCash';
  pRefPenerimaanBS          REFCURSOR := 'refPenerimaanBS';
  pRefPenerimaanIn          REFCURSOR := 'refPenerimaanIn';
  pRefPengeluaranBS         REFCURSOR := 'refPengeluaranBS';
  pRefPengeluaranIn         REFCURSOR := 'refPengeluaranIn';
  pRefBSJadiBiaya           REFCURSOR := 'refBSJadiBiaya';
  pRefUraian            REFCURSOR := 'refUraian';

  pTenantId             ALIAS FOR $1;
  pOuId             ALIAS FOR $2;
  pUserId                   ALIAS FOR $3;
  pDatetime                 ALIAS FOR $4;
  pSessionId                ALIAS FOR $5;
  pDatePeriod                   ALIAS FOR $6;

  vTotalKasKeluar numeric;
  vTotalKasMasuk  numeric;
  vTotalAmount  numeric;
  vSaldoKasAwal numeric;
  vSaldoBonSementaraAwal numeric;
  vGiroTerimaAwal numeric;
  vGiroTolakAwal numeric;
  vGiroTitipanAwal numeric;
  vSaldoKasAkhir numeric;
  vSaldoBonSementaraAkhir numeric;
  vGiroTerimaAkhir numeric;
  vGiroTolakAkhir numeric;
  vGiroTitipanAkhir numeric;

  vDocTypeFollUpCashAdvance bigint;
  vDocTypeCBInOtherRcv bigint;
  vDocTypeCBInPartnerRcv bigint;
  vDocTypeReqCashAdvance bigint;

  vDateMinusOneDay character varying;
  vDateStartCashbank character varying;
  vReportName       character varying;
  vCurrCode character varying;
  vNo               character varying;
  vYes          character varying;
  vStatusDocApproved  character varying;
  vStatusDocSubmitted  character varying;
  vStatusDocVoid  character varying;
  vTypeCashbankIn character varying;
  vCbTypeOut  character varying;
  vRecTypeKredit character varying;
  vRecordTypeSaldoAwal character varying;
  vStatusReject character varying;
  vStatusAccept character varying;

BEGIN
  vReportName := 'LAPORAN KAS HARIAN';
  vNo := 'N';
  vYes := 'Y';
  vCurrCode := 'IDR';
  vStatusDocApproved := 'R';
  vStatusDocSubmitted := 'S';
  vStatusDocVoid := 'V';
  vTypeCashbankIn := 'I';
  vCbTypeOut := 'O';
  vRecTypeKredit := 'K';
  vRecordTypeSaldoAwal := 'A';
  vStatusReject := 'REJECT';
  vStatusAccept := 'ACCEPT';

  vTotalKasKeluar := 0;
  vTotalKasMasuk  := 0;
  vTotalAmount  := 0;

  vDocTypeFollUpCashAdvance := 641;
  vDocTypeCBInOtherRcv := 623;
  vDocTypeCBInPartnerRcv := 621;
  vDocTypeReqCashAdvance := 602;

  SELECT TO_CHAR(pDatePeriod::date - INTERVAL '1 day', 'YYYYMMDD') into vDateMinusOneDay;

  Open pRefHeader FOR
  SELECT vReportName AS report_name, pDatePeriod AS period;
  RETURN NEXT pRefHeader;

  Open pRefPenerimaanCash FOR
  SELECT ROW_NUMBER () OVER (ORDER BY trx_pos_cash_payment_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, A.total_payment AS amount
  FROM i_trx_pos A
  INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.curr_code = vCurrCode
  AND A.status = vStatusDocSubmitted
  UNION
  SELECT ROW_NUMBER () OVER (ORDER BY trx_pos_cash_payment_id) AS row_no, D.doc_date, A.doc_no, C.partner_name, A.remark, A.total_payment* (-1) AS amount
  FROM i_trx_pos A
  INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  INNER JOIN i_trx_log_voided_pos_custom D ON A.doc_no = D.doc_no AND A.tenant_id = D.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND D.doc_date = pDatePeriod
  AND A.curr_code = vCurrCode
  AND A.status = vStatusDocVoid;

  RETURN NEXT pRefPenerimaanCash;

--
  SELECT COALESCE(SUM(A.total_payment), 0) INTO vTotalAmount
  FROM i_trx_pos A
  INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.curr_code = vCurrCode
  AND A.status = vStatusDocSubmitted;
  vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;

  SELECT COALESCE(SUM(A.total_payment*-1), 0) INTO vTotalAmount
  FROM i_trx_pos A
  INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  INNER JOIN i_trx_log_voided_pos_custom D ON A.doc_no = D.doc_no AND A.tenant_id = D.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND D.doc_date = pDatePeriod
  AND A.curr_code = vCurrCode
  AND A.status = vStatusDocVoid;
  vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
--

  Open pRefPenerimaanBS FOR
  SELECT ROW_NUMBER () OVER (ORDER BY in_out_cashbank_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, A.ref_amount*(-1) AS amount
  FROM cb_in_out_cashbank A
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE doc_type_id = vDocTypeFollUpCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved
  AND A.type_in_out_cashbank = vTypeCashbankIn
  ORDER BY in_out_cashbank_id ASC;

  RETURN NEXT pRefPenerimaanBS;

--
  SELECT COALESCE(SUM(A.ref_amount*-1), 0) INTO vTotalAmount
  FROM cb_in_out_cashbank A
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE doc_type_id = vDocTypeFollUpCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.type_in_out_cashbank = vTypeCashbankIn
  AND A.status_doc = vStatusDocApproved;
  vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
--

  Open pRefPenerimaanIn FOR
  SELECT ROW_NUMBER () OVER (ORDER BY A.in_out_cashbank_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, B.payment_amount AS amount
  FROM cb_in_out_cashbank A
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  WHERE (doc_type_id = vDocTypeCBInPartnerRcv OR doc_type_id = vDocTypeCBInOtherRcv)
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.status_doc = vStatusDocApproved
  ORDER BY A.in_out_cashbank_id ASC;

  RETURN NEXT pRefPenerimaanIn;

--
  SELECT COALESCE(SUM(B.payment_amount), 0) INTO vTotalAmount
  FROM cb_in_out_cashbank A
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  WHERE (doc_type_id = vDocTypeCBInPartnerRcv OR doc_type_id = vDocTypeCBInOtherRcv)
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.status_doc = vStatusDocApproved;
  vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
--

  Open pRefPengeluaranBS FOR
  SELECT ROW_NUMBER () OVER (ORDER BY in_out_cashbank_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, A.ref_amount AS amount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  AND A.doc_type_id = vDocTypeFollUpCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved
  AND A.type_in_out_cashbank = vCbTypeOut
  UNION
  SELECT ROW_NUMBER () OVER (ORDER BY in_out_cashbank_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, A.ref_amount AS amount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  AND B.doc_type_id = vDocTypeReqCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved
  AND A.type_in_out_cashbank = vCbTypeOut
  ORDER BY row_no ASC;

  RETURN NEXT pRefPengeluaranBS;

--
  SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  AND A.doc_type_id = vDocTypeFollUpCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.type_in_out_cashbank = vCbTypeOut
  AND A.status_doc = vStatusDocApproved;
  vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;

  SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  AND B.doc_type_id = vDocTypeReqCashAdvance
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.type_in_out_cashbank = vCbTypeOut
  AND A.status_doc = vStatusDocApproved;
  vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;

--

  Open pRefPengeluaranIn FOR
  SELECT ROW_NUMBER () OVER (ORDER BY in_out_cashbank_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, A.ref_amount AS amount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE NOT EXISTS (
    SELECT 1 FROM cb_payment_order B
    WHERE E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
    AND B.doc_type_id = vDocTypeReqCashAdvance)
  AND A.doc_type_id != vDocTypeFollUpCashAdvance
  AND A.type_in_out_cashbank = vCbTypeOut
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved
  ORDER BY A.in_out_cashbank_id ASC;

  RETURN NEXT pRefPengeluaranIn;

--
  SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  FROM cb_in_out_cashbank A
  INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  WHERE B.doc_type_id != vDocTypeReqCashAdvance
  AND A.type_in_out_cashbank = vCbTypeOut
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved;
  vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;
--

  Open pRefBSJadiBiaya FOR
  SELECT ROW_NUMBER () OVER (ORDER BY A.advance_settle_id) AS row_no, A.doc_date, A.doc_no, C.partner_name, A.remark, B.advance_amount AS amount
  FROM cb_advance_settle A
  INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date = pDatePeriod
  AND A.ref_curr_code = vCurrCode
  AND A.status_doc = vStatusDocApproved
  ORDER BY A.advance_settle_id ASC;

  RETURN NEXT pRefBSJadiBiaya;

  SELECT MAX(A.cash_bank_date) INTO vDateStartCashbank
  FROM cb_cashbank_balance A
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.rec_type = vRecordTypeSaldoAwal
  AND A.cash_bank_date <= pDatePeriod;

  IF vDateStartCashbank IS null OR vDateStartCashbank IN ('', ' ') THEN

    SELECT SUM(CASE WHEN rec_type = vRecTypeKredit THEN A.amount * -1 ELSE A.amount END) INTO vSaldoKasAwal
    FROM cb_cashbank_balance A
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.cash_bank_date <= vDateMinusOneDay;

  ELSE

    WITH cb_awal_bulan AS (
      SELECT SUM(A.amount) AS saldoAwalBulan
      FROM cb_cashbank_balance A
      WHERE A.tenant_id = pTenantId
      AND A.ou_id = pOuId
      AND A.rec_type = vRecordTypeSaldoAwal
      AND A.cash_bank_date = vDateStartCashbank
    )
    SELECT A.saldoAwalBulan + SUM(CASE WHEN rec_type = 'K' THEN B.amount * -1 ELSE B.amount END) INTO vSaldoKasAwal
    FROM cb_awal_bulan A, cb_cashbank_balance B
    WHERE B.tenant_id = pTenantId
    AND B.ou_id = pOuId
    AND B.rec_type <> vRecordTypeSaldoAwal
    AND B.cash_bank_date >= vDateStartCashbank
    AND B.cash_bank_date <= vDateMinusOneDay
    GROUP BY A.saldoAwalBulan;

  END IF;

  SELECT SUM(A.amount) INTO vSaldoBonSementaraAwal
  FROM cb_advance_balance A
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.flg_settle = vNo
  AND A.cash_bank_payment_date <= vDateMinusOneDay;

  SELECT SUM(B.payment_amount) INTO vGiroTerimaAwal
  FROM cb_in_out_cashbank A
  INNER JOIN cb_in_out_cheque_giro_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id AND A.tenant_id = B.tenant_id
  WHERE A.status_doc = vStatusDocApproved
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date <= vDateMinusOneDay;

  SELECT SUM(B.cheque_giro_amount) INTO vGiroTolakAwal
  FROM cb_in_out_cashbank A
  INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id AND A.tenant_id = B.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND B.realization_status = vStatusReject
  AND A.doc_date <= vDateMinusOneDay;

  SELECT SUM(A.amount) INTO vGiroTitipanAwal
  FROM cb_cheque_giro_balance A
  INNER JOIN cb_in_out_cashbank C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  WHERE A.tenant_id = pTenantId
  AND C.ou_id = pOuId
  AND A.flg_realization = vNo
  AND A.flg_deposit = vYes
  AND A.cheque_giro_date <= vDateMinusOneDay;

--

  IF vDateStartCashbank IS null OR vDateStartCashbank IN ('', ' ') THEN

    SELECT SUM(CASE WHEN rec_type = vRecTypeKredit THEN A.amount * -1 ELSE A.amount END) INTO vSaldoKasAkhir
    FROM cb_cashbank_balance A
    WHERE A.tenant_id = pTenantId
    AND A.ou_id = pOuId
    AND A.cash_bank_date <= pDatePeriod;

  ELSE

    WITH cb_awal_bulan AS (
      SELECT SUM(A.amount) AS saldoAwalBulan
      FROM cb_cashbank_balance A
      WHERE A.tenant_id = pTenantId
      AND A.ou_id = pOuId
      AND A.rec_type = vRecordTypeSaldoAwal
      AND A.cash_bank_date = vDateStartCashbank
    )
    SELECT A.saldoAwalBulan + SUM(CASE WHEN rec_type = 'K' THEN B.amount * -1 ELSE B.amount END) INTO vSaldoKasAkhir
    FROM cb_awal_bulan A, cb_cashbank_balance B
    WHERE B.tenant_id = pTenantId
    AND B.ou_id = pOuId
    AND B.rec_type <> vRecordTypeSaldoAwal
    AND B.cash_bank_date >= vDateStartCashbank
    AND B.cash_bank_date <= pDatePeriod
    GROUP BY A.saldoAwalBulan;

  END IF;

  SELECT SUM(A.amount) INTO vSaldoBonSementaraAkhir
  FROM cb_advance_balance A
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.flg_settle = vNo
  AND A.cash_bank_payment_date <= pDatePeriod;

  SELECT SUM(B.payment_amount) INTO vGiroTerimaAkhir
  FROM cb_in_out_cashbank A
  INNER JOIN cb_in_out_cheque_giro_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id AND A.tenant_id = B.tenant_id
  WHERE A.status_doc = vStatusDocApproved
  AND A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND A.doc_date <= pDatePeriod;

  SELECT SUM(B.cheque_giro_amount) INTO vGiroTolakAkhir
  FROM cb_in_out_cashbank A
  INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id AND A.tenant_id = B.tenant_id
  WHERE A.tenant_id = pTenantId
  AND A.ou_id = pOuId
  AND B.realization_status = vStatusReject
  AND A.doc_date <= pDatePeriod;

  SELECT SUM(A.amount) INTO vGiroTitipanAkhir
  FROM cb_cheque_giro_balance A
  INNER JOIN cb_in_out_cashbank C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  WHERE A.tenant_id = pTenantId
  AND C.ou_id = pOuId
  AND A.flg_realization = vNo
  AND A.flg_deposit = vYes
  AND A.cheque_giro_date <= pDatePeriod;

  Open pRefUraian FOR
  SELECT vTotalKasKeluar AS total_kas_keluar, vTotalKasMasuk AS total_kas_masuk,
    vSaldoKasAwal AS saldo_kas_awal, vSaldoKasAkhir AS saldo_kas_akhir,
    vSaldoBonSementaraAwal AS saldo_bon_awal, vSaldoBonSementaraAkhir AS saldo_bon_akhir,
    vGiroTerimaAwal AS giro_terima_awal, vGiroTerimaAkhir AS giro_terima_akhir,
    vGiroTolakAwal AS giro_tolak_awal, vGiroTolakAkhir AS giro_tolak_akhir,
    vGiroTitipanAwal AS giro_titipan_awal, vGiroTitipanAkhir AS giro_titipan_akhir;
  RETURN NEXT pRefUraian;

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