rr_inv_inquiry_stock_detail_with_serial_number_log_stock_car

/**
 * Modified by Adrian, May 19, 2017
 * Menambahkan remark pada refDetail
 */

CREATE OR REPLACE FUNCTION r_inv_inquiry_stock_detail_with_serial_number_log_stock_card(character varying, bigint, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
 DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';
    pRefSummary                 REFCURSOR := 'refSummary';
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pRoleId                     ALIAS FOR $4;
    pOuId                       ALIAS FOR $5;
    pDatetime                   ALIAS FOR $6;
    pWarehouseId                ALIAS FOR $7;
    pProductId                  ALIAS FOR $8;
    pPeriodStart                ALIAS FOR $9;
    pPeriodEnd                  ALIAS FOR $10;
    pSerialNumber               ALIAS FOR $11;
    pPeriodStartMinusOneMonth   ALIAS FOR $12;


    vWarehouseEmpty              bigint      = -99;
    vBeginningQtyFromLastProcess numeric    := 0;
    vBeginningQty                numeric    := 0;
    vRekapQtyTrxPeriod           numeric    := 0;
    vEndQty                      numeric    := 0;
    vRekapQtyForSaldoAwal        numeric    := 0;
    vLastYearMonth                  text    := '';
    vFilterWarehouseId              text    := '';
    vSerialNumber                   text    := '';
    vFilterSerialNumber             text    := '';
    vEmpty                          text    := '';
    vForSerialNumber                text;

BEGIN

    IF(pSerialNumber <> vEmpty) THEN
        vSerialNumber := 'AND UPPER(C.serial_number) LIKE UPPER(''%' || pSerialNumber || '%'' )';
    END IF;

    IF(pSerialNumber <> vEmpty) THEN

            SELECT COALESCE(SUM(A.qty), 0) AS qty INTO vRekapQtyForSaldoAwal
            FROM in_log_product_balance_stock AS A
            INNER JOIN in_product_balance B ON A.product_balance_id = B.product_balance_id
            WHERE A.tenant_id = pTenantId
                AND A.ou_id = pOuId
                AND A.product_id = pProductId
                AND A.warehouse_id = pWarehouseId
                AND UPPER(B.serial_number) LIKE UPPER('%' || pSerialNumber || '%' )
                AND SUBSTRING(A.doc_date, 1, 6) > vLastYearMonth
                AND SUBSTRING(A.doc_date, 1, 6) <= pPeriodStartMinusOneMonth;

    ELSE

            SELECT COALESCE(SUM(A.qty), 0) AS qty INTO vRekapQtyForSaldoAwal
            FROM in_log_product_balance_stock AS A
            INNER JOIN in_product_balance B ON A.product_balance_id = B.product_balance_id
            WHERE A.tenant_id = pTenantId
                AND A.ou_id = pOuId
                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;

    END IF;

    IF (pWarehouseId <> vWarehouseEmpty) THEN
        vFilterWarehouseId := ' AND A.warehouse_id =  ' || pWarehouseId;
    END IF;

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

        SELECT COALESCE(SUM(A.qty), 0) INTO vBeginningQtyFromLastProcess
        FROM in_summary_monthly_qty AS A
        WHERE A.tenant_id = pTenantId
        AND A.sub_ou_id = pOuId
        AND A.doc_type_id = -99
        AND A.product_id = pProductId
        AND A.warehouse_id = pWarehouseId
        AND A.date_year_month = vLastYearMonth;

    IF(pPeriodStartMinusOneMonth = vLastYearMonth) THEN

        vBeginningQty := vBeginningQtyFromLastProcess;

    ELSE
        vBeginningQty := vBeginningQtyFromLastProcess + vRekapQtyForSaldoAwal;

    END IF;

    SELECT COALESCE(SUM(A.qty), 0) AS qty INTO vRekapQtyTrxPeriod
    FROM in_log_product_balance_stock A
    INNER JOIN in_product_balance B ON A.product_balance_id = B.product_balance_id
    WHERE A.tenant_id = pTenantId
        AND A.ou_id = pOuId
        AND A.product_id = pProductId
        AND A.warehouse_id = pWarehouseId
        AND UPPER(B.serial_number) LIKE UPPER('%' || pSerialNumber || '%' )
        AND SUBSTRING(A.doc_date, 1, 6) BETWEEN pPeriodStart AND pPeriodEnd;

    vEndQty := vBeginningQty + vRekapQtyTrxPeriod;

    Open pRefHeader FOR

    SELECT f_get_ou_name(pOuId)AS ou_name,
            f_get_warehouse_name(pWarehouseId) AS warehouse_name,f_get_product_name(pProductId) AS product_name,
            CASE WHEN pSerialNumber = vEmpty THEN '(All)' ELSE pSerialNumber END AS serial_number,
            f_get_username(pUserId) AS user_name, f_get_role_name(pRoleId) AS role_name,(pDatetime) AS datetime,
            (pPeriodStart) AS period_start,(pPeriodEnd) AS period_end;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
    EXECUTE '

    SELECT  f_get_doc_desc(A.doc_type_id) AS doc_type_desc, A.doc_no, A.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,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, C.serial_number,
            C.product_year_made, C.product_expired_date, COALESCE(D.remark, $7) AS remark

    FROM in_log_product_balance_stock AS A
            LEFT OUTER JOIN in_inventory AS B ON A.ref_id = B.inventory_id AND A.doc_type_id = B.doc_type_id
            LEFT OUTER JOIN in_inventory_item AS D ON D.inventory_id = B.inventory_id AND D.product_balance_id = A.product_balance_id
            INNER JOIN in_product_balance AS C ON A.product_balance_id = C.product_balance_id

    WHERE A.tenant_id = $1 AND A.ou_id = $2 AND A.product_id = $3 AND A.warehouse_id = $4
    AND SUBSTRING(A.doc_date, 1, 6) BETWEEN $5 AND $6 '|| vSerialNumber ||'
    GROUP BY A.doc_type_id, A.doc_no, A.doc_date, A.warehouse_id, A.partner_id, A.base_uom_id, B.warehouse_to_id, A.update_datetime, C.serial_number, C.product_year_made, C.product_expired_date, D.remark
    ORDER BY A.doc_date, A.update_datetime '
    USING pTenantId, pOuId,pProductId,pWarehouseId,pPeriodStart,pPeriodEnd, vEmpty;
    RETURN NEXT pRefDetail ;

    Open pRefSummary 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 pRefSummary;

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