abirama62

function_pos_message

Jun 8th, 2020
282
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION r_kas_harian(bigint, bigint, bigint, character varying, character varying, character varying)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader              REFCURSOR := 'refHeader';
  6.     pRefPenerimaanCash          REFCURSOR := 'refPenerimaanCash';
  7.   pRefPenerimaanBS          REFCURSOR := 'refPenerimaanBS';
  8.   pRefPenerimaanIn          REFCURSOR := 'refPenerimaanIn';
  9.   pRefPengeluaranBS         REFCURSOR := 'refPengeluaranBS';
  10.   pRefPengeluaranIn         REFCURSOR := 'refPengeluaranIn';
  11.   pRefBSJadiBiaya           REFCURSOR := 'refBSJadiBiaya';
  12.   pRefUraian            REFCURSOR := 'refUraian';
  13.  
  14.   pTenantId             ALIAS FOR $1;
  15.   pOuId             ALIAS FOR $2;
  16.   pUserId                   ALIAS FOR $3;
  17.   pDatetime                 ALIAS FOR $4;
  18.   pSessionId                ALIAS FOR $5;
  19.   pDatePeriod                   ALIAS FOR $6;
  20.  
  21.   vTotalKasKeluar numeric;
  22.   vTotalKasMasuk  numeric;
  23.   vTotalAmount  numeric;
  24.   vSaldoKasAwal numeric;
  25.   vSaldoBonSementaraAwal numeric;
  26.   vGiroTerimaAwal numeric;
  27.   vGiroTolakAwal numeric;
  28.   vGiroTitipanAwal numeric;
  29.   vSaldoKasAkhir numeric;
  30.   vSaldoBonSementaraAkhir numeric;
  31.   vGiroTerimaAkhir numeric;
  32.   vGiroTolakAkhir numeric;
  33.   vGiroTitipanAkhir numeric;
  34.  
  35.   vDocTypeFollUpCashAdvance bigint;
  36.   vDocTypeCBInOtherRcv bigint;
  37.   vDocTypeCBInPartnerRcv bigint;
  38.   vDocTypeReqCashAdvance bigint;
  39.  
  40.   vDateMinusOneDay character varying;
  41.   vDateStartCashbank character varying;
  42.   vReportName       character varying;
  43.   vCurrCode character varying;
  44.   vNo               character varying;
  45.   vYes          character varying;
  46.   vStatusDocApproved  character varying;
  47.   vStatusDocSubmitted  character varying;
  48.   vStatusDocVoid  character varying;
  49.   vTypeCashbankIn character varying;
  50.   vCbTypeOut  character varying;
  51.   vRecTypeKredit character varying;
  52.   vRecordTypeSaldoAwal character varying;
  53.   vStatusReject character varying;
  54.   vStatusAccept character varying;
  55.  
  56. BEGIN
  57.   vReportName := 'LAPORAN KAS HARIAN';
  58.   vNo := 'N';
  59.   vYes := 'Y';
  60.   vCurrCode := 'IDR';
  61.   vStatusDocApproved := 'R';
  62.   vStatusDocSubmitted := 'S';
  63.   vStatusDocVoid := 'V';
  64.   vTypeCashbankIn := 'I';
  65.   vCbTypeOut := 'O';
  66.   vRecTypeKredit := 'K';
  67.   vRecordTypeSaldoAwal := 'A';
  68.   vStatusReject := 'REJECT';
  69.   vStatusAccept := 'ACCEPT';
  70.  
  71.   vTotalKasKeluar := 0;
  72.   vTotalKasMasuk  := 0;
  73.   vTotalAmount  := 0;
  74.  
  75.   vDocTypeFollUpCashAdvance := 641;
  76.   vDocTypeCBInOtherRcv := 623;
  77.   vDocTypeCBInPartnerRcv := 621;
  78.   vDocTypeReqCashAdvance := 602;
  79.  
  80.   SELECT TO_CHAR(pDatePeriod::date - INTERVAL '1 day', 'YYYYMMDD') into vDateMinusOneDay;
  81.  
  82.   Open pRefHeader FOR
  83.   SELECT vReportName AS report_name, pDatePeriod AS period;
  84.   RETURN NEXT pRefHeader;
  85.  
  86.   Open pRefPenerimaanCash FOR
  87.   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
  88.   FROM i_trx_pos A
  89.   INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  90.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  91.   WHERE A.tenant_id = pTenantId
  92.   AND A.ou_id = pOuId
  93.   AND A.doc_date = pDatePeriod
  94.   AND A.curr_code = vCurrCode
  95.   AND A.status = vStatusDocSubmitted
  96.   UNION
  97.   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
  98.   FROM i_trx_pos A
  99.   INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  100.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  101.   INNER JOIN i_trx_log_voided_pos_custom D ON A.doc_no = D.doc_no AND A.tenant_id = D.tenant_id
  102.   WHERE A.tenant_id = pTenantId
  103.   AND A.ou_id = pOuId
  104.   AND D.doc_date = pDatePeriod
  105.   AND A.curr_code = vCurrCode
  106.   AND A.status = vStatusDocVoid;
  107.  
  108.   RETURN NEXT pRefPenerimaanCash;
  109.  
  110. --
  111.   SELECT COALESCE(SUM(A.total_payment), 0) INTO vTotalAmount
  112.   FROM i_trx_pos A
  113.   INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  114.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  115.   WHERE A.tenant_id = pTenantId
  116.   AND A.ou_id = pOuId
  117.   AND A.doc_date = pDatePeriod
  118.   AND A.curr_code = vCurrCode
  119.   AND A.status = vStatusDocSubmitted;
  120.   vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
  121.  
  122.   SELECT COALESCE(SUM(A.total_payment*-1), 0) INTO vTotalAmount
  123.   FROM i_trx_pos A
  124.   INNER JOIN i_trx_pos_cash_payment B ON A.process_no = B.process_no AND A.tenant_id = B.tenant_id
  125.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  126.   INNER JOIN i_trx_log_voided_pos_custom D ON A.doc_no = D.doc_no AND A.tenant_id = D.tenant_id
  127.   WHERE A.tenant_id = pTenantId
  128.   AND A.ou_id = pOuId
  129.   AND D.doc_date = pDatePeriod
  130.   AND A.curr_code = vCurrCode
  131.   AND A.status = vStatusDocVoid;
  132.   vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
  133. --
  134.  
  135.   Open pRefPenerimaanBS FOR
  136.   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
  137.   FROM cb_in_out_cashbank A
  138.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  139.   WHERE doc_type_id = vDocTypeFollUpCashAdvance
  140.   AND A.tenant_id = pTenantId
  141.   AND A.ou_id = pOuId
  142.   AND A.doc_date = pDatePeriod
  143.   AND A.ref_curr_code = vCurrCode
  144.   AND A.status_doc = vStatusDocApproved
  145.   AND A.type_in_out_cashbank = vTypeCashbankIn
  146.   ORDER BY in_out_cashbank_id ASC;
  147.  
  148.   RETURN NEXT pRefPenerimaanBS;
  149.  
  150. --
  151.   SELECT COALESCE(SUM(A.ref_amount*-1), 0) INTO vTotalAmount
  152.   FROM cb_in_out_cashbank A
  153.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  154.   WHERE doc_type_id = vDocTypeFollUpCashAdvance
  155.   AND A.tenant_id = pTenantId
  156.   AND A.ou_id = pOuId
  157.   AND A.doc_date = pDatePeriod
  158.   AND A.ref_curr_code = vCurrCode
  159.   AND A.type_in_out_cashbank = vTypeCashbankIn
  160.   AND A.status_doc = vStatusDocApproved;
  161.   vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
  162. --
  163.  
  164.   Open pRefPenerimaanIn FOR
  165.   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
  166.   FROM cb_in_out_cashbank A
  167.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  168.   INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  169.   WHERE (doc_type_id = vDocTypeCBInPartnerRcv OR doc_type_id = vDocTypeCBInOtherRcv)
  170.   AND A.tenant_id = pTenantId
  171.   AND A.ou_id = pOuId
  172.   AND A.doc_date = pDatePeriod
  173.   AND A.status_doc = vStatusDocApproved
  174.   ORDER BY A.in_out_cashbank_id ASC;
  175.  
  176.   RETURN NEXT pRefPenerimaanIn;
  177.  
  178. --
  179.   SELECT COALESCE(SUM(B.payment_amount), 0) INTO vTotalAmount
  180.   FROM cb_in_out_cashbank A
  181.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  182.   INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
  183.   WHERE (doc_type_id = vDocTypeCBInPartnerRcv OR doc_type_id = vDocTypeCBInOtherRcv)
  184.   AND A.tenant_id = pTenantId
  185.   AND A.ou_id = pOuId
  186.   AND A.doc_date = pDatePeriod
  187.   AND A.status_doc = vStatusDocApproved;
  188.   vTotalKasMasuk = vTotalKasMasuk + vTotalAmount;
  189. --
  190.  
  191.   Open pRefPengeluaranBS FOR
  192.   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
  193.   FROM cb_in_out_cashbank A
  194.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  195.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  196.   AND A.doc_type_id = vDocTypeFollUpCashAdvance
  197.   AND A.tenant_id = pTenantId
  198.   AND A.ou_id = pOuId
  199.   AND A.doc_date = pDatePeriod
  200.   AND A.ref_curr_code = vCurrCode
  201.   AND A.status_doc = vStatusDocApproved
  202.   AND A.type_in_out_cashbank = vCbTypeOut
  203.   UNION
  204.   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
  205.   FROM cb_in_out_cashbank A
  206.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  207.   INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  208.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  209.   AND B.doc_type_id = vDocTypeReqCashAdvance
  210.   AND A.tenant_id = pTenantId
  211.   AND A.ou_id = pOuId
  212.   AND A.doc_date = pDatePeriod
  213.   AND A.ref_curr_code = vCurrCode
  214.   AND A.status_doc = vStatusDocApproved
  215.   AND A.type_in_out_cashbank = vCbTypeOut
  216.   ORDER BY row_no ASC;
  217.  
  218.   RETURN NEXT pRefPengeluaranBS;
  219.  
  220. --
  221.   SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  222.   FROM cb_in_out_cashbank A
  223.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  224.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  225.   AND A.doc_type_id = vDocTypeFollUpCashAdvance
  226.   AND A.tenant_id = pTenantId
  227.   AND A.ou_id = pOuId
  228.   AND A.doc_date = pDatePeriod
  229.   AND A.ref_curr_code = vCurrCode
  230.   AND A.type_in_out_cashbank = vCbTypeOut
  231.   AND A.status_doc = vStatusDocApproved;
  232.   vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;
  233.  
  234.   SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  235.   FROM cb_in_out_cashbank A
  236.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  237.   INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  238.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  239.   AND B.doc_type_id = vDocTypeReqCashAdvance
  240.   AND A.tenant_id = pTenantId
  241.   AND A.ou_id = pOuId
  242.   AND A.doc_date = pDatePeriod
  243.   AND A.ref_curr_code = vCurrCode
  244.   AND A.type_in_out_cashbank = vCbTypeOut
  245.   AND A.status_doc = vStatusDocApproved;
  246.   vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;
  247.  
  248. --
  249.  
  250.   Open pRefPengeluaranIn FOR
  251.   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
  252.   FROM cb_in_out_cashbank A
  253.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  254.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  255.   WHERE NOT EXISTS (
  256.     SELECT 1 FROM cb_payment_order B
  257.     WHERE E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  258.     AND B.doc_type_id = vDocTypeReqCashAdvance)
  259.   AND A.doc_type_id != vDocTypeFollUpCashAdvance
  260.   AND A.type_in_out_cashbank = vCbTypeOut
  261.   AND A.tenant_id = pTenantId
  262.   AND A.ou_id = pOuId
  263.   AND A.doc_date = pDatePeriod
  264.   AND A.ref_curr_code = vCurrCode
  265.   AND A.status_doc = vStatusDocApproved
  266.   ORDER BY A.in_out_cashbank_id ASC;
  267.  
  268.   RETURN NEXT pRefPengeluaranIn;
  269.  
  270. --
  271.   SELECT COALESCE(SUM(A.ref_amount), 0) INTO vTotalAmount
  272.   FROM cb_in_out_cashbank A
  273.   INNER JOIN cb_trx_cashbank_balance E ON A.ref_id = E.trx_cashbank_balance_id AND E.tenant_id = E.tenant_id
  274.   INNER JOIN cb_payment_order B ON E.payment_id = B.payment_order_id AND E.doc_type_id = B.doc_type_id
  275.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  276.   WHERE B.doc_type_id != vDocTypeReqCashAdvance
  277.   AND A.type_in_out_cashbank = vCbTypeOut
  278.   AND A.tenant_id = pTenantId
  279.   AND A.ou_id = pOuId
  280.   AND A.doc_date = pDatePeriod
  281.   AND A.ref_curr_code = vCurrCode
  282.   AND A.status_doc = vStatusDocApproved;
  283.   vTotalKasKeluar = vTotalKasKeluar + vTotalAmount;
  284. --
  285.  
  286.   Open pRefBSJadiBiaya FOR
  287.   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
  288.   FROM cb_advance_settle A
  289.   INNER JOIN cb_advance_settle_cost B ON A.advance_settle_id = B.advance_settle_id AND A.tenant_id = B.tenant_id
  290.   INNER JOIN m_partner C ON A.partner_id = C.partner_id AND A.tenant_id = C.tenant_id
  291.   AND A.tenant_id = pTenantId
  292.   AND A.ou_id = pOuId
  293.   AND A.doc_date = pDatePeriod
  294.   AND A.ref_curr_code = vCurrCode
  295.   AND A.status_doc = vStatusDocApproved
  296.   ORDER BY A.advance_settle_id ASC;
  297.  
  298.   RETURN NEXT pRefBSJadiBiaya;
  299.  
  300.   SELECT MAX(A.cash_bank_date) INTO vDateStartCashbank
  301.   FROM cb_cashbank_balance A
  302.   WHERE A.tenant_id = pTenantId
  303.   AND A.ou_id = pOuId
  304.   AND A.rec_type = vRecordTypeSaldoAwal
  305.   AND A.cash_bank_date <= pDatePeriod;
  306.  
  307.   IF vDateStartCashbank IS null OR vDateStartCashbank IN ('', ' ') THEN
  308.  
  309.     SELECT SUM(CASE WHEN rec_type = vRecTypeKredit THEN A.amount * -1 ELSE A.amount END) INTO vSaldoKasAwal
  310.     FROM cb_cashbank_balance A
  311.     WHERE A.tenant_id = pTenantId
  312.     AND A.ou_id = pOuId
  313.     AND A.cash_bank_date <= vDateMinusOneDay;
  314.  
  315.   ELSE
  316.  
  317.     WITH cb_awal_bulan AS (
  318.       SELECT SUM(A.amount) AS saldoAwalBulan
  319.       FROM cb_cashbank_balance A
  320.       WHERE A.tenant_id = pTenantId
  321.       AND A.ou_id = pOuId
  322.       AND A.rec_type = vRecordTypeSaldoAwal
  323.       AND A.cash_bank_date = vDateStartCashbank
  324.     )
  325.     SELECT A.saldoAwalBulan + SUM(CASE WHEN rec_type = 'K' THEN B.amount * -1 ELSE B.amount END) INTO vSaldoKasAwal
  326.     FROM cb_awal_bulan A, cb_cashbank_balance B
  327.     WHERE B.tenant_id = pTenantId
  328.     AND B.ou_id = pOuId
  329.     AND B.rec_type <> vRecordTypeSaldoAwal
  330.     AND B.cash_bank_date >= vDateStartCashbank
  331.     AND B.cash_bank_date <= vDateMinusOneDay
  332.     GROUP BY A.saldoAwalBulan;
  333.  
  334.   END IF;
  335.  
  336.   SELECT SUM(A.amount) INTO vSaldoBonSementaraAwal
  337.   FROM cb_advance_balance A
  338.   WHERE A.tenant_id = pTenantId
  339.   AND A.ou_id = pOuId
  340.   AND A.flg_settle = vNo
  341.   AND A.cash_bank_payment_date <= vDateMinusOneDay;
  342.  
  343.   SELECT SUM(B.payment_amount) INTO vGiroTerimaAwal
  344.   FROM cb_in_out_cashbank A
  345.   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
  346.   WHERE A.status_doc = vStatusDocApproved
  347.   AND A.tenant_id = pTenantId
  348.   AND A.ou_id = pOuId
  349.   AND A.doc_date <= vDateMinusOneDay;
  350.  
  351.   SELECT SUM(B.cheque_giro_amount) INTO vGiroTolakAwal
  352.   FROM cb_in_out_cashbank A
  353.   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
  354.   WHERE A.tenant_id = pTenantId
  355.   AND A.ou_id = pOuId
  356.   AND B.realization_status = vStatusReject
  357.   AND A.doc_date <= vDateMinusOneDay;
  358.  
  359.   SELECT SUM(A.amount) INTO vGiroTitipanAwal
  360.   FROM cb_cheque_giro_balance A
  361.   INNER JOIN cb_in_out_cashbank C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  362.   WHERE A.tenant_id = pTenantId
  363.   AND C.ou_id = pOuId
  364.   AND A.flg_realization = vNo
  365.   AND A.flg_deposit = vYes
  366.   AND A.cheque_giro_date <= vDateMinusOneDay;
  367.  
  368. --
  369.  
  370.   IF vDateStartCashbank IS null OR vDateStartCashbank IN ('', ' ') THEN
  371.  
  372.     SELECT SUM(CASE WHEN rec_type = vRecTypeKredit THEN A.amount * -1 ELSE A.amount END) INTO vSaldoKasAkhir
  373.     FROM cb_cashbank_balance A
  374.     WHERE A.tenant_id = pTenantId
  375.     AND A.ou_id = pOuId
  376.     AND A.cash_bank_date <= pDatePeriod;
  377.  
  378.   ELSE
  379.  
  380.     WITH cb_awal_bulan AS (
  381.       SELECT SUM(A.amount) AS saldoAwalBulan
  382.       FROM cb_cashbank_balance A
  383.       WHERE A.tenant_id = pTenantId
  384.       AND A.ou_id = pOuId
  385.       AND A.rec_type = vRecordTypeSaldoAwal
  386.       AND A.cash_bank_date = vDateStartCashbank
  387.     )
  388.     SELECT A.saldoAwalBulan + SUM(CASE WHEN rec_type = 'K' THEN B.amount * -1 ELSE B.amount END) INTO vSaldoKasAkhir
  389.     FROM cb_awal_bulan A, cb_cashbank_balance B
  390.     WHERE B.tenant_id = pTenantId
  391.     AND B.ou_id = pOuId
  392.     AND B.rec_type <> vRecordTypeSaldoAwal
  393.     AND B.cash_bank_date >= vDateStartCashbank
  394.     AND B.cash_bank_date <= pDatePeriod
  395.     GROUP BY A.saldoAwalBulan;
  396.  
  397.   END IF;
  398.  
  399.   SELECT SUM(A.amount) INTO vSaldoBonSementaraAkhir
  400.   FROM cb_advance_balance A
  401.   WHERE A.tenant_id = pTenantId
  402.   AND A.ou_id = pOuId
  403.   AND A.flg_settle = vNo
  404.   AND A.cash_bank_payment_date <= pDatePeriod;
  405.  
  406.   SELECT SUM(B.payment_amount) INTO vGiroTerimaAkhir
  407.   FROM cb_in_out_cashbank A
  408.   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
  409.   WHERE A.status_doc = vStatusDocApproved
  410.   AND A.tenant_id = pTenantId
  411.   AND A.ou_id = pOuId
  412.   AND A.doc_date <= pDatePeriod;
  413.  
  414.   SELECT SUM(B.cheque_giro_amount) INTO vGiroTolakAkhir
  415.   FROM cb_in_out_cashbank A
  416.   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
  417.   WHERE A.tenant_id = pTenantId
  418.   AND A.ou_id = pOuId
  419.   AND B.realization_status = vStatusReject
  420.   AND A.doc_date <= pDatePeriod;
  421.  
  422.   SELECT SUM(A.amount) INTO vGiroTitipanAkhir
  423.   FROM cb_cheque_giro_balance A
  424.   INNER JOIN cb_in_out_cashbank C ON A.in_out_cashbank_id = C.in_out_cashbank_id
  425.   WHERE A.tenant_id = pTenantId
  426.   AND C.ou_id = pOuId
  427.   AND A.flg_realization = vNo
  428.   AND A.flg_deposit = vYes
  429.   AND A.cheque_giro_date <= pDatePeriod;
  430.  
  431.   Open pRefUraian FOR
  432.   SELECT vTotalKasKeluar AS total_kas_keluar, vTotalKasMasuk AS total_kas_masuk,
  433.     vSaldoKasAwal AS saldo_kas_awal, vSaldoKasAkhir AS saldo_kas_akhir,
  434.     vSaldoBonSementaraAwal AS saldo_bon_awal, vSaldoBonSementaraAkhir AS saldo_bon_akhir,
  435.     vGiroTerimaAwal AS giro_terima_awal, vGiroTerimaAkhir AS giro_terima_akhir,
  436.     vGiroTolakAwal AS giro_tolak_awal, vGiroTolakAkhir AS giro_tolak_akhir,
  437.     vGiroTitipanAwal AS giro_titipan_awal, vGiroTitipanAkhir AS giro_titipan_akhir;
  438.   RETURN NEXT pRefUraian;
  439.  
  440. END;
  441. $BODY$
  442.   LANGUAGE plpgsql VOLATILE
  443.   COST 100
  444.   ROWS 1000;
  445. /
RAW Paste Data