Functioin Report Log Stock Card With Invoice

-- Function: public.r_inv_inquiry_stock_log_stock_card_with_invoice(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying, character varying, character varying)

-- DROP FUNCTION public.r_inv_inquiry_stock_log_stock_card_with_invoice(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION public.r_inv_inquiry_stock_log_stock_card_with_invoice(
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    bigint,
    bigint,
    bigint,
    character varying,
    character varying,
    character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                      REFCURSOR := 'refHeader';
    pRefDetail_detail               REFCURSOR := 'refDetail_detail';
    pRefDetail_summary              REFCURSOR := 'refDetail_summary';
    pSessionId                      ALIAS FOR $1;
    pTenantId                       ALIAS FOR $2;
    pUserId                         ALIAS FOR $3;
    pRoleId                         ALIAS FOR $4;
    pDatetime                       ALIAS FOR $5;
    pOuId                           ALIAS FOR $6;
    pProductId                      ALIAS FOR $7;
    pWarehouseId                    ALIAS FOR $8;
    pPeriodStart                    ALIAS FOR $9;
    pPeriodEnd                      ALIAS FOR $10;
    pPeriodStartMinusOneMonth       ALIAS FOR $11;

    vDocTypeId                      bigint = -99;
    vAdminLedgerCodeInvQty          character varying := 'INV';
    vStatusLedgerDone               character varying := '1';
    vLastYearMonth                  character varying := '';
    vEmptyValue                     character varying := '';
    vBeginningQtyFromLastProcess    numeric := 0;
    vRekapQtyForSaldoAwal           numeric := 0;
    vRekapQtyTrxPeriod              numeric := 0;
    vBeginningQty                   numeric := 0;
    vEndQty                         numeric := 0;

BEGIN

    SELECT COALESCE(MAX(date_year_month), '') INTO vLastYearMonth
    FROM m_admin_process_ledger
    WHERE tenant_id = pTenantId AND
          ou_id = pOuId AND
          ledger_code = vAdminLedgerCodeInvQty AND
          status_ledger = vStatusLedgerDone AND
          date_year_month < pPeriodStart;

    SELECT COALESCE(SUM(A.qty), 0) INTO vBeginningQtyFromLastProcess
    FROM in_summary_monthly_qty A
    WHERE CASE WHEN pWarehouseId <> -99
        THEN
          A.tenant_id = pTenantId AND
          A.doc_type_id = vDocTypeId AND
          A.product_id = pProductId AND
          A.warehouse_id = pWarehouseId AND
          A.date_year_month = vLastYearMonth
        ELSE
          A.tenant_id = pTenantId AND
          A.doc_type_id = vDocTypeId AND
          A.product_id = pProductId AND
          A.date_year_month = vLastYearMonth
        END;

    IF (pPeriodStartMinusOneMonth = vLastYearMonth) THEN
        vBeginningQty := vBeginningQtyFromLastProcess;
    ELSE
        SELECT COALESCE(SUM(A.qty), 0) AS qty INTO vRekapQtyForSaldoAwal
        FROM in_log_product_balance_stock A
        WHERE CASE WHEN pWarehouseId <> -99
            THEN
              A.tenant_id = pTenantId AND
              A.product_id = pProductId AND
              A.warehouse_id = pWarehouseId AND
              SUBSTRING(A.doc_date, 1, 6) > vlastYearMonth AND
              SUBSTRING(A.doc_date, 1, 6) <= pPeriodStartMinusOneMonth
            ELSE
              A.tenant_id = pTenantId AND
              A.product_id = pProductId AND
              SUBSTRING(A.doc_date, 1, 6) > vlastYearMonth AND
              SUBSTRING(A.doc_date, 1, 6) <= pPeriodStartMinusOneMonth
            END;
        vBeginningQty := vBeginningQtyFromLastProcess + vRekapQtyForSaldoAwal;
    END IF;


    SELECT COALESCE(SUM(A.qty), 0) AS qty INTO vRekapQtyTrxPeriod
    FROM in_log_product_balance_stock A
    WHERE CASE WHEN pWarehouseId <> -99
        THEN
          A.tenant_id = pTenantId AND
          A.product_id = pProductId AND
          A.warehouse_id = pWarehouseId AND
          SUBSTRING(A.doc_date, 1, 6) BETWEEN pPeriodStart AND pPeriodEnd
        ELSE
          A.tenant_id = pTenantId AND
          A.product_id = pProductId AND
          SUBSTRING(A.doc_date, 1, 6) BETWEEN pPeriodStart AND pPeriodEnd
        END;
    vEndQty := vBeginningQty + vRekapQtyTrxPeriod;


    Open pRefHeader FOR
    SELECT f_get_ou_name(pOuId) AS ou_name,
           f_get_username(pUserId) AS user_name, f_get_role_name(pRoleId) AS role_name,
           pDatetime AS datetime,
           CASE WHEN -99 <> -99 THEN f_get_warehouse_name(-99) ELSE 'All' END AS warehouse_name,
           f_get_product_code(pProductId) AS product_code,
           f_get_product_name(pProductId) AS product_name,
           pPeriodStart AS period_start,
           pPeriodEnd AS period_end;
    RETURN NEXT pRefHeader;


    Open pRefDetail_detail FOR
    EXECUTE '
    WITH data AS (
           SELECT f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.doc_date,      COALESCE(COALESCE(B2.doc_no,B4.doc_no),$1) AS invoice_doc_no, COALESCE(COALESCE(B2.doc_date,B4.doc_date),$1) AS invoice_doc_date,      COALESCE(B.warehouse_to_id, A.warehouse_id) AS warehouse_id,      f_get_warehouse_code(COALESCE(B.warehouse_to_id, A.warehouse_id)) AS warehouse_code,       f_get_warehouse_name(COALESCE(B.warehouse_to_id, A.warehouse_id)) AS warehouse_name,       f_get_warehouse_code(COALESCE(B.warehouse_from_id, A.warehouse_id)) AS warehouse_from_code,       f_get_warehouse_name(COALESCE(B.warehouse_from_id, A.warehouse_id)) As warehouse_from_name,
             A.partner_id, f_get_partner_code(A.partner_id) AS partner_code,       f_get_partner_name(A.partner_id) AS partner_name,
             SUM(A.qty) AS qty, A.base_uom_id,      f_get_uom_code(A.base_uom_id) AS base_uom_code,       f_get_uom_name(A.base_uom_id) AS base_uom_name,       A.update_datetime, COALESCE(C.remark, $1) AS remark
           FROM in_log_product_balance_stock A
           LEFT JOIN in_inventory B ON A.ref_id = B.inventory_id AND A.doc_type_id = B.doc_type_id
           LEFT JOIN pu_po B1 ON B.ref_id = B1.po_id AND B.ref_doc_type_id = B1.doc_type_id
           LEFT JOIN pu_invoice B2 ON B1.po_id = B2.ref_id AND B1.doc_type_id = B2.ref_doc_type_id
           LEFT JOIN sl_so B3 ON B.ref_id = B3.so_id AND B.ref_doc_type_id = B3.doc_type_id
           LEFT JOIN sl_invoice B4 ON B3.so_id = B4.ref_id AND B3.doc_type_id = B4.ref_doc_type_id
           LEFT JOIN in_inventory_item C ON B.inventory_id = C.inventory_id AND A.product_balance_id = C.product_balance_id
           WHERE CASE WHEN $5 <> -99
             THEN
            A.tenant_id = $2
            AND A.ou_id = $3
            AND A.product_id = $4
            AND A.warehouse_id = $5
            AND SUBSTRING(A.doc_date, 1, 6) BETWEEN $6 AND $7
              ELSE
            A.tenant_id = $2
            AND A.ou_id = $3
            AND A.product_id = $4
            AND SUBSTRING(A.doc_date, 1, 6) BETWEEN $6 AND $7
              END
           GROUP BY A.doc_type_id, A.doc_no, A.doc_date, B2.doc_no, B2.doc_date, B4.doc_no, B4.doc_date, A.warehouse_id, B.warehouse_from_id, B.warehouse_to_id, A.partner_id, A.base_uom_id, A.update_datetime, C.remark
    ) SELECT A.*, $8 - A.qty + SUM (A.qty) OVER (ORDER BY A.doc_date, A.update_datetime asc rows between unbounded preceding and current row) AS running_qty_begin,
    $8 + SUM (A.qty) OVER (ORDER BY A.doc_date, A.update_datetime asc rows between unbounded preceding and current row) AS running_qty_end
    FROM data A
    ORDER BY A.doc_date, A.update_datetime  '
    USING vEmptyValue, pTenantId, pOuId, pProductId, pWarehouseId, pPeriodStart, pPeriodEnd, vBeginningQty;
    RETURN NEXT pRefDetail_detail ;

    Open pRefDetail_summary FOR
    EXECUTE
    ' SELECT a.base_uom_id, f_get_uom_code(a.base_uom_id) AS base_uom_code, f_get_uom_name(a.base_uom_id) AS base_uom_name,  '
    || vBeginningQty || ' AS beginning_qty, '
    || vRekapQtyTrxPeriod || ' AS rekap_qty, '
    || vEndQty ||' AS end_qty
    FROM m_product A
    WHERE A.product_id = $1 '
    USING pProductId;
    RETURN NEXT pRefDetail_summary ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
ALTER FUNCTION public.r_inv_inquiry_stock_log_stock_card_with_invoice(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, character varying, character varying, character varying)
  OWNER TO sts;