r_aging_goods_borrowing

DROP FUNCTION IF EXISTS r_aging_goods_borrowing(character varying,bigint,bigint,character varying,bigint,character varying,character varying,character varying,bigint,character varying);

/*Modified by Adrian, Jul 26, 2017,
Mengubah tanggal mulai dan perhitungan qty aging
Menghilangkan inventory_borrow_id pada tipe Summary*/

CREATE OR REPLACE FUNCTION r_aging_goods_borrowing(character varying,bigint,bigint,character varying,bigint,character varying,character varying,character varying,bigint,character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pDatetime                   ALIAS FOR $4;
    pOuId                       ALIAS FOR $5;
    pProductCodeName            ALIAS FOR $6;
    pStartDate                  ALIAS FOR $7;
    pEndDate                    ALIAS FOR $8;
    pPartnerId                  ALIAS FOR $9;
    pIsType                     ALIAS FOR $10;

    vSummary                    CHARACTER VARYING := 'summary';
    vDay                        CHARACTER VARYING := 'day';
    vFormatDate                 CHARACTER VARYING := 'YYYYMMDD';
    vInProgress                 CHARACTER VARYING := 'I';
    vReleased                   CHARACTER VARYING := 'R';
    vPartnerId                  CHARACTER VARYING := '';
    vProductCodeName            CHARACTER VARYING := '';
    vNullLongValue              BIGINT := -99;
    vDocTypeId                  BIGINT := 551;
    vEmptyValue                 CHARACTER VARYING := '';
    vCurrentDate                CHARACTER VARYING := to_char(current_date, vFormatDate);

BEGIN

    IF pPartnerId <> vNullLongValue THEN
        vPartnerId := ' AND B.partner_id = ' || pPartnerId;
    END IF;

    IF pProductCodeName <> vEmptyValue THEN
        vProductCodeName := ' AND ( UPPER(f_get_product_code(C.product_id)) LIKE UPPER(''%'||pProductCodeName||'%'')  OR UPPER(f_get_product_name(C.product_id)) LIKE UPPER(''%'||pProductCodeName||'%'') ) ';

    END IF;

    OPEN pRefHeader FOR
        SELECT f_get_ou_name(pOuId) AS ou_name,f_get_username(pUserId) AS username,CASE WHEN pPartnerId <> vNullLongValue THEN f_get_partner_name(pPartnerId) ELSE 'All' END AS partner_name,
                pStartDate AS start_date,pEndDate AS end_date;
    RETURN NEXT pRefHeader;

    IF pIsType = vSummary THEN

        OPEN pRefDetail FOR
            EXECUTE '
                SELECT f_get_product_code(C.product_id) AS product_code,f_get_product_name(C.product_id) AS product_name,SUM(D.qty_do) AS qty,
                    f_get_partner_name(B.partner_id) AS partner_name,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 0 AND 7 THEN SUM(D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp1,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 8 AND 14 THEN SUM(D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp2,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 15 AND 21 THEN SUM(D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp3,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) > 21 THEN SUM(D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp4
                FROM in_inventory_borrow A
                INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
                INNER JOIN in_inventory_item C ON A.inventory_id = C.inventory_id
                INNER JOIN in_inventory_borrow_balance_item D ON A.inventory_id = D.inventory_id AND C.inventory_item_id  = D.inventory_item_id
                WHERE B.tenant_id = $1
                AND B.ou_from_id = $2
                AND B.doc_date BETWEEN $3 AND $4
                AND D.status_item IN ($7,$8)
                AND B.doc_type_id = $9' || vPartnerId || vProductCodeName ||'
                GROUP BY C.product_id,D.due_date,B.partner_id,D.base_uom_id
                '
            USING pTenantId,pOuId,pStartDate,pEndDate,vDay,vFormatDate,vInProgress,vReleased,vDocTypeId,vCurrentDate;
        RETURN NEXT pRefDetail;

    ELSE

        OPEN pRefDetail FOR
            EXECUTE '
                SELECT B.doc_no,B.doc_date,B.remark,f_get_product_code(C.product_id) AS product_code,f_get_product_name(C.product_id) AS product_name,D.qty_do AS qty,D.due_date,
                        f_get_partner_name(B.partner_id) AS partner_name,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 0 AND 7 THEN (D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp1,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 8 AND 14 THEN (D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp2,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) BETWEEN 15 AND 21 THEN (D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp3,
                    CASE WHEN DATE_PART($5, to_timestamp($10, $6) - to_timestamp(D.due_date, $6)) > 21 THEN (D.qty_do - D.qty_return - D.qty_sales) ELSE 0 END AS temp4
                FROM in_inventory_borrow A
                INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
                INNER JOIN in_inventory_item C ON A.inventory_id = C.inventory_id
                INNER JOIN in_inventory_borrow_balance_item D ON A.inventory_id = D.inventory_id AND C.inventory_item_id  = D.inventory_item_id
                WHERE B.tenant_id = $1
                AND B.ou_from_id = $2
                AND B.doc_date BETWEEN $3 AND $4
                AND D.status_item IN ($7,$8)
                AND B.doc_type_id = $9' || vPartnerId || vProductCodeName ||'
                '
            USING pTenantId,pOuId,pStartDate,pEndDate,vDay,vFormatDate,vInProgress,vReleased,vDocTypeId,vCurrentDate;
        RETURN NEXT pRefDetail;

    END IF;

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