r_monitoring_gto

CREATE OR REPLACE FUNCTION r_monitoring_goods_transfer_out(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, 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;
    pRoleId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;
    pOuId                           ALIAS FOR $6;
    pWarehouseFromId    ALIAS FOR $7;
    pDateFrom                   ALIAS FOR $8;
    pDateTo                     ALIAS FOR $9;
    pWarehouseList      ALIAS FOR $10;

    vFlagLost       character varying(1);
    vFlagMissing    character varying(1);
    vFlagRejected   character varying(1);
    vFlagCorrection character varying(1);

    vEmptyValue                     character varying := '';
    vEmptyStrip         character varying := '-';
    vAll            character varying := 'ALL';

    vFilterWarehouseTo      character varying := '';
    vFilterWarehouseFrom        character varying := '';
    vFilterOu       character varying := '';
    vWarehouseName      character varying := '';
    vOuName         character varying := '';
    vOuCode         character varying := '';

    vDocTypeIdtransferIn    bigint := 535;
    vDocTypeIdtransferOut   bigint := 533;
    vDocTypeIdGTIR                  bigint := 536;

BEGIN

    vFlagLost := 'L';
    vFlagMissing := 'M';
    vFlagRejected := 'R';
    vFlagCorrection := 'C';

    vWarehouseName := vAll;
    vOuName := vAll;
    vOuCode := vEmptyStrip;

    IF (pOuId <> -99) THEN
        vFilterOu := ' AND A.ou_from_id = '|| pOuId ;
        vOuName := f_get_ou_code(pOuId);
        vOuCode := f_get_ou_name(pOuId);
    END IF;

    IF pWarehouseFromId <> -99 THEN
        vFilterWarehouseFrom := 'AND A.warehouse_from_id = '|| pWarehouseFromId;
        vWarehouseName := f_get_warehouse_name(pWarehouseFromId);
    END IF;

    IF pWarehouseList <> vEmptyValue THEN
        vFilterWarehouseTo := 'AND A.warehouse_to_id '|| pWarehouseList;
    END IF;


    DELETE FROM tt_monitoring_gto WHERE session_id = pSessionId;

    -- 1. insert data rgto, gto, pengiriman, finalisasi pengiriman, gti ke temp table
    EXECUTE '
        INSERT INTO tt_monitoring_gto(
            session_id, tenant_id, ou_from, warehouse_from,
            ou_to, warehouse_to,
            rgto_id, rgto_date, rgto_no,
            gto_id, gto_date, gto_no,   pengiriman_date, pengiriman_no,
            finalisasi_pengiriman_date, finalisasi_pengiriman_no,
            gti_id, gti_date, gti_no,
            product_id, product_code, product_name, base_uom,
            rgto_qty, gto_qty, gti_qty, diff_gto_gti_qty
        )
        SELECT $1, $2, f_get_ou_name(A.ou_from_id), f_get_warehouse_name(A.warehouse_from_id),
        f_get_ou_name(A.ou_to_id), f_get_warehouse_name(A.warehouse_to_id),
        COALESCE(D.inventory_id, -99), COALESCE(D.doc_date, $7), COALESCE(D.doc_no, $7),
        A.inventory_id, A.doc_date, A.doc_no, COALESCE(F.doc_date, $7), COALESCE(F.doc_no, $7),
        COALESCE(H.doc_date, $7), COALESCE(H.doc_no, $7),
        COALESCE(C.inventory_id, -99), COALESCE(C.doc_date, $7), COALESCE(C.doc_no, $7),
        X.product_id, X.product_code, X.product_name, f_get_uom_name(B.base_uom_id),
        B.qty_request, B.qty_realization, COALESCE(Y.qty_realization, 0), B.qty_realization - COALESCE(Y.qty_realization, 0)
        FROM in_inventory A -- ini gto
        INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
        INNER JOIN in_pengiriman_item E ON A.inventory_id = E.ref_id AND A.doc_type_id = E.ref_doc_type_id
        INNER JOIN in_pengiriman F ON E.pengiriman_id = F.pengiriman_id
        INNER JOIN m_product X ON B.product_id = X.product_id
        LEFT JOIN in_inventory C ON A.inventory_id = C.ref_id AND A.doc_type_id = C.ref_doc_type_id AND C.doc_type_id = $8 -- ini gti
        LEFT JOIN in_inventory_item Y ON Y.inventory_id = C.inventory_id AND B.product_id = Y.product_id -- item gti
        LEFT JOIN in_inventory D ON D.inventory_id = A.ref_id AND D.doc_type_id = A.ref_doc_type_id -- ini rgto
        LEFT JOIN in_finalisasi_pengiriman_item G ON A.inventory_id = G.ref_id AND A.doc_type_id = G.ref_doc_type_id
        LEFT JOIN in_finalisasi_pengiriman H ON G.finalisasi_pengiriman_id = H.finalisasi_pengiriman_id
        INNER JOIN t_user_role V ON V.user_id = $10 AND V.role_id = $11
        INNER JOIN t_policy_ou W ON A.ou_from_id = W.ou_id AND V.policy_id = W.policy_id
        INNER JOIN m_policy_warehouse Z ON A.warehouse_to_id = Z.warehouse_id AND V.user_role_id = Z.user_role_id
        WHERE A.tenant_id = $2
            '|| vFilterOu ||'
            '|| vFilterWarehouseFrom ||'
            AND A.doc_type_id = $9
            AND A.doc_date BETWEEN $4 AND $5
            '|| vFilterWarehouseTo ||'
    'USING pSessionId, pTenantId, pOuId, pDateFrom, pDateTo, pWarehouseFromId, vEmptyStrip,
            vDocTypeIdtransferIn, vDocTypeIdtransferOut, pUserId, pRoleId;

  -- 2. update data gtir ke temp table
    UPDATE tt_monitoring_gto A
        SET gtir_date = COALESCE(B.doc_date, vEmptyStrip), gtir_no = COALESCE(B.doc_no, vEmptyStrip),
        missing_qty = CASE WHEN C.reason_receipt_code = vFlagMissing THEN
                CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
                ELSE 0 END,
        rejected_qty = CASE WHEN C.reason_receipt_code = vFlagRejected THEN
                CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
                ELSE 0 END,
        correction_qty = CASE WHEN C.reason_receipt_code = vFlagCorrection THEN
                CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
                ELSE 0 END,
        lost_qty = CASE WHEN C.reason_receipt_code = vFlagLost THEN
                CASE WHEN C.qty_realization < 0 THEN C.qty_realization * -1 ELSE C.qty_realization END
                ELSE 0 END
    FROM in_inventory B
    INNER JOIN in_inventory_item_receipt C ON B.inventory_id = C.inventory_id
    WHERE B.ref_id = A.gti_id
        AND B.ref_doc_type_id = vDocTypeIdtransferIn
        AND C.product_id = A.product_id
        AND C.qty_realization <> 0;

  -- 3. update data hppa
    WITH product_cogs AS (
            SELECT D.tenant_id, D.ou_id, MAX(D.date_year_month) AS year_month_date, D.product_id, D.avg_price
            FROM in_summary_monthly_cogs D
            INNER JOIN m_ou_structure E ON E.ou_bu_id = D.ou_id
            GROUP BY D.tenant_id, D.ou_id, D.product_id, D.avg_price)
    UPDATE tt_monitoring_gto A
        SET hppa_price = COALESCE(B.avg_price, 0)
    FROM product_cogs B
    INNER JOIN m_ou_structure C ON C.ou_bu_id = B.ou_id
    WHERE A.session_id = pSessionId
        AND B.tenant_id = A.tenant_id
        AND B.product_id = A.product_id
        AND B.year_month_date <= SUBSTRING(A.gto_date, 1, 6);

    Open pRefHeader FOR
        SELECT f_get_user_fullname(pUserId) username, vOuCode AS ou_code, vOuName AS ou_name, pDatetime datetime,
            pDateFrom date_from, pDateTo date_to, f_get_warehouse_code(pWarehouseFromId) warehouse_code, vWarehouseName AS warehouse_name;
    RETURN NEXT pRefHeader;
    Open pRefDetail FOR
        SELECT ou_from, warehouse_from, ou_to, warehouse_to,
            rgto_id, rgto_date, rgto_no,
            gto_id, gto_date, gto_no,   pengiriman_date, pengiriman_no,
            finalisasi_pengiriman_date, finalisasi_pengiriman_no,
            gti_id, gti_date, gti_no,
            product_id, product_code, product_name, base_uom, hppa_price,
            gtir_date, gtir_no,
            rgto_qty, gto_qty, gti_qty,
            CASE WHEN diff_gto_gti_qty < 0 THEN diff_gto_gti_qty * -1 ELSE diff_gto_gti_qty END AS diff_gto_gti_qty,
            gto_qty * hppa_price AS gto_amount, gti_qty * hppa_price AS gti_amount,
            CASE WHEN diff_gto_gti_qty < 0 THEN (diff_gto_gti_qty * -1) * hppa_price
                    ELSE diff_gto_gti_qty * hppa_price END AS diff_gto_gti_amount,
            missing_qty, rejected_qty, correction_qty, lost_qty,
            missing_qty * hppa_price AS missing_amount, rejected_qty * hppa_price AS rejected_amount,
            correction_qty * hppa_price AS correction_amount, lost_qty * hppa_price AS lost_amount
        FROM tt_monitoring_gto A
        WHERE A.session_id = pSessionId
        ORDER BY A.ou_from, A.gto_date, A.gto_no, warehouse_from;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_monitoring_gto WHERE session_id = pSessionId;

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